public DataSet Get_ItemRecords(string itemName)//search { SqlCommand cmd_ItemList = new SqlCommand(); cmd_ItemList.CommandText = "[VRM].[Get_ItemRecords]"; cmd_ItemList.CommandType = CommandType.StoredProcedure; cmd_ItemList.Parameters.Clear(); SqlParameter sqlParaItemName = new SqlParameter("@itemName", SqlDbType.VarChar, 50); sqlParaItemName.Value = itemName; cmd_ItemList.Parameters.Add(sqlParaItemName); DataSet ds_ItemList = getDataSet(cmd_ItemList); return ds_ItemList; }
ALTER procedure [VRM].[GET_ItemRecords] @itemName varchar (50) = NULL AS select * from VRM.IetmTable where (@itemName IS NOT NULL or itemName= @itemName)
using System;using System.Data;using System.Web.UI;using System.Web.UI.WebControls;using com.os.database;using com.os.com; namespace OnlineStore{ public partial class item : System.Web.UI.Page { private os_database osdb = new os_database(); private String viewStateGVName = "gvItem"; //set the const for the ID of the GV Checkbox column private const string GVCHECKBOXCOLID = "selected"; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { //Set the sortExpression ViewState[this.ToString() + "_SortExpression"] = "ItemName"; ViewState[this.ToString() + "_SortDirection"] = "ASC"; } } protected void btnClear_Click(object sender, EventArgs e) { gvItem.Visible = false; tbSearchItemName.Text = ""; } protected void btnSearch_Click(object sender, EventArgs e) { BindGrid(true); gvItem.Visible = true; } protected void chkSelectAll_CheckedChanged(object sender, EventArgs e) { CheckBox chk; foreach (GridViewRow rowItem in gvItem.Rows) { chk = (CheckBox)(rowItem.Cells[0].FindControl("selected")); chk.Checked = ((CheckBox)sender).Checked; } } //Bind the GridView to with the Database returned records private void BindGrid(bool Reload) { DataTable dtItemRecords = null; if (Reload) //get from database and bind to GV { dtItemRecords = osdb.Get_ItemRecords(tbSearchItemName.Text).Tables[0]; ViewState[viewStateGVName] = dtItemRecords; } else { //retrive the view state object data table from previous retrival dtItemRecords = ViewState[viewStateGVName] as DataTable; } if (dtItemRecords != null) { gvItem.Columns[GVCHKBOX].Visible = true; gvItem.DataSource = ViewState[viewStateGVName]; gvItem.AllowSorting = true; gvItem.DataBind(); } else { dtTemplateRecords.Rows.Add(dtItemRecordss.NewRow()); ViewState[viewStateGVName] = dtItemRecords; gvItem.AllowSorting = false; gvItem.DataSource = ViewState[viewStateGVName]; gvItem.DataBind(); //hide the checkbox and edit columns gvItem.Columns[GVCHKBOX].Visible = false; gvItem.Columns[GVEDITBTN].Visible = false; int TotalColumns = dtItemRecords.Rows[0].Cells.Count; gvItem.Rows[0].Cells.Clear(); gvItem.Rows[0].Cells.Add(new TableCell()); gvItem.Rows[0].Cells[0].ColumnSpan = TotalColumns; gvItem.Rows[0].Cells[0].Text = "No Record Found"; } } }}