Apply EF to access SQLite data

1. Create a project

As shown in the initial structure of the project structure, the Netage.Data.SQLite class library project is used to define the interfaces and methods that access the data, Netage.SQLiteTest.UI the console project to reference the Netage.Data.SQLite class library, and call its corresponding method to access the data.

2. Add SQLite class library to the project

Right-Netage.Data.SQLite The project, select the "Manage Nuget Packages" menu, enter "System.Data.SQLite" in the input box, query to "System.Data.SQLite (x86/x64)", and click Install. ：

When the installation is complete, the corresponding class library is introduced in the "Netage.Data.SQLite" project.

3. Define the data context and the corresponding entity class

public class mycontext:dbcontext{Public    dbset<person> Persons {get; set;}    Public Mycontext ()        : Base ("Sqlitetest")    {    }}

public class person{Public    int Id {get; set;}    public string Name {get; set;}    Public DateTime BirthDay {get; set;}}

4. Modify the configuration file

When you install "System.Date.SQLite (x86/x64)", Some configuration information is added by default in the Class Library project's app. config file, but because now we need to access the data through a console project that accesses the method of the Class library project, the configuration information needs to be copied from the Class library project to the console project. and remove the App. Config file from the class library project. The configuration information in the console project is as follows:

<?xml version= "1.0" encoding= "Utf-8"?><configuration> <configSections> <!--for more Informa tion on Entity Framework configuration, visit http://go.microsoft.com/fwlink/? linkid=237468--<section name= "EntityFramework" type= " System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, version=6.0.0.0, Culture=neutral, publickeytoken=b77a5c561934e089 "requirepermission=" false "/> </configSections> <system.data> &L t;dbproviderfactories> <remove invariant= "System.Data.SQLite.EF6"/> <add name= "SQLite Data Prov Ider (Entity Framework 6) "invariant=" System.Data.SQLite.EF6 "description=". NET Framework Data Provider for SQLite (entit Y-Framework 6) "Type=" System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6 "/> </dbproviderfac tories> </system.data> <entityFramework> <defaultconnectionfactory type= "system.data.entity.i NfrastructuRe. Sqlconnectionfactory, entityframework "/> <providers> <provider invariantname=" System.Data.SqlClie NT "Type=" System.Data.Entity.SqlServer.SqlProviderServices, entityframework.sqlserver "/> <provider invariant Name= "System.Data.SQLite.EF6" type= "System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6"/> & Lt;/providers> </entityFramework></configuration>

This information is automatically configured when you install SQLite, because we define the data connection string name "Sqlitetest" in mycontext, so we need to include the connection string information in the configuration file.

<connectionStrings>    <add name= "sqlitetest" connectionstring= "Data source=sqlitetest.db" providername= "System.Data.SQLite.EF6"/>  </connectionStrings>

5. Access data through Mycontext in the console project

(1) referencing the "Netage.Data.SQLite" Class Library project

(2) Introduction of other DLLs

(3) Access to data via Mycontext

Class program{    static void Main (string[] args)    {        using (var context = new Mycontext ())        {            Console.WriteLine (context. Persons.count ());        }        Console.WriteLine ("Run End");        Console.readkey ();    }}

(4) Running the console project

(5) Manually copy the x64 and x86 from the "Packages\system.data.sqlite.core.1.0.103\build\net45" in the installation package to the Bin\Debug directory of the console project, as shown below.

(6) Run the console project again

(7) Modify the configuration file again, the modified configuration has been identified by the Red section, as follows:

<?xml version= "1.0" encoding= "Utf-8"?><configuration> <configSections> <!--for more information On Entity Framework configuration, visit http://go.microsoft.com/fwlink/? linkid=237468--<section name= "EntityFramework" type= " System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, version=6.0.0.0, Culture=neutral, publickeytoken=b77a5c561934e089 "requirepermission=" false "/> </configSections> <system.data> < dbproviderfactories> <remove invariant= "System.Data.SQLite"/> <add name= "SQLite Data Provider" Inva Riant= "System.Data.SQLite" description= ". NET Framework Data Provider for SQLite" type= "System.Data.SQLite.SQLite  Factory, System.Data.SQLite "/> <remove invariant=" System.Data.SQLite.EF6 "/> <add name=" SQLite Data Provider (Entity Framework 6) "invariant=" System.Data.SQLite.EF6 "description=". NET Framework Data Provider for SQLite (E Ntity Framework 6) "            Type= "System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6"/> </dbproviderfactories&  Gt </system.data> <entityFramework> <defaultconnectionfactory type= " System.Data.Entity.Infrastructure.SqlConnectionFactory, entityframework "/> <providers> <provider Inva Riantname= "System.Data.SqlClient" type= "System.Data.Entity.SqlServer.SqlProviderServices, Entityframework.sqlserver "/> <provider invariantname=" System.Data.SQLite.EF6 "type=" System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6 "/> <provider invariantname=" System.Data.SQLite "Type=" System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6 "/> </ providers> </entityFramework> <connectionStrings> <add name= "sqlitetest" connectionstring= "data so Urce=sqlitetest.db "providername=" System.Data.SQLite.EF6 "/> </connectionStrings></configuration>

(8) Run the application again as follows:

This is because EF SQLite does not support Codefirst mode, because so far we have not created a database and table structure, so we have this error.

Create database and table structure with SQLite expert

1, create the database, specify the database file name and file storage location

2. Create a table structure, add columns, and set the properties of a column

3, the test table is created, you can try to operate the other functions of the tool, here no longer elaborate.

Apply EF to access SQLite database data

Because the database files have been stored in a different location, you may need to modify the configuration file to a specific newly created database.

<add name= "Sqlitetest" connectionstring= "Data source=c:\users\paulhuang\desktop\sqlitetest" ProviderName= " System.Data.SQLite.EF6 "/>

In this case, we can write LINQ code to test the corresponding functionality.

1. Inserting data

2. Modify the data

The basic operation of accessing SQLite via EF is basically here, and if there is an incorrect place, please correct it.

Apply EF to access SQLite data