Introduction to ADO.NET

This lesson is an introduction to ADO.NET.  It introduces primary ADO.NET concepts and objects that you will learn about in later lessons.  Here are the objectives of this lesson:

• Learn what ADO.NET is.
• Understand what a data provider is.
• Understand what a connection object is.
• Understand what a command object is.
• Understand what a DataReader object is.
• Understand what a DataSet object is.
• Understand what a DataAdapter object is.

Introduction

ADO.NET is an object-oriented set of libraries that allows you to interact with data sources.  Commonly, the data source is a data base, but it could also be a text file, an Excel spread sheet, or an XML file.  For the purposes of this tutorial, we will look at ADO.NET as a way to interact with a data base.

As you are probably aware, there are many different types of data bases available.  For example, there is Microsoft SQL Server, Microsoft Access, Oracle, Borland Interbase, and IBM DB2, just to name a few.  To further refine the scope of this tutorial, all of the examples will use SQL Server. 

Data Providers

We know that ADO.NET allows us to interact with different types of data sources and different types of data bases.  However, there isn't a single set of classes that allow you to accomplish this universally.  Since different data sources expose different protocols, we need a way to communicate with the right data source using the right protocol.  Some older data sources use the ODBC protocol, many newer data sources use the OleDb protocol, and there are more data sources every day that allow you to communicate with them directly through .NET ADO.NET class libraries. 

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.  These libraries are called Data Providers and are usually named for the protocol or data source type they allow you to interact with.  table 1 lists some well known data providers, the API prefix they use, and the type of data source they allow you to interact with.

table 1.  ADO.NET Data Providers are class libraries that allow a common way to interact with specific data sources or protocols.  The library APIs have prefixes that indicate which provider they support.

Provider Name	API prefix	Data Source Description
ODBC Data Provider	Odbc	Data Sources with an ODBC interface.  Normally older data bases.
OleDb Data Provider	OleDb	Data Sources that expose an OleDb interface, i.e. Access or Excel.
Oracle Data Provider	Oracle	For Oracle Data Bases.
SQL Data Provider	Sql	For interacting with Microsoft SQL Server.
Borland Data Provider	Bdp	Generic access to many data bases such as Interbase, SQL Server, IBM DB2, and Oracle.

An example may help you to understand the meaning of the API prefix.  One of the first ADO.NET objects you'll learn about is the connection object, which allows you to establish a connection to a data source.  If we were using the OleDb Data Provider to connect to a data source that exposes an OleDb interface, we would use a connection object named OleDbConnection.  Similarly, the connection object name would be prefixed with Odbc or Sql for an OdbcConnection object on an Odbc data source or a SqlConnection object on a SQL Server data base, respectively.  Since we are using MSDE in this tutorial (a scaled down version of SQL Server) all the API objects will have the Sql prefix.  i.e. SqlConnection.

ADO.NET Objects

ADO.NET includes many objects you can use to work with data.  This section introduces some of the primary objects you will use.  Over the course of this tutorial, you'll be exposed to many more ADO.NET objects from the perspective of how they are used in a particular lesson.  The objects below are the ones you must know.  Learning about them will give you an idea of the types of things you can do with data when using ADO.NET.

The SqlConnection Object

To interact with a data base, you must have a connection to it.  The connection helps identify the data base server, the data base 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 data base to execute the command on.

The SqlCommand Object

The process of interacting with a data base 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 data base.  A command object uses a connection object to figure out which data base 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.

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.

The DataSet Object

DataSet objects are in-memory representations of data.  They contain multiple Datatable objects, which contain columns and rows, just like normal data base 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.

The SqlDataAdapter Object

Sometimes the data you work with is primarily read-only and you rarely need to make changes to the underlying data source.  Some situations also call for caching data in memory to minimize the number of data base calls for data that does not change.  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 data base.  A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the data base.  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 data base for you.  All you need to do is tell the data adapter when to load from or write to the data base.

Summary

ADO.NET is the .NET technology for interacting with data sources.  You have several Data Providers, which allow communication with different data sources, depending on the protocols they use or what the data base is.  Regardless, of which Data Provider used, you'll use a similar set of objects to interact with a data source.  The SqlConnection object lets you manage a connection to a data source.  SqlCommand objects allow you to talk to a data source and send commands to it.  To have fast forward-only read access to data, use the SqlDataReader.  If you want to work with disconnected data, use a DataSet and implement reading and writing to/from the data source with a SqlDataAdapter.

Read More

Working with Disconnected Data - The DataSet and SqlDataAdapter

 

