Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

Using C# SQLCommand and SQLDataReader in ASP.Net Web Pages

 

1. Introduction to SQLCommand and SQLDataReader

Almost every business web application makes use of databases to store and retrieve data. ADO.net is the next generation to the ADO (ActiveX data Objects). Dotnet provides SQLCommand to store the SQL statements which can be executed against the SQL Server database. We can also execute stored procedure through this class. The SQLDataReader iterates through the result set returned from the SQL Server. The iterator will not move backward while iterating the records.

In this example, we will fetch some data from the employee table of the NorthWnd database using SQLCommand  and SQLDataReader object and then show result in a sample web page.  The below picture tells how we will pull data from SQL Server to our Web Page:

Retrieve Data Through SQLCommand and SQLDataReader

Retrieve Data Through SQLCommand and SQLDataReader

2. About This Example

Look at the screenshot of the Example below:

Our Example showing Data Taken From SQL Server

Our Example showing Data Taken From SQL Server

Our Example in the ‘Internet Explorer’ looks like the above one. When our Example Web Page loads, it contacts NorthWnd database in the SQL Server and retrieves the data. Then it displays the data on the label. The above picture shows the Label in yellow color.

3. Connection String & Web Configuration File

We know that the web page will connect to an SQL Server Database and pull data from the Employee table. First, we need to connect to the database. In our example, we will connect to SQLServer’s NorthWnd database to pull the data through SQLDataReader and SQLCommand.

A ‘Text String’ which tells the code how to connect to a database is called ‘Connection String’. This string tells the code which driver to use, where the database is located and how to authenticate. The site in this link is all about connection string: connection string. One can bookmark it for a reference. In ASP.Net, website developers keep all the Web Configuration in an ‘XML File’. This file is named as ‘Web.config’. We will keep connection string in this web.config file.

3.1 Web Configuration File (Web.Config)

After starting a new website, we can see the web configuration file in the Solution Explorer. This is marked in the below picture.

Web.Config in Solution Explorer Pane

Web.Config in Solution Explorer Pane

A Web Configuration file is the one in which we define the web application settings. This will avoid compiling the application for a configuration change. Say, for example, we have a ‘Connection String’ to connect to the Database db1 hosted on server Srv1. When we want to connect to a different database db2 running on a different machine Srv2, all we need to do is change that data in the web.config file and our website keeps working with no code change.

3.2 Connection String in Web.Config

The below picture shows how a connection string is residing in the ‘Web.Config’ file:

Connection String in Web.Config file

Connection String in Web.Config file

In the above picture, the box shows we added a connectionstrings section that spawns the connection settings between the tags Open <> and End </>. Note that we are using the System.Data.SQLClient  as a provider. A provider is a bridge between the application and the database. It is a communication channel acting as translator. In the above connection string, we lay out the Server Name as ‘System’ (My Local machine name). If your database is not on the local machine, mention the name of the computer in the network or IP address of it. NorthWnd is the database we will connect to access the employees table. And finally, the connection string contains the qualified user id ‘sa’ and password ‘rohith’. Now the connection string informs that we will connect to Northwnd database in the Local machine using the database credential ‘sa & rohith’.

3.3 Getting Full Computer Name for Connection String

We can notice that the Connection String is expecting a computer or IP address. To know your system name or the name of the system in the network in which the database is running, right click on the My Computer icon and select properties. From the displayed ‘System Properties’ dialog, one can pick the ‘Full computer name’. The below picture shows this:

Picking Full Computer Name

Picking Full Computer Name

4. ASP Page Design

Once the connection string is specified in the web configuration file, we change the default aspx file name to ‘datareader.aspx’. Then, in the design view of the page, we add three labels. The below picture shows the Asp.Net Form Design:

Page Design of DataReader ASP Example

Page Design of DataReader ASP Example

To change the label name, go to the source (Highlighted as red in the above picture) and change the ID for the control. Sometimes changing the ID in the property window of the control will not reflect back. Hence, it is a good idea to change the HTML source of the page. The below video shows this:

Now let us implement our example and learn SQLDataReader and SQLCommand. Note, when the page loads, we will connect to the SQL Server database and will retrieve some data from the employees table of the NorthWnd database.

5. Web Configuration File

We already had a 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. Reading Connection String From Web.Config File

First, we handle the form load event. Here, 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 reading data from the web configuration file.

By default, the namespace System.Configuration is included in our source file. If not, we can add it using the statement as shown below:

using System.Configuration;

Dotnet provides ConfigurationManager class to read the settings from the ‘web.config’ file. We fetched the connection string settings from the ‘ConnectionStrings’ member and passed ‘SQLServerDatabase’ which is the name we gave for Connection String. Finally, the string format of the connection string is stored in the ConnString. Note, we can have multiple connection strings between <connectionStrings> tag. Using the ‘name’ attribute, we can retrieve a specific connection string.

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

7. Creating SqlConnection Object

The SqlConnection object knows where is our database and how to access it. So, it can be created by feeding the ‘connectionstring’. 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. Create SqlCommand Object

The SqlCommand object will say what we want to do with the database. It can hold SQL Statement and connection. The statement tells what operation we want to perform on the database. The connection tells where to perform that operation. In the below piece of code, we store a table select query in a string. While creating the SqlCommand object, we pass this query string along with a connection object. Now SqlCommand object is ready to retrieve some fields from the Employee table of the Northwnd database.

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

9. Read Query Result Through SqlDataReader

OK. Through SqlCommand we can execute the queries on the database. When the SQL statement is a Select statement, then the database will give back one or more rows of information. Where do we store that information? In our example, we are making use the SqlDataReader object to collect that table of information.

The SqlDataReader is forward-only. Means, we can read the information only once and move forward. This tells us that once we read something, we must store it in a local variable, as we cannot read it again. Below is the piece of code, which first executes the query through SqlCommand object, gets the resultant record collection in the reader of type SqlDataReader and then iterates through the reader to get the required information.

//DRead 006: Open the connection and get result in SQLDataReader
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;

}

In the iteration body, we format a string to show a result in readable format. Note how we retrieve the column value from the SqlDataReader object which contains all the queried column values. To refer a particular column, you can use the column name or the index. For Example, reader[“FirstName”] states that we want to get the value for the column FirstName. Also note, in every iteration we add a break using html </br>  tag. This will allow the ASP label to show the data in a separate row.

Download the Example from Google Drive: Link

Categories: Asp

Tags: , , , ,

Do you like this Example? Share your thoughts!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.