Introduction to ADO.Net

Introducing ADO.NET

This article introduces the ADO.Net object model. In addition it explains how to create and manage connections to a database.

Introduction to ADO.NET

Most applications need to handle data, whether it is in the form of a dataset, a text file, or a spreadsheet. The majority of modern-day applications need to deal with various types of databases. Therefore, to access this data the application needs to interact with various databases, such as Microsoft SQL Server, Oracle, Microsoft Access and so on.
 
What ADO.NET is

ADO.NET is a large set of .NET classes that enable us to retrieve and manipulate data, and update data sources, in very many ways. As an integral part of the .NET framework, it shares many of its features; features such as multi-language support, garbage collection, just-in-time compilation, object-oriented design, and dynamic caching, and is far more than an upgrade of previous versions of ADO. ADO.NET is set to become a core component of any data-driven .NET application or Web Service, and understanding its power will be essential to anyone wishing to utilize .NET data support to maximum effect.

ADO.NET is a part of the .NET framework architecture. It is a model used by .NET applications to communicate with a database for retrieving, accessing, and updating data, as shown in the following figure:

ADO.NET Object Model

In the ADO.NET object model, the data residing in a database is retrieved through a data provider. The data provider is the set of components including the Connection, Command, DataReader and DataAdapter objects. An application can access data either through a dataset or through a datareader object.

The ADO.NET object model consists of two fundamental components:

• Data Provider
• DataSet

Data Provider

Selecting an appropriate data provider for a client application depends on the type of data source being accessed. There are four .Net data providers available.

1. SQL Server: It's used to work specifically with Microsoft SQL Server. It exists in a namespace within the System.Data.SqlClient.
2. OLE DB: It's used to work with the OLEDB provider. The System.Data.dll assembly implements the OLEDB .NET framework data provider in the System.Data.OleDb namespace.
3. ODBC: To use this type of provider, you must use an ODBC driver. The System.Data.ODBC.dll assembly implements the ODBC .NET framework data provider . This assembly is not part of the Visual Studio .NET installation.
4. Oracle: The System.Data.OracleClient.dll assembly implements the Oracle  .NET framework data provider in the System.Data.OracleClient namespace. The Oracle client software must be installed on the system before you can use the provider to connect  to an Oracle data source.

Data Provider Components

The four key components of a dataprovider are:

1. Connection: Used to connect to the data source.
2. Command: Used to execute a command against the data source. This component retrieves, inserts, deletes, an modifies data in a data source.
3. DataReader: This component retrieves data from a data source in read-only and forward mode.
4. DataAdapter: Used to populate a dataset with the data retrived from a database and  to update the data source.
   

DataSet

DataSet is a part of a disconnected architecture. A DataSet is a cached memory of data retrieved  from a database. DataSet is present in the  System.Data namespace. In order to connect a DataSet to a data source, we need to use a DataAdapter.
 
Creating and Managing Connections In ADO.NET

Creating a Connection object

The connection component of a dataprovider establishes a connection with a data base. To connect to a Microsoft SQL Server, you use the SQL connection class. The following are the commonly used properties and Methods of the SqlConnection class.

ConnectionString: provides information, such as database name and, user credentials for database access and so on.

Open():  Opens the connection for accessing the database.

Close(): Closes the connection to the database.

For Example:

// Creating object of SqlConnection Class.

SqlConnection cn = new SqlConnection();

//Creating connection string to sample database.
cn.ConnectionString = "Data source=.; Initial Catalog=Sample; User Id=sa; Password=faculty";

The connection string provides the information that defines the connection to the database.

• Data Source: Specifies the provider name or your server name.
• Initial Catalog: Specifies the name of the database.
• User Id and Password: Provide the username and password of your database server.
  

Open the Connection

// Creating object of SqlConnection Class.

SqlConnection cn = new SqlConnection();

//Creating connection string to sample database.

cn.ConnectionString = "Data source=.; Initial Catalog=Sample; User Id=sa; Password=faculty";

cn.Open();  // it open the connection to database server..
 
Close the Connection

 

// Creating object of SqlConnection Class.

SqlConnection cn = new SqlConnection();

//Creating connection string to sample database.

cn.ConnectionString = "Data source=.; Initial Catalog=Sample; User Id=sa; Password=faculty";

cn.Open();  // it open the connection to database server..

//Creating sqlcommand class object

SqlCommand cmd = new SqlCommand("Select * from tblEmployees", cn);

SqlDataReader dr = cmd.ExecuteReader();//Executing query

cn.Close();//Closing the connection

Let's do one demo

Consider the situation in which you are working in XYZ inc. as an application developer. As a member of the development team, you have been asked to develop an application that will display all the records from tblEmployee table present in the sample database.

Note: In this example I'm using a sample database present in my datasource. Here you need to create a sample database and the tblEmployee table in that database, and populate the tblEmployee table with some values.

Step 1: Create a Windows Form application and design the form as shown in the following figure.


 
Step 2: Add the following two namespaces:

• System.Data.SqlClient;
• System.Data;

Step 3: On click event of the Button write the following code:

SqlConnection cn = new SqlConnection();

//Creating connection string to sample database.

cn.ConnectionString = "Data source=.; Initial Catalog=Sample; User Id=sa; Password=faculty";

//Open Connection

cn.Open();

//Creating SqlCommand class Object

SqlCommand cmd = new SqlCommand("Select * from tblEmployee", cn);

//Creating Object of dataAdapter

SqlDataAdapter da = new SqlDataAdapter();

//Creating object of dataSet

DataSet ds = new DataSet();

da.SelectCommand = cmd;

//Populating dataset by using fill method of sqldataAdapter

da.Fill(ds);

//Binding dataset with DataGrideview1 control

GridView1.DataSource = ds.Tables[0];

Note: We will see more about SqldataAdpter's methods and properties in my future articles on ADO.NET.

Step 4: Execute the application and verify the output by clicking on the button control. If everything goes fine then you will get the following output:

In the next article we will see DataBinding and filter records in ADO.NET.