ADO.NET – DataReader(Connected) and DataSet(Disconnected) in C# .Net with Examples

ADO.NET provides a relatively common way to interact with data sources, but comes in different sets of libraries for each way you can talk to a data source. 

Core Objects of .NET Framework Data Providers

The following table outlines the four core objects that make up a .NET Framework data provider.

Object

Description

Connection Establishes a connection to a specific data source. The base class for all Connection objects is the DbConnection class.
Command Executes a command against a data source. Exposes Parameters and can execute within the scope of a Transaction from a Connection. The base class for all Command objects is the DbCommand class.
DataReader Reads a forward-only, read-only stream of data from a data source. The base class for all DataReader objects is the DbDataReader class.
DataAdapter Populates a DataSet and resolves updates with the data source. The base class for all DataAdapter objects is the DbDataAdapter class.

 

In addition to the core classes listed in the table above, a .NET Framework data provider also contains the classes listed in the following table.

Object

Description

Transaction Enables you to enlist commands in transactions at the data source. The base class for all Transaction objects is the DbTransaction class.
Parameter Defines input, output, and return value parameters for commands and stored procedures. The base class for all Parameter objects is the DbParameter class.

 

SqlConnection Object

The first thing you will need to do when interacting with a data base is to create a connection.  The connection tells the rest of the ADO.NET code which data base it is talking to.

Creating a SqlConnection Object:

 SqlConnection conn = new SqlConnection(“Server=sadi;database=test;uid=Tiger;pwd=lion);  

Table below describes common parts of a connection string.

table 1.  ADO.NET Connection Strings contain certain key/value pairs for specifying how to make a data base connection.  They include the location, name of the database, and security credentials.

 

Connection String Parameter Name

Description

Data Source

Identifies the server.  Could be local machine, machine domain name, or IP Address.

Initial Catalog

Data base name.

Integrated Security

Set to SSPI to make connection with user’s Windows login

User ID

Name of user configured in SQL Server.

Password

Password matching SQL Server User ID.

Integrated Security is secure when you are on a single machine doing development.  However, you will often want to specify security based on a SQL Server User ID with permissions set specifically for the application you are using.  The following shows a connection string, using the User ID and Password parameters:

SqlConnection conn = new SqlConnection(“Data Source=DatabaseServer;Initial Catalog=sadi;User ID=YourUserID;Password=YourPassword”);

Notice how the Data Source is set to DatabaseServer to indicate that you can identify a data base located on a different machine, over a LAN, or over the Internet.  Additionally, User ID and Password replace the Integrated Security parameter.

The sequence of operations occurring in the lifetime of a SqlConnection are as follows:

  1. Instantiate the SqlConnection.
  2. Open the connection.
  3. Pass the connection to other ADO.NET objects.
  4. Perform data base operations with the other ADO.NET objects.
  5. Close the connection.

SqlCommand Object

A SqlCommand object allows you to specify what type of interaction you want to perform with a data base.  For example, you can do select, insert, modify, and delete commands on rows of data in a data base table.

SqlDataReader Object

A SqlDataReader is a type that is good for reading data in the most efficient manner possible.  You can *not* use it for writing data.  SqlDataReaders are often described as fast-forward firehose-like streams of data.

You can read from SqlDataReader objects in a forward-only sequential manner.  Once you’ve read some data, you must save it because you will not be able to go back and read it again.

 

Example on the use of these ADO.NET objects…

Getting Data from database :

string sConnectionString = “Server=sadi;database=test;uid=Tiger;pwd=Lion”;

            SqlConnection oConn = new SqlConnection(sConnectionString);

            string sQueryString = “select * from tblUser”;

            SqlCommand oCommand = new SqlCommand(sQueryString);

            oCommand.Connection = oConn;

            oConn.Open();

            SqlDataReader oReader = oCommand.ExecuteReader();

            ArrayList oList = new ArrayList();

            if (oReader.HasRows)

            {

                while (oReader.Read())

                {

                    oList.Add(oReader[0].ToString());

                    oList.Add(oReader[1].ToString());                   

                }

            }

            oReader.Close();

            oConn.Close();

Getting single data from database:

 SqlCommand cmd = new SqlCommand(“select count(*) from Categories”, connection); 
 
