C#+EF+SQLite数据库操作
1、首先在线安装SQLite 主要的引用
2.App.config配置文件
<?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> <appSettings> <add key="UpdateTime" value="600000" /> <add key="loading" value="\Images\update.gif"></add> <add key="HttpApiUrl" value="https://TestApi.Api.com/service"></add> <add key="ApiUrlToken" value="https://TestApi.com/UserControl/GetToken"></add> </appSettings> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1"/> </startup> <entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework"> <parameters> <parameter value="v11.0"/> </parameters> </defaultConnectionFactory> <providers> <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6"/> <provider invariantName="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"/> </providers> </entityFramework> <connectionStrings> <add name="SQliteDB" connectionString="Data Source=./DB/Test.sqlite;Version=3;BinaryGUID=False" providerName="System.Data.SQLite.EF6"/> </connectionStrings> <system.data> <DbProviderFactories> <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 (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6"/> <remove invariant="System.Data.SQLite"/> <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite"/> </DbProviderFactories> </system.data> <system.web> <membership defaultProvider="ClientAuthenticationMembershipProvider"> <providers> <add name="ClientAuthenticationMembershipProvider" type="System.Web.ClientServices.Providers.ClientFormsAuthenticationMembershipProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri=""/> </providers> </membership> <roleManager defaultProvider="ClientRoleProvider" enabled="true"> <providers> <add name="ClientRoleProvider" type="System.Web.ClientServices.Providers.ClientRoleProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" cacheTimeout="86400"/> </providers> </roleManager> </system.web> </configuration>
3 、项目文件目录下创建一个DB文件夹下放数据库文件 Test.sqlite 设置该文件属性为始终复制 运行项目时会自动复制到生成DEBUG文件中 
4、数据库操作类：
SQLiteHelper
public class SQLiteHelper {
public static string GetConnection()
{
return ConfigurationManager.ConnectionStrings["SQLiteDB"].ConnectionString;
}
public static int UpdateTime()
{
int time = 0;
int.TryParse(ConfigurationManager.AppSettings["UpdateTime"], out time);
return time;
}
/// <summary> /// 创建数据库 /// </summary> /// <param name="DbFilePath"></param> public void CreateSqlLiteDB(string DbFilePath) { try{ SQLiteConnection.CreateFile(DbFilePath); } catch (SQLiteException ex) { throw new Exception("据库" + DbFilePath + "失败：" + ex.Message); } } /// <summary> /// 创建表1 /// </summary> /// <param name="dbPath"></param> /// <param name="tableName"></param> public void CreateNewTab(string dbPath, string tableName) { SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath); if (sqliteConn.State != System.Data.ConnectionState.Open) { sqliteConn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = sqliteConn; cmd.CommandText = "CREATE TABLE " + tableName + "(create table " + tableName + "(Gid text,CarOpenGid text,CarNo nvarchar(20),CarColor nvarchar(15),CarName nvarchar(15),ShopName nvarchar(100),CreateDate datetime))"; cmd.ExecuteNonQuery(); } sqliteConn.Close(); }
/// <summary>
/// 创建表2
/// </summary>
/// <param name="dbPath"></param>
/// <param name="tableName"></param>
public void CreateNewTab2(string dbPath, string tableName)
{
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
cmd.CommandText = "CREATE TABLE " + tableName + "(Gid TEXT PRIMARY KEY,CarGid TEXT, CarType nvarchar(50), CreateDate datetime,CarOrderDate datetime)";
cmd.ExecuteNonQuery();
}
sqliteConn.Close();
}
}
4、EF数据库配置类
CarDbContext.cs public class CarDbContext:DbContext { public CarDbContext(): base("SQLiteDB") { } public DbSet<Car> Car { set; get; } public DbSet<CarType> CarType { set; get; } }
5、数据表类
public class Car { [Key] public Guid Gid { set; get; } public string CarNo { set; get; } public string CarShop { set; get; } public string CarName { set; get; } public DateTime? CreateDate { set; get; } }
public class CarType { [Key] public Guid Gid { set; get; } public Guid CarGid { set; get; } public string CarType { set; get; } public DateTime? CreateDate { set; get; } public DateTime? CarOrderDate { set; get; } }
6、创建一个主窗体页面代码
public class Main : Form {
System.Timers.Timer sendTimer = new System.Timers.Timer(); public Main() { InitializeComponent(); NewStart();
sendTimer.Start();
sendTimer.Elapsed += new System.Timers.ElapsedEventHandler(test);
}
public void NewStart()
{ Thread thread = new Thread(new ThreadStart(UpdateShopCar)); thread.Start(); }
public void UpdateShopCar()
{
int interval = SQLiteHelper.GetUpdateTime();//执行间隔时间,单位为毫秒; 这里实际间隔为10分钟
string strdbcontion = SQLiteHelper.GetConnection();
var strArr = strdbcontion.Split(new string[] { "Data Source=", ";" }, StringSplitOptions.RemoveEmptyEntries);
string SQLiteDbFilePath = strArr[0]; //如果本地不存在数据库，在创建
if (!System.IO.File.Exists(SQLiteDbFilePath))
{ SQLiteHelper SQLite = new SQLiteHelper();
SQLite.CreateSqlLiteDB(SQLiteDbFilePath);
SQLite.CreateNewTab(SQLiteDbFilePath, "Car");
SQLite.CreateNewTab2(SQLiteDbFilePath, "CarType");
}
else
{System.IO.File.Delete(SQLiteDbFilePath)} sendTimer.Interval = interval; sendTimer.Elapsed += (sender, e) => { upLoadShopCar(); }; sendTimer.Start(); } /// <summary> /// 定时更新汽车商店数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void uploadShopCar() { try { CarService car = new CarService(); var list = car.GetShopCarList(); using (CarDbContext context = new CarDbContext()) { foreach (var item in list) { List<CarType> typeList = null; Car Car = new Car(); CarType CarType = new CarType(); var oldcar = context.Car.SingleOrDefault(x => x.Gid == item.CarGid); if (oldcar != null) { typeList = context.CarType.Where(s => s.CarGid == lite.CarGid && s.CreateDate > s.UpdateTime).ToList(); if (typeList.Any()) { foreach (var itemOld in typeList) { CarType.CreateDate = itemOld.CreateDate; CarType.CarGid = itemOld.CarGid; FingerPrintType.Gid = itemOld.Gid; context.CarType.Add(CarType); var type = context.CarType.FirstOrDefault(s => s.Gid == itemOld.Gid); if (type != null) { type.UpDateTime = DateTime.Now; } } } } else { Car.Gid = item.CarGid; Car.CreateDate = item.CreateDate; Car.ShopName = item.ShopName; context.Car.Add(Car); CarType.CreateDate = item.CreateDate; CarType.CarType = item.CarType; CarType.CarGid = item.CarGid; CarType.Gid = item.Gid; FingerPrintType.UpDateTime = DateTime.Now; context.CarType.Add(CarType); } context.SaveChanges(); } } } catch(Exception ex) { System.Diagnostics.Debug.WriteLine(ex); sendTimer.Stop(); } } } }
7. 用
工具进行管理查看数据库即可
