Asp.net datalist pagging with LINQ SQL

Code Behind :

// Page load implementation :

protected void Page_Load(object sender, System.EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                string pagenumber = Request.QueryString["pagenumber"] as string;
                if (!string.IsNullOrEmpty(pagenumber))
                    CurrentPageNumber = Convert.ToInt32(pagenumber);
}
}



 void LoadUserDetails()
        {
            int totalRows = 0;
            using (AdminDTO dto = new AdminDTO())
            {
                dlUserdetails.DataSource = dto.GetAllUsers(CurrentPageNumber, defaultPageSize, ref totalRows, Convert.ToInt32(dlType.SelectedItem.Value));
                dlUserdetails.DataBind();
            }
            TotalPaggingRecords = totalRows;
        }


// the property

public int CurrentPageNumber
        {
            get
            {
                // look for current page in ViewState
                object o = this.ViewState["_CurrentPage"];
                if (o == null)
                    return 1; // default page index of 0
                else
                    return (int)o;
            }

            set
            {
                this.ViewState["_CurrentPage"] = value;
            }
        }

now lets look at how to page data using LINQ, the following method calls at linq  , the pagging tricks is in  .Skip((pageNumber - 1) * pageSize).Take(pageSize)

dto.GetAllUsers(CurrentPageNumber, defaultPageSize, ref totalRows, Convert.ToInt32(dlType.SelectedItem.Value)


public List GetAllUsers(int pageNumber, int pageSize, ref int TotalRows, int dType)
        {
            List users = new List();

            using (var context = new DBETNetEntities())
            {
           
                    TotalRows = context.UserInfoes
                        .OrderByDescending(ui => ui.CreateDate)
                                            .ToList().Count;

                    users = context.UserInfoes
                        .OrderByDescending(ui => ui.CreateDate)
                         .Skip((pageNumber - 1) * pageSize).Take(pageSize)
                        .ToList();
}
}

now create a function that will page the data based on page number and rowindex, probably you can make it more genric


 public string GetPageNavigation(int currentPageNumber, int pageSize, int totalRecords)
        {

            decimal _pages = (decimal)totalRecords / (decimal)pageSize;
            int pagecount = Convert.ToInt32(Math.Ceiling(_pages)); //Math.Max(_pages);

            StringBuilder pageStr = new StringBuilder();
            pageStr.Append("< table >< tr>");
            pageStr.Append(string.Format("< td >Page {0} of< /td >", CurrentPageNumber));
            pageStr.Append(string.Format("< td >Records {0} < /td >", TotalPaggingRecords));
            pageStr.Append("< td style='background-color:#ccc;width:18px;'> < /td>");

            for (int i = 1; i <= pagecount; i++)
            {
                if (i == currentPageNumber)
                    pageStr.Append(string.Format("< td style='background-color:#000;color:#fff;border:1px solid #e7e7e7;' >{0}< /td>", i));
                else
                    pageStr.Append(string.Format("< td style='background-color:#fff;border:1px solid #e7e7e7;' > < a href='ControlPanel.aspx?pagenumber={0}' >{1}< /a>< /td>", i, i));

                if (i >= 25)
                {
                    pageStr.Append(string.Format("< td style='background-color:#fff;border:1px solid #e7e7e7;'> < a href='ControlPanel.aspx?pagenumber={0}' >Next< /a >< /td >", (currentPageNumber + 1)));
                    break;
                }
            }

            pageStr.Append("< /tr>< /table >");
            return pageStr.ToString();
        }

Pagging is done, now lets look how to call the pagging from html implementation

< div style="margin: 2px; padding: 4px; border: 1px solid #ccc; background-color: #f5f5f5;" >
            < %=GetPageNavigation(CurrentPageNumber, defaultPageSize, TotalPaggingRecords)% >
        < /div >

Add, Update with Entity Framework 5

CRUD sample with Entity Framework 5.

in EF 5 there is something called DBContext, which is little different than earlier ObjectContext, DBContext generate less code to achieve the same functionality, and is based on code first principle

this is how the dbcontext file look like and connection string to be set

public partial class GoldEntities : DbContext
    {
        static string secureConnectionString = Util.EntityFrameworkConnectionString;

        public GoldEntities()
            : base(secureConnectionString)
        {
        }
}


ADD :

public bool AddDesignType(tbProductType designType)
        {
            bool result = false;
            using (var context = new GoldEntities())
            {
                context.tbProductTypes.Add(designType);
                context.SaveChanges();
                result = true;
            }
            return result;
        }

UPDATE using Entityframework 5:
to update an entity you wont find applychanges method, so here is how you can update an entity.

public bool UpdateColorStoneName(tbColorStoneName colorStoneName)
        {
            bool isSaved = false;
            using (var context = new GoldEntities())
            {
                tbColorStoneName csn = context.tbColorStoneNames
                    .Where(d => d.ColorStoneNameId == colorStoneName.ColorStoneNameId)
                    .FirstOrDefault();

                if (csn != null)
                {
                    context.Entry(csn).State = EntityState.Modified;
                    context.SaveChanges();
                    isSaved = true;
                }
            }
            return isSaved;
        }