DL(DAL)

STEP 1: Create a class file like NewsAndArticlesDL.cs

STEP 2:

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

namespace VCSB.INTRANET.DAL
{
    public class NewsAndArticlesDL
    {

        public static bool insert(NewsAndArticlesBL newsAndArticlesBL)
        {
            bool insert = false;
            using (VCSBINTRANETDataContext context = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
            {
             
                NewsAndArticleItem newsAndArticleItem = new NewsAndArticleItem() {
                    Title=newsAndArticlesBL.NewsTitle,
                    NewsDescription=newsAndArticlesBL.Newsdescription,
                    NewsArticleDate=newsAndArticlesBL.Newsdate,
             };


                context.NewsAndArticle.InsertOnSubmit(newsAndArticleItem);
                context.SubmitChanges();
                if (newsAndArticleItem.Id > 0)
                {
                    insert = true;
                    if (newsAndArticlesBL.NewsfilePath.FileName != null && newsAndArticlesBL.NewsfilePath.FileName != string.Empty)
                    {
                        if (InsertAttachment(Convert.ToInt32(newsAndArticleItem.Id), newsAndArticlesBL.NewsfilePath))
                        {
                            insert = true;
                        }
                    }
                    else
                    {
                        insert = true;
                    }
                }
            }
            return insert;
        }
        public static bool update(NewsAndArticlesBL newsAndArticlesBL)
        {
            bool update = false;
            using (VCSBINTRANETDataContext context = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
            {
                var itemUpdate = (from r in context.NewsAndArticle
                                  where r.Id == newsAndArticlesBL.ID
                                  select r).First();
                itemUpdate.Title = newsAndArticlesBL.NewsTitle;
                itemUpdate.NewsDescription = newsAndArticlesBL.Newsdescription;
                itemUpdate.NewsArticleDate = newsAndArticlesBL.Newsdate;
                context.SubmitChanges();
                if (!string.IsNullOrEmpty(Convert.ToString(newsAndArticlesBL.NewsfilePath)))
                {
                    string imagepath  =Convert.ToString(GetNewsImageAttachment(Convert.ToInt32(newsAndArticlesBL.ID)));
                     DeleteAttachment(Convert.ToInt32(newsAndArticlesBL.ID), imagepath);
                     if (InsertAttachment(Convert.ToInt32(newsAndArticlesBL.ID), newsAndArticlesBL.NewsfilePath))
                     {
                         update = true;
                     }
                }   
            }
            return update;
        }
        public static bool InsertAttachment(int listid, System.Web.UI.WebControls.FileUpload flpupload)
        {
                SPWeb oWeb = SPContext.Current.Site.RootWeb;
                SPList oList = oWeb.Lists["NewsAndArticle"];
                SPListItem item = oList.GetItemById(listid);

                string[] chkimage = flpupload.FileName.ToString().Split('.');

                if (chkimage[1] == "jpg" || chkimage[1] == "jpeg" || chkimage[1] == "png" || chkimage[1] == "gif")
                {
                    item.Attachments.Add(flpupload.FileName, flpupload.FileBytes);
                    item.Update();
                    return true;
                }
                else
                {
                    return false;
                }
           
        }
        public static bool DeleteAttachment(int listid,string imagepath)
        {
            SPWeb oWeb = SPContext.Current.Site.RootWeb;
                SPList oList = oWeb.Lists["NewsAndArticle"];
                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 bool delete(NewsAndArticlesBL newsAndArticlesBL)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (VCSBINTRANETDataContext datacontxt = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
                {
                    var itemDelete = (from deletequery in datacontxt.NewsAndArticle
                                      where deletequery.Id == Convert.ToInt32(newsAndArticlesBL.ID)
                                      select deletequery).First();

                    datacontxt.NewsAndArticle.DeleteOnSubmit(itemDelete);
                    datacontxt.SubmitChanges();
                }
            });
            return true;
        }
        private static object GetNewsImageAttachment(int listid)
        {
            string URL = null;
           
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                SPWeb oWeb = SPContext.Current.Site.RootWeb;
                SPList oList = oWeb.Lists["NewsAndArticle"];
                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] == "jpg" || chkimage[1] == "jpeg" || chkimage[1] == "png" || chkimage[1] == "gif" || chkimage[1] == "bmp")
                            {
                               
                                URL = (SPEncode.HtmlEncode(item.Attachments.UrlPrefix + attachment.ToString()));
                                                                                               
                            }
                        }
                    }
                }     
               
            });
            if (URL != null)
            {
                return URL;
            }
            else
            {

                return URL = @"~\_Layouts\Images\VCSB\photo_not_avl.png";
            }
        }


        #region get limited string
        public static object GetLimitedString(string text, int len)
        {
            string result;
            int maxLength = text.Length;
            if (maxLength > len)
            {
                maxLength = len;
                string tempString = text.Substring(0, maxLength);
                int trimOffset = tempString.LastIndexOf(" ");

                if (trimOffset > 0)
                {
                    result = tempString.Substring(0, trimOffset) + "...";
                }
                else
                {
                    result = tempString.Substring(0, maxLength) + "...";
                }
            }
            else
            {
                result = text;
            }
            return result;
        }
        #endregion

        public static System.Data.DataTable GetSingleRowListItem(NewsAndArticlesBL newsAndArticlesBL)
        {
          
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("NewsTitle");
            dt.Columns.Add("NewsDate");
            dt.Columns.Add("NewsDescription");
            dt.Columns.Add("NewsImage");
            DataRow row;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (VCSBINTRANETDataContext context = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
                {
                    var quer = from NewsArticle in context.NewsAndArticle
                                where NewsArticle.Id== newsAndArticlesBL.ID
                               select NewsArticle;

                    if (quer != null)
                    {
                        foreach (NewsAndArticleItem item in quer)
                        {
                            row = dt.NewRow();
                            row["ID"] = Convert.ToString(item.Id);
                            row["NewsTitle"] = Convert.ToString(item.Title);
                            row["NewsDate"] = Convert.ToString(item.NewsArticleDate.Value.ToShortDateString());
                            row["NewsDescription"] = Convert.ToString(item.NewsDescription);
                            row["NewsImage"] = GetNewsImageAttachment(Convert.ToInt32(item.Id));
                            dt.Rows.Add(row);

                        }
                    }
                }
            });
            return dt;
        }

        public static System.Data.DataTable GetNewsAndArticleData(NewsAndArticlesBL newsAndArticlesBL)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("NewsTitle");
            dt.Columns.Add("NewsDate");
            dt.Columns.Add("NewsDescription");
            DataRow row;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (VCSBINTRANETDataContext context = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
                {
                    var quer = from NewsArticle in context.NewsAndArticle
                               orderby NewsArticle.Id descending
                               select NewsArticle;

                    if (quer != null)
                    {
                        foreach (NewsAndArticleItem item in quer.Take(2))
                        {
                            row = dt.Rows.Add();
                            row["ID"] = Convert.ToString(item.Id);
                            row["NewsTitle"] = Convert.ToString(item.Title);
                            row["NewsDate"] = Convert.ToString(item.NewsArticleDate.Value.ToShortDateString());
                            row["NewsDescription"] = GetLimitedString(Convert.ToString(item.NewsDescription), 100);

                        }
                    }
                }
         
            });
            return dt;
              
        }
        public static System.Data.DataTable GetAllListItem(NewsAndArticlesBL newsAndArticlesBL)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("NewsTitle");
            dt.Columns.Add("NewsDate");
            dt.Columns.Add("NewsDescription");
            dt.Columns.Add("NewsImage");
            DataRow row;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (VCSBINTRANETDataContext context = new VCSBINTRANETDataContext(SiteHelper.SiteUrl))
                {
                    var quer = from NewsArticle in context.NewsAndArticle
                               orderby NewsArticle.Id descending
                               select NewsArticle;

                    if (quer != null)
                    {
                        foreach (NewsAndArticleItem item in quer)
                        {
                            row = dt.Rows.Add();
                            row["ID"] = Convert.ToString(item.Id);
                            row["NewsTitle"] = Convert.ToString(item.Title);
                            row["NewsDate"] = Convert.ToString(item.NewsArticleDate.Value.ToShortDateString());
                            row["NewsDescription"] =  Convert.ToString(item.NewsDescription);
                            row["NewsImage"] = GetNewsImageAttachment(Convert.ToInt32(item.Id));

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