Connection Strings

A connection string may be a series of name/value pairs separated by punctuation that indicate the settings for connecting to a database. Such settings embody the situation of the database, authentication, security, and therefore the name of the info to attach to. connection strings for various information sources aren’t precisely the same. for example, OLE DB ANd ODBC need an OLE DB and ODBC driver to be laid out in their connection strings. The connection classes of the data suppliers offer a ConnectionString property that you simply will use to specify the connection string for connecting to a data. Here is AN example of a connection string for connecting to a SQL Server express data source:

Data Source=.\SQLEXPRESS;Initial Catalog=University; Integrated Security=SSPI;

Figure 1 – A connection string example

For a list of connection strings for different providers, you can go to the following site:
http://connectionstrings.com

The following table is the basic connection string parameters that you can use when building your connection string. Note that not all parameters are the equivalent for each data source.

Parameter Description
AttachDBFileName / Initial File Name Used only if you want to connect to an attachable database file (for example, an .mdf file that isn’t registered with the database system). The full version of SQL Server doesn’t support this.
Connection Timeout The length of your time (in seconds) to wait for a connection to the server before terminating the try and generating a error. Defaults to fifteen seconds ANd zero seconds represent an infinite wait. ;
Data Source / Server / Address / Add / Network Address The server name or network address of the database product to connect to. Use localhost for the current computer.
Initial Catalog / Database The database the connection will initially use.
Integrated Security / Trusted_Connection Defaults to false. When set to true or SSPI, the .NET supplier makes an attempt to attach to the data source using Windows integrated security.
Persist Security Info When set to false (the default), security-sensitive info like the password is aloof from the ConnectionString property as shortly because the connection is opened.Note that not all parameters are the equivalent for each data source.
User ID The database account user ID.
Password/Pwd The password corresponding to the User ID.

Figure 2 – Connection String Parameters

The precedent that we will appear here is connection strings for interfacing with a SQL Server information source. You can continue to the site utilizing the connection above in the event that you are finding the connection strings for other information sources, for example, Access.

When building connection strings, you need to specify the data source or the server to be used. This should be possible utilizing the Data Source or the Server parameters. The connection string appeared beneath is utilized for interfacing with a SQL Express information source.

Data Source=.\SQLEXPRESS;Initial Catalog=University;Integrated Security=SSPI;

As specified by the Data Source parameter, “.\SQLEXPRESS“, we are connecting to a server instance named SQLEXPRESS that lies within the current computer(localhost). Alternatively, you can use the word localhost instead so this is equivalent: “localhost\SQLEXPRESS”. You can likewise indicate the server or the PC name as the information source pursued by the server example. For example, suppose that the server we are connecting to is named MyServer and we want to connect to the SQLEXPRESS server instance, then the following connection string can be used.

Data Source=MyServer\SQLEXPRESS;Initial Catalog=University; Integrated Security=SSPI;

You can also use the Server parameter instead of the Data Source which does the same thing.

"Server=.\SQLEXPRESS;Initial Catalog=University; Integrated Security=SSPI"

We in like manner need to decide the initialing database to be used. The Initial Catalog or Database parameter shows the initiating database to utilize. You can change the database during runtime by calling the DbConnection.ChangeDatabase method or executing the SQL command “USE DATABASE <Database Name>” where <Database Name> is the name of the database that will supplant the present database. Later lessons will show how to execute non-query SQL commands such as CREATE, UPDATE, and DELETE. Alternatively, you can use the Database parameter instead.

Data Source=.\SQLEXPRESS;Database=University;Integrated Security=SSPI;

The connection string also requires authentication and security information. Integrated Security was set to SSPI (Security Support Provider Interface) signifying we will be using the credentials of the current windows user.

Data Source=.\SQLEXPRESS;Initial Catalog=University; Integrated Security=SSPI;

Alternatively, you can use the Trusted_Connection parameter instead and set it to True.

Data Source=.\SQLEXPRESS;Initial Catalog=University; Trusted_Connection=True;

You can change this to a specific database user by replacing Integrated Security with the User Id and Password parameters.

Data Source=.\SQLEXPRESS;Initial Catalog=University;
 User Id=database_user;Password=the_password;

When using SQL Server Express, we can also attach a database file on a local SQL server instance. We can utilize the AttachDbFilename to do only that.

Data Source=.\SQLEXPRESS;AttachDbFileName=C:\Data\University.mdf;
 Database=University; Trusted_Connection=True;

We specified the path of the database file that we will use. If a Data Directory is defined, then you can use the |DataDirectory| instead.

Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|University.mdf;
 Database=University; Trusted_Connection=True;

ConnectionStringBuilder

.NET also provides the ConnectionStringBuilder class for each of the data providers. ConnectionStringBuilder classes inherit from the DbConnectionStringBuilder base class. The accompanying demonstrates the distinctive kinds of the ConnectionStringBuilder class.

Provider ConnectionStringBuilder Class
SQL Server SqlConnectionStringBuilder
OLE DB OleDbConnectionStringBuilder
ODBC OdbcConnectionStringBuilder

Figure 3 – ConnectionStringBuilder Classes

We will use SQL Server provider as our example. We can utilize the SqlConnectionStringBuilder class to make SQL Server association strings.

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = @".\SQLEXPRESS";
builder.IntegratedSecurity = true;
builder.InitialCatalog = "Northwind";

ConnectionStringBuilder contains properties which correspond to the different parameters of a connection string. Each kind of the ConnectionStringBuilder has distinctive arrangements of properties since connection strings for various information sources are unique. You can then access the connection string built by the ConnectionStringBuilder using the ConnectionString property.

string connectionString = builder.ConnectionString;

There are more advanced parameters that you can use when building connection strings. Just the fundamental ones were acquainted herewith give you a basic information for building connection strings.