//
you're reading...
ASP.NET, IT-Center, VB.NET, Web Based

ASP.NET C# SqlHelper & Retrieving Row Using Loop Dataset

Introduction

In an earlier article of mine, An Introduction and Overview of the Microsoft Application Blocks, we outlined the purpose and benefits of Microsoft Application Blocks. Application Blocks encapsulate two common patterns encountered in application programming, data access and exception management. Application Blocks provide value by making our programming more efficient and our programs more maintainable. They speed the application development process by “wrapping” common tasks. In this article we will examine code samples that illustrate the advantages of using the Data Access Application Block.

Recall that in the first article, we outlined the steps to install Application Blocks on your system. For completeness, let’s quickly review the basic procedure:

  1. Download the Application Block installer package and install the Application Block (the URL for the Data Access Application Block ishttp://download.microsoft.com/download/VisualStudioNET/daabref/RTM/NT5/EN-US/DataAccessApplicationBlock.msi),
  2. Navigate to the “Microsoft Application Blocks for .NET” item from Start Menu -> Programs Files and select the language of your choice (C# or VB).
  3. The Application Block Project will open in Visual Studio. Build the project.
  4. Once the assembly has been built, add a reference to Microsoft.ApplicationBlocks.Data.dll to your project and add using (C#) or Imports (VB) statements for theMicrosoft.ApplicationBlocks.DataSystem.Data, and System.Data.SqlClient namespaces.

That’s it! Once you’ve followed these steps you should be ready to begin using the Data Access Application Block.

Using the Data Access Application Block to Execute SQL Statements

Once you have the references set and the correct using or Imports statements in your class files, you will have access to the Data Access Application Blocks SqlHelper class. The SqlHelper class contains static methods that facilitate the execution of common data access tasks, including:

  • Calling stored procedures or SQL text commands,
  • Specifying parameter details, and
  • Returning SqlDataReader, DataSet, XmlReader objects, or single values.

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 withoutusing 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. As Scott Mitchell shared in an earlier article of his (The Low-Down on #includes):

Axiom 1, of Scott’s Axioms of Programming:

Writing more code leads to more bugs, which leads to a longer development cycle, which leads to higher costs. Therefore, a minimalistic approach should be taken to writing code.

Using the Data Access Application Block to Execute Stored Procedures

The 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();

To execute a stored procedure and return a SqlDataReader, we call the same static ExecuteReader() method but this time the function signature is different. Having a single function name with many different parameter forms to call it is known as method overloading. Overloading enables us to use the same SqlHelper class method but pass different parameters. To call a stored procedure, instead of passing the SQL statement, I send the name of the stored procedure and a SqlParameter object.

It’s important to note that there’s nothing magical about the ExecuteReader() method (or any other method in the Data Access Application Block). All Microsoft has done is create a wrapper that removes us from the details of creating the objects required to return a SqlDataReader. If you examine the Application Block’s code, (which I encourage you to do so), you would see the same familiar objects we have used for database access in the past. Now, we simply don’t have to worry about creating and maintaining them.

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();

Through the usage of the Application Block, we reduced the lines of code from 12 to 4! Once again, the reason for the simplified code is that the ExecuteDataset() method has all the necessary logic to create and manage the required objects to execute the stored procedure and return the DataSet.

Conclusion

Using the Data Access Application Block will speed your application development by encapsulating the code required to execute stored procedures or SQL text commands, specify parameters and return SqlDataReader, DataSet, XmlReader objects. Because the Data Access Block was developed using the latest best practices, you can be confident that your code will be efficient and scalable.

Using the Data Access Application Block frees us from the tedious details of data access and allows us to concentrate on the important business logic in our applications. It also reduces the amount of code we must write, which leads not only to quicker development times, but reduced errors, bugs, and typos as well.

Happy Programming!

  • By John Jakovich : http://www.4guysfromrolla.com/articles/070203-1.aspx
    _________________________________________________________________________

    Loop through a DataSet

    This is a very common question on the ASP.NET Forum.

    I decided to provide the following solution. I will create a DataTable, create 2 DataColumns (IntergerValue and StringValue), then add 5 rows to the DataTable.

    After that, I will add the DataTable dt to the collection of Tables of the DataSet ds.

    Finally, I will loop though the rows of the table inside the DataSet and print out each row by row.

    In real-life, the DataSet ds can be filled with the SqlDataAdapter by executing a query against the DataBase.

    For simplicity, I will create a simple DataTable and add it to the DataSet. The code is as follows:

    // Create new DataTable
    DataTable dt = new DataTable();
    // Create new DataSet
    DataSet ds = new DataSet();
    // Create new DataRow
    DataRow dr;
    // Define the columns of the table.
    dt.Columns.Add(new DataColumn("IntegerValue", typeof(Int32)));
    dt.Columns.Add(new DataColumn("StringValue", typeof(string)));
    // Populate the table with sample values.
    for (int i = 0; i < 5; i++)
    {
     dr = dt.NewRow();
     dr[0] = i;
     dr[1] = "Item " + i.ToString();
     dt.Rows.Add(dr);
    }
    // Add the above DataTable to the collection of tables in the DataSet
    ds.Tables.Add(dt);
    // Loop through the rows of the only table in the DataSet
    foreach( DataRow dataRow in ds.Tables[0].Rows)
    {
     Response.Write("Integer Value : " + dataRow["IntegerValue"].ToString() + "<br>");
     Response.Write("String Value : " + dataRow["StringValue"].ToString() + "<br>");
    }

Other example how to Loop dataset :

using System;
using System.Data;
using System.Data.SqlClient;

    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT FirstName, LastName FROM Person.Contact";

            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable( );
            da.Fill(dt);

            foreach (DataRow row in dt.Rows)
                Console.WriteLine("{0} {1}", row[0], row["LastName"]);

            DataSet ds = new DataSet( );
            da.Fill(ds, "Contact");

            Console.WriteLine(ds.Tables.Count);
            Console.WriteLine(ds.Tables[0].TableName);
            foreach (DataRow row in ds.Tables["Contact"].Rows)
                Console.WriteLine("{0} {1}", row[0], row[1]);
        }
    }

Other Sample :

For Loop in C# Wanted to post a for loop in a dataset, I will post the dataset function as well...

foreach (DataRow dr in ds.Tables[0].Rows)
            {
                //do something with the data
                if (dr["Field1"] = 1)
                {
                      //do something
                }
                else if (dr["Field2"] = 1)
                {
                      //do something
                }
                else if (dr["Field3"] = 1)
                {
                      //do something
                }

            }

See Also :
– http://www.exforsys.com/tutorials/asp.net/managing-data-with-ado.net-datasets-and-csharp.html
– http://www.codersource.net/asp-net/application-blocks/using-microsoft-data-access-application-block.aspx 

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: