Question Getting a SQL Connection object from a Db class to avoid repeating code

[FisherRon]

I am trying to consolidate code that is always the same by putting it in a DbManager class. The problem is that I'm not sure if doing this with a SQL Connection object is working properly.
This is the code I'm getting the connection for:

[COLOR=blue]public[/COLOR] [COLOR=blue]bool[/COLOR] CheckIfChildRecordsExist([COLOR=blue]string[/COLOR] tableName, [COLOR=blue]string[/COLOR] idNameValue, [COLOR=blue]string[/COLOR] idName)
		{
			[COLOR=blue]bool[/COLOR] exists [COLOR=#800040]=[/COLOR] [COLOR=blue]true[/COLOR]; [COLOR=green]// default to true so we don't inadvertantly try to delete child records...[/COLOR]
			[COLOR=#2b91af]StringBuilder[/COLOR] query [COLOR=#800040]=[/COLOR] [COLOR=blue]new[/COLOR] [COLOR=#2b91af]StringBuilder[/COLOR]();
			[COLOR=blue]string[/COLOR] paramName [COLOR=#800040]=[/COLOR] [COLOR=#a31515]"@"[/COLOR] [COLOR=#800040]+[/COLOR] idName;
			[COLOR=blue]int[/COLOR] recordCount [COLOR=#800040]=[/COLOR] [COLOR=#5f5f5f]0[/COLOR];
 
			query[COLOR=#800040].[/COLOR]Append([COLOR=#a31515]"SELECT COUNT(*) FROM "[/COLOR]);
			query[COLOR=#800040].[/COLOR]Append(tableName);
			query[COLOR=#800040].[/COLOR]Append([COLOR=#a31515]" WHERE "[/COLOR]);
			query[COLOR=#800040].[/COLOR]Append(idName);
			query[COLOR=#800040].[/COLOR]Append([COLOR=#a31515]" = "[/COLOR]);
			query[COLOR=#800040].[/COLOR]Append(paramName);
 
			[COLOR=blue]using[/COLOR] ([COLOR=#2b91af]SqlCommand[/COLOR] cmd [COLOR=#800040]=[/COLOR] [COLOR=blue]new[/COLOR] [COLOR=#2b91af]DBManager[/COLOR]()[COLOR=#800040].[/COLOR]GetDbSqlQueryCommand(query[COLOR=#800040].[/COLOR]ToString()))
			{
				cmd[COLOR=#800040].[/COLOR]Parameters[COLOR=#800040].[/COLOR]AddWithValue(paramName, idNameValue);
				cmd[COLOR=#800040].[/COLOR]Connection[COLOR=#800040].[/COLOR]Open();
				recordCount [COLOR=#800040]=[/COLOR] ([COLOR=blue]int[/COLOR])cmd[COLOR=#800040].[/COLOR]ExecuteScalar();
				cmd[COLOR=#800040].[/COLOR]Connection[COLOR=#800040].[/COLOR]Close();
			}
 
			[COLOR=blue]if[/COLOR] (recordCount [COLOR=#800040]>[/COLOR] [COLOR=#5f5f5f]0[/COLOR])
			{
				exists [COLOR=#800040]=[/COLOR] [COLOR=blue]true[/COLOR];
			}
			[COLOR=blue]else[/COLOR]
			{
				exists [COLOR=#800040]=[/COLOR] [COLOR=blue]false[/COLOR];
			}
 
			[COLOR=blue]return[/COLOR] exists; 
		}

This is the code in the DbManager class that returns a SQL Command object to be used in the previous code:

[COLOR=blue]public[/COLOR] [COLOR=#2b91af]SqlCommand[/COLOR] GetDbSqlQueryCommand([COLOR=blue]string[/COLOR] query)
		{
			[COLOR=#2b91af]SqlCommand[/COLOR] cmd [COLOR=#800040]=[/COLOR] [COLOR=blue]new[/COLOR] [COLOR=#2b91af]SqlCommand[/COLOR](query, [COLOR=blue]new[/COLOR] [COLOR=#2b91af]SqlConnection[/COLOR](ConnectionString));
			cmd[COLOR=#800040].[/COLOR]CommandType [COLOR=#800040]=[/COLOR] System[COLOR=#800040].[/COLOR]Data[COLOR=#800040].[/COLOR][COLOR=#2b91af]CommandType[/COLOR][COLOR=#800040].[/COLOR]Text;
			[COLOR=blue]return[/COLOR] cmd;
		}

It seems to work OK, but I think I may have a problem. I'm not sure if the SqlConnection is actually being closed or if I'm leaving it open out there. There are lots of methods for accessing data and I was just trying to consolidate some of the data access code into a wrapper type class.

Does getting the connection for the command this way leave a connection out there. If I'm working on lots of records at the same time, I can get a SQL Exception (time out or connection pool problems).

Can anyone tell me if this actually closes the connection or not?

Thanks,

Ron

 

[jmcilhinney]

You are creating a new SqlConnection object for each SqlCommand object. That's not necessarily an issue though. SqlConnection objects are actually relatively lightweight, with the actual database connection existing at a lower level. ADO.NET uses connection pooling so, if you create two or more SqlConnection objects with the same connection string, they will actually use the same database connection under the hood. It's only going to be an issue if you specifically want two commands to use the same SqlConnection object, e.g. you want to enlist them both in a transaction. With that in mind, you might overload GetDbSqlQueryCommand and add the ability to pass in an existing connection object.

You are closing the connection too, which is good. If that code wasn't closing the connection then it wouldn't be opening it either. If you're seeing connection pooling issues then it might be that you're exhausting the pool for that connection string before the garbage collector has a chance to clean up all the connection objects you've created. One option might to create a single connection object for each instance of your DbManager class and then use that same connection internally each time you create a command. That way, you'll only create a new connection when you create a new DbManager. That would mean not creating a new DbManager every time you create a command though, or you won't gain anything.

 

[FisherRon]

Thanks for your reply. I tried to find the answer to this by searching, but wasn't able to get it. I didn't know that 2 connection objects with the same connection string would share the connection. I also wasn't sure about if the connection object was getting closed properly with this code, but I thought it was.
The idea to provide 1 connection object for the DB Manager class is a good one. I will look at refactoring to make that work.

I really appreciate your quick reply.
Ron