c# odbc 连接mysql 执行sql语句,[转载]转)C#中连接各种数据库方法 odbc oledb sqlserver(...

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;"

OLEDB, 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 serverNameinstanceName 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;"

Trusted Connection:

"Data Source=Aron1;Initial Catalog=pubs;Integrated

Security=SSPI;"

(use serverNameinstanceName 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 connectionstring";

oSQLConn.Open();

VB.NET:

Imports System.Data.SqlClient

Dim oSQLConn As SqlConnection = New SqlConnection()

oSQLConn.ConnectionString="my connectionstring"

oSQLConn.Open()

Access

ODBC

Standard Security:

"Driver={Microsoft Access Driver

(*.mdb)};Dbq=somepathmydb.mdb;Uid=Admin;Pwd=asdasd;"

Workgroup:

"Driver={Microsoft Access Driver

(*.mdb)};Dbq=somepathmydb.mdb;SystemDB=somepathmydb.mdw;","admin",

""

OLEDB, OleDbConnection (.NET)

Standard security:

"Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=somepathmydb.mdb;User Id=admin;Password=asdasd;"

Workgroup (system database):

"Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=somepathmydb.mdb;Jet OLEDB:System

Database=system.mdw;","admin", ""

With password:

"Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=somepathmydb.mdb;Jet OLEDB:Database

Password=MyDbPassword;","admin", ""

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;"

OLEDB, 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;User

Id=Username;Password=asdasd;"

Trusted Connection:

"Provider=OraOLEDB.Oracle;Data

Source=MyOracleDB;OSAuthent=1;"

OracleConnection (.NET)

Standard:

"Data Source=Oracle8i;Integrated Security=yes";

This one works only with Oracle 8i release 3 or later

Declare the OracleConnection:

C#:

using System.Data.OracleClient;

OracleConnection oOracleConn = new OracleConnection();

oOracleConn.ConnectionString = "my connectionstring";

oOracleConn.Open();

VB.NET:

Imports System.Data.OracleClient

Dim oOracleConn As OracleConnection = New OracleConnection()

oOracleConn.ConnectionString = "my connectionstring"

oOracleConn.Open()

Data Shape

MS Data Shape:

"Provider=MSDataShape.1;Persist Security Info=False;Data

Provider=MSDAORA;Data Source=orac;user

id=username;password=mypw"

MySQL

ODBC

Local database:

"Driver={mySQL};Server=mySrvName;Option=16834;Database=mydatabase;"

Remote database:

"Driver={mySQL};Server=data.domain.com;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;"

OLEDB, OleDbConnection (.NET)

Standard:

"Provider=MySQLProv;Data Source=mydb;User

Id=UserName;Password=asdasd;"

MySqlConnection (.NET)

EID:

"Data Source=server;Database=mydb;User

ID=username;Password=pwd;Command Logging=false"

This one is used with eInfoDesigns dbProvider, an add-on to

.NET

Declare the MySqlConnection:

C#:

using EID.MySqlClient;

MySqlConnection oMySqlConn = new MySqlConnection();

oMySqlConn.ConnectionString = "my connectionstring";

oMySqlConn.Open();

VB.NET:

Imports EID.MySqlClient

Dim oMySqlConn As MySqlConnection = New MySqlConnection()

oMySqlConn.ConnectionString = "my connectionstring"

oMySqlConn.Open()

IBM DB2

OLEDB, 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"

Sybase

ODBC

Standard:

"Driver={SYBASE SYSTEM

11};Srvr=Aron1;Uid=username;Pwd=password;"

Intersolv 3.10:

"Driver={INTERSOLV 3.10 32-BIT

Sybase};Srvr=Aron1;Uid=username;Pwd=password;"

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"

DSN

ODBC

DSN:

"DSN=myDsn;Uid=username;Pwd=;"

File DSN:

"FILEDSN=c:myData.dsn;Uid=username;Pwd=;"

Excel

ODBC

Standard:

"Driver={Microsoft Excel Driver

(*.xls)};DriverId=790;Dbq=C:MyExcel.xls;DefaultDir=c:mypath;"

OLE DB

Standard:

"Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=C:MyExcel.xls;Extended Properties=Excel 8.0;HDR=Yes;"

"HDR=Yes;" indicates that the first row contains columnnames, not

data

DBF / FoxPro

ODBC

standard:

"Driver={Microsoft dBASE Driver

(*.dbf)};DriverID=277;Dbq=c:mydbpath;"

OLEDB, OleDbConnection (.NET)

standard:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:folder;Extended

Properties=dBASE IV;User ID=Admin;Password="

Pervasive

ODBC

Standard:

"Driver={Pervasive ODBC Client

Interface};ServerName=srvname;dbq=@dbname"

常用连接:

1.使用SqlConnection对象:

public void SqlConnectionOpen()

{

SqlConnection conn= new SqlConnection();

conn.ConnectionString = "user id=sa;password=;initial

catalog=northwind;datasource=localhost;connect Timeout=20";

conn.Open();

}

2.使用OleDbConnection对象:

public void OleDBConnectionOpen()

{

OleDBConnection conn = new OleDbconnection();

conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:Customer.mdb";

conn.Open();

}

(二)其它:

1.ODBC连接Access本地数据库

conGoodDay.Open("Driver={Microsoft Access

Driver(*.mdb)};"+"Dbq=C:a.mdb;"+

"Uid=Admin;"+"Pwd=;");

2.ODBC连接Access系统数据库

conGoodDay.Open("Driver={Microsoft Access

Driver(*.mdb)};"+"Dbq=C:a.mdb;"+

"SystemDB=Admin;"+"Pwd=;");

3.ODBC连接Access系统数据库

conGoodDay.Open("Driver={Microsoft Access

Driver(*.mdb)};"+"Dbq=\serversharea.mdb;");

4.ODBC连接Excel系统数据库

conGoodDay.Open("Driver={Microsoft Access

Driver(*.xls)};"+"DriverId=790;"+

"Dbq=C:a.xls;"+"DefaultDir=c:somepath;");

5.ODBC连接Oracle系统数据库

conGoodDay.Open("Driver={Microsoft ODBC for

oracle};"+"Server=OracleServer.world;"+

"Uid=Admin;"+"Pwd=password;");

6.ODBC连接Sql Servr

conGoodDay.Open("Driver={Sql

Server};"+"Server=myServer;"+"Database=myDatabaseName;"

"Uid=Admin;"+"Pwd=password;");

7.ODBC连接Visual FoxPro

conGoodDay.Open("Driver={Microsoft Visual FoxPro Driver};"+

"SourceType=DBC;"+"SourceDB=c:a.dbc;"+"Exclusive=No;");