ADO.NET

ADO.NET

ADO.NET is a set of computer software components that programmers can use to access data and data services.

It is a part of the base class library that is included with the Microsoft .NET Framework.

It is commonly used by programmers to access and modify data stored in relational database systems, though it can also access data in non-relational sources.

ADO.NET is sometimes considered an evolution of ActiveX Data Objects (ADO) technology, but was changed so extensively that it can be considered an entirely new product.

ADO.NET is an integral part of the .NET Compact Framework, providing access to relational data, XML documents, and application data. ADO.NET supports a variety of development needs. You can create database-client applications and middle-tier business objects used by applications, tools, languages or Internet browsers.

 ADO.NET Architecture

   ADO.NET NAMESPACE

ADO.Net Classes

1.         I.            The SqlConnection Object

To interact with a database, you must have a connection to it.  The connection helps identify the database server, the database name, user name, password, and other parameters that are required for connecting to the data base.  A connection object is used by command objects so they will know which database to execute the command on.

The SqlClient .NET Data Provider namespace:

using System.Data.SqlClient;

Steps involved in using connection object:

1. Declare a connection string pointing to a database
2. Create an SqlConnection object
3. Pass the connection string to this SqlConnection object.

         SqlConnection con = new SqlConnection(conString);

4.     Open connection when needed and close when finished

         con.Open();    

         con.Close();

1.       II.   The SqlCommand Object

The process of interacting with a database means that you must specify the actions you want to occur.  This is done with a command object.  You use a command object to send SQL statements to the database.  A command object uses a connection object to figure out which database to communicate with.  You can use a command object alone, to execute a command directly, or assign a reference to a command object to an SqlDataAdapter, which holds a set of commands that work on a group of data as described below.

1. Creating a SqlCommand Object

SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

2. Querying Data

// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();

3. Inserting Data

// prepare command string
 string insertString = @”insert into Categories(CategoryName, Description) values (‘value1', ‘value2’);

 // 1. Instantiate a new command with a query and connection
 SqlCommand cmd = new SqlCommand(insertString, conn);

 // 2. Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

4. Updating Data

// prepare command string
 string updateString = @” update Categories set CategoryName = 'Other’ where CategoryName = 'Miscellaneous'";

 // 1. Instantiate a new command with command text only
 SqlCommand cmd = new SqlCommand(updateString);

 // 2. Set the Connection property
 cmd.Connection = conn;

 // 3. Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

5. Deleting Data

// prepare command string

 string deleteString = @”delete from Categories where CategoryName = 'Other'";

 // 1. Instantiate a new command
 SqlCommand cmd = new SqlCommand();

 // 2. Set the CommandText property
 cmd.CommandText = deleteString;

 // 3. Set the Connection property
 cmd.Connection = conn;

 // 4. Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

6. Getting Single values

// 1. Instantiate a new command
 SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);

 // 2. Call ExecuteNonQuery to send command
 int count = (int)cmd.ExecuteScalar();

1.     III. The SqlDataReader Object

Many data operations require that you only get a stream of data for reading.  The data reader object allows you to obtain the results of a SELECT statement from a command object.  For performance reasons, the data returned from a data reader is a fast forward-only stream of data.  This means that you can only pull the data from the stream in a sequential manner.  This is good for speed, but if you need to manipulate data, then a DataSet is a better object to work with.

Steps involved:

1. Creating a SqlDataReader Object

SqlDataReader rdr = cmd.ExecuteReader();

2. Reading Data

SqlDataReader returns data via a sequential stream

The typical method of reading from the data stream returned by the SqlDataReader is to iterate through each row with a while loop.

while (rdr.Read())

 {

// get the results of each column

 string contact = (string)rdr["ContactName"];

 string company = (string)rdr["CompanyName"];

// print out the results

Console.Write("{0,-25}", contact);

Console.Write("{0,-25}", company);

Console.WriteLine();

}

1.     IV. The SqlDataAdapter Object

The data adapter makes it easy for you to accomplish these things by helping to manage data in a disconnected mode. 

The data adapter fills a DataSet object when reading the data and writes in a single batch when persisting changes back to the database. 

A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database. 

