For long, we (as a partner community) have been asking for longer field lengths. And this time, Microsoft seems to have delivered: about 860 field lengths were changed. Mostly from Text50 to Text100... (Continue reading)

I’m returning back from yet another great Directions US event in Las Vegas this time. At this point – I’m sitting in the lounge in New York – a great opportunity to do a short post about something I didn... (Continue reading)

My intentional plan was to share a small blog about the Fall release of Business Central . Well – it turned out I had more to say than I imagined beforehand ;-).
I would like to focus on one point,... (Continue reading)

You might remember my blog from yesterday … . I didn’t know when it would be released.
Well, it wasn’t available yesterday, but today, I got the comment on my blog from Daniel Göhler that it was available... (Continue reading)

I’m not attending Directions ASIA this year – and it doesn’t really feel good. There is a major release around the corner, and I have to experience it through social media – I hate it! But that’s life... (Continue reading)

Using ADO & Stored Procedures in C/SIDE

Recently, I had to do a small project. A client of us wanted to see the global inventory of all its companies. Furthermore, the companies were also spread over multiple databases. They wanted quick answers on questions like "What is the inventory of this item in any location in any company in any database". Now, I'm not going to tell you how I solved the case. I just wanted to highlight some interesting stuff that I used.

Some basics about using ADO in NAV.

ADO (ActiveX Data Objects) is described as "ADO is designed to provide a consistent way of accessing data regardless of how the data are structured. ". It's actually automation that you can use (should be installed together with windows - but I'm not sure) to access about any kind of database. It's generally used by developers in C/SIDE to access data in another database then the current database.

It's basically 3 steps:

Make a connection to the database, using a "connectionstring"

http://www.connectionstrings.com/ is a very helpful website to help you compose your connectionstring. Usually, I create a seperate setup table with the parameters needed to create a connectionstring.

If you're a dedicated C/SIDE developer, you see that this is not usual. Creating a connection? Closing a connection?? In C/SIDE, you're already connected by just declaring a record variable - by sorts of speaking. That's because the native client connects with the database before you can start working - and therefore, you're always connected . It's logical, I know, but anyway ...

What could it be useful for?

So, what can we do in for example in the database. Well. A number of things. From the top of my head:

Creating company-specific views when a new company is created

Create data in a queue table

Compose a SQL String, execute that SQL String and givie back the results ... .

...

It all comes down on executing a stored procedure in a SQL Server database. If you can do that, then the possibilities is limited to what you can do with Stored Procedures. And there is not much limitation to that . About everything you can do in T-SQL, you can do with SP's!

So, let me try to explain all this by using a few examples:

Creating company-specific views when a new company is created

First of all, I created a stored procedure in SSMS (SQL Server Management Studio) like this (I added some comments to make things clearer):

You see that I'm using LADOCommand, which is an automation variable of the type "'Microsoft ActiveX Data Objects 2.8 Library'.Command" and LADOParameter is a "'Microsoft ActiveX Data Objects 2.8 Library'.Parameter". Some explanation is required, I guess:

A command needs a Command Type. This site is useful to help you with the different command types. You see we used "4", being a stored procedure.

To let the SP know for what company we have to create the view, we have to send a parameter. Therefor, we use the ADO parameter automation mentioned above. To help you with this, this site is useful. You have to define the direction of the parameter (input, output, ...) and the data type and its length. In my example, I used an input parameter, varchar as datatype and length 30.

Note that we used lvarActiveConnection. This variable is of the type Variant. You have to give the active connection to the command variable, and this is the way to do it:

Note that we used lvarActiveConnection to load the active connection into our recordset variable as well.

The execution of the SP is the same as before, but now you expect something back. That's why you're loading the resultset into a recorset ('Microsoft ActiveX Data Objects 2.8 Library'.Recordset) with the statement: lADORecordset.Open(lADOCommand);

After the recordset is loaded, we can loop that recordset and put (in my case) the results into a temp table.

Not that you can move through your recordset with MoveNext, MoveFirst, MoveFirst, MoveLast. You can check if you're at the end with EOF (End Of File).

Using an output parameter in a Stored Procedure

OK, taking a little step back. Suppose we want to create a SP with a simple output parameter like a decimal, boolean or whatever. So you won't get back a recordset like above, but just an output parameter. Not really creative, but a SP with an output parameter looks something like this:

//Get your result back from the command variable :ltxtResult := FORMAT(lADOCommand.Parameters.Item('@Result').Value);

MESSAGE(ltxtResult);

... <Close your connection here>

You see that you need an extra statement to pull your result back from the command variable. It's not going to do that for you automatically (allthough you defined your parameter as being "output" (we defined the direction-argument in the CreateParameter-statement as "2")).

One comment might be that you want to use the return value in stead of an output parameter, but keep in mind that the return value of a SP must be "integer".

There are tons of other things you can do with ADO, Stored Procedures,... . I just wanted you to experience how you can use ADO, and what it might be useful for. I'm not saying that this is always the best solution in any case. It's just one of the possibilities where much is possible and flexible solutions can be built with.

Sorry for the somewhat bad editing. I had my hands full with it .

Any comment is highly appreciated.

Small remark: I created this blog using a live example (as mentioned above). I just copied / pasted / changed variables / rearranged code / ... . So I don't guarantee that everything just works by copy/paste/run it ... .

I have written a very long running stored procedure(inventory valuation) and I am worried that the user will think the application has stopped responding, have you done any work or have any ideas about how to execute the SP asynchronously so you can create a progress bar within NAV?