SqlHelper examples and advantages

In order to illustrate the advantage of using the Data Access Block, let's take a look at sample code that creates a SqlDataReader object and binds it to a DataGrid without using the Data Access Block. In general, returning a DataReader involves establishing a connection, creating a SqlCommand, and executing the command against the database. The resulting SqlDataReader object can then be bound to a DataGrid:

//create the connection string and sql to be executed
string strConnTxt = "Server=(local);Database=Northwind;Integrated Security=True;";
string strSql = "select * from products where categoryid = 1";

//create and open the connection object
SqlConnection objConn = new SqlConnection(strConnTxt);
objConn.Open();

//Create the command object
SqlCommand objCmd = new SqlCommand(strSql, objConn);
objCmd.CommandType = CommandType.Text;

//databind the datagrid by calling the ExecuteReader() method
DataGrid1.DataSource = objCmd.ExecuteReader();
DataGrid1.DataBind();

//close the connection
objConn.Close();

Now lets look at the same task using the SqlHelper class's static ExecuteReader() method:

//create the connection string and sql to be executed
string strSql = "select * from products where categoryid = 1";
string strConnTxt = "Server=(local);Database=Northwind;Integrated Security=True;";

DataGrid4.DataSource = SqlHelper.ExecuteReader(strConnTxt, CommandType.Text, strSql);
DataGrid4.DataBind();

As you can see, there is considerably less code in the second example. To execute a SQL statement and return a SqlDataReader, the ExecuteReader() method requires only the connection string, command type and SQL to be executed. The SqlHelper class contains all of the "plumbing" necessary to establish a connection, create a SqlCommand and execute the command against the database with a single static method call.
The main advantage of the Application Blocks is that they greatly reduce the amount of code you need to write by encapsulating common tasks in a wrapper class. While at first glance this may not seem that profound of a benefit, realize that writing less code means more than just shorter time needed to write the code. It also means fewer bugs and typos, and an overall lower total cost to produce the software.

Using the Data Access Application Block to Execute Stored ProceduresThe ExecuteReader() method also has several overloads that enable you to perform stored procedures and transactions. Lets take a quick look at the same method, but this time we'll execute a stored procedure:

DataGrid5.DataSource = SqlHelper.ExecuteReader(strConnTxt, CommandType.StoredProcedure,
"getProductsByCategory", new SqlParameter("@CategoryID", 1));
DataGrid5.DataBind();