SQL Calls from ASP.NET

When I started working in .NET, one of the things that confused me was the process to make SQL calls from ASP.NET. The basic process was:

  1. Create a SqlConnection object
  2. Give the SqlConnection a connection string
  3. Create a SqlCommand
  4. Give the SqlCommand the actual SQL code
  5. Bind the SqlConnection to the SqlCommand
  6. Open the SqlConnection
  7. Execute the SqlCommand
  8. Close the SqlConnection

An example of how to do this can be found at ASP.NET Tutorials. While this works, I thought it seemed like a lot of work just to make a call to a database.

Then I heard about the Database Factory that is part of the Microsoft Enterprise Library 2.0. (yes, I know that there are newer versions, but this is the version I’ve used.) I’m only using a very small part of this library, but it sure makes database calls easy.

Here is some sample code that would call the stored procedure from Case Statement in SQL Query

using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

const string DBConn = "MyConnectionString";

public static DataTable GetRooms(int roomID)
{
Database db = DatabaseFactory.CreateDatabase(DBConn);
DbCommand cmd = db.GetStoredProcCommand("GetRooms");
db.AddInParameter(cmd, "@RoomID", DbType.Int32, roomID);

DataSet ds = db.ExecuteDataSet(cmd);
return (ds.Tables[0]);
}

Much simpler code. What’s happing here is that we have a connection string called MyConnectionString, and we use it create a Database object. We then give the database object the name of the stored procedure. We give it the parameters we want to pass, then we execute it. This returns a DataSet to us. From this DataSet, we grab the first table (ds.Table[0]), and return this table.

To me, this is a much easier way to make the database call, much easier than building the SqlConnection and SqlCommand, and doing all that in the correct order.

Also note that because we’re using an integer parameter to pass the roomID into the stored procedure, we’ve pretty much eliminated the possibility of this sql command being successfully targeted by a SQL injection attack.  See SQL Injection Prevention: Stored Procedures

Advertisements

1 comment so far

  1. […] Posted August 6, 2008 Filed under: .NET, Development, SQL | In my last post, SQL Calls from ASP.NET, I talked about how to make a call to a stored procedure to query a database. But what do you do […]


Comments are closed.

%d bloggers like this: