图书 合集 文章 工具与资源 本站查询
C#开发训练营

第30课：实现MySQL操作组件(3)

本课将完成支持MySQL数据库操作的tSelect和tBatch接口组件类。

tSelect接口

tSelect接口组件会使用select语句查询数据，并返回查询结果，接口定义如下。

C#
public interface tSelect
{
    tJet Jet { get; }
    string Source { get; }
    // 将字段名列表转换为对象格式
    string GetObjFormat(string fields);
    //
    tPair GetValue(string returnField, tCond cond, int limit = -1);
    tPairList GetFirstRow(string returnField, tCond cond, int limit = -1);
    List<object> GetFirstColumn(string returnField, tCond cond, int limit = -1);
    DataTable GetTable(string returnField, tCond cond, int limit = -1);
    List<double> GetData(string returnField, tCond cond, int limit = -1);
    //
    tPair GetValue(string returnField, string cond, int limit = -1);
    tPairList GetFirstRow(string returnField, string cond, int limit = -1);
    List<object> GetFirstColumn(string returnField, string cond, int limit = -1);
    DataTable GetTable(string returnField, string cond, int limit = -1);
    List<double> GetData(string returnField, string cond, int limit = -1);
}

下面的代码（/app_code/data/mysql/tMySqlSelect.cs）是tMySqlSelect类的基本定义，用于MySQL数据库的查询操作，它继承于tSelectBase类。

C#
using System.Text;
using System.Collections.Generic;
using System.Data;

public class tMySqlSelect : tSelectBase
{
    public tMySqlSelect(tJet jet, string source)
    : base(jet, source) { }
    // 其它代码...
}

下面是两个版本的GetSelectSql()辅助方法，用于生成select语句。

C#
protected string GetSelectSql(string returnField, 
    tCond cond, int limit, out tPairList args)
{
    string condSql = tCondHelper.GetSql(Jet, cond, out args);
    //
    StringBuilder sb = new StringBuilder("select ", 256);
    if (condSql == "")
        sb.AppendFormat(" {0} from `{1}`", returnField, Source);
    else
        sb.AppendFormat(" {0} from `{1}` where {2}", returnField, Source, condSql);
    //
    if (limit > 0) sb.AppendFormat(" limit {0} ", limit);
    return sb.ToString();
}
//
protected string GetSelectSql(string returnField, string cond, int limit)
{
    StringBuilder sb = new StringBuilder("select ", 256);
    if (cond == null || cond.Trim() == "")
        sb.AppendFormat(" {0} from `{1}`", returnField, Source);
    else
        sb.AppendFormat(" {0} from `{1}` where {2}", returnField, Source, cond);
    //
    if (limit > 0) sb.AppendFormat(" limit {0} ", limit);
    return sb.ToString();
}

GetSelectSql()方法中，使用字符串指定查询条件时，返回的select语句格式如下。

MySQL
select <ReturnField参数> from `<表>` where <sCond参数>

使用tCond对象指定查询条件时，返回的select语句格式如下。

SQL
select <ReturnField参数> from `<表>` where
`<条件字段1>`=?cond_0,`<条件字段2>`=?cond_1,`<条件字段3>`=?cond_2,…

此外，使用tCond对象作为条件时，方法还会通过输出参数返回所需要的参数（tPairList对象）。

生成Select语句后，获取查询结果的方法比较容易实现了，先来看GetValue()方法实现，如下面的代码。

C#
//
public override tPair GetValue(string returnField, tCond cond, int limit = -1)
{
    tPairList args;
    string sql = GetSelectSql(returnField, cond, limit, out args);
    if (sql == "") return new tPair();
    return Jet.GetValue(sql, args.ToArray());
}
//
public override tPair GetValue(string returnField, string cond, int limit = -1)
{
    string sql = GetSelectSql(returnField, cond, limit);
    if (sql == "") return new tPair();
    return Jet.GetValue(sql);
}

代码中，首先会生成相应的select语句，并生成所需要的参数数据；然后会调用tJet接口的GetValue()方法执行语句并返回查询结果。

返回第一行记录时使用GetFirstRow()方法，实现如下。

C#
//
public override tPairList GetFirstRow(string returnField, tCond cond, int limit = -1)
{
    tPairList args;
    string sql = GetSelectSql(returnField, cond, limit, out args);
    if (sql == "") return new tPairList();
    return Jet.GetFirstRow(sql, args.ToArray());
}
//
public override tPairList GetFirstRow(string returnField, string cond, int limit = -1)
{
    string sql = GetSelectSql(returnField, cond, limit);
    if (sql == "") return new tPairList();
    return Jet.GetFirstRow(sql);
}

