SqlHelper Example 2

Lets take a look at a more dramatic example of the benefits of using the Data Access Application Block. For this example, we will retrieve a DataSet containing the results from a stored procedure (getProductsByCategory) that takes a single parameter (CategoryID). Again, to illustrate the amount of code saved by using the Data Access Application Block, let's first look at the necessary code when not using the Data Access Block:

// Open a connection to Northwind
SqlConnection objConn = new
SqlConnection("Server=(local);Database=Northwind;Integrated Security=True;");
objConn.Open();

//Create the stored procedure command object
SqlCommand objCmd = new SqlCommand("getProductsByCategory", objConn);
objCmd.CommandType = CommandType.StoredProcedure;
//create the parameter object for the stored procedure parameter
objCmd.Parameters.Add("@CategoryID", SqlDbType.Int);
objCmd.Parameters["@CategoryID"].Value = 1;

//create our DataAdapter and DataSet objects
SqlDataAdapter objDA = new SqlDataAdapter(objCmd);
DataSet objDS = new DataSet("Category_Results");

//fill the dataset
objDA.Fill(objDS);

//databind the datagrid
DataGrid1.DataSource = objDS;
DataGrid1.DataBind();

//close connection
objConn.Close();
Now, we will call the same stored procedure and return a DataSet using the SqlHelper class's ExecuteDataset() method:

string strConn = "Server=(local);Database=Northwind;Integrated Security=True;";
DataSet objDS = SqlHelper.ExecuteDataset(strConn, CommandType.StoredProcedure,
"getProductsByCategory", new SqlParameter("@CategoryID", 1) );

DataGrid2.DataSource = objDS;
DataGrid2.DataBind();