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

Table of Contents

Setting Up
Creating a New Table
Associating a Table with a Class
Inserting Data to the Database
Querying the Database
Summary

Part 1 of 6:

Setting Up

Images 1 of How to Use LINQ to SQL in C Sharp

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

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

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

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

Click OK
Images 6 of How to Use LINQ to SQL in C Sharp
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.
Images 7 of How to Use LINQ to SQL in C Sharp
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) { } } 
```
Images 8 of How to Use LINQ to SQL in C Sharp
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

Images 9 of How to Use LINQ to SQL in C Sharp
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.
Images 10 of How to Use LINQ to SQL in C Sharp
Add the follow line to finalize the changes to the database:
```
	db.SubmitChanges(); 
```

Part 3 of 6:

Associating a Table with a Class

Images 11 of How to Use LINQ to SQL in C Sharp
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

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

images 13 of How to Use LINQ to SQL in C Sharp — *Images 13 of How to Use LINQ to SQL in C Sharp*

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

Images 14 of How to Use LINQ to SQL in C Sharp

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

images 15 of How to Use LINQ to SQL in C Sharp — *Images 15 of How to Use LINQ to SQL in C Sharp*

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);

Images 16 of How to Use LINQ to SQL in C Sharp
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

images 17 of How to Use LINQ to SQL in C Sharp — *Images 17 of How to Use LINQ to SQL in C Sharp*

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); } } }

Micah Soto

Update 04 March 2020

« PREV : How to Create a File...

How to Change File... : NEXT »