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
2. About This Example
Look at the screenshot of the Example below:

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
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
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
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
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:
1 2 3 4 5 6 7 8 9 |
<!-- 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:
1 |
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.
1 2 3 4 5 6 |
//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:
1 2 |
//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.
1 2 3 |
//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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
//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
- 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