Browse DevX
Sign up for e-mail newsletters from DevX


Data Access with Microsoft Application Blocks : Page 4

Microsoft has created a set of libraries known as Application Blocks which help developers reduce the amount of code they must write—while using current best practices. One of the components, Data Access Application Block for .NET, addresses Microsoft SQL Server data access by wrapping up data access into a helper class.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Working with the Data Access Application Block
Together the SqlHelper class and the SqlHelperParameterCache class create a robust package for data access. Unfortunately, very rarely does one size fit all and the Data Access Application Block is no exception. It will handle most of what you need but you still may have to do some data access code by hand. For example, if you know your query may take more than 15 seconds to run, you probably don't want to use the SqlHelper class. You cannot specify a command timeout so you are stuck with the SqlCommand's default of 20 seconds. Now of course, because you have the source code there's nothing to stop you from overloading the existing methods and including a timeout parameter—but if a version 3.0 appears, you may have to implement your code again. Another area that you may have to get around is that since the SqlHelperParameterCache returns a set of SqlParameters as an array, there currently is no way to access the parameters by name, only by indexer. However, Listing 1 demonstrates a few lines of code you can use to get around that minor issue as well. Listing 2 illustrates how to use the Data Access Application Blocks. The main public method, GetSalesReport calls two private methods, GetSalesByYear and GetSalesByCategory to populate a DataSet with two DataTables.

In the GetSalesByYear method I used the ExecuteDataSet method of the SqlHelper class to return my DataSet as well as the GetSpParameterSet method of the SqlHelperParameterCache class to retrieve and cache an array of SqlParameter objects. I created a string variable and set it to my SQL command text. I used this command string to create a cache of SqlParameters to pass to the ExecuteDataSet method. Next I set up the SqlParameter objects so I can pass some parameters to the stored procedure. Instead of creating the SqlParameters by hand, I use the GetSpParameterSet method to get the SqlParameter list for me. I simply pass in the Sqlcommand string and the SqlConnectionString via the conn.ConnectionString property. As I mentioned before, this method will attempt to find an array of SqlParameter objects for the given command and connection string, clone that array, and return the cloned SqlParameter array. Otherwise it will use the SqlCommandBuilder to retrieve the parameters from the database, cache the SqlParameters, clone the SqlParameter array, and return the cloned array. Next I need to specify the values for my cloned copy of the SqlParameter array. This can pose a problem if the order of the parameters in the stored procedure changes. You'll have to live with this limitation unless you create a method for retrieving SqlParameter objects by name like in Listing 1. Finally I call the ExecuteDataSet method with the connection object, command type, command string, and SqlParameter array. Voila!

Now in the second method, GetSalesByCategory, I use a different approach. I pass in the DataSet and use the FillDataSet method of the SqlHelper class to populate it. Note that I am not using the SqlHelperParameterCache. Well, at least not directly. Remember that the method overloads that take an array of objects use the SqlHelperParameterCache behind the scenes to figure out the details for each SQL parameter. The same rule about the order of the parameters in the stored procedures applies here. If the order in the stored procedure changes then you need to modify your code to match that order. Otherwise your procedure will be called with incorrect parameters resulting in an exception or garbage data being passed to your procedure. Another thing to remember is that this particular overload only works with input SqlParameters, so you cannot retrieve output parameters. And you cannot pass in optional parameters. Finally, I want to point out that I did not pass the values as an array. I use the C# params keyword for the object array, allowing me to specify my values by simply listing them out. (The VB.NET equivalent of this is the ParamArray keyword.) Walking through the GetSalesByCategory method, I set a string variable equal to the SQLcommand string I intend to use. Next I created a string array of table names that the FillDataset method uses to map the resultset(s) returned by the SQLcommand to specific table names. (This is really useful with typed DataSets to fill the typed DataTables). Finally, I simply call the FillDataSet method with the SqlCommand object, command string, and the variables to be passed into the specified SQL command. As you can see, using this specific overload can result in a lot less coding, especially when you need to pass in a lot of parameters to an SQL Command.

The Data Access Application Block can save you a lot of time. You simply specify a few parameters and presto, you're done. However, with simplification comes reduced flexibility and control. Using Data Access Application Block may force you to make a few concessions, especially if you have queries that run a long time and you need to access parameters by name from the SqlParameter array. One nice thing about the Data Access Application Block is that you get the source code with the download, so with a few minor tweaks, this Application Block will meet most if not all of your data access logic needs. CoDe Magazine has an article on the Exception Management Application Block. This framework provides a simple, robust way to manage and publish application exceptions.

Keenan Newton is a Senior Consultant for Magenic Technologies, Inc., one of the nation's premiere Microsoft Gold Certified Partners, focused on delivering business value through applied technology. He has over eight years of experience designing, architecting, and developing n-tier applications for both Windows and Web environments.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date