Basic MariaDB/MySQL Stored Procedure with .NET Standard


介紹如何使用 MySqlConnector 讓 .NET Standard/.NET Core 可以對 MariaDB/MySQL 進行基本的 Stored Procedure 操作.

延續前一篇的Basic MariaDB/MySQL CRUD with .NET Standard

關聯式資料庫依定會遇到 Stored Procedure 的使用,一個 SP 的組成為下:

00.png

而在 MySQL/MariaDB 把 Stored ProceduresStored Functions 合稱為 Stored Routines

  • Stored Procedures

    官方解釋為:
    `Stored Procedures Routine invoked with a CALL statement.

    是一個可預先宣告的 SQL 語句,可透過 CALL 來呼叫.所以 Stored Procedures 可單獨做使用。

  • Stored Functions

    官方解釋為:
    `Stored Functions Defined Functions for use with SQL Statements.

    是一個預先定義好的函示,可在任一段 SQL 語句中呼叫使用.所以 Stored Functions 必須依賴在有 Stored Procedures 的情境下做使用(非硬規定)

國外知名論壇也有討論兩者的差異與整理如下:

01.jpg

而如果要撰寫一個 SP 包含資料回傳的話有 output 與 return value 兩種寫法

基本 SP 撰寫 - 使用 output

這邊如果要定義一個 SP 的可以透過下面的範例格式在資料庫實體執行:

DELIMITER //

CREATE PROCEDURE QUERY_USERS_COUNT (OUT param1 INT)
    BEGIN
        SELECT COUNT(*) INTO param1 FROM User;
    END
//

DELIMITER ;

以上寫法的第一行 DELIMITER // 主要是將換行字元從預設的 ; 換為 // 避免在建立 SP 產生錯誤,詳細內容可以參考只談MySQL (第16天) Stored Procedure及Function。而最後一行同理就是將其換行字元換為原先的 ;

而上述語法可以看到我們用 OUT 當作外部宣告的參數傳入後在運行完整個查詢後將總數填入此 param1 內,所以今天我們在 MariaDB/MySQL 的 console 或是工具呼叫取用時反而要用下方語法:

CALL QUERY_USERS_COUNT(@a);SELECT @a;

這邊以 Jetbrains 的跨平台資料庫 IDE - datagrip 為例做操作:

02.jpg

而對應的 C# 程式碼為:

public async Task<int> GetUserCountBySPWithOutputValue()
{
    using (var conn = new MySqlConnection(_connStrinng))
    {
        await conn.OpenAsync();
        // Calling SP with output
        using (var cmd = new MySqlCommand())
        {
            var parameter = new MySqlParameter("@param1",MySqlDbType.Int32);
            parameter.Direction = ParameterDirection.Output;
            
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "QUERY_USERS_COUNT";
            cmd.Parameters.Add(parameter);
            
            await cmd.ExecuteNonQueryAsync();

            return Convert.ToInt32(parameter.Value);
        }
    }
}

基本 SP 撰寫 - 使用 return value

而如果將上述語法改為 return value 則為:

DELIMITER //

CREATE PROCEDURE LabMariabDB.QUERY_USERS_COUNT_WITH_RETURNVALUE()
    BEGIN
        SELECT COUNT(*) AS "Count" FROM User;
    END
//

DELIMITER ;

而這邊呼叫也相對簡單一點:

CALL QUERY_USERS_COUNT_WITH_RETURNVALUE();

而對應的 C# 程式碼為:

public async Task<int> GetUserCountBySPWithReturnValue()
{
    using (var conn = new MySqlConnection(_connStrinng))
    {
        await conn.OpenAsync();
        // Calling SP with return value
        using (var cmd = new MySqlCommand())
        {
            
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "QUERY_USERS_COUNT_WITH_RETURNVALUE";
            
            var reader = await cmd.ExecuteReaderAsync();
            await reader.ReadAsync();
            return reader.GetInt32(0);
        }
    }
}

而完整的範例與測試可以參考:dotnet-mariadb-lab

References


作者: Blackie
版權聲明: 本站所有文章除特別聲明外,均採用 CC BY 4.0 許可協議。轉載請註明來源 Blackie !
  目錄