Additionally, the data adapter contains command object references for SELECT, INSERT, UPDATE, and DELETE operations on the data. 

 You will have a data adapter defined for each table in a DataSet and it will take care of all communication with the database for you. 

All you need to do is tell the data adapter when to load from or write to the database.

The DataAdapter provides four properties that allow us to control how updates are made to the server:

•       SelectCommand

•       UpdateCommand

•       InsertCommand

•       DeleteCommand

The four properties are set to Command objects that are used when data is manipulated.

The DataAdapter includes three main methods:

•       Fill (populates a DataSet with data).

•       FillSchema (queries the database for schema information that is necessary to update).

•       Update (to change the database, DataAdapter calls the DeleteCommand, the InsertCommand and the UpdateCommand properties).

Note:More about SqlDataAdapter and creation dealt along with SqlDataSet object.

1.       V.  The DataSet Object

DataSet objects are in-memory representations of data.  They contain multiple Datatable objects, which contain columns and rows, just like normal database tables.  You can even define relations between tables to create parent-child relationships.  The DataSet is specifically designed to help manage data in memory and to support disconnected operations on data, when such a scenario make sense.  The DataSet is an object that is used by all of the Data Providers, which is why it does not have a Data Provider specific prefix.

1. Creating a DataSet Object

DataSet ds = new DataSet();

2. Creating A SqlDataAdapter

The SqlDataAdapter holds the SQL commands and connection object for reading and writing data. 

SqlDataAdapter da = new SqlDataAdapter("select CustomerID, CompanyName from Customers", conn);

3. Filling the DataSet

da.Fill(ds, "Customers"); 

4. Using the DataSet

A DataSet will bind with both ASP.NET and Windows forms DataGrids.  Here's an example that assigns the DataSet to a Windows forms DataGrid:

dg.DataSource = ds;
dg.DataMember = "Customers";

5. Updating Changes

da.Update(ds, "Customers");

SAMPLE PROGRAM

using System;

using System.Data;

using System.Data.SqlClient;

class Program

{

static void Main()

   {

                string connectionString = "Data Source=(local);Initial Catalog=Northwind;" + "Integrated          Security=true";

                SqlConnection con=new SqlConnection(connectionString) ;

                String query= “select * from student”;

                Con.Open();

                SqlCommand cmd=new SqlCommand(query,con);

                SqlDataReader dr=cmd.ExecuteReader();

                while(dr.Read())

                     {

                        Console.Writeline(dr[“name”]);

                      }

                dr.Dispose();

                cmd.Dispose();

                con.Close();

     }   }

 CREATE A STORED PROCEDURE

Enterprise Manager provides an easy way to create stored procedures:

Select the database to create the stored procedure on.

Expand the database node, right-click on "Stored Procedures" and select "New Stored Procedure...".

You should see the following: CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS

 Substitute OWNER with "dbo" (database owner) and PROCEDURE NAME with the name of the procedure.

For example:

CREATE PROCEDURE [dbo].[GetProducts] AS SELECT ProductID, ProductName FROM Products

Click on the Check Syntax button in order to confirm that the stored procedure is syntactically correct.

Please note that the GetProducts example above will work on the Northwind sample database that comes with SQL Server. Modify it as necessary to suite the database you are using.

CALLING A STORED PROCEDURE

ADO.NET allows the developer to call a stored procedure in almost the exact same way as a standard SQL statement:

1. Create a new ASP.NET website.
2. From the Toolbox, drag and drop a GridView onto the Form.
3. Double-click on the PAGE to generate the Page_Load event handler. Before entering any code, add

"using System.Data.SqlClient" at the top of the file.

Enter the following code:

protected void Page_Load(object sender, EventArgs e)

 {

SqlConnection conn = new SqlConnection("Data Source=localhost;Database=Northwind;Integrated Security=SSPI");

SqlCommand command = new SqlCommand("GetProducts", conn);

command.CommandType=CommandType.StoredProcedure;

SqlDataAdapter adapter = new SqlDataAdapter();

adapter.SelectCommand=command;

 DataSet ds = new DataSet();

adapter.Fill(ds, "Products");

GridView1.DataSource = ds.Tables[“Products”];

DataBind();

 }

Posted in: ado.net,ado.net basics,ado.net beginners tutorial,ado.net tutorial