Retrieve data from an ASP.Net application using Ado.Net 2.0 disconnected model

Sunday, December 19, 2010

This is the second post in a series of posts regarding to ADO.Net 2.0. Have a look at the first post if you like.

In this post I am going to investigate the "Disconnected" model. When I say "Disconnected" I mean Datasets. Datasets are in memory representations of tables in a particular database.

A Dataset contains a Table collection and each Table collection contains a Row collection and each Row collection contains a Columns collection.

So initially you connect to the database, get the data to the dataset, then disconnect and then you can select data from the Dataset and do all CRUD operations.

The Dataset obviously, has some sort of mechanism to keep track of all the changes so when the new connection is established to the database all changes are persisted to the data store.

We can use this model when we have multiple users using the same database and we want to avoid issues like blocking/locking tables while at the same time managing more efficiently resources like connections.

So it is good to know what datasets are and a lot of applications are implemented using datasets. I am not proposing to use Datasets for your main data access methodology but it always useful to know it.

I am going to demonstrate the use of datasets with a hands on example.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008/2005 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Before you start make sure you have included those two lines of code in the Default.aspx.cs file.

using System.Data;
using System.Data.SqlClient;

3) In the Page_Load event handling routine of the Default.aspx page type

SqlConnection myconn = new SqlConnection("Data Source=FOFO-PC\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");

 myconn.Open();

 SqlCommand myCommand = myconn.CreateCommand();
 myCommand.CommandType = CommandType.Text;
 myCommand.CommandText = "SELECT * FROM Customers";

 SqlDataAdapter myDataAdapter = new SqlDataAdapter(myCommand);

 DataSet myDataSet = new DataSet();
 myDataAdapter.Fill(myDataSet);

 myconn.Close();

 foreach (DataRow myRow in myDataSet.Tables[0].Rows)
 {
 Response.Write(myRow["CompanyName"]);
 Response.Write(" ");
 Response.Write(myRow["ContactName"]);
 Response.Write(" ");
 Response.Write(myRow["City"]);
 Response.Write(" ");
 Response.Write("----------------");
 Response.Write(" ");
 }

4) I am using an object (myconn) of type SqlConnection to create the handshake with the database. Please note that you must change this line of code to reflect your sql connection string.

new SqlConnection("Data Source=FOFO-PC\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");

Then I use the Open() method to open that connection.

myconn.Open();

Then I create a command object.

SqlCommand myCommand = myconn.CreateCommand();

Then I use the CommandText property of the comm object to define the T-SQLstatement.Then I use the CommandType property to indicate that I am using T-SQL.

myCommand.CommandText = "SELECT * FROM Customers";
myCommand.CommandType = CommandType.Text;

Then I create a SQLDataAdapter object and pass it as a parameter the Command object.

SqlDataAdapter myDataAdapter = new SqlDataAdapter(myCommand);

Then I create a DataSet object, and use the Fill method of the Adapter object to fill the dataset.

DataSet myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet);

Then I close the connection object.

myconn.Close();

Then I loop through the rows in the datatable of the dataset.

foreach (DataRow myRow in myDataSet.Tables[0].Rows)
 {
 Response.Write(myRow["CompanyName"]);
 Response.Write(" ");
 Response.Write(myRow["ContactName"]);
 Response.Write(" ");
 Response.Write(myRow["City"]);
 Response.Write(" ");
 Response.Write("----------------");
 Response.Write(" ");
 }

5) Run you application and see the results printed out in the screen. Please note that I have closed the connection to the database but was still able to loop through the data, because I am not querying the data but the data in memory.

6) We could write the code above slightly different.Comment out all the code in the Page_Load() event handling routine of the Default.aspx page. In the Page_Load() event handling routine of the Default.aspx page type

SqlConnection myconn = new SqlConnection("Data Source=FOFO-PC\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");

myconn.Open();

        SqlDataAdapter dap = new SqlDataAdapter();

        dap.SelectCommand = myconn.CreateCommand();
        dap.SelectCommand.CommandText = "SELECT * FROM Customers";

DataSet myDataSet = new DataSet();
 dap.Fill(myDataSet);

 myconn.Close();

 foreach (DataRow myRow in myDataSet.Tables[0].Rows)
 {
 Response.Write(myRow["CompanyName"]);
 Response.Write(" ");
 Response.Write(myRow["ContactName"]);
 Response.Write(" ");
 Response.Write(myRow["City"]);
 Response.Write(" ");
 Response.Write("----------------");
 Response.Write(" ");

}

7) Run your application and see the results printed out in the screen. I will explain again what I do in the code.

I am using an object (myconn) of type SqlConnection to create the handshake with the database. Please note that you must change this line of code to reflect your sql connection string.

new SqlConnection("Data Source=FOFO-PC\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");

Then I use the Open() method to open that connection.

I am creating a new instance of SQLDataAdapter object.Then I use the SelectCommand property to set the Transact-SQL statement used to select records in the data source. Then I use the CommandText property to

set the Transact-SQL statement to execute at the data source.

        SqlDataAdapter dap = new SqlDataAdapter();

        dap.SelectCommand = myconn.CreateCommand();
        dap.SelectCommand.CommandText = "SELECT * FROM Customers";

Then I create a DataSet object, and use the Fill method of the Adapter object to fill the dataset.

DataSet myDataSet = new DataSet();
dap.Fill(myDataSet);

Then I close the connection object.

myconn.Close();

Then I loop through the rows in the datatable of the dataset.

foreach (DataRow myRow in myDataSet.Tables[0].Rows)
 {
 Response.Write(myRow["CompanyName"]);
 Response.Write(" ");
 Response.Write(myRow["ContactName"]);
 Response.Write(" ");
 Response.Write(myRow["City"]);
 Response.Write(" ");
 Response.Write("----------------");
 Response.Write(" ");

}

I will have more posts on inserts,updates and deletes using both the connected and disconnected model.

Email me if you need the source code. Hope it helps!!!

DotNetStories

Retrieve data from an ASP.Net application using Ado.Net 2.0 disconnected model

1 Comment