LINQ to SQLite tutorial

This tutorial will guide you through creating a simple application technology to support the LINQ to SQL technology. In less than 5 minutes, you will have a data access layer that can be used at any time for your business objects

The example is as follows:
Introduced the technology of LINQ to SQL
Requirements
Compile a project
Generate model from database
Query data
Insert new data
Update Data
Delete data
Other information

Introduced the technology of LINQ to SQL
LINQ advocates integrated language query, which means that data retrieval is no longer an independent language. Which is allowed by the LINQ engine. . NET application connection to the database has a lot of concerns about columns and rows. The data you receive is an automatically generated object ready to use your business logic.
LINQ to relational data may be considered as an object relational ing (ORM) tool. This type of secure LINQ query is compiled into msil for execution, and the query terms are translated into SQL and sent to the SQLite database for execution. This makes your data access layer more secure, fast, and convenient.

Requirements:
To connect to the running of your SQLite database server, install SQLite dotconnect and run IDE. What is needed for writing data from the SQL to the SQL. Net Framework 3.5 and Visual Studio 2008, and SQLite database 3 or later. Note: The function of LINQ to SQLite is only applicable to dotconnect of SQLite in Professional Edition.
This tutorial assumes that you have created database objects. You must execute a script from the following files if you have not done so:

Compile a project
Create a new console application in Visual Studio. It can be of any other project type, but for simplicity, we will use the entire tutorial of the console project. The remaining tutorials assume that the project name is consoleapplication1. If your project is named otherwise, you will have to replace this name with the actual Solution Explorer.

 
1. Generate a model from the database
Add a devart LINQ to SQL model project. To do this, right-click the reference node in Solution Explorer, point to add, and click New project .... in the Add new project dialog box, select data, select devart LINQ to SQL template, and click Add. This will automatically launch Entity Development, modify and save the demo project.

2. Select object development from the main menu and select file | create a database. This launch database reverse engineering wizard.
3. Click Next on the welcome screen. In this case, the object Development calls from the Visual Studio's LINQ to SQL mode, and this screen is disabled in the framework selected.
4. Fill in the connection settings and click Next.

5. Select a database object for the model. All these objects are run from the crm_demo script, including the auxiliary table. Click Next.

6. On the next screen, you can adjust the entity and its members of the naming rule. To demonstrate to the customer that the database has no rules, you only need to click Next.

7. Enter the crmdemodata namespace and the name of crmdemodatacontext after datacontext. This will be the primary data category of the name. Click Next.

8. click Next. And generate the code model is the open EMD design framework code.
9. On the main menu, click File | SAVE project. This will update the datacontext1 model in Visual Studio.
This model is ready for use. You can check its object development visual and graph and toolwindows, edit courses and relationships in various aspects, and add new model elements. Remember to save the project so that changes to development entities are reflected in Visual Studio.

All selected course lists created by this wizard represent entities. It also creates a system. Data. LINQ. datacontext, which controls the connection to the database and the entire data flow. This class contains the attributes and method named database objects. You will use these Members to retrieve and modify data categories. These codes are included in the datacontext. CS (datacontext. VB) file ).

Query data
You can use datacontext to execute all the operations of LINQ to SQLite. This is the name of crmdemodatacontext in this Guide. To retrieve data, you must first create an instance, then write a query to the SQL statement, and then access the query returned by the object. This may be a collection object or an object.
Let's look at all the data from the table company, sort it companyid, and output some columns. You can add the following code blocks:

[C #] CS

CrmDemoDataContext context = new CrmDemoDataContext();var query = from it in context.Companies            orderby it.CompanyID            select it;foreach (Company comp in query)  Console.WriteLine("{0} | {1} | {2}", comp.CompanyID, comp.CompanyName, comp.Country);Console.ReadLine();

[Visual Basic] VB

Dim context As CrmDemoDataContext = New CrmDemoDataContextDim query = From it In context.companies _    Order By it.CompanyID _    Select itDim comp As companyFor Each comp In query    Console.WriteLine("{0} | {1} | {2}", comp.CompanyID, comp.CompanyName, comp.Country)NextConsole.ReadLine()

That's simple. Your query statement, and then iterate through it, you will make a common collection object. The interaction between the database is based on the background of LINQ to SQLite. Now let's see who is here for the sample code.

 
With the name of crmdemodatacontext, you can know all the information about your model architecture and all its processing operations. All LINQ to SQLite operations to fulfill the attributes and methods in this category. We recommend that you keep the entire application of an instance because it consumes a lot of resources and the entity cannot use different data sharing environments.
Query, which is a LINQ to SQL statement in any variable name. The former is the object used to collect data, and the latter is a non-use declaration.
Context. Companies refers to the public property, crmdemodatacontext context. This attribute represents the category of all collected data.
The company name (in the foreach statement) is a product and first-class. This class maps to the company database in the table and its name comes from it.
The following figure shows the output of the project on the console:

Note: The query code of LINQ to SQL only describes the query. It is not executed. This approach is called deferred execution.
Now let's query the key data from two tables in the United States and foreign countries. The old replacement code is as follows:

[C #]

CrmDemoDataContext context = new CrmDemoDataContext();var query = from it in context.Companies            orderby it.CompanyID            select it;foreach (Company comp in query) {  if (comp.PersonContacts.Count > 0) {    Console.WriteLine("{0} | {1} | {2}",      comp.CompanyName, comp.PersonContacts[0].FirstName,      comp.PersonContacts[0].LastName);  }}Console.ReadLine();

[Visual Basic]

Dim context As CrmDemoDataContext = New CrmDemoDataContextDim query = From it In context.companies _    Order By it.CompanyID _    Select itDim comp As companyFor Each comp In query    If comp.personcontacts.Count > 0 Then        Console.WriteLine("{0} | {1} | {2}", _              comp.CompanyName, comp.personcontacts(0).FirstName, _              comp.personcontacts(0).LastName)    End IfNextConsole.ReadLine()

As you can see, the definition of the LINQ to SQL query has not changed. The data contacts automatically access the corresponding property when they are taken from the database. The controlled object. This is a great thing to do with LINQ to SQL: You don't have to worry about querying when writing dependencies.

Insert new data
What is the addition of a row table in the early stage? Now, only a new set within the object range is added. When you are ready to send changes to the database, call the submitchanges () method within the range. Before that, you must first set all attributes and do not support null (NO) values. The submitchanges () method generates and executes commands, and the execution is equivalent to the insert, update, or delete Statement on the data source.
Let's add a new product and a new type of database. The old replacement code is as follows:

[C #]

CrmDemoDataContext context = new CrmDemoDataContext();// Create a new categoryProductCategory newCategory = new ProductCategory();newCategory.CategoryID = 1000;newCategory.CategoryName = "New category";// Create a new productProduct newProduct = new Product();newProduct.ProductID = 2000;newProduct.ProductName = "New product";newProduct.Price = 20;// Associate the new product with the new categorynewProduct.ProductCategory = newCategory;context.Products.InsertOnSubmit(newProduct);// Send the changes to the database.// Until you do it, the changes are cached on the client side.context.SubmitChanges();// Request the new product from the databasevar query = from it in context.Products            where it.ProductID == 2000            select it;// Since we query for a single object instead of a collection, we can use the method First()Product product = query.First();Console.WriteLine("{0} | {1} | {2}",  product.ProductCategory.CategoryName, product.ProductName, product.Price);Console.ReadLine();

[Visual Basic]

Dim context As CrmDemoDataContext = New CrmDemoDataContext' Create a new categoryDim newCategory As productcategory = New productcategory()newCategory.CategoryID = 1000newCategory.CategoryName = "New category"' Create a new productDim newProduct As product = New product()newProduct.ProductID = 2000newProduct.ProductName = "New product"newProduct.Price = 20' Associate the new product with the new categorynewProduct.productcategory = newCategorycontext.products.InsertOnSubmit(newProduct)' Send the changes to the database.' Until you do it, the changes are cached on the client side.context.SubmitChanges()' Request the new product from the databaseDim query = From it In context.products _            Where it.ProductID = 2000 _            Select it' Since we query for a single object instead of a collection, we can use the method First()Dim product As product = query.First()Console.WriteLine("{0} | {1} | {2}", _  product.productcategory.CategoryName, product.ProductName, product.Price)Console.ReadLine()

The category of each set created by the insertonsubmit () method. This method stores all information in the database and objects. As shown in the example, it is only necessary to call insertonsubmit () to send objects to the two products and categories again.
Note that when you add a new product or category and submit a change, you will not be able to execute this solution again. Objects that change the ID card again for the solution will be added.

Update Data
Objects are modified as usual. The only thing to remember is that you must invoke the submitchanges () method to send data to the database.
Add the following existing code and startup project:

[C #]

product.ProductName = "Edited product";product.Price = 15;context.SubmitChanges();

[Visual Basic]

product.ProductName = "Edited product"product.Price = 15context.SubmitChanges()

Delete data
An instance to be extracted is collected from the context using the deleteonsubmit method. Objects are from the collection type rather than destroyed. If you want to delete the data of an object, call the submitchanges () method from the database.
To do this, you can use the following code:

[C #]

context.products.DeleteOnSubmit(newProduct);context.productcategories.DeleteOnSubmit(newCategory);context.SubmitChanges();

[Visual Basic]

context.products.DeleteOnSubmit(newProduct)context.productcategories.DeleteOnSubmit(newCategory)context.SubmitChanges()

The deleted object is a property model. When deleterule parameter cascade, dependent objects are automatically deleted. When this parameter is set to null, the dependent object is not deleted, but the link is invalid. It is very important that there is no sequence of deletion specified by the rule.
Other information
Now, you can execute basic data processing and the SQLite to be written to. You can enter some advanced topics. The SQLite of dotconnect includes specialized LINQ to SQL technologies for help. You can visit http://www.devart.com/dotconnect/linq/docs/or the Visual Studio help set.
As a developed country that closely cooperates with Microsoft to execute LINQ to SQL, you may find some useful information in msdn:
LINQ to SQL
LINQ to SQL :. . Net Integrated Query of relational databases
To experience SQLite running dotconnect as a sample. You can access the sample menu from the Start Menu.
To gain a deeper understanding of the work's LINQ to SQL engine, you can see the generated SQL statement in dbmonitor or use the datacontext. Log property.