private void PopulateSheet(string filePath, TargetTableSheet sheet)
{
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
ExcelHelper.InitSheet(filePath, sheet);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
Logger.Debug("Openning connction to " + filePath);
oleConn.Open();
var sql = string.Format("EXEC spX_FXDKTB_D '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var reader = dao.ExecuteReader(sql);
int rowCount = 0;
while (reader.Read()) {
rowCount++;
sql = GetInsertSql(reader, sheet);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
}
private void PopulateSheet(string filePath, TargetTableSheet sheet)
{
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
var sql = string.Format("EXEC spC_DQDKQK_M '{0}', '{1}', '{2}'", this.AsOfDate.ToString("yyyyMMdd")
, sheet.Index == 1 ? "ImportPublic" : "ImportPrivate"
, sheet.Index == 1 ? PublicColumns : PrivateColumns);
var dao = new SqlDbHelper();
Logger.Debug("Running: " + sql);
var reader = dao.ExecuteReader(sql);
var columnNames = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columnNames.Add(reader.GetName(i));
}
int rowCount = 0;
ExcelHelper.InitSheet(filePath, sheet, columnNames);
Logger.Debug("Openning connction to " + filePath);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
oleConn.Open();
while (reader.Read())
{
rowCount++;
sql = GetInsertSql(reader, sheet, columnNames);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
}
private void PopulateSheet(string filePath, TargetTableSheet sheet)
{
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
var sql = string.Format("EXEC spC_DQDKQK_M '{0}', '{1}', '{2}'", this.AsOfDate.ToString("yyyyMMdd")
, sheet.Index == 1 ? "ImportPublic" : "ImportPrivate"
, sheet.Index == 1 ? PublicColumns : PrivateColumns);
var dao = new SqlDbHelper();
Logger.Debug("Running: " + sql);
var reader = dao.ExecuteReader(sql);
var columnNames = new List<string>();
for (int i = 0; i < reader.FieldCount; i++) {
columnNames.Add(reader.GetName(i));
}
int rowCount = 0;
ExcelHelper.InitSheet(filePath, sheet, columnNames);
Logger.Debug("Openning connction to " + filePath);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
oleConn.Open();
while (reader.Read()) {
rowCount++;
sql = GetInsertSql(reader, sheet, columnNames);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
}
private void PopulateSheet(string filePath, TargetTableSheet sheet)
{
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
var columnNames = this.Columns.Select(x => x.MappingName).ToList();
ExcelHelper.InitSheet(filePath, sheet, columnNames);
Logger.Debug("Openning connction to " + filePath);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
oleConn.Open();
var sql = string.Format("EXEC {0} '{1}', '{2}', '{3}', '{4}'", this.SPName, GetSheetSuffix(sheet), this.AsOfDate.ToString("yyyyMMdd"), this.AsOfDate2.ToString("yyyyMMdd"), DbColumnNames);
var dao = new SqlDbHelper();
Logger.Debug("Running: " + sql);
var reader = dao.ExecuteReader(sql);
int rowCount = 0;
while (reader.Read())
{
rowCount++;
sql = GetInsertSql(reader, sheet, columnNames);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate, this.AsOfDate2);
}