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.

Dynamic Connections to variable databases in c sharp

Say we have Four different Databases and their connections as well which are stored in configuration file like web.Config or

<App-Name>.config and we are in need to connect to different database depending upon user type


User ID=admin;Initial Catalog=biblio;Data Source=betav9;
Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;
Workstation ID=BETAV9;Use Encryption for Data=False;
Tag with column collation when possible=False"/>

When we have taken a decision over user type, we may pass a value to if else structure or case. Depending upon the value you can get the type of connection to
a database we want.

Object dbCon = null;

switch(userType)
{

case "sqlConnection"
dbCon = new SqlConnection("SqlServerConString");
// provide appropriate sql command
break;

case "OleDbConnection"
dbCon = new System.Data.OleDb.OleDbConnection("OleDbConString");
// provide appropriate sql command
break;

case "OdbcConnection"
dbCon = new OdbcConnection("OdbcCongString");
// provide appropriate sql command
break;

case "OracleConnection"
dbCon = new OleDbConnection ("OracleConString");
// provide appropriate sql command
break;
}

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
reader.Close();
stream.Close()

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
connection.Open();

// execute command
command.ExecuteNonQuery();