Introduction To ADO.NET

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.

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 data binding and filter records in ADO.NET.