If God had wanted me to work, he wouldn’t have given me Angry Birds.
I figured it was about time I wrote something about Dynamics NAV again, so setting my continuing mission to get three stars on all levels to one side, I’ve finally taken time to pass on a couple of tips around .NET Interop and executing SQL Stored Procs from within NAV (I used to work with someone that pronounced SQL as “Squeal” instead of “Sequel” hence the title of this post).
Way back in April (wow has it been that long?) I wrote about how you could use .NET Interop to execute SQL commands using NAV 2009 R2. In my example I executed a simple SQL command and showed how you could parse the dataset returned from the command.
When I finally came to put this into practice I discovered I had made two errors in my original post which proved to me that I am better at Angry Birds than I am at NAV programming and that, whilst I get many people reading my blog, it’s rare that people actually try out the things I write about.
My first mistake was when I said that I would use the session table to get the machine name of the SQL Server. Wrong! Yes it worked in my example, but that was only because I was running the NAV Server on the same box as the SQL Server. The “Host Name” field on the Session table is the machine name that initiated the connection, so when I moved my code to three tiers it didn’t work. So how do you find the name of the SQL Server you are connecting to?
Here’s one way that works. I created a view that includes the server name like so:
CREATE VIEW [dbo].[SQLConnectionDetails] AS SELECT @@ServerName AS [Server Name], DB_NAME() AS [Database Name]
Then I made a linked table in NAV over the top of the view. Now I can use SQLConnectionDetails.[Server Name] as part of my connect string. Phew!
My next mistake was when I assumed that the enumerated value for a SQL Stored Proc command type was 1. Wrong again! I’d made that assumption because the documentation on the properties of the CommandType showed a table with three options then they would be numbered 0, 1, and 2. Finding the correct integer value was not easy but in the end it was Waldo to the rescue. I knew I had read a post from Waldo about using enumerated types in .NET interop so I used his code to parse the value. Here’s how to do it for the SQL command type.
//Set the command type as StoredProcedure. dnCommandType := dnSQLCommand.CommandType; dnSQLCommand.CommandType := dnCommandType.Parse(dnCommandType.GetType(),'StoredProcedure');
Much better to have this code that is self-documenting than put in the numeric values but if you’re feeling lazy, the number values are: Text is 1, StoredProcedure is 4 and TableDirect is 512.
My final problem (not a mistake this time but something I thought I would pass on) was when I came to try to add parameters to my stored procedure I hit a problem I thought I couldn’t get past.
I could not instantiate a Parameter collection on my SQL Command because NAV thought for some crazy reason that there were two Parameters properties. Now .NET (as far as I know) does not support overloaded properties so this is not possible and I can only put it down to a bug in the R2 release. I have reported this to the product team and I am assured this problem will be fixed in a later release.
So if I can’t add parameters to my stored proc, how can I get around it? My solution was to instantiate a variable of type System.Data.Common.DbCommand which the SQLCommand class inherits. I could then use the Parameters property of the Common.DbCommand and assign this to my SQLParameterCollection. Don’t you just love object oriented?
dnCommonDBCommand := dnSQLCommand;
// Need to use the dnCommonDBCommand.Parameters becuase the SQLCommand.Parameters gets confused in the R2 Release dnSQLParameterCollection := dnCommonDBCommand.Parameters(); dnSQLParameterCollection.Add(pParameterName,pParameterValue);
Now I have instantiated my SQLParameterCollection, I can use the Add method and add my new parameters.
Well that’s it for now. Time to try to crack three stars on level 10. Die pigs, die!