public DataTable Execute(ArrayList parameters)
{
ReportTableAdapter reportAdapter = new ReportTableAdapter();
SqlCommand cmd = reportAdapter.Connection.CreateCommand();
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoredProcedureName;
foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
try
{
da.SelectCommand = cmd;
da.Fill(dt);
}
catch (Exception ex)
{
throw new Exception("Run query error: " + cmd.CommandText + " [" + ex.Message + "]");
}
finally
{
if (cmd.Connection != null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Connection.Dispose();
}
}
return(dt);
}
protected override void OnPreRender(EventArgs e)
{
base.OnPreRender(e);
if (!Page.IsPostBack)
{
parameterHolder.Visible = true;
toggleHolder.Visible = true;
resultHolder.Visible = false;
linkHolder.Visible = false;
runDateHolder.Visible = false;
id = GetIdFromQueryString();
ReportTableAdapter reportAdapter = new ReportTableAdapter();
DollarSaverDB.ReportDataTable reportTable = reportAdapter.GetReport(id);
if (reportTable.Count == 1)
{
reportToRun = reportTable[0];
var roleCheck = from DollarSaverDB.AdminRoleRow role in reportToRun.TypeOfReport.Roles
where role.AdminRoleId == (int)AdminRole.Root
select role;
if (!roleCheck.Any())
{
Response.Redirect("default.aspx");
}
idHidden.Value = reportToRun.ReportId.ToString();
reportHeaderLabel.Text = reportToRun.Name;
parameterRepeater.DataSource = reportToRun.Parameters.Rows;
parameterRepeater.DataBind();
}
else
{
Response.Redirect("default.aspx");
}
}
}
private void reportTypeRepeater_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
DollarSaverDB.ReportTypeRow reportType = (DollarSaverDB.ReportTypeRow)e.Item.DataItem;
Label reportTypeNameLabel = (Label)e.Item.FindControl("reportTypeNameLabel");
reportTypeNameLabel.Text = reportType.Name;
Repeater reportRepeater = (Repeater)e.Item.FindControl("reportRepeater");
reportRepeater.ItemDataBound += new RepeaterItemEventHandler(reportRepeater_ItemDataBound);
ReportTableAdapter reportAdapter = new ReportTableAdapter();
DollarSaverDB.ReportDataTable reports = reportAdapter.GetByType(reportType.ReportTypeId);
reportRepeater.DataSource = reports.Rows;
reportRepeater.DataBind();
}
}
private void runButton_Click(object sender, EventArgs e)
{
id = Convert.ToInt32(idHidden.Value);
ReportTableAdapter reportAdapter = new ReportTableAdapter();
reportToRun = reportAdapter.GetReport(id)[0];
ArrayList sqlParameters = new ArrayList();
ParameterTableAdapter parameterAdapter = new ParameterTableAdapter();
ArrayList parameters = new ArrayList();
foreach (RepeaterItem item in parameterRepeater.Items)
{
if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem)
{
HiddenField paramIdHidden = (HiddenField)item.FindControl("paramIdHidden");
DollarSaverDB.ParameterRow parameter = parameterAdapter.GetParameter(Convert.ToInt32(paramIdHidden.Value))[0];
SqlParameter sqlParameter = new SqlParameter();
sqlParameter.ParameterName = parameter.VariableName;
sqlParameter.SqlDbType = (SqlDbType)parameter.ParameterType.SqlDbTypeId;
TextBox paramValueBox = (TextBox)item.FindControl("paramValueBox");
switch (parameter.ParameterTypeId)
{
case 1:
CheckBox paramCheckBox = (CheckBox)item.FindControl("paramCheckBox");
sqlParameter.Value = paramCheckBox.Checked;
break;
case 2:
DateTime paramDate;
try {
paramDate = Convert.ToDateTime(paramValueBox.Text.Trim());
} catch {
ErrorMessage = parameter.Name + " is not in the proper format";
return;
}
sqlParameter.Value = paramDate;
break;
case 3:
case 4:
case 5:
case 7:
sqlParameter.Value = paramValueBox.Text.Trim();
break;
case 8:
DropDownList hourList = (DropDownList)item.FindControl("hourList");
DropDownList minuteList = (DropDownList)item.FindControl("minuteList");
string paramValue = paramValueBox.Text.Trim() + " " + hourList.SelectedValue + ":" + minuteList.SelectedValue + ":00";
DateTime paramDateTime;
try {
paramDateTime = Convert.ToDateTime(paramValue);
} catch {
ErrorMessage = parameter.Name + " is not in the proper format";
return;
}
sqlParameter.Value = paramDateTime;
break;
case 6:
HiddenField paramValueHidden = (HiddenField)item.FindControl("paramValueHidden");
sqlParameter.Value = Convert.ToInt32(paramValueHidden.Value);
break;
/* case 9 :
* case 10 :
*
*/
default:
throw new Exception("ERROR: Unknown Parameter Type");
}
sqlParameters.Add(sqlParameter);
parameter.RunParameter = sqlParameter;
parameters.Add(parameter);
}
}
reportHeaderLabel.Text = reportHeaderLabel.Text;
parameterHolder.Visible = false;
toggleHolder.Visible = false;
resultHolder.Visible = true;
runDateHolder.Visible = true;
bool textFile = false;
bool excelFile = false;
if (excelRadioButton.Checked)
{
excelFile = true;
}
else if (delimitedRadioButton.Checked)
{
textFile = true;
}
else
{
linkHolder.Visible = true;
}
DataTable results = reportToRun.Execute(sqlParameters);
if (!textFile)
{
String header = string.Empty;
foreach (DataColumn column in results.Columns)
{
header += "<th class=\"reportHeader\" align=\"center\">" + column.ColumnName + "</th>";
}
StringBuilder val = new StringBuilder("");
string rowClass;
int count = 0;
if (results.Rows.Count > 0)
{
String cellValue = String.Empty;
String align = String.Empty;
Type intType = typeof(System.Int32);
Type smallIntType = typeof(System.Int16);
Type decimalType = typeof(System.Decimal);
foreach (DataRow row in results.Rows)
{
count++;
if (count % 2 == 0)
{
rowClass = "reportRow";
}
else
{
rowClass = "reportAlternatingRow";
}
val.Append("<tr class=\"" + rowClass + "\";>");
for (int i = 0; i < row.ItemArray.Length; i++)
{
cellValue = row.ItemArray[i].ToString().Trim();
align = "left";
if (row.ItemArray[i].GetType() == intType ||
row.ItemArray[i].GetType() == decimalType ||
row.ItemArray[i].GetType() == smallIntType)
{
align = "center";
}
if (!excelRadioButton.Checked && cellValue == String.Empty)
{
cellValue = " ";
}
val.Append("<td align=\"").Append(align).Append("\">").Append(cellValue).Append("</td>");
}
val.Append("</tr>\n");
}
}
else
{
val.Append("<tr><td colspan=").Append(results.Columns.Count.ToString()).Append(" style=\" padding: 15px;\">No Data Found</td></tr>\n");
}
if (excelFile)
{
String resultTable =
"<table border=\"1\" cellpadding=\"5\" cellspacing=\"0\">\n" +
"<thead><tr>" + header + "</tr></thead>\n" +
"<tbody>" + val + "</tbody>\n" +
"</table>\n";
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment; filename=" + reportToRun.Name + ".xls");
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.Write(resultTable);
Response.Flush();
Response.Close();
}
else
{
String resultTable =
"<table border=\"0\" cellpadding=\"5\" cellspacing=\"0\" class=\"admin_form\" width=\"100%\">\n" +
"<thead><tr>" + header + "</tr></thead>\n" +
"<tbody>" + val + "</tbody>\n" +
"</table>\n";
DateTime runTime = DateTime.Now;
runParamRepeater.DataSource = parameters;
runParamRepeater.DataBind();
Page.EnableViewState = false;
runDateLabel.Text = runTime.ToShortDateString() + " " + runTime.ToShortTimeString();
resultsLabel.Text = resultTable;
}
}
else
{
StringBuilder val = new StringBuilder("");
char delimiter = ' ';
switch (delimiterList.SelectedValue)
{
case "pipe":
delimiter = '|';
break;
case "comma":
delimiter = ',';
break;
case "colon":
delimiter = ':';
break;
case "semicolon":
delimiter = ';';
break;
case "space":
delimiter = ' ';
break;
case "tab":
delimiter = '\t';
break;
default:
throw new Exception("ERROR: Unknown Delimter Type");
}
if (results.Rows.Count > 0)
{
String cellValue = String.Empty;
foreach (DataRow row in results.Rows)
{
for (int i = 0; i < row.ItemArray.Length; i++)
{
cellValue = row.ItemArray[i].ToString().Trim();
val.Append(cellValue).Append(delimiter);
}
val.Append(Environment.NewLine);
}
}
else
{
// No data found
}
string fileName = DateTime.Now.ToString("yyyy-MM-dd") + " " + reportToRun.Name + ".txt";
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.Buffer = true;
Response.ContentType = "text/plain";
Response.Write(val.ToString());
Response.Flush();
Response.Close();
}
}