This lesson explains how to work with disconnected data, using the DataSet and SqlDataAdapter objects.  Here are the objectives of this lesson:

• Understand the need for disconnected data.
• Obtain a basic understanding of what a DataSet is for.
• Learn to use a SqlDataAdapter to retrieve and update data.

Introduction

In Lesson 3, we discussed a fully connected mode of operation for interacting with a data source by using the SqlCommand object.  In Lesson 4, we learned about how to read data quickly an let go of the connection with the SqlDataReader.  This Lesson shows how to accomplish something in-between SqlConnection and SqlDataReader interaction by using the DataSet and SqlDataAdapter objects.

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.  Because the applications holds on to connections only when necessary, the application becomes more scalable.

A couple scenarios illustrate why you would want to work with disconnected data: people working without network connectivity and making Web sites more scalable.  Consider sales people who need customer data as they travel.  At the beginning of the day, they'll need to sync up with the main data base to have the latest information available.  During the day, they'll make modifications to existing customer data, add new customers, and input new orders.  This is okay because they have a given region or customer base where other people won't be changing the same records.  At the end of the day, the sales person will connect to the network and update changes for overnight processing.

Another scenario is making a Web site more scalable.  With a SqlDataReader, you have to go back to the data base for records every time you show a page.  This requires a new connection for each page load, which will hurt scalability as the number of users increase.  One way to relieve this is to use a DataSet that is updated one time and stored in cache.  Every request for the page checks the cache and loads the data if it isn't there or just pulls the data out of cache and displays it.  This avoids a trip to the data base, making your application more efficient.

Exceptions to the scenario above include situations where you need to update data.  You then have to make a decision, based on the nature of how the data will be used as to your strategy.  Use disconnected data when your information is primarily read only, but consider other alternatives (such as using SqlCommand object for immediate update) when your requirements call for something more dynamic.  Also, if the amount of data is so large that holding it in memory is impractical, you will need to use SqlDataReader for read-only data.  Really, one could come up with all kinds of exceptions, but the true guiding force should be the requirements of your application which will influence what your design should be.

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, which is used if you were to serialize the data to XML.  Since that isn't a requirement for this example, I left it out.  Right now, the DataSet is empty and you need a SqlDataAdapter to load it.

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

The code above creates a new SqlDataAdapter, daCustomers.  The SQL select statement specifies what data will be read into a DataSet.  The connection object, conn, should have already been instantiated, but not opened.  It is the SqlDataAdapter's responsibility to open and close the connection during Fill and Update method calls.

As indicated earlier, the SqlDataAdapter contains all of the commands necessary to interact with the data source.  The code showed how to specify the select statment, but didn't show the insert, update, and delete statements.  These are added to the SqlDataAdapter after it is instantiated.

There are two ways to add insert, update, and delete commands:  via SqlDataAdapter properties or with a SqlCommandBuilder.  In this lesson, I'm going to show you the easy way of doing it with the SqlCommandBuilder.  In a later lesson, I'll show you how to use the SqlDataAdapter properties, which takes more work but will give you more capabilities than what the SqlCommandBuilder does.  Here's how to add commands to the SqlDataAdapter with the SqlCommandBuilder:

SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);

Notice in the code above that the SqlCommandBuilder is instantiated with a single constructor parameter of the SqlDataAdapter, daCustomers, instance.  This tells the SqlCommandBuilder what SqlDataAdapter to add commands to.  The SqlCommandBuilder will read the SQL select statement (specified when the SqlDataAdapter was instantiated), infer the insert, update, and delete commands, and assign the new commands to the Insert, Update, and Delete properties of the SqlDataAdapter, respectively.

As I mentioned earlier, the SqlCommandBuilder has limitations.  It works when you do a simple select statement on a single table.  However, when you need a join of two or mor tables or must do a stored procedure, it won't work.  I'll describe a work-around for these scenarios in future lessons.

Filling the DataSet

Once you have a DataSet and SqlDataAdapter instances, you need to fill the DataSet.  Here's how to do it, by using the Fill method of the SqlDataAdapter:

daCustomers.Fill(dsCustomers, "Customers");

The Fill method, in the code above, takes two parameters: a DataSet and a table name.  The DataSet must be instantiated before trying to fill it with data.  The second parameter is the name of the table that will be created in the DataSet.  You can name the table anything you want.  Its purpose is so you can identify the table with a meaningful name later on.  Typically, I'll give it the same name as the database table.  However, if the SqlDataAdapter's select command contains a join, you'll need to find another meaningful name.

