Blogs >> Technology >>
Efficient best Syntax to Open a SqlConnection in Asp.Net 2.0 3.5
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.
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 a
ll 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 imm
ediately 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.
|