C#使用ADO.Net连接数据库与DbProviderFactory实现多数据库访问

第C#使用ADO.Net连接数据库与DbProviderFactory实现多数据库访问(1)SQLSever

标准安全:Driver={SQLServer};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;

信任的连接:Driver={SQLServer};Server=Aron1;Database=pubs;Trusted_Connection=yes;

(2)SQLNativeClientODBCDriver（=SQLServer2005）

标准安全Driver={SQLNativeClient};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;

信任的连接

Driver={SQLNativeClient};Server=Aron1;Database=pubs;Trusted_Connection=yes;--IntegratedSecurity=SSPI等同于Trusted_Connection=yes

(3)Oracle:

新版本:Driver={MicrosoftODBCforOracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;

旧版本:Driver={MicrosoftODBCDriverforOracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;

(4)Access:

标准安全:Driver={MicrosoftAccessDriver(*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;

2、OleDbConnection(System.Data.OleDb)

(1)SQLSever

标准安全:Provider=sqloledb;DataSource=Aron1;InitialCatalog=pubs;UserId=sa;Password=asdasd;

信任的连接:

Provider=sqloledb;DataSource=Aron1;InitialCatalog=pubs;IntegratedSecurity=SSPI;

(useserverName\instanceNameasDataSourcetouseanspecifikSQLServerinstance,onlySQLServer2000)

(2)SQLNativeClientOLEDBProvider（=SQLServer2005）

标准安全：Provider=SQLNCLI;Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;

信任的连接：

Provider=SQLNCLI;Server=Aron1;Database=pubs;Trusted_Connection=yes;--IntegratedSecurity=SSPI等同于Trusted_Connection=yes

(3)Oracle:

标准安全:

Provider=msdaora;DataSource=MyOracleDB;UserId=UserName;Password=asdasd;

ThisonesfromMicrosoft,thefollowingarefromOracle

标准安全:Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;UserId=Username;Password=asdasd;

信任的连接:Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;OSAuthent=1;

(4)Access:

标准安全:

Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\somepath\mydb.mdb;UserId=admin;Password=;

3、SqlConnection(Syste.Data.SqlClient)SQL专用

标准安全:

DataSource=Aron1;InitialCatalog=pubs;UserId=sa;Password=asdasd;

-或者-Server=Aron1;Database=pubs;UserID=sa;Password=asdasd;Trusted_Connection=False

信任的连接:

DataSource=Aron1;InitialCatalog=pubs;IntegratedSecurity=SSPI;

-或者-

Server=Aron1;Database=pubs;Trusted_Connection=True;(useserverName\instanceNameasDataSourcetouseanspecifikSQLServerinstance,仅仅适用于SQLServer2000)

4、OracleConnection(System.Data.OracleClient\Oracle.ManagedDataAccess.Client)Oracle专用

标准安全：

DataSource=MyOracleDB;IntegratedSecurity=yes;--ThisoneworksonlywithOracle8irelease3orlater

指定用户名和密码：

DataSource=MyOracleDB;UserId=username;Password=passwd;IntegratedSecurity=no;--ThisoneworksonlywithOracle8irelease3orlater

指定主机：

DataSource=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=3)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=testDemo)));UserId=oracle_test;Password=oracle

其中Oracle数据库服务器IP：3

ServiceName：testDemo

用户名：oracle_test

密码：oracle

二、利用DbProviderFactory创建各种ADO.Net对象

DbProviderFactory是一个工厂类，工厂类的作用提供其他一系列相互之间有关系的类。在这里，DbProviderFactory就自动生成了包括DbConnection、DbCommand、DbDataAdapter等一系列数据库操作的相关类。

1、配置文件ConnectionString节：

configuration

connectionStrings

addname="default"connectionString="server=localhost;userid=sa;password=******;database=northwind"

providerName="System.Data.SqlClient"/

/connectionStrings

/configuration

2、利用DbProviderFactory类自动查找数据库的驱动

ConnectionStringSettingssettings=ConfigurationManager.ConnectionStrings["default"];

DbProviderFactoryprovider=DbProviderFactories.GetFactory(settings.ProviderName);

3、利用DbProviderFactory类实例创建各种ADO.Net对象。

using(DbConnectionconn=provider.CreateConnection())

conn.ConnectionString=settings.ConnectionString;

conn.Open();

DbCommandcmd=conn.CreateCommand();

cmd.CommandText="Selecttop10*FromShortTermBill";

//使用DbDataAdapter

DbDataAdapterda=provider.CreateDataAdapter();

da.SelectCommand=cmd;

DataSetds=newDataSet();

da.Fill(ds);

da.Dispose();

Console.WriteLine(ds.Tables[0].Rows[0]["BillCode"]);

//使用DbDataReader

DbDataReaderreader=cmd.ExecuteReader()

while(reader.Read())

Console.WriteLine(reader.GetString(0));

conn.Close();

}

三、利用DbConnection获取数据库架构信息

SQLServer架构集合-ADO.NET|Microsoft官方文档

classProgram

staticvoidMain()

stringconnectionString=GetConnectionString();

using(SqlConnectionconnection=newSqlConnection(connectionString))

//Connecttothedatabasethenretrievetheschemainformation.

connection.Open();string[]columnRestrictions=newString[4];

//Forthearray,0-memberrepresentsCatalog;1-memberrepresentsSchema;

//2-memberrepresentsTableName;3-memberrepresentsColumnName.

//NowwespecifytheTable_NameandColumn_Nameofthecolumnswhatwewanttogetschemainformation.

columnRestrictions[2]="Device";

DataTabledepartmentIDSchemaTable=connection.GetSchema("Columns",columnRestrictions);

ShowColumns(departmentIDSchemaTable);

privatestaticstringGetConnectionString()

//Toavoidstoringtheconnectionstringinyourcode,

//youcanretrieveitfromaconfigurationfile.

return"server=;Database=TPM;user=it;pwd=;ApplicationIntent=ReadOnly;MultiSubnetFailover=True";

privatestaticvoidShowColumns(DataTablecolumnsTable)

varselectedRows=frominfoincolumnsTable.AsEnumerable()

selectnew

TableCatalog=info["TABLE_CATALOG"],

TableSchema=info["TABLE_SCHEMA"],

TableName=info["TABLE_NAME"],

ColumnName=info["COLUMN_NAME"],

DataType=info["DATA_TYPE"],

ORDINAL_POSITION=info["ORDINAL_POSITION"],

COLUMN_DEFAULT=info["COLUMN_DEFAULT"],

IS_NULLABLE=info["IS_NULLABLE"],

CHARACTER_MAXIMUM_LENGTH=info["CHARACTER_MAXIMUM_LENGTH"],

NUMERIC_PRECISION=info["NUMERIC_PRECISION"],

NUMERIC_SCALE=info["NUMERIC_SCALE"],

DATETIME_PRECISION=info["DATETIME_PRECISION"],

Console.WriteLine("{0,-15},{1,-15},{2,-15},{3,-15},{