Best way to establish a connection for use with an ADO.NET command object

Question

I'm designing a web service in ASP.NET and VS2008, and am using typed datasets for retrieving table data. These work well and establish their own connections through their associated TableAdapter objects. Edit: I'm using VB, BTW!

I am now attempting to run a custom SQL string using a DataAdapter and a Command object, however I need to reference a Connection object in order for the Command to work. What is the best way to handle this? Should I:

a) Create a global connection object using Global.asax, retrieving the connection string from web.config? (I've been trying that one already, with not much success)

b) Create a class-level connection object using the InitialiseComponent method, also retrieving the ConnectionString from web.config?

c) Retrieve a Connection from one of the TableAdapters that I've already created in my typed DataSets?

d) Something else I haven't thought of yet?

BTW I've been finding it very difficult to extract a ConnectionString from web.config, so any help with that would be appreciated also!

I'm not entirely inexperienced with ASP.NET, but my last big project used VS2003, and I want to make sure that I'm using the current tools correctly.

Answer 1

To extract the connection string, use

WebConfigurationManager.ConnectionStrings["name"].ConnectionString

It's best to open and close the connections as close as possible to their use. ADO.NET will do connection pooling so that this won't be expensive:

var connectionString = 
    WebConfigurationManager.ConnectionStrings["name"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("query", conn))
    {
        conn.Open();

        // Use the command
    }
}

Answer 2

For Connection and data access problems I will advise you to go with some kind of Data Helpers like Microsoft Data Access Application Block

Here you can find small tutorial about how to use it.

For getting connectionstring from web.config use folowing methods

 public static string GetConnectionString( string strConstringKey )
        {
            return ConfigurationManager.ConnectionStrings[strConstringKey];
        }

        public static bool GetConnectionString(string strConstringKey, ref string strConstring)
        {
            return (strConstring = ConfigurationManager.ConnectionStrings[strConstringKey] ) == null ? false : true  ;
        }