Select or Get Data Code

Simple with sorting, Select or Get Data Code

 Step by Step:

1.  Call logic layer with appropriate parameter

(a). ClientId or other if required by logic.

(b). ViewState["SortBy"] ,which set before For Column name for Sorting.

(c). ViewState["SortAs"],which set before for Sorting as  asc(ascending) or desc(descending) order for column sorting.

(d). ViewState["SearchExprBy"], which set before for value ,which is takeb by search text box means search date by inserting name.

2.  First thing is that I am not using property here due to some search sort.

3. Take these value in Logic Layer method and send them to Data Class’s Mehod.

4. In DataClass take these value in assign to SqlParameter[] as before.

5. Send this SqlParameter[] and Store Procedure name to Mail DataClass for taking Dataset.

6. All method from first step has returen type will be Dataset.

//BindGrid for taking Dataset and Formating GridView such as total records etc.

public void BindGrid(string orderby, string strVal)  //BingGrid

    {

       

        try

        {

ClsEvent objEvent = new ClsEvent(); //Logic layer

            DataSet ds = objEvent.GetEvent

(Convert.ToInt32(Session["clientId"]),

ViewState["SortBy"].ToString(),

ViewState["SortAs"].ToString(),

ViewState["SearchExprBy"].ToString());

          

            Int32 intCnt;

            intCnt = ds.Tables[0].Rows.Count;

            if (ds.Tables[0].Rows.Count > 0)

            {

               

                lblTotalRecords.Text = "Total Records : " + intCnt;

                DataView dv = (DataView)ds.Tables[0].DefaultView;

                GVUSer.DataSource = ds;

                GVUSer.DataBind();

                lblGoto.Visible = true;

                txtPage.Visible = true;

                imgbtnGo.Visible = true;

            }

            else

            {

                lblTotalRecords.Text = "";

                GVUSer.DataBind();

                lblGoto.Visible = false;

                txtPage.Visible = false;

                imgbtnGo.Visible = false;

            }

        }

        catch (Exception ex)

        {

            lblError.Text = "<b>Following Error Found<p>" + ex + "</p></b>";

        }

    }

//Logic Layer

public DataSet GetEvent(int ClientId, string SortBy, string SortAs, string SearchExprBy)

    {

        DataSet ds = new DataSet();

       ClsEventDB objDB = new ClsEventDB();

        ds = objDB.GetEvent(ClientId, SortBy, SortAs, SearchExprBy);

        return ds;

    }

//Data Layer

internal DataSet GetEvent(int ClientId, string SortBy, string SortAs, string SearchExprBy)

    {

        SqlParameter[] param ={

                                new SqlParameter ("@SortBy", SortBy),

                                new SqlParameter ("@SortAs", SortAs),

                                new SqlParameter ("@Search", SearchExprBy),

                                new SqlParameter ("@ClientID", ClientId),

                           };

        DataSet ds = new DataSet();

        ds = ClsDataLayer.GetDataSet("[sp_Get_Event]", param);

        return ds;

    }

//Main Data Class

public static DataSet GetDataSet(string ProcName, SqlParameter[] param)

    {

        SqlConnection cn = GetConnection();

        DataSet Ds = new DataSet();

        SqlDataAdapter ObjAdapter = new SqlDataAdapter(ProcName, cn);       

        ObjAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

        ObjAdapter.SelectCommand.CommandTimeout = 0;

             

              try

        {

                     cn.Open();

                     foreach (SqlParameter par in param)

                     {

                           ObjAdapter.SelectCommand.Parameters.Add(par);

                     }

                     ObjAdapter.Fill(Ds);

            cn.Close();

        }

        catch (Exception Ex)

        {

            throw Ex;

        }

        finally

        {            

                     cn.Close();

              cn.Dispose();

        }

        return Ds;

    }

When you don’t send parameter and just take value :

Step by Step:

<![if !supportLists]>1.       <![endif]>Don’t Send anything in Mehod

<![if !supportLists]>2.       <![endif]>In DataClass send Only Procedure Name.

<![if !supportLists]>3.       <![endif]>Best Example take value of Country and Regions.

Code For Main DataLayer.

public static DataSet GetDS(string ProcName)

    {       

        DataSet ds = new DataSet();

        SqlConnection cn = GetConnection();

        using (cn)

        {

                  try

                  {

                        SqlDataAdapter da = new SqlDataAdapter(ProcName, cn);

                        da.SelectCommand.CommandType = CommandType.StoredProcedure;

                        da.SelectCommand.CommandTimeout = 0;

                        da.Fill(ds);

                cn.Close();

                cn.Dispose();

                  }

                  catch (Exception Ex)

                  {

                        throw Ex;

                  }

                  finally

                  {

                        cn.Close();

                        cn.Dispose();

                  }          

            return ds;

        }

    }

Help