Click here to Skip to main content
12,502,009 members (49,785 online)
Click here to Skip to main content
Add your own
alternative version

Stats

232.9K views
1.6K downloads
76 bookmarked
Posted

Introduction to some basic classes of ADO.NET

, 21 Feb 2002
Rate this:
Please Sign up or sign in to vote.
If you want to begin ADO.NET,start it from here
<!-- Download Links --> <!-- Article image -->

Sample Image - ADODotNet.jpg

<!-- Add the rest of your HTML here -->

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Mazdak
Web Developer
Iran (Islamic Republic of) Iran (Islamic Republic of)
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralMy vote of 4 Pin
D-Kishore4-Sep-12 18:35
memberD-Kishore4-Sep-12 18:35 
Generalcreate a class for ADO.NET in C# Pin
Member 449634210-Mar-08 18:44
memberMember 449634210-Mar-08 18:44 
GeneralRe: create a class for ADO.NET in C# Pin
Luis Ramirez2-Apr-08 10:56
memberLuis Ramirez2-Apr-08 10:56 
GeneralFinally, a decent tutorial Pin
ARCH-ViLE8-Sep-05 3:25
memberARCH-ViLE8-Sep-05 3:25 
GeneralUpdate requires a valid UpdateCommand Pin
WesleySnipes21-Aug-05 10:02
memberWesleySnipes21-Aug-05 10:02 
GeneralProblem With Update Pin
Partha Pratim Dutta29-Sep-04 2:35
memberPartha Pratim Dutta29-Sep-04 2:35 
GeneralRe: Problem With Update Pin
bestsimo22-May-05 0:26
memberbestsimo22-May-05 0:26 
GeneralProblems with Update Pin
norbland27-Feb-04 23:10
membernorbland27-Feb-04 23:10 
GeneralRe: Problems with Update Pin
Mazdak28-Feb-04 4:44
memberMazdak28-Feb-04 4:44 
GeneralRe: Problems with Update Pin
norbland28-Feb-04 5:24
membernorbland28-Feb-04 5:24 
Generalms access table view in dataset Pin
lebogang22-Oct-03 0:15
memberlebogang22-Oct-03 0:15 
GeneralA suggestion... Pin
Marc Clifton21-Jun-02 2:25
memberMarc Clifton21-Jun-02 2:25 
GeneralRe: A suggestion... Pin
Mazdak21-Jun-02 8:10
memberMazdak21-Jun-02 8:10 
GeneralRe: A suggestion... Pin
Marc Clifton21-Jun-02 10:16
memberMarc Clifton21-Jun-02 10:16 
GeneralPlease stop rehashing the same old examples Pin
Anonymous26-Feb-02 7:40
memberAnonymous26-Feb-02 7:40 
GeneralRe: Please stop rehashing the same old examples Pin
Agyklon11-Dec-02 4:39
memberAgyklon11-Dec-02 4:39 
Generaldisconnected Pin
Rick Crone26-Feb-02 4:20
memberRick Crone26-Feb-02 4:20 
GeneralRe: disconnected Pin
Agyklon11-Dec-02 4:41
memberAgyklon11-Dec-02 4:41 
GeneralRe: disconnected Pin
Anonymous23-Mar-03 22:08
sussAnonymous23-Mar-03 22:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160919.1 | Last Updated 22 Feb 2002
Article Copyright 2002 by Mazdak
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid