DL(DAL)

STEP 1: Create a class file like PropellerStudioDL.cs

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


namespace VCSB.INTRANET.DAL
{
    public class PropellerStudioDL
    {
       
        public static bool insert(PropellerStudioBL propellerStudioBL)
        {
          
            bool insert = false;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (VCSBINTRANETDataContext context = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
                {
                    PropellerStudioItem propellerStudioItem = new PropellerStudioItem()
                    {
                        Title = propellerStudioBL.Propellertitle,
                        Description = propellerStudioBL.Description,

                    };
                    context.PropellerStudio.InsertOnSubmit(propellerStudioItem);
                    context.SubmitChanges();

                    if (propellerStudioItem.Id > 0)
                    {
                        insert = true;
                        if (propellerStudioBL.FilePath.FileName != null && propellerStudioBL.FilePath.FileName != string.Empty)
                        {
                            if (InsertAttachment(Convert.ToInt32(propellerStudioItem.Id), propellerStudioBL.FilePath))
                            {
                                insert = true;
                            }
                        }
                        else
                        {
                            insert = true;
                        }
                    }
                }
            });
            return insert;
        }

        private static bool InsertAttachment(int listid, FileUpload fldupload)
        {
           
                SPWeb oWeb = SPContext.Current.Site.RootWeb;
                SPList oList = oWeb.Lists["PropellerStudio"];
                SPListItem item = oList.GetItemById(listid);

               
                string[] chkimage = fldupload.FileName.ToString().Split('.');
               
                    if (chkimage[1] == "doc" || chkimage[1] == "docx" || chkimage[1] == "xls" || chkimage[1] == "xlsx" || chkimage[1] == "ppt" || chkimage[1] == "pptx" || chkimage[1] == "txt" || chkimage[1] == "pdf")
                    {
                        item.Attachments.Add(fldupload.FileName, fldupload.FileBytes);
                        item.Update();
                        return true;
                    }
                   
                else
                {
                    return false;
                }
              
        }
        public static bool update(PropellerStudioBL propellerStudioBL)
        {
            bool update = false;
            SPSecurity.RunWithElevatedPrivileges(delegate()
           {
                using (VCSBINTRANETDataContext datacontxt = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
                {
                   

                     var itemUpdate = (from updateequery in datacontxt.PropellerStudio
                                       where updateequery.Id == Convert.ToInt32(propellerStudioBL.ID)
                                       select updateequery).First();

                     itemUpdate.Title = propellerStudioBL.Propellertitle;
                     itemUpdate.Description = propellerStudioBL.Description;
                     datacontxt.SubmitChanges();
                     if (!string.IsNullOrEmpty(Convert.ToString(propellerStudioBL.FilePath)))
                     {
                         string imagepath = Convert.ToString(GetListAttachmentsFile(Convert.ToInt32(propellerStudioBL.ID)));
                         DeleteAttachment(Convert.ToInt32(propellerStudioBL.ID), imagepath);
                         if (InsertAttachment(Convert.ToInt32(propellerStudioBL.ID), propellerStudioBL.FilePath))
                         {
                             update = true;
                         }
                     }   
                }
           });

            return update;
        }

        public static bool DeleteAttachment(int listid, string imagepath)
        {
            SPWeb oWeb = SPContext.Current.Site.RootWeb;
            SPList oList = oWeb.Lists["PropellerStudio"];
            SPListItemCollection oListItems = oList.Items;
            foreach (SPListItem item in oListItems)
            {
                if ((int)item["ID"] == Convert.ToInt32(listid))
                {
                    SPAttachmentCollection collAttachments = item.Attachments;
                    if (collAttachments.Count > 0)
                    {
                        foreach (var attachment in collAttachments)
                        {
                            item.Attachments.Delete(attachment.ToString());
                            if (collAttachments.Count == 0)
                            {
                                break;
                            }

                        }
                        item.Update();
                    }
                }
            }

            return true;

        }
        public static System.Data.DataTable GetAllListItem(PropellerStudioBL propellerStudioBL)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("Title");
            dt.Columns.Add("FileImage");
            dt.Columns.Add("Description");
            dt.Columns.Add("FilePathUrl");

            DataRow row;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (VCSBINTRANETDataContext context = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
                {
                    var quer = from propellerda in context.PropellerStudio
                               orderby propellerda.Id descending
                               select propellerda;

                    if (quer != null)
                    {
                        foreach (PropellerStudioItem item in quer)
                        {
                            row = dt.Rows.Add();
                            row["ID"] = item.Id.ToString();
                            row["Title"] = item.Title.ToString();
                            row["Description"] = item.Description.ToString();
                            row["FileImage"] = GetListAttachments(Convert.ToInt32(item.Id));
                            row["FilePathUrl"] = GetListAttachmentsFile(Convert.ToInt32(item.Id));
                        }
                    }
                }
                //grd.DataSource = dt;
                //grd.DataBind();
            });
            return dt;
        }


        public static System.Data.DataTable GetPropellerData(PropellerStudioBL propellerStudioBL)
        {
            DataTable dt = new DataTable();
              dt.Columns.Add("Title");
               dt.Columns.Add("FileImage");
               dt.Columns.Add("FilePathUrl");
                DataRow row;
                SPSecurity.RunWithElevatedPrivileges(delegate()
              {
                  using (VCSBINTRANETDataContext context = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
                  {
                      var quer = from propellerda in context.PropellerStudio
                                 orderby propellerda.Id descending
                                 select propellerda;

                      if (quer != null)
                      {
                          foreach (PropellerStudioItem item in quer.Take(3))
                          {
                              row = dt.Rows.Add();
                              row["Title"] = item.Title.ToString();
                              row["FileImage"] = GetListAttachments(Convert.ToInt32(item.Id));
                              row["FilePathUrl"] = GetListAttachmentsFile(Convert.ToInt32(item.Id));
                          }
                      }
                  }
              });
            return dt;
        }
     
        private static object GetListAttachmentsFile(int listid)
        {
            string URL = null;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                SPWeb oWeb = SPContext.Current.Site.RootWeb;
                SPList oList = oWeb.Lists["PropellerStudio"];
                SPListItemCollection oListItems = oList.Items;
                foreach (SPListItem item in oListItems)
                {
                    if ((int)item["ID"] == Convert.ToInt32(listid))
                    {
                        SPAttachmentCollection collAttachments = item.Attachments;
                        foreach (var attachment in collAttachments)
                        {
                            string[] chkimage = attachment.ToString().Split('.');
                            if (chkimage[1] == "doc" || chkimage[1] == "docx" || chkimage[1] == "xls" || chkimage[1] == "xlsx" || chkimage[1] == "ppt" || chkimage[1] == "pptx" || chkimage[1] == "txt" || chkimage[1] == "pdf")
                            {
                                URL = (SPEncode.HtmlEncode(item.Attachments.UrlPrefix + attachment.ToString()));
                            }
                        }
                    }
                }
            });
                if (URL != null)
                {
                    return URL;
                }
                else
                {
                    return URL = "";
                }
           
        }

        private static object GetListAttachments(int listid)
        {
            string URL = null;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                SPWeb oWeb = SPContext.Current.Site.RootWeb;
                SPList oList = oWeb.Lists["PropellerStudio"];
                SPListItemCollection oListItems = oList.Items;
                foreach (SPListItem item in oListItems)
                {
                    if ((int)item["ID"] == Convert.ToInt32(listid))
                    {
                      
                        SPAttachmentCollection collAttachments = item.Attachments;
                        foreach (var attachment in collAttachments)
                        {
                            string[] chkimage = attachment.ToString().Split('.');
                            if (chkimage[1] == "doc" || chkimage[1] == "docx")
                            {
                                URL = @"~\_Layouts\Images\VCSB\doc.png";
                                //URL = (SPEncode.HtmlEncode(item.Attachments.UrlPrefix + attachment.ToString()));
                            }
                            else if (chkimage[1] == "pdf")
                            {
                                URL = @"~\_Layouts\Images\VCSB\pdf.png";
                            }
                            else if (chkimage[1] == "xls" || chkimage[1] == "xlsx")
                            {
                                URL = @"~\_Layouts\Images\VCSB\xls.png";
                            }
                            else if (chkimage[1] == "ppt" || chkimage[1] == "pptx")
                            {
                                URL = @"~\_Layouts\Images\VCSB\ppt.png";
                            }
                            else if (chkimage[1] == "txt")
                            {
                                URL = @"~\_Layouts\Images\VCSB\txt.png";
                            }
                        }
                       
                    }
                }
            });
            if (URL != null)
            {
                return URL;
            }
            else
            {

                return URL = @"~\_Layouts\Images\VCSB\doc_not_av2.png";
            }
        }
        public static bool delete(PropellerStudioBL propellerStudioBL)
        {
          
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (VCSBINTRANETDataContext datacontxt = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
                {
                     var itemDelete = (from deletequery in datacontxt.PropellerStudio
                                       where deletequery.Id == Convert.ToInt32(propellerStudioBL.ID)
                                       select deletequery).First();

                     datacontxt.PropellerStudio.DeleteOnSubmit(itemDelete);
                     datacontxt.SubmitChanges();
                }
            });
            return true;
        }

        public static System.Data.DataTable GetSingleRowListItem(PropellerStudioBL propellerStudioBL)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("Title");
            dt.Columns.Add("Description");
            dt.Columns.Add("FileImage");
            dt.Columns.Add("FilePathUrl");
            DataRow row;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (VCSBINTRANETDataContext context = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
                {
                    var quer = from PropellerStudioData in context.PropellerStudio
                               where PropellerStudioData.Id == propellerStudioBL.ID
                               select PropellerStudioData;

                    if (quer != null)
                    {
                        foreach (PropellerStudioItem item in quer)
                        {
                            row = dt.NewRow();
                            row["ID"] =Convert.ToString(item.Id);
                            row["Title"] =Convert.ToString(item.Title);
                            row["Description"] = Convert.ToString(item.Description);
                            row["FileImage"] = GetListAttachments(Convert.ToInt32(item.Id));
                            row["FilePathUrl"] = GetListAttachmentsFile(Convert.ToInt32(item.Id));
                            dt.Rows.Add(row);

                        }
                    }
                }
            });
            return dt;
        }
    }
}