Data Adapter and Database Connection - ASP.NET Database Tutorial

Home > Aspnet > Tutorials > Data Adapter and Database Connection - ASP.NET Database Tutorial

Tell a friend

Link to us

Total Members

Members: 84657

Sitemap

Forum

Chat

Home

ASP.NET Database Tutorial Home

1 . Introduction to Asp.Net 2.0

2 . Introduction to ADO.Net

3 . Connecting Access Database with AccessDataSource and GridView Control

4 . Accessing SQL SERVER Database [Using SqlDataSource]

5 . Storing Connection String in Web.Config File

6 . Inserting Records in Database

7 . Updating Records in Database

8 . Updating Records in Database

9 . Deleting Records from Database

10 . Execute Scalar: Getting Single Value from the Database

11 . Execute Reader: Fetching Records from Database

12 . Data Adapter

13 . Data Adapter and Database Connection

14 . Performing Batch Updates: Using SqlCommandBuilder

15 . Improving Performance using Stored Procedure

16 . Filtering and Sorting Data Using DataView/DataTable

17 . Data Binding and Data Synchronization

18 . SQL Transaction and Locking

19 . SQL Joins and .Net

20 . Automatically Generation of SQL Statement

Aspnet Group Home

Aspnet Discussion (10)

Aspnet Members (2382)

Aspnet Resources

Aspnet Source Code (388)

Aspnet Articles (1)

Aspnet Blogs

Aspnet Jobs

Aspnet Components (201)

- Aspnet Ask Question

- Aspnet Questions

- Aspnet Unanswered Questions

GROUPS

.NET

Java

Other

TUTORIALS

.NET

.NET Tutorial

ASP Tutorial

ASP.NET Database Tutorial

ASP.NET Development Tips

ASP.Net Security,Internationalisation And Deployment

ASP.NET Server Controls Tips

Web Development

Software Development

Java

Java Web Component Tutorial

JSP And J2EE Design Tutorial

RESOURCES

SUBMISSIONS

COMMUNITY

SITE

Aspnet database Tutorial

Data Adapter and Database Connection

<< Prev: Data Adapter

Next: Performing Batch Updates: Using SqlCommandBuilder >>

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

<< Prev: Data Adapter	Next: Performing Batch Updates: Using SqlCommandBuilder >>
Aspnet Database Tutorial Home
Give feedback and win a prize.