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:

2. About This Example

Look at the screenshot of the Example below:

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.

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:

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:

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:

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:

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:

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.

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:

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.

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.

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

• Bulk Insert and Temp Tables – Learn With Examples
• System Tray Balloon Tooltip Using C# NotifyIcon Control

Categories: Asp 2.0

Tags: ConfigurationManager, ConnectionStringSettings, SqlCommand, SqlConnection, SqlDataReader

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