ADO.NET Tutorial for Beginners

ado.net tutorialADO.NET, an important feature of the .NET framework, is a set of object-oriented libraries used to provide consistent access to data sources such Microsoft SQL server, Access, text files, Excel spreadsheets and XML. ADO.NET classes reside in a namespace ‘System.Data’ and its implementation with XML is in ‘System.Xml’. Most of the data-centric applications use the ADO.NET to connect to their data sources and retrieve, manage and update the data. This ADO.NET tutorial covers a basic introduction of  two main components of ADO.NET classes, which are .NET framework ‘Data Providers’ (for connecting to database, executing commands, retrieve results, etc.) and ‘DataSet’ (disconnected cached data).

New to C#? Check out a Udemy.com course.

ADO.NET .NET Framework Data Providers

For connecting to the different data sources and interacting with data, a relatively common managed interface is available in ADO.NET data provider model. A single set of classes is not able to accomplish access to all of the data sources since each of a data source has special interface, exposed to a separate protocol and require a different set of rules to connect and interact.

Following are the four core objects of ADO.NET data providers.

  • The ‘Connection’ object, which establishes the connection to a data source.
  • The ‘Command’ object, which interacts with the data source by executing commands. It uses the ‘Connection’ object to identify the data source.
  • The ‘DataReader’ object reads a stream of data from data source with a read-only attribute. This object retrieves the result of ‘SELECT’ statement from a ‘Command’ object.
  • The ‘DataAdapter’ object fills the ‘DataSet’ with requested part of data source as a discontinued data and manages the updates with the data source.

The following are the main data providers available in .NET.

  • SQL data provider is for accessing the Microsoft SQL Server where no intermediate layers of service components are involved. These classes are located in ‘System.Data.SqlClient’ namespace.
  • OLE DB data provider is for Access and Excel as they expose an OLE DB interface. Classes are located in  ‘System.Data.OleDb’ namespace.
  • ODBC data provider classes reside in ‘System.Data.Odbc’ namespace used for old databases exposing the ODBC interface.
  • Oracle data provider is used for interacting with Oracle data sources through Oracle client connectivity software. Classes are encapsulated in ‘System.Data.OracleClient’ namespace.

The following C# code snippet demonstrates the use of OLE DB data provider classes to read Excel spreadsheet.

string connectionString = string.Format ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties=\"Excel 12.0 Xml; HDR=No; IMEX=1; MAXSCANROWS=20; READONLY=TRUE\";", strExcelFilePath);

OleDbConnection connection = new OleDbConnection (connectionString);

try {

connection.Open ();

OleDbCommand command = new OleDbCommand ("select * from [SheetName$]", connection);

OleDbDataAdapter dataAdapter = new OleDbDataAdapter (command);

DataSet dataSet = new DataSet ();

dataAdapter.Fill (dataSet);

con.Close ();

}

catch (Exception e) {

}

finally {

con.Close ();

}

In the above code, ‘ACE OLEDB’ driver is used to connect to an Excel spreadsheet using ‘OledbConnection’ object with a ‘connectionString’ (the path of a data source, properties for file access and name of a provider) as an argument. The ‘OledbCommand’ object uses that connection object and a query string to set a command for a data source. Finally, the ‘OledbDataAdapter’ object takes that command object as an argument and retrieves all the data of a ‘sheetName’ in query string and filled a DataSet with that data.

Interested in learning more about ADO.NET? Look at this online course at Udemy.com.

ADO.NET DataSet

The ADO.NET ‘DataSet’ object supports the disconnected data access and operations on the data. It is cached data that provides a reliable relational programming model regardless of the data source. Therefore, it can be used with different data sources such as XML data or for managing the part of data downloaded in an application to increase the scalability ability of an application.

ADO.NET ‘DataSet’ contains ‘DataTableCollection’ and ‘DataRelationCollection’ object. A ‘DataTableCollection’ have none or multiple ‘DataTable’, which represents a single table, residing in the memory. A ‘DataTable’ contains ‘DataColumnCollection’, a collection of columns, and ‘ConstraintCollection’, a collection of constraints, which collectively describes the schema of the table. It also contains ‘DataRowCollection’, a collection of rows, which holds the data in the table in the form of ‘DataRow’ objects, a row of data in a table. A ‘DataRelationCollection’ have none or multiple ‘DataRelation’ objects, which represents a relationship between rows of two ‘DataTable’ object by identifying the matched columns in these two tables.

The following C# code snippet illustrates the basic use of ADO.NET ‘DataSet’ and Microsoft SQL Server data provider classes, to access the SQL server, retrieve and filter the ‘Customer’ data.

string connectionString = @"Data Source=SQLSERVERXXXX; Initial Catalog=DB_NAME; User ID=USER_NAME; Password=USER_PASSWORD";

SqlConnection conenction = new SqlConnection (connectionString);

DataSet dataSet = new DataSet ();

SqlCommand command = new SqlCommand ();

command.Connection = conenction;

SqlDataAdapter dataAdapter = new SqlDataAdapter ();

command.CommandText = "Select * From CustomerOrder";

dataAdapter.SelectCommand = command;

dataAdapter.Fill (dataSet, "CustomerOrder");

DataTable dataTable = new DataTable ();

dataTable = dataSet.Tables[0];

if (dataSet.Tables.Count > 0)

{

foreach (DataRow dataRow in dataTable.Rows)

{//Traverse all rows and output when a order status is only production

if (dataRow["Order_Status"].ToString () == "Production")

{

Console.WriteLine ("ID: " + Convert.ToString (dataRow["Customer_ID"]));

Console.WriteLine ("Name: " + Convert.ToString (dataRow["Customer_Name"]));

}

}

}

DataRow newOrder;

newOrder = dataTable.NewRow ();

newOrder["Customer_ID"] = 04;

newOrder["Customer_Name"] = "Mr. George";

newOrder["Order_Status"] = "Delivered";

//Add a row

dataTable.Rows.Add (newOrder);

 

newOrder = dataTable.Rows.Find (1);

//Edit a row

newOrder.BeginEdit ();

newOrder["Order_Status"] = "Delivered";

newOrder.EndEdit ();

 

newOrder = dataTable.Rows.Find (1);

//Delete a row

newOrder.Delete ();

 

SqlCommandBuilder CommandBuilder = new SqlCommandBuilder (dataAdapter);

dataAdapter.Update (dataSet, "CustomerOrder");

In the above code snippet, an ADO.NET DataSet connection is established with the SQL server database. Using ‘SqlCommand’ and ‘SqlDataAdapter’, all data specified in ‘SELECT’ query is retrieved and ‘DataSet’ object is filled with it. A ‘DataTable’ at index zero of ‘DataTableCollection’ holds the entire customer’s data. A DataTable object is then enumerated using a ‘foreach’ loop to get a row of a table in ‘DataRow’ object in iterations. To insert a new row, first a ‘NewRow’ method of DataTable is called to return a DataRow object with the same fields as in the other DataRow objects of DataTable.

Then the ‘Add’ method of ‘DataTable.Row’ adds a new row in DataTable. To update and delete a row, the ‘Find’ method of ‘DataTable.Row’ is called that searches for a record, provided the value matches the primary key of a table. When a match is found, ‘BeginEdit’ and ‘EndEdit’ methods are called to update the retrieved record. The ‘Delete’ method is used to delete that searched record from a DataTable. Finally, all changes are made to a database table in SQL server by calling the ‘Update’ method of ‘DataAdapter’.

To get started programming in C#, check out a tutorial at Udemy.com.