connection string syntax

The database connection string is passed to the necessary object during object instantiation or set via a property or method. The format of a connection string is a semicolon-delimited list of key/value parameter pairs.

example in C# that shows how to connect to SQL Server with the creation of a SqlConnection object :-

string cString = "Data Source=server;Initial Catalog=db;User ID=test;Password=test;";
SqlConnectionconn = new SqlConnection();
conn.ConnectionString = cString;
conn.Open();

VB.NET version:-

Dim cString As String
cString = "Data Source=server;Initial Catalog=db;User ID=test;Password=test;"

Dim conn As SqlConnection = New SqlConnection()
conn.ConnectionString = cString
conn.Open()

The connection string specifies the database server and database, along with the necessary username and password to access the database. While this format isn't appropriate for all database interactions, it does have a number of available options; many of the options have synonyms.

Along with the Data Source, Initial Catalog, User ID, and Password elements, the following options are available:

• Application Name: The name of the application. If not specified, the value is .NET SqlClient Data Provider.
• AttachDBFilename/extended properties/Initial File Name: The name of the primary file, including the full path name, of an attachable database. The database name must be specified with the keyword database.
• Connect Timeout/Connection Timeout: The length of time (in seconds) to wait for a connection to the server before terminating. The default value is 15.
• Connection Lifetime: When a connection is returned to the pool, its creation time is compared with the current time. The connection is destroyed if that time span (in seconds) exceeds the value specified by connection lifetime. The default value is zero.
• Connection Reset: Signals whether a connection is reset when removed from a pool. A false valid negates an additional server round-trip when obtaining a connection. The default value is true.
• Current Language: The SQL Server Language record name.
• Data Source/Server/Address/Addr/Network Address: The name or network address of the instance of SQL Server.
• Encrypt: When true, SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed. Recognized values are true, false, yes, and no.
• Enlist: Signals whether the pooler automatically enlists the connection in the creation thread's current transaction context. The default value is true.
• Database/Initial Catalog: The database name.
• Integrated Security/Trusted Connection: Signals whether Windows authentication is used to connect to the database. It may be set to true, false, or sspi, which is equivalent to true. The default value is false.
• Max Pool Size: The maximum number of connections allowed in the pool. The default value is 100.
• Min Pool Size: The minimum number of connections allowed in the pool। The default value is zero। OR
  

If MinPoolSize is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity. However, if the specified MinPoolSize is greater than zero, the connection pool is not destroyed until the AppDomain is unloaded and the process ends. Maintenance of inactive or empty pools involves minimal system overhead.

• Network Library/Net: The network library used to establish a connection to an instance of SQL Server. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol/RPC), dbmsvinn (Banyan Vines), dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP). The protocol's DLL must be installed to properly connect. The default value is TCP/IP.
• Packet Size: The size of the network packets (in bytes) used to communicate with the database. The default value is 8192.
• Password/Pwd: The account name's corresponding password.
• Persist Security Info: Determines whether security information is available once a connection has been established. A true value says security-sensitive data like the username and password are available, whereas false says it is not. Resetting the connection string resets all connection string values including the password. The default value is false.
• Pooling: Determines if connection pooling is utilized. A true value says the connection is drawn from the appropriate pool, or if necessary, is created and added to the appropriate pool. The default value is true.
• User ID: The account name used to log on the database.
• Workstation ID: The name of the workstation connecting to SQL Server. The default value is the local computer's name.

The following connection string connects to the Northwind database on the Test\Dev1 server using a trusted connection and the specified logon (the less than secure blank administrator password) credentials:

Server=Test\Dev1;Database=Northwind;User ID=sa;
Password=;Trusted_Connection=True;

The next connection string used TCIP/IP and a specific IP address:

Data Source=192.162.1.100,1433;Network Library=DBMSSOCN;
Initial Catalog=Northwind;User ID=sa;Password=;

The options used are easy to include in the connection string, but they will depend upon your application and its requirements. It is good to know what is available, so you can use it appropriately.