• Peforming CRUD with LINQ to SQL

    by Venkata Koppaka | Oct 28, 2010

    LINQ to SQL is an ORM(Object-relational mapping) tool introduced by Microsoft.

    In this post I will walk through how to perform basic CRUD(Create, Retrieve, Update, Delete) operations using LINQ to SQL.

    (NOTE: This post assumes you have basic knowledge of using LINQ to SQL like adding the DBML class and dragging tables on to a DBML designer)

    Lets get started -

    The table we are going use for this example is a simple Person Table. Here is the schema for the table -

     

    Create

    To perform a Create operation on a table using LINQ to SQL you need to create an instance of the class and populate the data into the instance. Once you are done call "InsertOnSubmit" and "SaveChanges" to insert the object.

    Here is the code sample for doing so -

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 Person aPersonToInsert = new Person(); 
    4                 aPersonToInsert.Name = "Test User"; 
    5                 aPersonToInsert.Age = 26; 
    6                 aPersonToInsert.Email = "test@test.com"; 
    7                 context.Persons.InsertOnSubmit(aPersonToInsert); 
    8                 context.SubmitChanges(); 
    9             } 

    The above code will insert the object only after context.SubmitChanges() is called.

    Retrieve

    To perform a retrieve from a table using you can use one of the two syntax. The first one is a normal LINQ syntax which reads more like plain SQL. The second syntax is called "Lamda" syntax which is just a more convenient way to create a delegate.

    Here are the code samples for doing both.

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 //Normal Syntax 
    4                 Person person = (from aPerson in context.Persons 
    5                                  where aPerson.PersonId == 1 
    6                                  select aPerson).SingleOrDefault(); 
    7  
    8                 //Lambda Syntax 
    9                 Person personLamda = context.Persons.SingleOrDefault(p => p.PersonId == 1); 
    10                  
    11             } 

    The SingleOrDefault() method above gets a single entity that matches the criteria or returns NULL if nothing is found.

    Update

    To perform a Update on a database record using LINQ to SQL, you have to get the record using one of the two syntax shown above and make changes to record and then Submit the changes to the database using SubmitChanges().

    Here is the code sample for doing so -

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 //Lambda Syntax 
    4                 Person person = context.Persons.SingleOrDefault(p => p.PersonId == 1); 
    5                 person.Name = "Updated User"; 
    6                 person.Age = 99; 
    7                 person.Email = "noone@someone.com"; 
    8  
    9                 context.SubmitChanges(); //this will save your changes to the database 
    10             } 

    Again please note unless you call SubmitChanges() none of your changes are saved to the database.

     

    Delete

     Delete operation using LINQ to SQL is similar to Update operation. You have to get the record, delete the record and Submit the changes to the database using SubmitChanges().

    Here is the code for doing so -

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 //Lambda Syntax 
    4                 Person person = context.Persons.SingleOrDefault(p => p.PersonId == 1); 
    5  
    6                 context.Persons.DeleteOnSubmit(person); 
    7                 context.SubmitChanges(); //this will save your changes to the database 
    8             } 

    Again unless you call SubmitChanges() nothing is deleted from the database.

    So we are done performing a simple CRUD operations using LINQ to SQL.

    Hope you find it useful,

     Cheers,

    Venkata

    Go comment!

  • How to add Twitter Stream to your ASP.NET MVC Website

    by Venkata Koppaka | Oct 24, 2010

    This post will walk you through how to add twitter stream to your ASP.NET MVC Website.

    First go to  http://twitter.com/widgets, then in left side bar under "Widgets for..." select "My Website"

    From the widgets page, choose "Profile Widget" -

    You should end up in a "Customize your Profile Widget" page -

    1. In the settings tab enter your twitter username.
    2. In the preferences tab choose your preferences ideally the number of tweets can be adjusted here.
    3. From the Appearance tab choose the colors of your widget.
    4. From the Dimensions tab choose your Dimensions of the widget.

    Once you are done, hit finish and grab code.

    Copy the script in the textbox.

    Now to integrate it to your ASP.NET MVC Website, Add new MVC Partial View and paste the code from the above step in to your partial view.

    Then simply call Html.RenderPartial and render the Partial View where ever you want.

    Hope this helped,

    Cheers,

    Venkata

    Go comment!