C#快速入門教程（28）—— ADO.NET

.NET Framework類庫中用於運算元據庫的資源稱為ADO.NET，主要定義在System.Data名稱空間及其下級名稱空間，其中，System.Data.SqlClient等名稱空間中的資源可以用於SQL Server資料庫操作；此外，還可以使用OLEDB或ODBC方式進行資料庫操作。本課，我們將結合SQL Server資料庫討論ADO.NET中常用元件的應用。

ADO.NET元件按照工作時是否連線資料庫可以分為兩類，即連線元件和非連線元件（離線元件）。連線元件是指在工作時必須與資料庫進行連線，如IDbConnection、IDbCommand、IDataReader、IDataAdapter等元件；非連線元件是指可以不與資料庫連線，以離線的方式進行資料處理，如DataSet等元件。

連線資料庫

在應用中使用資料庫，第一步就是與資料庫進行連線，這裡使用IDbConnection元件，用於連線SQL Server資料庫的型別就是SqlConnection類。此外，連線資料庫時，還需要一個連線字串（ConnectionString）指定一系列的資料庫連線引數，ADO.NET中提供了SqlConnectionStringBuilder類，用於SQL Server資料庫連線字串的生成，下面的程式碼演示了SQL Server資料的連線操作。

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleTest
{
    class Program
    {
        static void Main(string[] args)
        {
            // 連線字串
            SqlConnectionStringBuilder sb =
                new SqlConnectionStringBuilder();
            sb.DataSource = @".\MSSQLSERVER1";
            sb.InitialCatalog = @"Cdb_Test";
            sb.IntegratedSecurity = true;
            sb.AsynchronousProcessing = true;
            sb.Pooling = true;
            string cnnStr = sb.ConnectionString;
            // 連線測試
            try
            {
                using (SqlConnection cnn = new SqlConnection(cnnStr))
                {
                    cnn.Open();
                    Console.WriteLine("資料庫已成功開啟");
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

首先，資料庫連線字串的生成使用了SqlConnectionStringBuilder類的一些成員，主要包括：

• DataSource屬性，指定資料庫的伺服器和例項，@".\MSSQLSERVER1"中的圓點（.）表示本機，如果是網路伺服器，應指定IP地址和SQL Server服務的TCP/IP埠。MSSQLSERVER1表示連線的資料庫例項，大家可以根據上一課記錄的內容指定。此外，如果連線本機的預設例項，可以只使用一個圓點表示。
• InitialCatalog屬性，指定連線的資料庫名稱，本例使用上一課建立的Cdb_Test資料庫。
• IntegratedSecurity屬性，是否使用整合安全（也就是我們在SSMS中使用的“Windows 身份驗證”），本例使用true，使用Windows身份驗證方式，此時會使用Windows當前登入的使用者資訊登入資料庫。如果是網路資料庫，則此屬性應該設定為false，並通過UserID和Password屬性設定登入資料庫的使用者和密碼。
• AsynchronousProcessing屬性，是否允許進行非同步操作。
• Pooling屬性，是否記動資料庫連線池，大多數情況下應該啟動。
• ConnectionString屬性，通過一系列引數生成的資料庫連線字串，這裡賦值給cnnStr物件，稍後會使用。如果大家想看資料庫連線串中的內容，可以使用Console.WriteLine()方法顯示。

接下來是連線資料庫的操作，我們知道，SqlConnection類實現了IDisposable介面，所以， 這裡使用using語句結構進行資料庫的連線操作。建立SqlConnection物件後，使用Open()方法開啟資料庫連線，如果一切順利會顯示“資料庫已成功開啟”，如果連線的引數有問題，則會顯示異常情況的描述資訊。確認可以正確連線Cdb_Test資料庫以後，就可以進行接下來的測試了。

請注意，接下來的程式碼只擷取關鍵的部分，即using(SqlConnection cnn = new SqlConnection(cnnStr)){...}部分，其他程式碼不需要改變。

執行SQL

執行SQL時，可以使用SqlCommand型別，需要注意的是建立SqlCommand物件應使用SqlConnection物件的CreateCommand()方法，如下面的程式碼。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandText = "select UserID from UserMain where UserName='Jerry';";
    Console.WriteLine(cmd.ExecuteScalar());
}

程式碼中，使用CommandText屬性設定SqlCommand執行的SQL語句，然後，使用ExecuteScalar()方法返回查詢結果第一行的第一個欄位的資料；如果沒有返回結果，則返回null值。正常情況下，執行此程式碼會顯示2，即顯示使用者名稱（UserName）為Jerry的UserID值。

返回執行結果

在SqlCommand物件中，除了ExecuteScalar()方法，還有一些方法可以返回執行結果，如：

• ExecuteNonQuery()方法，返回SQL執行影響的記錄行數，如新增、更新、刪除等操作。
• ExecuteReader()方法，返回一個記錄集，稍後會討論如何從中讀取資料。
• BeginExecuteNonQuery()和EndExecuteNonQuery()方法，ExecuteNonQuery()方法的非同步版本。
• BeginExecuteReader()和EndExecuteReader()方法，ExecuteReader()方法的非同步版本。

下面的程式碼，我們將Jerry使用者的密碼修改為5678910。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandText = "update UserMain set UserPwd = '5678910' where UserName='Jerry';";
    Console.WriteLine(cmd.ExecuteNonQuery());
}

執行程式碼顯顯示1，即修改了一條記錄的資料。

下面的程式碼會讀取所有使用者的UserName、UserPwd和IsLocked欄位資料。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandText = "select UserName,UserPwd,IsLocked from UserMain;";
    IAsyncResult ar = cmd.BeginExecuteReader();
    using (SqlDataReader dr = cmd.EndExecuteReader(ar))
    {
        // 顯示欄位名
        for (int i = 0; i < dr.FieldCount; i++)
            Console.Write(dr.GetName(i).PadRight(16));
        Console.WriteLine();
        // 顯示記錄
        while(dr.Read())
        {
            for (int i = 0; i < dr.FieldCount; i++)
                Console.Write("{0}".PadRight(16), dr[i]);
            Console.WriteLine();
        }
    }
}

程式碼執行結果如下圖所示。

本例使用了ExecuteReader()的非同步版本，其中，BeginExecuteReader()方法會返回IAsyncResult物件，而對應的EndExecuteReader()方法需要此物件作為引數。讀取後的資料集為SqlDataReader物件，使用的主要成員包括：

• FieldCount屬性，記錄集中的欄位數量。
• GetName()方法，按索引值給出欄位名。
• Read()方法，讀取下一條記錄，如果讀取成功返回true，否則返回false。程式碼中正是使用此方法的結果判斷是否讀取了有效的資料。
• 索引器，SqlDataReader物件的索引器是從0開始的數值索引，用於讀取當前記錄中相應欄位的資料，讀取的結果是object型別，如果可以確認欄位資料的型別，還可以使用GetInt32()、GetInt64()、GetFloat()、GetDecimal()等方法獲取相應型別的資料。

使用引數

前面的示例中，都直接使用SQL語句來完成資料操作，而實際工作中，如果需要組合SQL，比如，將使用者輸入的資料連線到SQL語句，此時，可能造成SQL隱碼攻擊，即使用滿足SQL語法的資料組合成具有破壞性的操作語句。

避免資料注入的一個好辦法就是使用引數，在SqlCommand中有一個Parameters屬性，它是一個引數集合，可以使用其中的AddWithValue()方法新增引數和資料，如下面的程式碼，會在UserMain表中新增一條記錄。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandText = @"insert into UserMain(UserName,UserPwd,IsLocked) output inserted.UserID values(@UserName,@UserPwd,@IsLocked);";
    cmd.Parameters.AddWithValue("@UserName", "Smith");
    cmd.Parameters.AddWithValue("@UserPwd", "123456");
    cmd.Parameters.AddWithValue("@IsLocked", 0);
    //
    Console.WriteLine(cmd.ExecuteScalar());
}

請注意SQL語句，在values關鍵字後的資料列表中，使用了以@字元開始的引數；然後，通過SqlCommand中Parameters屬性的AddWithValue()方法分別新增這些引數及資料。程式碼中，還使用SQL Server資料庫的另一個特點，即inserted表，此表會儲存會話中新新增的資料，使用“output inserted.UserID”子語可以讓insert語句直接返回新記錄的UserID欄位值；程式碼的最後就是使用cmd.ExecuteScalar()方法返回這個新新增記錄的UserID欄位值。

執行儲存過程

為了方便測試，我們先在SQL Server中的Cdb_Test資料庫中新增一個名為usp_login的儲存過程；在SSMS中新建一個查詢，並執行以下程式碼。

use Cdb_Test;
go

create procedure usp_login
    @username as nvarchar(50),@userpwd as nvarchar(50)
as
begin
    select UserID from UserMain where UserName=@username and UserPwd = @userpwd and IsLocked=0;
end;

此儲存過程的作用就是驗證使用者的登入，其中，需要給入@username和@userpwd兩個引數，而且使用者並沒被鎖定（IsLocked=0）。執行儲存過程，如果使用者登入資訊正確會返回UserID資料。

接下來，我們回到C#程式碼，通過SqlCommand物件呼叫usp_login儲存過程，如下面的程式碼。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandText = "usp_login";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@username", "Smith");
    cmd.Parameters.AddWithValue("@userpwd", "123456");
    //
    Console.WriteLine(cmd.ExecuteScalar());
}

