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; }