LINQ To SQL

Today in this article I will teach you the most interesting and new features of the C# programming language, LINQ-To-SQL.

LINQ To SQL
 
Today in this article I will teach you the most interesting and new features of the C# programming language, LINQ-To-SQL.

LINQ To SQL

First of all I will introduce you people to LINQ, then I will tell you how to work with LINQ-To-SQL.

Introduction

Language Integrated Query (LINQ) is a new feature introduced by Microsoft in .NET Framework 3.5. It used for querying databases and local collections and brings static type safety to database queries. It's very simple and well organized. A universal querying language that can work across SQL, XML, local collections and third-party APIs such as SharePoint.

Basically, what LINQ provides is lightweight programmatic data integration. This is such a big offer because currently data is everything and in the future I will write a complete article on Big Data.
 
Now I will tell you how to work with LINQ-To-SQL.

Step 1:

  • Open your Visual Studio and make a new console project  with any name.
  • Then open Server Explorer then make a new database with some name and make a new table add some columns in the table.
  • Then open your project and go into Solution Explorer and right-click on the project and then click "Add" -> "New item...".
  • There search LINQ-To-SQL and add this file and press the "OK" button.
  • Then you will see a blank file; on that file you will drag your table in the SQL Server database file onto the LINQ-To-SQL .dbml file extension.

Step 2: Here I will declare some class data members in the class.

class Program

{                   // this is my program class

    private int id;

    private string name;

    private string fname;

    private int age;

    private string sem;

}

Step 3: Now I will tell you how to insert data into the database using a LINQ-To-SQL query.

INSERT Data

Here is the insert data function in which we will insert our data into the database.

public void insert()

{

    // these are the class data members through we will send our objects data in the database

    Console.WriteLine("Enter id");

    id = Convert.ToInt32(Console.ReadLine());

    Console.WriteLine("Enter name");

    name = Console.ReadLine();

    Console.WriteLine("Enter father name");

    fname = Console.ReadLine();

    Console.WriteLine("Enter age");

    age = Convert.ToInt32(Console.ReadLine());

    Console.WriteLine("Enter semester");

    sem = Console.ReadLine();

    // this is the data context class the main class which handles all the functionality in this will pass the connection string of our database file.

    LTSDataContext LTS = new LTSDataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Ehtesham Mehmood\Documents\Visual Studio 2012\Projects\ConsoleApplication5\ConsoleApplication5\SDatabase.mdf;Integrated Security=True;Connect Timeout=30");

    // this is the table class which we drag on our linq to sql file

    Student objStudentTable = new Student();

    objStudentTable.Id = id;

    objStudentTable.Name = name;

    objStudentTable.Father_Name = fname;

    objStudentTable.Age = age;

    objStudentTable.Semester = sem;

    LTS.Students.InsertOnSubmit(objStudentTable); // this is built in function.

    LTS.SubmitChanges();// here is the final query will run in the data context class.           

 

Step 4: Display Data 

void Display()

{

    LTSDataContext LTS = new LTSDataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Ehtesham Mehmood\Documents\Visual Studio 2012\Projects\ConsoleApplication5\ConsoleApplication5\SDatabase.mdf;Integrated Security=True;Connect Timeout=30");

    var selectQuery = from s in LTS.Students

                      select s;

    foreach (Student s in selectQuery)

    {

        Console.WriteLine(s.Id + "\t" + s.Name + "\t" + s.Father_Name + "\t" + s.Age + "\t" + s.Semester);

    }

}

Step 5: Delete Data

void Delete()

{

    int iid = 0;

    Console.WriteLine("Enter the Id of the student u want to delete?");

    iid = Convert.ToInt32(Console.ReadLine());

    LTSDataContext LTS = new LTSDataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Ehtesham Mehmood\Documents\Visual Studio 2012\Projects\ConsoleApplication5\ConsoleApplication5\SDatabase.mdf;Integrated Security=True;Connect Timeout=30");

    var delete = from p in LTS.Students

                 where p.Id == iid

                 select p;

    LTS.Students.DeleteAllOnSubmit(delete);

    LTS.SubmitChanges();

    Student objStudentTable = LTS.Students.Single(c=> c.Id == iid);   

}

Step 6: Update Data

void update()

{

    LTSDataContext LTS = new LTSDataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Ehtesham Mehmood\Documents\Visual Studio 2012\Projects\ConsoleApplication5\ConsoleApplication5\SDatabase.mdf;Integrated Security=True;Connect Timeout=30");

    Student objStudentTable = new Student();

    int iid = 0;

    Console.WriteLine("Enter the Id of the student u want to update ?");

    iid = Convert.ToInt32(Console.ReadLine());

    Console.WriteLine("Enter the new name of the student u want to update?");

    string up = (Console.ReadLine());

         var update = from s1 in LTS.Students

                     where s1.Id == iid

                     select s1;

        foreach (var v in update)

            v.Name = up;

        LTS.SubmitChanges();

}

Main function
 

static void Main(string[] arg)

{           

    Program p1 = new Program(); // creates object

    p1.insert();

    p1.Display();

    p1.Delete();

    p1.update();

    Console.ReadKey();

}

I will also attach the entire code file so you can download it.