認識基礎 ADO.NET

發表於 更新於 分類於
AdSense

前言

在學 LINQ 之前，我想先認識一下 ADO.NET，這兩個同樣都是 .NET Framework 很重要的技術。

ADO.NET

ADO.NET，全名 ActiveX Data Objects for the .NET Framework，是 .NET Framework 類別庫 (一堆預先寫好的工具，e.g. 我們很常用到的 System.Console.WriteLine(); 就是預先寫好的。) 的一部分，主要用來存取資料。

開發者可以利用已經預先寫好的 ADO.NET 工具，輕鬆地來連接並存取 「關聯式資料庫 (需要連線)」 與 「非資料庫型資料來源 (例如 XML、Excel 或文字檔案，不用連線) 」。如果要存取 XML，記得加 using System.Xml; 命名空間。

連線型

主要有兩個核心：資料提供者 (Data Provider)、資料集 (DataSet) (不是必須)。

Data Provider

它是對資料庫的主要溝通管道，負責跟資料庫連線、執行命令、讀取資料、和 DataSet 互動。

因為資料庫有太多類型，目前 .NET Framework 只有內建少數幾種：SQL Server、Oracle、EntityClient 等。如果有其他資料庫廠商，他們要依照 ADO.NET interface 介面標準寫出 Data Provider，身為開發者不需要煩惱，只要懂 ADO.NET 即可。

四個核心 Class

Connection(連線)、Command(執行資料庫命令)、DataReader(讀取資料)、DataAdapter(和 DataSet 互動)。

以 SQL Server 為例：SqlConnection、SqlCommand、SqlDataReader、SqlDataAdapter。

參考資料：
.NET Framework Data Providers

DataSet

簡單理解，就是存在記憶體中的資料庫。可以和 XML 互相無痛轉換。一旦資料從資料庫中存進 DataSet，可以暫時中斷連線，增加效能，減少佔用系統資源。如果要使用 DataSet 相關 Class，記得引入 using System.Data; 命名空間。

離線型

不需要與資料庫連線，因此只有資料集 (DataSet)。

實作與 SQL Server 互動

記得引入 using System.Data.SqlClient; 命名空間。這個例子不會提到 SqlDataAdapter。

1. 建立連線

連線字串寫在一起：

1
2
3
4
5
6
using System.Data.SqlClient;

string connStr = "Data Source=Server名稱;Initial Catalog=資料庫名稱;"
    + "User Id=Username;Password=Password;";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();

或是，連線字串寫在 web.config 中，這才是實作上安全的做法：

1
2
3
4
5
6
7
// C#
using System.Configuration;
using System.Data.SqlClient;

string connStr = ConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
1
2
3
4
5
6
7
8
<!-- web.config -->

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
   <add name="myConnStr" connectionString="Data Source=Server名稱;Initial Catalog=資料庫名稱;User Id=Username;Password=Password;" />
  </connectionStrings>
</configuration>

2. 建立 SQL 命令

1
2
3
4
5
6
7
8
9
string cmdText = "SELECT * FROM Students ORDER BY studentId;";
SqlCommand cmd = new SqlCommand(cmdText);
cmd.CommandType = CommandType.Text;

cmd.Connection = conn;
cmd.CommandTimeout = 60; // 60 sec

// 如果 SQL 命令是 SELECT
SqlDataReader dr = cmd.ExecuteReader();
1
2
3
4
5
6
7
8
9
string cmdText = "DELETE FROM Students WHERE studentId = 1;";
SqlCommand cmd = new SqlCommand(cmdText);
cmd.CommandType = CommandType.Text;

cmd.Connection = conn;
cmd.CommandTimeout = 60; // 60 sec

// 如果 SQL 命令是 INSERT、UPDATE、DELETE
cmd.ExecuteNonQuery();

cmdText 會存在 cmd.CommandText 屬性，所以如果不寫

1
SqlCommand cmd = new SqlCommand(cmdText);

改成以下也是可以的：

1
2
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdText;

此外，CommandText 可以不只是如上的 Transact-SQL statement，還可以是表的名字或 stored procedure 預存程式的名字。相對地，CommandType 要改成 CommandType.TableDirect 或 CommandType.StoredProcedure 。

3. 使用 DataReader 獲得資源

1
2
3
4
5
6
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    string value = dr["ColumnOneName"].ToString(); // If it is string type
    // ...
}

4. 關閉和釋放資源

1
2
3
4
5
dr.Close();
dr.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();

如果不想手動關閉和釋放資源，或者怕忘記，可以使用 using()

總結

  • conn 和 cmd 的順序不重要。哪一個先都可以。
  • 一個網站 (One website/instance) 只能允許三個連線 conn.Open() 同時進行，第四個要等。

可以寫成如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
using System.Data.SqlClient;

string connStr = "Data Source=Server名稱;Initial Catalog=資料庫名稱;"
    + "User Id=Username;Password=Password;";
using(SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();
    
    using(SqlCommand cmd = new SqlCommand("...."))
    {
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;
        cmd.CommandTimeout = 60;
        
        using(SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                string value = dr["ColumnOneName"].ToString();
                // ...
            }
        }
    }
}

實作與 XML 互動

記得引入 using System.Data;、using System.Xml; 命名空間。

1
2
3
4
5
6
using System.Data; // for DataSet
using System.Xml; // for XML

DataSet ds = new DataSet();
// Fill with the data
ds.ReadXml("example.xml");

更多資訊可以參考：
DataTable.ReadXml Method
ADO .NET and XML