C#怎么使用ADO.Net連接數據庫與實現多數據庫訪問

本文小編為大家詳細介紹“C#怎么使用ADO.Net連接數據庫與實現多數據庫訪問”，內容詳細，步驟清晰，細節處理妥當，希望這篇“C#怎么使用ADO.Net連接數據庫與實現多數據庫訪問”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學習新知識吧。

一、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();
}

三、利用DbConnection獲取數據庫架構信息

SQL Server 架構集合 - ADO.NET | Microsoft 官方文檔

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Connect to the database then retrieve the schema information.  
            connection.Open();string[] columnRestrictions = new String[4];

            // For the array, 0-member represents Catalog; 1-member represents Schema;
            // 2-member represents Table Name; 3-member represents Column Name.
            // Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
            columnRestrictions[2] = "Device";

            DataTable departmentIDSchemaTable = connection.GetSchema("Columns", columnRestrictions);

            ShowColumns(departmentIDSchemaTable);

        }
    }

    private static string GetConnectionString()
    {
        // To avoid storing the connection string in your code,  
        // you can retrieve it from a configuration file.  
        return "server=10.126.64.1;Database=TPM;user=it;pwd=;ApplicationIntent=ReadOnly;MultiSubnetFailover=True";
    }
    private static void ShowColumns(DataTable columnsTable)
    {
        var selectedRows = from info in columnsTable.AsEnumerable()
                           select new
                           {
                               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},{4,-15},{5,-15},{6,-15},{7,-15},{8,-15},{9,-15},{10,-15},{11,-15}", "TableCatalog", "TABLE_SCHEMA",
            "表名", "列名", "數據類型", "字段原始順序", "列默認值", "是否可空", "字符串最大長度", "數字精度", "數字小數點位數", "日期精度"
            );
        foreach (var row in selectedRows)
        {
            Console.WriteLine("{0,-15},{1,-15},{2,-15},{3,-15},{4,-15},{5,-15},{6,-15},{7,-15},{8,-15},{9,-15},{10,-15},{11,-15}", row.TableCatalog,
                row.TableSchema, row.TableName, row.ColumnName, row.DataType, row.ORDINAL_POSITION, row.COLUMN_DEFAULT, row.IS_NULLABLE
                , row.CHARACTER_MAXIMUM_LENGTH, row.NUMERIC_PRECISION, row.NUMERIC_SCALE, row.DATETIME_PRECISION);
        }
    }
}

讀到這里，這篇“C#怎么使用ADO.Net連接數據庫與實現多數據庫訪問”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。