Question

如何实现快速的将DataTable插入到临时表

Answer 1

string selectCommand = 
dataTable.Columns.Cast<DataColumn>().Aggregate("select ", 
(current, col) => current + (col.ColumnName + ","));
            selectCommand = selectCommand.Substring(0, selectCommand.Length - 1);
            selectCommand += " from " + TableName;
DataRow[] errorRows = SaveDataTable(dataTable, TableName, commandText,
                continueUpdateOnError);

/// <summary>
        /// 保存DataTable
        /// </summary>
        /// <param name="dataTable">DataTable</param>
        /// <param name="tableName">表名</param>
        /// <param name="commandText">sql文本命名</param>
        /// <param name="continueUpdateOnError">行更新过程中遇到错误是否成异常</param>
        /// <returns>错误行集合</returns>
        public static DataRow[] SaveDataTable(DataTable dataTable, string tableName, string commandText,
            bool continueUpdateOnError)
        {
            var helper = SQLiteDBHelperService.Instanse();
            var trans = helper.BeginTrans();
            var conn = trans.Connection;
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            try
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = commandText;
                cmd.Connection = conn;
                cmd.Transaction = trans;

                var adp = helper.DBComm.CreateDataAdapter(cmd);
                adp.ContinueUpdateOnError = continueUpdateOnError;
                DbCommandBuilder cmdBuilder = helper.DBComm.CreateCommandBuilder(adp);
                cmdBuilder.DataAdapter = adp;
                cmdBuilder.QuotePrefix = "[";
                cmdBuilder.QuoteSuffix = "]";
                adp.InsertCommand = cmdBuilder.GetInsertCommand();
                adp.UpdateCommand = cmdBuilder.GetUpdateCommand();
                adp.DeleteCommand = cmdBuilder.GetDeleteCommand();
                adp.Update(dataTable);
                dataTable.AcceptChanges();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                MessageBox.Show(ex.ToString());
            }

            conn.Close();
            return dataTable.GetErrors();
        }