从 .NET DataSet 生成多个 Insert 命令到 MySql

引言

此代码的目的是从数据集中的一组数据表动态构建 SQL insert 语句，并以参数化批次（数百个、数千个等）运行它们，以便将数据加载到 MySql 服务器表中，与逐行运行相比，性能大大提高。

当然，目标表必须具有与数据集填充的源系统相同的元数据。

背景

我开发此代码是为了优化和改进我们当前的集成软件核心。

该软件集成并同步来自不同 ERP 系统和不同电子商务平台的数据，因此它不断地将数据从（通常是 MS SQL Server 和 Oracle 数据库）移动到 MySQL 服务器数据库。

由于该软件不断地通过新的需求或具有不同结构的新 ERP 系统进行参数化，数据访问和目标需要新的实体，除了现有的实体之外，因此我们需要一个例程来轻松适应这些新的列、表或系统，并将这些数据批量加载到中间暂存区域，以便将其与电子商务平台同步。

Using the Code

第一步是使用数据填充一组 DataTables 并将它们添加到 DataSet 中。

因此，假设 DataTables 已经填充。

GetDataSet() 是一个函数，您可以在其中添加要加载到 MySQL 中的所有 DataTables，并在 DataSet 中返回它们。

每个 DataTable 将对应于一个完整的表定义，它是源数据库的 dataset 中填充的查询定义的列子集。

public DataSet GetDataSet()
{
    DataSet ds = new DataSet();

    DataTable dtProducts = new DataTable();
    DataTable dtCustomers = new DataTable();

    dtProducts .TableName = "Products"; // it's mandatory to add the DataTable name 
    dtCustomers .TableName = "Customers"; 

    ds.Tables.Add(dtProducts );
    ds.Tables.Add(dtCustomers );

    return ds;
}

BuildInsertQueriesByBatch() 是本文的主要函数。 它负责构建和连接来自每个 DataTable 的行。

• 第一个参数是要加载到 MySql 中的行的 DataTable。
• Batch 整数参数是我们要为每个批次加载的行数，也是此函数的关键，因此根据其值，该过程将获得更好的性能，因此如果 DataSet 有 2000 行，并且 batch 变量的值为 200... 该过程将收集这 200 行，并用一个指令插入它们，用 10 个指令插入 2000 行，从而大大减少总时间。

batch 数字必须根据一些因素进行调整，例如每行的长度、它拥有的数据类型（如果存在长字符串，则 batch 数字应更短）、目标服务器的速度等。

public string BuildInsertQueriesByBatch(DataTable dt, int batch)
{
    string sReturnSql = "";
    string sTableName = "";
    int nPosition = 0;
    int nRows = 0;
    sTableName = dt.TableName;

    sReturnSql += "TRUNCATE TABLE " + sTableName + ";";
            
    foreach (DataRow dr in dt.Rows)
    {
        sReturnSql += "INSERT INTO " + sTableName + "(";
        nPosition = 0;

        foreach (DataColumn column in dt.Columns)
        {
            if (nPosition == 0)
            {
                sReturnSql += "" + column.ColumnName + " ";
                nPosition += 1;
            }
            else
            {
                sReturnSql += "," + column.ColumnName + " ";
            }
        }

        sReturnSql += ")";
        sReturnSql += "VALUES(";
        nPosition = 0;

        foreach (DataColumn column in dt.Columns)
        {
            if (nPosition == 0)
            {
                sReturnSql += this.DefineInsertType
                (column.DataType.ToString(), dr[column.ColumnName].ToString());
                nPosition += 1;
            }
            else
            {
                sReturnSql += "," + this.DefineInsertType
                (column.DataType.ToString(), dr[column.ColumnName].ToString());
            }
        }
        sReturnSql += ");";
        nRows++;
        if (nRows == batch)
        {
            ExecuteRequest(sReturnSql);

            sReturnSql = "";
            nRows = 0;
        }
    }

    ExecuteRequest(sReturnSql);
    nRows = 0;

    return sReturnSql;
}

DefineInsertType() 函数根据 MySQL 标准将不同的数据类型格式化为正确的格式，以便将它们添加到 insert 语句中。

private object DefineInsertType(string sType, string sReceived)
{
    object oReturn;
    oReturn = null;

    sReceived = this.FormatValue(sReceived.ToString());

