. NET SQL Server Connection string syntax

. NET SQL Server Connection string syntax

The connectivity of the database has evolved into a standard aspect of application development. The database connection string is now a standard requirement for each project. I found myself in order to find the syntax that I need, I often have to copy the connection string from another application or perform a search. This is especially true when interacting with SQL Server because it has too many connection string options. Now let's look at the many aspects of the connection string.

Connection string

During object instantiation or establishment, the database connection string is passed to the necessary objects through properties or methods. The format of the connection string is a semicolon-delimited list of key/value parameter pairs. Listing a includes an example in C # that shows how to connect to SQL Server using the method of creating the SqlConnection object (the actual connection string is assigned through the object's ConnectionString property). The version of VB.net is included in List B.

Reference content:

String cString = "Data source=server;initial catalog=db; User id=test; Password=test; ";

Sqlconnectionconn = new SqlConnection ();

Conn. ConnectionString = cString;

Conn. Open ();

Reference content:

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, as well as the user name and password required to access the database. However, this format does not apply to all database interactions, it does have many options available, many of which have synonyms. Along with elements such as data source, Initial catalog (initial catalog), User ID (userid), and password (password), the following options are available:

* Application Name (application name): The name of the application. If not specified, it has a value of. NET SqlClient data Provider.

* attachdbfilename/extended Properties (extended attribute)/initial file name (initial file name): The name of the primary file that can connect to the database, including the full path name. The database name must be specified with the keyword database.

* Connect Timeout (connection timeout)/connection timeout (connection timeout): The length of time (in seconds) that a connection to the server waits before terminating, with a default value of 15.

* Connection Lifetime (Connection Life time): When a connection is returned to the connection pool, its creation time is compared to the current time. If the time span exceeds the validity of the connection, the connection is canceled. Its default value is 0.

* Connection Reset (Connection reset): Indicates whether a connection is reset when it is removed from the connection pool. A pseudo-effective when a connection is obtained there is no need to perform an additional server back and forth, its default value is true.

* Current Language (currently language): The name of the SQL Server language record.

* Data Source (DataSource)/server (server)/address (address)/addr (address)/network address (Network): The name or network address of the instance of SQL Server.

* Encrypt (encryption): When the value is true, if the server has an authorization certificate installed, SQL Server uses SSL encryption for all data transferred between the client and the server. The accepted values are true (TRUE), False (pseudo), yes (yes), and no (NO).

* Enlist (Registration): Indicates whether the connection pooling program automatically enlists the connection in the context of the current transaction in which the thread was created, and its default value is true.

* Database/initial Catalog (initial catalog): The name of the database.

[b]* Integrated Security (Integrated Secure)/trusted Connection (trusted connection): Indicates whether Windows authentication is used to connect to the database. It can be set to a true, pseudo, or an SSPI with true equivalence, and its default value is pseudo.

* Max Pool size (maximum connection pool capacity): The maximum number of connections allowed for a connection pool, with a default value of 100.

* min Pool size (minimum size of connection pool): The minimum number of connections allowed for the connection pool, with a default value of 0.

* Network library/net (network): A network library that is 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 dynamic link library for the protocol must be installed to the appropriate connection with the default value of TCP/IP.

* Packet size (packet size): The amount of network packets used to communicate with the database. Its default value is 8192.

* Password (password)/pwd: the password corresponding to the account name.

* Persist Security Info (keep safe information): used to determine if the security information is available after the connection has been established. If the value is true, data that is sensitive to security, such as the user name and password, is available and is not available if the value is pseudo. Resetting the connection string will reconfigure the values of all connection strings, including passwords. Its default value is pseudo.

* Pooling (Pool): Determines whether connection pooling is used. If the value is true, the connection is obtained from the appropriate connection pool, or, if necessary, the connection is created and then added to the appropriate connection pool. Its default value is true.

* User ID (username): The name of the account used to log in to the database.

* Workstation ID (Workstation ID): the name of the workstation that is connected to SQL Server. The default value is the name of the local computer.

The following connection string establishes a connection to the Northwind database on the TEST\DEV1 server with a trusted connection and a specified login certificate (less secure than the Administrator password):

Reference content:

Server=test\dev1;database=northwind; User Id=sa;

password=; Trusted_connection=true;

The next connection string uses TCIP/IP and a specified IP address:

Reference content:

Data source=192.162.1.100,1433; Network LIBRARY=DBMSSOCN;

Initial Catalog=northwind; User Id=sa; password=;

The options you use can be easily included in the connection string, but they still depend on your application and its requirements. It's good to know what's available, so you can use it properly.

Using ADO 2.0

ADO 2.0 for each one. The NET Framework's data provider introduces a new connection string generator. The keywords are listed as attributes, making the connection string syntax effective before committing to the data source. There are also new classes that make it easy to store and retrieve connection strings in a configuration file and encrypt them in a protected manner.

More or less, exactly what you need.

Pass. NET application to connect to SQL Server provides a variety of options for specific connection parameters and other options. It can be as simple as specifying a database, server, and login credentials, or as complex as setting up a buffered connection pool and security options. ADO 2.0 does allow for greater flexibility in connection string options through database classes. You just need to use the options required by the application, regardless of the others. Also, if you find yourself in a bind, remember not to concatenate string syntax, you can check the following URL connectionstrings.com.

. NET SQL Server Connection string syntax