Best way to establish a connection for use with ADO.NET command object
I am developing a web service in ASP.NET and VS2008 and am using typed datasets to retrieve table data. They work well and make their own connections through related TableAdapter objects. Edit: I'm using VB, BTW!
Now I'm trying to run a custom SQL string using the DataAdapter and Command object, however I need to reference the Connection object for the command to work. What's the best way to handle this? Should I:
a) Create a global connection object with Global.asax by extracting the connection string from web.config? (I've already tried this one, with little success)
b) Create a class level connection object using the InitialiseComponent method, also extracting the ConnectionString from the web.config?
c) Get a connection to one of the TableAdapters that I've already created in my typed datasets?
d) Anything else I haven't thought of yet?
BTW I found it very difficult to extract the ConnectionString from the web.config so any help with that would be appreciated as well!
I'm not completely inexperienced with ASP.NET, but my last big project was using VS2003 and I want to make sure I'm using the current tools correctly.
source to share
To extract the connection string use
WebConfigurationManager.ConnectionStrings["name"].ConnectionString
It is best to open and close connections as close to using them as possible. ADO.NET will do connection pooling in a way that is not 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
}
}
source to share
For data connectivity and access issues, I suggest you check out some data assistants like Microsoft Application Access Block
Here you can find a small tutorial on how to use it.
To get connection string from web.config use following 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 ;
}
source to share