Displaying data retrive from database into Grid View.

 

using System;
using System.ComponentModel;

using System.Data.SqlClient;
using System.Data;
using 
System.Data.SqlTypes;

namespace com.os.database
{
    public partial class 
os_database : Component
    
{
        private SqlConnection sqlConn = 
new SqlConnection();
        string 
s_sqlConn = (string)((new 
System.Configuration.AppSettingsReader()).GetValue("sqlConn.ConnectionString", 
typeof(string)));

        public 
os_database()
        
{
            throw 
new 
System.NotImplementedException();
        
}

        public DataSet 
Get_ItemsRecords(string itemNameIn, string userloginId, string 
permission)
        
{
            
SqlCommand cmd_itemList = new SqlCommand();

            
cmd_itemList.CommandText = 
"[OS].[GET_ItemsRecords]";
            
cmd_itemList.CommandType = 
CommandType.StoredProcedure;
            
cmd_itemList.Parameters.Clear();

            
SqlParameter sqlParaName = new SqlParameter("@itemName", SqlDbType.VarChar, 
1000);
            
sqlParaName.Value = 
formatWildCard(itemNameIn);
            
cmd_itemList.Parameters.Add(sqlParaName);

            

            
SqlParameter sqlParaUserID = new SqlParameter("@userloginid", SqlDbType.VarChar, 
50);
            
sqlParaUserID.Value = 
userloginId;
            
cmd_VRMList.Parameters.Add(sqlParaUserID);

            
SqlParameter sqlParaPermission = new SqlParameter("@permission", 
SqlDbType.VarChar, 
50);
            
sqlParaPermission.Value = 
permission;
            
cmd_VRMList.Parameters.Add(sqlParaPermission);

            DataSet 
ds_itemList = 
getDataSet(cmd_itemList);
            
return ds_itemList;
        }

       

        public DataSet 
Get_AuthenticatedUserDetails(string 
userid)
        
{
            
SqlCommand cmd_UserDetails = new SqlCommand();

            
cmd_UserDetails.CommandText = 
"[OS].[GET_AuthenticatedUserDetails]";
            
cmd_UserDetails.CommandType = 
CommandType.StoredProcedure;
            
cmd_UserDetails.Parameters.Clear();
       

            return 
getDataSet(cmd_UserDetails);
        }

        

     

        private DataSet 
getDataSet(SqlCommand cmd)
        
{
            
this.sqlConn.ConnectionString = 
this.s_sqlConn;
            
cmd.Connection = 
sqlConn;
            
cmd.Connection.Open();
            
SqlDataAdapter da = new 
SqlDataAdapter(cmd);
            
DataSet ds = new 
DataSet();
            
da.Fill(ds);
            
cmd.Connection.Close();
            
return ds;
        }

        private Boolean 
executeNotQuery(SqlCommand cmd)
        
{
            
this.sqlConn.ConnectionString = 
this.s_sqlConn;
            
cmd.Connection = 
sqlConn;
            
cmd.Connection.Open();
            
cmd.ExecuteNonQuery();
            
cmd.Connection.Close();
            
return true;
        }

        /* Method to format the WildCard 
of the search to comply with the database 
wildcard
         */

        private String 
formatWildCard(String original)
        
{
            return 
original.Replace('*', '%');
        }

        public DataSet 
Get_OS_Permission(string 
userlogin)
        
{
            
SqlCommand cmd_Permission = new SqlCommand();

            
cmd_Permission.CommandText = 
"[OS].Get_OS_Permission";
            
cmd_Permission.CommandType = 
CommandType.StoredProcedure;
            
cmd_Permission.Parameters.Clear();

            
SqlParameter sqlPara = new SqlParameter("@UserLogin", SqlDbType.VarChar, 
50);
            
sqlPara.Value = 
userlogin;
            
cmd_Permission.Parameters.Add(sqlPara);

            DataSet 
ds_Permission = getDataSet(cmd_Permission);

            return 
ds_Permission;
        }

 

        public DataSet 
Get_TableColumns(String tableName)
        
{
            
SqlCommand cmd_Column = new SqlCommand();

            
cmd_Column.CommandText = 
"[OS].[GET_TableColumns]";
            
cmd_Column.CommandType = 
CommandType.StoredProcedure;
            
cmd_Column.Parameters.Clear();

            
SqlParameter sqlPara = new SqlParameter("@tableName", SqlDbType.VarChar, 
-1);
            
sqlPara.Value = 
tableName;
            
cmd_Column.Parameters.Add(sqlPara);

            DataSet 
ds_Column = 
getDataSet(cmd_Column);
            
return ds_Column;
        }

       

        public DataSet 
Get_RoleRecords(string roleDescriptionIn, SqlBoolean 
isActiveIn)
        
{
            
SqlCommand cmd_RoleList = new SqlCommand();

            
cmd_RoleList.CommandText = 
"[Common].[GET_RoleRecords]";
            
cmd_RoleList.CommandType = 
CommandType.StoredProcedure;
            
cmd_RoleList.Parameters.Clear();

            
SqlParameter sqlParaRoleDescription = new SqlParameter("@roleDescriptionIn", 
SqlDbType.VarChar, 
300);
            
sqlParaRoleDescription.Value = 
formatWildCard(roleDescriptionIn);
            
cmd_RoleList.Parameters.Add(sqlParaRoleDescription);

            
SqlParameter sqlParaIsActive = new SqlParameter("@isActiveIn", 
SqlDbType.Bit);
            
sqlParaIsActive.Value = 
isActiveIn;
            
cmd_RoleList.Parameters.Add(sqlParaIsActive);

            DataSet 
ds_RoleList = 
getDataSet(cmd_RoleList);
            
return ds_RoleList;
        }

   
    }

}

 

