LINQ To SQL Using C#

Today in this tip, I will tell you the most interesting and the most useful feature of C# programming language.

Before I start, I have to tell you guys the basic introduction of LINQ, i.e., what is LINQ? And then I will move further.

Introduction

LINQ = Language Integrated Query

LINQ is a new feature that was introduced by Microsoft in .NET Framework 3.5. It is used for querying databases and local collections bringing 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 now a day’s data is everything and in the coming future, the future is in Big Data.

Now here, I will tell you how to start a project and all other stuff.

Step 1

• Open your Visual Studio and make 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 in to 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 ok button.
• Then you will see a blank file there, on that file you will drag your table in the SQL Server database file on to the LINQ-To-SQL .dbml file extension.

Step 2

Here I will declare some class data members in the class.

class Program
    {							// this is  program class
        private int id;
        private string name;
        private string fname;
        private int age;
        private string sem;
}

Step 3

Now here, I will tell you how you will insert the data in the database through LINQ-To-SQL query.

INSERT Data

Here is the insert data function in which we will insert our data in 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();
        }

Thank you for reading. Please leave your comments.

Cheers! Enjoy coding. :)