public override bool InsertBulkRows(string pSelectSQL, System.Data.Common.DbDataReader pDataReader, SetGadgetStatusHandler pStatusDelegate = null, CheckForCancellationHandler pCancellationDelegate = null)
{
bool result = false;
System.Data.SqlClient.SqlConnection Conn = null;
System.Data.SqlClient.SqlDataAdapter Adapter = null;
System.Data.SqlClient.SqlCommandBuilder builderSQL = null;
System.Data.Common.DbCommand cmdSqL = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
try
{
StringBuilder InsertSQL;
StringBuilder ValueSQL;
Conn = new System.Data.SqlClient.SqlConnection(ConnectionString);
Adapter = new System.Data.SqlClient.SqlDataAdapter(pSelectSQL, Conn);
Adapter.FillSchema(dataSet, SchemaType.Source);
builderSQL = new System.Data.SqlClient.SqlCommandBuilder(Adapter);
Conn.Open();
cmdSqL = Conn.CreateCommand();
cmdSqL.CommandTimeout = 1500;
int rowCount = 0;
int skippedRows = 0;
int totalRows = 0;
if (pStatusDelegate != null && dataSet.Tables.Count > 0)
{
totalRows = dataSet.Tables[0].Rows.Count;
}
while (pDataReader.Read())
{
cmdSqL = builderSQL.GetInsertCommand(true);
InsertSQL = new StringBuilder();
ValueSQL = new StringBuilder();
InsertSQL.Append("Insert Into ");
InsertSQL.Append(pSelectSQL.Replace("Select * From ", ""));
InsertSQL.Append(" (");
ValueSQL.Append(" values (");
List<System.Data.SqlClient.SqlParameter> ParameterList = new List<SqlParameter>();
foreach (System.Data.SqlClient.SqlParameter param in cmdSqL.Parameters)
{
string FieldName = param.SourceColumn;
InsertSQL.Append("[");
InsertSQL.Append(FieldName);
InsertSQL.Append("],");
ValueSQL.Append(param.ParameterName);
ValueSQL.Append(",");
param.Value = pDataReader[FieldName];
ParameterList.Add(param);
}
InsertSQL.Length = InsertSQL.Length - 1;
ValueSQL.Length = ValueSQL.Length - 1;
InsertSQL.Append(")");
ValueSQL.Append(")");
InsertSQL.Append(ValueSQL);
cmdSqL = null;
cmdSqL = Conn.CreateCommand();
cmdSqL.CommandText = InsertSQL.ToString();
foreach (System.Data.SqlClient.SqlParameter param in ParameterList)
{
DbParameter p2 = cmdSqL.CreateParameter();
p2.DbType = param.DbType;
p2.Value = pDataReader[param.SourceColumn];
p2.ParameterName = param.ParameterName;
cmdSqL.Parameters.Add(p2);
}
try
{
cmdSqL.ExecuteNonQuery();
rowCount++;
}
catch (Exception ex)
{
skippedRows++;
continue;
}
if (pStatusDelegate != null)
{
pStatusDelegate.Invoke(string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowCount.ToString(), totalRows.ToString()), (double)rowCount);
}
if (pCancellationDelegate != null && pCancellationDelegate.Invoke())
{
pStatusDelegate.Invoke(string.Format(SharedStrings.DASHBOARD_EXPORT_CANCELLED, rowCount.ToString()));
break;
}
}
if (pStatusDelegate != null)
{
pStatusDelegate.Invoke(string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, rowCount.ToString()));
}
}
catch (System.Exception ex)
{
Logger.Log(DateTime.Now + ": " + ex.Message);
}
finally
{
if (Conn != null)
{
Conn.Close();
}
}
result = true;
return result;
}
public override bool InsertBulkRows(string pSelectSQL, System.Data.Common.DbDataReader pDataReader, SetGadgetStatusHandler pStatusDelegate = null, CheckForCancellationHandler pCancellationDelegate = null)
{
bool result = false;
System.Data.OleDb.OleDbConnection ConnOle = null;
System.Data.OleDb.OleDbDataAdapter AdapterOle = null;
System.Data.OleDb.OleDbCommandBuilder builderOLE = null;
System.Data.Common.DbCommand cmdOle = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
try
{
StringBuilder InsertSQL;
StringBuilder ValueSQL;
ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString.Replace(";IMEX=1", ""));
AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle);
AdapterOle.FillSchema(dataSet, SchemaType.Source);
AdapterOle.Fill(Temp);
builderOLE = new System.Data.OleDb.OleDbCommandBuilder();
builderOLE.DataAdapter = AdapterOle;
ConnOle.Open();
cmdOle = ConnOle.CreateCommand();
cmdOle.CommandTimeout = 1500;
int rowCount = 0;
int skippedRows = 0;
int totalRows = 0;
int truncatedCellCount = 0;
bool numericFieldOverflow = false;
while (pDataReader.Read())
{
cmdOle = builderOLE.GetInsertCommand();
InsertSQL = new StringBuilder();
ValueSQL = new StringBuilder();
InsertSQL.Append("Insert Into ");
InsertSQL.Append(pSelectSQL.Replace("Select * From ", ""));
InsertSQL.Append(" (");
ValueSQL.Append(" values (");
int CheckLength = 0;
List<OleDbParameter> ParameterList = new List<OleDbParameter>();
foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters)
{
string FieldName = param.SourceColumn;
InsertSQL.Append("[");
InsertSQL.Append(FieldName);
InsertSQL.Append("],");
ValueSQL.Append(param.ParameterName);
ValueSQL.Append(",");
try
{
param.Value = pDataReader[FieldName];
}
catch (Exception ex)
{
param.Value = DBNull.Value;
}
ParameterList.Add(param);
}
InsertSQL.Length = InsertSQL.Length - 1;
ValueSQL.Length = ValueSQL.Length - 1;
InsertSQL.Append(")");
ValueSQL.Append(")");
InsertSQL.Append(ValueSQL);
cmdOle = null;
cmdOle = ConnOle.CreateCommand();
cmdOle.CommandText = InsertSQL.ToString();
foreach (OleDbParameter param in ParameterList)
{
DbParameter p2 = cmdOle.CreateParameter();
p2.DbType = param.DbType;
try
{
p2.Value = pDataReader[param.SourceColumn];
CheckLength = p2.Value.ToString().Length;
if (CheckLength > 255)
{
p2.Value = p2.Value.ToString().Substring(0, 255);
truncatedCellCount++;
}
}
catch (Exception ex)
{
p2.Value = DBNull.Value;
}
p2.ParameterName = param.ParameterName;
cmdOle.Parameters.Add(p2);
}
try
{
cmdOle.ExecuteNonQuery();
rowCount++;
}
catch (OleDbException ex)
{
skippedRows++;
if (ex.Message.ToLower().Contains("numeric field overflow"))
{
numericFieldOverflow = true;
}
continue;
}
if (pStatusDelegate != null)
{
totalRows = rowCount + skippedRows;
string messageString = String.Empty;
if (skippedRows == 0)
{
messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowCount.ToString(), totalRows.ToString());
}
else
{
messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS_INCLUDE_SKIPPED, rowCount.ToString(), totalRows.ToString(), skippedRows.ToString());
}
pStatusDelegate.Invoke(messageString, (double)rowCount);
}
if (pCancellationDelegate != null && pCancellationDelegate.Invoke())
{
pStatusDelegate.Invoke(string.Format(SharedStrings.DASHBOARD_EXPORT_CANCELLED, rowCount.ToString()));
break;
}
}
if (pStatusDelegate != null)
{
totalRows = rowCount + skippedRows;
string messageString = String.Empty;
if (skippedRows == 0)
{
messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, totalRows.ToString());
}
else if (skippedRows > 0 && !numericFieldOverflow)
{
messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS_SOME_SKIPPED, rowCount.ToString(), totalRows.ToString(), skippedRows.ToString());
}
else if (skippedRows > 0 && numericFieldOverflow)
{
messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS_SOME_SKIPPED_NUMERIC_FIELD_OVERFLOW, rowCount.ToString(), totalRows.ToString(), skippedRows.ToString());
}
if (truncatedCellCount > 0)
{
messageString = messageString + string.Format("; {0} cells truncated to 255 maximum character limit.", truncatedCellCount);
}
pStatusDelegate.Invoke(messageString);
}
}
//catch (System.Exception ex)
//{
// Logger.Log(DateTime.Now + ": " + ex.Message);
//}
finally
{
if (ConnOle != null)
{
ConnOle.Close();
ConnOle.Dispose();
}
if (AdapterOle != null)
{
AdapterOle.Dispose();
}
if (builderOLE != null)
{
builderOLE.Dispose();
}
if (cmdOle != null)
{
cmdOle.Dispose();
}
}
result = true;
return result;
}
