Using The SQLCommand And SQLDataReader C#

Download Files:

 

1. Introduction

 

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

 

Look at the following illustration of 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 one above. When the sample loads the page, it contacts the SQL Server NorthWind database and retrieves the data to display the label shown in yellow color. 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 thing every database programmer should do is to specify a way to connect to the database. In our sample we are going to connect to the 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 web site you can see the web configuration file in the Solution Explorer. This is marked in the following picture.

 


 

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 web site keep 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 in 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 admin user) user id sa and password rohith. Now the connection string specifies that you are going to connect to the Northwnd database in the Local machine System using the database credential sa&rohith.

 

To know your system name or name of the system in the network in which the database is 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 following video.

Video : Link

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 have already looked 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 following 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 ConfigurationManagerusing 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 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 needs. The following is the piece of code that 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 a SQL Statement that needs to be executed in the database as well as connection information to know where it should be executed. In the following 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 a command object is sufficient 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 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 isforwardonly and that means you can read the information only once and move forward. This tells you that once you read something store it in your local variable, as you cannot read it again. The following 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 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 of 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.

Advertisements

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