SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Proper .NET SQL connection code

    I'm trying to get into some good habits when I'm using .NET to connect to SQL. I'm somewhat new to .NET and I sometimes read conflicting advice on how this is best done. I hear use try/catch and then sometimes don't use try/catch (because of the overhead) and use if/else instead. I've also read that it is good to use the "using" keyword. Should mention I'm using .NET 2.0.

    Two of the main things I need to do are handle exceptions cleanly and also serve up an alternate message (instead of the Repeater) if no rows are returned for a given query. With my code below, what would be the best way to do this? I want to have basic foundation good before I start to build on it.

    Code:
    protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                // declare objects
                SqlConnection conn;
                SqlCommand comm;
                SqlDataReader reader;
    
                // Read the connection string from Web.config
                string connectionString = ConfigurationManager.ConnectionStrings["dbname"].ConnectionString;
    
                // Initialize connection
                conn = new SqlConnection(connectionString);
                comm = new SqlCommand("select * from example1; select * from example2", conn);
    
                conn.Open();
                
                reader = comm.ExecuteReader();
    
                Repeater1.DataSource = reader;
                Repeater1.DataBind();
                
                if (reader.NextResult())
                {
                    Repeater2.DataSource = reader;
                    Repeater2.DataBind();
                }
    
                reader.Close();
                 
                conn.Close();
            }
        }

  2. #2
    SitePoint Wizard
    Join Date
    Feb 2007
    Posts
    1,274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try-catch has virtually *no* overhead, unless an exception is thrown. Period. Use try-catch and using statements to make your code more robust. Never refrain from such a statement out of fear of performance. You have been given bad advice.

    Just don't use exceptions as means of controlling program flow. Exceptions should only be thrown for exceptional reasons.

    Returning 0 (zero) rows will not throw an exception.

    In the above code use something like

    Code Csharp:
    protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                // Read the connection string from Web.config
                string connectionString = ConfigurationManager.ConnectionStrings["dbname"].ConnectionString;
     
                // Initialize connection
                using(SqlConnection conn = new SqlConnection(connectionString)) {
                    conn.Open();
                    SqlCommand comm = new SqlCommand("select * from example1; select * from example2", conn);
     
                    SqlDataReader reader = comm.ExecuteReader();
                    Repeater1.DataSource = reader;
                    Repeater1.DataBind();
     
                    if (reader.NextResult())
                    {
                        Repeater2.DataSource = reader;
                        Repeater2.DataBind();
                    }
     
                    reader.Close();
     
                    conn.Close();
                }
            }
        }

    Limited resources such as connections should *always* be managed by a using statement, or if not feasible, a try-catch-finally block.

    Note: I think there may be a problem with the repeater overwriting the results of the previous resultset.

  3. #3
    SitePoint Wizard webcosmo's Avatar
    Join Date
    Oct 2007
    Location
    Boston, MA
    Posts
    1,480
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Without using Exception your users would see the ugly error page. Thats the way to go.

  4. #4
    SitePoint Wizard
    Join Date
    Feb 2007
    Posts
    1,274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by webcosmo View Post
    Without using Exception your users would see the ugly error page. Thats the way to go.
    Actually, no. ASP.NET is more sophisticated than that. By all means, if you don't know how to rectify the exceptional condition or don't have anything to add to the exception you should leave it uncaught. This is important and somewhat contrary to e.g. PHP and classic ASP.

    The "ugly error pages" is much better dealt with by configuring a default error page in web.config. The custom error page can show an apropriate "service interruption" message to the user while still logging the exeption /sending a mail to the admin.

  5. #5
    SitePoint Wizard webcosmo's Avatar
    Join Date
    Oct 2007
    Location
    Boston, MA
    Posts
    1,480
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by honeymonster View Post
    The "ugly error pages" is much better dealt with by configuring a default error page in web.config. The custom error page can show an apropriate "service interruption" message to the user while still logging the exeption /sending a mail to the admin.
    I personally do fetch the error on catch block and show a custom error message to the user.

    But I like your idea quoted above. Much better practice I must say.

  6. #6
    SitePoint Wizard
    Join Date
    Feb 2007
    Posts
    1,274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Reading my post again I realize that I may have come across as a bit condescending. I apologize; it was never my intention.

    There is only one example I can think of where I'd want to catcht the exception and do something else: When doing a page with several independent components (think portals and the like) where I don't have complete control over each element. If I deem that the page may still provide value I may catch exceptions and simply remove the offending element.

  7. #7
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I modified my code as you suggested honeymonster, thanks.

    As for the repeater overwriting the results, I haven't run into any problems with doing 2 queries this way. Should I be concerned? Is there a better way to do 2 queries and put them in repeaters/loop through them?

    Quote Originally Posted by honeymonster View Post
    Note: I think there may be a problem with the repeater overwriting the results of the previous resultset.

  8. #8
    SitePoint Wizard
    Join Date
    Feb 2007
    Posts
    1,274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frankdux View Post
    Is there a better way to do 2 queries and put them in repeaters/loop through them?
    union or union all (or Concat if using LINQ).

  9. #9
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have to use .NET 2.0 for the time being. Haven't used union before but I will check it out.

    Quote Originally Posted by honeymonster View Post
    union or union all (or Concat if using LINQ).


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •