C#: Microsoft Enterprise Library: Data Access
By: Zack Turnbow
Introduction
Most developers, as they continue to produce software products, start to build libraries of reusable code. Much of it is the mundane tasks that are associated with standard software development.
That’s why Microsoft started sharing some of their proven code with the development community back in 2005. Since that initial release there have been several versions that have continued keeping up with new technologies and providing a much quicker development time with the most current version being 4.0 that was released on May of this year.
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
This series will delve into common uses for each of these “blocks” of code. For this series, version 3.1 will be used along with SQL 2005 Express with the AdventureWorks database.
Implementation
Start a new project, it can be a Windows or web application. Go ahead and add a reference to the data access application block.
[Click to see full-size]
Repeat the step to add a reference to the shared Common and Configuration dll as well. They will come into play later in the article.
On the designer for the Default.aspx drag a GridView control onto the work area. Before we jump right into the DAAB, let’s take a look at what would usually be done when accessing data from the database.
// Create a connection to the datbase
string conn = "Server=(local);Database=AdventureWorks;IntegratedSecurity=True;";
// Create the query to be executed
string query = "select * from Production.Product where ProductID = 1";
// Create the connection object then open the connection
SqlConnection sqlConn = new SqlConnection(conn);
sqlConn.Open();
// Create the command object then set the appropriate commamd type
SqlCommand sqlCmd = new SqlCommand(query, sqlConn);
sqlCmd.CommandType = CommandType.Text;
// Set the data source of the grid to the the ExecuteReader method
GridView1.DataSource = sqlCmd.ExecuteReader();
GridView1.DataBind();
// Make sure to close the connection
objConn.Close();
If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!
Now to achieve the same results using the DAAB, so first open up the web.config and add some connection information. I should look something like this:
Go back to the code behind and add the following using statements:
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Common;
Since everything is setup now, let’s write some code. The first thing is to create a database reference. It will need to be placed after the partial class declaration in the code behind. It will look like this:
Add code to create the SQL query string and access the database to retrieve the data and bind it to the grid.
// Create the SQL query
string strSql = "select * from Production.Product where ProductID = 1";
// Set the grid data source to the SQL helper class ExecuteReader method by supplying
// the proper information
GridView1.DataSource = db.ExecuteReader(CommandType.Text, strSql);
GridView1.DataBind();
We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.
It is simple to see that it requires less typing to get the same results. Many people think there is not much benefit but when doing development, the more typing involved, the more likely there are going to be mistakes.
The more mistakes (bugs), the more it takes time to finish a project.
What have we learned?
How to add a reference to a dll in a project.
How use the web.config file to hold database connection strings.
How to use the Enterprise Library Configuration Application Block to configure a database connection from the web.config
How to use the Enterprise Library Data Access Application Block to retrieve data from a database.
Attachments
|
|