Post
Ask Question

From DAO to ADO.NET

Summary : 

This article will briefly discuss and show a summary of the evolution of the Microsoft ActiveX Data Objects, it will also explain some database concepts and finally provide some code examples. 

A DBMS (Database Management System)  is a software system able to manage collections of data that are large, shared and persistente, and to ensure they reliability and privacy. ODBC (Open Database Connectivity) is a DBMS from Microsoft.  ODBC is a specification for an API that defines a standard set of routines with which an application can access data in a data source.

Software systems dedicated specifically to the management of data have been in existence only for 40 years, and still  some some applications do not make use of them. In the absence of specific software, data management is is performed by means  of traditional programming languages, for example C, Fortran or Visual Basic. Visual Basic 3.0 was the first version with database access capabilities. Data Access Object (DAO) enabled programmers to access local databases in the Microsoft Jet Database Engine format, which were primarily Indexed Sequential Access Method (ISAM) files. After DAO came RDO  and then ActiveX Data Objects (ADO). These data access technologies were designed for a client / server paradigm. However  the tendency of distributed computing forced the development of a new technology  to solve the problems of  data manipulation on a n-tier architecture. ADO.NET is the evolution of ADO and its components have benn designed to function properly on a n-tier architecture.

The next table is a summary of the evolution of the database objects to access data from Microsoft:

Name Brief Description
DAO Data Access Objects The first object-oriented interface that exposed the Microsoft Jet database engine that allowed developers using Visual Basic to directly connect to Access tables and other databases using ODBC. It is ideal for small databases in local deployments and single-system applications.
RDO Remote Data Objects An object-oriented data access interface to ODBC combined with the easy functionality of DAO allowing an interface to almost all of ODBC's low power and flexibility.
RDO can't access Jet of ISAM databases in an efficient way.  RDO provides the objects, properties, and methods needed to access the more complex aspects of stored procedures and complex resultsets.
ADO Microsoft ActiveX Data Objects ADO is the successor to DAO/RDO. ADO is the consolidation of almost all   the functionality of DAO and RDO.
ADO mostly includes RDO-style functionality to interact with OLE DB data sources, plus remoting and DHTML technology.
ADO MD Microsoft ActiveX Data Objects Multidimensional Provides easy access to multidimensional data from languages such as Microsoft Visual Basic and  Microsoft Visual C++. ADO MD extends Microsoft ActiveX Data Objects (ADO) to include objects specific to multidimensional data, such as the CubeDef and Cellset objects. To work with ADO MD, the provider must be a multidimensional data provider (MDP) as defined by the OLE DB for OLAP specification. MDPs present data in multidimensional views as opposed to tabular data providers (TDPs) that present data in tabular views. With ADO MD you can browse multidimensional schema, query a cube, and retrieve the results.
ADOX Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security Is an extension to the ADO objects and programming model. ADOX includes objects for schema creation and modification, as well as security. Because it is an object-based approach to schema manipulation, you can write code that will work against various data sources regardless of differences in their native syntaxes.ADOX is a companion library to the core ADO objects. It exposes additional objects for creating, modifying, and deleting schema objects, such as tables and procedures. It also includes security objects to maintain users and groups and to grant and revoke permissions on objects.
RDS Remote Data Service You can move data from a server to a client application or Web page, manipulate the data on the client, and return updates to the server in a single round trip.
ADO.NET Microsoft ActiveX Data Objects .NET ADO.NET is entirely based on XML. ADO.NET provides consistent access to data sources such as Microsoft SQL Server, as well as data sources exposed via OLE DB and XML. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update data.
ADO.NET cleanly factors data access from data manipulation into discrete components that can be used separately or in tandem. ADO.NET includes .NET data providers for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, or placed in an ADO.NET DataSet object in order to be exposed to the user in an ad-hoc manner, combined with data from multiple sources, or remoted between tiers. The ADO.NET DataSet object can also be used independently of a .NET data provider to manage data local to the application or sourced from XML.

The ADO.NET classes are found in System.Data.dll, and are integrated with the XML classes found in System.Xml.dll. When compiling code that uses the System.Data namespace, reference both System.Data.dll and System.Xml.dll.  Compared with ADO there is no Recordset object.

In ADO.NET there are four classes that read and write data from data sources:

  1. Connection .- Connect to data source
  2. Command .-   Execute stored procedures
  3. DataAdapter .-  Connects DataSet to database
  4. DataReader .- Forward/only, read/only cursor
