Published: X0NE | Published: November 01, 2004, 15:04
In various application development of the database, the connection database is the first step in database application development, and it is also the most important step. For different databases, their connection modes are different, and the corresponding connection strings are different. Programmers may have such experiences, sometimes I don't know how to write or write the connection string to the database, causing the database that cannot be properly accessed. Of course, many programming tools can directly generate the correct connection string through visual interface, but the specific meaning of each parameter in the string is not clear, often confused. This article lists the connection string required for different connection methods for most common databases, so that the programmer reference!
SQL Server
· ODBC
o Standard security:
"Driver = {SQL Server}; server = aron1; data = pubs; uid = sa; pwd = asdasd;"
1) When the server is local when the server is local; local;
"Driver = {SQL Server}; server = (local); database = pubs; uid = sa; pwd = asdasd;"
2) When connecting the remote server, you need to specify an address, port number, and a network library.
"Driver = {SQL Server}; server = 130.120.110.001; address = 130.120.110.001, 1052; network = dbms SOCN
Database = PUBS; UID = SA; PWD = asdasd; "
Note: The Address parameter must be an IP address, and must include a port number.
o Trusted Connection: (Microsoft Windows NT integrates security)
"Driver = {SQL Server}; server = aron1; database = pubs; trusted_connection = yes;"
or
"Driver = {SQL Server}; server = aron1; database = pubs; uid =; pwd =;"
o Popked the username and password dialog box when connecting:
Conn.properties ("prompt") = adpromptalways
Conn.open "driver = {SQL Server}; server = aron1; database = public;"
· OLE DB, OLEDBCONNECTION (.NET)
o Standard security:
"Provider = sqloledb; data source = aron1; initial catalog = pubs; user ID = sa; password = asdasd;"
o Trusted Connection:
"Provider = sqloledb; data source = aron1; initial catalog = pubs; integrated security = SSPI;
(If connected to a specific named SQLServer instance, using Data Source = Servere NameInstance Name; but only for the SQLServer2000), for example: "Provider = sqloledb; Data Source = MyServerNameMyInstanceName; Initial Catalog = MyDatabaseName; User Id = MyUsername; Password = MyPassword "" O connection pops up the username and password dialog box:
Conn.Provider = "sqloledb"
Conn.properties ("prompt") = adpromptalways
Conn.open "Data Source = aron1; initial catalog = pubs;"
o Connect to the IP address:
"Provider = SQLOLEDB; DATA SOURCE = 190.190.200.100, 1433; network library = dbmssocn; initial catalog = pubs; user ID = sa; password = asdasd;"
(DBMSSOCN = TCP / IP instead of NAMED PIPES, the end number required for Data Source is required (default 1433)))
SqlConnection (.NET)
o Standard security:
"Data Source = aron1; initial catalog = pubs; user ID = sa; password = asdasd;"
or
"Server = aron1; database = pubs; user ID = sa; password = asdasd; trusted_connection = false"
(The result of these two connection strings is the same)
o Trusted Connection:
"Data Source = aron1; initial catalog = pubs; integrated security = sspi;"
or
"Server = aron1; database = pubs; trusted_connection = true;"
(The result of these two connection strings is the same)
(You can use ServerNameInstanceName to replace Data Source, take the value of a specific SQLServer instance, but only for SQLServer2000)
o Connect to the IP address:
"Data Source = 190.190.200.100, 1433; network library = dbmsoCN; Initial catalog = pubs; user ID = sa; password = asdasd;"
(DBMSSOCN = TCP / IP instead of NAMED PIPES, the end number required for Data Source is required (default 1433)))
o SqlConnection connection declaration:
C #:
Using system.data.sqlclient;
SqlConnection SQLCONN = New SqlConnection ();
Sqlconn.connectionstring = "my connectionstring";
Sqlconn.open ();
VB.NET:
Imports system.data.sqlclient
DIM SQLCONN AS SQLCONNECTION = New SqlConnection () Sqlconn.connectionstring = "My Connectionstring"
Sqlconn.open ()
· Data Shape
o MS Data Shape
"Provider = msdatashape; data provides = sqloledb; data source = aron1; initial catalog = pubs; user ID = sa; password = asdasd;"
· More
o How to define which protocol to use
§ Example:
"Provider = SQLOLEDB; DATA SOURCE = 190.190.200.100, 1433; network library = dbmssocn; initial catalog = pubs; user ID = sa; password = asdasd;"
Name Network Agreement Library
DBNMPNTW WIN32 NAMED PIPES
DBMSSOCN WIN32 WINSOCK TCP / IP
DBMSSPXN WIN32 SPX / IPX
DBMSVINN WIN32 Banyan Vines
DBMSRPCN WIN32 MULTI-Protocol (Windows RPC)
§ important hint
Use the following syntax when connecting through the SQLOLEDB provider:
Network library = dbmsocco
However, the following syntax is used by the MSDasql provider:
Network = dbmssocn
o All SQLConnection connection string properties
§ The following table shows all the connection string properties of the ADO.NET SQLConnection object. Most of the properties are also used in ADO. All attributes and descriptions come from MSDN.
Name Default Value Description
Application Name Application Name or when the application is not provided, a .NET SQLCLIENT data provider
AttachDbFileName or Extended Properties or the name of the primary file name, including the full path to the associated database. The database name must be specified by keyword 'database'.
Connect TimeOut or Connection Timeout 15 When the connection request is aborted, wait for the server to wait for the server to connect (in seconds)
Connection Lifetime 0 When a connection returns to the connection pool, the current time and connection creation time, if the time period exceeds the specified connection time, this connection is destroyed. It is used to force load balancing between running servers and ready-to-line servers in aggregate settings.
Connection Reset 'True' When the connection is removed from the connection pool, it is decided to reset the database connection. When set to 'false', it is used to avoid the additional server to get a connection.
CURRENT LANGUAGE SQL Server Language Record Name
Data Source or Server or Addr or Addr or NetWork Address's name or network address of the SQL Server instance to connect
Enlist 'true' is true, the connection pool automatically lists the connections in the current transaction context of the thread.
Initial Catalog or Database Database Name
Integrated Security or Trusted_Connection 'false' is connected to a trust connection. Its value is 'True', 'false' and 'sspi' (equal to 'TRUE'). Max Pool Size 100 The maximum number of connections allowed in the pool
MIN POOL SIZE 0 Connect the minimum connection allowed in the pool
The Network library or net 'dbmsoCN' network library is used to establish a connection with an SQL Server instance. The value includes DBNMPNTW, DBMSRPCN (Multi-Protocol), DBMsadSn (Apple Talk), DBMSGNET (VIA), DBMSIPCN (Shared Memory), and DBMSSPXN (IPX / SPX), and DBMSSOCN (TCP / IP). The corresponding dynamic link library must be installed. If you don't specify a network, you will use a local server (for example, ",", "), will use Shared Memory
Packet size 8192 network package byte size with an instance of SQL Server
Password- or PWD SQL Server account login password
Persist security info 'false' is set to 'false', when the connection has been turned on or is in an open state, the sensitivity security information (such as password) does not return a part of the connection as a connection.
Pool 'true' is true, remove the SqlConnection object from the appropriate connection pool, or create a SQLConnection object if necessary and increase it to the appropriate connection pool.
User ID SQL Server Login User
WorkStation ID The Local Computer Name Connect to SQL Server Workstation Name
§ Note:
Separate each attribute using a semicolon
If a name is more than twice, the value in the last occurrence of the connection string will be used.
If you build a connection string by using the value of the field in the application, you must ensure that the user will not insert an additional genus through another value in the user value.
Sex change the connection string.
Access · ODBC O standard connection ("driver = {Microsoft Access Driver (* .mdb)}; dbq = c: mydatabase.mdb; uid = admin; pwd =;" O group (system database) connection (Workgroup : The two methods are: in the connection string or specify the username and password "Driver = {Microsoft Access Driver (* .mdb)}; dbq = c: mydatabase.mdb; systemdb = c: mydbase.mdw; "," admin "," "or if (pdb.open (" driver = {microsoft access driver (* .mdb)}; dbq = c: mydatabase.mdb; systemdb = c: mydatabase.mdw; ",", "Databaseuser", "DatabasePass")) {dosomething (); pdb.close ();} O exclusive: "driver = {Microsoft Access Driver (* .mdb)}; dbq = c: myDatabase.mdb; Exclusive = 1; uid = admin; pwd = "· OLE DB, OLEDBCONNECTION (.NET) O standard connection (" provike = microsoft.jet.Oledb.4.0; data source = somepathmydb.mdb; user ID = admin Password =; "O group connection (system database)" provider = microsoft.jet.OLEDB.4.0; data source = somepathmydb.mdb; Jet OLEDB: System Database = system.mdw; "," admin "," "O Password connection: "provider = microsoft.jet.Oledb.4.0; Data Sou RCE = SomePathmydb.mdb; Jet OLEDB: Database Password = MyDbpassword; "," admin "," "· Oracle · ODBC O new version:" driver = {microsoft odbc for oracle}; server = oracleserver.world; uid = username; PWD = asdasd; "O old version:" driver = {microsoft odbc driver for oracle}; connectionString = oracleserver.world; uid = myuse; "PWD = mypassword;"
