Data Adapter and Database Connection

text zoom

Previous: Data Adapter Performing Batch Updates: Using SqlCommandBuilder :Next

Establishing and tearing down connections to a database is resource intensive. Because most applications will be re-connecting to a database over and over through its life-cycle, a pool of open connections is created by default for reuse for the application. This is known as Connection Pooling. The concept of connection pooling follows a simple rule i.e. Open connections to the database as late as possible and close connections to the database as early as possible. We know the data adapter will automatically open and close the connection thus help us to achieve the connection pooling rule.

Note:- In the SqlDataAdapter, the connection is never explicitly opened in code, because the data adapter object will automatically opens the connection when it is initialized. But here we go through the scenario, where we explicitly open the connection.

Example: Demonstrate Data Adapter and Database Connection

DataAdapterAndConnection.aspx

<% @ Page Language ="C#" AutoEventWireup ="true" CodeFile ="DataAdapterAndConnection.aspx.cs" Inherits ="DataAdapterAndConnection" %>

<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<asp:Label ID ="Label1" runat ="server" Text ="DataAdapter and Database Connection" Width ="247px"></asp:Label ></td></tr>

<asp:Button ID ="Button1" runat ="server" OnClick ="Button1_Click" Text ="Implicit Opening and Closing Connection" Width ="253px" /></td></tr>

<asp:GridView ID ="grdvFirst" runat ="server" AutoGenerateColumns ="False" BackColor ="#DEBA84" BorderColor ="#DEBA84" BorderStyle ="None" BorderWidth ="1px" CellPadding ="3" CellSpacing ="2">

<asp:BoundField DataField ="FirstName" HeaderText ="First Name" />

<asp:BoundField DataField ="LastName" HeaderText ="Last Name" />

<asp:BoundField DataField ="Address" HeaderText ="Address" />

</Columns>

</asp:GridView></td></tr>

<asp:Button ID ="Button2" runat ="server" OnClick ="Button2_Click" Text="Explicitly Opening and Closing Connection" Width ="252px" />

</td></tr>

<asp:GridView ID ="grdvSecond" runat ="server" AutoGenerateColumns ="False" BackColor ="#DEBA84" BorderColor ="#DEBA84" BorderStyle ="None" BorderWidth ="1px" CellPadding ="3" CellSpacing ="2">

<asp:BoundField DataField ="ProductId" HeaderText ="ProductId" />

<asp:BoundField DataField ="ProductName" HeaderText ="ProductName" />

<asp:BoundField DataField ="QuantityPerUnit" HeaderText ="QuantityPerUnit" />

</Columns>

</asp:GridView ></ td ></ tr >< tr >< td colspan ="3">

<asp:GridView ID ="grdvThird" runat ="server" AutoGenerateColumns ="False" BackColor ="#DEBA84" BorderColor ="#DEBA84" BorderStyle ="None" BorderWidth ="1px" CellPadding ="3" CellSpacing ="2">

<asp:BoundField DataField ="CategoryID" HeaderText ="CategoryID" />

<asp:BoundField DataField ="CategoryName" HeaderText ="Category Name" />

<asp:BoundField DataField ="Description" HeaderText ="Description" />

</Columns>

</asp:GridView>

</td></tr></table></div></form></body></html>

DataAdapterAndConnection.aspx.cs

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class DataAdapterAndConnection : System.Web.UI. Page

{

protected void Button1_Click( object sender, EventArgs e)

{ //Implicit Opening and Closing Connection: Using SqlDataAdapter Object

string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password = test " ;

string sqlQuery = "select * from Employees where EmployeeId < 6" ;

SqlConnection conn = new SqlConnection (strConn);

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = sqlQuery;

cmd.Connection = conn;

cmd.CommandType = CommandType .Text;

//Create Data Adapter Instance

//Implicitly Opening the Connection

SqlDataAdapter ada = new SqlDataAdapter ();

ada.SelectCommand = cmd;

//Create DataSet Object

DataSet dstEmployee = new DataSet ();

ada.Fill(dstEmployee);

//GridView

grdvFirst.DataSource = dstEmployee.Tables[0].DefaultView;

grdvFirst.DataBind();

}

protected void Button2_Click( object sender, EventArgs e)

{ //Explicitly Opening and Closing Connection: Using SqlDataAdapter Object

string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password = test " ;

string sqlProQuery = "select * from products where productId < 6" ;

string sqlCatQuery = "select * from categories where categoryId < 6" ;

SqlConnection conn = new SqlConnection (strConn);

//Product Command Object

SqlCommand cmdProduct = conn.CreateCommand();

cmdProduct.Connection = conn;

cmdProduct.CommandType = CommandType .Text;

cmdProduct.CommandText = sqlProQuery;

//Category Command Object

SqlCommand cmdCategory = conn.CreateCommand();

cmdCategory.Connection = conn;

cmdCategory.CommandType = CommandType .Text;

cmdCategory.CommandText = sqlCatQuery;

SqlDataAdapter ada = new SqlDataAdapter ();

ada.SelectCommand = cmdProduct;

DataSet dst = new DataSet ();

//Explicitly Open the Connection

conn.Open();

ada.Fill(dst, "Products" );

ada.SelectCommand = cmdCategory;

ada.Fill(dst, "Categories" );

//Closing the Connection

conn.Close();

//Binding the Second GridView with DataSet

grdvSecond.DataSource = dst.Tables[ "Products" ].DefaultView;

grdvSecond.DataBind();

//Binding the Third GridView with DataSet

grdvThird.DataSource = dst.Tables[ "Categories" ].DefaultView;

grdvThird.DataBind();

}

Output

Share This ...

Previous: Data Adapter Performing Batch Updates: Using SqlCommandBuilder:Next

Give feedback and win a prize.

Aspnet Discussion

...more

Aspnet Source Code

...more

Aspnet Related Tutorials

- ASP Tutorial
- ASP.NET Tutorial

...more

New Aspnet Resources

...more

>>Earn money through freelance projects!

Database Tutorial Home

Database Home

ASP.NET Resources

Community

Site

Data Adapter and Database Connection

Aspnet Discussion

Aspnet Source Code

Aspnet Related Tutorials

New Aspnet Resources

Member Panel