The Fill method has an overload that accepts one parameter for the DataSet only.  In that case, the table created has a default name of "table1" for the first table.  The number will be incremented (table2, table3, ..., tableN) for each table added to the DataSet where the table name was not specified in the Fill method.

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:

dgCustomers.DataSource = dsCustomers;
dgCustomers.DataMember = "Customers";

The first thing we do, in the code above, is assign the DataSet to the DataSource property of the DataGrid.  This lets the DataGrid know that it has something to bind to, but you will get a '+' sign in the GUI because the DataSet can hold multiple tables and this would allow you to expand each available table.  To specify exactly which table to use, set the DataGrid's DataMember property to the name of the table.  In the example, we set the name to Customers, which is the same name used as the second parameter to the SqlDataAdapter Fill method.  This is why I like to give the table a name in the Fill method, as it makes subsequent code more readable.

Updating Changes

After modifications are made to the data, you'll want to write the changes back to the data base.  Refer to previous discussion in the Introduction of this article on update guidance.  The following code shows how to use the Update method of the SqlDataAdapter to push modifications back to the data base.

daCustomers.Update(dsCustomers, "Customers");

The Update method, above, is called on the SqlDataAdapter instance that originally filled the dsCustomers DataSet.  The second parameter to the Update method specifies which table, from the DataSet, to update.  The table contains a list of records that have been modified and the Insert, Update, and Delete properties of the SqlDataAdapter contain the SQL statements used to make data base modifications.

Putting it All Together

Until now, you've seen the pieces required to implement disconnected data managment.  What you really need is to see all this implemented in an application.  Listing 1 shows how the code from all the previous sections is used in a working program that has been simplified to enhance the points of this lesson:

Listing 1: Implementing a Disconnected Data Management Strategy

using System;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Windows.forms;

 class DisconnectedDataform : form

{

            private SqlConnection  conn;

            private SqlDataAdapter daCustomers;

             private DataSet  dsCustomers;

            private DataGrid dgCustomers;

             private const string tableName = "Customers";

             // initialize form with DataGrid and Button

            public DisconnectedDataform()

            {

                        // fill dataset

                        Initdata();

                         // set up datagrid

                        dgCustomers = new DataGrid();

                        dgCustomers.Location = new Point(5, 5);

                        dgCustomers.Size = new Size(
                                      this.Clientrectangle.Size.Width - 10, 
                                      this.Clientrectangle.Height - 50);

                        dgCustomers.DataSource = dsCustomers;

                        dgCustomers.DataMember = tableName;

                         // create update button

                        Button btnUpdate = new Button();

                        btnUpdate.Text = "Update";

                        btnUpdate.Location = new Point(

                                     this.Clientrectangle.Width/2 - btnUpdate.Width/2,

                                     this.Clientrectangle.Height - (btnUpdate.Height + 10));

                        btnUpdate.Click += new EventHandler(btnUpdateClicked);

                          // make sure controls appear on form

                        Controls.AddRange(new Control[] { dgCustomers, btnUpdate });

            }

             // set up ADO.NET objects

            public void Initdata()

            {

                        // instantiate the connection

                        conn = new SqlConnection(
                                       "Server=(local);DataBase=Northwind;Integrated Security=SSPI");

                         // 1. instantiate a new DataSet

                        dsCustomers = new DataSet();

                         // 2. init SqlDataAdapter with select command and connection

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

                        // 3. fill in insert, update, and delete commands

                        SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);                     

                        // 4. fill the dataset

                        daCustomers.Fill(dsCustomers, tableName);

            }

             // Update button was clicked

            public void btnUpdateClicked(object sender, EventArgs e)

            {

                        // write changes back to DataBase

                        daCustomers.Update(dsCustomers, tableName);

            }

             // start the Windows form

            static void Main()

            {

                        Application.Run(new DisconnectedDataform());

            }

 }

The Initdata method in Listing 1 contains the methods necessary to set up the SqlDataAdapter and DataSet.  Notice that various data objects are defined at class level so they can be used in multiple methods.  The DataGrid's DataSource property is set in the constructor.  Whenever a user clicks the Update button, the Update method in the btnUpdateClicked event handler is called, pushing modifications back to the data base.

Summary

DataSets hold multiple tables and can be kept in memory and reused.  The SqlDataAdapter enables you to fill a DataSet and Update changes back to the data base.  You don't have to worry about opening and closing the SqlConnection because the SqlDataAdapter does it automatically.  A SqlCommandBuilder populates insert, update, and delete commands based on the SqlDataAdapter's select statement.  Use the Fill method of the SqlDataAdapter to fill a DataSet with data.  Call the SqlDataAdapter's Update method to push changes back to a data base.

Read More