• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Featured
    • C# Tutorials
      • LinQ Tutorials
      • Facebook C# API Tutorials
    • PHP Tutorials
      • CodeIgniter Tutorials
    • Amazon AWS Tutorials
  • Categories
    • Programming
    • Development
    • Database
    • Web Server
    • Source Control
    • Management
    • Project
  • About
  • Write
  • Contact

CodeSamplez.com

Programming, Web development, Cloud Technologies

You are here: Home / Database / LinQ To SQL Database Update Operations In C#

LinQ To SQL Database Update Operations In C#

February 2, 2011 by Rana Ahsan 17 Comments

LinQ C# Tutorials

Recently, I have discussed getting started linq to sql in c#.net. The articles also covered creating select queries for retrieving both single and multiple results. Today, in this tutorial, I will try to give some more examples of other types of database operations, that write/updates the database. These will cover writing and executing ‘Update’/’Insert’/’Delete’ query in the proper way. Please use the database structure given below, if you want to try the following examples directly. I am assuming, you already know how to create DBML/LINQ to SQL classes:

Sample Linq To SQL Dbml
Example DBML To be used In this tutorial

Insert Data With Linq:

LinQ being an ORM, it’s quite an easy and SQL syntax-free way to insert data using LinQ. We need to create a new object of corresponding database table type, then add it to the DataContext object and then commit the changes. Here is a small block of c# code samples to create new ‘User’ LINQ to SQL class(Representing ‘Users’ Table in the Database) and add it to ‘UsersDataContext’ class(Represents the database) and commit the changes to the original database by ‘SubmitChanges'(without this, no changes will be reflected to the database):

/// <summary> /// Create A New User /// </summary> /// <returns>True=User Created Successfully/False=User Couldn't Be Created</returns> public static bool CreateUser(string userName, string passWord,int roleId) { User user = new User(); user.Username = userName; user.Password = passWord; user.RoleId = roleId; UserDataContext UDB = new UserDataContext(); UDB.Users.InsertOnSubmit(user); UDB.SubmitChanges(); }
Code language: PHP (php)

For your information, the above code example is for .NET 3.5+ . If you are using an earlier version, you will have to use ‘Add‘ method instead of ‘InsertOnSubmit‘ . From .NET 3.5, the ‘Add’ method is removed.

If you want to insert more than one row, then you will have to create a ‘List’ array object containing all the ‘User’ objects and then use ‘InsertAllOnSubmit‘ method, it will work fine.


Read All LinQ Tutorials By CodeSamplez.com

Delete Data Using LinQ:

Deleting data/rows from the database using LINQ is as simple as inserting data and this is also SQL syntax-free operation 🙂 . First, we will have to retrieve the ‘user’ object we want to delete, and then add this deletion operation in the queue by ‘DeleteOnSubmit’ method. Finally commit the operation using ‘SubmitChanges’ method as before;

/// <summary> /// Delete A User /// </summary> /// <returns>True=User Deleted Successfully/False=user Couldn't Be Deleted</returns> public static bool DeleteUser(int userId) { UDB= new UserDataContext(); User user = UDB.Users.Single(u =&gt; u.Id == userId); UDB.TVUsers.DeleteOnSubmit(user); UDB.SubmitChanges(); return true; }
Code language: PHP (php)

Just like the insert operation, this delete operation example given above will work for .NET framework 3.5+ and for the earlier framework versions, you will have to use ‘Remove’ method instead of ‘DeleteOnSubmit’ method.

For Removing More than one database record, follow the code example below:

List<User> users = (List<User>)from u in UDB.Users where u.RoleId = 2 select u; UDB.Users.DeleteOnSubmit(user); UDB.SubmitChanges();
Code language: HTML, XML (xml)

Edit/Update Data With LinQ To SQL:

To edit data, simply first, we will have to retrieve the objects(rows) and make changes to them wherever needed. Then use the ‘SubmitChanges’ method to commit the changes to the database. Here is a simple code example below which will change a single row:(There is no difference between this and changing multiple rows only need to retrieve multiple objects and change them)

public static bool SaveUser(User user) { UDB= new UserDataContext(); User nUser = UDB.Users.Single(u =&gt; u.Id == user.Id); nUser.RoleId = user.RoleId; nUser.Username = user.Username; nUser.Password = user.Password; UDB.SubmitChanges(); return true; }
Code language: PHP (php)

You will notice an interesting thing here, we didn’t have to use any extra method to tell the DataContext object to notify the changes like ‘InsertOnSubmit’ or ‘DeleteOnSubmit’ methods before. We don’t have to, because the DataContext object keeps track of all objects it contains in a time frame(after we retrieve them). When the ‘SubmitChanges’ method is called, it simply commits all containing objects’ changes. So, here it’s automatically reflecting when we made a change in the ‘nUSer’ Object. However, the following code won’t work:

public static bool SaveUser(User user) { UDB= new UserDataContext(); User nUser = UDB.Users.Single(u =&gt; u.Id == user.Id); nUser = user; UDB.SubmitChanges(); return true; }
Code language: PHP (php)

This won’t work because it causes the nUser object to point to another memory location reference and that memory location isn’t tracked by the DataContext object. Thus, no changes will be reflected in your database.

Retrieve The SQL Query Text From LinQ:

