LINQ learning Essay three------LINQ to SQL

LINQ to SQL Provides a run-time infrastructure for managing relational data as objects. In LINQ to SQL, the data model of a relational database was mapped to an object model expressed in the programming language of the developer. When you execute the application, LINQ to SQL translates language-integrated queries in the object model into SQL and send s them to the database for execution. When the database returns the results, the LINQ-to-SQL translates them back into objects so you can manipulate.

LINQ to SQL includes Support for stored procedures and user-defined functions in the database, and for inheritance in the object model.

From the above description we can see that LINQ to SQL is actually an ORM structure, and LINQ to SQL translates the language-integrated query in the object model into SQL and then sends it to the database for execution. When the database returns a result, LINQ to SQL converts the result back to an actionable object. Before expanding the following topic, we need to do some preparatory work, first create a DB, add a table, db design to what the following explanation of the content of the small impact, not much to say, then directly on the dry.

1. Create an Object Model

The Object model is created from the database structure, and each table corresponds to a class, and the db corresponds to a class, This class inherits System.Data.Linq.DataContext, if you write manually, the scale of the work of the smaller DB is good, if the table more, then manually write this part of the code is too tired, according to Microsoft's official introduction, there are two tools can be produced by the DB object Model,object Relational Designer and Sqlmetal,object Relational Designer are the IDE that comes with VS, only Object relational in this article Designer when it comes to the presentation.

A. Right-click Project, add New Item, select LINQ to SQL Classes

B. In Server Explorer, right-click Data Connections,add Connection to add the previously prepared DB, Then drag the table from the DB to the 2 part of the. DBML interface, and if there is a stored procedure in the DB, drag it to the 3 part of the. dbml interface.

When you do this, the object Model code is automatically produced in the CS file under. dbml

2. How to invoke the automatic production of the object Model code

1  　　　　　　　 using(Herosomdatacontext CTX =NewHerosomdatacontext ("server=xxxxx;database=xxxx;uid=xx;pwd=xxxxx"))2             {3                 varHH = fromHinchCTX. Heros4                          whereH.stateid = =25                          Selecth;6list();7                 intMaxid = ( fromHinchCTX. Heros8                               byh.id9                              Selecth.id). Max ();Ten                 intWustateid = ( fromSinchCTX. States One                                  whereS.name = ="Wu" A                                  Selects.id). Singleordefault (); -                 intShustateid = ( fromSinchCTX. States -                                   whereS.name = ="Shu" the                                   Selects.id). Singleordefault (); -                 intWeistateid = ( fromSinchCTX. States -                                   whereS.name = ="Wei" -                                   Selects.id). Singleordefault (); +CTx. Heros.insertonsubmit (NewHero {Id = Maxid +1, Name ="ganning", weapon ="Dao", Stateid =Wustateid}); - CTX. SubmitChanges (); +CTx. Heros.deleteallonsubmit ( fromHinchCTX. Heros A                                             whereH.name = ="Zhouyu" at                                             Selecth); - CTX. SubmitChanges (); -Hero Hero = ctx. Heros.where (h = h.name = ="Xiahoudun"). FirstOrDefault (); -Hero. Weapon ="Dadao"; - CTX. SubmitChanges (); -  inCTx. Proc_deletestate (Wustateid,"Wu"); -CTx. ExecuteCommand ("INSERT into state (Id,name,master) values ({0}, {1}, {2})",1,"Wu","Sunquan"); to}

The first line of code is both a class that inherits System.Data.Linq.DataContext, and we construct the Herosomdatacontext class to specify the code that connects which DB,2-13 line and the usage of Linq mentioned in the previous article is no different, the only difference is the data set of the operation is different, since it is for SQL, so for SQL some increase, delete, change, check is indispensable.

A. Increase

The above example code in the 14-15 lines corresponding to the add operation, from the invocation of the method, and we normally insert in the collection is no different, the only difference is that after calling InsertOnSubmit (TEntity entity), the added object is not immediately added to the database , only after the method SubmitChanges () is called, does the "increment" operation in the DB really occur, and if you are curious about what statements are executed in SQL Server, you can use SQL Server Profiler to monitor.

B. by deleting

