How to Use LINQ to SQL in C Sharp

Step-by-Step Guide

1. 1

   Step 1: Create or open a C# program in Visual Studio.

   The example uses the Console Application template.
2. 2

   Step 2: Right click on your program in Solution Explorer.

   ,,,,,,, (ID int, Name varchar(50), Department varchar(50))"); Other SQL commands may be issued in a similar manner, by passing the command string to ExecuteCommand. ,,,,,,, For instance, to get and display Ted Black's ID number, you could use this code: var queryResult = from e in db.Employees where e.Name == "Ted Black" select e; var tedBlack = queryResult.First(); Console.WriteLine(tedBlack.ID)
3. 3

   Step 3: Click Add Reference...

   This is the entire program shown above.
   
   It connects to SQL server, creates a new table, adds a few new entries to that table, then queries some of the data. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Linq; using System.Data.Linq.Mapping; namespace ConsoleApplication1 { public class Employee { public int ID; public string Name; public string Department; } class MyDatabase :
   DataContext { private const String LoginString = "login_string"; public Table<Employee> Employees; public MyDatabase() : base(LoginString) { } } class Program { static void Main(string[] args) { MyDatabase db = new MyDatabase(); db.ExecuteCommand("CREATE TABLE Employees (ID int, Name varchar(50), Department varchar(50))"); Employee employee1 = new Employee(); employee1.ID = 101; employee1.Name = "John Smith"; employee1.Department = "Sales"; db.Employees.InsertOnSubmit(employee1); Employee employee2 = new Employee(); employee2.ID = 102; employee2.Name = "Ted Black"; employee2.Department = "Research"; db.Employees.InsertOnSubmit(employee2); Employee employee3 = new Employee(); employee3.ID = 103; employee3.Name = "Allen Gottlieb"; employee3.Department = "Sales"; db.Employees.InsertOnSubmit(employee3); db.SubmitChanges(); var salesDept = from e in db.Employees where e.Department == "Sales" select e; foreach(var employee in salesDept) Console.WriteLine(employee.Name + " " + employee.ID); var queryResult = from e in db.Employees where e.Name == "Ted Black" select e; var tedBlack = queryResult.First(); Console.WriteLine(tedBlack.ID); } } }
4. 4

   Step 4: Under .NET

5. 5

   Step 5: select System.Data.Linq

6. 6

   Step 6: Click OK

7. 7

   Step 7: Add the following lines to the top of the source code: using System.Linq; using System.Data.Linq; using System.Data.Linq.Mapping; Note: Visual Studio may have already added the line for System.Linq.

8. 8

   Step 8: Create a new DataContext class by entering this code

9. 9

   Step 9: replacing login_string with your actual login string: class MyDatabase : DataContext { private const String LoginString = "login_string"; public MyDatabase() : base(LoginString) { } }

10. 10

    Step 10: Add this line to your Main method to create a new instance of the MyDatabase class: MyDatabase db = new MyDatabase(); The DataContext instance acts as your connection to SQL Server.

11. 11

    Step 11: Add this line your Main method to create a new table in your database: db.ExecuteCommand("Create table employees.

12. 12

    Step 12: Add the follow line to finalize the changes to the database: db.SubmitChanges();

13. 13

    Step 13: Add these lines to your source code: public class Employee { public int ID; public string Name; public string Department; } This will define a new class

14. 14

    Step 14: with the Table attribute indicating that the class represents table data

15. 15

    Step 15: the Name parameter associating a name for that table

16. 16

    Step 16: the Column attribute indicating column names and types

17. 17

    Step 17: and the IsPrimaryKey parameter indicating the primary key column.

18. 18

    Step 18: Add this line to the MyDatabase class: public Table<Employee> Employees;

19. 19

    Step 19: In your Main method

20. 20

    Step 20: create three new rows of data with new instances of the Employee class and filling in the data

21. 21

    Step 21: as follows: Employee employee1 = new Employee(); employee1.ID = 101; employee1.Name = "John Smith"; employee1.Department = "Sales"; db.Employees.InsertOnSubmit(employee1); Employee employee2 = new Employee(); employee2.ID = 102; employee2.Name = "Ted Black"; employee2.Department = "Research"; db.Employees.InsertOnSubmit(employee2); Employee employee3 = new Employee(); employee3.ID = 103; employee3.Name = "Allen Gottlieb"; employee3.Department = "Sales"; db.Employees.InsertOnSubmit(employee3); db.SubmitChanges();

22. 22

    Step 22: Know that querying the database can be done within your source code using a syntax which is similar to SQL.

23. 23

    Step 23: To access the data for everyone in sales

24. 24

    Step 24: and display their names and ID numbers

25. 25

    Step 25: use the following lines: var salesDept = from e in db.Employees where e.Department == "Sales" select e; foreach(var employee in salesDept) Console.WriteLine(employee.Name + " " + employee.ID);

26. 26

    Step 26: Similar queries can be made using similar code.

27. 27

    Step 27: Look at the whole thing.

Detailed Guide

About the Author