[asp.net] Setting up connection string in ASP.NET to SQL SERVER

I found this very difficult to get an answer to but eventually figured it out. So I will write the steps below.

1. Before you setup your connection string in code, ensure you actually can access your database. Start obviously by logging into the database server using SSMS (Sql Server Management Studio or it's equivalent in other databases) locally to ensure you have access using whatever details you intend to use.

2. Next (if needed), if you are trying to access the database on a separate server, ensure you can do likewise in SSMS. So setup SSMS on a computer and ensure you can access the server with the username and password to that database server.

If you don't get the above 2 right, you are simply wasting your time as you cant access the database. This can either be because the user you setup is wrong, doesn't have remote access enabled (if needed), or the ports are not opened (if needed), among many other reasons but these being the most common.

Once you have verified that you can access the database using SSMS. The next step, just for the sake of automating the process and avoiding mistakes, is to let the system do the work for you.

3. Start up an empty project, add your choice of Linq to SQL or Dataset (EF is good but the connection string is embedded inside of an EF con string, I want a clean one), and connect to your database using the details verified above in the con string wizzard. Add any table and save the file.

Now go into the web config, and magically, you will see nice clean working connection string there with all the details you need.

{ Below was part of an old post so you can ignore this, I leave it in for reference as its the most basic way to access the database from only code behind. Please scroll down and continue from step 2 below. }

Lets assume the above steps start you off with something like the following as your connection string in the code behind:

string conString = "Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;";

This step is very important. Make sure you have the above format of connection string working before taking the following steps. Make sure you actually can access your data using some form of sql command text which displays some data from a table in labels or text boses or whatever, as this is the simplest way to do a connection string.

Once you are sure the above style works its now time to take the next steps:

1. Export your string literal (the stuff in the quotes, including the quotes) to the following section of the web.config file (for multiple connection strings, just do multiple lines:

<configuration>
    <connectionStrings>
        <add name="conString" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />
        <add name="conString2" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />
        <add name="conString3" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

{ The above was part of an old post, after doing the top 3 steps this whole process will be done for you, so you can ignore it. I just leave it here for my own reference. }

2. Now add the following line of code to the C# code behind, prefrably just under the class definition (i.e. not inside a method). This points to the root folder of your project. Essentially it is the project name. This is usually the location of the web.config file (in this case my project is called MyProject.

static Configuration rootWebConfig = WebConfigurationManager.OpenWebConfiguration("/MyProject");

3. Now add the following line of code to the C# code behind. This sets up a string constant to which you can refer in many places throughout your code should you need a conString in different methods.

const string CONSTRINGNAME = "conString";

4. Next add the following line of code to the C# code behind. This gets the connection string from the web.config file with the name conString (from the constant above)

ConnectionStringSettings conString = rootWebConfig.ConnectionStrings.ConnectionStrings[CONSTRINGNAME];

5. Finally, where you origionally would have had something similar to this line of code:

SqlConnection con = new SqlConnection(conString)

you will replace it with this line of code:

SqlConnection con = new SqlConnection(conString.ConnectionString)

After doing these 5 steps your code should work as it did before. Hense the reason you test the constring first in its origional format so you know if it is a problem with the connection string or if it is a problem with the code.

I am new to C#, ASP.Net and Sql Server. So I am sure there must be a better way to do this code. I also would appreicate feedback on how to improve these steps if possible. I have looked all over for something like this but I eventually figured it out after many weeks of hard work. Looking at it myself, I still think, there must be an easier way.

I hope this is helpful.