Thread: C# database question

that seems to work, just like my original code, but I continue to get this in the upper left hand corner of the page:

database connecteddatabase not connectedThread was being aborted.

Originally Posted by jmurrayhead

Alright, now give this a shot:

Code:

String set = ConfigurationManager.ConnectionStrings["peebman2000"].ConnectionString;
 
datacommand.Connection = new SqlConnection(set);

Your connection string should look like this:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrin gs["peebman2000"].ToString());

Since you are getting both messages, I think it's possible that your Response.End() may be throwing an exception -- you Catch block will catch every exception. (Tho why the second Response.End() doesn't throw another exception, I don't know).

Have you tried stepping through this in the debugger and seeing what happens?

Thanks wolffy, i'm not real good at using the debugger in visual studio 05, I usually use response.write's to debug my code, even though in this case i'm not using response.write's to debug the code. I just want to know if the code is allowing to connect to the database or not.

I'll try puting a break point and see if I can understand some of the things the debugger throughs at me.

Originally Posted by Wolffy

Since you are getting both messages, I think it's possible that your Response.End() may be throwing an exception -- you Catch block will catch every exception. (Tho why the second Response.End() doesn't throw another exception, I don't know).

Have you tried stepping through this in the debugger and seeing what happens?

Since you're learning, it's important to learn how to handle connections an such the right way:
Make sure that you use try ... catch blocks for your db related access as shown below:

Code:

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["peebman2000"].ToString());
try
{
     connection.Open();
     SqlCommand command = new SqlCommand(YourSQLStatement, connection);            
     SqlDataReader reader = command.ExecuteReader();

     if (reader.HasRows)
     {             
          while (reader.Read())
          {
             Response.Write(reader["YourDbField"].ToString() + "<br>");
          }                             
      }
      else
          Response.Write("There are no matching Records");
}
catch (SqlException ex)
{
     Response.Write(ex.TargetSite.ToString() + "<br />ERROR MESSAGE <br /> " + ex.Message.ToString() + "<br />STACK TRACE <br />" + ex.StackTrace.ToString()); 
}
finally
{
     connection.Close();
     SqlConnection.ClearPool(connection);
}

This code will ensure that your connections are closed properly and that any exceptions are caught

Okay Lewy that worked, i tried several times and when I hit the button I get:

database connected

from the response.write.

Thanks for the help, but one question. What does this snippit of code do?

Code:

SqlConnection.ClearPool(datacommand.Connection);

I don't understand what this is doing.

Let me know and thanks all the help guys again.

Here is my updated code.

Code:

protected void Button1_Click(object sender, EventArgs e)
    {

        {
            SqlDataAdapter dataadapter;
            SqlCommand datacommand;
            dataadapter = new SqlDataAdapter();
            datacommand = new SqlCommand();//ConnectionStringSettings
            String set = ConfigurationManager.ConnectionStrings["peebman2000"].ToString();

             datacommand.Connection = new SqlConnection(set);

            
             try
             {
                 datacommand.Connection.Open();
                 
                 Response.Write("database connected");
                 Response.End();

             }
            
             catch (SqlException ex)
             {
                 Response.Write("database not connected" + ex.Message);
                
             }
             finally
             {
                 datacommand.Connection.Close();
                 SqlConnection.ClearPool(datacommand.Connection);
             }
        } 
    }

Originally Posted by lewy

Since you're learning, it's important to learn how to handle connections an such the right way:
Make sure that you use try ... catch blocks for your db related access as shown below:

Code:

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["peebman2000"].ToString());
try
{
     connection.Open();
     SqlCommand command = new SqlCommand(YourSQLStatement, connection);            
     SqlDataReader reader = command.ExecuteReader();

     if (reader.HasRows)
     {             
          while (reader.Read())
          {
             Response.Write(reader["YourDbField"].ToString() + "<br>");
          }                             
      }
      else
          Response.Write("There are no matching Records");
}
catch (SqlException ex)
{
     Response.Write(ex.TargetSite.ToString() + "<br />ERROR MESSAGE <br /> " + ex.Message.ToString() + "<br />STACK TRACE <br />" + ex.StackTrace.ToString()); 
}
finally
{
     connection.Close();
     SqlConnection.ClearPool(connection);
}

This code will ensure that your connections are closed properly and that any exceptions are caught

Okay thanks for the explaination jmurray. I'm done practicing C# for today. You guys may see me again tommorrow.

thanks.

Originally Posted by jmurrayhead

SqlConnection.ClearPool(datacommand.Connection); is used for cleanup. Essentially, ClearPool clears the connection pool that is associated with the connection.

In some quick surfing on Reponse.End(), there does some to be some anecdotal evidence that it causes problems in ASP.NET, especially in Try..Catch blocks. Having never used it in .NET, I'm going to do some more research.