Advanced usage of SQL

SQL 不僅能進行簡單的查詢資料，還能進行更多細節設定與操作。

Table of Contents

Custom Connection

除了在Congfig中設定預設的連線資訊之外，也可以直接從程式碼中指定

示範：

string Host = "127.0.0.1";     // 資料庫位置
string DBName = "Fpage";       // 資料庫名稱
string User = "fpage";         // 連線帳號
string Password = "I7890";     // 連線密碼

SQL db = new SQL(Host, DBName, User, Password); // 建立資料庫存取物件

db.Timeout = 15;                        // 設定連線逾時秒數，預設 15 秒
db.ApplicationIntent = "ReadWrite";     // 該連線所需意圖，預設 ReadWrite
db.MultiSubnetFailover = false;         // 是否啟用容錯移轉叢集執行個體，預設 false
db.Encrypt = false;                     // 是否啟用加密，預設 false
db.TrustServerCertificate = false;      // 連結到執行個體時使用憑證，預設 false

object[] o = db.quickQuery<object>("select * from class");  // 查詢

v1.20 也可以使用自訂的連線字串(Connection String)進行連線 

SQL db = new SQL("Data Source=(LocalDb)\MSSQLLocalDB;Initial ..."); // 使用自訂 Connection String 連線

備註：Timeout, ApplicationIntent, MultiSubnetFailover ... 等屬性，多數情況不需設定

Exec Stored Procedure

以下範例將示範使用 quickExec() 執行 Stored Procedure，假設有個 Stored Procedure 像是這樣

輸入：

  • act (string)
  • passportcode (string)

輸出：

  • res (int)
create procedure [dbo].[xp_checklogin]
    @act nvarchar(100),
    @passportcode nvarchar(64),
    @res int output
as 
begin
...

定義輸出資料的資料模型 (Model)，注意屬性名稱需相同

示範：

class ModelOutput
{
    public int res { get; set; }
};

執行 Stored Procedure，並取得輸出的資料自動填充到 Model 裡，最後自動關閉連線

示範：

SQL db = new SQL();

// 輸入參數
var input_para = new
{
    act = "admin",
    passportcode = "123456789"
};

// 輸出參數定義 (使用 System.Data.SqlDbType 對應資料型態)
var output_para = new
{
    res = SqlDbType.Int
};

// 執行 Stored Procedure 
ModelOutput result = db.quickExec<ModelOutput>("xp_checklogin",input_para,output_para);

if (result == null) // 判斷是否執行成功
    Console.WriteLine(db.getErrorMessage()); // 執行出錯，印出錯誤訊息
else 
    Console.WriteLine(result.res); // 執行成功，印出輸出資料

備註

Detail Control

以下將示範滿足複雜需求，同時建立兩個 SQL 連線，第一個連線取出資料，並使用第二個連線寫入資料，挑戰如下：

  • 同時開啟兩個 SQL 連線並交換資料
  • 對每一筆資料逐一處理
  • 同一個連線中進行多次寫入 (I/O 效能)

示範：

SQL db1 = new SQL(Host1, DBName1, User1, Password1);  // 第一個連線
SQL db2 = new SQL(Host2, DBName2, User2, Password2);  // 第二個連線

db1.connet(); // 手動連線
db2.connet(); // 手動連線

if (db1.isConn && db2.isConn) // 確認連線成功
{
    string sql_1 = "select oid from object";                   // 第一個連線，取出資料 SQL
    string sql_2 = "insert into TestTable(oid) values(@oid)";  // 第二個連線，寫入資料 SQL

    SqlDataReader reader1 = db1.query(sql_1); // 第一個連線，執行查詢
    ModelObject[] data1 = db1.fetch<ModelObject>(reader1, false);  // 第一個連線，取出一筆資料

    while (data1 != null && data1.Length>0)  // 第一個連線，有取出資料
    {
        int id = data1[0].oid;
        var para = new
        {
            oid = id
        };

        SqlDataReader reader2 = db2.query(sql_2, para);     // 第二個連線，執行寫入
        var data2 = db2.fetch<ModelObject>(reader2, false); // 第二個連線，取得寫入結果

        if (data2 == null)  // 寫入失敗
            Console.WriteLine("inserted fail");  

        reader2.Close(); 
        data1 = db1.fetch<ModelObject>(reader1, false); // 第一個連線，取出下一筆資料
    }
    reader1.Close();
}       

// 手動關閉資料庫連線
db1.close();
db2.close();

方法 quickQuery() 與 quickExec() 底層實作也同上所示

Transaction

如果需要針對 新增/修改/刪除 的動作添加復原機制，可以開啟 SQL API 內建的 Transaction 功能。

  • SQL db = new SQL(true);
  • SQL db = new SQL(Host, DBName, User, Password, true);

示範：

string sql_1 = "insert into TestTable(oid) values(@oid)";  // 第一個 SQL，會執行成功
string sql_2 = "insert2 into TestTable(oid) values(@oid)"; // 第二個 SQL，語法有錯誤，不會執行成功

SQL db = new SQL(true);  // 開啟 Transaction

db.connet(); // 連線並開始 Transaction

if (db.isConn)
{
    try
    {
        var reader = db.query(sql_1, new { oid = 10 }); // 執行第一個 SQL
        reader.Close();
        reader = db.query(sql_2, new { oid = 20 }); // 執行第二個 SQL
        reader.Close();
        db.tran.Commit();  // 生效所有的語法
    }
    catch(Exception e)
    {
        db.tran.Rollback();    // 復原全部的改變
        Console.WriteLine(e.ToString()); // 輸出錯誤訊息
    }
}

db.close();

輸出：

System.Data.SqlClient.SqlException (0x80131904): 接近關鍵字 'into' 之處的語法不正確。
   於 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   於 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   於 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean as
...

Transaction 也支援 quickQuery() 與 quickExec()，但 Stored Procedure 中請勿撰寫 transcation SQL 語法避免衝突
Transaction 在執行過程中會鎖定作用到的所有資料，請注意可能發生效能問題

Writing Large Amounts Of Data

雖然 Detail Control 章節示範了在同一連線中執行多次查詢，但如果要同時寫入超過上千/上萬筆資料，還是會有效能瓶頸。可以使用 SQL API 提供的 Bulk Copy 方法進行大量資料同時寫入

  • quickBulkCopy(DataTable, TableName)

示範：

// 依照要寫入的 Table 的欄位，建立 DataTable 物件，將要寫入的資料儲存其中
var dt = new DataTable();
dt.Columns.Add("oid", typeof(int));

for(int i =0; i < 100000; i++) // 建立 10 萬筆資料
{
    var row = dt.NewRow();
    row["oid"] = i;
    dt.Rows.Add(row);
}

SQL db = new SQL(); // 建立資料庫存取物件
bool result = db.quickBulkCopy(dt, "dbo.TestTable"); // 指定 Table 名稱，並使用 BulkCopy 方法寫入

if (!result) // 如果寫入失敗
    Console.WriteLine(db.getErrorMessage());

根據實測，寫入 10 萬筆資料僅需要 3 秒鐘，完成後，會自動關閉資料庫連線 大量寫入如需對每一筆資料進行額外檢查，可以撰寫 trigger 來達成

More

results matching ""

    No results matching ""