HomeSystem

How to Use LINQ to SQL in C Sharp

Part 1 of 6:

Setting Up

  1. 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.
  2. How to Use LINQ to SQL in C Sharp Picture 2
    Right click on your program in Solution Explorer.
  3. How to Use LINQ to SQL in C Sharp Picture 3
    Click Add Reference...
  4. How to Use LINQ to SQL in C Sharp Picture 4
    Under .NET, select System.Data.Linq
  5. How to Use LINQ to SQL in C Sharp Picture 5
    Click OK
  6. 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.
  7. 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) { } }
  8. 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

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

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

  1. How to Use LINQ to SQL in C Sharp Picture 12
    Add this line to the MyDatabase class: 
    public Table<Employee> Employees;
  2. 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

  1. 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.
  2. 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);
  3. 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

  1. 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); } } }
3.5 ★ | 2 Vote
employeenewemployeestable

You should read it

May be interested

System