Linq-to-SQL, Linq-to-DataSet and Linq-to-Entities

LINQ to SQL is an O/RM (object relational mapping) implementation that was provided in the .NET Framework “Orcas” (.NET Framework version 3.5) release.

It allows us to model a relational database using .NET classes and query those models using LINQ notation / syntax. We can query the database and perform all operations like update, insert and delete etc.

As per MSDN: ” It provides a run-time infrastructure for managing relational data as objects”

Linq-to-DataSets is just LINQ, but it is used to query against the ADO.NET DataSets. As per MSDN : “LINQ to DataSet makes it easier and faster to query over data cached in a DataSet object”.

Linq-to-Entities is better and provides more flexibility and is usable with many types of data sources and is not limited to SQL Server only, like Linq-to-SQL is. It uses Entity Framework in the background, as the ORM.


How to store word documents as BLOB in Database using C#

We can store word documents in Database as a BLOB (Binary Large OBject).. We need to convert the document into binary format using BinaryReader class before it gets passed to Database. Following code block shows how to convert and store a word document in Database.

// file path to read file
string filePath = @"D:\test.txt"; 

// declare and initialize FileStream object
FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);

// declare and initialize BinaryReader object
BinaryReader reader = new BinaryReader(stream);

// Read bytes  from the file
byte[] file = reader.ReadBytes((int)stream.Length);

// always Remember  to close the handles, 
// or resources remain locked

Following code block shows how to write bytes to database.

// connection string
string connectionString = "";

// declare and initialize SqlConnection object
SqlConnection connection = new SqlConnection(connectionString);

// declare and initialize SqlCommand object.
// Remember File column is  Blob in database
SqlCommand  command =
 new SqlCommand("INSERT INTO FileTable (File) Values(@File)", connection);

// add parameters to SqlCommand  object
command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file;

// open connection

// execute command