sharing a db connection among functions in web service

[cj2]

I have a web service, the WebMethod starts off using a sql connection
and a odbc connection--with the using structure. This WebMethod calls a
couple functions as part of it's processing. Some of the functions it
calls need to update tables in the same databases opened by the
WebMethod. The problem is that the functions can't see the database
connections since they were opened inside the WebMethod.

Should I create new connections in these functions using a using
structure in them too. Or, should I stop using the using structure and
create my database connections in the class above the WebMethod?

 

[Steven Cheng]

Hi Cj,

.NET ADO.NET uses a pool based connection management pattern by default.
Thus, each time you create a new connection, it retrieve a physical db
connection from the connection pool and drop the connection back to pool
after you close it. Therefore, the recommended means for using connection
is create it when necessary and close it as early as possible(right after
you finish using it). So I think use a using block(if possible) around the
code block that will use the connection is a good idea.

Here is some reference about ADO.NET connection pool and management tips:

#Connection Pooling and Connection Management
http://www.dbazine.com/sql/sql-articles/charran2

#ADO.NET Connection Pooling Explained
http://www.ondotnet.com/pub/a/dotnet/2004/02/09/connpool.html

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.


--------------------

 

[cj2]

Ok. I broke my code into functions more to break out specific distinct
actions the code does for readability and maintenance purposes mostly.
What bothered me is repeating the same code to create the connection in
each function. Each time I have to type the same connection string etc
and if I had to change that sting I'd have to change it in each
function. I could put the connection string in a variable at the top
but I'd still be repeating the code which makes it longer when reading
through it. I just like my code compact.

My colleagues don't use using. They are opening the connections at the
top of the program, above the web method. I guess this is still refered
to it as a global declaration even if it's declared private. That way
it's available to all functions, subroutines etc in that class. The
problem is they have to make sure they close the connection--I think so
anyway--even if the code hits an exception and bombs in the most
unlikely and unplanned for place. I wanted to use using so I could be
insured that my connections are not left open and I think it's the new
way of doing things. Your thoughts?

 

[Andrew Morton]

Ok. I broke my code into functions more to break out specific
distinct actions the code does for readability and maintenance
purposes mostly. What bothered me is repeating the same code to
create the connection in each function. Each time I have to type the
same connection string etc and if I had to change that sting I'd have
to change it in each function. I could put the connection string in
a variable at the top but I'd still be repeating the code which makes
it longer when reading through it. I just like my code compact.

You could put the connection string in a settings file.

Andrew

 

[cj2]

Yes, perhaps you can point me to a good article on creating setting
files. In the past I've made xml files that I created on c:\ for
instance. They work but surely there is a better way of saving settings.

 

[Steven Cheng]

Hi Cj,

As for "using", you can also use a "try...catch...finally" block to replace
it. For example:

===================
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim conn As SqlConnection = Nothing

Try
conn = New
SqlConnection(ConfigurationManager.ConnectionStrings("myConnectionString").C
onnectionString)

'......

Catch ex As Exception

Finally

conn.Close()
End Try

End Sub
==================

the "finally" block will help ensure the connection get closed even there
occurs exception(the same as using).

Also, here I use "ConfigurationManager.ConnectionStrings" collection to
retrieve connectionstring info(instead of hard code it in each function).
And the actual connecction string is defined in app.config as below:

=-====================
<?xml version='1.0' encoding='utf-8'?>
<configuration>
<connectionStrings>
<clear />
<add name="myConnectionString"
connectionString="[Connection String content here]" />
</connectionStrings>
..........................
</configuration>
=====================

#Connection Strings and Configuration Files (ADO.NET)
http://msdn.microsoft.com/en-us/library/ms254494.aspx

#Getting connection string from app.config in .NET 2.0
http://shico.blogspot.com/2007/04/getting-connection-string-from.html

In addition, if you want to further centralize your code so as to avoid
duplicated connection creating code, you can encapsulate them in a utility
class. e.g.

==============
public class DBUtil

Public Shared Function GetConnection(ByVal connName As String) As
SqlConnection

Dim conn As New
SqlConnection(ConfigurationManager.ConnectionStrings(connName).ConnectionStr
ing)

Return conn

End Function

end class

=============

Then, you can call this utility function in each place you need to create
connection.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

--------------------

 

[cj2]

Ok, thanks.

Hi Cj,

As for "using", you can also use a "try...catch...finally" block to replace
it. For example:

===================
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim conn As SqlConnection = Nothing

Try
conn = New
SqlConnection(ConfigurationManager.ConnectionStrings("myConnectionString").C
onnectionString)

'......

Catch ex As Exception

Finally

conn.Close()
End Try

End Sub
==================

the "finally" block will help ensure the connection get closed even there
occurs exception(the same as using).

Also, here I use "ConfigurationManager.ConnectionStrings" collection to
retrieve connectionstring info(instead of hard code it in each function).
And the actual connecction string is defined in app.config as below:

=-====================
<?xml version='1.0' encoding='utf-8'?>
<configuration>
<connectionStrings>
<clear />
<add name="myConnectionString"
connectionString="[Connection String content here]" />
</connectionStrings>
.........................
</configuration>
=====================

#Connection Strings and Configuration Files (ADO.NET)
http://msdn.microsoft.com/en-us/library/ms254494.aspx

#Getting connection string from app.config in .NET 2.0
http://shico.blogspot.com/2007/04/getting-connection-string-from.html

In addition, if you want to further centralize your code so as to avoid
duplicated connection creating code, you can encapsulate them in a utility
class. e.g.

==============
public class DBUtil

Public Shared Function GetConnection(ByVal connName As String) As
SqlConnection

Dim conn As New
SqlConnection(ConfigurationManager.ConnectionStrings(connName).ConnectionStr
ing)

Return conn

End Function

end class

=============

Then, you can call this utility function in each place you need to create
connection.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

--------------------

Date: Tue, 27 Jan 2009 16:03:52 -0500
From: cj2 <[email protected]>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Subject: Re: sharing a db connection among functions in web service

Yes, perhaps you can point me to a good article on creating setting
files. In the past I've made xml files that I created on c:\ for
instance. They work but surely there is a better way of saving settings.