internal static void BuildLogger(ref Serilog.ILogger _perfLogger, ref Serilog.ILogger _usageLogger, ref Serilog.ILogger _errorLogger, ref Serilog.ILogger _diagnosticLogger, McsLoggingSettings _settings)
{
_perfLogger = new LoggerConfiguration()
.WriteTo.File(path: $"{_settings.LogFolderLocation}\\perf-{DateTime.Now.ToString("MMddyyyy")}.txt")
.WriteTo.MSSqlServer(_settings.LogConnection, "PerfLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: _settings.LogBatchSize)
.CreateLogger();
_usageLogger = new LoggerConfiguration()
.WriteTo.File(path: $"{_settings.LogFolderLocation}\\usage-{DateTime.Now.ToString("MMddyyyy")}.txt")
.WriteTo.MSSqlServer(_settings.LogConnection, "UsageLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: _settings.LogBatchSize)
.CreateLogger();
_errorLogger = new LoggerConfiguration()
.WriteTo.File(path: $"{_settings.LogFolderLocation}\\error-{DateTime.Now.ToString("MMddyyyy")}.txt")
.WriteTo.MSSqlServer(_settings.LogConnection, "ErrorLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: _settings.LogBatchSize)
.CreateLogger();
_diagnosticLogger = new LoggerConfiguration()
.WriteTo.File(path: $"{_settings.LogFolderLocation}\\diagnostic-{DateTime.Now.ToString("MMddyyyy")}.txt")
.WriteTo.MSSqlServer(_settings.LogConnection, "DiagnosticLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: _settings.LogBatchSize)
.CreateLogger();
}
private List <ColumnInfo> PrepareSelectSql()
{
var returned = SqlColumns.Where(a => a.IsDatabaseGenerated).ToList();
if (!returned.Any())
{
return(null);
}
Separator = " AND ";
SqlText.Append("select " + string.Join(",", returned.Select(q => Encode(q.ColumnName))));
SqlText.Append(" from " + TableName + " WHERE @@ROWCOUNT=1");
if (IdentityColumn != null)
{
AddSeparator(Separator);
SqlText.Append(Encode(IdentityColumn.ColumnName) + "=scope_identity();");
}
else
{
for (var index = 0; index < SqlColumns.Length; index++)
{
var col = SqlColumns[index];
if (!col.IsInPrimaryKey)
{
continue;
}
var pn = AddPropertyValue(index);
AddSeparator(Separator);
SqlText.Append(Encode(col.ColumnName) + "=" + pn);
}
}
return(returned);
}
private IReadOnlyList <ColumnInfo> PrepareUpdateSql(bool skipSelect)
{
var whereSql = PrepareWhereSql();
SqlText.AppendFormat("Update {0} set ", TableName);
Separator = null;
for (var index = 0; index < SqlColumns.Count; index++)
{
var columnInfo = SqlColumns[index];
if (columnInfo.IsInPrimaryKey || columnInfo.IsDatabaseGenerated)
{
continue;
}
AddSeparator(", ");
SqlText.Append(Encode(columnInfo.ColumnName) + "=@p" + ParameterValues.Count);
ParameterValues.Add(columnInfo.ValueReader.ReadPropertyValue(Entity));
}
SqlText.Append(whereSql);
if (skipSelect)
{
return(new ColumnInfo[0]);
}
var returned = SqlColumns.Where(a => a.IsDatabaseGenerated && !a.IsInPrimaryKey).ToList();
if (!returned.Any())
{
return(returned);
}
SqlText.Append("; select " + string.Join(",", returned.Select(q => Encode(q.ColumnName))));
SqlText.Append(" from " + TableName);
SqlText.Append(whereSql);
return(returned);
}
internal static void BuildLogger(ref Serilog.ILogger _perfLogger, ref Serilog.ILogger _usageLogger, ref Serilog.ILogger _errorLogger, ref Serilog.ILogger _diagnosticLogger, string logLocation, string connectionString, int batchPostingLimit = 1)
{
_perfLogger = new LoggerConfiguration()
.WriteTo.File(path: $"{logLocation}\\perf-{DateTime.Now:MMddyyyy}.txt")
.WriteTo.MSSqlServer(connectionString, "PerfLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: batchPostingLimit)
.CreateLogger();
_usageLogger = new LoggerConfiguration()
.WriteTo.File(path: $"{logLocation}\\usage-{DateTime.Now:MMddyyyy}.txt")
.WriteTo.MSSqlServer(connectionString, "UsageLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: batchPostingLimit)
.CreateLogger();
_errorLogger = new LoggerConfiguration()
.WriteTo.File(path: $"{logLocation}\\error-{DateTime.Now:MMddyyyy}.txt")
.WriteTo.MSSqlServer(connectionString, "ErrorLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: batchPostingLimit)
.CreateLogger();
_diagnosticLogger = new LoggerConfiguration()
.WriteTo.File(path: $"{logLocation}\\diagnostic-{DateTime.Now:MMddyyyy}.txt")
.WriteTo.MSSqlServer(connectionString, "DiagnosticLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: batchPostingLimit)
.CreateLogger();
}
/// <summary>
/// Get all columns for selected table.
/// If there is a primary key it's mark as one
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void lstTableNames_SelectedIndexChanged(object sender, EventArgs e)
{
var columnsForSelectedTable = new SqlColumns(DatabaseNameListBox.Text, DatabaseTableNamesListBox.Text);
var results = columnsForSelectedTable.GetColumns();
SelectedTableColumnCheckedListBox.Items.Clear();
if (results.Count <= 0)
{
return;
}
OrderByComboBox.Items.Clear();
OrderByComboBox.Items.Add("None");
foreach (var column in results)
{
SelectedTableColumnCheckedListBox.Items.Add(column);
if (column.IsIdentity)
{
continue;
}
OrderByComboBox.Items.Add(column.ColumnName);
}
OrderByComboBox.SelectedIndex = 0;
}
protected string GetColumnsInString(bool withOrder = false)
{
if (withOrder)
{
return(string.Join(", ", SqlColumns));
}
return(string.Join(", ", SqlColumns.Select(cao => cao.SqlColumn.Name)));
}
protected bool equalDocumentHeaderDescribe(string tableName)
{
foreach (var oneCcol in OneCv77DocumentsHeaderColumns.Where(c => c.tableName.Equals(tableName)))
{
if (!SqlColumns.Any(c => c.tableName.Equals(tableName) && c.columnName.Equals(oneCcol.columnName)))
{
return(false);
}
}
return(true);
}
protected bool equalCatalogTableDescribe(string tableName)
{
foreach (var oneCcol in OneCv77CatalogColumns.Where(c => c.tableName.Equals(tableName)))
{
if (!SqlColumns.Any(c => c.tableName.Equals(tableName) && c.columnName.Equals(oneCcol.columnName)))
{
return(false);
}
}
return(true);
}
internal static void BuildLogger(ref Serilog.ILogger _perfLogger, ref Serilog.ILogger _usageLogger, ref Serilog.ILogger _errorLogger, ref Serilog.ILogger _diagnosticLogger, McsLoggingSettings _settings)
{
_perfLogger = new LoggerConfiguration()
.WriteTo.MSSqlServer(_settings.LogConnection, "PerfLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: _settings.LogBatchSize)
.CreateLogger();
_usageLogger = new LoggerConfiguration()
.WriteTo.MSSqlServer(_settings.LogConnection, "UsageLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: _settings.LogBatchSize)
.CreateLogger();
_errorLogger = new LoggerConfiguration()
.WriteTo.MSSqlServer(_settings.LogConnection, "ErrorLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: _settings.LogBatchSize)
.CreateLogger();
_diagnosticLogger = new LoggerConfiguration()
.WriteTo.MSSqlServer(_settings.LogConnection, "DiagnosticLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: _settings.LogBatchSize)
.CreateLogger();
}
internal static void BuildLogger(ref Serilog.ILogger _perfLogger, ref Serilog.ILogger _usageLogger, ref Serilog.ILogger _errorLogger, ref Serilog.ILogger _diagnosticLogger, string connectionString, int batchPostingLimit = 1)
{
_perfLogger = new LoggerConfiguration()
.WriteTo.MSSqlServer(connectionString, "PerfLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: batchPostingLimit)
.CreateLogger();
_usageLogger = new LoggerConfiguration()
.WriteTo.MSSqlServer(connectionString, "UsageLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: batchPostingLimit)
.CreateLogger();
_errorLogger = new LoggerConfiguration()
.WriteTo.MSSqlServer(connectionString, "ErrorLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: batchPostingLimit)
.CreateLogger();
_diagnosticLogger = new LoggerConfiguration()
.WriteTo.MSSqlServer(connectionString, "DiagnosticLogs", autoCreateSqlTable: true,
columnOptions: SqlColumns.GetSqlColumnOptions(), batchPostingLimit: batchPostingLimit)
.CreateLogger();
}
public override bool Sync()
{
try
{
foreach (var tableName in OneCv77CatalogColumns.Select(s => s.tableName).Distinct())
{
_sqlAdaptor.recreateTable(tableName, SqlColumns.Where(w => w.tableName.Equals(tableName)).ToList());
int page = 0;
var tableColumns = _comClient1Cv77Adatpor.getCatalogTableColumns(tableName);
while (true)
{
DataTable dt = _comClient1Cv77Adatpor.getCatalogDataTable(tableName, tableColumns, page);
_sqlAdaptor.bulkInsertData(tableName, dt);
if (dt.Rows.Count == 0)
{
break;
}
page++;
}
_sqlAdaptor.dropTable(string.Format("{0}_ok", tableName));
_sqlAdaptor.renameTable(tableName, string.Format("{0}_ok", tableName));
}
string tableNameData = string.Empty;
foreach (var tableName in OneCv77DocumentsHeaderColumns.Select(s => s.tableName).Distinct())
{
_sqlAdaptor.recreateTable(tableName, SqlColumns.Where(w => w.tableName.Equals(tableName)).ToList());
DateTime date = new DateTime(2003, 01, 01);
IEnumerable <TableColumn> dataDocTableExist = OneCv77DocumentsColumns.Where(
s =>
comClient1Cv77Adatpor.cleanSuffix(s.tableName) ==
comClient1Cv77Adatpor.cleanSuffix(tableName));
if (dataDocTableExist.Count() > 0)
{
tableNameData = dataDocTableExist.First().tableName;
_sqlAdaptor.recreateTable(tableNameData, SqlColumns.Where(w => w.tableName.Equals(tableNameData)).ToList());
}
while (true)
{
List <string> dataRows = _comClient1Cv77Adatpor.GetDocumentData(tableName, date);
if (dataRows.Count > 0)
{
_sqlAdaptor.bulkInsertData(tableName, _comClient1Cv77Adatpor.getDocumentDataHeader(tableName, dataRows));
if (dataDocTableExist.Count() > 0)
{
var dataTable = _comClient1Cv77Adatpor.getDocumentDataTable(tableNameData, dataRows);
if (dataTable.Rows.Count > 0)
{
_sqlAdaptor.bulkInsertData(tableNameData, dataTable);
}
}
}
if (date > DateTime.Now)
{
break;
}
date = date.AddDays(1);
}
_sqlAdaptor.dropTable(string.Format("{0}_ok", tableName));
_sqlAdaptor.renameTable(tableName, string.Format("{0}_ok", tableName));
if (dataDocTableExist.Count() > 0)
{
_sqlAdaptor.dropTable(string.Format("{0}_ok", tableNameData));
_sqlAdaptor.renameTable(tableNameData, string.Format("{0}_ok", tableNameData));
}
}
}
catch (Exception ex)
{
Logger.logger.Error(ex);
return(false);
}
return(true);
}