MSDN Links:
  1. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/dasdkadooverview.asp
  2. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/adonetprogmsdn.asp

ADO RecordSets are a usefull tool for manipulating data in a Windows scenario,  a COM-based scenario, and they are also used with  ASP. However the RecordSet looses its appeal when you want to achieve application interoperability over the internet. ADO.NET has divided the RecordSet in three different objects, the DataSet, the DataReader and the DataAdapter. These new objects are centered on XML, so they provide new functionality to create distributed application development. Some of these functionality is the disconnected acces to relational data using DataSets, or working with XML objects using XSLT and XPath.

Example: Data Access with ADO.NET on a C# Windows Form

Step 1: Create a new C# Windows Form project with Visual Studio.NET

Step 2: Go to the server Explorer (Ctrl+Alt+S) then  press on the icon to connect with a database.

  • If you will be using an Access database choose the Microsoft Jet 4.0 Ole DB Provider, then press Next>>
  • Then select the database name and test the connection.

Step 3: Go to the toolbox (Ctrl+Alt+X) then click where it says Data

Step 4: Drag an OleDbDataAdapter to the Form, you might be prompted to configure the DataAdapter, say  yes.

Step 5: Select the database connection you added in the Server Explorer then press Next>>

Step 6: Use SQL statemets then press Next>>

Step 7: Use the Query Builder to generate the queries you would like to use, then press Next >> or Finish.

Step 8: Select with the mouse the oleDbDataAdapter, then go to the Properties Window (F4) and click on Generate Dataset...

  • Make sure that the checbox option that will add the dataset to the designer is selected.

Step 9: A new dataSet will be added to the desinger.

Step 10: Go to the toolbox (Ctrl+Alt+X) then click where it says Windows Forms and select a DataGrid, add it to the designer.

Step 11: Click on the dataGrid and then go to the Properties Window, on the DataSource field press the button and add the dataSet that was added to the designer.

Step 12: Double click on the form in the designer to switch to code view or press F7. Then type the following lines on the class constructor

oleDbConnection1.Open();
oleDbDataAdapter1.Fill(dataSet11);

Step 13: Build and compile and finnaly enjoy the data access with ADO.NET.

Source Code

Use the following source code as an example, if you try to compile it, it won't work because you do not have the database I used.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
namespace MSDNA1
{
/// <summary>
/// This program was developed as an aid to an article for MSDNAA by Gustavo Parés
/// The purpose of this program is to access data using the ADO.NET components and bind it to a dataGrid
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
private MSDNA1.DataSet1 dataSet11;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// On the class constructor you need to initialize the components from ADO.NET.
// Do not forget to open the connection, and fill the dataSet with the dataAdapter.
// If you are using SQL Server you should switch the components from OLE to SQL in the toolbox
//
InitializeComponent();
oleDbConnection1.Open();
oleDbDataAdapter1.Fill(dataSet11);
} 

protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}

#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>

