Resources » Code Snippets » LINQ Samples

Basic database operations (SELECT, INSERT, UPDATE & DELETE) using LINQ


Posted Date: 18-Oct-2010  Last Updated:   Category: LINQ Samples    
Author: Member Level: Gold    Points: 8


In this article, i will explain how to select, insert, update, and delete a record from SQL database using LINQ. Using LINQ, we can easily perform these basic DML (Data Manipulation Language) operations without writing any sql queries.



We need to add an new item "LINQ to SQL Classes" for implementing the LINQ conecpts. Right click your solution click add an new item. On the open dialog choose the "LINQ to SQL Classes" item and click open button. After clicked the open button, it immediately asks the alert window for adding the dbml file in "App_Code". Click the ok button.

Open the ".dbml" file, and choose the "View->Server explorer" option. This server explorer shows the list of available connections. If your database connection is already available use that otherwise right click the connection and add an new connection with your SQL server. After made a connection select your table and drag into your .dbml file.

Initialize an object to this database like this,
MyDatabaseDataContext objDatabase = new MyDatabaseDataContext();


Here MyDatabase is a file name of the LINQ class "MyDatabase.dbml". The linq class name always creates DataContext with the name of your file name. After initialize this object you can select, insert, delete, or update with this object and your table object.

Select



If we want to select the records from a table. First initialize an object to that table. Refer the following codes,


var Users = from tblUsers in objDatabase.Tbl_UserLoginDetails orderby tblUsers.Username select new { tblUsers.Username, tblUsers.Userid };


Here, "Tbl_UserLoginDetails" is a name of the table and "objDatabase" is a name of the object for dbml class. "tblUsers" alias name of the table or query.

All your results are assigned to Users variable. While writing the LINQ queries we always store the results in variant datatype variable otherwise type cast with the required class.

Above query returns the records with username and userid columns only. If we want to retrieve all the fields then simply use the following query,


var Users = from tblUsers in objDatabase.Tbl_UserLoginDetails orderby tblUsers.Username
select tblUsers;



Insert



First initialize the object to a particular table (Specify the table name Where do we want to insert), and assign the values to their properties. Refer the following codes,



Tbl_UserLoginDetail objUserDetails = new Tbl_UserLoginDetail();
objUserDetails.Username = txtUsername.Text.Trim();
objUserDetails.Password = txtpassword.Text.Trim();
objUserDetails.emailid = txtEmailid.Text.Trim();

//Don't forget to add these lines then only updates into SQL database
objDatabase.Tbl_UserLoginDetails.InsertOnSubmit(objUserDetails);
objDatabase.SubmitChanges();



The "InsertOnSubmit" method is used for passing the values to database for insert to the table.

The "SubmitChanges" method is used for keep the records/updates into database.


Update



Initialize the table object with the particular recrods. See the below lines of code,


//Get the userid 4 details
Tbl_UserLoginDetail objUserDetails = objDatabase.Tbl_UserLoginDetails.Where(tuser => tuser.userid == 4).Single();

//Assign the new values to that table
objUserDetails.Username = txtUsername.Text.Trim();
objUserDetails.Password = txtpassword.Text.Trim();
objUserDetails.emailid = txtEmailid.Text.Trim();


//update the changes to db
objDatabase.SubmitChanges();



Delete



Delete also same as update method. While updating a records just called the "submitchanges" method only. But while deleting a record we also need to call the "DeleteOnSubmit" method before call the "submitchanges" method as like "InsertonSubmit".

See the below lines of code,

//Get the userid 4 details
Tbl_UserLoginDetail objUserDetails = objDatabase.Tbl_UserLoginDetails.Where(tuser => tuser.userid == 4).Single();

//Assign the new values to that table
objUserDetails.Username = txtUsername.Text.Trim();
objUserDetails.Password = txtpassword.Text.Trim();
objUserDetails.emailid = txtEmailid.Text.Trim();


objDatabase.Tbl_UserLoginDetails.DeleteOnSubmit(objUserDetails);
//update the changes to db
objDatabase.SubmitChanges();



Herewith i attached the complete sample applications. Find the attachment and let me know your comments.


Did you like this resource? Share it with your friends and show your love!

Responses to "Basic database operations (SELECT, INSERT, UPDATE & DELETE) using LINQ"

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Phagu Mahato
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India