Employee: How to Use Linq to SQL in C Sharp

This updated guide examines Employee: How to Use Linq to SQL in C Sharp and organizes the essential facts, background, and practical takeaways in clear American English.

Part 1of 6:

Setting Up

1. Createor open a C# program in Visual Studio. The example uses the Console Application template.
2. Right click on your program in Solution Explorer.
3. ClickAdd Reference...
4. Under. NET, selectSystem. Data. Linq
5. ClickOK
6. Add the following lines to the top of the source code:

   usingSystem. Linq;usingSystem. Data. Linq;usingSystem. Data. Linq. Mapping;

   • Note: Visual Studio may have already added the line for System. Linq.
7. Create a new DataContext class by entering this code, replacinglogin_stringwith your actual login string:

   classMyDatabase:DataContext{privateconstStringLoginString="login_string";publicMyDatabase():base(LoginString){}}

8. Add this line to your Main method to create a new instance of the MyDatabase class:

   MyDatabasedb=newMyDatabase();

   • The DataContext instance acts as your connection to SQL Server.

Part 2of 6:

Creating a New Table

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

   • Other SQL commands may be issued in a similar manner, by passing the command string to ExecuteCommand.
2. Add the follow line to finalize the changes to the database:

   db.SubmitChanges();

Part 3of 6:

Associating a Table with a Class

1. Add these lines to your source code:

   [Table(Name = "Employees")]publicclassEmployee{[Column(IsPrimaryKey = true)]publicintID;[Column]publicstringName;[Column]publicstringDepartment;}

   • 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 4of 6:

Inserting Data to the Database

1. Add this line to the MyDatabase class:

   publicTableEmployees;

2. In your Main method, create three new rows of data with new instances of the Employee class and filling in the data, as follows:

   Employeeemployee1=newEmployee();employee1.ID=101;employee1.Name="John Smith";employee1.Department="Sales";db.Employees.InsertOnSubmit(employee1);Employeeemployee2=newEmployee();employee2.ID=102;employee2.Name="Ted Black";employee2.Department="Research";db.Employees.InsertOnSubmit(employee2);Employeeemployee3=newEmployee();employee3.ID=103;employee3.Name="Allen Gottlieb";employee3.Department="Sales";db.Employees.InsertOnSubmit(employee3);db.SubmitChanges();

Part 5of 6:

Querying the Database

1. Know that querying the database can be done within your source code using a syntax which is similar to SQL.
2. To access the data for everyone in sales, and display their names and ID numbers, use the following lines:

   varsalesDept=fromeindb.Employeeswheree.Department=="Sales"selecte;foreach(varemployeeinsalesDept)Console.WriteLine(employee.Name+" "+employee.ID);

3. Similar queries can be made using similar code. For instance, to get and display Ted Black's ID number, you could use this code:

   varqueryResult=fromeindb.Employeeswheree.Name=="Ted Black"selecte;vartedBlack=queryResult.First();Console.WriteLine(tedBlack.ID);

Part 6of 6:

Summary

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

   usingSystem;usingSystem. Collections. Generic;usingSystem. Linq;usingSystem. Text;usingSystem. Data. Linq;usingSystem. Data. Linq. Mapping;namespaceConsoleApplication1{[Table(Name = "Employees")]publicclassEmployee{[Column(IsPrimaryKey = true)]publicintID;[Column]publicstringName;[Column]publicstringDepartment;}classMyDatabase:DataContext{privateconstStringLoginString="login_string";publicTableEmployees;publicMyDatabase():base(LoginString){}}classProgram{staticvoidMain(string[]args){MyDatabasedb=newMyDatabase();db.ExecuteCommand("CREATE TABLE Employees (ID int, Name varchar(50), Department varchar(50))");Employeeemployee1=newEmployee();employee1.ID=101;employee1.Name="John Smith";employee1.Department="Sales";db.Employees.InsertOnSubmit(employee1);Employeeemployee2=newEmployee();employee2.ID=102;employee2.Name="Ted Black";employee2.Department="Research";db.Employees.InsertOnSubmit(employee2);Employeeemployee3=newEmployee();employee3.ID=103;employee3.Name="Allen Gottlieb";employee3.Department="Sales";db.Employees.InsertOnSubmit(employee3);db.SubmitChanges();varsalesDept=fromeindb.Employeeswheree.Department=="Sales"selecte;foreach(varemployeeinsalesDept)Console.WriteLine(employee.Name+" "+employee.ID);varqueryResult=fromeindb.Employeeswheree.Name=="Ted Black"selecte;vartedBlack=queryResult.First();Console.WriteLine(tedBlack.ID);}}}