Database connection string
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 = dbmssocn; 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 Name / Instance Name; but only for the SQLServer2000), for example: "Provider = sqloledb; Data Source = MyServerName / MyInstanceName; Initial Catalog = MyDatabaseName; User Id = MyUsername Password = mypassword; "O" O connections pop-up input username and password dialog:
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 ServerName / InstanceName 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 entering the value of the field in the app, you must ensure that the user does not insert an additional attribute through another value in the user value to change the connection string.
ACCESS
· ODBC
o Standard security:
"Driver = {Microsoft Access Driver (* .mdb)}; dbq = c: /mydatabase.mdb; uid = admin; pwd =;"
O group (system database): Workgroup:
The two methods are: specify the username and password in the connection string or in the open data set.
"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" ))
{DOSMETHING ();
PDB.Close ();
}
o Exclusive (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 A connection to the password:
"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 Older version:
"Driver = {microsoft odbc driver for oracle}; connectionString = 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 format of Microsoft, the following is the format of Oracle (the provider is different)
"Provider = oraoledb.oracle; data source = myoracledb; user ID = username; password = asdasd;"
Note: "Data Source =" must be set to NET8 name according to the corresponding naming method. For example, for local naming, it is the alias in tnsnames.ora, named Oracle, is Net8 network service name
o Trusted Connection:
"Provider = oraoledb.racle; data source = myoracledb; osauthent = 1;"
Or set the USER ID "/"
"Provider = oraoledb.racle; 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 higher
o OracleConnection declaration:
C #:
Using system.data.oraclient;
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 source = msdara; 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-data; uid = username; pwd = password;"
Option value
1 The client cannot handle myODBC to return a column of true width
2 The client cannot handle the true value of the row of MySQL returns the row of the impact, and MySQL returns 'Found Rows'. MySQL 3.21.14 or update version can take effect
4 Generate a debug log in C: /Myodbc.log. This is the same as the setting mysql_debug = d: t: o, c :: / myodbc.log
8 Do not set any package restrictions on the results and parameters
16 Do not bring the drive to the problem
32 Use or remove dynamic cursor support. This is not allowed in myodbc 2.50
64 Using the use of the database name in 'Database.table.column'
128 Forced ODBC Manager Cursor
256 Remove the use of extension (FETCH)
512 full of char fields full length
1024 SQLDESCRIBECOL () Function Returns the column name that fully meets the conditions
2048 Using Compressed Server / Client Protocol
4096 Tell the server after the function and '(' ignoring the space before '(PowerBuilder needs). This will generate all function name keywords
8192 Using a Named Pipe Connection Mysqld Server on NT
16384 Change the longlong column to an INTL column (some applications cannot handle the longlong column)
32768 Return 'User' from SqlTables as Table_Qualifier and Table_owner
66536 read the parameters from the client, read the ODBC group from `my.cnf '
131072 Add some additional safety inspection
If you need a lot of options, you should add the above flags. · 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, or server = localhost; db = test
· MySQLConnection (.NET)
o einfodesigns.dbProvider:
"Data Source = Server; Database = MYDB; User ID = Username; Password = PWD; Command logging = false"
Only for Einfodesigns dbProvider, attached to .NET
o MySQLConnection declaration:
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, 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, 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 = computename: c: /mydatabase.gdb; uid = username; pwd = password
This drive is now available by DataDirect Technologies (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 Specify the role:
"provider = sibprovider; location = localhost:; data source = c: /databases/gdbs/mygdb.gdb; user ID = sysdba; password = masterkey; role = digitadores
· Need to get more please link to Interbase, the following Borland developer network 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 ": Watcom SQL ODBC Driver:
"ODBC; driver = sybase sql anywhere 5.0; defaultdir = c: / dbfaulter /; dbf = c: /mydatabase.db; uid = username; pwd = password; dsn =" "" "
Note: For dual quotes (VB syntax) referenced at the end, you must change the reference symbol syndrome specified by the language you use. The DSN parameter is actually very important, and if not included, the 7778 error will be caused.
· OLE DB
o Adaptive Server Anywhere (ASA):
"Provider = asaprov; data source = myasa"
o Adaptive Server Enterprise (ASE) (with data source is .IDS file):
"Provider = Sybase ASE OLE DB Provider; Data Source = Myase"
Note You must use 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 You must specify a file name (such as "Select Name, Address from Clients.dbf" when writing SQL statements.
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 agriculture 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 proties = /" / "Excel 8.0; HDR = yes; /" / ";" Note: If "HDR = YES, then the provider does not include the first line of the selected selection in the recordset, if, the provider will include the first line of the unit range (or have named) in the recordset.
TEXT
· ODBC
o Standard security:
"Driver = {Microsoft text driver (* .txt; * .csv)}; dbq = c: // DatabasePath //; extensions = ASC, CSV, Tab, TXT;"
If the text file uses Tab as a separator, you must create a schema.ini file, you must use the format = tabdelimited option in the connection string.
Note: You must specify a file name in the SQL statement (such as "SELECT NAME, Address from Clients.csv")
· OLE DB Provider for Microsoft Jet
o Standard security:
"Provider = microsoft.jet.Oledb.4.0; data source = c: // DatabasePath //; extended proties = /" / "text;" HDR = yes; fmt = DELIMITED; / "/"; "
Note You must specify the file name (such as "Select Name, Address from Clients.txt" when writing SQL statements.
ODBC DSN
"DSN = mydsn; uid = myusername; pwd = mypassword;"
OLE DB Provider for ODBC Databases
Connect Access:
"Provider = msdasql; driver = {microsoft access driver (* .mdb)}; dbq = c: //database.mdb; uid = myusername; pwd = mypassword;"
Connect to SQL Server:
"Provider = msdasql; driver = {sql server}; server = myservername; database = mydatabaseename; uid = myusername; pwd = mypassword;"
Use DSN to connect:
"Provider = msdasql; persistSecurityInfo = false; trusted_connection = yes; data source = mydsn; catalog = mydatabase;"
OLE DB Provider for OLAP
"Provider = msolap; data source = myolapservername; initial catalog = myolapdatabaseename;"
Connection via HTTP:
This feature allows the client application to specify a URL in the Data Source parameter of the client application connection string and connect to an analytical server using IIS. This connection method allows the PivotTable service to connect to the analysis server via a firewall or proxy server. A special ASP page MSOLAP.ASP is connected via IIS. When connected to the server, this file must be in this directory and as part of the URL (eg http://www.myserver.com/myolap/). Use the URL connection:
"Provider = msolap; data source = http: // myolapservername /; initial catalog = myolapdatabaseename;"
Use SSL connections:
"Provider = msolap; data source = https: // myolapservername /; initial catalog = myolapdatabaseename;"
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//database, "
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 schema file in that folder, when you open it, it displays all the fields that can be obtained. Empty mapilevel indicates the highest layer folder.
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 the Mail Store as the database.
Example: "SQL" Select Contacts. * From Contacts; "
