A connection string is a string
consists of information needed to connect to a data store. It is passed via
code to an underlying driver or provider in order to initiate the connection. It
is commonly used to connect to a database, but you can also connect to a
spreadsheet or text file using connection string and ADO.Net. The connection
string may include attributes such as the name of the driver, server and
database, as well as security information such as user name and password.
.NET Framework provides a set of data
providers that serves as a bridge between an application and a data source. A
data provider is used to retrieve data from a data source and to reconcile
changes to that data back to the data source.
To connect to SQL Server, .Net
Framework provides three main Data Providers: SqlClient Data Provider, OleDb
Data Provider, and Odbc Data Provider. Each data provider uses different
connection strings for different types of connections.
Below is given the list of connection strings
used with different ADO.Net data providers to connect to SQL Server database:
|
ADO.Net Data Provider
|
Connection Type
|
Connection Strings
|
|
SqlClient Data Provider
|
Standard Security
|
Data Source= DatabaseServerName;Initial
Catalog=DatabaseName; User Id=myUsername;Password=myPassword;
OR
Server= DatabaseServerName; Database=DatabaseName;
User ID=myUsername; Password=myPassword; Trusted_Connection=False;
|
|
Trusted Connection
|
Data Source= DatabaseServerName;Initial
Catalog= DatabaseName;Integrated Security=SSPI;
OR
Server= DatabaseServerName;Database=myDataBase;Trusted_Connection=True;
|
|
Via IP Address
|
Network Library=DBMSSOCN; Data
Source=xxx.xxx.xxx.xxx,1433; Initial Catalog= DatabaseName; User Id=myUserName;
Password=myPassword;
|
|
OLE DB Data Provider
|
Standard Security
|
Provider=sqloledb;Data Source=DatabaseServerName;Initial
Catalog=DatabaseName;User Id=myUsername;Password=myPassword;
|
|
Trusted Connection
|
Provider=sqloledb;Data Source= DatabaseServerName;Initial
Catalog= DatabaseName;Integrated Security=SSPI;
|
|
Via IP Address
|
Driver=SQLOLEDB; Network
Library=DBMSSOCN; Data Source=xxx.xxx.xxx.xxx,1433; Initial Catalog= DatabaseName;
User id=myUserName; Password=myPassword;
|
|
ODBC Data Provider
|
Using DSN
|
Dsn=DsnName; Uid=myUserName; Pwd=myPassword;
|
|
Standard Security
|
Driver={SQL Server}; Server= DatabaseServerName;
DataBase= DatabaseName; Uid=myUserName; Pwd=myPassword;
|
|
Trusted Connection
|
Driver={SQL Server}; Server= DatabaseServerName;
DataBase= DatabaseName; Uid=; Pwd=;
OR
Driver={SQL Server}; Server= DatabaseServerName;
DataBase= DatabaseName; Trusted_Connection=Yes;
|
Sometime you may also need to
connect to an SQL Server instance. In that case, use “DatabaseServerName\InstanceName”
as the Data Source/Server in the connection string.
The syntax of specifying the server
instance in the value of the Data Source/Server key is the same for all
connection strings for SQL Server. The below is given an example of connection string to connect to an SQL Server Instance:
Data Source=DatabaseServerName\InstanceName;
Initial Catalog=DatabaseName; User Id=myUsername; Password=myPassword;