// 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