Sometimes, while writing comparatively big LINQ to SQL query, you might be interested to see what kind of resultant SQL query is generated for a specific kind of operation. This will be helpful for debugging purposes and also to learn/know more about original SQL syntax. Here is a simple code snippet that will show how to get the corresponding SQL query of a LINQ to SQL operation and print it on the console:

UDB= new UserDataContext(); //simple query var results = from u in TV.Users where u.UserName == "Admin" select u; //database command object DbCommand dc = db.GetCommand(q); //Show the command text/sql query text on console Console.WriteLine("\nCommand Text: \n{0}",dc.CommandText);
Code language: JavaScript (javascript)

I hope this article will be helpful for you in writing simple LINQ to SQL queries thus helping to do basic database operations. I will be writing on some more advanced level LINQ to SQL usage soon. To be updated, keep in touch. Happy coding 🙂

Share If Liked

  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pocket (Opens in new window)

You may also like

Filed Under: Database Tagged With: .net, c#, linq

About Rana Ahsan

Rana is a passionate software engineer/Technology Enthusiast.
Github: ranacseruet

Reader Interactions

Comments

  1. ashwin says

    February 10, 2011 at 3:13 am

    gud i like it………

    Reply
  2. Lalit says

    April 15, 2011 at 4:37 am

    How to Change password with linq and what are the property section define in solution Explorer

    Reply
  3. sattu says

    June 28, 2011 at 11:13 pm

    I like this article very much..
    It is very help full for my project….

    Reply
  4. Mihir says

    July 31, 2011 at 11:10 am

    This is indeed a very useful piece of information. I know this code is gonna help me famous in lab session tomorrow! Thnx a lot buddy!

    Reply
  5. prasanna says

    October 12, 2011 at 5:54 am

    very good artical. its very helpful thank u.

    Reply
  6. Archie says

    October 13, 2011 at 1:21 pm

    i was searching for similar stuff and got onto this..thanx a lot for this article….very useful!

    Reply
  7. Chris M says

    November 15, 2011 at 7:15 am

    Very useful. Thanks!

    Reply
  8. Jayavant says

    November 26, 2011 at 3:34 am

    Thank’s for given this artical………

    Reply
  9. Diana says

    February 27, 2012 at 9:15 pm

    Thanx a ton !!

    Reply
  10. Rohit Naik says

    December 26, 2012 at 11:23 am

    Nice Post Buddy………….
    Keep It Up……………………….

    Reply
  11. asmita says

    May 21, 2014 at 5:00 am

    very good
    useful information

    Reply
  12. aghil says

    August 27, 2014 at 6:51 pm

    very good and useful…thank very much

    Reply
  13. Neelam Pandey says

    December 10, 2014 at 3:33 am

    easy coding nice

    Reply
  14. Mae says

    December 16, 2014 at 11:57 pm

    Very helpful. 🙂 Thanks

    Reply
  15. Ipeleng Molete says

    April 4, 2015 at 11:01 am

    Thank you so much. I’ve enjoyed your series of articles, you’ve taken what’s been an imposing subject for me and really made it simple to understand. Just a comment on style, instead of using:

    UDB= new UserDataContext();
    User nUser = UDB.Users.Single(u => u.Id == user.Id);
    nUser.RoleId = user.RoleId;
    nUser.Username = user.Username;
    nUser.Password = user.Password;
    UDB.SubmitChanges();

    you could encapsulate them in a using statement, to make sure all the resources are released when the operation finishes:

    using (var UDB = new UserDataContext())
    {
    User nUser = UDB.Users.Single(u => u.Id == user.Id);
    …
    …
    …
    UDB.SubmitChanges();
    }

    Reply
  16. anjali says

    April 21, 2016 at 6:13 am

    How to update generated list for table in c# ??

    Reply

Trackbacks

  1. Linq To Sql Update? Best 30 Answer - Ar.taphoamini.com says:
    July 26, 2022 at 11:04 pm

    […] + View Here […]

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Follow Us

  • Twitter
  • Facebook

Top Posts & Pages

  • How To Work With JSON In Node.js / JavaScript
    How To Work With JSON In Node.js / JavaScript
  • PHP HTML5 Video Streaming Tutorial
    PHP HTML5 Video Streaming Tutorial
  • LinQ Query With Like Operator
    LinQ Query With Like Operator
  • How To Work With C# Serial Port Communication
    How To Work With C# Serial Port Communication
  • Getting Started With Smarty Template Engine
    Getting Started With Smarty Template Engine
  • Generate HTTP Requests using c#
    Generate HTTP Requests using c#
  • Using Supervisord Web Interface And Plugin
    Using Supervisord Web Interface And Plugin
  • Facebook C# API Tutorials
    Facebook C# API Tutorials
  • Utilizing Config File In C#.NET Application
    Utilizing Config File In C#.NET Application
  • LinQ To SQL Database Update Operations In C#
    LinQ To SQL Database Update Operations In C#

Recent Posts

  • Building Auth With JWT – Part 2
  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read

Tags

.net angularjs apache api audio auth authenticatin aws c# cloud server codeigniter deployment docker doctrine facebook git github golang htaccess html5 http javascript jwt linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty socket.io tfs tips unit-test utility web application wordpress wpf

Footer

Archives

Follow Us

  • Twitter
  • Facebook

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Copyright © 2023