.Net插入大批量数据

　1、　使用SqlDataAdapter

/// <summary>
        /// 实现数据库事务，大批量新增数据
        /// </summary>
        /// <param name="dt">数据源</param>
        public static int ExecuteSqlTran(DataTable dt)
        {
            int affectRows = 0;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                    try
                    {
                        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

string insertcmd = @"
                                        DECLARE @Count INT;
                                        SELECT @Count = COUNT(*) FROM {0} WHERE {3};
                                        IF(@Count = 0)
                                        INSERT INTO {0} ({1}) VALUES ({2})";

StringBuilder columns = new StringBuilder();
StringBuilder filters = new StringBuilder();
StringBuilder paramvalues = new StringBuilder();

foreach (DataColumn dc in dt.Columns)
                        {
                            columns.Append(dc.ColumnName + ",");
                            paramvalues.Append("@" + dc.ColumnName + ",");
                            filters.AppendFormat(" {0} = @{0} AND ", dc.ColumnName);
                            SqlParameter par = new SqlParameter();
                            par.ParameterName = "@" + dc.ColumnName;
                            par.SourceColumn = dc.ColumnName;
                            command.Parameters.Add(par);
                        }
                        string column = columns.ToString().Substring(0, columns.ToString().LastIndexOf(','));
                        string paramvalue = paramvalues.ToString().Substring(0, paramvalues.ToString().LastIndexOf(','));
                        string filter = filters.ToString().Substring(0, filters.ToString().LastIndexOf("AND"));
                        command.CommandText = string.Format(insertcmd, dt.TableName, column, paramvalue, filter);
                        command.Connection = connection;
                        command.Transaction = transaction;
                        sqlDataAdapter.InsertCommand = command;

affectRows = sqlDataAdapter.Update(dt);

transaction.Commit();
                        connection.Close();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        command.Dispose();
                        connection.Close();
                    }
                }
            }
            return affectRows;
        }

巴特西

.Net插入大批量数据

最新文章

热门文章