• SQLite
  • SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine.
  • https://www.sqlite.org/index.html
  • License: Public Domain, Open-Source, not Open-Contribution
• SQLite for .NET (System.Data.SQLite)
  • introduction
  • An ADO.NET provider for SQLite including EF and Linq functions.
  • http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki
  • Developed by SQLite Development Team
  • License: Public Domain, Open-Source, not Open-Contribution
  • how to use
    • Add references (Nuget to find “System.Data.SQLite”)
      • EntityFramework (can be upgraded to 6.2.0, already include EF and Linq)
      • System.Data.SQLite
      • System.Data.SQLite.Core
      • System.Data.SQLite.EF6 (can be removed)
      • System.Data.SQLite.Linq (can be removed)
    • Setup configuration file (in App.config)
      • connection string
      • db provider
    • Create custom context class

在App.Config或Web.config文件中配置数据库的连接字符串及使用到的Provider：

 1 <?xml version="1.0" encoding="utf-8"?> 2 <configuration> 3 <configSections> 4 <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 --> 5 <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/> 6 </configSections> 7 <connectionStrings> 8 <add name="PresetDataConnectionString" connectionString="data source=PresetData.db" providerName="System.Data.SQLite"/> 9 </connectionStrings>10 <entityFramework>11 <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">12 <parameters>13 <parameter value="v13.0"/>14 </parameters>15 </defaultConnectionFactory>16 <providers>17 <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6"/>18 </providers>19 </entityFramework>20 <system.data>21 <DbProviderFactories>22 <remove invariant="System.Data.SQLite"/>23 <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite"/>24 </DbProviderFactories>25 </system.data>26 <startup>27 <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.2"/>28 </startup>29 </configuration>

自定义Context类，用于操作数据库：

 1 /// <summary> 2 /// Code First mode to use EF, on behalf of one DB 3 /// </summary> 4 public class UserDataContext : DbContext 5  { 6 public DbSet<FavoriteData> FavoriteDatas { get; set; } 7  8 public UserDataContext(string connectionString) 9 : base(new SQLiteConnection() { ConnectionString = connectionString }, true)10  {11 12  }13 14 /// <summary>15 /// avoid "no such table error" of __MigrationHistory and EdmMetadata in EF Code-First Mode16 /// </summary>17 static UserDataContext()18  {19 Database.SetInitializer<PresetDataContext>(null);20  }21 22 /// <summary>23 /// dummy code so that the compiler would detect that the reference is being used.24 /// make sure projects like VisualXml and VisualXml.Test have related dlls in output bin folder25 /// </summary>26 private static void FixProvidersNotAutoLoadProblem()27  {28 var _ = typeof(System.Data.SQLite.EF6.SQLiteProviderFactory);29 var __ = typeof(System.Data.SQLite.SQLiteFactory);30 //var ___ = typeof(System.Data.Entity.SqlServer.SqlProviderServices);31  }32 33 /// <summary>34 /// remove restriction of DbSet field names based on table names35 /// </summary>36 /// <param name="modelBuilder"></param>37 protected override void OnModelCreating(DbModelBuilder modelBuilder)38  {39 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();40 41 //modelBuilder.Configurations.AddFromAssembly(typeof(DefaultContext).Assembly);42 //modelBuilder.Configurations.AddFromAssembly(Assembly.GetExecutingAssembly());43  }44 }

也可以动态设置数据库连接字符串：

1 SQLiteConnectionStringBuilder builder = SQLiteFactory.Instance.CreateConnectionStringBuilder() as SQLiteConnectionStringBuilder;2 3 // set up connection string for user data db in code4 builder.DataSource = userDataDBFilePath;5 builder.Password = GenerateEncryptedDBPwd();6 UserDataContext userDataDB = new UserDataContext(builder.ToString());