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.