private void InitializeComponent()
{
this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.dataSet11 = new MSDNA1.DataSet1();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// oleDbDataAdapter1
//
this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Impuestos", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("ApellidoMaterno", "ApellidoMaterno"),
new System.Data.Common.DataColumnMapping("ApellidoPaterno", "ApellidoPaterno"),
new System.Data.Common.DataColumnMapping("Nombre", "Nombre"),
new System.Data.Common.DataColumnMapping("RFC", "RFC"),
new System.Data.Common.DataColumnMapping("Telefono", "Telefono"),
new System.Data.Common.DataColumnMapping("yaPagoImpuestos", "yaPagoImpuestos")})});
this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
//
// oleDbConnection1
//
this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\BasesDatos\bdMexico.mdb;Mode=Share Deny None;Extended Properties="""";
Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";
Jet OLEDB:Engine Type=5;
Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="""";
Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
//
// dataSet11
//
this.dataSet11.DataSetName = "DataSet1";
this.dataSet11.Locale = new System.Globalization.CultureInfo("en-US");
this.dataSet11.Namespace = http://www.tempuri.org/DataSet1.xsd;
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.DataSource = this.dataSet11.Impuestos;
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(24, 16);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(392, 232);
this.dataGrid1.TabIndex = 0;
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.CommandText = "SELECT ApellidoMaterno, ApellidoPaterno, Nombre, RFC, Telefono,
yaPagoImpuestos F" +
"ROM Impuestos";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
//
// oleDbInsertCommand1
//
this.oleDbInsertCommand1.CommandText = "INSERT INTO Impuestos(ApellidoMaterno, ApellidoPaterno, Nombre, RFC, Telefono, ya" +
"PagoImpuestos) VALUES (?, ?, ?, ?, ?, ?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ApellidoMaterno", System.Data.OleDb.OleDbType.VarWChar, 50, "ApellidoMaterno"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ApellidoPaterno", System.Data.OleDb.OleDbType.VarWChar, 50, "ApellidoPaterno"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Nombre", System.Data.OleDb.OleDbType.VarWChar, 50, "Nombre"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("RFC", System.Data.OleDb.OleDbType.VarWChar, 50, "RFC"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Telefono", System.Data.OleDb.OleDbType.VarWChar, 50, "Telefono"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("yaPagoImpuestos", System.Data.OleDb.OleDbType.Boolean, 2, "yaPagoImpuestos"));
//
// oleDbUpdateCommand1
//
this.oleDbUpdateCommand1.CommandText = @"UPDATE Impuestos SET ApellidoMaterno = ?, ApellidoPaterno = ?, Nombre = ?, RFC = ?, Telefono = ?, yaPagoImpuestos = ? WHERE (RFC = ?) AND (ApellidoMaterno = ? OR ? IS NULL AND ApellidoMaterno IS NULL) AND (ApellidoPaterno = ? OR ? IS NULL AND ApellidoPaterno IS NULL) AND (Nombre = ? OR ? IS NULL AND Nombre IS NULL) AND (Telefono = ? OR ? IS NULL AND Telefono IS NULL) AND (yaPagoImpuestos = ?)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ApellidoMaterno", System.Data.OleDb.OleDbType.VarWChar, 50, "ApellidoMaterno"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ApellidoPaterno", System.Data.OleDb.OleDbType.VarWChar, 50, "ApellidoPaterno"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Nombre", System.Data.OleDb.OleDbType.VarWChar, 50, "Nombre"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("RFC", System.Data.OleDb.OleDbType.VarWChar, 50, "RFC"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Telefono", System.Data.OleDb.OleDbType.VarWChar, 50, "Telefono"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("yaPagoImpuestos", System.Data.OleDb.OleDbType.Boolean, 2, "yaPagoImpuestos"));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_RFC", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "RFC", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ApellidoMaterno", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ApellidoMaterno", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ApellidoMaterno1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ApellidoMaterno", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ApellidoPaterno", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ApellidoPaterno", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ApellidoPaterno1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ApellidoPaterno", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Nombre", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Nombre", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Nombre1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Nombre", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Telefono", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Telefono", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Telefono1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Telefono", System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_yaPagoImpuestos", System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "yaPagoImpuestos", System.Data.DataRowVersion.Original, null));
//
// oleDbDeleteCommand1
//
this.oleDbDeleteCommand1.CommandText = @"DELETE FROM Impuestos WHERE (RFC = ?) AND (ApellidoMaterno = ? OR ? IS NULL AND ApellidoMaterno IS NULL) AND (ApellidoPaterno = ? OR ? IS NULL AND ApellidoPaterno IS NULL) AND (Nombre = ? OR ? IS NULL AND Nombre IS NULL) AND (Telefono = ? OR ? IS NULL AND Telefono IS NULL) AND (yaPagoImpuestos = ?)";
this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_RFC", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "RFC", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ApellidoMaterno", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ApellidoMaterno", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ApellidoMaterno1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ApellidoMaterno", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ApellidoPaterno", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ApellidoPaterno", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_ApellidoPaterno1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ApellidoPaterno", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Nombre", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Nombre", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Nombre1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Nombre", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Telefono", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Telefono", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_Telefono1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Telefono", System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_yaPagoImpuestos", System.Data.OleDb.OleDbType.Boolean, 2, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "yaPagoImpuestos", System.Data.DataRowVersion.Original, null));
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(456, 273);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.dataGrid1});
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
#endregion 

[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void Form1_Load(object sender, System.EventArgs e)
{
MessageBox.Show("Enjoy ADO.NET and greetings from Gustavo Pares");
}
}
}

Bibliography


Similar Articles