LinQ To SQL Database Update Operations In C#

Recently, I have discussed about getting started linq to sql in c#.net. That articles also covered for creating select query for retrieving both single and multiple results. Today, in this tutorial, I will try to give some more examples on other types of database operations, that writes/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:

Being LinQ as a ORM, it’s quite easy and sql syntax free way to insert data using LinQ. We just 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 Database) and add it to ‘UsersDataContext’ class(Represents the database) and commit the changes to original database by ‘SubmitChanges'(without this, no changes will be reflected to 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();
}

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/row from 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 => u.Id == userId);
            UDB.TVUsers.DeleteOnSubmit(user);
            UDB.SubmitChanges();
            return true;
        }

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

For Removing More than one database records, 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();

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 just use the ‘SubmitChanges’ method to commit the changes to database. Here is a simple code example below which will change a single row:(There is no difference between this and changing multiple row 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;
        }

You will notice an interesting thing here, we didn’t have to use any extra method to tell 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 is containing in a time frame(after we retrieving them). When the ‘SubmitChanges’ method called, it simply commits it’s all containing objects’ changes. So, here it’s automatically reflecting when we made 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;
}

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

Retrieve The SQL Query Text From LinQ:

Sometime, while writing comparatively big linq to sql query, you might be interested to see what kind of resultant sql query is actually generated for a specific kind of operations. This will be helpful for debugging purpose 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 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);

Hope this article will be helpful for you for writing simple linq to sql query thus help doing basic database operations. I will be writing on some more advance level linq to sql usage soon. To be updated, keep in touch. Happy coding 🙂