int count = (int)cmd.ExecuteScalar();

Inserting data to database:

string conectionstring = “Server=sadi;database=test_db;uid=Tiger;pwd=Lion”;            SqlConnection connection = new SqlConnection(conectionstring);

            string querystring = “insert into customer values(‘”+ Guid.NewGuid() + “‘,'”+ txtCustName.Text+ “‘)”;

            SqlCommand oSqlCommand = new SqlCommand(querystring);

            connection.Open();

            oSqlCommand.Connection = connection;

            oSqlCommand.ExecuteNonQuery();

Working with Disconnected Data – The DataSet and SqlDataAdapter

A DataSet is an in-memory data store that can hold numerous tables.  DataSets only hold data and do not interact with a data source.  It is the SqlDataAdapter that manages connections with the data source and gives us disconnected behavior.  The SqlDataAdapter opens a connection only when required and closes it as soon as it has performed its task.  For example, the SqlDataAdapter performs the following tasks when filling a DataSet with data:

  1. Open connection
  2. Retrieve data into DataSet
  3. Close connection

and performs the following actions when updating data source with DataSet changes:

  1. Open connection
  2. Write changes from DataSet to data source
  3. Close connection

In between the Fill and Update operations, data source connections are closed and you are free to read and write data with the DataSet as you need.  These are the mechanics of working with disconnected data.

Creating a DataSet Object

There isn’t anything special about instantiating a DataSet.  You just create a new instance, just like any other object:

DataSet dsCustomers = new DataSet();

The DataSet constructor doesn’t require parameters.  However there is one overload that accepts a string for the name of the DataSet

Creating A SqlDataAdapter

The SqlDataAdapter holds the SQL commands and connection object for reading and writing data.  You initialize it with a SQL select statement and connection object:

SqlDataAdapter daCustomers = new SqlDataAdapter(
“select CustomerID, CompanyName from Customers”, conn);

As indicated earlier, the SqlDataAdapter contains all of the commands necessary to interact with the data source. 

The Example of DataSet of select query is given below-

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Data;

 

namespace ConsoleApplication1

{

    class Program

    {

        DataSet dataset = new DataSet();

        static void Main(string[] args)

        {

 

            Program p = new Program();

            p.TestRead();

            p.printTest();

            Console.ReadKey();

        }

 

        private void TestRead()

        {

            SqlConnection oConnection = new SqlConnection(“Server=sadi;database=test;uid=Tiger;pwd=Lion”);

 

            try

            {

                oConnection.Open();              

                SqlDataAdapter adapter = new SqlDataAdapter();

                adapter.SelectCommand = new SqlCommand(“select * from tblUser”, oConnection);

                adapter.Fill(dataset,“TEST”);

                oConnection.Close();

               

            }

            catch (SqlException oSqlExp)

            {

                Console.WriteLine(“” + oSqlExp.Message);

            }

            catch (Exception oEx)

            {

                Console.WriteLine(“” + oEx.Message);

            }

            finally

            {

                if (oConnection != null)

                {

                    oConnection.Close();

                }

            }

        }

Advertisements

30 thoughts on “ADO.NET – DataReader(Connected) and DataSet(Disconnected) in C# .Net with Examples

  1. Hi,
    I am new to .net. I dont knw much about disconnected approach..
    I have one query that if we are using disconnected approach and at the same time two users are updating same records, how it will handled by disconnected approach.

  2. Hi Buddy,
    above article is really helped me lot……
    thankyou
    one more favour
    kindly give detail description about DataGrid,DataList operation on these two control and webservices in asp.net with example, if you have any notes are material kindly mail to me
    my mail ID badri.bly@gmail.com

    Thanking you

  3. Hey, thank you soooo mch for this article…
    Helped me a alot !!!!!!
    Can you also post some good examples of using Delegates

  4. I have been exploring for a little bit for any high-quality articles or blog posts on this kind of area . Exploring in Yahoo I at last stumbled upon this site. Reading this info So i am happy to convey that I’ve an incredibly good uncanny feeling I discovered exactly what I needed. I most certainly will make certain to don’t forget this web site and give it a glance on a constant basis.

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