LinQ To SQL Database Update Operations In C#

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:

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 => 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 => 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 🙂