请注意，记录数据使用tPairList对象返回，如果没有查询记录，则返回0个成员的tPiarList对象，这也是tJet.GetFirstRow()方法的执行规则。

获取查询结果中第一列数据时使用tSelect.GetFirstColumn()方法，实现如下。

C#
//
public override List<object> GetFirstColumn(string returnField, tCond cond, int limit = -1)
{
    tPairList args;
    string sql = GetSelectSql(returnField, cond, limit, out args);
    if (sql == "") return new List<object>();
    return Jet.GetFirstColumn(sql, args.ToArray());
}
//
public override List<object> GetFirstColumn(string returnField, string cond, int limit = -1)
{
    string sql = GetSelectSql(returnField, cond, limit);
    if (sql == "") return new List<object>();
    return Jet.GetFirstColumn(sql);
}

返回全部查询结果时使用GetTable()方法，实现如下。

C#
//
public override DataTable GetTable(string returnField, tCond cond, int limit = -1)
{
    tPairList args;
    string sql = GetSelectSql(returnField, cond, limit, out args);
    if (sql == "") return null;
    return Jet.GetTable(sql, args.ToArray());
}
//
public override DataTable GetTable(string returnField, string cond, int limit = -1)
{
    string sql = GetSelectSql(returnField, cond, limit);
    if (sql == "") return null;
    return Jet.GetTable(sql);
}

tSelect.GetData()方法用于获取查询结果中第一列的double数据，返回类型为List<double>对象，tMySqlSelect类中的实现如下。

C#
//
public override List<double> GetData(string returnField, tCond cond, int limit = -1)
{
    tPairList args;
    string sql = GetSelectSql(returnField, cond, limit, out args);
    if (sql == "") return new List<double>();
    return Jet.GetData(sql, args.ToArray());
}
//
public override List<double> GetData(string returnField, string cond, int limit = -1)
{
    string sql = GetSelectSql(returnField, cond, limit);
    if (sql == "") return new List<double>();
    return Jet.GetData(sql);
}

tBatch接口

tBatch接口组件用于批量更新数据表记录，接口定义如下。

C#
public interface tBatch
{
    tJet Jet { get; }
    string TableName { get; }
    string IdName { get; }
    // 记录主键，用于判断记录是否存在，更新时必须指定
    string[] PrimaryKey { get; set; }
    // 数据
    DataTable Data { get; set; }
    //
    int Insert();
    int TruncateAndInsert();
    int Update();
    int InsertOrUpdate(out int insertCounter, out int updateCounter);
}

接下来使用tMySqlBatch类作为MySQL数据库的批处理操作类，基本实现如下（/app_code/data/mysql/tMySqlBatch.cs）。

C#
using System;
using System.Text;
using MySql.Data.MySqlClient;

// 数据批量导入，通过事务完成
public class tMySqlBatch : tBatchBase
{
    // 
    public tMySqlBatch(tJet jet, string sTable, string sIdName)
        : base(jet, sTable, sIdName) { }
    // 添加参数数据
    protected void AddParam(MySqlCommand cmd, int row)
    {
        for (int col = 0; col < Data.Columns.Count; col++)
        {
            cmd.Parameters.AddWithValue("?arg_" + col.ToString(), 
                Data.Rows[row][col]);
        }
    }
    // 其它代码...
}

代码中，tMySqlBatch类继承于tBatchBase，并通过继承构造函数设置了Jet、TableName和IdName属性值。

AddParam()方法用于向指定的MySqlCommand对象添加参数数据，其中，参数一指定MySqlCommand对象，参数二指定Data对象中的行索引值。添加的参数名格式为?arg_<columnIndex>，按照列索引值的顺序添加。

下面的代码实现了tMySqlBatch.Insert()方法和生成insert语句的GetInsertSql()方法。

