数据库连接字符串
一、SQL Server
________________________________________
ODBC
标准连接
Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
受信的连接
Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;
指定帐号和密码
oConn.Properties("Prompt") = adPromptAlways
Driver={SQL Server};Server=myServerAddress;Database=myDataBase;
OLE DB, OleDbConnection (.NET) 标准连接
Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
受信的连接
Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
使用serverNameinstanceName作为数据源可以指定SQL Server实例。
连接到一个SQL Server的实例 指定服务器实例的表达式和其他SQL Server的连接字符串相同。
Provider=sqloledb;Data Source=myServerName heInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;
指定帐户和密码
oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways
Data Source=myServerAddress;Initial Catalog=myDataBase;
使用IP地址的连接
Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
SqlConnection (.NET) 标准连接
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
Standard Security alternative syntax
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
受信任的连接
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
Trusted Connection alternative syntax
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
连接到SQL Server实例 指定服务器实例的表达式和其他SQL Server的连接字符串相同。
Server=myServerName heInstanceName;Database=myDataBase;Trusted_Connection=True;
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomainmyUsername;Password=myPassword;
仅能用于CE设备。
带有IP地址的连接
Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
指定包的大小
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;Packet Size=4096;
默认的包大小为8192字节。
Data Shape MS Data Shape
Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=myServerAddress;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
________________________________________
二、MYSQL
________________________________________
MyODBC
MyODBC 2.50 本地数据库
Driver={mySQL};Server=localhost;Option=16834;Database=myDataBase;
MyODBC 2.50 远程数据库
Driver={mySQL};Server=myServerAddress;Port=3306;Option=131072;Stmt=; Database=myDataBase;Uid=myUsername;Pwd=myPassword;
MyODBC 3.51 本地数据库
Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDataBase; User=myUsername;Password=myPassword;Option=3;
MyODBC 3.51 远程数据库
Driver={MySQL ODBC 3.51 Driver};Server=data.domain.com;Port=3306;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;
OLE DB, OleDbConnection (.NET)
标准
Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;
Connector/Net 1.0 (.NET)
标准
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
默认端口号是3306.
指定端口号
Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
命名管道
Server=myServerAddress;Port=-1;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
如果端口是-1，意思是告诉驱动程序使用命名管道网络协议来连接数据库。
MySqlConnection (.NET)
eInfoDesigns.dbProvider
Data Source=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Command Logging=false;
SevenObjects MySqlClient (.NET)
标准
Host=myServerAddress;UserName=myUsername;Password=myPassword;Database=myDataBase;
Core Labs MySQLDirect (.NET)
标准
User ID=root;Password=myPassword;Host=localhost;Port=3306;Database=myDataBase; Direct=true;Protocol=TCP;Compress=false;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;
MySQLDriverCS (.NET)
标准
Location=myServerAddress;Data Source=myDataBase;UserID=myUsername;Password=myPassword;Port=3306;Extended Properties="""";
________________________________________
三、Oracle
ODBC
新版本
Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;
旧版本
Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;
OLE DB, OleDbConnection (.NET)
标准连接
此连接字符串适用了微软的驱动。
Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
受信连接
Provider=msdaora;Data Source=MyOracleDB;Persist Security Info=False;Integrated Security=Yes;
标准连接
由Oracle提供的驱动。
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
受信连接
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;
Oracle.DataAccess.Client.OracleConnection
标准
Data Source=TORCL;User Id=myUsername;Password=myPassword;
标准安全连接
Data Source=TORCL;Integrated Security=SSPI;
使用ODP.NET而不使用tnsnames.ora
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;
OracleConnection, Oracle Data Provider, ODP.NET, System.Data.OracleClient.OracleConnection
标准
Data Source=MyOracleDB;Integrated Security=yes;
用于8i RC3及以后的版本
指定用户名和密码
Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no;
用于8i RC3及以后的版本
忽略tnsnames.ora
另一种不需要使用DSN的连接方式。
SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword;
使用上面的连接字符串可能会导致Visual Studio报告错误，如果您在使用中出现了这些问题，请使用下面的这种连接方式。
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;
使用连接池
如果连接池服务程序找不到已经存在的连接池，它将根据连接字符串创建一个新的池，否则将循环使用池中已存在的连接。
Data Source=myOracleDB;User Id=myUsername;Password=myPassword;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;
Windows身份验证
Data Source=myOracleDB;User Id=/;
特权连接
使用SYSDBA
Data Source=myOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSDBA;
特权连接
使用SYSOPER
Data Source=myOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSOPER;
密码过期处理过程
当使用一个连接字符串连接数据库后，出现“密码已过期”的错误时，请执行OpenWithNewPassword命令来提供新密码。
Data Source=myOracleDB;User Id=myUsername;Password=myPassword;
oConn.OpenWithNewPassword(sTheNewPassword);
Proxy验证
Data Source=myOracleDB;User Id=myUsername;Password=myPassword;Proxy User Id=pUserId;Proxy Password=pPassword;
Core Labs OraDirect (.NET)
Standard
User ID=myUsername;Password=myPassword;Host=ora;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;
Data Shape
MS Data Shape
Provider=MSDataShape.1;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;User Id=myUsername;Password=myPassword;
________________________________________
四、Access
________________________________________
ODBC
标准安全策略
Driver={Microsoft Access Driver (*.mdb)};Dbq=C:mydatabase.mdb;Uid=Admin;Pwd=;
工作组
Driver={Microsoft Access Driver (*.mdb)};Dbq=C:mydatabase.mdb;SystemDB=C:mydatabase.mdw;
独占模式
Driver={Microsoft Access Driver (*.mdb)};Dbq=C:mydatabase.mdb;Exclusive=1;Uid=admin;Pwd=;
管理员模式
如果您需要在程序中使用 CREATE USER, CREATE GROUP, ADD USER, GRANT, REVOKE 和DEFAULTS等命令，您就需要使用此连接字符串。
Driver={Microsoft Access Driver (*.mdb)};Dbq=C:mydatabase.mdb;Uid=Admin;Pwd=;ExtendedAnsiSQL=1;
OLE DB, OleDbConnection (.NET)
标准安全策略
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydatabase.mdb;User Id=admin;Password=;
使用数据库密码
如果您的Access数据库设置了密码，您就需要参照如下连接字符串将密码写入后才能够正常使用。
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;
工作组(系统数据库)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydatabase.mdb;Jet OLEDB:System Database=system.mdw;
工作组(系统数据库)并且制定用户名和密码
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydatabase.mdb;Jet OLEDB:System Database=system.mdw;User ID=myUsername;Password=myPassword;
________________________________________
五、DBF / FoxPro
________________________________________
以下字符串以及实例将告诉您如何连接到 DBF / FoxPro.
如果您使用的是 2.x DBF，您不必使用dBase或者Jet driver，使用"Visual FoxPro / FoxPro 2.x"替换VFP driver。Visual FoxPro ODBC和OLEDB驱动可以用于所有的Foxpro数据表，其中包括很老的foxbase数据表以及dBaseII, dBaseIII, Clipper文件等。
ODBC
标准
Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:mydbpath;
远程驱动
Driver={Microsoft dBase Driver (*.dbf)};datasource=dBase Files;
SQL表达式： "select * from \remotemachine hefile.dbf"
OLE DB, OleDbConnection (.NET)
标准
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:folder;Extended Properties=dBASE IV;User ID=Admin;Password=;
________________________________________
六、Excel
________________________________________
ODBC
标准
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:MyExcel.xls;DefaultDir=c:mypath;
SQL表达式"SELECT * FROM [sheet1$]"。例如：在excel工作表名称后面跟"$"字符并且使用"[" "]"将其括起来。
OLE DB
标准
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
请注意！
字符串中的引号 " 需要根据您使用的语言来进行转义。例如：
c#, c++ "
VB6, VBScript ""
xml (web.config等) "
或者您也可以直接使用单引号 ' 。
"HDR=Yes;" 表示工作表的第一行是表头，没有数据。 "HDR=No;"与之相反。
"IMEX=1;"告诉驱动程序始终将"intermixed"数据类型(numbers, dates, strings等等)作为文本型读取。注意：该选项可能引起Excel工作表写权限的修改。
SQL表达式"SELECT * FROM [sheet1$]"。例如：在excel 工作表名称后面跟字符"$"并且适用"[" "]"括起来。
________________________________________
七、Visual FoxPro / FoxPro 2.x
________________________________________
OLE DB, OleDbConnection (.NET)
Database container (.DBC)
Provider=vfpoledb.1;Data Source=C:MyDbFolderMyDbContainer.dbc;Collating Sequence=machine;
Free table directory
Provider=vfpoledb.1;Data Source=C:MyDataDirectory;Collating Sequence=general;
Force the provider to use an ODBC DSN
Provider=vfpoledb.1;DSN=MyDSN;
ODBC
Database container (.DBC)
Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:myvfpdb.dbc;Exclusive=No; NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO;
Note that DELETED=NO will cause the driver to include deleted rows in the resultset. To not retrieve deleted rows specify DELETED=YES. The terminology is a bit confusing here, a more appropriate keyword would have been IGNORE DELETED instead of DELETED.
Free Table directory
Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:myvfpdbfolder;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;
Note that DELETED=NO will cause the driver to include deleted rows in the resultset. To not retrieve deleted rows specify DELETED=YES. The terminology is a bit confusing here, a more appropriate keyword would have been IGNORE DELETED instead of DELETED.
________________________________________
八、Textfile
________________________________________
ODBC
Standard
Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c: xtFilesFolder;Extensions=asc,csv,tab,txt;
OLE DB
Delimited columns
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c: xtFilesFolder;Extended Properties="text;HDR=Yes;FMT=Delimited";
The delimiter can be specified in the registry at the following location:
HKEY_LOCAL_MACHINE SOFTWARE Microsoft Jet 4.0 Engines Text
"Format" = "TabDelimited"
or
"Format" = "Delimited(;)"
Fixed length columns
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c: xtFilesFolder;Extended Properties="text;HDR=Yes;FMT=Fixed";
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
To specify each columns length use the Schema.ini file. See description below.
Important note!
The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++ "
VB6, VBScript ""
xml (web.config etc) "
or maybe use a single quota '.
Schema.ini
_
The schema information file tells the driver about the format of the text files. The file is always located in the same folder as the text files and must be named schema.ini.
[customers.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI
[orders.txt]
Format=Delimited(;)
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI
[invoices.txt]
Format=FixedLength
ColNameHeader=False
Col1=FieldName1 Integer Width 15
Col2=FieldName2 Date Width 15
Col3=FieldName3 Char Width 40
Col4=FieldName4 Float Width 20
CharacterSet=ANSI