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;
|
| 使用serverName\instanceName作为数据源可以指定SQL Server实例。 |
连接到一个SQL Server的实例指定服务器实例的表达式和其他SQL Server的连接字符串相同。
|
Provider=sqloledb;Data Source=myServerName\theInstanceName;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\theInstanceName;Database=myDataBase;Trusted_Connection=True;
|
|
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;
|
带有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;
|
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;
|
|
|
|
|
| 指定端口号 |
| |
|
|
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;
|
|
|
|
|
| 指定用户名和密码 |
| |
|
|
Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no;
|
|
|
|
|
| 忽略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\thefile.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:\MyDbFolder\MyDbContainer.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:\txtFilesFolder\;Extensions=asc,csv,tab,txt;
|
|
| |
|
| OLE DB |
| |
| Delimited columns |
| |
|
|
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;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:\txtFilesFolder\;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
|
|