Tag: LINQ

Linq.Binary(SQL Image Data Type) Type to File Stream

On October 3, 2008

I got a column with Data Type of Image in SQL Server. What I would like to do is to omit this data to a FileStream or to a file. I tried to read the data using LINQ and I found the data type detected for that particular column is System.Data.Linq.Binary. I was expecting it to be Byte Data type. So I need to convert the Binary to byte then to File Stream. But I found a simpler way by using “ToArray” properties and cast it back to byte solves my problem.

    foreach (tblExpReceiptFile file in ExpReceiptFactory.tblExpReceiptFileSelect())
            {
                string fileName = file.vcFileName.Replace(" ","_");
                FileStream fileStream = File.Create(DirectoryPath + @"\" + fileName + ".pdf");
                fileStream.Write((byte[])file.imgFileContent.ToArray(), 0, ((byte[])file.imgFileContent.ToArray()).Length);
                fileStream.Close();
            }

SQL Methods in LINQ

By fransiscuss

On October 2, 2008

In .NET, C#, SQL Server

I’m trying to implement DateDiff in LINQ. I would like to get all records with the same date and ignoring the timestamp.

Here is my code snippet which doesn’t work

 public int tblExpReceiptFileUniqueID(string chCreateStaffCode, string vcFileName,
                                                long intFileSize, DateTime? sdCreateDate)
        {
            var expReceiptFile = from ef in DataContext.tblExpReceiptFiles orderby ef.intFileID
                                 where ef.chCreateStaffCode == chCreateStaffCode
                                 && ef.vcFileName == vcFileName
                                 && ef.bintFileSize == intFileSize
                                 && ((DateTime)ef.sdCreateDate).Date == sdCreateDate.Value.Date
                                 select ef;

            tblExpReceiptFile expReceiptFileRec = expReceiptFile.First();

            return expReceiptFileRec.intFileID;
        }

I’m trying to cast the column to “DateTime” and use the “Date” property but I got this error
“The Member ‘System.DateTime.Date’ has no supported translation to SQL”

I thought of “DATEDIFF” function in SQL server and I’m trying to get my head around in implementing this using LINQ and I found that we can use a library called “SQLClient”

using System.Data.Linq.SqlClient;

Code Snippet which is working

    public int tblExpReceiptFileUniqueID(string chCreateStaffCode, string vcFileName,
                                                long intFileSize, DateTime? sdCreateDate)
        {
            var expReceiptFile = from ef in DataContext.tblExpReceiptFiles orderby ef.intFileID
                                 where ef.chCreateStaffCode == chCreateStaffCode
                                 && ef.vcFileName == vcFileName
                                 && ef.bintFileSize == intFileSize
                                 && SqlMethods.DateDiffDay(ef.sdCreateDate, sdCreateDate.Value.Date) == 0
                                 select ef;

            tblExpReceiptFile expReceiptFileRec = expReceiptFile.First();

            return expReceiptFileRec.intFileID;
        }

Paging in Order BY using LINQ

By fransiscuss

On August 29, 2008

In Uncategorized

This is a simple way of doing the paging in LINQ. If I do the paging before the ORDER BY then it will give me different result set. The paging is should be after the ORDER BY Statement. So you sort first then you paging it.

        /// 
        /// to get all the have your say using paging based on optional parameter
        /// of isapproved and sorted descending based on the posted date
        /// 
        /// 
        /// 
        /// 
        /// 
        public IQueryable HaveYourSaySelectApproved(bool? IsApproved, int startPage, int pageSize)
        {
            var haveyoursay = from h in HaveYourSayContext.HaveYourSays
                                      orderby h.DatePosted descending
			select h;

            if (IsApproved.HasValue)
            {
                haveyoursay = from h in HaveYourSayContext.HaveYourSays
                              where (h.IsApproved == IsApproved.Value)
                              orderby h.DatePosted descending
                              select h;
            }

	return haveyoursay.Skip(startPage * pageSize).Take(pageSize);
        }

Simple LINQ Tutorial

By fransiscuss

On August 29, 2008

In C#

I’ve started learning about LINQ because I need to keep up to date with the latest technology out there. I’ve created a simple Business Layer which interact directly with Linq to SQL(DBML). It includes Insert, Update, Delete, Select and paging with LINQ.

Hopefully this tutorial will be useful enough for someone who is going to learn about LINQ. With LINQ we can really simplify/integrate the stored procedure into our Code base but it doesn’t mean LINQ does not support Stored Procedure.

It supports Stored Procedure as well since we might use Stored Procedure for complex calculation. You don’t need to create a table adapter anymore since LINQ does everything the same as table adapter.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqCore.Factories
{
    public class GroupFactory
    {

        /// 
        /// this is used to get all the groups
        /// 
        /// 
        public IQueryable GetAllGroups()
        {
            GroupsDataContext db = new GroupsDataContext();
            var groups = from g in db.Groups
                         select g;

            return groups;
        }

        /// 
        /// this is used to get the group based on the group id
        /// 
        /// 
        /// 
        public Group GetGroup(int groupid)
        {
            GroupsDataContext db = new GroupsDataContext();

            var groups = from g in db.Groups
                         where (g.GroupID == groupid)
                         select g;

            return groups.SingleOrDefault();
        }

        /// 
        /// this is used to insert Or Update which determined by the nullable
        /// integer value of the groupID
        /// 
        /// 
        /// 
        /// 
        public void GroupUpdate(int? groupID, string groupName, bool valid)
        {
            GroupsDataContext db = new GroupsDataContext();
            Group group = new Group();

            if (groupID.HasValue)
            {
               group = db.Groups.Single(p => p.GroupID == groupID.Value);
            }

            group.GroupName = groupName;
            group.valid = valid;

            if (!groupID.HasValue)
            {
                db.Groups.InsertOnSubmit(group);
            }
            db.SubmitChanges();
        }

        /// 
        /// this is used to delete a group based on its ID
        /// 
        /// 
        public void GroupDelete(int groupID)
        {
            GroupsDataContext db = new GroupsDataContext();

            Group group = db.Groups.Single(p => p.GroupID == groupID);
            db.Groups.DeleteOnSubmit(group);
            db.SubmitChanges();
        }

        /// 
        /// this is used to do paging for the returned result
        /// 
        /// 
        /// 
        /// 
        public IQueryable GetAllGroups(int startIndex, int pageSize)
        {
            GroupsDataContext db = new GroupsDataContext();
            var groups = from g in db.Groups
                         select g;

            return groups.Skip(startIndex).Take(pageSize);
        }
    }
}