Insert record into multiple tables using LINQ

This tutorial not only covers how to insert record into multiple tables using LINQ but also includes how select, join tables to display in DataGridView and search record in related tables. In order to achieve this tutorial, first you have already installed Visual Studio in your computer with C# language, however LINQ doesn’t support lower version of .NET. The version I already used in this tutorial is the latest version of Visual Studio 11 which is now available in Beta, unfortunately there’s a lot of feedback in comes the color of an icons because they didn’t like the appearance the way how it looks and color is very important to distinguish which icon is necessary to use unlike previous versions of Visual Studio. Indeed, Visual Studio 11 has more features added that meets the need of every developers, however only Windows 8 has the power to achieve the fully features of VS11.

The database I used to store data is SQL Server that’s provided by Visual Studio 11.

This is the start page of Visual Studio 11.

To start, create new project – select C# for language to use and click Windows Form Application. The form should looks like the following.

This form serves as template throughout this tutorial.

Now, we are going to setup our SQL Server connection in order to create a table. On your Data Connections, you are going to create New Connection Server Database which is the name of your server. After connection was established, Data Connection looks like the following.

It belongs to the Server Explorer our database. We need only Database Diagrams to create our relationships and Tables which we are going to work with to create 2 tables. We need to create a table name tblinfo and tblorder where tblinfo is a foreign key to the tblorder table.

To create table, right click on Tables and Add New Table. Write the column name ID, Firstname, Lastname, and Gender, take note, we need to auto increment the ID so that every time we add new record it will automatically generated new ID number.

In order to generate auto increment, in Column Properties locate the Identity Specification, the default value is No, change it to Yes. Save it as tblinfo.

Next, create another table where tblinfo is the foreign key to tblorder. Set the column name ID and set as auto increment the same as we did in tblinfo, InfoID is the foreign key from tblinfo ID, and ProductName.

Now, you’ve already tables.

We need to distinguish the relationship, in Database Diagrams right click then Add New Diagram and Add the following tables. The diagram now looks like the following.

Now, simply drag the ID from tblinfo to tblorder infoID. When you noticed while creating a relationship, there’s dialog box appear that place which table you want to enforce relationships. Now it’s look like the following.

Make sure to name the diagram before closing. Now, we have done to create tables and creating relationships.

It’s now to create our Database Model (DBML) LINQ to SQL classes mapped to relational object enable to query data from database source. In order DBML available, in solution explorer right click the name of your project name and Add – New Item. When dialog box appear, select LINQ to SQL Classes then name it DBinfoClass and DBML looks like the following.

Now, from Server Explorer drag the Tables tblinfo and tblorder to design surface.

It’s now generating relational database. It’s easy for programmer to write the code once the databases already normalized.

LINQ is a powerful language to write less code that generates quick result because table is no longer to use instead table treated as an objects to query data from database. Let’s take a look how LINQ works!

public static DBinfoClassDataContext db = new DBinfoClassDataContext();

We create new instance of object. Noticed, no need to configure database connection however, you can manually write the connection string where your database path save.

Example: string con = "your connection path";

         public static DBinfoClassDataContext db = new DBinfoClassDataContext(con);

Next, we are going to Insert/Add new records to our database but we have to insert it once with two tables. I made this as my own method to insert record into two related tables. Here we go! Double click the save button and copy this code.

     tblinfo tableInfo = new tblinfo

            {

                Firstname = txtfirstname.Text,

                Lastname = txtlastname.Text,

                Gender = cbGender.Text

            };

            db.tblinfos.InsertOnSubmit(tableInfo);

            db.SubmitChanges();

            tableInfo.ID = tableInfo.ID;//ID to be inserted to Order table

            tblorder tableOrder = new tblorder

            {

                InfoID = tableInfo.ID,

                Productname = txtproduct.Text

            };

            db.tblorders.InsertOnSubmit(tableOrder);

            db.SubmitChanges();

            MessageBox.Show("Saved");

This is just how LINQ syntax works to add new record, simply simple. Next, double click load button to load data to DataGridView.

   var load = from queryInfo in db.tblinfos

                     join queryOrder in db.tblorders

                     on queryInfo.ID equals queryOrder.InfoID

select new { queryInfo.Firstname,queryInfo.Lastname,    queryOrder.Productname };

            dataGridView1.DataSource = load;

When you noticed the syntax, it’s clean and easy to understand the way how LINQ join two tables. Next, to search record, double click search button.

  var load = from queryInfo in db.tblinfos

                    join queryOrder in db.tblorders

                    on queryInfo.ID equals queryOrder.InfoID

where (queryInfo.Firstname == txtSearch.Text) || (queryInfo.Lastname == txtSearch.Text)

select new { queryInfo.Firstname, queryInfo.Lastname, queryOrder.Productname };

            dataGridView1.DataSource = load;

If you want to search record that match any character, instead using ==(equal sign), use Contains or Like keywords. Just like the example given below!

where (queryInfo.Firstname.Contains(txtSearch.Text)) || (queryInfo.Lastname.Contains(txtSearch.Text))

OR

where SqlMethods.Like(queryInfo.Firstname,search) || SqlMethods.Like(queryInfo.Lastname,search)

When using this method don't forget to used namespace using System.Data.Linq.SqlClient;

Note: don't forget to use string search = "%" + txtSearch.Text + "%";

Please visit Language Integrated Query (LINQ) syntax and other documentation to MSDN.