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