C#
// 生成insert语句
protected string GetInsertSql()
{
    // 第一个字段
    StringBuilder sb = new StringBuilder("insert into ", 256);
    sb.AppendFormat("`{0}`(`{1}`", TableName, Data.Columns[0].ColumnName);
    StringBuilder sbValue = new StringBuilder(")values(?arg_0", 128);
    // 其它字段
    for (int col = 1; col < Data.Columns.Count; col++)
    {
        sb.AppendFormat(",`{0}`", Data.Columns[col].ColumnName);
        sbValue.AppendFormat(",?arg_{0}", col);
    }
    //
    sb.Append(sbValue.ToString());
    sb.Append(")");
    return sb.ToString();
}
//
public override int Insert()
{
     try
     {
        if (Data == null || Data.Rows.Count == 0) return -1001;
        string insSql = GetInsertSql();
        using (MySqlConnection cnn = new MySqlConnection(Jet.CnnStr))
        {
            cnn.Open();
            MySqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = insSql;
            using (MySqlTransaction tran = cnn.BeginTransaction())
            {
                cmd.Transaction = tran;
                int counter = 0;
                for (int row = 0; row < Data.Rows.Count; row++)
                {
                    cmd.Parameters.Clear();
                    AddParam(cmd, row);
                    counter += cmd.ExecuteNonQuery();
                }
                tran.Commit();
                return counter;
            }
        }
    }
    catch (Exception ex)
    {
        tLog.E(ex, -1000, "tMySqlBatch.Insert()");
        return -1000;
    }
}

代码中首先定义了GetInsertSql()方法，它会根据TableName属性、Data中的列名（字段名）生成Insert语句，其中的参数名称使用arg_<columnIndex>格式。生的语句格式如下。

MySQL
insert into `<表>`(`<值1>`,`<值2>`,`<值3>`,…) 
values(?arg_0,?arg_1,?arg_2,…);

Insert()方法中，通过事务（Transaction）执行记录的追加操作，只有Data对象中的所有数据成功添加到表时才会提交事务，否则不会修改表中的数据。也就是说，Data中的数据必须全部导入，否则就不做任何修改。

Insert()方法是将Data中的数据追加到表中，而TruncateAndInsert()方法则是将数据表清空后再将Data中的数据导入，实现代码如下。

C#
public override int TruncateAndInsert()
{
    try
    {
        if (Data == null || Data.Rows.Count == 0) return -1001;
        string insSql = GetInsertSql();
        using (MySqlConnection cnn = new MySqlConnection(Jet.CnnStr))
        {
            cnn.Open();
            MySqlCommand cmd = cnn.CreateCommand();
            using (MySqlTransaction tran = cnn.BeginTransaction())
            {
                cmd.Transaction = tran;
                // 清表
                cmd.CommandText = "truncate table `" + TableName + "`";
                cmd.ExecuteNonQuery();
                //
                cmd.CommandText = insSql;
                int counter = 0;
                for (int row = 0; row < Data.Rows.Count; row++)
                {
                    cmd.Parameters.Clear();
                    AddParam(cmd, row);
                    counter += cmd.ExecuteNonQuery();
                }
                tran.Commit();
                return counter;
            }
        }
    }
    catch (Exception ex)
    {
        tLog.E(ex, -1000, "tMySqlBatch.TruncateAndInsert()");
        return -1000;
    }
}

代码中，在导入数据之前会使用truncate语句重置数据表，全部成功导入时返回导入的记录数量，否则数据不做修改。

下面的代码实现了Update()方法和GetUpdateSql()方法，用于数据的批量更新操作。

C#
// 生成Update语句
protected string GetUpdateSql()
{
    StringBuilder sb = new StringBuilder("update", 256);
    sb.AppendFormat(" `{0}` set `{1}`=?arg_0",
        TableName, Data.Columns[0].ColumnName);
    int dataColCount = Data.Columns.Count - PrimaryKey.Length;
    for (int col = 1; col < dataColCount; col++)
    {
        sb.AppendFormat(",`{0}`=?arg_{1}",
            Data.Columns[col].ColumnName, col);
    }
    // 条件
    sb.AppendFormat(" where `{0}`=?arg_{1}",
        Data.Columns[dataColCount].ColumnName, dataColCount);
    for (int col = dataColCount + 1; col < Data.Columns.Count; col++)
    {
        sb.AppendFormat(" and `{0}`=?arg_{1}",
            Data.Columns[col].ColumnName, col);
    }
    return sb.ToString();
}
//
public override int Update()
{
    try
    {
        if (Data == null || Data.Rows.Count == 0) return -1001;
        // 预处理，将主键字段移动到前面
        if (DataPreprocess() == false) return -1002;
        string updSql = GetUpdateSql();
        //
        using (MySqlConnection cnn = new MySqlConnection(Jet.CnnStr))
        {
            cnn.Open();
            MySqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = updSql;
            using (MySqlTransaction tran = cnn.BeginTransaction())
            {
                cmd.Transaction = tran;
                int counter = 0;
                for (int row = 0; row < Data.Rows.Count; row++)
                {
                    cmd.Parameters.Clear();
                    AddParam(cmd, row);
                    counter += cmd.ExecuteNonQuery();
                }
                tran.Commit();
                return counter;
            }
        }
    }
    catch (Exception ex)
    {
        tLog.E(ex, -1000, "tMySqlBatch.Update()");
        return -1000;
    }
}

