我的sqlLite的操作模板

public class SQLHelper
{
string SqlFile = Environment.CurrentDirectory + “\sql.db”;
IDbConnection conn = null;

    public IDbConnection   GetConn()
    {
        if (File.Exists(SqlFile))//文件存在就操作打开
        {
            conn = new SQLiteConnection(string.Format("Data Source ={0}", SqlFile));//string.Format("Data Source ={0}; Version = 3; Legacy Format = True", SqlFile));

            try
             {
                    conn.Open();
                return conn;
           }
                catch (Exception ex)
            {
                    throw new Exception("数据库打开错误:" + ex.Message);
             }
            
        }
        else//如果文件不存在则创建
        {
            SQLiteConnection.CreateFile(SqlFile);
            string CreateTabel = @"CREATE TABLE ControlsSet(Id integer NOT NULL,FormName TEXT,ControlName TEXT,X integer,Y integer,Heigte integer,Width integer,
                                FontType TEXT,Type TEXT,ConText TEXT,EnableSet integer, PRIMARY KEY(Id)); ";
            SQLiteConnection sqliteConn = new SQLiteConnection(string.Format("Data Source ={0}; Version = 3; Legacy Format = True", SqlFile));
            sqliteConn.Open();
            SQLiteCommand cmd = new SQLiteCommand(sqliteConn);
            cmd.CommandText = CreateTabel;
            cmd.ExecuteNonQuery();
            return sqliteConn;
        }

    }
    /// <summary>
    /// 获取相关form内数据
    /// </summary>
    /// <param name="FormName"></param>
    /// <returns></returns>
    public List<FormAtAll> GetOneForm(string FormName)
    {
        using (IDbConnection conn = GetConn())

        {
            List<FormAtAll> controlList = null;
            string sql = "select * from ControlsSet where FormName = @FormName";
            if (conn != null)
            {
                controlList = new List<FormAtAll>();
                controlList = conn.Query<FormAtAll>(sql, new { FormName = FormName }).ToList();

            }
            return controlList;

        }
            
    }
    /// <summary>
    /// 获取当前模型是否存在
    /// </summary>
    /// <param name="model"></param>
    /// <returns></returns>
    public List<FormAtAll> GetCountThisControl(FormAtAll model)
    {
        using (IDbConnection conn = GetConn())

        {
            List<FormAtAll> controlList = null;
            string sql ="select * from ControlsSet Where FormName =@FormName AND ControlName =@ControlName";
            if (conn != null)
            {
                controlList = new List<FormAtAll>();
                controlList = conn.Query<FormAtAll>(sql,model).ToList();

            }
            return controlList;
        }
    }
    /// <summary>
    /// 获取插入或更新数据
    /// </summary>
    /// <param name="list"></param>
    /// <returns></returns>
    public int InsertOrUpdateForms(List<FormAtAll> list)
    {

        int Outint = 0 ;
        using (IDbConnection conn = GetConn())

        {
            foreach (FormAtAll one in list)
            {
                string sql = string.Empty;
                if (GetCountThisControl(one).Count > 0)//大于0就是 update 
                {
                    sql = "update ControlsSet set  FormName =@FormName , ControlName = @ControlName, X=@X, Y =  @Y,Heigte=@Heigte,Width = @Width, FontType =@FontType,Type =@Type ,ConText =@ConText, EnableSet = @EnabelSet where  FormName =@FormName AND ControlName =@ControlName";
                }
                else //小于就是插入
                {
                    sql = @" INSERT INTO ControlsSet (FormName,ControlName,X,Y, Heigte, Width, FontType, Type, ConText, EnableSet) values (@FormName, @ControlName, @X, @Y, @Heigte, @Width,@FontType, @Type,@ConText ,@EnableSet)";
                }
                Outint += conn.Execute(sql, one);
            }
            return Outint;
        }
    }



}

其中需要安装在dapper 安装以后，在system.data.sqllite 安装Nuget 后确认正常。

sqlite 数据的 时间 获取：

select datetime('now')    -----------2021-01-05 07:52:58
select date('now')    -----------2021-01-05 

sqlite获取最后一条插入的数据：

select last_insert_rowid()  ///如何确认你在那张表呢？就是在同一个connection下面

https://www.runoob.com/sqlite/sqlite-date-time.html