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