    if (sType.ToLower().IndexOf("int") != -1)
    {
        if (sReceived == null || sReceived.ToString() == string.Empty)
        {
            oReturn = 1;
        }
        else
        {
            oReturn = sReceived;
        }
    }
    if (sType.ToLower().IndexOf("system.byte") != -1)
    {
        if (sReceived == null || sReceived.ToString() == string.Empty)
        {
            oReturn = 1;
        }
        else
        {
            oReturn = sReceived;
        }
    }
    if (sType.ToLower().IndexOf("char") != -1)
    {
        oReturn = "'" + sReceived + "'";
    }
    if (sType.ToLower().IndexOf("string") != -1)
    {
        oReturn = "'" + sReceived + "'";
    }
    if (sType.ToLower().IndexOf("datetime") != -1)
    {
        oReturn = "'" + sReceived + "'";
    }
    if (sType.ToLower().IndexOf("decimal") != -1)
    {
        oReturn = sReceived.ToString().Replace(",", ".");
    }
    if (sType.ToLower().IndexOf("uniqueidentifier") != -1)
    {              
        oReturn = "'" + sReceived.ToString().Replace(".", ",") + "'";              
    }            
    if (sType.ToLower().IndexOf("image") != -1)
    {
        oReturn = "'" + sReceived + "'";
    }
    if (sType.ToLower().IndexOf("money") != -1)
    {
        if (sReceived == null || sReceived.ToString() == string.Empty)
        {
            oReturn = 0;
        }
        else
        {
            oReturn = sReceived;
        }
    }
    if (sType.ToLower().IndexOf("text") != -1)
    {
        if (sReceived == null || sReceived.ToString() == string.Empty)
        {
            oReturn = "''";
        }
        else
        {
            oReturn = "'" + sReceived.ToString().Replace("'", "''") + "'";
        }
    }
    if (sType.ToLower().IndexOf("bool") != -1)
    {
        oReturn = "'" + sReceived + "'";
    }
    if (sType.ToLower().IndexOf("bit") != -1)
    {
        oReturn = "'" + sReceived + "'";
    }
    if (sType.ToLower().IndexOf("uniqueidentifier") != -1)
    {
        oReturn = "'" + sReceived + "'";
    }
    if (string.IsNullOrEmpty(sType))
    {
        oReturn = "'" + sReceived + "'";
    }

    return oReturn;
}

ExecuteRequest() 函数执行 batch var 值收集的 insert 语句数。

要运行此函数，我们使用此链接上可用的 MySQL 的 Connector/Net 驱动程序

• https://dev.mysqlserver.cn/downloads/connector/net/6.0.html

public int ExecuteRequest(string sRequest)
{            
    MySql.Data.MySqlClient.MySqlConnection oMySqlConnection;
    MySql.Data.MySqlClient.MySqlCommand oSqlCommand;
    int i = -1;

    oSqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
    oMySqlConnection = new MySql.Data.MySqlClient.MySqlConnection("MySqlConnectionString");

    try
    {
        oSqlCommand.Connection = oMySqlConnection;
        oSqlCommand.CommandType = System.Data.CommandType.Text;
        oSqlCommand.CommandText = sRequest;

        if (oMySqlConnection.State == System.Data.ConnectionState.Closed)
            oMySqlConnection.Open();

        oSqlCommand.ExecuteNonQuery();
        oSqlCommand.Connection.Close();
        oMySqlConnection.Close();
        i = 1;
    }
    catch (System.Data.SqlClient.SqlException ex)
    {
        i = -1;
        oMySqlConnection.Close();
    }
    return i;
}

因此，拥有所有这些所需的函数以及生成多个 insert 语句的逻辑，我们只需要使用类似这样的东西调用主函数 BuildInsertQueriesByBatch()

public void ExecuteExportNew()
{
    DataSet ds = new DataSet();

    ds = GetDataSet();

    BuildInsertQueriesByBatch(ds.Tables["Products"], 200);
    BuildInsertQueriesByBatch(ds.Tables["Customers"], 100);
}

我们两次调用 BuildInsertQueriesByBatch() 函数，每个 DataTable 调用一次。 如果我们有更多，我们将需要为 DataSet 中的每个表添加一个指令，其中包含 DataTable 名称和所需的 batch 数字值。

Using the Code

只需将函数包含在您的程序中并调用这些函数即可。

历史

• 2016 年 7 月 11 日：初始版本