December 16, 2020

Programmingempire

In this post on LINQ To SQL Examples, I will explain how to create an application that manipulates and retrieves data from a database using LINQ to SQL.

To begin with, let us first understand what is LINQ to SQL. Basically, it is a mechanism for database access and manipulation using database objects rather than using a relational database connectivity approach.

Object-Relational Modeling

Indeed, Object-Relational Modeling represents the mapping between the relational database model and the object data model. Further, each table in the database is represented by a class and we formulate queries in the language of programming such as C# rather than in SQL. Also, the queries are translated in SQL at runtime.

Entity Classes in LINQ to SQL

Generally, a database may consist of several tables and each table is represented by an entity class in LINQ to SQL. Further, the fields in an entity class represent the columns of the table. Also, all related entity classes have an associated DataContext object which is basically a source of all entities.

Creating a LINQ to SQL Application

Now we create an application to demonstrate the LINQ to SQL Examples. Therefore, we create a Windows Forms Application. Further, after creating the application, click on the Project menu and select the Add New Item option. Finally, select the LINQ to SQL Classes from the list of items. It will open an Object-Relational Designer Editor. Now, you can drag your database table from the Server Explorer to the Object-Relational Designer surface.

Basically, this process creates several entity classes that we will use in our application. Also, when we select LINQ to SQL Classes, we also get a DBML (Database Markup Language) file in our solution.

Design the GUI of Application

LINQ To SQL Examples

Retrieve Data

In fact, retrieving data with LINQ to SQL is as simple as writing a LINQ query. However, before we perform any database operation, we must have a DataContext object with us. Therefore, we need to create an object of the DataContext and pass the ConnectionString as a parameter in the constructor. While, you can also create an object of DataContext without ConnectionString, but then updates are not reflected in the physical database.

Create a DataContext Object

public partial class Form1 : Form
    {
        EmployeesDataContext dc = new EmployeesDataContext(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=C:UsersKAVITAsourcereposLINQToSQLExamplesd2.mdf;Integrated Security=True;Connect Timeout=30");
       
//Rest of the Code

}

Now, define a query using LINQ and execute the query using a foreach loop as shown in following code.

 private void button1_Click(object sender, EventArgs e)
 {
      var q = from x in dc.Emps
              select x;
      String str = "";
      foreach(Emp ob in q)
      {
          str += "Emp ID: " + ob.empid;
          str += "tName: " + ob.ename.Trim();
          str += "tSalary: " + ob.salary;
          str += Environment.NewLine;
       }
       textBox1.Text = str
  }

Insert a Record

Now that, we have retrieved the data from the Emp table, we can also insert a new record. Basically, inserting data requires creating an object of the entity class Emp and setting its properties to the appropriate values. The InsertOnSubmit() method inserts a row in the entity class Emps that represents the database table and SubmitChanges() method actually updates the database.

private void button2_Click(object sender, EventArgs e)
{
    int x = Int32.Parse(textBox2.Text);
    String s = textBox3.Text;
    int y = Int32.Parse(textBox4.Text);
    Emp ob = new Emp();
    ob.empid = x;
    ob.ename = s;
    ob.salary = y;
    dc.Emps.InsertOnSubmit(ob);
    dc.SubmitChanges();
    textBox2.Text = textBox3.Text = textBox4.Text = "";
}

Record Inserted with LINQ to SQL — **Record Inserted**

Update Records

In a similar way, we can update a record by formulating a query and setting the value of a particular field in a foreach loop. Again, the SubmitChanges() method will perform the updates in the real database.

private void button3_Click(object sender, EventArgs e)
{
    int x = Int32.Parse(textBox5.Text);
    int y = Int32.Parse(textBox6.Text);
    var q = from i in dc.Emps
            where i.empid==x
            select i;

    foreach(Emp ob in q)
        ob.salary = y;
    dc.SubmitChanges();
    textBox5.Text = textBox6.Text = "";
 }

Record Updated Using LINQ to SQL — **Record Updated**

Delete Records

Next, we perform delete operation by formulating another query and delete the specific record using DeleteOnSubmit() method and then call the SubmitChanges() method.

 private void button4_Click(object sender, EventArgs e)
 {
     int x = Int32.Parse(textBox7.Text);
     var q = from i in dc.Emps
             where i.empid == x
             select i;

     foreach (Emp ob in q)
        dc.Emps.DeleteOnSubmit(ob);
     dc.SubmitChanges();
     textBox7.Text = "";
 }

Record Deleted Using LINQ to SQL — **Record Deleted**

Summary

In this article on LINQ To SQL Examples, I have explained the concept and implementation of LINQ to SQL through an example of a Windows Forms Application in C#. Here, all of the basic operations of insert, update, delete, and select on a database are discussed with code examples.