.NET中各种数据库连接大全

 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 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;"
  
  
•  Trusted Connection:
  "Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

  (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 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=\somepath\mydb.mdb;Uid=Admin;Pwd=asdasd;"
  
  
•  Workgroup:
  "Driver={Microsoft Access Driver (*.mdb)};Dbq=\somepath\mydb.mdb;SystemDB=\somepath\mydb.mdw;","admin", ""
  
  

 OLEDB, OleDbConnection (.NET)

•  Standard security:
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=asdasd;"
  
  
•  Workgroup (system database):
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;","admin", ""
  
  
•  With password:
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.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"
  
  

 UDL  UDL

•  UDL:
  "File Name=c:\myDataLink.udl;"