Geeks With Blogs
Saqib Ullah BootStrapper Know How
In my last blog we saw Microsoft LINQ and its LINQ to Object part and we also saw some small code snippet of LINQ to Object. One thing that I feel if you want to superior command on LINQ so you must have a good understanding of Anonymous types, Extension methods, Lambda expressions and Expression trees all are new feature of C# 3.0. Today I write small code snippet that use LINQ to SQL (formerly Dlinq).
 
Before LINQ I use pure ADO.Net classes to write code that brings my required data.

// Specify the connection to the MS SQL Express database
SqlConnection con = new SqlConnection("Data Source=SU\\SQLExpress;Initial Catalog=LINQ;Integrated Security=SSPI;");
 
SqlCommand salesCommand = new SqlCommand("select * from patient",con);
salesCommand.CommandType = CommandType.Text;
con.Open();
 
SqlDataReader reader = salesCommand.ExecuteReader();
//Read and Print All records
   while (reader.Read())
     {
     Debug.WriteLine(reader[0].ToString() + " " + reader[1].ToString());
     }
//Close Reader and Connection objects
reader.Close();
con.Close();

Before I dive into the coding of LINQ one thing that I want to mention here is LINQ to SQL implement IQueryable<T> interface to convert query expressions into Expression trees, which it transforms into SQL statements. Existing ADO.Net 2.0 classes are totally integrated with LINQ to SQL and easily plug with existing program and applications. One thing about which I am worry is LINQ to SQL only support for Microsoft SQL Server 2000 and 2005. Still there are no provider frameworks available for other databases.
 
DataContext:
DataContext provided a bridge between application and database. DataContext class is the heart of DLINQ. The DataContext is the main object through which you retrieve objects from the database and submit changes back. It translates application request into SQL queries.
 
Data Access Layer:
LINQ require a special data layer that communicate between database and LINQ application and this layer called DLINQ layer. There are three different approaches to create DLINQ layer.
  • Create class that extended DataContext class
  • Use DLINQ Visual Designer
  • Use command line utility called sqlmetal.exe
Object Model:
There are two ways to create object model that maps to the relation data.
·         Create classes with mapping attributes that tell where and how should it store database. This is refer to attributes base mapping
·         It is also possible for you to specify the mapping using an extenal XML file
 
You can use any approach for object model, both approaches has same performance effects.
Sqlmetal Utility
LINQ to SQL provide us a command line utility called Sqlmetal.exe and you find sqlmetal utility in the “Drive_Name\Program Files\LINQ Preview\Bin” folder. By the help of sqlmetal you generate entity classes, properties and their association. One important thing that I should mention is sqlmetal.exe utility generate object model both in class file and in xml file.

Sqlmetal.exe /server:su\SQLExpress /database:LINQ /code:LINQ.cs
generate mapping class file name LINQ.cs
 
Sqlmetal.exe /server:su\SQLExpress /database:LINQ /xml.LINQMeta.xml
generate mapping XML file name LINQMeta.xml

 
Fig 1. LINQ Database diagram.
LINQTestPage
public partial class LINQTestPage : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
This code snippet fetches records that lay in department “Cardio” or 3. Here GetQueryText method is used for print the SQL statement that sends by the LINQ to database.

// Specify the connection to the MS SQL Express database
string conn = "Data Source=Saqib\\SQLExpress;Initial Catalog=LINQ;Integrated Security=SSPI;";
 
LINQ obj = new LINQ(conn);
    var q = from d in obj.Doctor
        where d.Departmet_Id == 3
        orderby d.Id descending
        select d;
// Print the SQL send to database
   Debug.WriteLine(obj.GetQueryText(q));
   Debug.WriteLine("++++++Doctor++++++");
   foreach(var c in q)
   {
// Print all records
    Debug.WriteLine(c.Name.Trim() + " " +             c.Department.Name.ToString()
    + " " + c.Gender.Trim());                                    
   }
The following code snippet add a record in doctor table. For this first I create object of Doctor class and set its properties and pass the doc object to the Add method of Doctor entity and call the SubmitChanges() of DataContext. Here SubmitChanges() method will contact the database and will execute the SQL statement.            

//Add New Record
        Doctor doc = new Doctor();
        doc.DepartmentId = 2;
        doc.Name = "Saqib Ullah";
        doc.Gender = "Male";
        obj.Doctor.Add(doc);
        obj.SubmitChanges();
The following code snippent update existing record in patient table. Here we use Lamda expression in Single method that fetch single record.

//Modify the Record
        var patient = obj.Patient.Single(p => p.Id == 1);
        patient.Name = "Azhar";
        obj.SubmitChanges();
The following code snippent remove record from patient table.

//Remove Patient Record
        Patient pp = new Patient();
        pp.Id = 4;
        obj.Patient.Remove(pp);
        obj.SubmitChanges();
    }
} 
In above code snippets we have seen add, update and remove operations on doctor and patient tables. Actually LINQ DataContext makes our job easier and act like a Unit of Work.
Posted on Tuesday, May 22, 2007 9:23 PM .Net , Web Tech | Back to top


Comments on this post: More on LINQ

# re: More on LINQ
Requesting Gravatar...
Hi,

The problem with your approach is that you are accessing the Database directly from the UI.

// Specify the connection to the MS SQL Express database
string conn = "Data Source=Saqib\\SQLExpress;Initial Catalog=LINQ;Integrated Security=SSPI;";

LINQ obj = new LINQ(conn);
var q = from d in obj.Doctor
where d.Departmet_Id == 3
orderby d.Id descending
select d;
// Print the SQL send to database
Debug.WriteLine(obj.GetQueryText(q));
Debug.WriteLine("++++++Doctor++++++");
foreach(var c in q)
{
// Print all records
Debug.WriteLine(c.Name.Trim() + " " + c.Department.Name.ToString()
+ " " + c.Gender.Trim());
}

This is not good way!!!


Left by AzamSharp on Jul 20, 2007 4:53 AM

# re: More on LINQ
Requesting Gravatar...
THERE IS NOTHING WRONG WITH THIS APPROACH!
Left by Marcus Maggie on Jun 29, 2009 12:10 PM

# re: More on LINQ
Requesting Gravatar...
THERE IS NOTHING WRONG WITH THIS APPROACH!

They are just trying to illustrate how all the parts connect.

AzamSharp - Why don't you put up a post since you know so much better; instead of complaining about it!?

+MxM
Left by Marcus Maggie on Jun 29, 2009 12:11 PM

# re: More on LINQ
Requesting Gravatar...
This is how we can reach where we want to.
Left by std disease on Jan 15, 2011 9:05 AM

Your comment:
 (will show your gravatar)


Copyright © Saqib Ullah | Powered by: GeeksWithBlogs.net