A Beginners Guide to the Connection String

One of the most misunderstood items, using ASP.Net, and some of the most asked questions, are about Connection Strings – the way in which you connect to the database. Any time you connect to a database, a Connection String is required, so it’s an integral part of .Net programming, whether it’s ASP.Net or WinForms programming. Of course, here, we’re addressing only ASP.Net programming, and mainly with SQL Server.

Each Connection String has several ’sections’ in it. The basic form of the Connection String consists of:

ConnectionString=""

Your string can reside in different locations, including a DataSource Control (ASP.Net 2.0), in code, on the fly, and in the Web.Config file. Most likely, if you are using a particular string in multiple locations throughout your website, it will probably be best to store it in the Web.Config file. Then, any time, in your application, you need the connection string, the setting in the Web.Config file is referenced instead of the exact connection string. This way, if the connection string ever changes (and believe me, after years of doing this, I’m here to attest that it most likely WILL change at some time), then you will only need to change it in one place. How this is done, will be addressed, later on in this tutorial,

There are many sections of a connection string which are possible, but there are 3 basic sections which are absolutely required: The Server
The Database Name
The authentication

The Server can be notated in the Connection String several different ways, however, the most common usages are:

Data Source=YourServerGoesHere
Server=YourServerGoesHere

In the above section, instead of ‘YourServerGoesHere’, you would put the address to the Server itself, in either a domain type of construction (like:MyServer.Com), or an IP address (like: 198.12.1987).

So, at this point, using the Basic form from above, this would look like:

ConnectionString="Data Source=YourServerGoesHere"
or ConnectionString="Data Source=YourServerGoesHere"

Naturally, the above, as mentioned before, can use ‘Server’ instead of ‘Data Source’

Next comes the Database section. It may be in 2 different forms:

Database=Northwind
Intial Catalog=Northwind

The different sections in the Connection String need to be separated, so in order to do this, we use a semi-colon in between the different sections “;”. The entire connection string, then, will be surrounded by double-quotes

At this point, using the Basic form from above, this would look like:

ConnectionString="Data Source=YourServerGoesHere;Initial Catalog=Northwind"
or ConnectionString="Server=YourServerGoesHere;Database=Northwind"

And, lastly, we come to the Authentication method. The two types of authentication use either Windows Authentication, or a direct access to the database, using a User ID and a Password. For Windows Authentication, the Authentication section would look something like :
Integrated Security=[true, false, yes, no, SSPI]
Trusted Connection=[true, false, yes, no, SSPI]

True, False, Yes, no and SSPI are different from each other, and your choice to make. SSPI is equivalent to ‘true’, and is the generally recommended when using Windows Authentication. When set to ‘false’, it is required that you use a User ID and Password in the connection string. Also, a point to make here, is that, if it is set to ‘false’, it’s not really required that you put it in the connection string at all.

Now, we come to the User ID and Password parts. These, of course, are the User ID and Password required, when logging into the Server. The form can either be ‘User ID’ or ‘uid’, and either ‘Password’ or ‘pwd’. Case sensitivity does not matter here.

At this point, using the Basic form from above, this would look like:

ConnectionString="Data Source=YourServerGoesHere;Initial Catalog=Northwind;User ID=YourUserNameGoesHere;Password=YourPasswordGoesHere"
or ConnectionString="Server=YourServerGoesHere;Database=Northwind;uid= YourUserNameGoesHere;pwd= YourPasswordGoesHere"

One thing which is not necessary, but is commonly asked about on the Net is the Connection Timeout. Sometimes, with more resource intensive queries, it might be necessary to lengthen the time it takes to complete the query, before it stops and returns an error. To do this, add a Connection Timout section to the Connection String. It uses seconds as an argument, and can use either of the following forms:

Connection Timeout=120
Connect Timeout=120

Now, to store this Connection String in the Web.Config file, inside the configuration section, locate (if available) or create (if not available) a ‘connectionStrings’ section:

<connectionStrings>
</connectionStrings>

Then, put a new line in this section, referencing your Connection String:

<add name=" ConnStringName" connectionString="server=YourServer;uid=YourUID;pwd=YourPassword;database=Northwind" providerName="System.Data.SqlClient" />

Then, in your code, you would reference this, as in the following:

Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("YourConnStringName").ConnectionString)

In a DataSource Control, you would reference it like the following:

ConnectionString="<%$ ConnectionStrings:YourConnStringName %>">

There are many other things you can add in, and, if you would like to explore it further, you are invited to visit this MSDN link: MSDN Connection Strings As you can see, when you break it down, Connection Strings can be very easy to understand. I hope this tutorial has explained it in a way that it is not confusing and very helpful

Related Posts:

• No Related Posts