Single database & multiple functions in web services.

[Kiran123]

Hi,

In Web Method I want to share the database among the functions using SQL connection & ODBC connection along with the using structure. Now some functions need to update the database already accessed & opened by the web method.
Now the problem is when the database is open the functions cant see the database & if i want the functions to connect to the database then do I need to create new connection for the same database? IS this possible?

[AmolP]

Well do you know the ADO.net feature from VB.Net?
With Adaptor it establishes connection with the database & creates a virtual image of the database & all the queries are executed on it and as soon as the connection is closed all the changes are saved to the actual database.
Now this is what you need. where it is possible.

[Katty]

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/g...tring-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.