Thursday, February 25, 2010

Efficient best Syntax to Open a SqlConnection in Asp.Net 2.0 3.5




Please visit my new Web Site WWW.Codedisplay.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.

3 comments:

Anonymous said...

Thanks for the great info.

Just one thing, what about if I want to wrap the conn.open() on a cftry as well as the SqlDataReader on my case. Where should i mount it. Or do i still need it? ofcourse i do

Or can i just wrap the whole thing starting from using (SqlConnection conn = new SqlConnection(sConstr))
?

Your recommendation, what's the best way.

Saion Roy said...

Hi,
You can use try catch out side "Using" statement but no need to use within the "Using" since "Using" automatically rolledback for transactions if you follow the below way:

using (SqlConnection sqlCn = new SqlConnection())
{
using (SqlTransaction yourTrans = sqlCn.BeginTransaction())
{
...
yourTrans.Commit();
}
}

Unknown said...

i always under estimate the power of using statement now i cant do withoout it. thank alot

Want to say something?
I WOULD BE DELIGHTED TO HEAR FROM YOU

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash