How To Work With SQL Database Added As Item In Visual Studio 2008?

Question

If I'm letting Visual Studio take care of adding an SQL Server database to an existing project, it adds the connection string to app.config. How can I use use THAT connection string to make my own connections and datasets?

Answer 1

Use the ConfigurationManager.AppSettings to read the connection string when required.

For example, if you opening a SQL Connection, use the assign the "Connection String" property to the value retrieved from ConfigurationManager.AppSettings ("MyConnectionString")

If it is placed in the appropriate section in the app config file, then you can use ConfigurationManager.ConnectionStrings to retrieve it as well.

Read more here http://msdn.microsoft.com/en-us/library/ms254494.aspx

Answer 2

Place the connection string in your app.config then use

ConfigurationManager.ConnectionStrings[str].ConnectionString.ToString();

to get the connection string.

For example:

private string GetConnectionString(string str)
    {
        //variable to hold our return value
        string conn = string.Empty;
        //check if a value was provided
        if (!string.IsNullOrEmpty(str))
        {
            //name provided so search for that connection
            conn = ConfigurationManager.ConnectionStrings[str].ConnectionString.ToString();
        }
        else
        //name not provided, get the 'default' connection
        {
            conn = ConfigurationManager.ConnectionStrings[ConnStr].ConnectionString;
        }
        //return the value
        return conn;
    }

Then you can reference the connection using ado.net or Linq

For Example:

your app.config would contain an entry like:

<connectionStrings>
<add name="nameofConnString" connectionString="Data Source=SQLDATA;Initial Catalog=&quot;nameofdatabase&quot;;Persist Security Info=True;User ID=username;Password=password;Connection Timeout=30;Pooling=True; Max Pool Size=200" providerName="System.Data.SqlClient"/>

'

Then you could call

conStr = GetConnectionString("nameofConnString")

With Ado.net

You could then establish the connection with:

sqlConn = new SqlConnection(conStr);

sqlConn.Open();

Or with Linq

LinqData ld = new LinqData(); DataContext dataContext = new DataContext(ld.GetConnectionString(sqlConn));

where LinqData is a class that contains the GetConnectionString() method.

Answer 3

Well, both of those helped get me on the right track. I found this quite simple, yet highly annoying. The solution I used was:

1. using System.Configuration;
2. add a reference System.configuration
3. create a new connection with SqlConnection(ConfigurationManager.ConnectionStrings["MyDatabaseConnectionFromApp.Config"].ConnectionString)