Subscribe
Please visit my new Web Site https://coderstechzone.com
To describe the best way to open sql server connection in asp.net here i am choosing to bind the gridview because in most of the cases the major task is to bind GridView data. You can make more generous method to collect sql server data but here my intension is to show you how you can open sql server connection efficiently.
Please have a look at the code sample:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable dt;
String SQL= "SELECT B.Name [Brand Name],C.Name [Category Name], " +
"P.Name [Product Name] FROM " +
"Brand B, Category C, Product P " +
"WHERE B.ID=P.BrandID AND C.ID=P.CategoryID Order BY 1,2,3";
string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(sConstr))
{
using (SqlCommand comm = new SqlCommand(SQL, conn))
{
conn.Open();
using (SqlDataAdapter da = new SqlDataAdapter(comm))
{
dt = new DataTable("tbl");
da.Fill(dt);
}
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
The best practice is to wrap up all code under using statement. If you look at the code you will find that i have wrapped up all code under connection object as well as sql command. Keep in mind that when corresponding "using" statement reached at the end then asp.net automatically clear all variables immediately within the scope. You do not need to dispose those manually. Such as here i don't close the connection, sqlcommand. For ease understanding here i am using datatable. You can use any ado.net component whichever you like. But keep in mind to wrap up the connection object within "Using" statement.
This is my message to you.
