ADO.Net数据库连接字符串、DbProviderFactory

一、ADO.Net数据库连接字符串

1、OdbcConnection（System.Data.Odbc）

(1)SQL Sever

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

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

(2)SQL Native Client ODBC Driver（>=SQL Server 2005）

标准安全
" Driver={SQL Native Client}; Server=Aron1; Database=pubs; UID=sa; PWD=asdasd; "
信任的连接
" Driver={SQL Native Client}; Server=Aron1; Database=pubs; Trusted_Connection=yes; " --Integrated Security=SSPI 等同于Trusted_Connection=yes

(3)Oracle:

新版本:
"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; "

(4)Access:

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

2、OleDbConnection(System.Data.OleDb)

(1)SQL Sever

标准安全:
" Provider=sqloledb; Data Source=Aron1; Initial Catalog=pubs; User Id=sa; Password=asdasd; "

信任的连接:
" 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)

(2)SQL Native Client OLE DB Provider（>=SQL Server 2005）

标准安全：
" Provider=SQLNCLI; Server=Aron1; Database=pubs; UID=sa; PWD=asdasd; "
信任的连接：
" Provider=SQLNCLI; Server=Aron1; Database=pubs; Trusted_Connection=yes; " --Integrated Security=SSPI 等同于Trusted_Connection=yes

(3)Oracle:

标准安全:
"Provider=msdaora; Data Source=MyOracleDB; User Id=UserName; Password=asdasd; "
This one's from Microsoft, the following are from Oracle
标准安全:
"Provider=OraOLEDB.Oracle; Data Source=MyOracleDB; User Id=Username; Password=asdasd; "
信任的连接:
"Provider=OraOLEDB.Oracle; Data Source=MyOracleDB; OSAuthent=1; "

(4)Access:

标准安全:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\somepath\mydb.mdb; User Id=admin; Password=; "

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

标准安全:

" Data Source=Aron1; Initial Catalog=pubs; User Id=sa; Password=asdasd; "
- 或者 -
" Server=Aron1; Database=pubs; User ID=sa; Password=asdasd; Trusted_Connection=False"
信任的连接:
" Data Source=Aron1; Initial Catalog=pubs; Integrated Security=SSPI; "
- 或者 -
" Server=Aron1; Database=pubs; Trusted_Connection=True; " –(use serverName\instanceName as Data Source to use an specifik SQLServer instance, 仅仅适用于SQLServer2000)

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

标准安全：
"Data Source=MyOracleDB; Integrated Security=yes; " --This one works only with Oracle 8i release 3 or later
指定用户名和密码：
"Data Source=MyOracleDB; User Id=username; Password=passwd; Integrated Security=no; " --This one works only with Oracle 8i release 3 or later
指定主机：
"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)

(HOST=192.168.115.33) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME= testDemo)));

User Id=oracle_test; Password=oracle"
其中Oracle数据库服务器IP：192.168.115.33

ServiceName：testDemo

用户名：oracle_test

密码：oracle

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

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

1、配置文件ConnectionString节：

<configuration>

    <connectionStrings>

        <add name="default"  connectionString="server=localhost; user id=sa; password=******; database=northwind"

             providerName="System.Data.SqlClient"/>

    </connectionStrings>

</configuration>

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

ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["default"];

DbProviderFactory provider = DbProviderFactories.GetFactory(settings.ProviderName);

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

using (DbConnection conn = provider.CreateConnection())

{

    conn.ConnectionString = settings.ConnectionString;

    conn.Open();

    DbCommand cmd = conn.CreateCommand();

    cmd.CommandText = "Select top 10 * From ShortTermBill";

    //使用DbDataAdapter

    DbDataAdapter da = provider.CreateDataAdapter();

    da.SelectCommand = cmd;

    DataSet ds = new DataSet();

    da.Fill(ds);

    da.Dispose();

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

    //使用DbDataReader

    DbDataReader reader = cmd.ExecuteReader()

    while (reader.Read())

    {

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

    }

    conn.Close();

}

巴特西

ADO.Net数据库连接字符串、DbProviderFactory

一、ADO.Net数据库连接字符串

1、OdbcConnection（System.Data.Odbc）

(1)SQL Sever

(2)SQL Native Client ODBC Driver（>=SQL Server 2005）

(3)Oracle:

(4)Access:

2、OleDbConnection(System.Data.OleDb)

(1)SQL Sever

(2)SQL Native Client OLE DB Provider（>=SQL Server 2005）

(3)Oracle:

(4)Access:

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

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

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

最新文章

热门文章