Database connection string collection

In the development of various database applications, connecting to the database is the first step in the development of database applications, and also the most important step. Different databases have different connection modes and their connection strings.
Programmers may have such experiences. Sometimes they do not know how to write or often write errors to the connection strings required to connect to the database, which leads to improper access to the database. Of course, many programming tools can directly generate correct connection strings through visual interfaces, but the specific meanings of parameters in the strings are also unclear and often confused. This article lists and describes the connection strings required for different connection methods in most common databases, so that programmers can refer to them!

SQL Server
· ODBC
O Standard Security ):
"Driver = {SQL Server}; Server = Aron1; Database = pubs; Uid = sa; Pwd = asdasd ;"
1) when the Server is local, the Server can use (local );
"Driver = {SQL Server}; Server = (local); Database = pubs; Uid = sa; Pwd = asdasd ;"
2) When connecting to a remote server, you must specify the address, port number, and network library.
"Driver = {SQL Server}; Server = 130.120.110.001; Address = 130.120.110.001, 1052; Network = dbmssocn; Database = pubs; Uid = sa; Pwd = asdasd ;"
Note: The Address parameter must be an IP Address and must contain the 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 The enter user name and password dialog box is displayed during connection:
Conn. Properties ("Prompt") = adPromptAlways
Conn. Open "Driver = {SQL Server}; Server = Aron1; DataBase = pubs ;"
· 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 you connect to a specific named SQLServer Instance, use Data Source = Servere Name \ Instance Name; but only applicable to SQLServer2000) for example: "Provider = sqloledb; Data Source = MyServerName \ MyInstanceName; initial Catalog = MyDatabaseName; User Id = MyUsername; Password = MyPassword ;"
O The enter user name and password dialog box is displayed during connection:
Conn. Provider = "sqloledb"
Conn. Properties ("Prompt") = adPromptAlways
Conn. Open "Data Source = Aron1; Initial Catalog = pubs ;"
O connect by 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 replaces Named Pipes, and the end of Data Source is the port number to be used (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 results of these two connection strings are the same)
O Trusted connection ):
"Data Source = Aron1; Initial Catalog = pubs; Integrated Security = SSPI ;"
Or
"Server = Aron1; Database = pubs; Trusted_Connection = True ;"
(The results of these two connection strings are the same)
(You can use serverName \ instanceName to replace Data Source. The value is a specific SQLServer instance, but only applicable to SQLServer2000)
O connect by IP Address:
"Data Source = 190.190.200.100, 1433; Network Library = DBMSSOCN; Initial Catalog = pubs; User ID = sa; Password = asdasd ;"
(DBMSSOCN = TCP/IP replaces Named Pipes, and the end of Data Source is the port number to be used (default: 1433 ))
O SqlConnection connection statement:
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 Provider = 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 Protocol 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
Use the following syntax when connecting through the SQLOLEDB provider:
Network Library = dbmssocn
However, the following syntax is used for connection through the MSDASQL provider:
Network = dbmssocn
O all SqlConnection connection string attributes
§ The following table shows all the connection string attributes of the ADO. NET SqlConnection object. Most of the attributes are also used in ADO. All attributes and descriptions are from msdn.
Default Name Description
Application Name or. Net SqlClient data provider when no Application is provided
The Name of the main File AttachDBFilename, extended properties, or Initial File Name, including the full path of the associated database. The database name must be specified by the keyword '''database.
Connect Timeout or Connection Timeout 15 wait for the server Connection time before terminating the Connection request and generating an error (in seconds)
Connection Lifetime 0 when a Connection is returned to the Connection pool, the difference between the current time and the Connection creation time is greater than the specified Connection survival time, the Connection is damaged. It is used to forcibly balance the load between the running server and the server to be launched in the aggregation settings.
Connection Reset ''' '''true' ''' determines whether to Reset the database Connection when the Connection is removed from the Connection pool. When it is set to '''''false''', it is used to avoid extra server reciprocating costs when the connection is obtained.
Current Language SQL Server Language Record Name
Name or Network Address of the SQL Server instance to be connected by Data Source, Server, Address, Addr, or Network Address
When Enlist '''''true' is true, the connection pool automatically lists the connections in the current transaction context of the created thread.
Initial Catalog or Database name
Whether the Integrated Security or Trusted_Connection ''' false''' connection is a trusted connection. The value is '''''true '''', '''''false''' and ''''sspi ''' (equal to '''''true '''').
Max Pool Size 100 maximum number of connections allowed in the connection Pool
Min Pool Size 0 minimum number of connections allowed in the connection Pool
Network Library or net''' dbmssocn ''' Network Library is used to establish a connection with an SQL Server instance. Values include dbnmpntw (Named Pipe), dbmsrpcn (multi-protocol), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmsipcn (shared memory), and dbmsspxn (IPX/SPX ), and dbmssocn (TCP/IP ). the connected system must install the corresponding dynamic link library. If you do not specify a network, shared memory is used when you use a local server (for example, "." or "(local )").
Packet Size 8192 bytes for network Packet communication with an instance of SQL Server
Password-or Pwd SQL Server account logon Password
Persist Security Info '''false''' is set to '''''false'''. When a connection is enabled or has been enabled, sensitive security information (such as passwords) is not returned as part of the connection.
When Pooling '''''true' is true, obtain the SQLConnection object from the appropriate connection pool, or create a SQLConnection object and add it to the appropriate connection pool if necessary.
User ID: logon User of SQL Server
Workstation ID the local computer name the name of the Workstation connecting to SQL Server
Note:
Use semicolons to separate each attribute
If a name appears more than twice, the last value in the connection string will be used.
If you construct a connection string by entering the value of a field in the application, you must ensure that the user will not change the connection string by inserting another value in the user value to an additional attribute.
Access
· ODBC
O Standard Security ):
"Driver = {Microsoft Access Driver (*. mdb)}; Dbq = C: \ mydatabase. mdb; Uid = Admin; Pwd = ;"
O group (System Database) connection (Workgroup ):
You can specify the user name and password in the connection string or in the dataset.
"Driver = {Microsoft Access Driver (*. mdb)}; Dbq = C: \ mydatabase. mdb; SystemDB = C: \ mydatabase. mdw;", "admin ",""
Or
If (pDB. open ("Driver = {Microsoft Access Driver (*. mdb)}; Dbq = C: \ mydatabase. mdb; SystemDB = C: \ mydatabase. mdw; "," "," DatabaseUser "," DatabasePass "))
{DoSomething ();
PDB. Close ();
}
Exclusive ):
"Driver = {Microsoft Access Driver (*. mdb)}; Dbq = C: \ mydatabase. mdb; Exclusive = 1; Uid = admin; Pwd ="
· Ole db, OleDbConnection (. NET)
O Standard Security ):
"Provider = Microsoft. Jet. OLEDB.4.0; Data Source = \ somepath \ mydb. mdb; User Id = admin; Password = ;"
O group connection (System database)
"Provider = Microsoft. Jet. OLEDB.4.0; Data Source = \ somepath \ mydb. mdb; Jet OLEDB: System Database = system. mdw;", "admin ",""
O password-based connection:
"Provider = Microsoft. Jet. OLEDB.4.0; Data Source = \ somepath \ mydb. 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}; ConnectString = OracleServer. world; Uid = myUsername; Pwd = myPassword ;"
· Ole db, OleDbConnection (. NET)
O Standard Security ):
"Provider = msdaora; Data Source = MyOracleDB; User Id = UserName; Password = asdasd ;"
This is the Microsoft format. The following is the Oracle format (different providers)
"Provider = OraOLEDB. Oracle; Data Source = MyOracleDB; User Id = Username; Password = asdasd ;"
Note: "Data Source =" must be set to the Net8 name based on the corresponding naming method. For example, for local naming, It is the alias in tnsnames. ora. For Oracle naming, It is the Net8 network service name.
O Trusted Connection ):
"Provider = OraOLEDB. Oracle; Data Source = MyOracleDB; OSAuthent = 1 ;"
Or set the user ID "/"
"Provider = OraOLEDB. Oracle; Data Source = MyOracleDB; User Id =/; Password = ;"
· OracleConnection (. NET)
O standard connection:
"Data Source = Oracle8i; Integrated Security = yes ";
This is only valid for Oracle 8i release 3 or later.
O OracleConnection statement:
C #:
Using System. Data. OracleClient;
OracleConnection OracleConn = new OracleConnection ();
OracleConn. ConnectionString = "my connectionstring ";
OracleConn. Open ();
VB.net:
Imports System. Data. OracleClient
Dim OracleConn As OracleConnection = New OracleConnection ()
OracleConn. ConnectionString = "my connectionstring"
OracleConn. Open ()
· Data Shape
O MS Data Shape:
"Provider = MSDataShape.1; Persist Security Info = False; Data Provider = MSDAORA; Data Source = orac; user id = username; password = mypw"
· MySQL
· ODBC
O local database:
"Driver = {mySQL}; Server = mySrvName; Option = 16834; Database = mydatabase ;"
The Sever parameter can also use localhost as its value.
O remote database:
"Driver = {mySQL}; Server = data.domain.com; Port = 3306; Option = 131072; Stmt =; Database = my-database; Uid = username; Pwd = password ;"
Meaning of Option value
1. The client cannot handle the case where MyODBC returns a column of true width.
2. The client cannot process the actual values of the affected rows returned by MySQL. If this flag is set, MySQL returns 'found rows '. MySQL 3.21.14 or later
4. Generate a debugging log in c: \ myodbc. log. This is the same as setting MYSQL_DEBUG = d: t: O, c: \ myodbc. log in 'autoexec. BAT '''.
8. No package restrictions are set for results and parameters.
16. Do not make the drive pop-up problem
32. Use or remove dynamic cursors. This is not allowed in MyODBC 2.50.
64 ignore the use of database names in '''database. table. column '''
128 force ODBC manager cursor
256 remove the use of extended values (fetch)
512 full char field full length
1024 SQLDescribeCol () function returns a column name that fully meets the conditions
2048 use the compressed server/client Protocol
4096 tell the server to ignore spaces before functions and ''' (required by PowerBuilder ). This generates all function name keywords.
8192 use a named pipe to connect to the Mysqld server running on NT
16384 change the LONGLONG column to the INTl column (some applications cannot process the LONGLONG column)
32768 return 'user' from SQLTables as Table_qualifier and Table_owner
66536 read parameters from the client and read ODBC groups from 'my. cnf '''
131072 add some additional security checks
If you need many options, you should add the above labels.
· Ole db, OleDbConnection (. NET)
O standard connection:
"Provider = MySQLProv; Data Source = mydb; User Id = UserName; Password = asdasd ;"
Data Source is the name of the MySQL database. You can also use server = localhost; DB = test
· MySqlConnection (. NET)
O eInfoDesigns. dbProvider:
"Data Source = server; Database = mydb; User ID = username; Password = pwd; Command Logging = false"
Only applicable to eInfoDesigns dbProvider and attached to. NET
O MySqlConnection statement:
C #:
Using eInfoDesigns. dbProvider. MySqlClient;
MySqlConnection MySqlConn = new MySqlConnection ();
MySqlConn. ConnectionString = "my connectionstring ";
MySqlConn. Open ();

VB.net:
Imports eInfoDesigns. dbProvider. MySqlClient
Dim MySqlConn As MySqlConnection = New MySqlConnection ()
MySqlConn. ConnectionString = "my connectionstring"
MySqlConn. Open ()
· Interbase
· ODBC and Easysoft
O local computer:
"Driver = {Easysoft IB6 ODBC}; Server = localhost; Database = localhost: C: \ mydatabase. gdb; Uid = username; Pwd = password"
O remote computer:
"Driver = {Easysoft IB6 ODBC}; Server = ComputerName; Database = ComputerName: C: \ mydatabase. gdb; Uid = username; Pwd = password"
· ODBC and Intersolv
O local computer:
"Driver = {INTERSOLV InterBase ODBC Driver (*. gdb)}; Server = localhost; Database = localhost: C: \ mydatabase. gdb; Uid = username; Pwd = password"
O remote computer:
"Driver = {INTERSOLV InterBase ODBC Driver (*. gdb)}; Server = ComputerName; Database = ComputerName: C: \ mydatabase. gdb; Uid = username; Pwd = password"
This drive is now provided by DataDirect Technologies (previously provided by Intersolv)
· Ole db, SIBPROvider
O standard connection:
"Provider = sibprovider; location = localhost:; data source = c: \ databases \ gdbs \ mygdb. gdb; user id = SYSDBA; password = masterkey"
O specified character set:
"Provider = sibprovider; location = localhost:; data source = c: \ databases \ gdbs \ mygdb. gdb; user id = SYSDBA; password = masterkey; character set = ISO8859_1"
O specified role:
"Provider = sibprovider; location = localhost:; data source = c: \ databases \ gdbs \ mygdb. gdb; user id = SYSDBA; password = masterkey; role = DIGITADORES"
· Need to learn more please link to Interbase's following Borland Developer Web Article http://community.borland.com/article/0,1410,27152,00.html
· IBM DB2
· Ole db, OleDbConnection (. NET) from MS
O TCP/IP:
"Provider = DB2OLEDB; Network Transport Library = TCPIP; Network Address = XXX. XXX. XXX. XXX; Initial Catalog = MyCtlg; Package Collection = MyPkgCol; Default Schema = Schema; User ID = MyUser; Password = MyPW"
O APPC:
"Provider = DB2OLEDB; APPC Local LU Alias = MyAlias; APPC Remote LU Alias = MyRemote; Initial Catalog = MyCtlg; Package Collection = MyPkgCol; Default Schema = Schema; User ID = MyUser; password = MyPW"
AS400
· ODBC
"Driver = {Client Access ODBC Driver (32-bit)}; System = myAS400; Uid = myUsername; Pwd = myPassword"
· Ole db, OleDbConnection (. NET)
"Provider = IBMDA400; Data source = myAS400; User Id = myUsername; Password = myPassword ;"
· Sybase
· ODBC
O Sybase System 12 (or 12.5) EntERPrise Open Client standard connection:
"Driver = {sybase ase odbc Driver}; Srvr = Aron1; Uid = username; Pwd = password"
O Sybase System 11 standard connection:
"Driver = {sybase system 11}; Srvr = Aron1; Uid = username; Pwd = password ;"
O Intersolv 3.10:
"Driver = {INTERSOLV 3.10 32-BIT Sybase}; Srvr = Aron1; Uid = username; Pwd = password ;"
O Sybase SQL Anywhere (previously Watcom SQL ODBC driver ):
"ODBC; Driver = Sybase SQL Anywhere 5.0; DefaultDir = c: \ dbfolder \; Dbf = c: \ mydatabase. db; Uid = username; Pwd = password; Dsn = """""
Note: For double quotation marks (VB syntax) that are referenced with the DSN parameter at the end, you must change the referenced symbol syntax specified by your language. It is actually very important to leave the DSN parameter empty. If it is not included, the 7778 error will occur.
· OLE DB
O Adaptive Server Anywhere (ASA ):
"Provider = ASAProv; Data source = myASA"
O Adaptive Server Enterprise (ASE) (with the data source as the. IDS file ):
"Provider = Sybase ase ole db Provider; Data source = myASE"
Note that you must use the data manager to create a data source. IDS file. These. IDS files are similar to ODBC DSNs.
Visual FoxPro (dBASE)
· ODBC
O dBASE:
"Driver = {Microsoft dBASE Driver (*. dbf)}; DriverID = 277; Dbq = c: \ DatabasePath"
Note: When writing an SQL statement, you must specify the file Name (for example, "Select Name, Address From Clients. dbf ").
O Visual Foxpro (with database containers ):
"Driver = {Microsoft Visual Foxpro Driver}; UID =; SourceType = DBC; SourceDB = C: \ DatabasePath \ MyDatabase. dbc; Exclusive = No"
O Visual Foxpro (no database container ):
"Driver = {Microsoft Visual Foxpro Driver}; UID =; SourceType = DBF; SourceDB = C: \ DatabasePath \ MyDatabase. dbc; Exclusive = No"
· Ole db, OleDbConnection (. NET)
O standard connection:
"Provider = VFPoledb; Data Source = C: \ DatabasePath \ MyDatabase. dbc ;"
Excel
· ODBC
O Standard Security ):
"Driver = {Microsoft Excel Driver (*. xls)}; DriverId = 790; Dbq = C: \ DatabasePath \ DBSpreadSheet.xls; DefaultDir = c: \ databasepath ;"
· Ole db Provider for Microsoft Jet
O Standard Security ):
"Provider = Microsoft. jet. OLEDB.4.0; Data Source = C: \ DatabasePath \ DBSpreadSheet.xls; Extended Properties = \ "\" Excel 8.0; HDR = Yes ;\"\";"
NOTE: If "HDR = Yes", the provider will not include the first row selected in the record set. If so, the provider will include the unit range (or already named range) in the record set) the first line
Text
· ODBC
O Standard Security ):
"Driver = {Microsoft Text Driver (*. txt; *. csv)}; Dbq = C: \ DatabasePath \; Extensions = asc, csv, tab, txt ;"
If a text file uses tab as the separator, you must create the schema. ini file and use the Format = TabDelimited option in the connection string.
Note: You must specify the file Name (for example, "Select Name, Address From Clients.csv") in the SQL statement ")
· Ole db Provider for Microsoft Jet
O Standard Security ):
"Provider = Microsoft. jet. OLEDB.4.0; Data Source = C :\\ DatabasePath \\; Extended Properties = \ "\" text; "HDR = Yes; FMT = Delimited ;\"\";"
Note that when writing an SQL statement, you must specify the file Name (for example, "Select Name, Address From Clients.txt ").
ODBC DSN
"DSN = MyDSN; Uid = MyUsername; Pwd = MyPassword ;"
Ole db Provider for ODBC Databases
Connection Access:
"Provider = MSDASQL; Driver = {Microsoft Access Driver (*. mdb)}; Dbq = c: \ DatabasePath \ MyDatabase. mdb; Uid = MyUsername; Pwd = MyPassword ;"
Connect to SQL Server:
"Provider = MSDASQL; Driver = {SQL Server}; Server = MyServerName; Database = MyDatabaseName; Uid = MyUsername; Pwd = MyPassword ;"
Use DSN for connection:
"Provider = MSDASQL; PersistSecurityInfo = False; Trusted_Connection = Yes; Data Source = MyDSN; catalog = MyDatabase ;"
Ole db Provider for OLAP
"Provider = MSOLAP; Data Source = MyOLAPServerName; Initial Catalog = MyOLAPDatabaseName ;"
Connect through http:
This feature allows client applications to specify a URL in the Data Source parameter of the client application connection string and connect to an analysis server using IIS. This connection method allows the mongottable service to connect to the analysis server through a firewall or proxy server. A special ASP page Msolap. asp is connected through IIS. When you connect to the server, this file must be in this directory and be part of a URL, such as a http://www.myserver.com/myolap ).
Connect using url:
"Provider = MSOLAP; Data Source = http: // MyOLAPServerName/; Initial Catalog = MyOLAPDatabaseName ;"
Use ssl connection:
"Provider = MSOLAP; Data Source = https: // MyOLAPServerName/; Initial Catalog = MyOLAPDatabaseName ;"
Ole db Provider for Active Directory
"Provider = ADSDSOObject; User Id = myUsername; Password = myPassword ;"
Ole db Provider for Index Server
"Provider = msidxs; Data Source = MyCatalog ;"
Ole db Data Link Connections
"File Name = c :\\ DataBasePath \ DatabaseName. udl ;"
Outlook 2000 personal mail box
"Provider = Microsoft. Jet. OLEDB.4.0; Outlook 9.0; MAPILEVEL =; DATABASE = C: \ Temp \\;"
Use any Temporary Folder instead of c: \ temp. This will create a pattern file in that folder. When you open it, it will display all the available fields. An empty mapilevel indicates the folder at the highest level.
Exchange mail box
"Provider = Microsoft. Jet. OLEDB.4.0; Exchange 4.0; MAPILEVEL = Mailbox-Pat Smith |; DATABASE = C: \ Temp \\;"
Use any Temporary Folder instead of c: \ temp.
Note: You can enter and query the mail store like a database.
Example: "SQL" SELECT Contacts. * FROM Contacts ;"