C#使用ADO.Net連接數(shù)據(jù)庫與DbProviderFactory實(shí)現(xiàn)多數(shù)據(jù)庫訪問

第C#使用ADO.Net連接數(shù)據(jù)庫與DbProviderFactory實(shí)現(xiàn)多數(shù)據(jù)庫訪問(1)SQLSever

標(biāo)準(zhǔn)安全:Driver={SQLServer};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;

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

(2)SQLNativeClientODBCDriver（=SQLServer2005）

標(biāo)準(zhǔn)安全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:

標(biāo)準(zhǔn)安全:Driver={MicrosoftAccessDriver(*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;

2、OleDbConnection(System.Data.OleDb)

(1)SQLSever

標(biāo)準(zhǔn)安全:Provider=sqloledb;DataSource=Aron1;InitialCatalog=pubs;UserId=sa;Password=asdasd;

信任的連接:

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

(useserverName\instanceNameasDataSourcetouseanspecifikSQLServerinstance,onlySQLServer2000)

(2)SQLNativeClientOLEDBProvider（=SQLServer2005）

標(biāo)準(zhǔn)安全：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:

標(biāo)準(zhǔn)安全:

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

ThisonesfromMicrosoft,thefollowingarefromOracle

標(biāo)準(zhǔn)安全:Provider=OraOLEDB.Oracle;DataSource=MyOracleDB;UserId=Username;Password=asdasd;

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

(4)Access:

標(biāo)準(zhǔn)安全:

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

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

標(biāo)準(zhǔn)安全:

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專用

標(biāo)準(zhǔn)安全：

DataSource=MyOracleDB;IntegratedSecurity=yes;--ThisoneworksonlywithOracle8irelease3orlater

指定用戶名和密碼：

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

指定主機(jī)：

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

其中Oracle數(shù)據(jù)庫服務(wù)器IP：3

ServiceName：testDemo

用戶名：oracle_test

密碼：oracle

二、利用DbProviderFactory創(chuàng)建各種ADO.Net對(duì)象

DbProviderFactory是一個(gè)工廠類，工廠類的作用提供其他一系列相互之間有關(guān)系的類。在這里，DbProviderFactory就自動(dòng)生成了包括DbConnection、DbCommand、DbDataAdapter等一系列數(shù)據(jù)庫操作的相關(guān)類。

1、配置文件ConnectionString節(jié)：

configuration

connectionStrings

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

providerName="System.Data.SqlClient"/

/connectionStrings

/configuration

2、利用DbProviderFactory類自動(dòng)查找數(shù)據(jù)庫的驅(qū)動(dòng)

ConnectionStringSettingssettings=ConfigurationManager.ConnectionStrings["default"];

DbProviderFactoryprovider=DbProviderFactories.GetFactory(settings.ProviderName);

3、利用DbProviderFactory類實(shí)例創(chuàng)建各種ADO.Net對(duì)象。

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獲取數(shù)據(jù)庫架構(gòu)信息

SQLServer架構(gòu)集合-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},{