ADO.NET : Connected & Disconnected Environment!

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 Connectionobjects 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 allDataReader objects is the DbDataReader class.
DataAdapter	Populates a DataSet and resolves updates with the data source. The base class for allDataAdapter 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 allTransaction 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( ConfigurationManager.ConnectionStrings["NewGoMahadConnectionString"].ConnectionString );  

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:

web.config Code:

<configuration>
    <appSettings/>
    <connectionStrings>
  <remove name="LocalSqlServer" />
  <add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" />
  <add name="NewGoMahadConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\gomahad.mdf;Initial Catalog=gomahad;Integrated Security=True;User Instance=false" />
  <add name="gomahadConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\gomahad.mdf;Initial Catalog=gomahad;Integrated Security=True;User Instance=false" />
  <add name="gomahadConnectionStringSearch" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\gomahad.mdf;Initial Catalog=gomahad;Integrated Security=True;User Instance=false"
   providerName="System.Data.SqlClient" />
  <add name="gomahadConnectionStringCourseSearch" connectionString="Data Source=MAHAD-PC;Initial Catalog=gomahad;Integrated Security=True"
   providerName="System.Data.SqlClient" />
 </connectionStrings>

SqlConnection conn = new SqlConnection(“Data Source=Karachi;Initial Catalog=gomahad;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 = ConfigurationManager.ConnectionStrings["NewGoMahadConnectionString"].ConnectionString;

            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 = ConfigurationManager.ConnectionStrings["NewGoMahadConnectionString"].ConnectionString ;
            SqlConnection connection = newSqlConnection(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(ConfigurationManager.ConnectionStrings["NewGoMahadConnectionString"].ConnectionString);

            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();

                }

            }

        }

Posted in: ADO.NET,ASP.NET,How To?