ODBC和OleDb访问数据库的连接字符串
数据库,介绍其连接符的含义及使用方法
数据库,介绍其连接符的含义及使用方法ODBC（OpenDatabaseConnectivity）：在早期，各个数据库的格式均不统一，为了能处理各种各样的数据库，人们就创建了ODBC这样一个通用的API 库。
但是程序员在开发数据库程序中，仍然感到困难，所以微软公司在DAO、ADO的基础上提出了OLEDB。
OLEDB：一个基于COM的数据存储对象，能提供对所有类型的数据的操作，甚至能在离线的情况下存取数据。
在前面使用DSN数据源时，每次都需要在ODBC数据管理器当中建立一个数据源，然后Web程序就通过ODBC层来与数据库进行通信。
但是OLEDB消除了Web应用程序和数据库之间的ODBC层，从而提高了连接的速度。
由于不同的数据库需要不同的OLEDB驱动程序，所以在使用OLEDB时，需要指定驱动程序。
如果没有指定ASP会使用ODBC驱动程序的默认OLEDB提供程序与ODBC驱动程序进行通信，然后再与数据库进行通信。
可以在微软的网站上下载MicrosoftDataAccessComponents（简称MDAC）2.8软件包，并将其安装在运行IIS或PWS的Windows计算机上，获取用于MicrosoftAccess和SQLServer的OLEDB提供程序。
而Oracle数据库的OLEDB提供程序需要另外从OracleWeb站点下载，该站点需要读者注册为用户才能下载。
（注意：由于OLEDB由微软公司提出，所以只有Windows2000/2003/XP 支持。
）对于Access和SQLServer数据库，连接字符串具有如下语法格式。
ODBC：Driver={Driver(*.mdb)};DBQ=[DSN]OLEDB：Provider=[OLEDBProvider];Server=[ServerName];Database=[Databa seName];UID=[UserID];PWD=[Password]其中涉及的参数解析如下：Provider：该参数指定数据库的OLEDB提供程序。
ADO,OLEDB,ODBC,DAO,RDO的区别
ADO,OLEDB,ODBC,DAO,RDO的区别ODBC(Open Database Connectivity，开放数据库互连)是微软公司开放服务结构(WOSA,Windows Open Services Architecture)中有关数据库的一个组成部分，它建立了一组规范，并提供了一组对数据库访问的标准API（应用程序编程接口）。
这些API利用SQL 来完成其大部分任务。
ODBC本身也提供了对SQL语言的支持，用户可以直接将SQL语句送给ODBC。
---出现的最早，但不总是万能。
DAO：数据访问对象是用来显露了Microsoft Jet数据库引擎（最早是给Microsoft Access 所使用，现在已经支持其它数据库），并允许开发者通过ODBC直接连接到其他数据库一样，直接连接到Access 表。
DAO 最适用于单系统应用程序或在小范围本地分布使用。
其内部已经对Jet数据库的访问进行了加速优化，而且其使用起来也是很方便的。
所以如果数据库是Access数据库且是本地使用的话，建议使用这种访问方式---应用的专一性RDO（Remote Data Objects）远程数据对象是一个到ODBC的、面向对象的数据访问接口，它同易于使用的DAO style组合在一起，提供了一个接口，形式上展示出所有ODBC的底层功能和灵活性。
尽管RDO在很好地访问Jet或ISAM数据库方面受到限制，而且它只能通过现存的ODBC驱动程序来访问关系数据库。
但是，RDO已被证明是许多SQL Server、Oracle 以及其他大型关系数据库开发者经常选用的最佳接口。
RDO提供了用来访问存储过程和复杂结果集的更多和更复杂的对象、属性，以及方法。
---无疑是在odbc 基础上的OLE DB 是Microsoft 的一个战略性系统级编程接口，用于管理整个组织内的数据。
OLE DB 是建立在ODBC 功能之上的一个开放规范。
ODBC 是为访问关系型数据库而专门开发的，OLE DB 则用于访问关系型和非关系型信息源，例如主机ISAM/VSAM 和层次数据库，电子邮件和文件系统存储，文本、图形和地理数据以及自定义业务对象。
关于ODBC数据源连接文本
关于ODBC数据源连接⽂本关于ODBC数据源连接⽂本 在《外部数据库的连接原理》⼀讲中我们说过，ODBC提供对多种数据库的⽀持，如dBase、Access、MS SQL Server及Oracle，也就是说运⽤ODBC数据源中所提供的连接代码，我们可以实现对多种数据库的连接。
以连接Access数据库为例，ODBC数据源连接⽂本的格式是： “Driver={数据库驱动程序};Dbq=数据库⽂件;” 在以上连接⽂本中，如果数据库跟程序在同⼀⽬录下，或者⽤变量DefaultDir指定了数据库所在⽬录，则数据库⽂件可以不⽤全路径名，如下即可： “ODBC;DBQ=MSAccess.mdb;Driver={Microsoft Access Driver (*.mdb)};” 如下也可： “ODBC;DBQ=MSAccess.mdb;DefaultDir=d:/Downloads/e21;Driver={Microsoft Access Driver (*.mdb)};” 如果数据库跟程序不在同⼀⽬录下，或者没有⽤变量DefaultDir指定数据库所在⽬录，则数据库⽂件需要⽤全路径名，如下： “ODBC;DBQ=E:/Quake III Arena/MSAccess.mdb;Driver={Microsoft Access Driver (*.mdb)};” 以上所说的是连接Access数据库的格式，那么连接其他数据库的ODBC数据源连接⽂本⼜是怎样的？连接不同类型的数据库要使⽤不同的对应驱动程序，没忘记吧！不同的驱动程序当然它们的参数组合也就不同了，每⼀种不同驱动程序都有其特定的的参数形式： ⑴、MS Access ODBC DSNless 连接： ☆、参数：Driver 设置值：{Microsoft Access Driver (*.mdb)} ☆、参数：Dbq 设置值：实际路径⽂件名称 ☆、例句： “Driver={Microsoft Access Driver (*.mdb)};Dbq=c:/somepath/dbname.mdb;Uid=Admin;Pwd=pass; ” ⑵、dBase ODBC DSNless 连接： ☆、参数：Driver 设置值：{Microsoft dBASE Driver (*.dbf)} ☆、参数：Dbq 设置值：实际路径⽂件名称 ☆、例句： “Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:/somepath/dbname.dbf; ” ⑶、Oracle ODBC DSNless 连接： ☆、参数：Driver 设置值：{Microsoft ODBC for Oracle} ☆、参数：Dbq 设置值：实际路径⽂件名称 ☆、例句： “Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=admin;Pwd=pass; ” ⑷、MS SQL Server DSNless 连接： ☆、参数：Driver 设置值：{SQL Server}; ☆、参数：Server 设置值：服务器名称 ☆、参数：Database 设置值：数据表名称 ☆、参数：Uid 设置值：⽤户名称 ☆、参数：Pwd 设置值：密码 ☆、例句： “Driver={SQL Server};Server=servername;Database=dbname;Uid=sa;Pwd=pass; ” ⑸、MS Text Driver DSNless 连接： ☆、参数：Driver 设置值：{Microsoft Text Driver (*.txt; *.csv)} ☆、参数：Dbq 设置值：实际路径⽂件名称 ☆、例句： “Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:/somepath/;Extensions=asc,csv,tab,txt;Persist Security Info=False;” ⑹、Visual Foxpro DSNless 连接： ☆、参数：Driver 设置值：{Microsoft Visual FoxPro Driver} ☆、参数：SourceType 设置值：DBC ☆、参数：SourceDB 设置值：实际路径⽂件名称 ☆、例句： “Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:/somepath/dbname.dbc;Exclusive=No;” ⑺、MySQL DSNless 连接： ☆、参数：Driver 设置值：{mysql} ☆、参数：database 设置值：数据表名称 ☆、参数：uid 设置值：⽤户名称 ☆、参数：pwd 设置值：密码 ☆、例句： “driver={mysql}; database=yourdatabase;uid=username;pwd=password;option=16386”*******************************************************************SQL语⾔简介 在上⼀讲中我们介绍了连接外部数据库的⽅法，那么连接之后怎样对外部数据库进⾏读取、显⽰、增删、更新、查询等操作呢？这些操作需要通过外部数据库等对象调⽤SQL指令才能完成。
OleDb连接各种数据库的字符串
OleDb连接各种数据库的字符串Access数据库Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\test.mdbSql数据库Provider=SQLOLEDB; Data Source=服务器名称; Initial Catalog=数据库名称; User ID=登录名; Password='登录密码'eg:Provider=SQLOLEDB; Data Source=192.168.0.100\SQL2008; Initial Catalog=wuhan_gl; User ID=sa; Password='sa'Oracle数据库（必须安装Oracle客户端）Provider=OraOLEDB.Oracle;Data Source=服务器名称;User Id=登录名;Password=密码eg:Provider=OraOLEDB.Oracle;Data Source=orcl;User Id=wuhan_gl;Password=123456另：OracleClient远程连接Oracle数据库的字符串SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=IP地址)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=服务器名称)));uid=登录名;pwd=登录密码; ps:1521为端口号eg:SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1192.168.0.100)(POR T=1521))(CONNECT_DA TA=(SERVICE_NAME=orcl)));uid=wuhan_gl;pwd=123456;SqlClient远程连接SQL数据库Server=服务器IP; Data Source=服务器名称; Initial Catalog=数据库名称; user id=登录名; password='登录密码'eg:Server=192.168.0.100; Data Source=192.168.0.100\SQL2008; Initial Catalog=wuhan_gl; user id=sa; password='sa'。
数据库连接字符串详解
数据库连接字符串详解本⽂将详细介绍如何使⽤Connection对象连接数据库。
对于不同的.NET数据提供者，采⽤不同的Connection对象连接数据库。
这些Connection对象为我们屏蔽了具体的实现细节，并提供了⼀种统⼀的实现⽅法。
Connection类有四种：SqlConnection，OleDbConnection，OdbcConnection和OracleConnection。
SqlConnection类的对象连接SQL Server数据库；OracleConnection 类的对象连接Oracle数据库； OleDbConnection类的对象连接⽀持OLE DB的数据库，如Access；⽽OdbcConnection类的对象连接任何⽀持ODBC的数据库。
与数据库的所有通讯最终都是通过Connection对象来完成的。
SqlConnection类 Connection ⽤于与数据库“对话”，并由特定提供程序的类（如 SqlConnection）表⽰。
尽管SqlConnection类是针对Sql Server的，但是这个类的许多属性、⽅法与事件和OleDbConnection及OdbcConnection等类相似。
本章将重点讲解SqlConnection特定的属性与⽅法，其他的Connection类你可以参考相应的帮助⽂档。
注意：使⽤不同的Connection对象需要导⼊不同的命名空间。
OleDbConnection的命名空间为System.Data.OleDb。
SqlConnection的命名空间为System.Data.SqlClient。
OdbcConnection的命名空间为System.Data.Odbc。
OracleConnection的命名空间为System.Data.OracleClinet。
SqlConnection属性：属性说明ConnectionString 其返回类型为string,获取或设置⽤于打开 SQL Server 数据库的字符串。
ODBC连接字符串全解
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb; Exclusive=1; Uid=admin; Pwd="
dBase
"Driver={microsoft dbase driver(*.dbf)};driverid=277;dbq=------------;"
USER=myUsername; PASSWORD=myPassword; OPTION=3; "
SQL server
本地数据库
Driver={SQL Server};Database=数据库名;Server=数据库服务器名(localhost);UID=用户名(sa);PWD=用户口令；
注：数据库服务器名(local)表示本地数据库
“Driver={Microsoft Text Driver(*.txt;*.csv)};DefaultDir=指向.txt文件的物理路径”
Visual FoxPro
带数据库容器
“Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDb=指向.dbc文件的物理路径”
Microsoft Excel
Driver={Microsoft Excel Driver(*.xls)};DBQ=指向.xls文件的物理路径;DriverID=278（97版本:ID=790）
MS text
"Driver={microsoft text driver(*.txt; *.csv)};dbq=-----;extensions=asc,csv,tab,txt;Persist SecurityInfo=false;"
几种常见的数据库连接方法
几种常见的数据库连接方法一、连接Access数据库1.使用已有DSN的连接字符串进行连接（ODBC）使用DSN进行连接〃导入命名空间using System.Data.Odbc;protected void Page_Load(Object sender,EventArgs e)(〃设置连接字符串String connstr=@"DSN=sample";〃实例化Connection对象OdbcConnection myConnection = new OdbcConnection(connstr);〃执行Open方法打开连接myConnection.Open();〃执行SQL语句OdbcCommand myCommand new OdbcCommand("select * from sampletable",myConnection);〃将查询的结果赋给GridView的数据源gv.DataSource = myCommand.ExecuteReader();〃绑定GridViewgv.DataBind();〃关闭连接myConnection.Close();)2.使用无DSN的连接字符串进行连接（ODBC）不使用DSN进行连接〃导入命名空间using System.Data.Odbc;protected void Page_Load(Object sender,EventArgs e)(〃设置连接字符串String connstr=@"Driver=Microsoft Access Driver (*.mdb);Dbq=c:\sample.mdb;";〃实例化Connection对象OdbcConnection myConnection = new OdbcConnection(connstr);〃执行Open方法打开连接myConnection.Open();〃执行SQL语句OdbcCommand myCommand new OdbcCommand("select * from sampletable",myConnection);〃将查询的结果赋给GridView的数据源gv.DataSource = myCommand.ExecuteReader();〃绑定GridViewgv.DataBind();〃关闭连接myConnection.Close();)3.使用连接字符串进行连接(OLEDB) Data Provider 支持的OLEDB Provider：SQLOLEDB：用来访问SQL Server数据库MSDAORA：用来访问Oracle数据库Microsoft.Jet.OLEDB.4.0：用来访问Access 数据库。
数据库连接字符串大全
1.SQL Server(1)ODBC"Driver={SQL Server};Server=Chris;Database=pubs;Uid=sa;Pwd=sa""Driver={SQL Server};Server=Chris;Database=pubs;Trusted_Connection=yes;"oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Driver={SQL Server};Server=Chris;DataBase=pubs;"(2)OLEDB,OLEDBConnection(.net)"Provider=sqloledb;Data Source=Chris;Initial Catalog=pubs;User Id=sa;Password=sa;""Provider=sqloledb;Data Source=Chris;Initial Catalog=pubs;Integrated Security=SSPI;"(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)oConn.Provider = "sqloledb"oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Data Source=Chris;Initial Catalog=pubs;""Provider=sqloledb;Data Source=192.168.0.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=sa;"(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))(3) SqlConnection (.NET)"server=.;database=pubs;uid=sa;pwd=123456" –用户名密码方式"Data Source=.;Initial Catalog=MySchool;User ID=sa;Pwd=sa""Data Source=.;Initial Catalog=pubs;Integrated Security=SSPI;" – SSPI方式"Data Source=Chris;Initial Catalog=pubs;User Id=sa;Password=sa;""Data Source=Chris;Initial Catalog=pubs;Integrated Security=SSPI;"(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)"Data Source=192.168.0.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=sa;"(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))2.Access(1)ODBC"Driver={Microsoft Access Driver (*.mdb)};Dbq=\somepath\mydb.mdb;Uid=Admin;Pwd=asdasd;""Driver={Microsoft Access Driver (*.mdb)};Dbq=\somepath\mydb.mdb;SystemDB=\somepath\mydb.mdw;","admin", ""(2)OLEDB, OleDbConnection (.NET)"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=asdasd;""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;","admin", "" "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;","admin", "" 3.Oracle(1)ODBC"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;""Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"(2)OLEDB, OleDbConnection (.NET)"Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;""Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;""Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;"(3)OracleConnection (.NET)"Data Source=Oracle8i;Integrated Security=yes";4.MySql(1)ODBC"Driver={mySQL};Server=mySrvName;Option=16834;Database=mydatabase;""Driver={mySQL};Server=;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;" (2)OLEDB, OleDbConnection (.NET)"Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;"(3)MySqlConnection (.NET)"Data Source=server;Database=mydb;User ID=username;Password=pwd;Command Logging=false"5.IBM DB2(1)OLEDB, OleDbConnection (.NET) from ms"Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;PackageCollection=MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW""Provider=DB2OLEDB;APPC Local LU Alias=MyAlias;APPC Remote LU Alias=MyRemote;Initial Catalog=MyCtlg;PackageCollection=MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW"6.SyBase(1)ODBC"Driver={SYBASE SYSTEM 11};Srvr=Aron1;Uid=username;Pwd=password;""Driver={INTERSOLV 3.10 32-BIT Sybase};Srvr=Aron1;Uid=username;Pwd=password;"7. Informix(1)ODBC2.Driver={INFORMIX3.30 32BIT};Host=hostname;Server=myserver;Service=service-name;Protocol=olsoctcp;Database=mydb;UID=username;PWD=myPwd"Driver={Informix-CLI 2.5 (32 Bit)};Server=myserver;Database=mydb;Uid=username;Pwd=myPwd"8.DSN(1)ODBC"DSN=myDsn;Uid=username;Pwd=;""FILEDSN=c:\myData.dsn;Uid=username;Pwd=;"9.Excel(1)ODBC"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;"(2)OLE DB"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=Excel 8.0;HDR=Yes;""HDR=Yes;"。
ODBC和OLEDB的连接字符串
ODBC和OLEDB的连接字符串ODBC连接适合数据库类型连接方式access"Driver={microsoft accessdriver(*.mdb)};dbq=*.mdb;uid=admin;pwd=pass;"dBase"Driver={microsoft dbase driver(*.dbf)};driverid=277;dbq=------------;"oracle"Driver={microsoft odbc fororacle};server=oraclesever.world;uid=admin;pwd=pass;"MSSQL server"Driver={sqlserver};server=servername;database=dbname;uid=sa;pwd=pass;"MS text"Driver={microsoft text driver(*.txt;*.csv)};dbq=-----;extensions=asc,csv,tab,txt;Persist SecurityInfo=false;"Visual Foxpro"Driver={microsoft Visual Foxprodriver};sourcetype=DBC;sourceDB=*.dbc;Exclusive=No;"MySQL"Driver={mysql};database=yourdatabase;uid=username;pwd=yourpassword;option=16386;"SQLite"Driver={SQLite3ODBC Driver};Database=D:/SQLite/*.db" PostgreSQL"Driver={PostgreSQLANSI};server=127.0.0.1;uid=admin;pwd=pass;database=databaseName" OLEDB连接适合的数据库类型连接方式access"Provider=microsoft.jet.oledb.4.0;data source=your_database_path;user id=admin;password=pass;"oracle"Provider=OraOLEDB.Oracle;data source=dbname;userid=admin;password=pass;"MS SQL Server"Provider=SQLOLEDB;data source=machinename;initial catalog=dbname;userid=sa;password=pass;"MS text"Provider=microsof.jet.oledb.4.0;data source=your_path;Extended Properties'text;FMT=Delimited'"。
VB与数据库连接的几种方法
VB与数据库连接的几种方法在VB中，有多种方法可以连接数据库。
以下是几种常见的方法：1. 使用OLE DB连接：OLE DB是Microsoft提供的一种连接数据库的标准接口，可以连接各种类型的数据库。
可以通过VB中的ADO(ActiveX Data Objects)对象来使用OLE DB连接数据库。
以下是一个使用OLE DB连接数据库的示例代码：```Dim conn As New ADODB.Connectionconn.ConnectionString = "Provider=SQLOLEDB;DataSource=ServerName;Initial Catalog=DatabaseName;UserID=Username;Password=Password"conn.Open```上述代码中，通过设置ConnectionString属性来指定数据库连接字符串，其中包含了数据库类型、服务器名称、数据库名称、用户名和密码等信息。
然后调用Open方法打开数据库连接。
2. 使用ODBC连接：ODBC(Open Database Connectivity)也是一种标准的数据库连接接口，可以连接多种类型的数据库。
可以通过VB中的ODBC对象来使用ODBC连接数据库。
以下是一个使用ODBC连接数据库的示例代码：```Dim conn As New ADODB.Connectionconn.ConnectionString = "Driver={SQL Server Native Client 11.0};Server=ServerName;Database=DatabaseName;Uid=Username;Pwd=P assword;"conn.Open```上述代码中，通过设置ConnectionString属性来指定ODBC连接字符串，其中包含了ODBC驱动程序名称、服务器名称、数据库名称、用户名和密码等信息。
oledb 连接用法
oledb 连接用法OLEDB连接用法OLEDB (Object Linking and Embedding Database) 是一种面向对象的数据库连接接口，它提供了一种统一的方式访问不同类型的数据源，如关系型数据库、电子表格、文本文件等。
使用OLEDB连接可以实现数据的读取、写入、更新和删除等操作。
本文将详细介绍OLEDB连接的用法，包括连接字符串的构建、数据源的选择、连接对象的创建和使用等。
希望本文可以帮助读者更好地理解并应用OLEDB连接。
一、连接字符串的构建在使用OLEDB连接之前，首先需要构建正确的连接字符串。
连接字符串可以理解为连接数据库所需的一系列参数的集合。
下面是一个连接字符串的示例：Provider=Microsoft.Jet.OLEDB.4.0; DataSource=D:\Data\mydatabase.mdb; User Id=admin; Password=;上述示例中的连接字符串用于连接Microsoft Access数据库。
其中，Provider指定了所使用的提供程序，Data Source指定了数据源的路径和名称，User Id和Password指定了连接数据库所需的用户名和密码。
根据所使用的数据库类型的不同，连接字符串的格式也会有所不同。
可通过查阅相关文档或搜索引擎，找到对应数据库类型的连接字符串格式。
二、数据源的选择OLEDB连接支持访问多种类型的数据源，常见的有Microsoft Access、Microsoft Excel、SQL Server等。
在选择数据源时，需要明确所需操作的数据类型以及数据源的特点。
如果需要操作的是关系型数据库，如SQL Server，可以使用SQL Server 提供的本机的API，效率更高。
同时，还可以根据需要，选择使用不同版本的OLEDB提供程序，如SQL Server Native Client、OLEDB Provider for SQL Server等。
ODBC和OleDb访问数据库的连接字符串
C# 中ODBC和OleDb访问数据库的连接字符串因为工作需要自学数据库的访问，查看相关书籍时，都是以OleDb类进行.Net开发为例，虽然也提到ODBC和OleDb在原理上是一样，但是没有实际的例子，而我恰恰需要通过ODBC来访问数据库，这就碰到连接字符串问题，下面介绍一种简单的查找连接字符串的方法，前提是你用的是VisualStudio开发工具。
步骤一：先在控制面板的数据源ODBC中设定好系统DSN，方法如下；打开“控制面板”—>“管理工具”—>“数据源（ODBC）”，打开“ODBC 数据源管理器”，设置系统DSN或用户DSN。
单击“添加”按钮进入创建新数据源，选择数据源的驱动程序，因为我要演示的是通过ODBC访问本地Access数据库，所以此处选择“Microsoft for Access Driver(*mdb,*accdb)”,其中mdb文件的Access是Access2003数据库文件的后缀，accdb文件Access2007和以上版本数据库文件的后缀，accdb文件转换为mdb需要在Office2007中打开accdb文件然后另存为Access2003数据库，后缀名自动变为mdb，这两种格式的文件数据库连接组件不一样。
当然你也可以通过ODBC访问SQL Serve等其他数据库，虽然使用ODBC能以统一的方式处理所有的数据库，但是使用ODBC来进行.NET开发的时候，其速度要比OleDB、SQL Server慢很多。
选择数据源的驱动程序后自动跳入下面界面，“数据源名”自己设定，此处命名为DataBase，在“数据库”中点击“选择”，选择需要通过ODBC连接的数据库，选择好后在“数据库：”右侧会显示数据库的路径如图。
单击“确定”后返回“ODBC数据源管理器”，此时在系统数据源中已经添加了一个名称为“DataBase”的“Microsoft for Access Driver(*mdb,*accdb)”的驱动程序。
常用ADO连接数据库字符串大全
OLE DB Providers, Remote Data Services (RDS), MS Remote, MS DataShape.Also included are connection strings for each .NET Managed Provider (SQLClient, OLEDB, and ODBC).These sample connection strings are compiledby Carl Prothman, a Microsoft Visual Basic MVPEnjoy!Table of ContentsODBC DSN Connections DSNFile DSNODBC DSN-Less Connections ODBC Driver for AS/400ODBC Driver for AccessODBC Driver for dBASEODBC Driver for ExcelODBC Driver for MySQLODBC Driver for OracleODBC Driver for ParadoxODBC Driver for SQL ServerODBC Driver for SybaseODBC Driver for Sybase SQL AnywhereODBC Driver for TextODBC Driver for TeradataODBC Driver for Visual FoxProOLE DB Data Link Connections Data Link File (UDL)OLE DB Data Provider Connections OLE DB Provider for AS/400OLE DB Provider for Active Directory ServiceOLE DB Provider for DB2OLD DB Provider for Internet PublishingOLE DB Provider for Index ServerOLE DB Provider for Microsoft JetOLE DB Provider for ODBC DatabasesOLE DB Provider for Oracle (From Microsoft)OLE DB Provider for Oracle (From Oracle)OLE DB Provider for Simple ProviderOLE DB Provider for SQL ServerRemote Data Service (RDS) Connections RDS Data Control - Connect Property RDS Data Control - URL PropertyADO URL Connections ADO RecordsetMS Remote Provider Connections MS Remote - Access (Jet)MS Remote - SQL ServerData Shape Provider Connections MS DataShape - SQL Server.NET Managed Provider Connections SQL Client .NET Managed Provider (System.Data.SqlClient)OLE DB .NET Managed Provider (System.Data.OleDb)ODBC .NET Managed Provider (System.Data.ODBC)ODBC DSN ConnectionsUsing an ODBC DSN (Data Source Name) is a two step process.1) You must first create the DSN via the "ODBC Data Source Administrator" programfound in your computer's Control Panel (or Administrative Tools menu in Windows 2000).Make sure to create a SYSTEM DSN (not a USER DSN) when using ASP.Note: You can also create the DSN via VB code.2) Then use the following connection string - with your own DSN name of course. ;-)ODBC - DSNoConn.Open "DSN=AdvWorks;" & _"Uid=Admin;" & _"Pwd=;You can also create and use a File DSN. Then use the following ADO Connection string:ODBC - File DSNoConn.Open "FILEDSN=c:\somepath\mydb.dsn;" & _"Uid=Admin;" & _"Pwd=;"For more information, see: About ODBC data sources andHow to Use File DSNs and DSN-less ConnectionsNote: The problem with DSN is that Users can (and will) modify them (or delete by mistake),then your program won't work so well... So it's better to use a DSN-Less or OLE DB Providerconnection string with a Trusted Connection if possible!ODBC DSN-Less ConnectionsODBC Driver for AS/400oConn.Open "Driver={Client Access ODBC Driver (32-bit)};" & _"System=myAS400;" & _"Uid=myUsername;" & _"Pwd=myPassword;"For more information, see: A Fast Path to AS/400 Client/ServerODBC Driver for AccessFor Standard Security:oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _"Dbq=c:\somepath\mydb.mdb;" & _"Uid=Admin;" & _"Pwd=;"If you are using a Workgroup (System database):oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _"Dbq=c:\somepath\mydb.mdb;" & _"SystemDB=c:\somepath\mydb.mdw;", _"admin", ""If MDB is located on a network share:oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _"Dbq=\\myServer\myShare\myPath\myDb.mdb;"For more information, see: Microsoft Access Driver Programming Considerations ODBC Driver for dBASEoConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _"DriverID=277;" & _"Dbq=c:\somepath;"Note: Specify the filename in the SQL statement. For example:oRs.Open "Select * From user.dbf", oConn, , ,adCmdTextNote: MDAC 2.1 (or greater) requires the Borland Database Engine (BDE) to update dBase DBF files. (Q238431).For more information, see: dBASE Driver Programming ConsiderationsODBC Driver for ExceloConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _"DriverId=790;" & _"Dbq=c:\somepath\mySpreadsheet.xls;" & _"DefaultDir=c:\somepath;"For more information, see: Microsoft Excel Driver Programming ConsiderationsODBC Driver for MySQL (via MyODBC)To connect to a local databaseoConn.Open "Driver={mySQL};" & _"Server=MyServerName;" & _"Option=16834;" & _"Database=mydb;"To connect to a remote databaseoConn.Open "Driver={mySQL};" & _"Server=;" & _"Port=3306;" & _"Option=131072;" & _"Stmt=;" & _"Database=mydb;" & _"Uid=myUsername;" & _"Pwd=myPassword;"For more information, see: Programs Known to Work with MyODBCODBC Driver for OracleFor the current Oracle ODBC Driver from Microsoft:oConn.Open "Driver={Microsoft ODBC for Oracle};" & _"Server=OracleServer.world;" & _"Uid=myUsername;" & _"Pwd=myPassword;"For the older Oracle ODBC Driver from Microsoft:oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _"ConnectString=OracleServer.world;" & _"Uid=myUsername;" & _"Pwd=myPassword;"For more information, see: Connection String Format and AttributesODBC Driver for ParadoxoConn.Open "Driver={Microsoft Paradox Driver (*.db)};" & _"DriverID=538;" & _"Fil=Paradox 5.X;" & _"DefaultDir=c:\dbpath\;" & _"Dbq=c:\dbpath\;" & _"CollatingSequence=ASCII;"Note: MDAC 2.1 (or greater) requires the Borland Database Engine (BDE) to update Paradox ISAM fDBF files. (Q230126).For more information, see: Paradox Driver Programming ConsiderationsODBC Driver for SQL ServerFor Standard Security:oConn.Open "Driver={SQL Server};" & _"Server=MyServerName;" & _"Database=myDatabaseName;" & _"Uid=myUsername;" & _"Pwd=myPassword;"For Trusted Connection security:oConn.Open "Driver={SQL Server};" & _"Server=MyServerName;" & _"Database=myDatabaseName;" & _"Uid=;" & _"Pwd=;"' oroConn.Open "Driver={SQL Server};" & _"Server=MyServerName;" & _"Database=myDatabaseName;" & _"Trusted_Connection=yes;"To Prompt user for username and passwordoConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Driver={SQL Server};" & _"Server=MyServerName;" & _"DataBase=myDatabaseName;"For more information, see: SQLDriverConnect (ODBC)ODBC Driver for SybaseIf using the Sybase System 11 ODBC Driver:oConn.Open "Driver={SYBASE SYSTEM 11};" & _"Srvr=myServerName;" & _"Uid=myUsername;" & _"Pwd=myPassword;"If using the Intersolv 3.10 Sybase ODBC Driver:oConn.Open "Driver={INTERSOLV 3.10 32-BIT Sybase};" & _"Srvr=myServerName;" & _"Uid=myUsername;" & _"Pwd=myPassword;"For more information, see: Sybase System 10 ODBC Driver Reference Guide ODBC Driver for Sybase SQL AnywhereoConn.Open "ODBC; Driver=Sybase SQL Anywhere 5.0;" & _"DefaultDir=c:\dbpath\;" & _"Dbf=c:\sqlany50\mydb.db;" & _"Uid=myUsername;" & _"Pwd=myPassword;""Dsn="""";"Note: Including the DSN tag with a null string is absolutely critical or elseyou get the dreaded -7778 error.For more information, see: Sybase SQL Anywhere User GuideODBC Driver for TeradataoConn.Open "Provider=Teradata;" & _"DBCName=MyDbcName;" & _"Database=MyDatabaseName;" & _"Uid=myUsername;" & _"Pwd=myPassword;"For more information, see Teradata ODBC DriverODBC Driver for TextoConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _"Dbq=c:\somepath\;" & _"Extensions=asc,csv,tab,txt;" & _"Persist Security Info=False"Note: Specify the filename in the SQL statement. For example:oRs.Open "Select * From customer.csv", _oConn, adOpenStatic, adLockReadOnly, adCmdTextFor more information, see: Text File Driver Programming Considerations ODBC Driver for Visual FoxProWith a database container:oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _"SourceType=DBC;" & _"SourceDB=c:\somepath\mySourceDb.dbc;" & _"Exclusive=No;"Without a database container (Free Table Directory):oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _"SourceType=DBF;" & _"SourceDB=c:\somepath\mySourceDbFolder;" & _"Exclusive=No;"For more information, see: Visual FoxPro ODBC Driver and Q165492OLE DB Data Link ConnectionsData Link File (UDL)For Absolute Path:oConn.Open "File Name=c:\somepath\myDatabaseName.udl;"For Relative Path:oConn.Open "File Name=myDatabaseName.udl;"For more information, see: HOWTO: Use Data Link Files with ADONote: Windows 2000 no longer contains the "New | Microsoft Data Link" menu anymore. You can add the Data Link menu back in the menu list by running the "C:\Program Files\Common Files\System\Ole DB\newudl.reg" reg file,then right-click on the desktop and select "New | Microsoft Data Link" menu. Or you can also create a Data Link file by creating a text file and change it'sfile extension to ".udl", then double-click the file.OLE DB Provider ConnectionsOLE DB Provider for AS/400oConn.Open "Provider=IBMDA400;" & _"Data source=myAS400;""User Id=myUsername;" & _"Password=myPassword;"For more information, see: A Fast Path to AS/400 Client/ServerOLE DB Provider for Active Directory ServiceoConn.Open "Provider=ADSDSOObject;" & _"User Id=myUsername;" & _"Password=myPassword;"For more information, see: Microsoft OLE DB Provider for Microsoft Active Directory ServiceOLE DB Provider for DB2oConn.Open = "Provider=DB2OLEDB;" &"Network Transport Library=TCPIP;" &"Network Address=MyServer;" & _"Package Collection=MyPackage;" &"Host CCSID=1142""Initial Catalog=MyDB;" &"User ID=MyUsername;" & _"Password=MyPassword;"For more information, see: OLE DB Provider for DB2and INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2OLE DB Provider for Index ServeroConn.Open "Provider=msidxs;" & _"Data source=MyCatalog;"For more information, see: Microsoft OLE DB Provider for Microsoft Indexing ServiceOLE DB Provider for Internet PublishingoConn.Open "Provider=MSDAIPP.DSO;" & _"Data Source=http://mywebsite/myDir;" & _"User Id=myUsername;" & _"Password=myPassword;"For more information, see: Microsoft OLE DB Provider for Internet Publishing and Q245359OLE DB Provider for Microsoft JetFor standard security:oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=c:\somepath\myDb.mdb;" & _"User Id=admin;" & _"Password=;"If using a Workgroup (System Database):oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=c:\somepath\mydb.mdb;" & _"Jet OLEDB:System Database=MySystem.mdw;", _"admin", ""Note, remember to convert both the MDB and the MDW to the 4.0 database format when using the 4.0 OLE DB Provider.If MDB has a database password:oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=c:\somepath\mydb.mdb;" & _"Jet OLEDB:Database Password=MyDbPassword;", _"admin", ""If MDB is located on a network share:oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=\\myServer\myShare\myPath\myDb.mdb;If want to open up the Access database exclusively:oConn.Mode = adModeShareExclusiveoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=c:\somepath\myDb.mdb;" & _"User Id=admin;" & _"Password=;"For more information, see: OLE DB Provider for Microsoft Jet, Q191754,Q225048, Q239114, and Q271908You can also open an Excel Spreadsheet using the "OLE DB Provider for Microsoft Jet"oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=c:\somepath\myExcelSpreadsheet.xls;" & _"Extended Properties=""Excel 8.0;HDR=Yes;"";"Where "HDR=Yes" means that there is a header row in the cell range(or named range), so the provider will not include the first row of the selection into the recordset. If "HDR=No", then the provider will include the first row of the cell range (or named ranged) into the recordset.For more information, see: Q278973You can also open a Text File using the "OLE DB Provider for Microsoft Jet"oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=c:\somepath\;" & _"Extended Properties=""text;HDR=Yes;FMT=Delimited;"";"' Then open a recordset based on a select on the actual fileoRs.Open "Select * From MyTextFile.txt", oConn, adOpenStatic, adLockReadOnly, adCmdTextFor more information, see: Q262537OLE DB Provider for ODBC DatabasesFor Access (Jet):oConn.Open "Provider=MSDASQL;" & _"Driver={Microsoft Access Driver (*.mdb)};" & _"Dbq=c:\somepath\mydb.mdb;" & _"Uid=myUsername;" & _"Pwd=myPassword;"For SQL Server:oConn.Open "Provider=MSDASQL;" & _"Driver={SQL Server};" & _"Server=myServerName;" & _"Database=myDatabaseName;" & _"Uid=myUsername;" & _"Pwd=myPassword;"For more information, see: Microsoft OLE DB Provider for ODBCOLE DB Provider for Oracle (from Microsoft)oConn.Open "Provider=msdaora;" & _"Data Source=MyOracleDB;" & _"User Id=myUsername;" & _"Password=myPassword;"For more information, see: Microsoft OLE DB Provider for OracleOLE DB Provider for Oracle (from Oracle)For Standard Security:oConn.Open "Provider=OraOLEDB.Oracle;" & _"Data Source=MyOracleDB;" & _"User Id=myUsername;" & _"Password=myPassword;"For a Trusted Connection:oConn.Open "Provider=OraOLEDB.Oracle;" & _"Data Source=MyOracleDB;" & _"User Id=/;" & _"Password=;"' OroConn.Open "Provider=OraOLEDB.Oracle;" & _"Data Source=MyOracleDB;" & _"OSAuthent=1;"Note: "Data Source=" must be set to the appropriate Net8 name which is known to the naming method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; for Oracle Names, it is the Net8 Service Name.For more information, see: Connecting to an Oracle Database(Note, if you get a Logon dialog, then click Cancel, then perform a one-time free signup with Oracle's TechNet system)OLE DB Provider for Simple ProviderThe Microsoft OLE DB Simple Provider (OSP) allows ADO to access any data for which a provider hasbeen written using the OLE DB Simple Provider Toolkit. Simple providers are intended to access datasources that require only fundamental OLE DB support, such as in-memory arrays or XML documents.OSP in MDAC 2.6 has been enhanced to support opening hierarchical ADO Recordsets over arbitraryXML files. These XML files may contain the ADO XML persistence schema, but it is not required. Thishas been implemented by connecting the OSP to the MSXML2.DLL, therefore MSXML2.DLL or newer isrequired.oConn.Open "Provider=MSDAOSP;" & _"Data Source=MSXML2.DSOControl.2.6;"oRS.Open "http://WebServer/VirtualRoot/MyXMLFile.xml", oConnFor more information, see: Microsoft OLE DB Simple Provider OLE DB Provider for SQL ServerFor Standard Security:oConn.Open "Provider=sqloledb;" & _"Data Source=myServerName;" & _"Initial Catalog=myDatabaseName;" & _"User Id=myUsername;" & _"Password=myPassword;"For a Trusted Connection:oConn.Open "Provider=sqloledb;" & _"Data Source=myServerName;" & _"Initial Catalog=myDatabaseName;" & _"Integrated Security=SSPI;"To connect to a "Named Instance" (SQL Server 2000)oConn.Open "Provider=sqloledb;" & _"Data Source=myServerName\Inst2;" & _"Initial Catalog=myDatabaseName;" & _"User Id=myUsername;" & _"Password=myPassword;"To Prompt user for username and password:oConn.Provider = "sqloledb"oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Data Source=myServerName;" & _"Initial Catalog=myDatabaseName;"To connect via an IP address:oConn.Open "Provider=sqloledb;" & _"Data Source=xxx.xxx.xxx.xxx,1433;" & _"Network Library=DBMSSOCN;" & _"Initial Catalog=myDatabaseName;" & _"User ID=myUsername;" & _"Password=myPassword;"Note:- xxx.xxx.xxx.xxx is an IP address- "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than Named Pipes (Q238949)- 1433 is the default port number for SQL Server- You can also add "Encrypt=yes" for encryptionFor more information, see: Microsoft OLE DB Provider for SQL ServerRemote Data Service (RDS) ConnectionsThe following examples show how to connect to a remote database using the RDS Data Control.When using the RDS DataControl's Server/SQL/Connect properties, the RDS DataControl uses theRDS DataFactory on the remote server. If you use the RDS DataControl's URL property,then the RDS DataFactory is not used at all.WARNING: The RDS DataFactory can be a major security hole if not setup and configured correctly!For more information, see RDS FAQ #24RDS DataControl - Connect PropertyWith the RDS default handler disabled (not recommend due to security risks):With oRdc.Server = "http://carl2".Sql = "Select * From Authors Where State = 'CA'".Connect = "Provider=sqloledb;" & _"Data Source=(local);" & _"Initial Catalog=pubs;" & _"User Id=sa;" & _"Password=;".RefreshEnd WithWith the RDS default handler enabled (recommend):With oRdc.Server = "http://carl2".Handler = "MSDFMAP.Handler".Connect = "Data Source=MyConnectTag;".Sql = "MySQLTag(""CA"")".RefreshEnd WithThe corresponding CONNECT and SQL sections in the default handler\WINNT\MSDFMAP.INI file would be:[connect MyConnectTag]Access = ReadWriteConnect = "Provider=sqloledb;Data Source=(local);Initial Catalog=pubs;User Id=sa;Password=;"[sql MySQLTag]Sql = "Select * From Authors Where State = '?'"For more information about the RDS Default Handler, see:Q243245, Q230680, and RDS Customization Handler Microsoft articlesRDS DataControl - URL PropertyTo get records from a remote database:With oRdc.URL = "http://carlp0/Authors_GetByState.asp?state=CA".RefreshEnd WithTo save, set the URL property to an ASP web page:With oRdc.URL = "http://carlp0/rdsdatacontrol/Authors_Save.asp".SubmitChangesEnd WithFor more information, see: RDS URL PropertyADO URL ConnectionsADO 2.5+ allows you to open up a Recordset based on XML returned from an ASP file over HTTP.This feature doesn't use RDS at all.ADO RecordsetTo get records from a remote database:oRs.Open "http://carlp0/Authors_GetByState.asp?state=CA", , _adOpenStatic, adLockBatchOptimisticTo save changes, you must use the MSXML's XMLHTTP object to POST back theupdated XML.The Recordset's Update and UpdateBatch methods will not work in this case.' Save Recordset into StreamSet oStm = New ADODB.StreamoRs.Save oStm, adPersistXML' Use MSXML's XMLHTTP object to open ASP and post a XML streamSet oXMLHTTP = New MSXML2.XMLHTTP30oXMLHTTP.Open "POST", "http://carlp0/Authors_Save.asp", False oXMLHTTP.Send oStm.ReadText' If an error occurredIf oXMLHTTP.Status = 500 ThenDebug.Print oXMLHTTP.statusTextEnd IfFor more information, see: ADO Recordset's Open MethodMS Remote Provider ConnectionsThe following connections strings use Microsoft's remote provider (MS Remote). The MS Remoteprovider tells ADO to communicate with the remote server (via the RDS DataFactory) and to usethe remote provider that is installed on the remote server.WARNING: The RDS DataFactory can be a major security hole if not setup and configured correctly!For more information, see RDS FAQ #24MS Remote - Access (Jet)If you want to use an ODBC DSN on the remote machine:oConn.Open "Provider=MS Remote;" & _"Remote Server=http://myServerName;" & _"Remote Provider=MSDASQL;" & _"DSN=AdvWorks;" & _"Uid=myUsername;" & _"Pwd=myPassword;"If you want to use an OLE DB Provider on the remote machine:oConn.Open "Provider=MS Remote;" & _"Remote Server=http://myServerName;" & _"Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=c:\somepath\mydb.mdb;", _"admin", ""If you want to use an OLE DB Provider on the remote machine (via RDS DataFactory Default Handler):oConn.Open "Provider=MS Remote;" & _"Remote Server=http://myServerName;" & _"Handler=MSDFMAP.Handler;" & _"Data Source=MyAdvworksConn;"The corresponding entry in the \winnt\Msdfmap.ini file would be:[connect MyAdvworksConn]Access = ReadWriteConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=mydb.mdb;" & _"User Id=admin;" & _"Password=;"MS Remote - SQL ServerIf you want to use an ODBC DSN on the remote machine:oConn.Open "Provider=MS Remote;" & _"Remote Server=http://myServerName;" & _"Remote Provider=MSDASQL;" & _"DSN=myDatabaseName;" & _"Uid=myUsername;" & _"Pwd=myPassword;"If you want to use an OLE DB Provider on the remote machine:oConn.Open "Provider=MS Remote;" & _"Remote Server=http://myServerName;" & _"Remote Provider=SQLOLEDB;" & _"Data Source=myServerName;" & _"Initial Catalog=myDatabaseName;" & _"User ID=myUsername;" & _"Password=myPassword;"If you want to use an OLE DB Provider on the remote machine (via RDS DataFactoryDefault Handler):oConn.Open "Provider=MS Remote;" & _"Remote Server=http://myServerName;" & _"Handler=MSDFMAP.Handler;" & _"Data Source=MyPubsConn;"The corresponding entry in the \winnt\Msdfmap.ini file would be:[connect MyPubsConn]Access = ReadWriteConnect = "Provider=SQLOLEDB;" & _"Data Source=myServerName;" & _"Initial Catalog=myDatabaseName;" & _"User ID=myUsername;" & _"Password=myPassword;"For more information, see: Microsoft OLE DB Remoting Provider and Q240838Data Shape Provider ConnectionsMS DataShape - SQL ServeroConn.Open "Provider=MSDataShape;" & _"Data Provider=SQLOLEDB;" & _"Data Source=mySQLServerName;" & _"Initial Catalog=myDatabase;" & _"User ID=myUsername;" & _"Password=myPassword;"Then use a Shape command with SQL strings:sSQL = "SHAPE {select * from authors} " & _"APPEND ({select * from titleauthor} AS chapter " & _"RELATE au_id TO au_id)"Or use a Shape command that calls Stored Procedures:sSQL = "SHAPE {exec spAuthors_LoadAll} " & _"APPEND ({exec spTitleAuthor_LoadAll} AS chapter " & _"RELATE au_id TO au_id)"For more information, see: Microsoft Data Shaping Service for OLE DB and Q288409.NET Managed Provider ConnectionsSQL Client .NET Managed Provider (System.Data.SqlClient)The SQL Client .NET Managed Provide allows you to connect to a Microsoft SQL Server 7.0or 2000 database. For Microsoft SQL Server 6.0 or earlier, use the OLE DB .NET Data Providerwith the "SQL Server OLE DB Provider" (SQLOLEDB).Dim oSQLConnection As SqlClient.SqlConnectionDim sConnString As StringsConnString = "Data Source=(local);" & _"Initial Catalog=NorthWind;" & _"Integrated Security=SSPI;" & _"Pooling=True;" & _"Min Pool Size=10;" & _"Max Pool Size=50;" & _"Connection Lifetime=30;" & _"Connection Reset=True;" & _"Enlist=True;"oSQLConnection = New SqlClient.SqlConnection(sConnString) oSQLConnection.Open()For more information, see: System.Data.SQL Namespace and .NET Data Providers Note: 'SQL' namespace got renamed to 'SQLClient'OLE DB .NET Managed Provider (System.Data.OleDb)The OLE DB .NET Data Provider uses native OLE DB through COM interop to enable data access.To use the OLE DB .NET Data Provider, you must also use an OLE DB provider (e.g. SQLOLEDB,MSDAORA, or Microsoft.JET.OLEDB.4.0).For SQL Server OLE DB Provider (for SQL Server 6.0 or earlier)Dim oOleDbConnection As OleDb.OleDbConnectionDim sConnString As StringsConnString = "Provider=sqloledb;" & _"Data Source=myServerName;" & _"Initial Catalog=myDatabaseName;" & _"User Id=myUsername;" & _"Password=myPassword;"oOleDbConnection = New OleDb.OleDbConnection(sConnString) oOleDbConnection.Open()。
数据库连接-连接字符串
一.数据库的连接字符串string connectionstr = "Data Source=PC201305072142\\SQLEXPRESS;Da taBase=db_stu;User ID=sa;pwd=123456";Data Source后加你的连接服务器，DataBase后加连接的数据库ID=sa;pwd=123456ID:用户名Pwd:密码二．C#语言实现SQL SERVER、access连接SQL SERVER：添加 using System.Data.SqlClient;SqlConnection con =new SqlConnection("server=.;database=数据库名;uid=sa;pwd=密码");//密码是你数据库sa的登录的密码或者是 SqlConnection con = new SqlConnection("Data source=你的数据库服务器;Initial Catalog=要连接的数据库名;trusetd_connection=sspi");//c是window身份认证的方式access:添加using System.Data.OleDb;OleDbConnection omd=newOleDbConnection("Provider=Microsoft.jet.oldb.4.0;database=数据库的相对路径");//路径是想对你程序运行的路径三.问：如何手动设置数据库连接（数据库的IP、登录密码、用户名称以让用户自己输入一次，然后把它们保存到配置文件中，以后就不需要再输了，直接从配置文件中读取就行了）答：1、在解决方案中添加一个新的类，命名CL_Conn，用于连接数据库。
完整代码如下：using System;using System.Collections.Generic;using ponentModel;using System.Text;using System.Windows.Forms;using Microsoft.Data.ConnectionUI;namespace CL_Conn{public class Conn{/// <summary>/// 获取 自带的数据库连接对话框的数据库连接信息/// </summary>/// <returns>数据库连接</returns>public string GetDatabaseConnectionString(){string connString = String.Empty;Microsoft.Data.ConnectionUI.DataConnectionDialog connDialog = new Microsoft.Data.ConnectionUI.DataConnectionDialog();// 添加数据源列表，可以向窗口中添加自己程序所需要的数据源类型必须增加以下几项中任一一项connDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.AccessDataSo urce); // AccessconnDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.OdbcDataSou rce); // ODBCconnDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.OracleDataSo urce); // OracleconnDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.SqlDataSourc e); // Sql ServerconnDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.SqlFileDataSo urce); // Sql Server File// 初始化connDialog.SelectedDataSource =Microsoft.Data.ConnectionUI.DataSource.SqlDataSource;connDialog.SelectedDataProvider =Microsoft.Data.ConnectionUI.DataProvider.SqlDataProvider;//只能够通过DataConnectionDialog类的静态方法Show出对话框//不同使用dialog.Show()或dialog.ShowDialog()来呈现对话框if(Microsoft.Data.ConnectionUI.DataConnectionDialog.Show(connDialog) == DialogResult.OK){connString = connDialog.ConnectionString;}return connString;}}}2、编译。
ADO连接数据库字符串大全
oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
Note: MDAC 2.1 (or greater) requires the Borland Database Engine (BDE) to update Paradox ISAM fDBF files. (Q230126).
ODBC Driver for SQL Server
"Trusted_Connection=yes;"
To Prompt user for username and password
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"SystemDB=c:\somepath\mydb.mdw;", _
"admin", ""
If MDB is located on a network share:
"DriverID=277;" & _
"Dbq=c:\somepath;"
VB使用ADODB操作数据库的常用方法
VB使用ADODB操作数据库的常用方法在Visual Basic(VB)中，我们可以使用ADODB (ActiveX Data Objects)来操作数据库。
ADODB提供了一套用于连接和访问数据库的对象和方法。
下面是VB中使用ADODB操作数据库的常用方法：1.ADODB连接数据库：- 创建一个ADODB连接对象：使用`Dim conn As NewADODB.Connection`。
- 设置连接字符串：使用`conn.ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\path\mydatabase.mdb"`，其中Data Source为数据库文件的路径。
- 打开连接：使用`conn.Open`。
2.ADODB执行SQL查询：-设置命令对象的属性：- `cmd.ActiveConnection = conn`：设置命令对象的连接。
- 执行查询并返回结果集：使用`Dim rs As ADODB.Recordset = cmd.Execute`。
- 循环遍历结果集：使用`Do While Not rs.EOF`和`rs.MoveNext`来遍历结果集的每一行。
- 获取查询结果：使用`rs.Fields("fieldname").Value`来获取特定字段的值。
3.ADODB执行SQL更新：-设置命令对象的属性：- `cmd.ActiveConnection = conn`：设置命令对象的连接。
- 执行更新：使用`cmd.Execute`来执行更新语句。
4.ADODB事务管理：- 开始事务：使用`conn.BeginTrans`来开始一个事务。
- 回滚事务：使用`conn.RollbackTrans`来回滚事务。
5.ADODB断开连接：- 关闭连接：使用`conn.Close`来关闭连接。
数据库连接字符串
数据库连接字符串/doc/e814683476.html数据库连接字符串总结标签: /doc/e814683476.html数据库连接字符串⼀、使⽤OleDbConnection对象连接OLE DB数据源1．连接Access 数据库Access 2000： “provider=Microsoft.Jet.Oledb.3.5;Data Source=Access ⽂件路径”Access 2003： “provider=Microsoft.Jet.Oledb.4.0;Data Source=Access ⽂件路径”Access 2007： “provider=Microsoft.Ace.Oledb.12.0;DataSource=Access⽂件路径”备注：Access数据库只提供两个连接属性provider（数据提供程序）和data source（数据源）；Access2000\2003的⽂件格式是“.mdb”，,Access2007的⽂件格式是“.accdb”;Access的数据提供程序版本是向下兼容的，在Win7下测试使⽤Microsoft.Jet.OLEDB.3.5提⽰“未在本地计算机上注册“Microsoft.Jet.OLEDB.3.5”提供程序。
”，改⽤Microsoft.Jet.OLEDB.4.0或者Microsoft.Ace.OLEDB12.0完全可以访问Access2000的数据库⽂件。
当然也可以尝试使⽤微软提供的MDAC 来修改provider的版本。
2．连接Excel数据库Excel 2003： “provider=Microsoft.Jet.OLEDB.4.0;DataSource=Access⽂件路径;extended properties=excel 8.0”Excel 2007： “provider=Microsoft.Ace.OLEDB.12.0;DataSource=Access⽂件路径;extended properties=excel 12.0”备注：在代码中引⽤⼯作表时，应将表名表⽰为“[⼯作表名$]”，遇到字段为数据库保留关键字时，给该字段名加上[]以⽰区别，如定义select 语句时：string connStr=”select * from [login$] where username=’abc’ and [password]=’abc123’ ”；如果在数据表中⽤数字作为⽂本类型数据时，则应在数字前加单引号将默认的数值强⾏设定为⽂本类型。
通过OleDB连接方式,访问Access,Excel数据库
通过OleDB连接方式,访问Access,Excel数据库.OleDbConnectionStringBuilder oleConStr = new OleDbConnectionStringBuilder();Access 连接信息oleConStr.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + myDBFileName + ";User Id=admin;Password=;";Excel 连接信息string myExcelConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m yDBFileName + ";Extended Properties=Excel 8.0;";myExcelConStr =@"Provider=Microsoft.Jet.OLEDB.4.0;" +@"Data Source=" + myDBFileName + ";" +@"Extended Properties=" + Convert.ToChar(34).ToString() +@"Excel 8.0;" + ExcelConnectionOptions() + Convert.ToChar(34).To String();public bool Headers{get { return HasHeaders; }set { HasHeaders = value; }}public bool MixedData{get { return IsMixedData; }set { IsMixedData = value; }}private string ExcelConnectionOptions(){string strOpts = "";if (this.MixedData == true)strOpts += "Imex=2;";if (this.Headers == true)strOpts += "HDR=Yes;";elsestrOpts += "HDR=No;";return strOpts;}=======查询数据===========OleDbConnection OleConn = new OleDbConnection(oleConStr.ConnectionString);OleDbCommand cmd;string SqlStr = " Select top 1 * from [" + newMaTolName + "] ";cmd = new OleDbCommand(SqlStr, OleConn);OleDbDataAdapter OleDapt = new OleDbDataAdapter();OleDapt.SelectCommand = cmd;OleConn.Open();DataSet myDS = new DataSet();OleDapt.Fill(myDS);注意:如果是Excel数据库需在表名后加"$";=======增加字段===========SqlStr = " ALTER TABLE [" + newMaTolName.Replace("$","") + "] ADD IsRea d decimal,RowNo long IDENTITY(1,1) ";cmd = new OleDbCommand(SqlStr, OleConn);int affectRows = cmd.ExecuteNonQuery();=======在DataSet表中增加一列=======myDS 是已保存有数据的DataSetif (myDS.Tables[0].Rows.Count < 1){return IsSuccess;}DataTable dt = myDS.Tables[0];DataColumn dc = new DataColumn();dc.DataType = Type.GetType("System.String");dc.DefaultValue = 1;dc.ColumnName = "MaTolName";dt.Columns.Add(dc);//给该列赋值for (int i = 0; i < dt.Rows.Count; i++){dt.Rows[i]["MaTolName"] = myMaTolName;}dt.DataSet.AcceptChanges();=======获取数据源的框架信息,如其中的表名等.============== System.Data.DataTable dt= OleConn.GetOleDbSchemaTable(OleDbSchemaGuid. Tables, null);=======获取与更新 DataGrid中的数据改变,并更新数据源.==========================DataTable dtChanges = dt.GetChanges();OleDbCommand oleCmd;OleDbDataAdapter oleda = new OleDbDataAdapter(oleCmd); oleda.InsertCommand = new OleDbCommand(strInsert,oleConn); oleda.UpdateCommand = new OleDbCommand(strUpdate,oleConn); oleAdapter.Update(dtChanges);private void CreateNewTable(){System.Data.DataTable table = new DataTable("Student");DataColumn column;DataRow row;column = new DataColumn();column.DataType = System.Type.GetType("System.Int32"); column.ColumnName = "SID";column.AutoIncrement = true;column.ReadOnly = false;column.Unique = true;table.Columns.Add(column);column = new DataColumn();column.DataType = System.Type.GetType("System.String"); column.ColumnName = "Name";column.AutoIncrement = false;column.Caption = "Name";column.ReadOnly = false;column.DefaultValue = "myName";column.Unique = false;table.Columns.Add(column);//设置表的主键DataColumn[] PrimaryKeyColumns = new DataColumn[1]; PrimaryKeyColumns[0] = table.Columns["SID"];table.PrimaryKey = PrimaryKeyColumns;table.AcceptChanges();DataSet dataSet = new DataSet();dataSet.Tables.Add(table);for (int i = 0; i <= 2; i++){row = table.NewRow();//row["SID"] = i;//row["Name"] = "name " + i;table.Rows.Add(row);}dataSet.AcceptChanges();this.dataGrid1.DataSource = dataSet.Tables[0];}=======表的复制=========//创建新表dataTableDestDataTable dataTableDest = new DataTable();//将表dataTableSource的结构复制到新表dataTableDest中dataTableDest = dataTableSource.Clone();//然后再复制数据到新表中foreach(DataRow dr in dataTableSource.Rows){//使用ImportRow()方法复制数据。
数据库连接字符串大全
数据库连接字符串大全转自：SQL Server ODBC Standard Security："Driver={SQL Server}；Server=Aron1；Database=pubs；Uid=sa；Pwd=asdasd；"Trusted connection："Driver={SQL Server}；Server=Aron1；Database=pubs；Trusted_Connection=yes；"PRompt for username and passWord：oConn.Properties("Prompt")=adPromptAlways oConn.Open"Driver={SQL Server}；Server=Aron1；DataBase=pubs；"OLE DB,OleDbConnection(.NET)Standard Security："Provider=sqloledb；Data Source=Aron1；Initial Catalog=pubs；User Id=sa；Password=asdasd；"Trusted Connection："Provider=sqloledb；Data Source=Aron1；Initial Catalog=pubs；Integrated Security=SSPI；"(use serverName\instanceName as Data Source to use an specifik SQLServer instance,only SQLServer2000)Prompt for username and password：oConn.Provider="sqloledb"oConn.Properties("Prompt")=adPromptAlways oConn.Open"Data Source=Aron1；Initial Catalog=pubs；"Connect via an 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,at the end of the Data Source is the port to use(1433 is the default))SqlConnection(.NET)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"(both connection strings produces the same result)Trusted Connection："Data Source=Aron1；Initial Catalog=pubs；IntegratedSecurity=SSPI；"-or-"Server=Aron1；Database=pubs；Trusted_Connection=True；"(both connection strings produces the same result)(use serverName\instanceName as Data Source to use an specifik SQLServer instance,only SQLServer2000)Connect via an IP address："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,at the end of the Data Source is the port to use(1433 is the default))Declare the SqlConnection：C#：using System.Data.SqlClient；SqlConnection oSQLConn=new SqlConnection()；oSQLConn.ConnectionString="my connection string"；oSQLConn.Open()；：Imports System.Data.SqlClient Dim oSQLConn As SqlConnection=New SqlConnection()oSQLConn.ConnectionString="my connection string"oSQLConn.Open()Data Shape MS Data Shape"Provider=MSDataShape；Data Provider=SQLOLEDB；Data Source=Aron1；Initial Catalog=pubs；User ID=sa；Password=asdasd；"Want to learn data shaping?Check out 4GuyfFromRolla's greatarticle about Data Shaping Read more How to define which network 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 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 note！When connecting through the SQLOLEDB provider use the syntax Network Library=dbmssocn and when connecting through MSDASQL provider use the syntax Network=dbmssocn All SqlConnection connection string properties This table shows all connection string properties for the SqlConnection object.Most of the properties are also used in ADO.All properties and descriptions is from msdn.Name Default Description application Name The name of the application,or'.Net SqlClient Data Provider'if no application name is provided.AttachDBFilename-or-extended properties-or-Initial File Name The name of the primary file,including the full path name,of an attachable database.The database name must be specified with the keyword'database'.Connect Timeout-or-Connection Timeout 15 The length of time(in seconds)to wait for aconnection to the server before terminating the attempt and generating an error.Connection Lifetime 0When aconnection is returned to the pool,its creation time is com pared with the current time,and the connection is destroyed if that time span(in seconds)exceeds the value specified by connection eful in clustered configurations to force load balancing between arunning server and aserver just brought on-line.Connection Reset'true'Determines whether the database connection is reset when being removed from the pool.Setting to'false'avoids making an additional server round-trip when obtaining aconnection,but the programmer must be aware that the connection state is not being reset.Current Language The SQL Server Language record name.Data Source-or-Server-or-Address-or-Addr-or-Network Address The name or network address of the instance of SQL Server to which to connect.Enlist'true'When true,the pooler automatically enlists the connection in the creation thread's current transaction context.Initial Catalog-or-Database The name of the database.Integrated Security-or-Trusted_Connection'false'Whether the connection is to be asecure connection or not.Recognized values are'true','false',and'sspi',which is equivalent to'true'.Max Pool Size 100 The maximum number of connections allowed in the pool.Min Pool Size 0The minimum number of connections allowed in the pool.Network Library-or-Net'dbmssocn'The network library used to establish aconnection to an instance of SQL Server.Supported values include dbnmpntw(Named Pipes),dbmsrpcn(Multiprotocol),dbmsadsn(AppleTalk),dbmsgnet(VIA),dbmsipcn(Shared Memory)and dbmsspxn(IPX/SPX),and dbmssocn(TCP/IP).The corresponding network DLL must be installed on the system to which you connect.If you do not specify anetwork and you use alocal server(for example,"."or"(local)"),shared memory is used.Packet Size 8192 Size in bytes of the network packets used to communicate with an instance of SQL Server.Password-or-Pwd The password for the SQL Server account logging on.Persist Security Info'false'When set to'false',security-sensitive inf ormation,such as the password,is not returned as part of the connection if the connection is open or has ever been in an open state.Resetting the connection string resets all connection string values including the password.Pooling'true'When true,the SQLConnection object is drawn from the appropriate pool,or if necessary,is created and added to the appropriate pool.User ID The SQL Server login account.Workstation ID the local computer name The name of the workstation connecting to SQL Server.Note Use；to separate each property.If aname occurs more than once,the value from the last one in the connection string will be used.If you are building your connection string in your app using values from user input fields,make sure the user can't change the connection string by inserting an additional property with another value within the user value.SQL Server 2005 SQL Native Client ODBC Driver Standard security："Driver={SQL Native Client}；Server=Aron1；Database=pubs；UID=sa；PWD=asdasd；"Trusted connection："Driver={SQL Native Client}；Server=Aron1；Database=pubs；Trusted_Connection=yes；"Equivalents Integrated Security=SSPI equalsTrusted_Connection=yes Prompt for username and password：oConn.Properties("Prompt")=adPromptAlways oConn.Open"Driver={SQL Native Client}；Server=Aron1；DataBase=pubs；"Enabling MARS(multiple active result sets)："Driver={SQL Native Client}；Server=Aron1；Database=pubs；Trusted_Connection=yes；MARS_Connection=yes"Equivalents MultipleActiveResultSets=true equalsMARS_Connection=yes Using MARS with SQL Native Client,by Chris Lee Encrypt data sent over network："Driver={SQL Native Client}；Server=Aron1；Database=pubs；Trusted_Connection=yes；Encrypt=yes"Attach adatabase file on connect to alocal SQL Server Express instance："Driver={SQL Native Client}；Server=.\SQLExpress；AttachDbFilename=c：\asd\qwe\mydbfile.mdf；Database=dbname；Trusted_Connection=Yes；"-or-"Driver={SQL Native Client}；Server=.\SQLExpress；AttachDbFilename=|DataDirectory|mydbfile.mdf；Database=dbname；Trusted_Connection=Yes；"(use|DataDirectory|when your database file resides in the data directory)Why is the"Database"parameter needed?Answer：If the database was previously attached,SQL Server does not reattach it(it uses the attached database as the default for the connection).Download the SQL Native Client here(the package contains booth the ODBC driver and the OLE DB provider)Using SQL Server 2005 Express?Don't miss the server name syntax：SERVERNAME\SQLEXPRESS(Substitute"SERVERNAME"with the name of the computer)SQL Native Client OLE DB Provider Standard security："Provider=SQLNCLI；Server=Aron1；Database=pubs；UID=sa；PWD=asdasd；"Trusted connection："Provider=SQLNCLI；Server=Aron1；Database=pubs；Trusted_Connection=yes；"Equivalents Integrated Security=SSPI equalsTrusted_Connection=yes Prompt for username and password：oConn.Properties("Prompt")=adPromptAlwaysoConn.Open"Provider=SQLNCLI；Server=Aron1；DataBase=pubs；"Enabling MARS(multiple active result sets)："Provider=SQLNCLI；Server=Aron1；Database=pubs；Trusted_Connection=yes；MarsConn=yes"Equivalents MarsConn=yes equals MultipleActiveResultSets=true equals MARS_Connection=yes Using MARS with SQL Native Client,by Chris Lee Encrypt data sent over network："Provider=SQLNCLI；Server=Aron1；Database=pubs；Trusted_Connection=yes；Encrypt=yes"Attach adatabase file on connect to alocal SQL Server Express instance："Provider=SQLNCLI；Server=.\SQLExpress；AttachDbFilename=c：\asd\qwe\mydbfile.mdf；Database=dbname；Trusted_Connection=Yes；"-or-"Provider=SQLNCLI；Server=.\SQLExpress；AttachDbFilename=|DataDirectory|mydbfile.mdf；Database=dbname；Trusted_Connection=Yes；"(use|DataDirectory|when your database file resides in the data directory)Why is the"Database"parameter needed?Answer：If the database was previously attached,SQL Server does not reattach it(it uses the attached database as the default for the connection).Download the SQL Native Client here(the package contains booth the ODBC driver and the OLE DB provider)Using SQL Server 2005 Express?Don't miss the server name syntax：SERVERNAME\SQLEXPRESS(Substitute"SERVERNAME"with the name of the computer)SqlConnection(.NET)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"(both connection strings produces the same result)Trusted Connection："Data Source=Aron1；Initial Catalog=pubs；IntegratedSecurity=SSPI；"-or-"Server=Aron1；Database=pubs；Trusted_Connection=True；"(both connection strings produces the same result)(use serverName\instanceName as Data Source to use an specifik SQLServer instance)Connect via an IP address："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,at the end of the Data Source is the port to use(1433 is the default))Enabling MARS(multiple active result sets)："Server=Aron1；Database=pubs；Trusted_Connection=True；MultipleActiveResultSets=true"Note！Use 2.0 for MARS functionality.MARS is not supported in 1.0 nor 1.1 Streamline your Data Connections by Moving to MARS,by Laurence Moroney, Attach adatabase file on connect to alocal SQL Server Express instance："Server=.\SQLExpress；AttachDbFilename=c：\asd\qwe\mydbfile.mdf；Database=dbname；Database=dbname；Trusted_Connection=Yes；"-or-"Server=.\SQLExpress；AttachDbFilename=|DataDirectory|mydbfile.mdf；Database=dbname；Trusted_Connection=Yes；"(use|DataDirectory|when your database file resides in the data directory)Why is the"Database"parameter needed?Answer：If the database was previously attached,SQL Server does not reattach it(it uses the attached database as the default for the connection).Using"User Instance"on alocal SQL Server Express instance："Data Source=.\SQLExpress；integrated security=true；attachdbfilename=|DataDirectory|\mydb.mdf；user instance=true；"The"User Instance"functionality creates anew SQL Server instance on the fly during connect.This works only on alocal SQL Server 2005 instance and only when connecting using windows authentication over local named pipes.The purpose is to be able to create afull rights SQL Server instance to auser with limited administrative rights on the computer.To enable the functionality：sp_configure'user instances enabled','1'(0 to disable)Using SQL Server 2005 Express?Don't miss the server name syntax：SERVERNAME\SQLEXPRESS(Substitute"SERVERNAME"with the name of the computer)Context Connection-connecting to"self"from within your CLR stored prodedure/function C#：using(SqlConnection connection=new SqlConnection("context connection=true")){connection.Open()；//Use the connection}Visual Basic：Using connection as new SqlConnection("context connection=true")connection.Open()'Use the connection End Using The context connection lets you execute Transact-SQL statements in the same context(connection)that your code was invoked in the first place.Read more When to use SQL Native Client?.Net applications Do not use the SQL Native e Framework Data Provider for SQL Server(SqlConnection).COM applications,all other applications Use the SQL Native Client if you are accessing an SQL Server 2005 and need the new features of SQL Server 2005 such as MARS,encryption,xml data type etc.Continue use your current provider(OLE DB/ODBC through the MDAC package)if you are not connecting to an SQL Server 2005(that's quite obvious eh.)or if you are connecting to an SQL Server 2005 but arenot using any of the new SQL Server 2005 features.For more details on the differences between MDAC and SQL Native Client,read this msdn article Access ODBC Standard Security："Driver={Microsoft Access Driver(*.mdb)}；Dbq=C：\mydatabase.mdb；Uid=Admin；Pwd=；"Workgroup："Driver={Microsoft Access Driver(*.mdb)}；Dbq=C：\mydatabase.mdb；SystemDB=C：\mydatabase.mdw；"Exclusive："Driver={Microsoft Access Driver(*.mdb)}；Dbq=C：\mydatabase.mdb；Exclusive=1；Uid=admin；Pwd="OLE DB,OleDbConnection(.NET)Standard security："Provider=Microsoft.Jet.OLEDB.4.0；Data Source=\somepath\mydb.mdb；User Id=admin；Password=；"Workgroup(system database)："Provider=Microsoft.Jet.OLEDB.4.0；Data Source=\somepath\mydb.mdb；Jet OLEDB：System Database=system.mdw；"With password："Provider=Microsoft.Jet.OLEDB.4.0；Data Source=\somepath\mydb.mdb；Jet OLEDB：Database Password=MyDbPassword；"Oracle ODBC New version："Driver={Microsoft ODBC for Oracle}；Server=OracleServer.world；Uid=Username；Pwd=asdasd；"Old version："Driver={Microsoft ODBC Driver for Oracle}；ConnectString=OracleServer.world；Uid=myUsername；Pwd=myPassword；"OLE DB,OleDbConnection(.NET)Standard security："Provider=msdaora；Data Source=MyOracleDB；User Id=UserName；Password=asdasd；"This one's from Microsoft,the following are from Oracle Standard Security："Provider=OraOLEDB.Oracle；Data Source=MyOracleDB；UserId=Username；Password=asdasd；"Trusted Connection："Provider=OraOLEDB.Oracle；Data Source=MyOracleDB；OSAuthent=1；"OracleConnection(.NET)Standard："Data Source=MyOracleDB；Integrated Security=yes；"This one works only with Oracle 8i release 3or later Specifying username and password："Data Source=MyOracleDB；User Id=username；Password=passwd；Integrated Security=no；"This one works only with Oracle 8i release 3or later Declare the OracleConnection：C#：using System.Data.OracleClient；OracleConnection oOracleConn=new OracleConnection()；oOracleConn.ConnectionString="my connection string"；oOracleConn.Open()；：Imports System.Data.OracleClient Dim oOracleConn As OracleConnection=New OracleConnection()oOracleConn.ConnectionString="my connection string"oOracleConn.Open()Missing the System.Data.OracleClient namespace? Managed Provider for Oracle Great article！"Features of Oracle Data Provider "by Rama Mohan G.at C#Corner Core Labs OraDirect(.NET)"User ID=scott；Password=tiger；Host=ora；Pooling=true；Min Pool Size=0；Max Pool Size=100；Connection Lifetime=0"Read more at Core Lab and the product page.Data Shape MS Data Shape："Provider=MSDataShape.1；Persist Security Info=False；Data Provider=MSDAORA；Data Source=orac；user id=username；password=mypw"Want to learn data shaping?Check out 4GuyfFromRolla's greatarticle about Data Shaping MySQL MyODBC MyODBC 2.50 Local database："Driver={mySQL}；Server=localhost；Option=16834；Database=mydatabase；"MyODBC 2.50 Remote database："Driver={mySQL}；Server=；Port=3306；Option=131072；Stmt=；Database=my-database；Uid=username；Pwd=password；"MyODBC 3.51 Local database："DRIVER={MySQL ODBC 3.51 Driver}；SERVER=localhost；DATABASE=myDatabase；USER=myUsername；PASSWORD=myPassword；OPTION=3；"MyODBC 3.51 Remote database："DRIVER={MySQL ODBC 3.51 Driver}；SERVER=；PORT=3306；DATABASE=myDatabase；USER=myUsername；PASSWORD=myPassword；OPTION=3；"OLE DB,OleDbConnection(.NET)"Provider=MySQLProv；Data Source=mydb；User Id=UserName；Password=asdasd；"Connector/Net 1.0(.NET)Standard："Server=Server；Database=Test；Uid=UserName；Pwd=asdasd；"Download the driver at MySQL Developer Zone.Specifying port："Server=Server；Port=1234；Database=Test；Uid=UserName；Pwd=asdasd；"Default port is 3306.Enter value-1 to use anamed pipe connection.Declare the MySqlClient connection：C#：using MySql.Data.MySqlClient；MySqlConnection oMySqlConn=new MySqlConnection()；oMySqlConn.ConnectionString="Server=Server；Database=Test；Uid=UserName；Pwd=asdasd；"；oMySqlConn.Open()；：Imports MySql.Data.MySqlClient Dim oMySqlConn As MySqlConnection=New MySqlConnection()oMySqlConn.ConnectionString="Server=Server；Database=Test；Uid=UserName；Pwd=asdasd；"oMySqlConn.Open()MySqlConnection(.NET)eInfoDesigns.dbProvider："Data Source=server；Database=mydb；User ID=username；Password=pwd；Command Logging=false"This one is used with eInfoDesigns dbProvider,an add-on Declare the MySqlConnection：C#：using eInfoDesigns.dbProvider.MySqlClient；MySqlConnection oMySqlConn=new MySqlConnection()；oMySqlConn.ConnectionString="my connection string"；oMySqlConn.Open()；：Imports eInfoDesigns.dbProvider.MySqlClient Dim oMySqlConn As MySqlConnection=New MySqlConnection()oMySqlConn.ConnectionString="my connection string"oMySqlConn.Open()SevenObjects MySqlClient(.NET)Standard："Host=server；UserName=myusername；Password=mypassword；Database=mydb；"This is afreeware data provider from SevenObjects Core Labs MySQLDirect(.NET)Standard："User ID=root；Password=pwd；Host=localhost；Port=3306；Database=test；Direct=true；Protocol=TCP；Compress=false；Pooling=true；Min Pool Size=0；Max Pool Size=100；ConnectionLifetime=0"Read more at Core Lab and the product page.Interbase ODBC,Easysoft Local computer："Driver={Easysoft IB6 ODBC}；Server=localhost；Database=localhost：C：\mydatabase.gdb；Uid=username；Pwd=password"Remote Computer："Driver={Easysoft IB6 ODBC}；Server=ComputerName；Database=ComputerName：C：\mydatabase.gdb；Uid=username；Pwd=password"Read more about this driver：Easysoft ODBC-Interbase driver ODBC,Intersolv Local computer："Driver={INTERSOLV InterBase ODBC Driver(*.gdb)}；Server=localhost；Database=localhost：C：\mydatabase.gdb；Uid=username；Pwd=password"Remote Computer："Driver={INTERSOLV InterBase ODBC Driver(*.gdb)}；Server=ComputerName；Database=ComputerName：C：\mydatabase.gdb；Uid=username；Pwd=password"This driver are provided by DataDirect Technologies(formerly Intersolv)OLE DB,SIBPROvider Standard："provider=sibprovider；location=localhost：；data source=c：\databases\gdbs\mygdb.gdb；user id=SYSDBA；password=masterkey"Specifying character set："provider=sibprovider；location=localhost：；data source=c：\databases\gdbs\mygdb.gdb；user id=SYSDBA；password=masterkey；character set=ISO8859_1"Specifying role："provider=sibprovider；location=localhost：；data source=c：\databases\gdbs\mygdb.gdb；user id=SYSDBA；password=masterkey；role=DIGITADORES"Read more about SIBPROvider Read more about connecting to Interbase in this Borland Developer Network article IBM DB2 OLE DB,OleDbConnection(.NET)from ms 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"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"IBM's OLE DB Provider(shipped with IBM DB2 UDB v7 or above)TCP/IP：Provider=IBMDADB2；Database=sample；HOSTNAME=db2host；PROTOCOL=TCPIP；PORT=50000；uid=myUserName；pwd=myPwd；ODBC Standard："driver={IBM DB2 ODBC DRIVER}；Database=myDbName；hostname=myServerName；port=myPortNum；protocol=TCPIP；uid=myUserName；pwd=myPwd"Sybase ODBC Standard Sybase System 12(or 12.5)Enterprise Open Client："Driver={SYBASE ASE ODBC Driver}；Srvr=Aron1；Uid=username；Pwd=password"Standard Sybase System 11："Driver={SYBASE SYSTEM 11}；Srvr=Aron1；Uid=username；Pwd=password；Database=mydb"For more information check out the Adaptive Server Enterprise Document Sets Intersolv 3.10："Driver={INTERSOLV 3.10 32-BIT Sybase}；Srvr=Aron1；Uid=username；Pwd=password；"Sybase SQL Anywhere(former Watcom SQL ODBC driver)："ODBC；Driver=Sybase SQL Anywhere 5.0；DefaultDir=c：\dbfolder\；Dbf=c：\mydatabase.db；Uid=username；Pwd=password；Dsn="""""Note！The two double quota following the DSN parameter at the end are escaped quotas(VB syntax),you may have to change this to your language specific escape syntax.The empty DSN parameter is indeed critical as not including it will result in error 7778.Read more in the Sybase SQL Anywhere User Guide(see part3,chapter 13)OLE DB Adaptive Server Anywhere(ASA)："Provider=ASAProv；Data source=myASA"Read more in the ASA User Guide(part 1,chapter 2)Adaptive Server Enterprise(ASE)with Data Source.IDS file："Provider=Sybase ASE OLE DB Provider；Data source=myASE"Note that you must create aData Source.IDS file using the Sybase Data Administrator.These.IDS files resemble ODBC DSNs.Adaptive Server Enterprise(ASE)："Provider=Sybase.ASEOLEDBProvider；Srvr=myASEserver,5000；Catalog=myDBname；User Id=username；Password=password"-some reports on problem using the above one,try the following as an alternative-"Provider=Sybase.ASEOLEDBProvider；Server Name=myASEserver,5000；Initial Catalog=myDBname；User Id=username；Password=password"This one works only from Open Client 12.5 where the server port number feature works,?allowing fully qualified connection strings to be used without defining?any.IDS Data Source files.AseConnection(.NET)Standard："Data Source='myASEserver'；Port=5000；Database='myDBname'；UID='username'；PWD='password'；"Declare the AseConnection：C#：using Sybase.Data.AseClient；AseConnection oCon=new AseConnection()；oCon.ConnectionString="my connection string"；oCon.Open()；：Imports System.Data.AseClient Dim oCon As AseConnection=New AseConnection()oCon.ConnectionString="my connection string"oCon.Open()Read more！Adaptive Server Enterprise Data Provider Documentation Informix ODBC Informix 3.30："Dsn=''；Driver={INFORMIX 3.30 32 BIT}；Host=hostname；Server=myserver；Service=service-name；Protocol=olsoctcp；Database=mydb；UID=username；PWD=myPwd Informix-CLI 2.5："Driver={Informix-CLI 2.5(32 Bit)}；Server=myserver；Database=mydb；Uid=username；Pwd=myPwd"OLE DB IBM Informix OLE DB Provider："Provider=Ifxoledbc.2；password=myPw；User ID=myUser；Data[email=Source=dbName@serverName；Persist]Source=dbName@serverName；Persist[/email]Security Info=true"Ingres ODBC DSN-less"Provider=MSDASQL.1；DRIVER=Ingres；SRVR=xxxxx；DB=xxxxx；Persist Security Info=False；uid=xxxx；pwd=xxxxx；SELECTLOOPS=N；Extended Properties="""SERVER=xxxxx；DATABASE=xxxxx；SERVERTYPE=INGRES""Mimer SQL ODBC Standard Security："Driver={MIMER}；Database=mydb；Uid=myuser；Pwd=mypw；"Prompt for username and password："Driver={MIMER}；Database=mydb；"Lightbase Standard Standard："user=USERLOGIN；password=PASSWORD；UDB=USERBASE；server=SERVERNAME"PostgreSQL Core Labs PostgreSQLDirect(.NET)Standard："User ID=root；Password=pwd；Host=localhost；Port=5432；Database=testdb；Pooling=true；Min Pool Size=0；Max Pool Size=100；Connection Lifetime=0"Read more at Core Lab and the product page.PostgreSQL driver Standard："DRIVER={PostgreSQL}；SERVER=ipaddress；port=5432；DATABASE=dbname；UID=username；PWD=password；"Npgsql by pgFoundry(.NET)SSL activated："Server=127.0.0.1；Port=5432；Userid=myuserid；password=mypw；Protocol=3；SSL=true；Pooling=true；MinPoolSize=3；MaxPoolSize=20；Encoding=UNICODE；Timeout=20；SslMode=Require"Without SSL："Server=127.0.0.1；Port=5432；Userid=myuserid；password=mypw；Protocol=3；SSL=false；Pooling=true；MinPoolSize=1；MaxPoolSize=20；Encoding=UNICODE；Timeout=15；SslMode=Disable"Read more in the Npgsql：User's Manual and on the pgFoundry website.Paradox ODBC 5.X：Driver={Microsoft Paradox Driver(*.db)}；DriverID=538；Fil=Paradox 5.X；DefaultDir=c：\pathToDb\；Dbq=c：\pathToDb\；CollatingSequence=ASCII"7.X："Provider=MSDASQL.1；Persist Security Info=False；Mode=Read；Extended Properties='DSN=Paradox；DBQ=C：\myDb；DefaultDir=C：\myDb；DriverId=538；FIL=Paradox 7.X；MaxBufferSize=2048；PageTimeout=600；'；Initial Catalog=C：\myDb"OleDbConnection(.NET)Standard"Provider=Microsoft.Jet.OLEDB.4.0；Data Source=c：\myDb；Extended Properties=Paradox 5.x；"MS kb-article：How to use Paradox data with Access and Jet DSN ODBC DSN："DSN=myDsn；Uid=username；Pwd=；"File DSN："FILEDSN=c：\myData.dsn；Uid=username；Pwd=；"Firebird ODBC-IBPhoenix Open Source Standard："DRIVER=Firebird/InterBase(r)driver；UID=SYSDBA；PWD=masterkey；DBNAME=D：\FIREBIRD\examples\TEST.FDB"IBPhoenix ODBC；More info,download etc Data Provider Standard："User=SYSDBA；Password=masterkey；Database=SampleDatabase.fdb；DataSource=localhost；Port=3050；Dialect=3；Charset=NONE；Role=；Connection lifetime=15；Pooling=true；MinPoolSize=0；MaxPoolSize=50；Packet Size=8192；ServerType=0"。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
C# 中ODBC和OleDb访问数据库的连接字符串
因为工作需要自学数据库的访问，查看相关书籍时，都是以OleDb类进行.Net开发为例，虽然也提到ODBC和OleDb在原理上是一样，但是没有实际的例子，而我恰恰需要通过ODBC来访问数据库，这就碰到连接字符串问题，下面介绍一种简单的查找连接字符串的方法，前提是你用的是VisualStudio开发工具。
步骤一：先在控制面板的数据源ODBC中设定好系统DSN，方法如下；
打开“控制面板”—>“管理工具”—>“数据源（ODBC）”，打开“ODBC 数据源管理器”，设置系统DSN或用户DSN。
单击“添加”按钮进入创建新数据源，选择数据源的驱动程序，因为我要演示的是通过ODBC访问本地Access数据库，所以此处选择“Microsoft for Access Driver(*mdb,*accdb)”,其中mdb文件的Access是Access2003数据库文件的后缀，accdb文件Access2007和以上版本数据库文件的后缀，accdb文件转换为mdb需要在Office2007中打开accdb文件然后另存为Access2003数据库，后缀名自动变为mdb，这两种格式的文件数据库连接组件不一样。
当然你也可以通过ODBC访问SQL Serve等其他数据库，虽然使用ODBC能以统一的方式处理所有的数据库，但是使用ODBC来进行.NET开发的时候，其速度要比OleDB、SQL Server慢很多。
选择数据源的驱动程序后自动跳入下面界面，“数据源名”自己设定，此处命名为DataBase，在“数据库”中点击“选择”，选择需要通过ODBC连接的数据库，选择好后在“数据库：”右侧会显示数据库的路径如图。
单击“确定”后返回“ODBC数据源管理器”，此时在系统数据源中已经添加了一个名称为“DataBase”的“Microsoft for Access Driver(*mdb,*accdb)”的驱动程序。
点击“确定”关闭该界面。
步骤二：打开VisualStudio 软件，创建一个Windows应用程序。
单击菜单上的“工具”—“连接到数据库”，打开“添加连接”窗口。
点击“更改”可以更改数据源，即选择你需要访问的数据库类型，如果不确定可以看右边的“说明”，此处选择“Microsoft ODBC数据源”。
单击“确定”返回之前的“添加连接”窗口。
在“指定数据源”下面选择“使用用户名或系统数据源名称”，单击下拉框，可以看到步骤一设置的“DataBase”数据源。
如果登录数据库需要用户名和密码，请在“登录信息”中填入用户名和密码。
此时你可以测试连接是否成功，单击左下角的“测试连接”，如果连接成功，单击“高级”，即可查看你的“连接字符串”如下图；如果连接不成功，则可能是你的数据源或者数据库文件名有问题，此处测试连接不成功，你写的程序中也无法打开数据库连接的，所以此处必须测试连接成功。
其实当你连接成功后在“服务器资源管理器”的“属性”窗口中也可以看到连接字符串，此处就不详细介绍了。
此方法对于OleDB访问数据源同样适用。
通过OleDB访问数据源则不需要步骤一。
下图是通过OleDB访问本地E盘data文件夹下名称为“DataBase.accdb”的Access数据库的连接字符串。