How to Use LINQ to SQL in C Sharp

LINQ to SQL is a .Net framework component from Microsoft which is used to access to Microsoft SQL Server databases. This article outlines how to set up some basic functionality with LINQ to SQL in C#. It describes basic steps to creating a...

Part 1 of 6:

Setting Up

How to Use LINQ to SQL in C Sharp Picture 1

Create or open a C# program in Visual Studio. The example uses the Console Application template.
How to Use LINQ to SQL in C Sharp Picture 2

Right click on your program in Solution Explorer.
How to Use LINQ to SQL in C Sharp Picture 3

Click Add Reference...
How to Use LINQ to SQL in C Sharp Picture 4

Under .NET, select System.Data.Linq
How to Use LINQ to SQL in C Sharp Picture 5

Click OK
How to Use LINQ to SQL in C Sharp Picture 6
Add the following lines to the top of the source code:
```
using System.Linq; using System.Data.Linq; using System.Data.Linq.Mapping; 
```
1. Note: Visual Studio may have already added the line for System.Linq.

How to Use LINQ to SQL in C Sharp Picture 7

Create a new DataContext class by entering this code, replacing login_string with your actual login string:

class MyDatabase : DataContext { private const String LoginString = "login_string"; public MyDatabase() : base(LoginString) { } }

How to Use LINQ to SQL in C Sharp Picture 8
Add this line to your Main method to create a new instance of the MyDatabase class:
```
MyDatabase db = new MyDatabase(); 
```
1. The DataContext instance acts as your connection to SQL Server.

Part 2 of 6:

Creating a New Table

How to Use LINQ to SQL in C Sharp Picture 9
Add this line your Main method to create a new table in your database:
```
db.ExecuteCommand("Create table employees. (ID int, Name varchar(50), Department varchar(50))"); 
```
1. Other SQL commands may be issued in a similar manner, by passing the command string to ExecuteCommand.
How to Use LINQ to SQL in C Sharp Picture 10
Add the follow line to finalize the changes to the database:
```
 db.SubmitChanges(); 
```

Part 3 of 6:

Associating a Table with a Class

How to Use LINQ to SQL in C Sharp Picture 11
Add these lines to your source code:
```
[Table(Name = "Employees")] public class Employee {  [Column(IsPrimaryKey = true)] public int ID;  [Column] public string Name;  [Column] public string Department; } 
```
1. This will define a new class, with the Table attribute indicating that the class represents table data, the Name parameter associating a name for that table, the Column attribute indicating column names and types, and the IsPrimaryKey parameter indicating the primary key column.

Part 4 of 6:

Inserting Data to the Database

How to Use LINQ to SQL in C Sharp Picture 12
Add this line to the MyDatabase class:
```
public Table<Employee> Employees; 
```

How to Use LINQ to SQL in C Sharp Picture 13

In your Main method, create three new rows of data with new instances of the Employee class and filling in the data, 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();

Part 5 of 6:

Querying the Database

How to Use LINQ to SQL in C Sharp Picture 14

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

How to Use LINQ to SQL in C Sharp Picture 15

To access the data for everyone in sales, and display their names and ID numbers, 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);

How to Use LINQ to SQL in C Sharp Picture 16
Similar queries can be made using similar code. 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); 
```

Part 6 of 6:

Summary

How to Use LINQ to SQL in C Sharp Picture 17

Look at the whole thing. 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 {  [Table(Name = "Employees")] public class Employee {  [Column(IsPrimaryKey = true)] public int ID;  [Column] public string Name;  [Column] 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); } } }