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();
}
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;
}
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);
}
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);
}
}
}