using System;
using System.Data;
using System.Web.UI;
using 
System.Web.UI.WebControls;
using com.vrm.database;
using com.vrm.com;

namespace onlinestore
{

    public partial class ItemList : 
System.Web.UI.Page
    
{
        private os_database osdb = new 
os_database();--This is always in error. but i have alrd define it
        private String 
viewStateGVName = "gvItem";
        

        //set the index for the columns of 
the gridview in the aspx that required formatting of 
text
        private const int GVCHKBOX = 
0;
        private const int GVEDITBTN = 
1;
        private const int GVVENDORBRN = 
2;
        private const int GVMATERIALGRP 
= 4;
        private const int GVINSAP = 
8;
        private const int 
GVSTARHUBCONTACTPERSON = 9;
        
private const int GVDELETEFLAG = 
16;
        private const int GVBLOCKED = 
17;

        //set the const for the ID of the 
GV Checkbox column
        private const 
string GVCHECKBOXCOLID = "selected";


        protected void 
Page_Load(object sender, EventArgs 
e)
        
{
            

            
ViewState[this.ToString() + "_SortExpression"] = 
"TemplateName";
            
ViewState[this.ToString() + "_SortDirection"] = "ASC";

        
}
        protected void 
btnClear_Click(object sender, EventArgs 
e)
        
{
            
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)
        
{
            string 
functionCode = 
"";
            
//determine user access rights and show or hide edit 
button
            
DataSet userAccessRights = 
vrmdb.Get_OS_Permission(SessionHandler.UserLogin);

            if 
(userAccessRights.Tables[0].Rows.Count > 
0)
            
{
                
functionCode = 
userAccessRights.Tables[0].Rows[0][0].ToString();
                
if 
(functionCode.Equals(generalconstant.VRM_VIEW_VML_ALL))
                
{

                    
gvItem.Columns[GVEDITBTN].Visible = 
false;
                
}
            }

            DataTable 
dtItemsRecords = null;

            if 
(Reload)
            
{

                
//Get from db and bind to 
datagrid
                
dtItemsRecords = vrmdb.GET_ItemsRecordss(tbSearchItemName.Text, 
SessionHandler.UserLogin, 
functionCode).Tables[0];
                
ViewState[viewStateGVName] = 
dtItemsRecords;
            
}
            
else
            
{
                
//retrieve the ViewState object datatable from previous 
retrieval
                
dtItemsRecords = ViewState[viewStateGVName] as 
DataTable;
            
}

            
dtItemsRecords.DefaultView.Sort = ViewState[this.ToString() 
+
                  
"_SortExpression"].ToString() + " " 
+
                  
ViewState[this.ToString() + "_SortDirection"].ToString();

            if 
(dtItemsRecords != 
null)
            
{
                
if (dtItemsRecords.Rows.Count > 
0)
                
{
                    
gvItem.Columns[GVCHKBOX].Visible = 
true;
                    
gvItem.DataSource = 
ViewState[viewStateGVName];
                    
gvItem.AllowSorting = 
true;
                    
gvItem.DataBind();

 

                
}
                
else
                
{
                    
dtItemsRecords.Rows.Add(dtItemsRecords.NewRow());

                    
ViewState[viewStateGVName] =  
dtItemsRecords;
                    
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 = 
gvItem.Rows[0].Cells.Count;
                    
gvItem.Rows[0].Cells.Clear();
                    
gvItem.Rows[0].Cells.Add(new 
TableCell());
                    
ggvItem.Rows[0].Cells[0].ColumnSpan = 
TotalColumns;
                    
gvItem.Rows[0].Cells[0].Text = "No Record Found";

 


                
}
            }


        }
    }

}