代码之家 › 专栏 › 技术社区 › Arpit Gupta

使用C#高效地在MySQL数据库表中插入数千行

mysql c#

Arpit Gupta · 技术社区 · 7 年前

列表中的每个词典代表一行。每行,字典 key 包含 ,和 value 列数据

我取得了这样的成就-

public long InsertMultiple(string TableName, List<Dictionary<string, string>> listMultipleRows)
{
    try
    {
        string columnNames = null;
        StringBuilder sCommand = new StringBuilder();
        List<string> Rows = new List<string>();
        int columnLength = listMultipleRows.First().Select(x => x.Key).ToArray().Length;

        //Fetching required column names.
        if (listMultipleRows.Count > 0)
            columnNames = string.Join(", ", listMultipleRows.First().Select(x => x.Key).ToArray());

        //Preparing command
        if (columnNames != null)
            sCommand = new StringBuilder("INSERT INTO " + TableName + " (" + columnNames + ") VALUES ");

        //Preparing column format like - '{0}','{1}'......
        string columnFormat = "(";
        for (int i = 0; i < columnLength; i++)
        {
            if (i != 0)
                columnFormat = columnFormat + ",";
            columnFormat = columnFormat + "'{" + i + "}'";
        }
        columnFormat = columnFormat + ")";                

        //Appending each row values. Actual rows which needs to be inserted.
        foreach (Dictionary<string, string> row in listMultipleRows)
        {
            Rows.Add(string.Format(columnFormat, row.Select(x => MySql.Data.MySqlClient.MySqlHelper.EscapeString(x.Value)).ToArray()));
        }

        sCommand.Append(string.Join(",", Rows));
        sCommand.Append(";");

        MySqlCommand Comm = new MySqlCommand();
        Comm.CommandText = sCommand.ToString();
        Comm.Connection = m_Conn;
        //One shot insertion operation
        return Convert.ToInt64(ExecuteScalar(Comm));  
    }
    catch (Exception Ex)
    {
        return 0;
    }
}