public void ExecuteProcWithParam(string fileName, string[] procNames, string[] sheetNames, string[,] parameters, bool isTwoParas, DateTime generateDate)
{
string para;
for (int i = 0; i < procNames.Length; i++)
{
try
{
_sqlCon.Open();
_sqlCmd.CommandType = CommandType.StoredProcedure;
string ProcName = procNames[i].ToString();
_sqlCmd.CommandText = ProcName;
if (ProcName == "TriBenchMarkRpt_MatchedBacklog")//add if condition for benchmark report.
{
DateTime EndDay = generateDate.AddDays(-2);
DateTime StartDay = generateDate.AddDays(-8);
string ParaEndDate = EndDay.ToString("yyyy-MM-dd");
string ParaStartDate = StartDay.ToString("yyyy-MM-dd");
_sqlCmd.Parameters.AddWithValue("@startDate", ParaStartDate);
_sqlCmd.Parameters.AddWithValue("@endDate", ParaEndDate);
}
else
{
if (isTwoParas)
{
for (int j = 0; j < 2; j++)
{
if (parameters[i, j] == "")
{
;
}
else
{
if (j == 0)
{
para = "@diffn";
}
else
{
para = "@diffb";
}
_sqlCmd.Parameters.AddWithValue(para, Int32.Parse(parameters[i, j].ToString()));
}
}
}
else
{
for (int j = 0; j < 1; j++)
{
if (parameters[i, j] == "")
{
;
}
else
{
if (j == 0)
{
para = "@diffn";
}
else
{
para = "@diffb";
}
_sqlCmd.Parameters.AddWithValue(para, Int32.Parse(parameters[i, j].ToString()));
}
}
}
}
_sqlDA.SelectCommand = _sqlCmd;
_sqlDA.Fill(_ds, ProcName);
}
catch (Exception e)
{
throw e;
}
finally
{
_sqlCmd.Parameters.Clear();
_sqlCon.Close();
}
}
//filename1:the template file name
ExcelBus excelBiz = new ExcelBus();
excelBiz.ExportDataToExcel(fileName, procNames, sheetNames, _ds, generateDate);
}
public void ExecuteProcWithParam(string fileName, string[] procNames, string[] sheetNames, string[,] parameters, bool isTwoParas, DateTime generateDate)
{
string para;
for (int i = 0; i < procNames.Length; i++)
{
try
{
_sqlCon.Open();
_sqlCmd.CommandType = CommandType.StoredProcedure;
string ProcName = procNames[i].ToString();
_sqlCmd.CommandText = ProcName;
if (ProcName == "TriBenchMarkRpt_MatchedBacklog")//add if condition for benchmark report.
{
DateTime EndDay = generateDate.AddDays(-2);
DateTime StartDay = generateDate.AddDays(-8);
string ParaEndDate = EndDay.ToString("yyyy-MM-dd");
string ParaStartDate = StartDay.ToString("yyyy-MM-dd");
_sqlCmd.Parameters.AddWithValue("@startDate", ParaStartDate);
_sqlCmd.Parameters.AddWithValue("@endDate", ParaEndDate);
}
else
{
if (isTwoParas)
{
for (int j = 0; j < 2; j++)
{
if (parameters[i, j] == "")
{
;
}
else
{
if (j == 0)
{
para = "@diffn";
}
else
{
para = "@diffb";
}
_sqlCmd.Parameters.AddWithValue(para, Int32.Parse(parameters[i, j].ToString()));
}
}
}
else
{
for (int j = 0; j < 1; j++)
{
if (parameters[i, j] == "")
{
;
}
else
{
if (j == 0)
{
para = "@diffn";
}
else
{
para = "@diffb";
}
_sqlCmd.Parameters.AddWithValue(para, Int32.Parse(parameters[i, j].ToString()));
}
}
}
}
_sqlDA.SelectCommand = _sqlCmd;
_sqlDA.Fill(_ds, ProcName);
}
catch (Exception e)
{
throw e;
}
finally
{
_sqlCmd.Parameters.Clear();
_sqlCon.Close();
}
}
//filename1:the template file name
ExcelBus excelBiz = new ExcelBus();
excelBiz.ExportDataToExcel(fileName, procNames, sheetNames, _ds, generateDate);
}
