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

第20课：数据库常用操作(3)——批量导入

在一些数据驱动的项目中，可能需要批量交换数据，如通过批量导入保持数据同步等操作；本课将讨论如何使用tBatch接口组件将DataTable对象数据更新到数据库中。

下面的代码（/app_code/data/base/tBatch.cs）定义了tBatch接口。

C#
using System.Data;

// 数据批量导入，通过事务完成
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);
}

接口中定义了五个基本属性，分别是：

  • Jet只读属性，定义tJet对象。
  • TableName只读属性，定义操作的数据表名。
  • IdName只读属性，定义ID字段名。
  • PrimaryKey属性，定义为字符串数组，指定用于更新记录的主键字段。
  • Data属性，定义为DataTable对象，指定导入的数据集合。

执行数据批量导入的方法包括：

  • Insert()方法，将Data属性指定的数据追加到数据表中，方法返回添加的记录数量。此方法操作时不需要指定主键字段。
  • TruncateAndInsert()方法，清空数据表后导入Data属性指定的数据，方法返回添加的记录数量。此方法操作时同样不需要指定主键字段。
  • Update()方法，将Data属性指定的数据按主键数据更新到数据表，方法返回更新记录的数量。
  • InsertOrUpdate()方法，根据主键数据判断，记录存在时更新数据，记录不存在时添加数据，方法返回更新和添加记录的总数量；此外，输出参数insertCounter返回添加的记录数量，updateCounter返回更新的记录数量。

这四个方法中，Insert()和TruncateAndInsert()方法适用于全部字段数据更新，Update()和InsertOrUpdate()方法则适用部分字段数据更新。此外，在使用这些方法时，我们还约定，操作会通过事务执行，也就是说除非数据全部成功导入，否则不会修改原有数据。

下面的代码（/app_code/data/base/tBatch.cs）定义tBatchBase类，作为tBatch接口组件的基类。

C#
public abstract class tBatchBase : tBatch
{
    public tBatchBase(tJet jet, string sTable, string sIdName)
    {
        Jet = jet;
        TableName = sTable;
        IdName = sIdName;
    }
    public tJet Jet { get; private set; }
    public string TableName { get; private set; }
    public string IdName { get; private set; }
    // 记录主键，用于判断记录是否存在，更新时必须指定
    public string[] PrimaryKey { get; set; }
    // 数据
    public DataTable Data { get; set; }
    //
    public abstract int Insert();
    public abstract int TruncateAndInsert();
    public abstract int Update();
    public abstract int InsertOrUpdate(out int insertCounter, out int updateCounter);
    // 将主键列移动到最后，只在更新时使用
    protected bool DataPreprocess()
    {
        int lastIndex = Data.Columns.Count - 1;
        for (int i = 0; i < PrimaryKey.Length; i++)
        {
            if (Data.Columns.Contains(PrimaryKey[i]))
                Data.Columns[PrimaryKey[i]].SetOrdinal(lastIndex);
            else
                return false;
        }
        return true;
    }
}

tBatchBase类中，实现了tBatch接口中的一系列属性，数据操作方法则定义为抽象方法，需要在子类中重写；构造函数需要指定Jet、TableName和IdName属性数据。

此外，DataPreprocess()方法的功能是将Data中的主键字段列移动到最后，方便生成Update等语句；如果Data中不包含指定的主键，方法会返回false。在Update()和InsertOrUpdate()方法中会调用此操作，而Insert()和TruncateAndInsert()方法不会调用此操作。

SQL Server的实现组件定义为tSqlBatch类，继承于tBatchBase类，基本定义如下（/app_code/data/sqlserver/tSqlBatch.cs）。

C#
using System;
using System.Text;
using System.Data.SqlClient;

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

构造函数中，需要指定tJet对象，表名和ID字段名，并通过继承基类的构造函数设置相关属性。此外，AddParam()方法将Data属性中指定行的数据添加到SqlCommand对象，其参数名使用@arg_<列索引>格式。

接下来是Insert()方法及相关实现，其功能是将Data属性中的数据批量导入到数据表中，并返回成功导入的记录数量。

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 (SqlConnection cnn = new SqlConnection(Jet.CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = insSql;
            using (SqlTransaction 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, "tSqlBatch.Insert()");
        return -1000;
    }
}

代码中，首先定义了GetInsertSql()方法，用于生成insert语句，其中包括Data属性中的所有字段的数据。Insert()方法中，使用事务逐一添加Data属性中的所有数据，并使用counter变量计数，当某一条记录添加失败时会退出方法并返回-1000；如果数据全部导入成功，则方法返回counter变量值，即成功添加的记录数量。

下面是TruncateAndInsert()方法的实现，其功能是清空表中的数据后，将Data属性中的数据批量导入，并返回成功导入的记录数量。

C#
public override int TruncateAndInsert()
{
    try
    {
        if (Data == null || Data.Rows.Count == 0) return -1001;
        string insSql = GetInsertSql();
        using (SqlConnection cnn = new SqlConnection(Jet.CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            using (SqlTransaction 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, "tSqlBatch.TruncateAndInsert()");
        return -1000;
    }
}

TruncateAndInsert()方法与Insert()方法实现基本一致，只在在事务中首先执行了truncate语句，用于重置数据表，方法同样会返回成功添加的记录数量。

Update()方法的功能是将Data属性中的数据按指定的主键更新到数据表中，并返回更新的记录数量，相关方法的实现如下。

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);
    }
    sb.Append(";");
    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 (SqlConnection cnn = new SqlConnection(Jet.CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = updSql;
            using (SqlTransaction 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, "tSqlBatch.Update()");
        return -1000;
    }
}

GetUpdateSql()方法的功能是生成update语句，需要注意的是，在生成update语句前需要调用DataPreprocess()方法将主键字段移动到字段序列的最后，在Update()方法中可以看到相关操作。

Update()方法中同样使用事务更新所有记录的指定数据（Data属性），并通过counter变量记录更新的记录数；当有记录更新操作失败时，方法返回-1000，否则方法返回counter变量的值。

最后是InsertOrUpdate()及相关方法的实现，其功能根据主键数据进行判断，更新数据表中已存在的记录，并添加数据表中不存在的记录，如下面的代码。

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(";");
    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 (SqlConnection cnn = new SqlConnection(Jet.CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            using (SqlTransaction 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, "tSqlBatch.InsertOrUpdate()");
        return -1000;
    }
}

代码中，GetSelectSql()方法用于创建根据主键数据查询的Select语句，返回的数据是ID字段值；在InsertOrUpdate()方法中，当select语句返回数据大于0时表示记录存在时，执行更新操作；否则表示记录不存在时，执行添加操作。

InsertOrUpdate()方法会返回更新和添加记录的总数，并通过输出参数insertCounter返回添加记录的数量，updateCounter返回修改记录的数量。

后续课程中，在数据交换操作相关内容中看到tBatch接口组件的具体应用。

豫ICP备11002860-1

公安机关备案41152102000002号

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