Introduction to some basic classes of ADO.NET

• Download source files - 21.0 Kb

Introduction

The first day I began to study ADO.NET I got confused because of new classes that Microsoft introduce in .NET Framework. In this article I want to introduce some basic classes of ADO.Net, hopefully those beginner who want to learn ADO.NET will not get confused like I did.

Classes and Objects Overview in ADO.NET

We can devide the ADO.NET classes into provider and consumer objects. Provider objects are each type of data source,the actual reading and writing to and from data source is done with the provider-specific objects.Consumer objects are tools that we use to access and manipulate the data after we have read it into memory.The consumer objects work in disconnected mode.The provider objects need an active connection;we use these first to read the data,then,depending on our needs,we can work with the data in memory using the consumer objects and/or update the data in the data source using the provider objects to write the changes back to the data source.

Provider Objects

There are the objects define in each .NET data provider.The name are prefaced with a name unique to the provider.For example,the actual connection object for OLE DB provider is OleDBConnection;(this use for MS Access,Oracle...).Provider for SQLServer is SqlConnection,and the ODBC .NET provider connection class is OdbcConnection.

Connection Object

I named them in the previous paragragh,this object is the first object that we have to use and needed before using any other ADO.NET objects.Obviously,it makes connection string to the data source.

OleDBConnection myConnection = 
       new OleDBConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" +
                           @"Data Source=C:\Test.MDB");
	
// open connection
myConnection.Open();

Or this one:

SqlConnection myConnection = new SqlConnection("Data Source=(local);" +
                                               "Integrated Security=SSPI;"+ 
                                               "Initial Catalog=Test");

// Open connection
myConnection.Open();

Command Object

We use this object to give command such as a SQL query to a data source,for example SELECT * FROM TABLE. The provider specific names are SqlCommand and OleDBCommand.

SqlCommand myCommand = myConnection.CreateCommand();
//this code is from MSDN with a little change
myCommand.CommandText = "select count(*) as NumberOfRegions from region";
Int count = (int) myCommand.ExecuteScalar();

ExecuteScalar() return first column of the first row in the resultset.For more information about this method you can check MSDN.

CommandBuilder Object

This object is used to build SQL commands for data modification from objects based on a single table query. Provider names are SqlCommandBuilder and OleDBCommandBuilder.I'll give you an example soon.

DataReader Object

This is fast and simple object to use which reads a forward-only read-only stream of data from data source.This object gives the maximum performance for simply reading data.The providers name are SqlDataReader for SQLServer and OleDBDataReader for OLE DB.Imagine these line of code in a console application:

// create connection object for Microsoft Access OLE DB Provider
OleDbConnection myConnection
         = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + 
                               @"Data Source=C:\Test.MDB");

// open connection object
myConnection.Open();

// create SQL command object on this connection 
OleDbCommand myCommand = myConnection.CreateCommand();

// initialize SQL SELECT command to retrieve desired data
myCommand.CommandText = "SELECT Column1 FROM Table1";

// create a DataReader object based on previously defined command object
OleDbDataReader myReader = myCommand.ExecuteReader();

while (myReader.Read())
{
	Console.WriteLine("{0}",  myReader["Column1"]);
}
myReader.Close();
myConnection.Close();

The output of this code is all rows of column1 from table1.Don't forget to close both Connection and DataReader at the end of your operations.

DataAdapter Object

This class is for general purpose.It can performs lots of operation to the data source,like upadting changed data and another operation. The providers name are SqlDataAdapter for SQLServer and OledbDataAdapter for OLE DB.

OleDbConnection myConnection
          = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" +
                                @"Data Source=C:\Test.MDB");
myConnection.Open();

OleDbDataAdapter myAdapter
         = new OleDbDataAdapter("SELECT Column1 from Table1", myConnection);
Console.WriteLine("{0}\n",myAdapter.SelectCommand.CommandText);

OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdapter);
OleDbCommand insertCommand = myBuilder.GetInsertCommand();
Console.WriteLine("{0}\n",insertCommand.CommandText);

The output is so wonderful:

SELECT Column1 from  Table1
INSERT INTO 'Column1' ('Column1') VALUES (?)

Nice,I like it!

Consumer Objects

DataSet Object

It represents a set of related tables refrenced as one unit in your application.For example Table1,Table2 and Table3 might all be tables in one DataSet.With this object you can get all the data in each table quickly,examine,change it while disconnected from server,and then update the server with the changes in one efficient operation.

// Create DataAdapter object for update and other operations
SqlDataAdapter myAdapter
          = new SqlDataAdapter("SELECT * FROM Table1", myConnection);

// Create DataSet to contain related data tables, rows, and columns
DataSet myDataSet = new DataSet();

// Fill DataSet using query defined previously for DataAdapter
myAdapter.Fill(myDataSet, "Table1");

// Show data before change
Console.WriteLine("Record before change: {0}",
                  myDataSet.Tables["Table1"].Rows[3]["Column1"]);

// Change data in Table1, row 3, Column1
myDataSet.Tables["Table1"].Rows[3]["Column1"] = "Hello";

// Show data after change
Console.WriteLine("Record after change: {0}",
                  myDataSet.Tables["Table1"].Rows[3]["Column1"]);

// Call Update command to mark change in table
myAdapter.Update(myDataSet, "Table1");

Don't forget, you have to use Fill() method for your DataSet class to do operation in it.

Another Objects

There are some other simple objects,DataTable,DataColumn,DataRow and it is very clear what they are. This codes add new row to data source.

DataRow myRow = myDataSet.Tables["Table1"].NewRow();
myRow["Column1"] = "Hi";
myRow["Column2"] = "How do you do?";
myDataSet.Tables["Table1"].Rows.Add(myRow);

myAdapter.Update(myDataSet, "Table1");

When you are writing codes and using SQLServer or MSAccess you can be sure there are only prefix names and also connection string are different,but of course there are lots of difference between their performance.

In the demo project there is another example and I put some notes there.