Linq to SQL and linqtosql

Linq to SQL and linqtosql
Directory

Preface

Series of articles

Linq to SQL

Summary

Preface

The previous article introduced the content related to linq to xml, which provides a more convenient way to create and query xml trees. This article will continue to introduce the content of linq to SQL. I personally think that in actual development, many small and medium projects are used in linq to SQL. It is indeed important to use ef or nhib.pdf in Small and Medium Projects. Linq to SQL provides a wide range of functions to fully meet the needs of daily data access. The usage is also very simple and flexible.

Series of articles

A preliminary understanding of Lambda expressions in Linq

Lambda of Linq (advanced tutorial)

Implicit type, automatic attribute, initializer, and Anonymous class of Linq

Extended method of Linq

First Appearance of Expression of Linq

Expression IN Linq (advanced tutorial)

Expression of Linq (common Expression types)

Common keywords of Linq

Latency loading of Linq

Linq to Objects

Linq to XML

Linq to SQL

As a component of. NET Framework Version 3.5, LINQ to SQL provides a runtime infrastructure for using relational data as object management.
In LINQ to SQL, the relational database data model is mapped to the object model represented by the programming language used by developers. When the application is running, LINQ to SQL converts the language integration query in the object model to SQL, and then sends them to the database for execution. When the database returns the results, LINQ to SQL converts them back to objects that can be processed in your own programming language.

For more information, see the example.

Prepare a Test Database

Add a linq to SQL class

After creation, Linq2Sql. dbml is enabled by default in the window, and then select server resource manager to add ing relationships.

Add connection

Drag the table to the Linq2Sql. dbml window.

At this point, the database ing has been completed, and you will see the following file in Solution Explorer:

Settings. settings is a visual management of database connection strings. You can modify and add connection strings here.

The connection string generated in App. config

 1 <?xml version="1.0" encoding="utf-8" ?> 2 <configuration> 3     <configSections> 4     </configSections> 5     <connectionStrings> 6         <add name="Wolfy.Linq2Sql.Properties.Settings.TestConnectionString" 7             connectionString="Data Source=.;Initial Catalog=Test;User ID=sa;Password=sa" 8             providerName="System.Data.SqlClient" /> 9     </connectionStrings>10     <startup> 11         <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />12     </startup>13 </configuration>

Check Linq2Sql. designer. cs, where the DataContext class is the main entry point of linq to SQL, that is, the database context that we often refer.

Now, the ing relationship with the database has been completed. Let's take a look at the operations related to linq to SQL.

Add

1 static void Main (string [] args) 2 {3 // create a database context object 4 Linq2SqlDataContext context = new Linq2SqlDataContext (); 5 TB_Customer customer = new TB_Customer () {ID = 1, Name = "wolfy", Address = "Beijing Haidian"}; 6 // insert 7 context. TB_Customer.InsertOnSubmit (customer); 8 List <TB_Customer> MERs = new List <TB_Customer> () {9 new TB_Customer () {ID = 2, Name = "zhangsan ", address = "Shanghai"}, 10 new TB_Customer () {ID = 3, Name = "lisi", Address = "Henan"} 11}; 12 // insert 13 context in batches. TB_Customer.InsertAllOnSubmit <TB_Customer> (MERS mers); 14 // submit the database 15 context. submitChanges (); 16 17}

Generated SQL statement

Exec sp_executesql n' insert into [dbo]. [TB_Customer] ([Name], [Address]) VALUES (@ p0, @ p1) select convert (Int, SCOPE_IDENTITY () AS [value] ', n' @ p0 nvarchar (4000), @ p1 nvarchar (4000) ', @ p0 = n' wolfy ', @ p1 = n' Beijing Haidian 'exec sp_executesql n' insert into [dbo]. [TB_Customer] ([Name], [Address]) VALUES (@ p0, @ p1) select convert (Int, SCOPE_IDENTITY () AS [value] ', n'@ p0 nvarchar (4000), @ p1 nvarchar (4000) ', @ p0 = n' zhangsan ', @ p1 = N 'shanghai' exec sp_executesql n' insert into [dbo]. [TB_Customer] ([Name], [Address]) VALUES (@ p0, @ p1) select convert (Int, SCOPE_IDENTITY () AS [value] ', n'@ p0 nvarchar (4000), @ p1 nvarchar (4000) ', @ p0 = n'lisi', @ p1 = n' Henan'

Delete

Batch delete customer information with id> = 2

1 Linq2SqlDataContext context = new Linq2SqlDataContext (); 2 var query = from c in context. TB_Customer3 where c. ID> = 24 select c; 5 // batch Delete 6 context. TB_Customer.DeleteAllOnSubmit (query); 7 // submit the database 8 context. submitChanges ();

Generated SQL statement

Exec sp_executesql n' delete from [dbo]. [TB_Customer] WHERE ([ID] = @ p0) AND ([Name] = @ p1) AND ([Address] = @ p2) ', n' @ p0 int, @ p1 nvarchar (4000), @ p2 nvarchar (4000) ', @ p0 = 2, @ p1 = n'zhangsan ', @ p2 = n'shanghai' exec sp_executesql n' delete from [dbo]. [TB_Customer] WHERE ([ID] = @ p0) AND ([Name] = @ p1) AND ([Address] = @ p2) ', n' @ p0 int, @ p1 nvarchar (4000), @ p2 nvarchar (4000) ', @ p0 = 3, @ p1 = N 'lisi', @ p2 = N 'henan'

Change

Name of the customer whose id is 1

1 Linq2SqlDataContext context = new Linq2SqlDataContext (); 2 TB_Customer customer = context. TB_Customer.SingleOrDefault (c => c. ID = 1); 3 customer. name = "zhangsan"; 4 // submit database 5 context. submitChanges ();

SQL statement

Exec sp_executesql n' UPDATE [dbo]. [TB_Customer] SET [Name] = @ p3WHERE ([ID] = @ p0) AND ([Name] = @ p1) AND ([Address] = @ p2 )', n' @ p0 int, @ p1 nvarchar (4000), @ p2 nvarchar (4000), @ p3 nvarchar (4000) ', @ p0 = 1, @ p1 = n'wolfy ', @ p2 = n' Beijing Haidian', @ p3 = n' zhangsan'

Query

Find all the order information of the customer with id 1.

1 Linq2SqlDataContext context = new Linq2SqlDataContext (); 2 // query the association between the customer and order Table 3 var query = from o in context. TB_Order4 join c in context. TB_Customer5 on o. ID equals c. ID6 select new {CustomerID = c. ID, OrderID = o. ID, OrderDate = o. orderDate}; 7 // submit the database 8 context. submitChanges ();

SQL statement

SELECT [t1].[ID] AS [CustomerID], [t0].[ID] AS [OrderID], [t0].[OrderDate]FROM [dbo].[TB_Order] AS [t0]INNER JOIN [dbo].[TB_Customer] AS [t1] ON [t0].[ID] = [t1].[ID]SELECT [t1].[ID] AS [CustomerID], [t0].[ID] AS [OrderID], [t0].[OrderDate]FROM [dbo].[TB_Order] AS [t0]INNER JOIN [dbo].[TB_Customer] AS [t1] ON [t0].[ID] = [t1].[ID]

Summary

This article introduces the related content of linq to SQL, and provides an example of using linq to SQL to operate databases. We hope this will help you quickly get started with linq to SQL.

References

Http://kb.cnblogs.com/page/70851/