C# + MySql 存贮过程开发示例

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

MySql 发展至今，已经不是当初那个只依靠免费和速度取胜的开源数据库服务器了，它提供的一系列 GUI 工具，以及加入商业数据库的一些特性，依然免费并且开源，让我们实在无法拒绝它的魅力。

很多人在谈及 MySql 可能会条件反射般地联想到 php，就像很多人在谈及 asp 或 asp.net 就会联系到 SqlServer 一样。其实，ADO.NET 配合 MySql，其实也是一对不错的鸳鸯哦。

以 MySql Server 5.0 为例，首先到 MySql 网站下载 GUI 工具，包括 Administrator 和 Query Browser 这两个常用的 GUI 工具，它们的作用相当于 SqlServer 下的企业管理器和查询分析器。当然，GUI 工具并非必须，我们完全可以在控制台下实现相同的目的（如果你愿意那样做的话）。

打开 MySql Administrator，以管理员权限登录到 MySql Server，新建一个数据库，名定名为 db_test。再分别创建一个 user 表和一个 mail_check 表，字段请参考如下 SQL 文本：

CREATE TABLE `db_test`.`user` (
  `id` CHAR(36) NOT NULL COMMENT '用户ID',
  `username` VARCHAR(45) NOT NULL COMMENT '用户名',
  `password` VARCHAR(45) NOT NULL COMMENT '密码',
  `gender` SMALLINT UNSIGNED NOT NULL COMMENT '性别',
  `birth_year` INTEGER UNSIGNED NOT NULL COMMENT '出生年份',
  `email` VARCHAR(45) DEFAULT NULL COMMENT '邮箱',
  `face_img_extend` VARCHAR(45) DEFAULT NULL COMMENT '头像扩展名',
  `email_checked` BOOLEAN NOT NULL DEFAULT false COMMENT '邮箱是否得通过验证',
  `allow_upload` BOOLEAN NOT NULL DEFAULT false COMMENT '是否允许上传文件',
  `allow_comment` BOOLEAN NOT NULL DEFAULT true COMMENT '是否允许评论',
  `reg_date` DATETIME NOT NULL COMMENT '注册日期',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `Index_username`(`username`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT = '注册用户信息表';

CREATE TABLE `db_test`.`mail_check` (
  `user_id` CHAR(36) NOT NULL COMMENT '用户GUID',
  `close_date` DATETIME NOT NULL COMMENT '允许验证的时间期限，如当前时间晚于这个时间，记录无效',
  `random_code` CHAR(5) NOT NULL DEFAULT 00000 COMMENT '5位数随机验证码',
  PRIMARY KEY (`user_id`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci
COMMENT = '待验证的邮箱列表';

之后，创建一个存储过程，用于处理过期为验证的邮箱，它必须同时完成两个步骤：
1) 将 user 表中具有指定 id 的行的 email 字段设置为 null
2) 将 user 表中具有指定 id 的行的 email_checked 字段设置为 0（false）
3) 将 user 表中具有指定 id 的行的 allow_upload 字段的值设置为 0（fase）
4) 从 mail_check 表中删除过期为验证的游戏地址

根据以上规则，我们创建一个简单的存贮过程用于完成我们的目的：

CREATE PROCEDURE `removeNoCheckedMail`(in currentTime datetime)
BEGIN

        # open a transaction session
        START TRANSACTION;

        # 01: Update user table
        UPDATE `user` u SET
        u.email = null
        AND u.email_checked = 0
        AND u.allow_upload = 0
        WHERE u.id IN
        (SELECT m.user_id FROM mail_check m WHERE close_date > currentTime);

        # 02: Delete from mail_check
        DELETE FROM mail_check WHERE close_date > currentTime;

        # commit the session
        COMMIT;

END

之后，我们需要在后台编码调用这个存贮过程，代码如下：

/// <summary>
/// 处理所有过期未通过验证的邮箱
/// </summary>
public void RemoveNoCheckedEmails() {
    // 注意：这里的存储过程只能是名称，参数加入到 MySqlParameter 列表中传回数据库服务器。
    DBUtility.ExecuteNonQuery(CommandType.StoredProcedure, "removeNoCheckedMail",
        new MySqlParameter("?currentTime", DateTime.Now)
    );
}

DBUtility.ExecuteNonQuery 方法代码如下：

public sealed class DBUtility {

public static string ConnectionString =
    "server=localhost;user id=root; password=123456; database=db_test; pooling=true; charset=utf8"

public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) {
            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms) {

    if (conn.State != ConnectionState.Open)
        conn.Open();

    cmd.Connection = conn;
    cmd.CommandText = cmdText;

    if (trans != null)
        cmd.Transaction = trans;

    cmd.CommandType = cmdType;

    if (cmdParms != null) {
        foreach (MySqlParameter parm in cmdParms)
            cmd.Parameters.Add(parm);
    }
}

}