Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Using the Enterprise Library Data Access Block for .NET 2.0 : Page 3

Writing database-access code is a repetitious and time-consuming task, but now that it's available as a reusable Enterprise Data Access Application Block, you'll never have to write such code again.


advertisement
Execute a SQL Query Returning a DataSet
The Database class provides a database-agnostic approach, meaning that you can leverage the class across a wide range of relational databases such as SQL Server, Oracle and others. However, it also contains database-specific classes, such as SqlDatabase (derived from Database), which represents a SQL Server database, and OracleDatabase, which lets you program against the Oracle database. You should use these database-specific classes when you're programming solely for one of the supported databases.

Note that neither the SqlDatabase nor the OracleDatabase classes provide static methods; you must create an instance to work with them. The following page code shows how to execute a SQL query against the AdventureWorks database using the SqlDatabase class.

<%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.Common" %> <%@ Import Namespace= "Microsoft.Practices. EnterpriseLibrary.Data" %> <%@ Import Namespace= "Microsoft.Practices. EnterpriseLibrary.Data.Sql" %> <script runat="server"> void Page_Load(object sender, EventArgs e) { string connString = System.Web.Configuration. WebConfigurationManager.ConnectionStrings ["AdventureWorksDB"].ConnectionString; SqlDatabase db = new SqlDatabase(connString); string sqlCommand = "Select " + "EmployeeID, NationalIDNumber," + "LoginID, Title from " + " HumanResources.Employee "; DbCommand dbCommand = db.GetSqlStringCommand (sqlCommand); DataSet dst = db.ExecuteDataSet(dbCommand); gridEmployees.DataSource = dst.Tables[0].DefaultView; gridEmployees.DataBind(); } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Executing a SQL Select Statement to retrieve a DataSet</title></head> <body> <form id="form1" runat="server"> <div><asp:GridView runat="server" ID="gridEmployees" /></div> </form> </body> </html>

Author's Note: The namespaces you import (Imports) and the page HTML code for the rest of the examples in this article are similar to the preceding example, so I'll omit those in the remaining examples; however, you can download the sample code to get complete sample pages that you can experiment with.

You start by retrieving the connection string from the Web.config file using the WebConfigurationManager class, then you supply the connection string as an argument to the SqlDatabase class constructor. After you have an instance of the SqlDatabase class, you work with it in the same manner as the generic Database class, because SqlDatabase inherits from Database. For example, the SqlDatabase class also exposes methods such as GetSqlStringCommand(), and ExecuteDataSet() through which you can retrieve a DbCommand object instance, execute a SQL query, and return the results in the form of a DataSet object. Although the preceding example uses a SELECT SQL statement, you can easily perform the same operation using a stored procedure.

Executing a Stored Procedure to Retrieve a Single Item
Sometimes, you might want to retrieve only one item from a stored procedure or SQL query. For example, after inserting a new row into the database, you might want to return the newly inserted identity value to the consumers.

For this purpose, the Database class provides a method named ExecuteScalar() that accepts a DbCommand object as an input argument. To showcase this, I will leverage a stored procedure named GetEmployeeNationalID that takes an EmployeeID as an argument and returns the NationalID value for that employee. The stored procedure is declared as follows:

Create Procedure GetEmployeeNationalID @EmployeeID int AS Select NationalIDNumber from HumanResources.Employee Where EmployeeID = @EmployeeID

The following ASP.NET page excerpt shows the code required to invoke the GetEmployeeNationalID stored procedure.



<script runat="server"> void Page_Load(object sender, EventArgs e) { int employeeID = 1; Database db = DatabaseFactory.CreateDatabase(); string sqlCommand = "GetEmployeeNationalID"; DbCommand dbCommand = db.GetSqlStringCommand (sqlCommand); dbCommand.CommandType = CommandType.StoredProcedure; db.AddInParameter(dbCommand, "EmployeeID", DbType.Int32, 1); string nationalID = (string) db.ExecuteScalar(dbCommand); Response.Write("NationalID : " + nationalID + "<br>"); } </script>

First, the code adds the EmployeeID input parameter to the DbCommand object using the AddInParameter() method. Note that the EmployeeID parameter is not prefixed with an ampersand (@), which is how you would normally indicate a stored procedure parameter for SQL Server—and how the previous version of the Data Access Block worked. In this new version of the Data Access Block, the Database class is generic, obviating the need to specify database-specific prefixes such as ampersands and question marks. The ExecuteScalar() method executes the stored procedure and returns the NationalID, which the code displays on the client using the Response.Write() statement at the end of the script.



Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap