网上找的代码,还没经过验证
/// /// Bulk inserts multiple rows to SQL /// /// The name of the table to insert into /// The name of the primary key column of the table /// True if the primary key is automatically allocated by the DB /// The POCO objects that specifies the column values to be inserted /// The number of POCOS to be grouped together for each database rounddtrip public void BulkInsert(string tableName, string primaryKeyName, bool autoIncrement, IEnumerable pocos, int batchSize = 25) { try { OpenSharedConnection(); try { using (var cmd = CreateCommand(_sharedConnection, "")) { var pd = PocoData.ForObject(pocos.First(), primaryKeyName); // Create list of columnnames only once var names = new List
(); foreach (var i in pd.Columns) { // Don't insert result columns if (i.Value.ResultColumn) continue; // Don't insert the primary key (except under oracle where we need bring in the next sequence value) if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, true) == 0) { // Setup auto increment expression string autoIncExpression = _dbType.GetAutoIncrementExpression(pd.TableInfo); if (autoIncExpression != null) { names.Add(i.Key); } continue; } names.Add(_dbType.EscapeSqlIdentifier(i.Key)); } var namesArray = names.ToArray(); var values = new List
(); int count = 0; do { cmd.CommandText = ""; cmd.Parameters.Clear(); var index = 0; foreach (var poco in pocos.Skip(count).Take(batchSize)) { values.Clear(); foreach (var i in pd.Columns) { // Don't insert result columns if (i.Value.ResultColumn) continue; // Don't insert the primary key (except under oracle where we need bring in the next sequence value) if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, true) == 0) { // Setup auto increment expression string autoIncExpression = _dbType.GetAutoIncrementExpression(pd.TableInfo); if (autoIncExpression != null) { values.Add(autoIncExpression); } continue; } values.Add(string.Format("{0}{1}", _paramPrefix, index++)); AddParam(cmd, i.Value.GetValue(poco), i.Value.PropertyInfo); } string outputClause = String.Empty; if (autoIncrement) { outputClause = _dbType.GetInsertOutputClause(primaryKeyName); } cmd.CommandText += string.Format("INSERT INTO {0} ({1}){2} VALUES ({3})", _dbType.EscapeTableName(tableName), string.Join(",", namesArray), outputClause, string.Join(",", values.ToArray())); } // Are we done? if (cmd.CommandText == "") break; count += batchSize; DoPreExecute(cmd); cmd.ExecuteNonQuery(); OnExecutedCommand(cmd); } while (true); } } finally { CloseSharedConnection(); } } catch (Exception x) { if (OnException(x)) throw; } } ///
/// Performs a SQL Bulk Insert /// ///
The POCO objects that specifies the column values to be inserted ///
The number of POCOS to be grouped together for each database rounddtrip public void BulkInsert(IEnumerable
pocos, int batchSize = 25) { if (!pocos.Any()) return; var pd = PocoData.ForType(pocos.First().GetType()); BulkInsert(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, pd.TableInfo.AutoIncrement, pocos); }
摘自:http://stackoverflow.com/questions/6595105/bulk-insert-update-with-petapoco/14479073