Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / SQL
Tip/Trick

LINQ To SQL Using C#

Rate me:
Please Sign up or sign in to vote.
3.27/5 (13 votes)
2 Feb 2015CPOL2 min read 34K   28   9
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.

C#
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.

C#
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

C#
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

C#
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

C#
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

C#
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. :)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
QuestionMy vote of 5 Pin
Member 114333976-Feb-15 8:19
Member 114333976-Feb-15 8:19 
AnswerRe: My vote of 5 Pin
Ehtesham Mehmood7-Feb-15 2:09
professionalEhtesham Mehmood7-Feb-15 2:09 
GeneralThank for tutorial Pin
Bheesham Kumar Sharma4-Feb-15 19:29
Bheesham Kumar Sharma4-Feb-15 19:29 
GeneralRe: Thank for tutorial Pin
Ehtesham Mehmood4-Feb-15 20:25
professionalEhtesham Mehmood4-Feb-15 20:25 
QuestionLinq to SQL replaced by EF Pin
marc borgers3-Feb-15 19:06
marc borgers3-Feb-15 19:06 
AnswerRe: Linq to SQL replaced by EF Pin
Shabbazz3-Feb-15 21:35
professionalShabbazz3-Feb-15 21:35 
GeneralRe: Linq to SQL replaced by EF Pin
Ehtesham Mehmood3-Feb-15 21:55
professionalEhtesham Mehmood3-Feb-15 21:55 
GeneralLinqPad as a Tool Pin
tyaramis2-Feb-15 2:45
tyaramis2-Feb-15 2:45 
Just an info:

http://www.linqpad.net/[^]
Tolga

GeneralRe: LinqPad as a Tool Pin
Bob Fayman4-Feb-15 10:43
Bob Fayman4-Feb-15 10:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.