本例中，直接將SqlCommand物件的CommandText屬性設定為儲存過程的名稱，但同時需要將CommandType屬性設定為StoredProcedure值（預設為Text，即按SQL語句執行）；接下來，通過SqlCommand.Parameters物件的AddWithValue()方法新增儲存過程所需要的引數和資料。如果顯示的結果是大於0的整數，說明使用者登入成功，否則會返回null值（顯示為空白）。

執行事務

上一課，我們介紹過事務的特點，即一個事務中的一個或多個任務，要不全部完成，要不什麼都不做。在ADO.NET元件中，使用SqlTransaction類執行SQL Server資料庫的事務，如下面的程式碼。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    using (SqlTransaction tran = cnn.BeginTransaction())
    {
        cmd.Transaction = tran;
        cmd.CommandText = "insert into UserMain(UserName,UserPwd) values(@username,@userpwd);";
        cmd.Parameters.AddWithValue("@username", "Frank");
        cmd.Parameters.AddWithValue("@userpwd", "123456");
        if(cmd.ExecuteNonQuery()==1)
        {
            cmd.CommandText = "select @@IDENTITY;";
            object obj = cmd.ExecuteScalar();
            tran.Commit();
            Console.WriteLine(obj);
        }
    }
}

本例中使用事務時，首先通過SqlConnection物件的BeginTransaction()方法開始事務，然後通過SqlCommand物件的Transaction屬性與SqlTransaction物件關聯，這樣就可以在事務中執行命令了；程式碼中的事務是新增一條使用者資訊並返回新的ID值，這裡共執行了兩條語句，當兩條語句都執行成功時，使用SqlTransaction物件的Commit()方法提交事務所做的修改。

實際應用中，如果有某條語句的執行結果不是預期的，還可以使用SqlTransaction物件的Rollback()方法進行回滾，即恢復事務執行前的狀態。另一方面，如果執行事務中發生發生異常，在using語句結構中執行的事務會自動進行回滾操作。

小結

本課以SQL Server資料庫為例，討論瞭如果使用ADO.NET元件運算元據庫；資料應用開發過程中，希望可以舉一反三，找到ADO.NET元件在操作不同資料庫時的共同點，以提高學習和工作效率。

另一方面，.NET Framework原生類庫中已放棄對Oracle資料庫的支援；在專案中使用Oracle資料庫或MySQL等資料庫時，應該從資料庫的官方網站獲取相應的ADO.NET元件。

CHY軟體小屋原創作品！