Post
Ask Question

Introduction to ADO.Net

Introduction

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

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

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 data provider 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, and 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 retrieved from a database and to update the data source.
    Components

DataSet

The data set is a part of a disconnected architecture. A data set 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 data provider establishes a connection with a database. 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.

  1. ConnectionString: provides information, such as database name and, user credentials for database access and so on.
  2. Open(): Opens the connection for accessing the database.
  3. 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 the 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.

Demo

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.

Output

In the next article, we will see data binding and filter records in ADO.NET.


Similar Articles