LINQ to SQL

LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it. LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.We are trying to come up with a code sample were we can do all the CURD operations to a database using LINQ.In our example we are trying to come up with a solution somthing like below mentioned.

Solution structure:

For this code sample we will need two projects.Not a must have thing, but I would love to explain with the right architechure. One is a data layer (created as a Class Library) which we will generate and the other is a client application to use this.It can be either web/windowscdoes nt matter.The structure looks somthing like this in Solution Explorer.Also given the data model we are going to hit.

Creating Data Layer:

So, the database is modelled and our classes are created, Now Let's show some code-examples for working with all these classes.First we will try to populate a grid is nothing but pull the data from SampleDB.First create a class library solution and name it as DAL.Before we generate our data layer we must create a new connection in Server Explorer which points to TestDB database(This is the name that I given for DB).Right-click on the DAL project and select Add New Item. From the dialog choose ‘LINQ to SQL Classes’ and call the file SampleCustomer.dbml.The .dbml extension stands for Database Markup Language.Have a look on the autogenerated files , itself is essentially an XML file that describes the database which is used to generate our classes.When you add your new dbml file the Object Relational Designer (O/R Designer) should launch which is a visual tool for creating your model.Locate your database in the Server Explorer window (View -> Server Explorer), and drag and drop the tables to use onto the surface of the O/R Designer.Here I have created a sample data base and two tables on it.DataContext is the main object through wich we will communicate with our database.The properties of the DataContext class are the tables and stored procedures in the database we modelled/created.

Posting all the CURD operatoins code here:

private void PopulateStudents()

{

using (SampleDataContextDataContext context = new SampleDataContextDataContext())

{

   var customer = from c in context.Customers

   select new Customer

   {

      Name = c.Name,

      CustomerID = c.CustomerID,

      OrderID = c.OrderID,

      Zip = c.Zip,

      Order = c.Order

    };

    gvStudents.DataSource = customer.ToList<Customer>();

    gvStudents.DataBind();

   }

}

private void Create()

{

using (SampleDataContextDataContext context = new  SampleDataContextDataContext())

{

  Order newOrder = new Order()

  {

    OrderID = "2",

    Item = "HP Notebook",

    CustomerID = "11100001"

  };

  Customer newCustomer = new Customer()

  {

     Name = "Mark Walker",

     CustomerID = "11100001",

     OrderID = "100024",

     Zip = "90503",

     Order = newOrder

  };

  context.Customers.InsertOnSubmit(newCustomer);

  context.SubmitChanges();

  }

}

private void Update()

{

  SampleDataContextDataContext db = new SampleDataContextDataContext();

  Customer  customer = db.Customers.Single(c=> c.Name == "Mark Walker");

  customer.Zip = "85283";

  db.SubmitChanges();

}

private void Delete()

{

  SampleDataContextDataContext db = new SampleDataContextDataContext();

  db.Orders.DeleteOnSubmit(db.Orders.Where(c => c.OrderID.Contains("2")).Select(c => c).Single())

   db.SubmitChanges();

}

Likewice we can also call a stored procedure also.The code below demonstrates how to retrieve Customer entities not using LINQ query syntax, but rather by calling the "GetCustomers" stored procedure we added to our data model above. Note that once I retrieve the Product results, I can update/delete them and then call db.SubmitChanges() to persist the modifications back to the database.

INQ to SQL provides a nice, clean way to model the data layer of your application. Once you've defined your data model you can easily and efficiently perform queries, inserts, updates and deletes against it.I think you'll find that LINQ to SQL can dramatically improve your productivity when working with data, and enable you to write extremely clean object-oriented data access code.Hopefully the above introduction and code samples have helped whet your appetite to learn more. Over the next few weeks I'll be continuing this series to explore LINQ in more detail.If any quries please shoot post I will more than happy to help you out , more over it will be a learning curve for me too.

private void GetAllCustomeres()

{

 //Sample for calling an stored procedure

using (SampleDataContextDataContext context = new SampleDataContextDataContext()

{

   var customers = from c in context.GetCustomers()

                           select c;

   foreach (Customer  c in customer)

   {

     Console.WriteLine("{0} {1}",

     c.Name, c.Zip);

   }

 }

}