The 16-19 lines in the example code above correspond to the delete operation, there is nothing special to note from the use of the method, it is still after calling the method SubmitChanges (), will not actually perform the "delete" operation in the DB.

C. Change

The 20-22 lines in the example code above correspond to the modification operation, first getting the object that needs to be modified, then updating the properties that need to be modified, and finally, after calling the method SubmitChanges (), the "change" operation is actually performed in the DB.

D. Check

The 2-13 lines in the example code above correspond to the modification operation, and the use of this part is no different from the use of LINQ described earlier in the article, and as with LINQ to Object and LINQ to XML, the query is asynchronous and executes only when it is actually called.

E. Executing a stored procedure

The 24 lines in the example code above correspond to the stored procedures in the execution db, and after you drag the stored procedure in DB in the Object Relational designer, a method is automatically produced in the DataContext class with the same name as the stored procedure. The parameters of the method are the same as the parameters defined in the stored procedure, and the stored procedure is executed without calling method SubmitChanges (), and the stored procedure is executed immediately in db when the corresponding method with the same name is called.

F. Executing SQL statements directly

The method ExecuteCommand (String command, params object[] parameters) called in 25 lines in the example code above gives the user an interface to execute the SQL statement directly. Another method ExecuteQuery is similar to the function.

G.attach

Use the Attach methods with entities that has been created in one DataContext, serialized to a client, and then Deserialized back (with the intention to perform an update or delete operation). For more information, see Data Retrieval and CUD Operations in N-tier applications (LINQ to SQL).

Do not try to Attach a entity that have not been detached through serialization. Entities that has not been serialized still maintain associations with deferred loaders that can cause unexpected results If the entity becomes tracked by a second data context.

When a new entity was attached, deferred loaders for any child collections (for example, EntitySet collections of Entities from associated tables) is initialized. When SubmitChanges was called, the members of the collections were put into a unmodified state. To update the members of a child collection, you must explicitly call Attach and specify that entity.

It is possible to understand the above introduction, through the two DataContext respectively acquired entities, the life cycle is confined to their own datacontext, if you want to put the current DataContext in the body to be used in another DataContext, It is necessary to "renew" this entity through attach, let him continue to "live" in the new DataContext, before using the life cycle to explain the actual is inaccurate, but such a statement may be easier to understand, combined with the following example file can be easier to understand the point

1 //Typically, the entities to be attached are obtained by deserializing the XML from another layer2 //This example uses LoadWith to preload customers and orders in a query.3 //and disable lazy loading4Customer cust =NULL;5 using(NorthwindDataContext tempdb =NewNorthwindDataContext ())6 {7DataLoadOptions shape =Newdataloadoptions ();8Shape. Loadwith<customer> (c =c.orders);9     //load the first customer entity and its ordersTenTempdb. Loadoptions =shape; OneTempdb. deferredloadingenabled =false; ACust = tempdb. Customers.first (x = X.customerid = ="ALFKI"); - } -Order Ordera =Cust. Orders.first (); theOrder Orderb = Cust. Orders.first (x = x.orderid >Ordera.orderid); - using(NorthwindDataContext DB2 =NewNorthwindDataContext ()) - { -     //attaches the first entity to the current data context to track changes + DB2. Customers.attach (Cust); -     //Attach related orders for tracking, otherwise they will be inserted at commit time + DB2. Orders.attachall (Cust. Orders.tolist ()); A     //Update the customer's phone. atCust. Phone ="2345 5436"; -     //Update the ShipCity of the first order Ordera. -Ordera.shipcity ="Redmond"; -     //Remove the second order Orderb. - Cust. Orders.remove (Orderb); -     //Add a new order to customer customers. inOrder Orderc =NewOrder () {ShipCity ="New York" }; - Cust. Orders.add (ORDERC); to     //Submit Execution + DB2. SubmitChanges (); -}

H. Conflict management

Because it is the operation of SQL database, so there will inevitably be multi-threading problem, followed by the problem of conflict arises, this part is not specifically described, interested in the following links can be seen below the content

https://msdn.microsoft.com/en-us/library/bb399389 (v=vs.90). aspx

To be Continued ...

LINQ learning Essay three------LINQ to SQL