How to Use datacontext in LINQ to SQL

Datacontext, as the main entry point of the LINQ to SQL framework, provides some methods and attributes for us. This article uses several examples to illustrate typical applications of datacontext.

Create and delete Databases

The createdatabase method is used to create a database on the server.
The deletedatabase method is used to delete the database identified by the datacontext connection string.

The database name can be defined as follows:
If the database is identified in the connection string, the name of the connection string is used.
If the databaseattribute attribute exists, use its name attribute as the database name.
If the connection string does not contain database tags and a strong datacontext type is used, the database with the same name as the datacontext inheritance class is checked. If a weak datacontext is used, an exception is thrown.
If you have created datacontext by using a file name, the database corresponding to the file name is created.

First, we use the object class to describe the structure attributes of the relational database tables and columns. Call the createdatabase method of datacontext, and use the defined object class structure to construct a new database instance. You can also use createdatabase with SQL server by using the. MDF file or by using only the directory name (depending on the connection string. The connection string is used to define the database to be created and the server where the database is created.

If you have said so much, use an example to describe it!

First, we create a newcreatedb class to create a new database named newcreatedb. MDF. The database has a person table with three fields: personid, personname, and age. (Click to expandCode)

Code is expanded here

The following code first creates a database. After createdatabase is called, the new database will exist and accept common queries and commands. Insert a record and query it. Delete the database.

  // 1. Create a temporary folder to store the new database  String Usertempfolder = Environment . Getenvironmentvariable ( "Systemdrive" ) + @ "\ Yjinglee" ;Directory . Createdirectory (usertempfolder ); // 2. Create a database newcreatedb  String Usermdf = system. Io. Path . Combine (usertempfolder, @ "Newcreatedb. MDF" ); String Connstr = String . Format ( @ "Data Source =. \ sqlexpress; attachdbfilename = {0}; Integrated Security = true; Connect timeout = 30; user instance = true; Integrated Security = sspi ;" , Usermdf ); Newcreatedb Newdb = New Newcreatedb (Connstr); newdb. createdatabase (); // 3. insert and query data  VaR Newrow = New  Person {Personid = 1, personname = "Yjinglee" , Age = 22}; newdb. Persons. insertonsubmit (newrow); newdb. submitchanges (); VaR Q = From X In Newdb. Persons Select X; // 4. Delete the database Newdb. deletedatabase ();// 5. Delete the temporary directory  Directory . Delete (usertempfolder );

Database Verification

The databaseexists method is used to try to open the database by using the connection in datacontext. If the database is successful, true is returned.

The following code shows whether the northwind database and newcreatedb database exist.

 // Check whether the northwind database exists  If (Db. databaseexists ()) Console . Writeline ( "The northwind database exists" ); Else      Console . Writeline ( "The northwind database does not exist" );// Check whether the newcreatedb database exists  String Usertempfolder = Environment . Getenvironmentvariable ( "Temp" ); String Usermdf = system. Io. Path . Combine (usertempfolder, @ "Newcreatedb. MDF" ); Newcreatedb Newdb = New  Newcreatedb (Usermdf ); If (Newdb. databaseexists ()) Console . Writeline ("Newcreatedb database exists" ); Else      Console . Writeline ( "Newcreatedb database does not exist" );

Database changes

The submitchanges method calculates the set of modified objects to be inserted, updated, or deleted, and executes the corresponding commands to implement database changes.

No matter how many changes the object has made, it is only changing the copy in the memory. No changes were made to the actual data in the database. The changes made to datacontext are not transmitted to the server until submitchanges is explicitly called for datacontext. During the call, datacontext will try to convert the changes we made to an equivalent SQL command. We can also use our own custom logic to override these operations, but the submission order is coordinated by a service called "Change processor" in datacontext. The event sequence is as follows:

1. When submitchanges is called, LINQ to SQL checks the set of known objects to determine whether the new instances have been attached to them. If these instances are attached, they are added to the set of tracked objects.
2. All objects with pending changes will be sorted into an Object Sequence Based on their dependencies. If an object depends on other objects, the object is placed after its dependencies.
3. When any actual changes are about to be transmitted, LINQ to SQL starts a transaction to encapsulate a series of commands.
4. Changes to objects are converted to SQL commands one by one and then sent to the server.

If the database detects any errors, the submission process stops and an exception is thrown. All changes to the database will be rolled back, just as if they were not submitted. Datacontext still has a complete record of all changes.

the following code describes the query of customers whose customerid is alfki in the database, then modify the company name, update and call the submitchanges () method for the first time, and update the data for the second time without calling the submitchanges () method.

// QueryCustomerCust = dB. Customers. First (C => C. customerid ="Alfki");// Update the data and call the submitchanges () methodCust. companyName ="Yjinglee's blog"; DB. submitchanges ();// The submitchanges () method is not called for data update.Cust. companyName =Http://lyj.cnblogs.com";
 

Dynamic query

This example uses the createquery () method to create an iqueryable <t> type expression to output the query statement. Here is an example. For more information about dynamic queries, see the next article.

 VaR C1 = Expression . Parameter ( Typeof ( Customer ), "C" ); Propertyinfo City = Typeof ( Customer ). Getproperty ( "City" ); VaR PRED = Expression . Lambda < Func < Customer , Bool > ( Expression . Equal ( Expression . Property (C1, city ), Expression . Constant ( "Seattle" ), C1 ); Iqueryable Custs = dB. MERs MERS; Expression Expr = Expression . Call ( Typeof ( Queryable ), "Where" , New  Type [] {Custs. elementtype}, custs. expression, Pred ); Iqueryable < Customer > Q = dB. Customers. asqueryable (). provider. createquery < Customer > (Expr );

Logs

The log attribute is used to print SQL queries or commands to textreader. This method may be useful for understanding the functions of LINQ to SQL and debugging specific issues.

The following example uses the log attribute to display this code in the console window before SQL code execution. This attribute can be used with the query, insert, update, and delete commands.

 // Disable the log function // dB. log = NULL; // use the log function: log output to the console window DB. log =Console . Out; VaR Q = From C In DB. MERs Where C. City = "London"  Select C; // Log output to file  Streamwriter Sw = New  Streamwriter (Server. mappath ( "Log.txt" ), True ); DB. log = Sw;VaR Q = From C In DB. MERs Where C. City = "London"  Select C; Sw. Close ();

The above content is reposted from the blog Park Li yongjing's blog (http://www.cnblogs.com/lyj)