GetUpdateSql()方法生成的update语句格式如下。

MySQL
update `<表>` set `<字段1>`=?arg_0,`<字段2>`=?arg_1,…,`<字段n>`=?arg_n
where `<字段n+1>`=?arg_<n+1>  and  `<字段n+2>`=?arg_<n+2>,…

Update()方法中，同样使用事务处理数据批量更新操作，只有数据全部成功更新时返回影响的记录数，否则不做任何修改。

tBatch接口中的最后一个操作是更新已有记录，或添加表中不存在的记录，使用InsertOrUpdate()方法实现；此外，GetSelectSql()方法用于创建查询记录是否存在的select语句；相关代码实现如下。

C#
//
protected string GetSelectSql()
{
    int dataColCount = Data.Columns.Count - PrimaryKey.Length;
    StringBuilder sb = new StringBuilder("select ", 150);
    sb.AppendFormat(" `{0}` from `{1}` where `{2}`=?arg_{3}",
        IdName, TableName,
        Data.Columns[dataColCount].ColumnName, dataColCount);
    for (int col = dataColCount + 1; col < Data.Columns.Count; col++)
    {
        sb.AppendFormat(" and `{0}`=?arg_{1}",
            Data.Columns[col].ColumnName, col);
    }
    sb.Append(" limit 1");
    return sb.ToString();
}
//
public override int InsertOrUpdate(out int insertCounter, out int updateCounter)
{
    insertCounter = 0;
    updateCounter = 0;
    try
    {
        //
        if (Data == null || Data.Rows.Count == 0) return -1001;
        if (DataPreprocess() == false) return -1002;
        //
        string insSql = GetInsertSql();
        string updSql = GetUpdateSql();
        string selSql = GetSelectSql();
        //
        int dataColCount = Data.Columns.Count - PrimaryKey.Length;
        //
        using (MySqlConnection cnn = new MySqlConnection(Jet.CnnStr))
        {
            cnn.Open();
            MySqlCommand cmd = cnn.CreateCommand();
            using (MySqlTransaction tran = cnn.BeginTransaction())
            {
                cmd.Transaction = tran;
                for (int row = 0; row < Data.Rows.Count; row++)
                {
                    // 判断记录是否存在
                    cmd.CommandText = selSql;
                    cmd.Parameters.Clear();
                    for (int col = dataColCount; col < Data.Columns.Count; col++)
                    {
                        cmd.Parameters.AddWithValue(
                            "?arg_" + col.ToString(), Data.Rows[row][col]);
                    }
                    long recid = tLng.GetValue(cmd.ExecuteScalar());
                    cmd.Parameters.Clear();
                    AddParam(cmd, row);
                    if (recid > 0)
                    {
                        // 更新记录
                        cmd.CommandText = updSql;
                        updateCounter += cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        // 添加记录
                        cmd.CommandText = insSql;
                        insertCounter += cmd.ExecuteNonQuery();
                    }
                }
                tran.Commit();
                return insertCounter + updateCounter;
            }
        }
    }
    catch (Exception ex)
    {
        tLog.E(ex, -1000, "tMySqlBatch.InsertOrUpdate()");
        return -1000;
    }
}

GetSelectSql()方法生成的语句会返回记录ID字段的数据，InsertOrUpdate()方法中，通过循环处理Data属性中的每一行数据，首先根据主键数据判断记录是否存在；记录存在时，使用Update语句更新数据，否则，使用Insert语句添加新的记录。输出参数insertCounter保存了添加的记录数量，updateCounter保存了更新的记录数量，方法的返回值则是这两个输出参数的和。这里同样使用了事务，除非全部数据都已正确处理，否则不对原数据进行修改。

豫ICP备11002860-1

公安机关备案41152102000002号

caohuayu.com    本站内容均为原创，转载请注明出处