Data Access in .NET using C#: Part 1 - Data Access in .NET

(Page 2 of 4 )

Data access is an important part of any web application. Relational database management systems (RDBMS's) are the most common items used in a web application. ADO.NET is the primary – and most important -- technology for working with RDBMS's in .NET.

ADO.NET is not the next version of ADO (ActiveX Data Objects). ADO provided many features however. In fact, previous data access models were designed around two-tier development. ADO ran on top of COM. ADO.NET supports the Microsoft.NET framework and its many features. It was built from the ground up, and this is the reason why ADO.NET handles XML very well. XML technology guarantees interoperability because it is an industry standard format.

In ADO.NET, data is represented and marshaled as XML automatically. Data can be serialized directly from XML for faster, more portable transfer across a network. Disconnected databases are a new feature in ADO.NET. They are not completely new, because ADO 2.0 introduced disconnected RecordSets for scalability, however if too many sessions were opened at the same time, then it effected system performance. Using a disconnected database, we can open a connection for short period of time. A DataSet can hold an entire database or part of a database in memory.

Because DataSets are disconnected, they must use a DataAdapter object to keep track of database changes. The ADO.NET object model classes are defined in two groups: content component and managed-provider component. The content component is the most important component because it includes the DataSet and DataRelation classes. The managed-provider components can be used to directly manipulate data. The data can be in any format, such as an XML file, Excel or ASCII text files.

The System.Data.OleDbClient namespace includes OLE DB support and the System.Data.SqlClient namespace supports Microsoft SQL Server 7 and above.



A Practical Example
Let's take one short example for ADO.NET. We will use an MS-Access Database. Start VisualStudio.NET IDE and create new C# Windows Application Project. Add one list box and button onto your form, as shown below:



Call the list box "lb" and call the button "btn". Set the buttons text property to "Retrieve Data". Right click on the form and choose view code from the menu. Add these namespaces to the code:

using System.Data;
using System.Data.OleDb;

The System.Data.OleDb namespace contains the components necessary to implement the OLE DB data provider. Now, double click on the button control and add this code into the buttons click event:

private void button1_Click(object sender, System.EventArgs e)

{

OleDbConnection Myconnection = null;
OleDbDataReader dbReader = null;

Myconnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=C:\familyTree.mdb");
Myconnection.Open();

OleDbCommand cmd = Myconnection.CreateCommand();
cmd.CommandText = "SELECT * FROM familyTree";
dbReader = cmd.ExecuteReader();

string Email;

while(dbReader.Read())
{
Email = (string)dbReader.GetValue(5);
lb.Items.Add(Email);

}

dbReader.Close();
Myconnection.Close();
}

To make a connection to the database, we should use a connection object from the ADO.NET Classes. Before we do anything with the database, we must have an active, working connection to it. Connection objects have different parameters, dependant upon the underlying database. For example, if we use an MS-Access database, then this code will establish a session with the database:

OleDbConnection Myconnection = null;

Myconnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=C:\familyTree.mdb");
Myconnection.Open();

An OleDbConnection object represents a unique connection to a data source. When you create an instance of OleDbConnection, all properties are set to their initial values. The OleDbconnection object has one string parameter with four attributes for the connection. The are as follows:

• Provider: This is dependant upon the underlying database. In this example we use the Microsoft Jet database engine to connect to a MS-Access database.
  
  We can add new vender-specific data providers. OLEDB and SQL data providers are the standard data providers that come with the .NET framework.
• User Id and Password: The username and password to connect to the database.
• Data Source: This is the physical location of the database file.

After the connection object has been created, it can be opened with the Open() method. This method is available in the OleDBConnection object.

Next: The DataReader Object >>
 

More ADO.NET Articles
More By Sachin Korgaonkar

Comments On: Data Access in .NET using C#: Part 1

>>> Be the FIRST to comment on this article!