Note:-The Question pertains to ASP.NET and C#.
I have 3 functions
- GetDataBySearchCriteria.
- GetData.
- GetGridData.
The first function is called when page is loaded. The second function gets called when the user makes a change on page and and presses a btn (GetData) where I set the Session Variable which holds the Search Criteria Data as GridData Object. The Third function is called by the second function to get data as a GridData Object where GridData is a user defined class. Later in the calling function it is converted to json object which is used to render a grid table(jqgrid).
Problem:- We maintain the session in order to facilitate a temporary search criteria holder which we use when the intended user navigates from one page to another in the same module. In my code I set the Session variable only when user presses GetData and according to the current requirement I had to put hard code changes to change the data I get from session for particular pages. In doing so the Session Data is automatically changing. I am unable to figure out why. please help.
Here's the code :-
[System.Web.Services.WebMethod]
public static string GetDataBySearchCriteria(int ReportID) // called when page is loaded
{
if (SessionVariables.RoleID == 0)
{
return "null";
}
try
{
SetProperties(ReportID);
if (!CheckAuthorization(ReportID)) // write ! before statement after testing if its not written
{
return "null";
}
else
{
#region else part
HttpContext.Current.Session["ReportID"] = ReportID;
if (HttpContext.Current.Session["SalesUserSession"] != null)
{
SetProperties(ReportID);
GridData gd = new GridData();
gd = HttpContext.Current.Session["SalesUserSession"] as GridData;
if (pagetitle.ToLower() == "top referring practices")
{
gd.RowArea = "Referring Practice";
gd.order = "Month13@desc";
gd.SelectedCpt = "";
gd.SelectedLocation = "";
gd.SelectedModality = "";
gd.SelectedReferringPractice = "";
gd.SelectedPhysicians = "";
//gd.direction = "desc";
}
else if (pagetitle.ToLower() == "top referring physicians")
{
gd.RowArea = "Physician";
gd.order = "Month13@desc";
gd.SelectedCpt = "";
gd.SelectedLocation = "";
gd.SelectedModality = "";
gd.SelectedReferringPractice = "";
gd.SelectedPhysicians = "";
//gd.direction = "desc";
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
return serializer.Serialize(GetGridData(isVolume: gd.IsVolume, pageNumber: 1, pageSize: gd.PageSize, rowArea: gd.RowArea, cpt: gd.SelectedCpt, referringPhysician: gd.SelectedPhysicians, referringPractice: gd.SelectedReferringPractice, speciality: gd.SelectedSpeciality, modalityFilter: gd.SelectedModality, locationFilter: gd.SelectedLocation, practice: gd.SelectedPractice,order:gd.order,orderBy:gd.orderBy,direction:gd.direction,orderBySummary:gd.orderBySummary,OrderBySummaryCol:gd.orderBySummaryCol,ShowSubTotalForAllGroup: gd.ShowSubTotalForAllGroup));//,direction:gd.direction,orderBySummary:"",OrderBySummaryCol:""));
}
else
{
SqlParameter[] parameters = new SqlParameter[3]; // Error Line
parameters[0] = new SqlParameter("@UserId", SqlDbType.Int);
parameters[0].Value = SessionVariables.UserID;
parameters[1] = new SqlParameter("@RoleID", SqlDbType.Int);
parameters[1].Value = SessionVariables.RoleID;
parameters[2] = new SqlParameter("@ReportTitle", SqlDbType.NVarChar);
// Below Code Added on 11/05/2015 (dd/mm/yyyy) for Testing Purposes
//if (pagetitle == "Top Referring Practices" || pagetitle == "Top Referring Physicians")
//{
// //pagetitle = "Trending Report";
//}
// Modification Ends 11/05/2015
parameters[2].Value = pagetitle;
GridData gridData = new GridData();
DataSet ds = SqlHelper.ExecuteDataset(sqlConnectionString, CommandType.StoredProcedure, "Sales_GetDataByDefaultSearch", parameters);
DataTable Datadt = ds.Tables[1];
if (ReportID == 1 && ds.Tables.Count > 3)
{
//holidayList = ds.Tables[3].AsEnumerable().Select(r=>r.Field<string>("NonWorkingDays")).ToArray();
}
StringBuilder sbcol = new StringBuilder();
sbcol.Append("[");
foreach (DataColumn column in Datadt.Columns)
{
sbcol.Append("\"").Append(GetPivotColumnName(column.ColumnName)).Append("\",");
}
sbcol.Remove(sbcol.Length - 1, 1).Append("]");
StringBuilder sbcolModel = new StringBuilder();
sbcolModel.Append("[");
string[] rowAreaFields = ds.Tables[0].Rows[0]["RowArea"].ToString().Split(',');
//string rowArea;
//rowArea = string.Join(",", rowAreaFields);
foreach (DataColumn column in Datadt.Columns)
{
//if (rowAreaFields.Contains(column.ColumnName.Trim()) && column.ColumnName != null)
if (rowAreaFields.Any(s => column.ColumnName.Trim().Contains(s)))
{
sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"classes\":\"colstyle\",\"align\":\"left\",\"sortable\":true,\"frozen\":true},");
}
//else if (holidayList.Contains(column.ColumnName.Trim()) && column.ColumnName != null)
//{
// sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"classes\":\"holidaystyle\",\"align\":\"right\",\"width\":\"70px\"},");
//}
else if (column.Ordinal >= Datadt.Columns.Count - NoofSummaryColumns)
{
if (PosNegSummaryValues.Contains(Datadt.Columns.Count - column.Ordinal))
{
sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"classes\":\"PosNegValue\",\"align\":\"right\",\"sortable\":true,\"width\":\"70px\"},");
}
else
{
if (column.ColumnName.Contains(" Actual"))
{
sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"classes\":\"SummaryColumns\",\"align\":\"right\",\"sortable\":true,\"width\":\"70px\"},");
}
else
{
sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"classes\":\"SummaryColumns\",\"align\":\"right\",\"sortable\":true,\"width\":\"120px\"},");
}
}
}
else
{
sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"align\":\"right\",\"width\":\"70px\"},");
}
}
sbcolModel.Remove(sbcolModel.Length - 1, 1);
sbcolModel.Append("]");
JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> drow;
foreach (DataRow dr in Datadt.Rows)
{
drow = new Dictionary<string, object>();
foreach (DataColumn col in Datadt.Columns)
{
drow.Add(GetPivotColumnName(col.ColumnName), dr[col]);
}
rows.Add(drow);
}
StringBuilder sbjsonRows = new StringBuilder();
sbjsonRows.Append(serializer.Serialize(rows));
StringBuilder json = new StringBuilder();
json.Append("{").Append("\"rows\":").Append(sbjsonRows.ToString().Trim()).Append("}");
json.ToString();
string[] GroupAreaSortOrderArray;
string[] SummarySortOrderArray;
foreach (DataRow dr in ds.Tables[0].Rows)
{
gridData.isReportDefault = dr["IsDefault"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsDefault"]);
gridData.isAllReportDefault = dr["IsAllReportDefault"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsAllReportDefault"]);
gridData.IsVolume = dr["IsVolume"] == DBNull.Value ? true : Convert.ToBoolean(dr["IsVolume"]);
gridData.RowArea = dr["RowArea"] == DBNull.Value ? "Modality" : GetRowAreaForPivot(Convert.ToString(dr["RowArea"]));
gridData.TitleName = dr["TitleName"] == DBNull.Value ? "" : Convert.ToString(dr["TitleName"]);
gridData.SelectedPractice = dr["PracticeFilter"] == DBNull.Value ? "" : Convert.ToString(dr["PracticeFilter"]);
gridData.SelectedModality = dr["ModalityFilter"] == DBNull.Value ? "" : Convert.ToString(dr["ModalityFilter"]).Replace("'","");
gridData.SelectedLocation = dr["LocationFilter"] == DBNull.Value ? "" : Convert.ToString(dr["LocationFilter"]);
gridData.SelectedCpt = dr["CptFilter"] == DBNull.Value ? "" : Convert.ToString(dr["CptFilter"]).Replace("'","");
gridData.SelectedPhysicians = dr["ReferringPhysicianFilter"] == DBNull.Value ? "" : Convert.ToString(dr["ReferringPhysicianFilter"]);
gridData.SelectedReferringPractice = dr["ReferringPracticeFilter"] == DBNull.Value ? "" : Convert.ToString(dr["ReferringPracticeFilter"]);
gridData.SelectedSpeciality = dr["SpecialityFilter"] == DBNull.Value ? "" : Convert.ToString(dr["SpecialityFilter"]);
gridData.PageSize = dr["PageSize"] == DBNull.Value ? 30 : Convert.ToInt32(dr["PageSize"]);
gridData.ScheduledReport = dr["IsReportScheduled"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsReportScheduled"]);
gridData.Daily = dr["Daily"] == DBNull.Value ? false : Convert.ToBoolean(dr["Daily"]);
gridData.Weekly = dr["Weekly"] == DBNull.Value ? -1 : Convert.ToInt32(dr["Weekly"]);
gridData.Monthly = dr["Monthly"] == DBNull.Value ? 0 : Convert.ToInt32(dr["Monthly"]);
gridData.SelectedPracticeName = dr["PracticeName"] == DBNull.Value ? "" : Convert.ToString(dr["PracticeName"]);
gridData.ShowSubTotalForAllGroup = dr["ShowSubTotalForAllGroup"] == DBNull.Value ? "False" : Convert.ToString(dr["ShowSubTotalForAllGroup"]);
//remap the db column to its corresponding Pivot column here
GroupAreaSortOrderArray = Convert.ToString(dr["GroupAreaSortOrder"]).Split('@');
SummarySortOrderArray = Convert.ToString(dr["SummarySortOrder"]).Split('@');
gridData.order = dr["order"] == DBNull.Value ? "" : Convert.ToString(dr["order"]);
if (GroupAreaSortOrderArray[0] != "")
{
gridData.orderBy = GroupAreaSortOrderArray[0] == null ? "" : MapDbColumnsToPivot(GroupAreaSortOrderArray[0], Datadt);
gridData.direction = GroupAreaSortOrderArray[1] == null ? "" : MapDbColumnsToPivot(GroupAreaSortOrderArray[1], Datadt);
}
if (SummarySortOrderArray[0] != "")
{
gridData.orderBySummary = SummarySortOrderArray[0] == null ? "" : MapDbColumnsToPivot(SummarySortOrderArray[0], Datadt);
gridData.orderBySummaryCol = SummarySortOrderArray[1] == null ? "" : MapDbColumnsToPivot(SummarySortOrderArray[1], Datadt);
}
//gridData.orderBy = gridData.order == "" ? "" :gridData.order.Split('@')[0];
//gridData.direction = gridData.direction==""?"" :gridData.order.Split('@')[1];
//gridData.Date = dr["Date"] == DBNull.Value ? "" : Convert.ToDateTime(dr["Date"]).ToString("yyyy-MM-dd");
}
gridData.page = 1;
gridData.total = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(ds.Tables[2].Rows[0][0]) / Convert.ToDouble(ds.Tables[0].Rows[0]["PageSize"])));
gridData.records = Convert.ToInt32(ds.Tables[2].Rows[0][0]);
gridData.col = sbcol.ToString();
gridData.colModel = sbcolModel.ToString();
gridData.rows = sbjsonRows.ToString().Trim();
return serializer.Serialize(gridData);
}
#endregion
//}
}
}
catch (Exception ex)
{
Elmah.ErrorLog.GetDefault(HttpContext.Current).Log(new Elmah.Error(ex));
throw;
}
}
[System.Web.Services.WebMethod]
public static string GetData(bool isVolume, int pageNumber, int pageSize, string rowArea, string specialityFilter, string modalityFilter, string locationFilter, string CptFilters, string referringpracticeFilter, string referringphysiciansFilter, int ReportID, string practiceFilter, string order, string orderBy, string direction, int SearchTitleID, string orderBySummary, string orderBySummaryCol, string ShowSubTotalForAllGroup)
{
try
{
if (HttpContext.Current.Session["RoleID"] == null || SessionVariables.RoleID == 0 || SessionVariables.UserID == 0)
{
return "null";
}
else
{
if (practiceFilter == null || practiceFilter == "")
{
practiceFilter = "1";
}
System.Collections.SortedList SystemSearchTitleIDs = new System.Collections.SortedList();
SystemSearchTitleIDs = (System.Collections.SortedList)HttpContext.Current.Session["SystemSearchTitleIds"];
SetProperties(ReportID);
HttpContext.Current.Session["ReportID"] = ReportID;
GridData gd;
string[] orderArray;
if (order.Trim() != string.Empty)
{
orderArray = order.Split('@');
orderArray[0] = SalesColumnMapper(orderArray[0]);
order = orderArray[0] + "@" + orderArray[1];
}
gd = GetGridData(isVolume: isVolume, pageNumber: pageNumber, pageSize: pageSize, rowArea: rowArea, modalityFilter: modalityFilter, locationFilter: locationFilter, referringPhysician: referringphysiciansFilter, referringPractice: referringpracticeFilter, speciality: specialityFilter, cpt: CptFilters, practice: practiceFilter, order: order, orderBy: orderBy, direction: direction, orderBySummary: orderBySummary, OrderBySummaryCol: orderBySummaryCol, ShowSubTotalForAllGroup: ShowSubTotalForAllGroup);
HttpContext.Current.Session["SalesUserSession"] = gd;
JavaScriptSerializer serializer = new JavaScriptSerializer();
return serializer.Serialize(gd);
}
}
catch (Exception ex)
{
Elmah.ErrorLog.GetDefault(HttpContext.Current).Log(new Elmah.Error(ex));
throw;
}
}
private static GridData GetGridData(bool isVolume, int pageNumber, int pageSize, string rowArea, string modalityFilter, string locationFilter, string referringPhysician, string referringPractice, string speciality, string cpt, string practice, string order,string orderBy, string direction, string orderBySummary, string OrderBySummaryCol,string ShowSubTotalForAllGroup)
{
try
{
string[] rowAreaFields = rowArea.Split(',').Select(field => field.Trim()).ToArray();
DataSet ds = GetDataByFilterCriteria(isVolume: isVolume, pageNumber: pageNumber, pageSize: pageSize, rowArea: rowArea, modalityFilter: modalityFilter, locationFilter: locationFilter, referringPhysician: referringPhysician, referringPractice: referringPractice, speciality: speciality, cpt: cpt, practice: practice, order: order, ShowSubTotalForAllGroup: ShowSubTotalForAllGroup);
DataTable Datadt = ds.Tables[0];
StringBuilder sbcol = new StringBuilder();
sbcol.Append("[");
foreach (DataColumn column in Datadt.Columns)
{
sbcol.Append("\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",");
}
sbcol.Remove(sbcol.Length - 1, 1);
sbcol.Append("]");
StringBuilder sbcolModel = new StringBuilder();
sbcolModel.Append("[");
foreach (DataColumn column in Datadt.Columns)
{
if (rowAreaFields.Any(s => GetPivotColumnName(column.ColumnName.Trim()).Contains(s)))
{
sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"classes\":\"colstyle\",\"align\":\"left\",\"sortable\":true,\"frozen\":true},");
}
else if (column.Ordinal >= Datadt.Columns.Count - NoofSummaryColumns)
{
if (PosNegSummaryValues.Contains(Datadt.Columns.Count - column.Ordinal))
{
sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"classes\":\"PosNegValue\",\"align\":\"right\",\"sortable\":true,\"width\":\"70px\"},");
}
else
{
if (column.ColumnName.Contains(" Actual"))
{
sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"classes\":\"SummaryColumns\",\"align\":\"right\",\"sortable\":true,\"width\":\"70px\"},");
}
else {
sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"classes\":\"SummaryColumns\",\"align\":\"right\",\"sortable\":true,\"width\":\"120px\"},");
}
}
}
else
{
sbcolModel.Append("{\"name\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"index\":\"").Append(GetPivotColumnName(column.ColumnName.Trim())).Append("\",\"align\":\"right\",\"width\":\"70px\"},");
}
}
sbcolModel.Remove(sbcolModel.Length - 1, 1);
sbcolModel.Append("]");
JavaScriptSerializer serializer = new JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> drow;
foreach (DataRow dr in Datadt.Rows)
{
drow = new Dictionary<string, object>();
foreach (DataColumn col in Datadt.Columns)
{
drow.Add(GetPivotColumnName(col.ColumnName), dr[col]);
}
rows.Add(drow);
}
StringBuilder sbjsonRows = new StringBuilder();
sbjsonRows.Append(serializer.Serialize(rows));
StringBuilder json = new StringBuilder();
json.Append("{");
json.Append("\"rows\":");
json.Append(sbjsonRows.ToString().Trim());
json.Append("}");
json.ToString();
GridData gridData = new GridData();
gridData.SelectedPractice = practice; // New statement returns practicefilter -- if grid displays this we can remove it
gridData.SelectedLocation = locationFilter;
gridData.SelectedModality = modalityFilter;
gridData.SelectedReferringPractice = referringPractice;
gridData.SelectedPhysicians = referringPhysician;
gridData.SelectedCpt = cpt;
gridData.SelectedSpeciality = speciality;
gridData.PageSize = pageSize;
gridData.RowArea = rowArea;
gridData.IsVolume = isVolume;
gridData.page = 1;
gridData.total = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(ds.Tables[1].Rows[0][0]) / Convert.ToDouble(pageSize)));
gridData.records = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
gridData.col = sbcol.ToString();
gridData.colModel = sbcolModel.ToString();
gridData.rows = sbjsonRows.ToString().Trim();
gridData.order = order;
gridData.orderBy = orderBy;
gridData.direction = direction;
gridData.orderBySummary = orderBySummary;
gridData.orderBySummaryCol = OrderBySummaryCol;
gridData.ShowSubTotalForAllGroup = ShowSubTotalForAllGroup;
//Temporary Solution for getting the practice name for the corresponding practice
//var reader = SqlHelper.ExecuteReader(sqlConnectionString, CommandType.Text, "select practicename from practice where practiceid="+practice);
SqlConnection con = new SqlConnection(sqlConnectionString);
SqlCommand cmd = new SqlCommand();
Object returnValue;
cmd.CommandText = "select practicename from practice where practiceid="+practice;
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
returnValue = cmd.ExecuteScalar();
con.Close();
gridData.SelectedPracticeName = returnValue.ToString();
return gridData;
}
catch (Exception ex)
{
Elmah.ErrorLog.GetDefault(HttpContext.Current).Log(new Elmah.Error(ex));
throw;
}
}
