How to manage SQL connections with utility class?

Question

We have a utility SQL class that takes the name of the stored procedure and its input parameters, and returns the results in a datatable. The rationale for this is that we don’t have to worry about forgetting about closing the connections and leaking the connections. In addition, we can reduce code without backing up data and datareaders in our data access layers.

The problem with this is that we are populating a datatable so that we can scroll through it to create our objects, so we mainly use it as a datareader. I read about classes that will return a datareader or dataadapter. But the problem is that either the client must open and close the connections, or close the connection in the Finalize method. It seems that you do not want garbage collection to be responsible for closing database connections.

To summarize, we want to have a class so that we can shorten the code without creating datareaders for each query and so that we can ensure that database connections are closed.

What is the best way to handle this?

UPDATE:. Still thinking about it, but for now it seems like the best thing is still to return the datareader, use CommandBehavior.CloseConnection, and then trust the one who ever used the class to call dr. Close ()?

Answer 1

Have you considered the Microsoft Enterprise Library ?

public List<User> GetUsers() { List<User> result = new List<User>(); Database db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(this.connectionString); DbCommand cmd = db.GetStoredProcCommand("GetUsers"); using (IDataReader rdr = db.ExecuteReader(cmd)) { while (rdr.Read()) { User user = new User(); FillUser(rdr, user); result.Add(user); } } return result; } 

Answer 2

We use something similar, and it works very well with large volumes.

  public SqlDataReader ExecuteReader(string command, SqlParameter[] parameters) { SqlDataReader reader = null; using (SqlConnection conn = new SqlConnection()) using (SqlCommand cmd = conn.CreateCommand()) { conn.Open(); cmd.CommandText = command; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(parameters); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } return reader; } 

Data tables are not considered good practice for several reasons, including bloating and type insecurity.

Answer 3

I have the same structures - utility classes with methods that retrieve data and return populated DataTables (or populate / update the DataTable passed to them) - for the same reasons: keeping database connections separate from the rest of the code and ensuring that they were open only when necessary and closed as soon as possible. Moreover, the data is stored in different internal systems, and I want to introduce only one interface for my application and not worry about the details.

There is one difference from your situation: we do not create objects from rows in DataTables, but rather work directly with data in rows. I believe that working with DataTables is simple and efficient.

In addition, I personally do not see anything wrong with this approach and I think that it works very well for our purposes.

Answer 4

Return datareader does not work in many scenarios. In many places, direct connections to the database from a client machine are not allowed for production (for good reason). Therefore, you must serialize the objects you retrieve. I can think of a design that allows you to persist in the datareader in any class that you use for remote / serialization on the server side, but returning elements via http or nettcp in a string agonizing a string model is probably not very useful.

Do you serialize these objects? If so, your choice comes down to Datatable, Dataset, or custom objects. Custom objects, if they are well written, perform and serialize the best, but you need to write concurrency in addition to a host of other functions.

IMO, because ADO.Net 2.0, data can work well even in situations of large-scale remote interaction. They provide a special binary format for remote access and are easy to use. Drop some compression, and you don’t even use large bandwidth for large data sets.

Answer 5

Well, if you plan to use this class inside web pages, you can register a utility class with a page unload event. In the case of a receiver, you can write your own logic to close the database connection. Check out this codeproject tip for more ideas .

however, this solution will not work for use inside web methods (web services). I suppose you have to adapt the technique of using web services. Your last line in the web method should be an event call. So when you write your web service class, define an event called WebMethodCompleted . You will probably get a link to an instance of the web service using the technique mentioned in the article. After receiving help, you can register the event in your class. Just remember to trigger the event in the web method.

Happy programming.