Connection string for database (Chinese)

Data | database | Chinese in various application development of database, connecting database is the first step of database application development, and also the most important step. For different databases, their connection modes are different, and the corresponding connection strings are different.

SQL Server
· Odbc
o Standard connection (Standard security):
"Driver={sql Server}; Server=aron1;database=pubs; Uid=sa; PWD=ASDASD; "
1 when the server is local, server can be used (locally);
"Driver={sql Server}; server= (local);D atabase=pubs; Uid=sa; PWD=ASDASD; "
2 When you connect to a remote server, 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: Address parameters must be IP addresses and must include the port number
o Trusted connections (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 Pop-up enter username and Password dialog box when connected:
Conn.properties ("Prompt") = adPromptAlways
Conn.Open "Driver={sql Server}; Server=aron1;database=pubs; "
· OLE DB, OleDbConnection (. NET)
o Standard connection (Standard security):
"Provider=sqloledb;data source=aron1;initial catalog=pubs; User Id=sa; PASSWORD=ASDASD; "
o Trust connection (Trusted connection):
"Provider=sqloledb;data source=aron1;initial catalog=pubs;integrated Security=sspi;"
(If you are connecting to a specific named instance of SQL Server, use the Data source=servere name\instance Name, but only for SQLServer2000), for example: "Provider=sqloledb;data Source=myservername\myinstancename;initial Catalog=mydatabasename; User Id=myusername; Password=mypassword; "
O Pop-up enter username and Password dialog box when connected:
Conn.provider = "SQLOLEDB"
Conn.properties ("Prompt") = adPromptAlways
Conn.Open "Data source=aron1;initial catalog=pubs;"
o Connect via 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, the end of Data source is the port number that needs to be used (default is 1433))
· SqlConnection (. NET)
o Standard connection (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 Trust connection (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 instead of data Source, value as a specific SQL Server instance, but only for SQLServer2000)
o Connect via 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, the end of Data source is the port number that needs to be used (default is 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 Tips
The following syntax is used when connecting through a SQLOLEDB provider:
Network LIBRARY=DBMSSOCN
However, the following syntax is used when connecting through a 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 these properties are also used in ADO. All attributes and descriptions are from MSDN.
Name Default Value Description
Application name application names or. Net SqlClient data providers when no application is provided
The name of the AttachDbFileName or extended properties or the initial file name principal, including the full path of the associated database. Database names must be specified by the keyword ' database '.
Connect Timeout or Connection Timeout 15 the time (in seconds) to wait for the server to connect before aborting the connection request and generating an error
Connection Lifetime 0 When a connection is returned to the connection pool, the difference between the current time and the connection creation time, and if the time period exceeds the specified connection lifetime, the connection is corrupted. It is used to enforce load balancing between servers running the server and preparing to go online in the aggregation settings.
Connection reset ' true ' determines whether to reset the database connection when the connection is removed from the connection pool. When set to ' false ' it is used to avoid additional server reciprocating costs when the connection is obtained.
Current Language SQL Server language record name
The name or network address of the SQL Server instance to which Data source or server or address or addr or network addresses are connected
When Enlist ' true ' is true, the connection pool automatically lists the connections in the current transaction context of the creation thread.
Initial catalog or database name
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 connection pool
Min Pool Size 0 The minimum number of connections allowed in a connection pool
The network library or net ' DBMSSOCN ' network library is used to establish a connection to an instance of SQL Server. Values include DBNMPNTW (Named Pipes), DBMSRPCN (Multiprotocol), Dbmsadsn (Apple Talk), Dbmsgnet (VIA), DBMSIPCN (Shared memory) and DBMSSPXN (ipx/spx), and DBMSSOC N (TCP/IP). The connected system must have the corresponding dynamic link library installed. If you do not specify a network, when you use a local server (for example, "." or "(local)"), the shared memory will be used
Packet size 8192 network packet byte sizes that are communicated with an instance of SQL Server
password-or PWD SQL Server account logon password
Persist Security Info ' False ' is set to ' false ', and when the connection is turned on or is always open, the sensitive safety information (such as the password) does not return as part of the connection information.
When pooling ' true ' is true, remove the SqlConnection object from the appropriate connection pool or, if necessary, create the SqlConnection object and add it to the appropriate connection pool.
User ID SQL Server logon users
Workstation ID The local computer name of the workstation connected to SQL Server
§ Note:
Separating each property with a semicolon
If a name appears more than two times, the last occurrence of the value in the connection string is used.
If you build the connection string by entering the value of the field in the application, you must ensure that the user does not insert an additional attribute into the user's value to change the connection string.
Access
· Odbc
o Standard connection (Standard security):
"Driver={microsoft Access Driver (*.mdb)};D Bq=c:\mydatabase.mdb; Uid=admin; pwd=; "
Group O (System database) connection (Workgroup):
The two methods are: to specify a username and password in the connection string or in the Open data set
"Driver={microsoft Access Driver (*.mdb)};D Bq=c:\mydatabase.mdb; systemdb=c:\mydatabase.mdw; "," admin "," "
Or
if (Pdb.open ("Driver={microsoft Access Driver (*.mdb)};D Bq=c:\mydatabase.mdb; systemdb=c:\mydatabase.mdw; "," "," Databaseuser "," Databasepass "))
{dosomething ();
Pdb.close ();
}
o Exclusive Way (Exclusive):
"Driver={microsoft Access Driver (*.mdb)};D Bq=c:\mydatabase.mdb; exclusive=1; Uid=admin; Pwd= "
· OLE DB, OleDbConnection (. NET)
o Standard connection (Standard security):
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb; User id=admin; password=; "
Group O connections (System database)
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb; Jet Oledb:system database=system.mdw; "," admin "," "
o with Password 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 connection (Standard security):
"Provider=msdaora;data Source=myoracledb; User Id=username; PASSWORD=ASDASD; "
This is the Microsoft format, and the following is the Oracle format (different provider)
"Provider=oraoledb.oracle;data Source=myoracledb; User Id=username; PASSWORD=ASDASD; "
Note: "Data source=" must be set to the Net8 name according to the appropriate naming method. For example, for local naming, it is an alias in Tnsnames.ora, and for Oracle, it is the NET8 Network service name
o Trust connection (Trusted Connection):
"Provider=oraoledb.oracle;data source=myoracledb;osauthent=1;"
or set the user ID to "/"
"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=;D atabase=my-database; Uid=username; Pwd=password; "
Meaning of option value
1 Client cannot process MYODBC return a column true width
2 client cannot handle the case of MySQL returning the true value of the affected row if this flag is set, MySQL returns ' found rows '. MySQL 3.21.14 or newer version to take effect
4 Generate a debug log in C:\myodbc.log. This is the same as in ' AUTOEXEC. BAT ' in the same setting Mysql_debug=d:t:o,c::\myodbc.log
8 do not set any package restrictions for results and parameters
16 does not cause the drive to eject the problem
32 Use or remove dynamic cursor support. This is not allowed in Myodbc 2.50.
64 ignoring the use of database names in ' Database.table.column '
128 enforce use of ODBC Administrator cursors
256 removing the use of extended values (fetch)
512 full Char field full length
The 1024 SQLDescribeCol () function returns the name of the column that fully satisfies the condition
2048 using a compressed server/client protocol
4096 tells the server to ignore the space (PowerBuilder required) after the function and ' ('). This will produce all the function name keywords
8192 mysqld server running on NT using named pipe connection
16384 Change longlong column to intl column (some applications cannot process longlong columns)
32768 return ' user ' as Table_qualifier and Table_owner from SQLTables
66536 read parameters from client, read ODBC group from ' MY.CNF '
131072 add some extra security checks
If you need a lot of options, you should add the above logo.
· 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 and can also be used Server=localhost;db=test
· Mysqlconnection (. NET)
o Einfodesigns.dbprovider:
"Data Source=server;database=mydb; User Id=username; Password=pwd; Command Logging=false "
Applies only to Einfodesigns dbprovider, attached to. NET
o Mysqlconnection's 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, 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=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:;d ata source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password= Masterkey "
o Specify the character set:
"Provider=sibprovider;location=localhost:;d ata source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password= Masterkey;character Set=iso8859_1 "
o Specify roles:
"Provider=sibprovider;location=localhost:;d ata source=c:\databases\gdbs\mygdb.gdb;user id=SYSDBA;password= Masterkey;role=digitadores "
· 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 (formerly watcom SQL ODBC driver):
"ODBC; Driver=sybase SQL Anywhere 5.0; Defaultdir=c:\dbfolder\;D bf=c:\mydatabase.db; Uid=username; Pwd=password;dsn= "" "" ""
Note: for double quotes (VB syntax) that are referenced at the end of the DSN parameter, you must change the syntax for the reference symbol specified in the language you are using. Null DSN parameter is actually very important, if not included will cause 7778 error.
· OLE DB
o Adaptive Server Anywhere (ASA):
"Provider=asaprov;data Source=myasa"
o Adaptive Server Enterprise (ASE) with data source. 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 files. 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 filename (such as "select Name, address from clients.dbf") when writing an SQL statement.
o Visual Foxpro (with Database Container):
"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 connection (Standard security):
"Driver={microsoft Excel Driver (*.xls)};D riverid=790; DBQ =c:\\databasepath\\dbspreadsheet.xls;defaultdir=c:\\databasepath; "
· OLE DB Provider for Microsoft Jet
o Standard connection (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", then the provider does not include the first row of the selection in the recordset, and if so the provider will include the first row of the cell range (or named range) in the recordset
Text
· Odbc
o Standard connection (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 the Schema.ini file, and you must use the format=tabdelimited option in the connection string.
Note: You must specify the filename in the SQL statement (for example, "Select Name, address from Clients.csv")
· OLE DB Provider for Microsoft Jet
o Standard connection (Standard security):
"Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\\databasepath\\; Extended properties=\ "\" text; Hdr=yes; Fmt=delimited;\ "\"; "
Note You must specify a filename (such as "select Name, address from Clients.txt") when writing an SQL statement.
ODBC DSN
"DSN=MYDSN; Uid=myusername; Pwd=mypassword; "
OLE DB Provider for ODBC Databases
Connect to access:
"Provider=msdasql;driver={microsoft Access Driver (*.mdb)};D Bq=c:\\databasepath\\mydatabase.mdb; Uid=myusername; Pwd=mypassword; "
To connect to SQL Server:
"Provider=msdasql;driver={sql Server}; Server=myservername;database=mydatabasename; Uid=myusername; Pwd=mypassword; "
To connect using DSN:
"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 the client application to specify a URL in the data source parameter of the client application connection string and use IIS to connect to an Analysis server. This connection method allows the PivotTable service to connect to the Analysis server through a firewall or proxy server. A special ASP page msolap.asp is connected through IIS. When connected to the server, this file must be in this directory and as part of the URL (for example, http://www.myserver.com/myolap/).
To connect using a URL:
"Provider=msolap;data source=http://myolapservername/;initial catalog=myolapdatabasename;"
To connect using SSL:
"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 Personal mail Box
"Provider=microsoft.jet.oledb.4.0;outlook 9.0; mapilevel=;D atabase=c:\\temp\\; "
Use any temporary folder instead of C:\Temp. This creates a schema file in that folder, and when you open it, it shows all the fields you can get. An empty mapilevel indicates the top-level folder.
Exchange Mail Box
"Provider=Microsoft.Jet.OLEDB.4.0; Exchange 4.0; Mapilevel=mailbox-pat smith|;D atabase=c:\\temp\\; "
Use any temporary folder instead of C:\Temp.
Note: You can enter a query into the mail store like a database
For example: "SQL" select contacts.* from Contacts; "