ICollection Items_CreateDataSource() { // Items Show begin Items_sSQL = ""; Items_sCountSQL = ""; string sWhere = "", sOrder = ""; bool HasParam = false; //------------------------------- // Build ORDER BY statement //------------------------------- if (Utility.GetParam("FormItems_Sorting").Length > 0 && !IsPostBack) { ViewState["SortColumn"] = Utility.GetParam("FormItems_Sorting"); ViewState["SortDir"] = "ASC"; } if (ViewState["SortColumn"] != null) { sOrder = ""; } //------------------------------- // Build WHERE statement //------------------------------- System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary(); if (!Params.ContainsKey("category_id")) { string temp = Utility.GetParam("category_id"); if (Utility.IsNumeric(null, temp) && temp.Length > 0) { temp = CCUtility.ToSQL(temp, FieldTypes.Number); } else { temp = ""; } Params.Add("category_id", temp); } if (!Params.ContainsKey("is_recommended")) { string temp = Utility.GetParam("is_recommended"); if (Utility.IsNumeric(null, temp) && temp.Length > 0) { temp = CCUtility.ToSQL(temp, FieldTypes.Number); } else { temp = ""; } Params.Add("is_recommended", temp); } if (Params["category_id"].Length > 0) { HasParam = true; sWhere += "i.[category_id]=" + Params["category_id"]; } if (Params["is_recommended"].Length > 0) { if (sWhere.Length > 0) { sWhere += " and "; } HasParam = true; sWhere += "i.[is_recommended]=" + Params["is_recommended"]; } if (HasParam) { sWhere = " AND (" + sWhere + ")"; } //------------------------------- // Build base SQL statement //------------------------------- Items_sSQL = "select [i].[author] as i_author, " + "[i].[category_id] as i_category_id, " + "[i].[is_recommended] as i_is_recommended, " + "[i].[item_id] as i_item_id, " + "[i].[name] as i_name, " + "[i].[price] as i_price, " + "[c].[category_id] as c_category_id, " + "[c].[name] as c_name " + " from [items] i, [categories] c" + " where [c].[category_id]=i.[category_id] "; //------------------------------- //------------------------------- //------------------------------- //------------------------------- // Assemble full SQL statement //------------------------------- Items_sSQL = Items_sSQL + sWhere + sOrder; if (Items_sCountSQL.Length == 0) { int iTmpI = Items_sSQL.ToLower().IndexOf("select "); int iTmpJ = Items_sSQL.ToLower().LastIndexOf(" from ") - 1; Items_sCountSQL = Items_sSQL.Replace(Items_sSQL.Substring(iTmpI + 7, iTmpJ - 6), " count(*) "); iTmpI = Items_sCountSQL.ToLower().IndexOf(" order by"); if (iTmpI > 1) { Items_sCountSQL = Items_sCountSQL.Substring(0, iTmpI); } } //------------------------------- OleDbDataAdapter command = new OleDbDataAdapter(Items_sSQL, Utility.Connection); DataSet ds = new DataSet(); command.Fill(ds, (i_Items_curpage - 1) * Items_PAGENUM, Items_PAGENUM, "Items"); OleDbCommand ccommand = new OleDbCommand(Items_sCountSQL, Utility.Connection); int PageTemp = (int)ccommand.ExecuteScalar(); Items_Pager.MaxPage = (PageTemp % Items_PAGENUM) > 0?(int)(PageTemp / Items_PAGENUM) + 1:(int)(PageTemp / Items_PAGENUM); bool AllowScroller = Items_Pager.MaxPage == 1?false:true; DataView Source; Source = new DataView(ds.Tables[0]); if (ds.Tables[0].Rows.Count == 0) { Items_no_records.Visible = true; AllowScroller = false; } else { Items_no_records.Visible = false; AllowScroller = AllowScroller && true; } Items_Pager.Visible = AllowScroller; return(Source); // Items Show end }
ICollection Orders_CreateDataSource() { // Orders Show begin Orders_sSQL = ""; Orders_sCountSQL = ""; string sWhere = "", sOrder = ""; bool bReq = true; bool HasParam = false; //------------------------------- // Build ORDER BY statement //------------------------------- if (Utility.GetParam("FormOrders_Sorting").Length > 0 && !IsPostBack) { ViewState["SortColumn"] = Utility.GetParam("FormOrders_Sorting"); ViewState["SortDir"] = "ASC"; } if (ViewState["SortColumn"] != null) { sOrder = " ORDER BY " + ViewState["SortColumn"].ToString() + " " + ViewState["SortDir"].ToString(); } //------------------------------- // Build WHERE statement //------------------------------- System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary(); if (!Params.ContainsKey("member_id")) { string temp = Utility.GetParam("member_id"); if (Utility.IsNumeric(null, temp) && temp.Length > 0) { temp = CCUtility.ToSQL(temp, FieldTypes.Number); } else { temp = ""; } Params.Add("member_id", temp); } if (Params["member_id"].Length > 0) { HasParam = true; sWhere += "o.[member_id]=" + Params["member_id"]; } else { bReq = false; } if (HasParam) { sWhere = " AND (" + sWhere + ")"; } //------------------------------- // Build base SQL statement //------------------------------- Orders_sSQL = "select [o].[item_id] as o_item_id, " + "[o].[member_id] as o_member_id, " + "[o].[order_id] as o_order_id, " + "[o].[quantity] as o_quantity, " + "[i].[item_id] as i_item_id, " + "[i].[name] as i_name " + " from [orders] o, [items] i" + " where [i].[item_id]=o.[item_id] "; //------------------------------- //------------------------------- //------------------------------- //------------------------------- // Assemble full SQL statement //------------------------------- Orders_sSQL = Orders_sSQL + sWhere + sOrder; //------------------------------- if (!bReq) { Orders_no_records.Visible = true; return(null); } OleDbDataAdapter command = new OleDbDataAdapter(Orders_sSQL, Utility.Connection); DataSet ds = new DataSet(); command.Fill(ds, 0, Orders_PAGENUM, "Orders"); DataView Source; Source = new DataView(ds.Tables[0]); if (ds.Tables[0].Rows.Count == 0) { Orders_no_records.Visible = true; } else { Orders_no_records.Visible = false; } return(Source); // Orders Show end }
ICollection Items_CreateDataSource() { // Items Show begin Items_sSQL = ""; Items_sCountSQL = ""; string sWhere = "", sOrder = ""; bool bReq = true; bool HasParam = false; //------------------------------- // Build WHERE statement //------------------------------- System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary(); if (!Params.ContainsKey("UserID")) { object Temp = Session["UserID"]; string temp; if (Temp == null) { temp = ""; } else { temp = Temp.ToString(); } if (Utility.IsNumeric(null, temp) && temp.Length > 0) { temp = CCUtility.ToSQL(temp, FieldTypes.Number); } else { temp = ""; } Params.Add("UserID", temp); } if (Params["UserID"].Length > 0) { HasParam = true; sWhere += "[member_id]=" + Params["UserID"]; } else { bReq = false; } if (HasParam) { sWhere = " AND (" + sWhere + ")"; } //------------------------------- // Build base SQL statement //------------------------------- Items_sSQL = "SELECT order_id, name, price, quantity, member_id, quantity*price as sub_total FROM items, orders WHERE orders.item_id=items.item_id"; sOrder = " ORDER BY order_id"; //------------------------------- //------------------------------- //------------------------------- //------------------------------- // Assemble full SQL statement //------------------------------- Items_sSQL = Items_sSQL + sWhere + sOrder; //------------------------------- if (!bReq) { Items_no_records.Visible = true; return(null); } OleDbDataAdapter command = new OleDbDataAdapter(Items_sSQL, Utility.Connection); DataSet ds = new DataSet(); command.Fill(ds, 0, Items_PAGENUM, "Items"); DataView Source; Source = new DataView(ds.Tables[0]); if (ds.Tables[0].Rows.Count == 0) { Items_no_records.Visible = true; } else { Items_no_records.Visible = false; } return(Source); // Items Show end }
ICollection Orders_CreateDataSource() { // Orders Show begin Orders_sSQL = ""; Orders_sCountSQL = ""; string sWhere = "", sOrder = ""; bool HasParam = false; //------------------------------- // Build ORDER BY statement //------------------------------- sOrder = " order by o.order_id Asc"; if (Utility.GetParam("FormOrders_Sorting").Length > 0 && !IsPostBack) { ViewState["SortColumn"] = Utility.GetParam("FormOrders_Sorting"); ViewState["SortDir"] = "ASC"; } if (ViewState["SortColumn"] != null) { sOrder = "ORDER BY @SortColumn @SortDir"; } //------------------------------- // Build WHERE statement //------------------------------- System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary(); if (!Params.ContainsKey("item_id")) { string temp = Utility.GetParam("item_id"); if (Utility.IsNumeric(null, temp) && temp.Length > 0) { temp = CCUtility.ToSQL(temp, FieldTypes.Number); } else { temp = ""; } Params.Add("item_id", temp); } if (!Params.ContainsKey("member_id")) { string temp = Utility.GetParam("member_id"); if (Utility.IsNumeric(null, temp) && temp.Length > 0) { temp = CCUtility.ToSQL(temp, FieldTypes.Number); } else { temp = ""; } Params.Add("member_id", temp); } if (Params["item_id"].Length > 0) { HasParam = true; sWhere += "o.[item_id]=" + Params["item_id"]; } if (Params["member_id"].Length > 0) { if (sWhere.Length > 0) { sWhere += " and "; } HasParam = true; sWhere += "o.[member_id]=" + Params["member_id"]; } if (HasParam) { sWhere = " AND (" + sWhere + ")"; } //------------------------------- // Build base SQL statement //------------------------------- Orders_sSQL = "select [o].[item_id] as o_item_id, " + "[o].[member_id] as o_member_id, " + "[o].[order_id] as o_order_id, " + "[o].[quantity] as o_quantity, " + "[i].[item_id] as i_item_id, " + "[i].[name] as i_name, " + "[m].[member_id] as m_member_id, " + "[m].[member_login] as m_member_login " + " from [orders] o, [items] i, [members] m" + " where [i].[item_id]=o.[item_id] and [m].[member_id]=o.[member_id] "; //------------------------------- //------------------------------- //------------------------------- //------------------------------- // Assemble full SQL statement //------------------------------- Orders_sSQL = Orders_sSQL + sWhere + sOrder; if (Orders_sCountSQL.Length == 0) { int iTmpI = Orders_sSQL.ToLower().IndexOf("select "); int iTmpJ = Orders_sSQL.ToLower().LastIndexOf(" from ") - 1; Orders_sCountSQL = Orders_sSQL.Replace(Orders_sSQL.Substring(iTmpI + 7, iTmpJ - 6), " count(*) "); iTmpI = Orders_sCountSQL.ToLower().IndexOf(" order by"); if (iTmpI > 1) { Orders_sCountSQL = Orders_sCountSQL.Substring(0, iTmpI); } } //------------------------------- OleDbDataAdapter command = new OleDbDataAdapter(Orders_sSQL, Utility.Connection); command.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("@SortColumn", viewstate["SortColumn"])); command.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("@SortDir", viewstate["SortDir"])); DataSet ds = new DataSet(); command.Fill(ds, (i_Orders_curpage - 1) * Orders_PAGENUM, Orders_PAGENUM, "Orders"); OleDbCommand ccommand = new OleDbCommand(Orders_sCountSQL, Utility.Connection); int PageTemp = (int)ccommand.ExecuteScalar(); Orders_Pager.MaxPage = (PageTemp % Orders_PAGENUM) > 0?(int)(PageTemp / Orders_PAGENUM) + 1:(int)(PageTemp / Orders_PAGENUM); bool AllowScroller = Orders_Pager.MaxPage == 1?false:true; DataView Source; Source = new DataView(ds.Tables[0]); if (ds.Tables[0].Rows.Count == 0) { Orders_no_records.Visible = true; AllowScroller = false; } else { Orders_no_records.Visible = false; AllowScroller = AllowScroller && true; } Orders_Pager.Visible = AllowScroller; return(Source); // Orders Show end }
ICollection Results_CreateDataSource() { // Results Show begin Results_sSQL = ""; Results_sCountSQL = ""; string sWhere = "", sOrder = ""; bool HasParam = false; //------------------------------- // Build ORDER BY statement //------------------------------- sOrder = " order by i.name Asc"; //------------------------------- // Build WHERE statement //------------------------------- System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary(); if (!Params.ContainsKey("author")) { string temp = Utility.GetParam("author"); Params.Add("author", temp); } if (!Params.ContainsKey("category_id")) { string temp = Utility.GetParam("category_id"); if (Utility.IsNumeric(null, temp) && temp.Length > 0) { temp = CCUtility.ToSQL(temp, FieldTypes.Number); } else { temp = ""; } Params.Add("category_id", temp); } if (!Params.ContainsKey("name")) { string temp = Utility.GetParam("name"); Params.Add("name", temp); } if (!Params.ContainsKey("pricemax")) { string temp = Utility.GetParam("pricemax"); if (Utility.IsNumeric(null, temp) && temp.Length > 0) { temp = CCUtility.ToSQL(temp, FieldTypes.Number); } else { temp = ""; } Params.Add("pricemax", temp); } if (!Params.ContainsKey("pricemin")) { string temp = Utility.GetParam("pricemin"); if (Utility.IsNumeric(null, temp) && temp.Length > 0) { temp = CCUtility.ToSQL(temp, FieldTypes.Number); } else { temp = ""; } Params.Add("pricemin", temp); } if (Params["author"].Length > 0) { HasParam = true; sWhere += "i.[author] like '%" + Params["author"].Replace("'", "''") + "%'"; } if (Params["category_id"].Length > 0) { if (sWhere.Length > 0) { sWhere += " and "; } HasParam = true; sWhere += "i.[category_id]=" + Params["category_id"]; } if (Params["name"].Length > 0) { if (sWhere.Length > 0) { sWhere += " and "; } HasParam = true; sWhere += "i.[name] like '%" + Params["name"].Replace("'", "''") + "%'"; } if (Params["pricemax"].Length > 0) { if (sWhere.Length > 0) { sWhere += " and "; } HasParam = true; sWhere += "i.[price]<" + Params["pricemax"]; } if (Params["pricemin"].Length > 0) { if (sWhere.Length > 0) { sWhere += " and "; } HasParam = true; sWhere += "i.[price]>" + Params["pricemin"]; } if (HasParam) { sWhere = " AND (" + sWhere + ")"; } //------------------------------- // Build base SQL statement //------------------------------- Results_sSQL = "select [i].[author] as i_author, " + "[i].[category_id] as i_category_id, " + "[i].[image_url] as i_image_url, " + "[i].[item_id] as i_item_id, " + "[i].[name] as i_name, " + "[i].[price] as i_price, " + "[c].[category_id] as c_category_id, " + "[c].[name] as c_name " + " from [items] i, [categories] c" + " where [c].[category_id]=i.[category_id] "; //------------------------------- //------------------------------- //------------------------------- //------------------------------- // Assemble full SQL statement //------------------------------- Results_sSQL = Results_sSQL + sWhere + sOrder; if (Results_sCountSQL.Length == 0) { int iTmpI = Results_sSQL.ToLower().IndexOf("select "); int iTmpJ = Results_sSQL.ToLower().LastIndexOf(" from ") - 1; Results_sCountSQL = Results_sSQL.Replace(Results_sSQL.Substring(iTmpI + 7, iTmpJ - 6), " count(*) "); iTmpI = Results_sCountSQL.ToLower().IndexOf(" order by"); if (iTmpI > 1) { Results_sCountSQL = Results_sCountSQL.Substring(0, iTmpI); } } //------------------------------- OleDbDataAdapter command = new OleDbDataAdapter(Results_sSQL, Utility.Connection); DataSet ds = new DataSet(); command.Fill(ds, (i_Results_curpage - 1) * Results_PAGENUM, Results_PAGENUM, "Results"); OleDbCommand ccommand = new OleDbCommand(Results_sCountSQL, Utility.Connection); int PageTemp = (int)ccommand.ExecuteScalar(); Results_Pager.MaxPage = (PageTemp % Results_PAGENUM) > 0?(int)(PageTemp / Results_PAGENUM) + 1:(int)(PageTemp / Results_PAGENUM); bool AllowScroller = Results_Pager.MaxPage == 1?false:true; DataView Source; Source = new DataView(ds.Tables[0]); if (ds.Tables[0].Rows.Count == 0) { Results_no_records.Visible = true; AllowScroller = false; } else { Results_no_records.Visible = false; AllowScroller = AllowScroller && true; } Results_Pager.Visible = AllowScroller; return(Source); // Results Show end }