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