TerryBurger
asked on
How to manage SQL connections
I have an ASP.NET/C# application (.NET 2.0) that is basically a data entry interface to a SQL database. I'm trying to figure out the best way to manage SQL connections within it.
What I'm doing now, which isn't working, is I have 2 protected classes:
The first takes a command, opens a connection, and returns a datareader. The problem is, for the datareader
to work, I need to leave the connection open. Once this function exits, I have no way to close the connection.
protected SqlDataReader sql_read(string lc_cmd)
{
SqlConnection cn = new SqlConnection(System.Confi
cn.Open();
SqlCommand cmd = new SqlCommand(lc_cmd, cn);
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
This second takes a command, opens a connection, and returns a datatable. This one is able to close the connection.
protected DataTable sql_run(string lc_cmd)
{
SqlConnection cn = new SqlConnection(System.Confi
SqlDataAdapter cmd = new SqlDataAdapter(lc_cmd, cn);
cmd.SelectCommand.CommandT
DataTable myDataTable = new DataTable();
cmd.Fill(myDataTable);
cmd.Dispose();
cn.Close();
return myDataTable;
}
What I want is one simple global class that opens the sql connection as part of the constructor, allows fluid access of the connection, and then closes it on the deconstructor. I tried to add in this code:
SqlConnection myConn = new SqlConnection(System.Confi
public batch2()
{
//SqlConnection myConn = new SqlConnection(System.Confi
myConn.Open();
}
~batch2()
{
myConn.Close();
}
and reference myConn inside the class, but it just crashes after I access myConn twice.
Any advice appreciated! There is a lot of SQL read/write so I think I'll be better served leaving the connection open, although if I had just one class I could set a timer object to close the connection if no activity came within the past 30 seconds, and reopen it when needed.
If it makes a difference, I have several pages (I guess, classes), so something that could be global to the entire project would be ideal.
Thanks,
Terry
What I'm doing now, which isn't working, is I have 2 protected classes:
The first takes a command, opens a connection, and returns a datareader. The problem is, for the datareader
to work, I need to leave the connection open. Once this function exits, I have no way to close the connection.
protected SqlDataReader sql_read(string lc_cmd)
{
SqlConnection cn = new SqlConnection(System.Confi
cn.Open();
SqlCommand cmd = new SqlCommand(lc_cmd, cn);
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
This second takes a command, opens a connection, and returns a datatable. This one is able to close the connection.
protected DataTable sql_run(string lc_cmd)
{
SqlConnection cn = new SqlConnection(System.Confi
SqlDataAdapter cmd = new SqlDataAdapter(lc_cmd, cn);
cmd.SelectCommand.CommandT
DataTable myDataTable = new DataTable();
cmd.Fill(myDataTable);
cmd.Dispose();
cn.Close();
return myDataTable;
}
What I want is one simple global class that opens the sql connection as part of the constructor, allows fluid access of the connection, and then closes it on the deconstructor. I tried to add in this code:
SqlConnection myConn = new SqlConnection(System.Confi
public batch2()
{
//SqlConnection myConn = new SqlConnection(System.Confi
myConn.Open();
}
~batch2()
{
myConn.Close();
}
and reference myConn inside the class, but it just crashes after I access myConn twice.
Any advice appreciated! There is a lot of SQL read/write so I think I'll be better served leaving the connection open, although if I had just one class I could set a timer object to close the connection if no activity came within the past 30 seconds, and reopen it when needed.
If it makes a difference, I have several pages (I guess, classes), so something that could be global to the entire project would be ideal.
Thanks,
Terry
You are absolutely wrong to think it's necessary to leave the connection open after "ExecuteReader" for the datereader to work. Just try the following:
protected SqlDataReader sql_read(string lc_cmd)
{
SqlConnection cn = new SqlConnection(System.Confi
cn.Open();
SqlCommand cmd = new SqlCommand(lc_cmd, cn);
SqlDataReader dr = cmd.ExecuteReader();
cn.Close();
return dr;
}
It still works.
protected SqlDataReader sql_read(string lc_cmd)
{
SqlConnection cn = new SqlConnection(System.Confi
cn.Open();
SqlCommand cmd = new SqlCommand(lc_cmd, cn);
SqlDataReader dr = cmd.ExecuteReader();
cn.Close();
return dr;
}
It still works.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A lot of good info here. I'm still processing it, and will accept shortly. Thanks!
Terry
Terry
np
Terry,
There is more background to the reason why I ended up going to the event-based model for the datareader. Without the event-based model there is a very simple way of ensuring the datareader is closed properly and that is to use a using{} block: the datareader, sqlcommand and sqlconnection all implement IDisposable, so a using{} block ensures that they are always closed properly regardless of normal execution, or an exception state.
I use a similar model to the Enterprise Library to manage my database conversations where they do in fact recommend the using{} block. However, the Enterprise Library doesn't allow for a critical scenario in our application; where the database is taken offline either through an admin statement, changing the database state from read/write to read only, or through hardware restart. In this situation it is impractical and undesirable to restart the web farm. We therefore have a .NET 1.1 workaround, which to all extents is pretty much the same as the .NET 2.0 SqlConnection.ClearPool command. In this case, implementing using{} blocks becomes a nightmare. In addition, my database classes are actual instance-based objects (not static) each with a connection property, command property, transaction property etc. This means that those objects can span multiple interactions from a consuming object. As a result, the database connection is not opened until just before the command is executed and the using block is impractical.
Apart from anything else this model allows me to enforce the rule that our developers do not use the System.Data.SqlClient objects directly. Instead they go through a "managed" (not the same as the .NET definition of managed) data access layer. They are more than happy to do this as it reduces the amount of code that needs to be written considerably and the event-based model means that readers and connections are _always_ closed properly. Without enforcing this it is possible for a piece of code to slip through to production that doesn't close it's connection. If this happens then you will eventually come across a situation where (after that code has been hit many times consecutively) your customers will see failures in your database conversations.
I'm writing an article for my web site with code examples of this method, and integration with the enterprise library in .NET 1.1 and 2.0. I'll post links when it's done.
Andy
There is more background to the reason why I ended up going to the event-based model for the datareader. Without the event-based model there is a very simple way of ensuring the datareader is closed properly and that is to use a using{} block: the datareader, sqlcommand and sqlconnection all implement IDisposable, so a using{} block ensures that they are always closed properly regardless of normal execution, or an exception state.
I use a similar model to the Enterprise Library to manage my database conversations where they do in fact recommend the using{} block. However, the Enterprise Library doesn't allow for a critical scenario in our application; where the database is taken offline either through an admin statement, changing the database state from read/write to read only, or through hardware restart. In this situation it is impractical and undesirable to restart the web farm. We therefore have a .NET 1.1 workaround, which to all extents is pretty much the same as the .NET 2.0 SqlConnection.ClearPool command. In this case, implementing using{} blocks becomes a nightmare. In addition, my database classes are actual instance-based objects (not static) each with a connection property, command property, transaction property etc. This means that those objects can span multiple interactions from a consuming object. As a result, the database connection is not opened until just before the command is executed and the using block is impractical.
Apart from anything else this model allows me to enforce the rule that our developers do not use the System.Data.SqlClient objects directly. Instead they go through a "managed" (not the same as the .NET definition of managed) data access layer. They are more than happy to do this as it reduces the amount of code that needs to be written considerably and the event-based model means that readers and connections are _always_ closed properly. Without enforcing this it is possible for a piece of code to slip through to production that doesn't close it's connection. If this happens then you will eventually come across a situation where (after that code has been hit many times consecutively) your customers will see failures in your database conversations.
I'm writing an article for my web site with code examples of this method, and integration with the enterprise library in .NET 1.1 and 2.0. I'll post links when it's done.
Andy
ASKER
It makes pretty good sense to use a managed class.
If you have enough developers, is there a reason you haven't switched to a service based connection pool? I've been too busy
to do it here, but it sounds like you're running a much larger set of applications. Without doing the background I imagine you'd have a bit more overhead in the XML, but you'd also save on opening/closing connections. Maybe it would just as efficent in the end?
If you have enough developers, is there a reason you haven't switched to a service based connection pool? I've been too busy
to do it here, but it sounds like you're running a much larger set of applications. Without doing the background I imagine you'd have a bit more overhead in the XML, but you'd also save on opening/closing connections. Maybe it would just as efficent in the end?
A connection pool service is a possibility - I take it you mean a remoting service? It isn't something I have yet looked at. I should imagine the problem with flushing the pool when the connections are invalidated would still be a problem.
The applications aren't particularly "large" in terms of usage. In one application the connections are used in the web application itself in a simple 2-tier architecture; there hasn't been any need to expand that application yet though it is layered to as to be able to do so when the need arises. Another application is more complex, with smart, rich and web clients and service-based architecture. This talks to the database in the bottom-most service layer. There is only one physical service implementation in this position at the moment and it uses a lot of datareaders to fill the data transfer objects.
Keeping smaller individual connection pools on each web service would intuitively seem to have a performance advantage over using a central remoting service, but there may be disadvantages that I haven't considered. You say there might be advantages on opening/closing connections? I'd be interested to hear more about that (I could open a question if you want :) ).
Andy
The applications aren't particularly "large" in terms of usage. In one application the connections are used in the web application itself in a simple 2-tier architecture; there hasn't been any need to expand that application yet though it is layered to as to be able to do so when the need arises. Another application is more complex, with smart, rich and web clients and service-based architecture. This talks to the database in the bottom-most service layer. There is only one physical service implementation in this position at the moment and it uses a lot of datareaders to fill the data transfer objects.
Keeping smaller individual connection pools on each web service would intuitively seem to have a performance advantage over using a central remoting service, but there may be disadvantages that I haven't considered. You say there might be advantages on opening/closing connections? I'd be interested to hear more about that (I could open a question if you want :) ).
Andy
public DataSet ACMBFill(SqlCommand SQLCmd, string tableName)
{
bool ManageConnection = false;
DataSet ReturnSet = new DataSet();
if( SQLCmd != null)
{
try
{
if (SQLCmd.Connection.State != ConnectionState.Open)
{
SQLCmd.Connection.Open();
ManageConnection = true;
}
SqlDataAdapter da = new SqlDataAdapter(SQLCmd);
da.Fill(ReturnSet, tableName);
da = null;
}
finally
{
if (ManageConnection)
SQLCmd.Connection.Close();
}
return ReturnSet;
}
return null;
}
Basically the Finally statement is important because it will run after the try and catch section no matter what happens which makes sure the connection gets closed.
I have a bool value to check whether or not my connection is open already and if it is then just use that one.
I send the SqlCommand object to the function, that is set up as required in my business layer.
Let me know if you need some more help...