Using the Web.Config to set up my SQL database connection string?

Answer 1

Here's a great overview on MSDN that covers how to do this.

In your web.config, add a connection string entry:

<connectionStrings>
  <add 
    name="MyConnectionString" 
    connectionString="Data Source=sergio-desktop\sqlexpress;Initial 
    Catalog=MyDatabase;User ID=userName;Password=password"
    providerName="System.Data.SqlClient"
  />
</connectionStrings>

Let's break down the component parts here:

Data Source is your server. In your case, a named SQL instance on sergio-desktop.

Initial Catalog is the default database queries should be executed against. For normal uses, this will be the database name.

For the authentication, we have a few options.

User ID and Password means using SQL credentials, not Windows, but still very simple - just go into your Security section of your SQL Server and create a new Login. Give it a username and password, and give it rights to your database. All the basic dialogs are very self-explanatory.

You can also use integrated security, which means your .NET application will try to connect to SQL using the credentials of the worker process. Check here for more info on that.

Finally, in code, you can get to your connection string by using:

ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString

Answer 2

Your best bet, starting fresh like you are, is to go grab the enterprise library. They have a configuration tool you can use to wire everything up for you nicely.

They also have a data access application block which is very useful and documentation filled with good samples.

http://www.microsoft.com/downloads/details.aspx?FamilyId=90DE37E0-7B42-4044-99BE-F8ECFBBC5B65&displaylang=en

Answer 3

If you are using SQL Express (which you are), then your login credentials are .\SQLEXPRESS

Here is the connectionString in the web config file which you can add:

<connectionStrings>
<add connectionString="Server=localhost\SQLEXPRESS;Database=yourDBName;Initial Catalog= yourDBName;Integrated Security=true" name="nametoCallBy" providerName="System.Data.SqlClient"/>
</connectionStrings>

Place is just above the system.web tag.

Then you can call it by:

connString = ConfigurationManager.ConnectionStrings["nametoCallBy"].ConnectionString;

Answer 4

Add this to your web config and change the catalog name which is your database name:

  <connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=SERGIO-DESKTOP\SQLEXPRESS;Initial Catalog=YourDatabaseName;Integrated Security=True;"/></connectionStrings>

Reference System.Configuration assembly in your project.

Here is how you retrieve connection string from the config file:

System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

Answer 5

http://www.connectionstrings.com is a site where you can find a lot of connection strings. All that you need to do is copy-paste and modify it to suit your needs. It is sure to have all the connection strings for all of your needs.

Answer 6

If you use the Connect to Database under tools in Visual Studio, you will be able to add the name of the Server and database and test the connection. Upon success you can copy the string from the bottom of the dialog.