LINQ Experience (--LINQ) DataContext to SQL statements

DataContext

DataContext, as the main entry point for the LINQ to SQL framework, provides us with methods and properties that illustrate several typical applications of DataContext in several examples.

Creating and Deleting 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 name of the database is defined in the following ways:
If the database is identified in the connection string, the name of the connection string is used.
If the Databaseattribute attribute (Attribute) exists, its Name property is used as the name of the database.
If there is no database tag in the connection string and a strongly typed DataContext is used, the database with the same name as the DataContext inheriting class is checked. If a weakly typed DataContext is used, an exception is thrown.
If DataContext has been created by using a file name, the database corresponding to the file name is created.

We first use entity classes to describe the properties of the structure of relational database tables and columns. Then call DataContext's CreateDatabase method, and LINQ to SQL constructs a new DB instance with our defined entity class structure. You can also use CreateDatabase with SQL Server by using an. mdf file or by using only the directory name (depending on the connection string). LINQ to SQL uses a connection string to define the database to be created and the server where the database is created.

Say so much, illustrate it with an example!

First, we create a new Newcreatedb class for creating a database called Newcreatedb.mdf, which has a person table with three fields, PersonID, PersonName, and age, respectively. (Click to expand the Code)

the code expands here .

The next piece of code creates a database, and after calling CreateDatabase, the new database will exist and will accept general queries and commands. Then insert a record and query. Finally, delete the database.

 //1. Create a new temporary folder to hold the newly created databasestringUsertempfolder =Environment. GetEnvironmentVariable ("systemdrive") +@ "\yjinglee";Directory. CreateDirectory (Usertempfolder);//2. Creating a new database NewcreatedbstringUsermdf = System.IO.Path. Combine (Usertempfolder,@ "Newcreatedb.mdf");stringConnStr =String. Format (@ "Data source=.\sqlexpress; Attachdbfilename={0};integrated security=true; Connect timeout=30; User instance=true; Integrated Security = SSPI; ", usermdf);Newcreatedbnewdb =NewNewcreatedb(CONNSTR); Newdb.createdatabase ();//3. Inserting data and queryingvarNewRow =New Person{PersonID = 1, PersonName ="Yjinglee", age = 22}; NewDB.Persons.InsertOnSubmit (NewRow); Newdb.submitchanges ();varQ = fromXinchNewdb.personsSelectX//4. Deleting a databaseNewdb.deletedatabase ();//5. Deleting a temp directoryDirectory. Delete (Usertempfolder);

Database validation

The Databaseexists method is used to attempt to open a database by using a connection in DataContext if the success returns TRUE.

The following code shows whether the Northwind database and the Newcreatedb database exist.

//Detect if the Northwind database existsoff(Db. Databaseexists ())Console. WriteLine ("The Northwind database exists");Else    Console. WriteLine ("The Northwind database does not exist");//Detect if Newcreatedb database existsstringUsertempfolder =Environment. GetEnvironmentVariable ("Temp");stringUsermdf = System.IO.Path. Combine (Usertempfolder,@ "Newcreatedb.mdf");Newcreatedbnewdb =NewNewcreatedb(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 insert, update, or delete, and executes the appropriate command to implement changes to the database.

No matter how many changes an object makes, it simply changes the in-memory copy. No changes have been made to the actual data in the database. The changes are not transferred to the server until SubmitChanges is explicitly called on DataContext. When called, DataContext will try to convert our changes to an equivalent SQL command. We can also use our own custom logic to override these operations, but the order of submission is coordinated by a service called "Change Processor" by DataContext. The sequence of events is as follows:

1. When SubmitChanges is called, LINQ to SQL examines a collection of well-known objects to determine whether new instances are attached to them. If attached, these new instances are added to the collection of tracked objects.
2. All objects that have pending changes are sorted into a sequence of objects according to their dependencies. If an object's changes depend on other objects, the object will be ranked after its dependencies.
3. When any actual changes are about to be transmitted, LINQ to SQL initiates a transaction that encapsulates a series of individual 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, it will cause the commit process to stop and throw an exception. All changes to the database are rolled back as if they had not been committed. DataContext still has a complete record of all changes.

The code below illustrates a customer querying CustomerID as ALFKI in a database, then modifying its company name, updating and invoking the SubmitChanges () method for the first time, The data was updated the second time but the SubmitChanges () method was not called.

Query  "ALFKI"); //Update data and call the SubmitChanges () method "Yjinglee ' s Blog";d B. SubmitChanges (); //Update data does not call the SubmitChanges () method "http://lyj.cnblogs.com";

Dynamic Query

Using dynamic queries, this example creates a statement of the iqueryable<t> type expression output query with the CreateQuery () method. Here is an example to illustrate. For dynamic query details, the next article.

varC1 =Expression. Parameter (typeof(Customer),"C");PropertyInfoCity =typeof(Customer). GetProperty ("City");varPred =Expression. lambda<Func<Customer,BOOL>> (Expression. Equal (Expression. (C1, city),Expression. Constant ("Seattle")), C1);IQueryablecusts = db. Customers;ExpressionExpr =Expression. Call (typeof(queryable),"Where",NewType[] {custs. ElementType}, Custs. Expression, pred);IQueryable<Customer> q = db. Customers.asqueryable (). provider.createquery<Customer> (expr);

Log

The Log property is used to print SQL queries or commands to TextReader. This method may be useful for understanding LINQ to SQL features and debugging specific problems.

The following example uses the Log property to display this code in a console window before the SQL code executes. We can use this property with the query, insert, UPDATE, and delete commands.

//Turn off log function//db. Log = null;//Use logging function: Log output to console windowDb. Log =Console. Out;varQ = fromCinchDb. CustomerswhereC.city = ="London"SelectC//Log output to fileStreamWriterSW =NewStreamWriter(Server.MapPath ("Log.txt"),true);d B. Log = SW;varQ = fromCinchDb. CustomerswhereC.city = ="London"SelectC;SW. Close ();

Copyright NOTICE: This article for Bo Master http://www.zuiniusn.com original article, without Bo Master permission not reproduced.

LINQ Experience (--LINQ) DataContext to SQL statements