public static ReturnProc RunProcedure(SqlConnection conn, string procedureName, string projectName, DateTime? startdate = null, DateTime? enddate = null)
{
Log.Write("Start RunProcedure: " + procedureName);
var sql = "EXEC " + procedureName;
var cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = 180;
var lst = new List<ValidationRows>();
var lstData = new List<QueryData>();
var indexData = new List<IndexData>();
try
{
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
var rdr = cmd.ExecuteReader();
Log.Write("Start: " + cmd.CommandText);
// Получаем список запросов валидации
if (rdr.HasRows)
{
while (rdr.Read())
{
lst.Add(new ValidationRows
{
s1 = rdr.GetString(0),
s2 = rdr.GetString(1),
s3 = rdr.GetString(2),
s4 = rdr.GetString(3),
s5 = rdr.GetString(4),
s6 = rdr.GetString(5),
});
}
}
rdr.Close();
rdr = null;
Log.Write("Exit: " + cmd.CommandText);
if (startdate != null && enddate != null)
{
foreach (var validationRowse in lst)
{
validationRowse.s6 = validationRowse.s6 +
" @start_date = N'" + startdate.ToString() + "', @end_date = N'" + enddate.ToString() + "'";
}
}
else
{
foreach (var validationRowse in lst)
{
if (validationRowse.s6.Contains("GET_USER"))
{
validationRowse.s6 = validationRowse.s6 +
" @start_date = NULL, @end_date = NULL";
}
}
}
Log.Write("Start: Parsing");
foreach (var item in lst)
{
var obj = GetQueryData(item.s2, item.s1, item.s5, item.s3, item.s4, item.s6, projectName, conn);
if (obj != null)
{
lstData.Add(obj);
indexData.Add(new IndexData
{
Description = item.s3,
NameList = item.s4,
SelectCommand = item.s6,
ValidationRule = item.s1
});
}
}
Log.Write("Exit: Parsing");
}
catch (Exception e)
{
Log.Write(e.Message + " - " + procedureName + " - ");
}
finally
{
Log.Write("Exit RunProcedure: " + procedureName);
}
var res = new ReturnProc(lstData, indexData);
return res;
}
public static string GenerateDocument(string filePath,ReturnProc res)
{
Excel.Application ExcelApp;
Excel.Worksheet ExcelSheet;
Excel.Workbook ExcelWorkbook;
Excel.Workbooks ExcelWorkbooks;
Excel.Range ExcelRange;
int rowsCount;
int colsCount;
Log.Write("Start GenerateDocument");
try
{
if (String.IsNullOrEmpty(filePath))
{
filePath = Path.Combine(filePath + res.Data[0].ProjectName + "_Validation_" + DateTime.Now.ToShortDateString());
//if (Environment.OSVersion.Version.Major >= 6)
//{
// filePath = Directory.GetParent(filePath).FullName;
//}
}
var filename = res.Data[0].ProjectName + "_Validation_" + DateTime.Now.ToShortDateString() + ".xlsx";
ExcelApp = CreateExcelObj();
ExcelWorkbooks = ExcelApp.Workbooks;
ExcelApp.ScreenUpdating = false;
ExcelApp.DisplayAlerts = false;
ExcelWorkbook = ExcelWorkbooks.Add();
res.Data = SortData(res.Data);
foreach (var itemData in res.Data)
{
Log.Write(itemData.NameList + " " + itemData.ValidationRule);
ExcelSheet = ExcelWorkbook.Sheets.Add();
ExcelSheet.Name = itemData.NameList;
FormatSheet(ExcelSheet, itemData);
rowsCount = itemData.Data.Rows.Count;
colsCount = itemData.Data.Columns.Count;
ExcelSheet.Cells[1, 1] = "Проект:";
ExcelSheet.Cells[1, 2] = itemData.ProjectName;
ExcelSheet.Range[ExcelSheet.Cells[1, 2], ExcelSheet.Cells[1, colsCount]].Merge();
ExcelSheet.Cells[2, 1] = "Правило:";
ExcelSheet.Cells[2, 2] = itemData.ValidationRule;
ExcelSheet.Range[ExcelSheet.Cells[2, 2], ExcelSheet.Cells[2, colsCount]].Merge();
ExcelSheet.Cells[3, 1] = "Описание:";
ExcelSheet.Cells[3, 2] = itemData.Description;
ExcelSheet.Range[ExcelSheet.Cells[3, 2], ExcelSheet.Cells[3, colsCount]].Merge();
object[,] dataSet = new object[itemData.Data.Rows.Count, itemData.Data.Columns.Count];
for (int i = 1; i <= itemData.FieldsName.Count; i++)
{
ExcelSheet.Cells[5, i] = itemData.FieldsName[i - 1];
}
for (int i = 0; i < itemData.Data.Rows.Count; i++)
{
for (int j = 0; j < itemData.FieldsName.Count; j++)
{
dataSet[i, j] = itemData.Data.Rows[i][j].ToString();
}
}
Excel.Range rng =
ExcelSheet.Range[
ExcelSheet.Cells[6, 1],
ExcelSheet.Cells[5 + itemData.Data.Rows.Count, itemData.FieldsName.Count]];
rng.Value = dataSet;
FormatDataArea(ExcelSheet.Range[ExcelSheet.Cells[5, 1], ExcelSheet.Cells[5 + rowsCount, colsCount]], itemData.ValidationRule);
FormatDescription(ExcelSheet.Range[ExcelSheet.Cells[1, 1], ExcelSheet.Cells[3, 2]]);
ExcelSheet.Range[ExcelSheet.Cells[3, 2], ExcelSheet.Cells[3, colsCount]].WrapText = true;
ExcelSheet.Range[ExcelSheet.Cells[3, 2], ExcelSheet.Cells[3, colsCount]].RowHeight = 60;
}
//Adding Index
object[,] dataIndex = new object[res.Index.Count, 4];
for (int j = 0; j < res.Index.Count; j++)
{
dataIndex[j, 0] = res.Index[j].NameList;
dataIndex[j, 1] = res.Index[j].ValidationRule;
dataIndex[j, 2] = res.Index[j].Description;
dataIndex[j, 3] = res.Index[j].SelectCommand;
}
ExcelSheet = ExcelWorkbook.Sheets.Add();
ExcelSheet.Name = "Index";
FormatSheet(ExcelSheet, res.Data[0]);
ExcelSheet.Cells[1, 1] = "Проект:";
ExcelSheet.Cells[1, 2] = res.Data[0].ProjectName;
ExcelSheet.Cells[2, 1] = "Описание:";
ExcelSheet.Range[ExcelSheet.Cells[2, 1], ExcelSheet.Cells[2, 3]].Merge();
FormatDescription(ExcelSheet.Range[ExcelSheet.Cells[1, 1], ExcelSheet.Cells[2, 2]]);
ExcelSheet.Cells[4, 1] = "Имя листа";
ExcelSheet.Cells[4, 2] = "Правило валидации";
ExcelSheet.Cells[4, 3] = "Описание правила для поиска ошибок";
ExcelSheet.Cells[4, 4] = "SQL";
Excel.Range rngIndex = ExcelSheet.Range[ExcelSheet.Cells[5, 1], ExcelSheet.Cells[4 + res.Index.Count, 4]];
rngIndex.Value = dataIndex;
FormatDataArea(ExcelSheet.Range[ExcelSheet.Cells[4, 1], ExcelSheet.Cells[4 + res.Index.Count, 4]], "index");
ExcelSheet.Range[ExcelSheet.Cells[5, 3], ExcelSheet.Cells[4 + res.Index.Count, 3]].ColumnWidth = 90;
ExcelSheet.Range[ExcelSheet.Cells[5, 3], ExcelSheet.Cells[4 + res.Index.Count, 3]].WrapText = true;
//End Creating Index
ExcelWorkbook.SaveAs();
ExcelWorkbook.SaveAs(filePath + filename, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
while (Marshal.ReleaseComObject(ExcelWorkbook) > 0)
{ }
while (Marshal.ReleaseComObject(ExcelWorkbooks) > 0)
{ }
ExcelApp.Quit();
while (Marshal.ReleaseComObject(ExcelApp) > 0)
{ }
Log.Write("Document created successfully !");
return filePath + "\\" + filename;
}
catch (Exception ex)
{
Log.Write(ex.Data + "\r\n" + ex.Message + "\r\n" + ex.Source + "\r\n" + ex.InnerException + "\r\n" +
ex.StackTrace);
return null;
}
finally
{
GC.Collect();
Log.Write("Exit GenerateDocument");
}
}
private static void Main(string[] args)
{
try
{
var strPath = args[0];
var strServer = args[1];
var strDBName = args[2];
var strUser = args[3];
var strPassword = args[4];
var strMethod = args[5];
var strProject = args[6];
var conn = Tools.GetConnectionString(strServer, strDBName, strUser, strPassword);
if (strMethod.Equals("RUN_VALIDATION"))
{
using (conn)
{
var res = new ReturnProc(null, null);
try
{
res = Tools.RunProcedure(conn, "RUN_VALIDATION", strProject);
// index = Tools.GetIndex(conn, "RUN_VALIDATION");
if (res.Data.Count > 0)
{
ExcelGeneration.GenerateDocument(strPath, res);
}
else
{
Log.Write("NoData");
}
}
catch (Exception ex)
{
Log.Write(ex);
}
finally
{
Log.Write("Finish");
}
}
}
else if (strMethod.Equals("RUN_EXPORT"))
{
// var data = new List<QueryData>();
var res = new ReturnProc(null, null);
try
{
res = Tools.RunProcedure(conn, "RUN_EXPORT", strProject);
if (res.Data.Count > 0)
{
ExcelGeneration.GenerateDocument2(strPath, res);
Log.Write("Finish");
}
else
{
Log.Write("NoData");
}
}
catch (Exception ex)
{
Log.Write(ex);
}
finally
{
Log.Write("Finish");
}
}
else if (strMethod.Equals("RUN_EXPORT_CSV"))
{
string fileName = "";
string sql = "";
var index = new List<IndexData>();
try
{
index = Tools.GetIndex(conn, "RUN_EXPORT");
Log.Write("Finish");
}
catch (Exception ex)
{
Log.Write(ex);
}
finally
{
if (index.Count > 0)
{
foreach (var queryData in index)
{
fileName = queryData.NameList;
sql = queryData.SelectCommand;
Tools.ExportToCSVFile(strPath, strProject, fileName, sql, conn,
Encoding.GetEncoding(1251), ";", true);
}
}
else
{
Log.Write("NoData");
}
Log.Write("Finish");
}
}
else if (strMethod.Equals("RUN_QUERY"))
{
try
{
Tools.GetQueriesInFormat(conn, strProject, strPath);
}
catch (Exception ex)
{
Log.Write(ex);
}
finally
{
Log.Write("Finish");
}
}
else if (strMethod.Equals("RUN_ACTIVITY"))
{
var res = new ReturnProc(null, null);
try
{
res = Tools.RunProcedure(conn, "RUN_ACTIVITY", strProject);
// var index = Tools.GetIndex(conn, "RUN_ACTIVITY");
if (res.Data.Count > 0)
{
ExcelGeneration.GenerateDocument(strPath, res);
}
}
catch (Exception ex)
{
Log.Write(ex);
}
finally
{
Log.Write("Finish");
}
}
else if (strMethod.Equals("RUN_SYNC"))
{
try
{
Tools.RunProcedureNonQuery(conn, "RUN_SYNC");
}
catch (Exception ex)
{
Log.Write(ex);
}
finally
{
Log.Write("Finish");
}
}
else
{
}
}
catch (Exception)
{
Log.Write("Error input");
}
finally
{
Console.ReadKey();
}
}
