LINQ to SQLite Practice Guide

Preface

Currently, software applicationsProgramDatabase has become an indispensable and important part. however, traditional databases tend to become dominant, with more and more system requirements and higher management costs. For small and medium projects, many of their functions become redundant, however, I have to pay more for these unnecessary features and labor costs. in these scenarios, the lightweight, zero-deployment, cross-platform, and portable embedded databases undoubtedly have unparalleled advantages over traditional databases. Of course, we must give up high capacity, high performance, and high concurrency, but who cares? We only need small and medium-sized applications, with a smaller size than 1 million and a smaller concurrency than 10 million.

In. net, we can choose a lot of embedded databases, db4o, SQLite, various xml db. I used to prefer db4o and db4o for traditional ADO. net driver development has more advantages, without manual ing, zero SQL. however, because db4o does a lot of processing, we can only intervene in the data persistence process, while db4o is in composite attributes and cascading attributes, especially when the entire system is designed with rich domain models, data storage often fails. therefore, we have to manually persist all objects in the composite object and add associated fields for association. As a result, the more we get closer to traditional databases such as SQL Server, the more we get, the less we get. in Web applications, data transmission must be performed in the server/client mode to provide concurrency capabilities. it always makes me feel awkward.

Since dlinq came out, my project has become more and more dependent on it. It makes our program more elegant and clear, and there is no need to piece together a piece of SQL, write configurations over and over again. unfortunately, Ms only provides the implementation of LINQ to SQL (ms SQL Server. fortunately, after the emergence of the LINQ idea,CommunityThe emergence of a variety of LINQ provider programs, there is a general architecture (LINQ to everything), SQLite is no exception. Today we come in to explain how to introduce SQLite into our development.

Ado. net provider & LINQ provider

First, download the LINQ provider of SQLite. on the Internet, we can find two LINQ providers for SQLite. one is the system of phxsoftware. data. SQLite, and dotconnect for SQLite of devart. system. data. SQLite is an open-source product, while dotconnect for SQLite is a commercial product (there is a free normal version ). here we select system. data. SQLite is our LINQ provider.

Download sqlite-1.0.60.0-setup.exe from sourceforge, and then install. At the end of installation, you will be asked to install SQLite design-time support and check vs2005/vs2008.

In this way, you can manage your SQLite database files in the Server Explorer of Vs, just like the database that manages SQL Server. After installation, we get the following files:

Create a database

To create an empty SQLite database, you can select either of the two methods.

1. Download The windowless Program (http://www.sqlite.org/sqlite-3_6_19.zip) from http://www.sqlite.org/download.htmland create it through the command line.

• . Db3 suffix names can be any, you can also use any other suffix names.
• In the shell of SQLite>, you must enter something. If you exit directly, the database file will not be created.

2. From http://www.sqlite.org/cvstrac/wiki? P = managementtools download various GUI programs.

After the database file is created, copy it to the desired location and open the Server Explorer of vs (if you use a GUI program, you do not need this function of ), we can maintain table information in it

 

 

We first design two tables to continue the following steps.

Create a project

Create datacontext

When you pull the table into dbml, the following error is displayed: "The selected object (s) use an unsupported data provider ."

Dbml designer is the same as dlinq. Microsoft only implements the for SQL Server function, but not other databases. since dmbl cannot be generated, we can generate the Code. In general, this layout is useless. find the code generation tool (Sqlmetal. EXE). Unfortunately, this stuff only supports Ms's home stuff, and does not support the generator extension -_-!!!.

Since the LINQ porivder provided by phxsoftware may provide customCodeGeneration Tool. However, it seems that only generation of entity frameworkd entity model is supported, but not that of LINQ to SQLite (dotconnect for SQLite is supported for both ).

Do we need to manually write datacontext? How is it possible? Someone must have implemented it. Google found a dlinq2007 project and provided a lot of LINQ.

 
Dbmetal.exe/Database: testdatacontext/Schema/namespace = linqtosqlite/sprocs/entityinterfaces =/culture = "en"/code = test. CS/provider = SQLite/Conn = "Data Source = D: \ doc \ My \ data \ code \ linqtosqlite \ DLLs \ test. db3"
 

/Database: Specify the class name,/namespace=linqtosqlite namespace ..you can get the tool (dbmetal.exe/?) under the command line /?).

Because dbmetal.exe is not perfect, not all structures can be generated. For example, if the database has a foreign key, an error occurs.

 

 the generated code is roughly as follows (you may need to manually clear the corresponding class of systemtable):  Public  partial  class  testdatacontext: dblinq. data. LINQ. datacontext 
{< br>  Public  testdatacontext (system. data. idbconnection connection):  base  (connection,  New  dblinq. SQLite. sqlitevendor () 
{< BR >}< br>  Public  testdatacontext (system. data. idbconnection connection, dblinq. vendor. ivendor vendor):  base  (connection, vendor) 
{< BR >}< br>  Public  table order { Get  { return  gettable ();}} 
  Public  table orderitem { Get {< span style =" color: # 0000ff; "> return  gettable ();}} 
}< br> 

 

 

DEMO code and related DLLs download: http://cid-af3411fff50fdeaa.skydrive.live.com/self.aspx/Public/Demo/LinqToSQLite.rar

 

 

This article is based on the signature 3.0 mainland China License Agreement, the original genuine, if there are similar, all are shanzhai, the author reserves the right to pursue. If Chen dayI (including the link), this document can be reproduced, interpreted, or used for commercial purposes. If you have any questions or negotiate with the Authority, please contact me via e-mail/MSN.