public SqlServerManager(DatabaseConnectionConfig config, int timeout) { if (string.IsNullOrEmpty(config.UserName)) { SetConnection(string.Format("Server={0}; Database={1}; Connect Timeout={2}; Integrated Security=true", config.ServerName, config.DatabaseName, timeout)); } else { SetConnection(string.Format("Server={0}; Database={1}; Connect Timeout={4}; User Id={2}; password={3}", config.ServerName, config.DatabaseName, config.UserName, config.Password, timeout)); } }
public void Update(Dictionary<string, string> workbookTables, DatabaseConnectionConfig dbConnConfig) { IDatabaseManager dbm = null; try { dbm = new SqlServerManager(dbConnConfig, 0); foreach (var workbookTable in workbookTables) { Console.WriteLine($"Updating {workbookTable.Key}"); var worksheet = Worksheets[workbookTable.Key]; if (worksheet == null) { worksheet = Worksheets.Add(workbookTable.Key); } else { worksheet.DeleteColumn(1, worksheet.Dimension.Columns); } var reader = dbm.OpenReaderSqlCommand($"select * from [adhoc].[{StopSqlInjection(workbookTable.Value)}]"); for (var i = 0; i < reader.FieldCount; i++) { worksheet.Cells[1, i + 1].Value = reader.GetName(i); } var sheetRow = 2; foreach (var row in reader.Rows()) { for (var i = 0; i < reader.FieldCount; i++) { worksheet.Cells[sheetRow, i + 1].Value = reader[i].ConvertTo<string>(); } sheetRow++; } reader.Close(); reader.Dispose(); } } finally { if (dbm != null) { dbm.Dispose(); } } }