// BulkCopy("dbo.T_Benutzer", dt) public override bool BulkCopy(string tableSchema, string tableName, System.Data.DataTable dt, bool bWithDelete) { try { string sanitizedTableName = this.QuoteObjectWhereNecessary(tableName); // Ensure table is empty - and throw on foreign-key if (bWithDelete) { this.Execute("DELETE FROM " + sanitizedTableName); } System.Collections.Generic.List <string> lsComputedColumns = GetComputedColumnNames(tableSchema, tableName); // http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions.aspx System.Data.SqlClient.SqlBulkCopyOptions bcoOptions = //System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints | System.Data.SqlClient.SqlBulkCopyOptions.KeepNulls | System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity; // http://stackoverflow.com/questions/6651809/sqlbulkcopy-insert-with-identity-column // http://msdn.microsoft.com/en-us/library/ms186335.aspx System.Data.SqlClient.SqlBulkCopy BulkCopyInstance = new System.Data.SqlClient.SqlBulkCopy(this.m_ConnectionString.ConnectionString, bcoOptions); foreach (System.Data.DataColumn dc in dt.Columns) { // The column "foo" cannot be modified because it is either a computed column or... if (MyExtensionMethods.Contains(lsComputedColumns, dc.ColumnName, System.StringComparer.InvariantCultureIgnoreCase)) { continue; } BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, "[" + dc.ColumnName.Replace("]", "]]") + "]"); } BulkCopyInstance.DestinationTableName = sanitizedTableName; /* * string strSQL = "INSERT INTO " + BulkCopyInstance.DestinationTableName + Environment.NewLine + "(" + Environment.NewLine; * * * for(int i=0; i < dt.Columns.Count; ++i) * { * if(i==0) * strSQL += " [" + dt.Columns[i].ColumnName + "]" + Environment.NewLine; * else * strSQL += " ,[" + dt.Columns[i].ColumnName + "]" + Environment.NewLine; * //BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); * } * strSQL += ") " + Environment.NewLine + "Values "+ Environment.NewLine + "(" + Environment.NewLine; * * for (int i = 0; i < dt.Columns.Count; ++i) * { * if (i == 0) * strSQL += " @parameter" + i.ToString() + Environment.NewLine; * else * strSQL += " ,@parameter" + i.ToString() + Environment.NewLine; * //BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); * } * * strSQL += "); "; * * // http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622244 * System.Data.IDbCommand idbc = this.CreateCommand(strSQL); * * for (int i = 0; i < dt.Rows.Count; ++i) * { * * for (int j = 0; j < dt.Columns.Count; ++j) * { * this.AddParameter(idbc, "parameter" + j.ToString(), dt.Rows[i][j]); * } * * //this.Execute(idbc); * this.ExecuteWithoutTransaction(idbc); * idbc.Parameters.Clear(); * } * * //MsgBox(strSQL); */ BulkCopyInstance.WriteToServer(dt); BulkCopyInstance.Close(); BulkCopyInstance = null; } catch (System.Exception ex) { if (Log("cMS_SQL_specific.BulkCopy", ex, "BulkCopy: Copy dt to " + tableName)) { throw; } //COR.Logging.WriteLogFile("FEHLER", "Ausnahme in COR.SQL.MSSQL.BulkCopy"); //COR.Logging.WriteLogFile("FEHLER", ex.Message); //COR.Logging.WriteLogFile("FEHLER", "-----------------------------------------------------------------"); //COR.Logging.WriteLogFile("FEHLER", ex.StackTrace.ToString()); //Console.WriteLine(ex.Message.ToString() + Environment.NewLine + ex.StackTrace.ToString()); //MsgBoxStyle.Critical, "FEHLER ..."); //COR.Logging.WriteLogFile("MELDUNG", "-----------------------------------------------------------------"); } return(false); } // End Function BulkCopy
// BulkCopy("dbo.T_Benutzer", dt) public override bool BulkCopy(string strDestinationTable, System.Data.DataTable dt, bool bWithDelete) { try { strDestinationTable = "[" + strDestinationTable + "]"; if (bWithDelete) { this.Execute("DELETE FROM " + strDestinationTable.Replace("'", "''")); } // http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions.aspx System.Data.SqlClient.SqlBulkCopyOptions bcoOptions = //System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints | System.Data.SqlClient.SqlBulkCopyOptions.KeepNulls | System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity; // http://stackoverflow.com/questions/6651809/sqlbulkcopy-insert-with-identity-column // http://msdn.microsoft.com/en-us/library/ms186335.aspx System.Data.SqlClient.SqlBulkCopy BulkCopyInstance = new System.Data.SqlClient.SqlBulkCopy(this.m_ConnectionString.ConnectionString, bcoOptions); foreach (System.Data.DataColumn dc in dt.Columns) { BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, "[" + dc.ColumnName + "]"); } BulkCopyInstance.DestinationTableName = strDestinationTable; /* * string strSQL = "INSERT INTO " + BulkCopyInstance.DestinationTableName + Environment.NewLine + "(" + Environment.NewLine; * * * for(int i=0; i < dt.Columns.Count; ++i) * { * if(i==0) * strSQL += " [" + dt.Columns[i].ColumnName + "]" + Environment.NewLine; * else * strSQL += " ,[" + dt.Columns[i].ColumnName + "]" + Environment.NewLine; * //BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); * } * strSQL += ") " + Environment.NewLine + "Values "+ Environment.NewLine + "(" + Environment.NewLine; * * for (int i = 0; i < dt.Columns.Count; ++i) * { * if (i == 0) * strSQL += " @parameter" + i.ToString() + Environment.NewLine; * else * strSQL += " ,@parameter" + i.ToString() + Environment.NewLine; * //BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); * } * * strSQL += "); "; * * // http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622244 * System.Data.IDbCommand idbc = this.CreateCommand(strSQL); * * for (int i = 0; i < dt.Rows.Count; ++i) * { * * for (int j = 0; j < dt.Columns.Count; ++j) * { * this.AddParameter(idbc, "parameter" + j.ToString(), dt.Rows[i][j]); * } * * //this.Execute(idbc); * this.ExecuteWithoutTransaction(idbc); * idbc.Parameters.Clear(); * } * * //MsgBox(strSQL); */ BulkCopyInstance.WriteToServer(dt); BulkCopyInstance.Close(); BulkCopyInstance = null; } catch (System.Exception ex) { if (Log("cSQLite_specific.cs ==> BulkCopy", ex, "BulkCopy: Copy dt to " + strDestinationTable)) { throw; } //COR.Logging.WriteLogFile("FEHLER", "Ausnahme in COR.SQL.MSSQL.BulkCopy"); //COR.Logging.WriteLogFile("FEHLER", ex.Message); //COR.Logging.WriteLogFile("FEHLER", "-----------------------------------------------------------------"); //COR.Logging.WriteLogFile("FEHLER", ex.StackTrace.ToString()); //Console.WriteLine(ex.Message.ToString() + Environment.NewLine + ex.StackTrace.ToString()); //MsgBoxStyle.Critical, "FEHLER ..."); //COR.Logging.WriteLogFile("MELDUNG", "-----------------------------------------------------------------"); } return(false); } // End Function BulkCopy