public static void QueryToFile(SqlConnection connection, string sqlQuery, string csvFilename, string customerCodeEnv, int commandTimeout)
{
string fileName = String.Format("{0}.csv", csvFilename);
string destinationFile = Path.Combine(Path.GetTempPath(), fileName);
using (var command = new SqlCommand(sqlQuery, connection))
{
command.CommandTimeout = commandTimeout;
using (var reader = command.ExecuteReader())
using (var outFile = File.CreateText(destinationFile))
{
string[] columnNames = GetColumnNames(reader).ToArray();
int numFields = columnNames.Length;
outFile.WriteLine(string.Join(",", columnNames));
if (reader.HasRows)
{
while (reader.Read())
{
string[] columnValues =
Enumerable.Range(0, numFields)
.Select(i => Encoding.UTF8.GetString(Encoding.UTF8.GetBytes(reader.GetValue(i).ToString())))
.Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
.ToArray();
outFile.WriteLine(string.Join(",", columnValues));
}
}
}
}
CompressFile(fileName, destinationFile);
S3Uploader.upload(destinationFile + ".gz", String.Format("{0}/{1}.gz", customerCodeEnv, fileName), "ecpr");
}
public static void DumpTableToFile(SqlConnection connection, string tableName, string fieldsName, string filterCond, string customerCodeEnv, int commandTimeout)
{
string fileName = String.Format("{0}.csv", tableName.ToLower());
string destinationFile = Path.Combine(Path.GetTempPath(), fileName);
string whereCond = "";
if (filterCond != null)
{
whereCond = String.Format(" WHERE {0}", filterCond);
}
SqlTransaction trans;
trans = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
using (var command = new SqlCommand("SELECT " + fieldsName + " FROM " + tableName + whereCond, connection))
{
command.CommandTimeout = commandTimeout;
command.Transaction = trans;
TableDumper.readerToFile(command, destinationFile);
}
CompressFile(fileName, destinationFile);
S3Uploader.upload(destinationFile + ".gz", String.Format("{0}/{1}.gz", customerCodeEnv, fileName), "ecpr");
}
public static void runQueryToFile(SqlConnection connection, string runQuery, string fileName, string customerCodeEnv, int commandTimeout, string resultType)
{
string destinationFile = Path.Combine(Path.GetTempPath(), fileName);
using (var command = new SqlCommand(runQuery, connection))
{
command.CommandTimeout = commandTimeout;
if (resultType == "csv")
{
TableDumper.readerToFile(command, destinationFile);
}
else
{
TableDumper.readerToFile_JSON(command, destinationFile);
}
}
CompressFile(fileName, destinationFile);
S3Uploader.upload(destinationFile + ".gz", String.Format("{0}/{1}.gz", customerCodeEnv, fileName), "ecpr");
}
internal void dumpTable(List <string> tableList, List <string> exclude_columns = null, string export_format = "csv", string filterCond = null)
{
System.Threading.Tasks.Task.Factory.StartNew(() =>
{
foreach (string tableName in tableList)
{
try
{
string fieldsName = "*";
if (this.config.dumpIgnoreFields != null && this.config.dumpIgnoreFields.ContainsKey(tableName.ToUpper()))
{
exclude_columns = new List <string>(this.config.dumpIgnoreFields[tableName.ToUpper()]);
}
if (exclude_columns != null && exclude_columns.Count > 0)
{
using (var command = new SqlCommand(String.Format("select lower(COLUMN_NAME) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '{0}' and COLUMN_NAME not in ({1})", tableName, "'" + String.Join("','", exclude_columns.ToArray()) + "'"), this.sqlConnection))
{
command.CommandTimeout = this.config.defaultSQLQueryTimeout;
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
exclude_columns.Clear();
while (reader.Read())
{
exclude_columns.Add(reader.GetValue(0).ToString());
}
fieldsName = String.Join(",", exclude_columns.ToArray());
}
}
}
}
this.logger.write(String.Format("Dumping table {0} to {1}", tableName, Path.GetTempPath()), Logger.LOGLEVEL.INFO);
using (SqlConnection _sqlConnection = new SqlConnection(this.config.cprDatabaseConnectionString))
{
try
{
if (_sqlConnection.State != ConnectionState.Open)
{
_sqlConnection.Open();
}
}
catch (Exception) { }
if (export_format.ToLower() == "json")
{
TableDumper.DumpTableToFile_JSON(_sqlConnection, tableName, fieldsName, filterCond, String.Format("{0}{1}", this.config.cprCustomerCode, this.config.cprCustomerEnvironment), this.config.defaultSQLQueryTimeout);
}
else
{
TableDumper.DumpTableToFile(_sqlConnection, tableName, fieldsName, filterCond, String.Format("{0}{1}", this.config.cprCustomerCode, this.config.cprCustomerEnvironment), this.config.defaultSQLQueryTimeout);
}
}
this.logger.write(String.Format("Dumped table {0} into {1} file successfully and uploaded to s3.", tableName, export_format), Logger.LOGLEVEL.INFO);
string downloadLink = S3Uploader.GenerateDownloadLink(tableName, this.config.cprCustomerCode, this.config.cprCustomerEnvironment, export_format);
this.serviceControlCenter.publishMessage(JsonConvert.SerializeObject(new Dictionary <string, object>()
{
{ "customer", this.config.cprCustomerCode },
{ "environment", this.config.cprCustomerEnvironment },
{ "uuid", Guid.NewGuid().ToString() },
{ "type", "url" },
{ "table_name", tableName },
{ "url", downloadLink }
}, new JsonSerializerSettings()
{
Formatting = Formatting.Indented,
}), "ecpr-config-s3-response", 2);
}
catch (S3UploadException error)
{
this.logger.write(String.Format("Cannot start uploading file {0}.{1}.gz to s3 because {2}.", tableName, export_format, error.ToString()), Logger.LOGLEVEL.ERROR);
}
catch (Exception error)
{
this.logger.write(String.Format("Cannot start dumping table {0} because {1}.", tableName, error.ToString()), Logger.LOGLEVEL.ERROR);
this.logger.write(ECPRERROR.ExceptionDetail(error), Logger.LOGLEVEL.ERROR);
}
}
});
}