How to insert new records using Linq To Sql

In one of my earlier posts I wrote about using LINQ To SQL to call stored procedures. That opens up a way for you to very handy ORM tool provided by Microsoft in form of LINQ to SQL classes. In this post I will discuss how you can use LINQ to SQL to add new records in the tables in database.

• First step in the process, as discussed in earlier post, is to add Linq to Sql class item in your project. This will add a file with DBML extension. For example I have PMDataObject.dbml in my class which I use to create all business objects from database.

• Next step is to drag and drop the database tables on the design surface of ORM tool. As you can see I have added Client table from my database to this surface. Once you have added a table to ORM, it will automaticallyu create a class related to that table in your project. In my case it added Client class.

  

• Now you may be looking for method like Insert or AddClient etc. to insert a new record in the database. You will not find any method like this. Linq To Sql ORM works on idea that you are making a change to collection of records in database. You will notice that you will have properties on DataContext class that represent collection of objects representing classes for each table. For example in my project I have property that looks like dataContext.Clients. You will notice that this collection has a method InsertOnSubmit. So the way this works is that you will create a new object, in my case Client, and then add it to collection. Once you have added all the objects in the collection, then you will call InsertOnSubmit method and you will have new record inserted into the table in the database. Following code sample show how I used it in my sample project.

  protected void OnAddClient(object sender, EventArgs e)
  {
    var conn = ConfigurationManager.
                     ConnectionStrings[Constants.ConnectionStringName].
                      ConnectionString;
    var dataContext = new PMDataObjectDataContext(conn);
    var client = new ByteBlocks.PMObjects.Client() 
    { 
      ClientName = AntiXss.HtmlEncode(ClientName_TextBox.Text.Trim()),
      ClientDescription = AntiXss.HtmlEncode(ClientDescription_TextBox.Text.Trim()),
      CreateDate = DateTime.UtcNow,
      Active = true,
      Deleted = false
    };
    dataContext.Clients.InsertOnSubmit(client);
    try
    {
      dataContext.SubmitChanges(ConflictMode.FailOnFirstConflict);
    }
    catch (Exception ex)
    {
       // TODO: Log the exception.
       System.Diagnostics.Debug.WriteLine(ex.Message);
    }
  }
  

As you can see how easy it is to add new record in the tables using Linq to Sql class objects. In subsequent posts I will discuss some advanced techniques of using Linq To Sql.