Tuesday, December 27, 2011

ASP.Net - Using SQLDataReader and SQLCommand

1. Introduction

Almost every business web application makes use of databases to store and retrieve data. ADO.net is next generation to the ADO (Activex data Objects). In this article, we will retrieve some column data from the employee table of the NorthWnd database.

Look at the below illustration on how we are going to take data from the database.



2. About the Sample

Look at the screenshot of the sample below:



Our sample in the explorer looks like the above one. When the sample loads the page, it contacts the SQL Server NorthWnd database and retrieves the data to display the label shown in yellow colour. There is nothing more to specify here. Let us move to the next section.

3. Connection String in Web Configuration

We know that the web page is going to connect to a database and pull data from the employee table. The first thigh every database programmer should do is specifying a way to connect to the database. In our sample, we are going to connect to SQLServer database NorthWnd to pull some information from the employee table.

The Connection object tells the application how to connect to the database. Using the methods or constructor of the Connection object one can specify the information required to connect the database with your application. Here, we are going to use the connection string and we will place that constant string as part of the web configuration file.

Once you start a new website you can see the web configuration file in the Solution Explorer. This is marked in the below picture.



A web configuration file is the one in which you specify all the web application-related settings. Doing so will avoid the further builds to your application. Say for example you have the connection string to connect to the Database db1 at server Srv1. When you want to connect to a different database db2 on a different machine Srv2, all you need to do is change that information in the configuration file and your website keeps working without any code change.

The changed portion of the web configuration file is shown below:



The box shows that we added a connectionstrings section that spawns the information between the tags Open <> and End </>. Note that we are using the System.Data.SQLClient provider. A provider is a bridge between your application and the database and they are the communication channel acting as translator. In the above connection string, I specified the Server Name as System (My Local machine name). If your database is not on the local machine you should specify the name of the computer in the network or IP address of the machine name. NorthWnd is the database I am going to connect on my local machine for this example to access the employees table. And finally, the connection string contains the qualified (of course it is. As sa is an admin user) user id sa and password rohith. Now the connection string informs that you are going to connect to Northwnd database in the Local machine System using the database credential sa&rohith.

To know your system name or the name of the system in the network in which the database in running, right click on the MyComputer icon and select properties. From the displayed dialog’s second tab pick the Full Computer name. This is shown below:



4. Page Design

Once the connection string is specified in the web configuration file, the default aspx file name is changed to datareader.aspx. Then in the design view of the page three labels are added as shown below:



To change the label name go to the source (Highlighted in red) and change the ID for the control. Sometimes changing the ID in the property window of the control will not reflect back and hence it is good to make the change in the HTML source of the page. This is shown in the below video: 



OK. Now let us move to the Source code of the form. Note when the page load we are going to connect to the SQL Server database and will retrieve some data from the employees table of the NorthWnd database.

5. Source: Config File

We already have look at the configuration file. In this file we specified our connection string to the database which is shown below:

<!-- DRead 001: Open the connectiontring section-->
<connectionStrings>
 <add name="SQLServerDatabase" providerName="System.Data.SqlClient"
 connectionString="Server=SYSTEM;
  Database=NorthWnd;
  User ID=sa;
  Password=rohith;
  Trusted_Connection=False;"/>
</connectionStrings>

6. Source: Reading the configuration

First, the form load event handler is handled and in the form load event handler, we need to connect to the database. To connect to the database we need the connection string. And, we already specified our connection string in the Web Config file. So this situation now leads us to the task of reading the information from the web configuration file.

By default the namespace System.Configuration is included in your source file. If not include it using the statement as shown below:
using System.Configuration;

Then have a look at the below code:

//DRead 003: Get the connection string from the configuration file
ConnectionStringSettings appsettings = 
ConfigurationManager.ConnectionStrings["SQLServerDatabase"];
string ConnString = null;
if (appsettings != null)
    ConnString = appsettings.ConnectionString;

In the above code snippet, we are reading our connection string from ConfigurationManager using the Name SQLServerDatabase from the collection ConnectionStrings, which is the collection of objects of type ConnectionStringSettings.  This value is stored in the appsettings. Note that in the webconfig file we used only one connection string. But you can have multiple connection strings between the tags <connectionStrings>            </connectionStrings>

Finally, the string format of the connection string is stored in the ConnString. Now we know the connection information to the database in the form of a string.

7. Source: Connection Object

The connection object knows where your database and how to access it. So the connection object can be created easily by supplying the connectionstring as it has all the information a connection object need. Below is the piece of code, which creates the connection object in our example:

//DRead 004: Create the Connection to SQL Server
SqlConnection Connection_toSQL = new SqlConnection(ConnString);

8. Source: Command Object

The command object will say what you want to do with the database. It usually contains SQL Statement that needs to be executed in the database and well as connection information to know where it should be executed. In the below piece of code, a table select query is stored in the string. Then supplying the query string and connection object creates a command object. Now command object is capable enough to retrieve some fields from the employee table of the Northwnd database in SQL Server. 

//DRead 005: Form the command object
string Query = "Select FirstName, TitleOfCourtesy, Title from employees";
SqlCommand command = new SqlCommand(Query, Connection_toSQL);

9. Source: DataReader and Iterating through it

OK. The command is capable of executing the SQL statements. When the SQL statement is a Select statement, then the database will give back one more rows of information. Where to we store that information? In our sample (There are other ways too. We will see that in some other article) we are making use the DataReader object to collect that table of information. The datareader is forwardonly and that means you can read the information only once and move forward. This tells you that once you read something to store it in your local variable, as you cannot read it again. Below is the piece of code, which first executes the command object, gets the resultant record collection in the reader of type SqlDataReader and then it iterates through the reader to get the column name queried.

//DRead 006: Open the connection and get result in DataReader
Connection_toSQL.Open();
SqlDataReader reader = command.ExecuteReader();

//DRead 007: Iterate through the reader
while (reader.Read())
{
    string line = "";
    line = string.Format("{0} {1} is Working as {2} </br>",
        reader["TitleOfCourtesy"], reader["FirstName"], reader["Title"]);

    lblResult.Text = lblResult.Text + line;

}

The resultant string is the combination some constant with the data retrieved from the database. And the string assigned to the label with the </br> tag to insert a new line after reading one complete record. Also, note that the column Read method of the X will return a column of columns that forms a complete a row of what is queried. To refer a particular column, you can use the column name or the index. The reader["FirstName"] states that we want to get the value for the column FirstName.


Source Code : Download


Like this site? Tell it to your Firend :)