C#+EF+SQLite数据库操作 -清零世界

bubuko 2022/1/25 19:24:29 dotnet 字数 37476 阅读 1212 1 来源 http://www.bubuko.com/infolist-5-1.html

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="30000" />
    <add key="loadingImage" value="\Images\loading.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
    {
        private static readonly string conStr = ConfigurationManager.ConnectionStrings["SQLiteDB"].ConnectionString;

public static string GetConnection()
{
return ConfigurationManager.ConnectionStrings["SQLiteDB"].ConnectionString;
}

public static int GetUpdateTime()
{
int time = 0;
int.TryParse(ConfigurationManager.AppSettings["UpdateTime"].ToString(), 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<CarShop> CarShop { set; get; }
        public DbSet<CarType> CarType { set; get; }

    }

5、数据表类

public class CarShop
    {
        [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 partial class Main : Form
    {public System.Timers.Timer sendTimer = new System.Timers.Timer();

        public Main()
        {
            InitializeComponent();
             NewStart();
          
        }


        public void NewStart()
        {
            Thread thread = new Thread(new ThreadStart(UpdateShopCar));
            thread.Start();
        }

     /// <summary>
        /// 更新数据
        /// </summary>
        public void UpdateShopCar() 
        {
            int interval = SQLiteHelper.GetUpdateTime();
            string strdb = SQLiteHelper.GetConnection();
            var strArr = strdb.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)}

            //首次打开程序默认更新
            GetShopCar();
            //定时更新数据
            sendTimer.Interval = interval;
            sendTimer.Elapsed += (sender, e) => { GetShopCar(); };
            sendTimer.Start();
        }

        /// <summary>
        /// 定时更新汽车商店数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void GetShopCar()
        {
            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. 用技术分享图片工具进行管理查看数据库即可

C#+EF+SQLite数据库操作

原文：https://www.cnblogs.com/Warmsunshine/p/13833109.html

如果您也喜欢它,动动您的小指点个赞吧

C#+EF+SQLite数据库操作

分类汇总

您的鼓励是对我最大的支持