DAL(Alter)

STEP 1: Create a class file like AppsDL.cs



using System;
using System.Web.UI.WebControls;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using System.Data;
using VCSB.INTRANET.BLL;

namespace VCSB.INTRANET.DAL
{
    public class AppsDL
    {
        #region GetAppsListItem
        public static  void GetAppsListItem(DataList Apps)
        {


            SPSite spsite = SPContext.Current.Site;
            SPWeb spweb = spsite.OpenWeb();
            SPList spList = spweb.Lists["AppsCategory"];
            SPList spList1 = spweb.Lists["Apps"];
            SPListItemCollection spListItems = spList.Items;
            SPListItemCollection spListItems1 = spList1.Items;

            //var query1 = from SPListItem pi in spList.Items
            //             from SPListItem ci in spList1.Items
            //             where pi.Title.ToString().Equals(new SPFieldLookupValue(ci["Category"].ToString()).LookupValue)
            //             select new { Parent = pi, Child = ci };


            using (VCSBINTRANETDataContext context = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
            {


                IQueryable<string> query1 = from Appsitem in context.Apps
                                            orderby Appsitem.Category.Id ascending
                                          select Appsitem.Category.Title;
                IQueryable<string> distinctNames = query1.Distinct();      
           
            DataTable dd1 = new DataTable();
            dd1.Columns.Add("Title");
            DataRow row;
            foreach (var grp in  distinctNames)
            {
                row = dd1.Rows.Add();
                row["Title"] = Convert.ToString(grp);
            }
            Apps.DataSource = dd1;
            Apps.DataBind();
        }

            var query = from SPListItem pi in spList.Items
                        from SPListItem ci in spList1.Items
                        where pi.Title.ToString().Equals(new SPFieldLookupValue(ci["Category"].ToString()).LookupValue)
                        select new { Parent = pi, Child = ci };

            DataTable ds1 = new DataTable();
            ds1.Columns.Add("Title");
            ds1.Columns.Add("Category");
            ds1.Columns.Add("AppsUrl");
            DataRow row1;
            foreach (var grp1 in query)
            {
                row1 = ds1.Rows.Add();
                    row1["Title"] = Convert.ToString(grp1.Parent.Title);
                    row1["Category"] = Convert.ToString(grp1.Child.Title);
                    row1["AppsUrl"] = Convert.ToString(grp1.Child["AppsURL"]);
             
            }
            DataSet test = new DataSet();
            test.Tables.Add(ds1);

            foreach (DataListItem item in Apps.Items)
            {
                string dataTitle = Convert.ToString(Apps.DataKeys[item.ItemIndex]);
                DataList gvr = (DataList)Apps.Items[item.ItemIndex].FindControl("Appscat");
                DataTable dt = new DataTable();
                dt.Columns.Add("Category");
                dt.Columns.Add("AppsUrl");
               

                foreach (DataRow item1 in test.Tables[0].Rows)
                {
                    DataRow drr = dt.NewRow();
                  

                        if (Convert.ToString(item1["Title"]) == dataTitle)
                        {
                            drr["Category"] = item1["Category"].ToString();
                            drr["AppsUrl"] = Convert.ToString(item1["AppsUrl"]);
                            dt.Rows.Add(drr);
                        }
                   
                }
                gvr.DataSource = dt;
                gvr.DataBind();
            }

        }
        #endregion
    }
}