【SQLite】使用汇总

一、DateTime类型字段存取问题

1、如果直接存储DateTime.ToString()或DateTime.ToString("yy/MM/dd hh:mm:ss")（写DateTime.ToString("yy-MM-dd hh:mm:ss")会插入数据不成功），能成功插入数据并在SqliteDeveloper中显示正确时间，但从数据库查询时，会报错。

2、用DateTime.ToString("s")插入数据，能成功插入数据，但在SqliteDeveloper中全都显示1899/12/30，从数据库查询时，不会报错。

3、有人说把数据类型设为nvarchar。这样可以成功插入数据并读取，但碰到要用时间作为查询条件时，无法实现功能。可以看看这个帖子：https://bbs.csdn.net/topics/390302153

无奈只能选第二种方式。这问题也是蛮坑的。

 

-----------------更新-----------------

无语了，本来用DateTime.ToString("s")已经测试可以了，不知怎么的今天又发现还是提示无效的DateTime。
又是一通搜索。
原来只能用参数化的方式插入/修改数据，用sql拼接语句就不行。


Sqlite参数化查询：
https://www.cnblogs.com/kdp0213/p/8554032.html

 

我自己写的代码：

        /// <summary>
        /// 指定列插入数据
        /// </summary>
        public bool AddDatas1<T>(T testClass, string tableName)
        {
            string colStr = "";
            string tmpValueStr = "";

            Type t = testClass.GetType();//获得该类的Type
            foreach (PropertyInfo pi in t.GetProperties())
            {
                string name = pi.Name;
                if (name.ToLower() == "id")
                {
                    continue;
                }
                colStr += pi.Name + ",";
                tmpValueStr += "@" + pi.Name + ",";
            }
            colStr = colStr.Substring(0, colStr.LastIndexOf(',')); //删掉最后一个逗号
            tmpValueStr = tmpValueStr.Substring(0, tmpValueStr.LastIndexOf(','));

            string cmdStr = string.Format("insert into {0}({1}) values({2});", tableName, colStr, tmpValueStr);

            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand(cmdStr, SQL_Conn))
                {
                    foreach (PropertyInfo pi in t.GetProperties())
                    {
                        string name = pi.Name;
                        if (name.ToLower() == "id")
                        {
                            continue;
                        }

                        DbType DBtype = DbType.String;
                        Type type = pi.PropertyType;
                        object value = pi.GetValue(testClass, null);//用GetValue获得值

                        if (type == typeof(DateTime))
                        {
                            DBtype = DbType.DateTime;
                        }
                        else if (type == typeof(bool))
                        {
                            DBtype = DbType.Boolean;
                            value = (((bool)value == true) ? 1 : 0);
                        }
                        else if (type == typeof(int))
                        {
                            DBtype = DbType.Int32;
                        }

                        if (value == null)
                        {
                            value = DBNull.Value;
                        }
                        cmd.Parameters.Add("@" + name, DBtype);//添加参数
                        cmd.Parameters["@" + name].Value = value;//为参数赋值
                    }
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch(Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 修改整条数据
        /// </summary>
        /// <param name="testClass">类的实例</param>
        /// <param name="tableName">表名</param>
        /// <param name="mainKeyName">主键名</param>
        /// <param name="keyValue">主键值</param>
        /// <returns></returns>
        public bool ModifyData1<T>(T testClass, string tableName, string mainKeyName, string keyValue)
        {
            string colStr = "";
            string valueStr = "";

            Type t = testClass.GetType();//获得该类的Type
            foreach (PropertyInfo pi in t.GetProperties())
            {
                string name = pi.Name;
                if (name.ToLower() == "id")
                {
                    continue;
                }
                if (name == mainKeyName) //如果索引的键是字符串类型
                {
                    Type type = pi.PropertyType;
                    if (type == typeof(string))
                    {
                        keyValue = "'" + keyValue + "'";//如果索引的键是字符串类型 加上引号
                    }
                }
                colStr += pi.Name + "=@" + pi.Name + ",";
            }
            colStr = colStr.Substring(0, colStr.LastIndexOf(',')); //删掉最后一个逗号
            string cmdStr = string.Format("update {0} set {1} where {2}" + " = " + "{3};", tableName, colStr, mainKeyName, keyValue);
          
            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand(cmdStr, SQL_Conn))
                {
                    foreach (PropertyInfo pi in t.GetProperties())
                    {
                        string name = pi.Name;
                        if (name.ToLower() == "id")
                        {
                            continue;
                        }

                        DbType DBtype = DbType.String;
                        Type type = pi.PropertyType;
                        object value = pi.GetValue(testClass, null);//用GetValue获得值

                        if (type == typeof(DateTime))
                        {
                            DBtype = DbType.DateTime;
                        }
                        else if (type == typeof(bool))
                        {
                            DBtype = DbType.Boolean;
                            value = (((bool)value == true) ? 1 : 0);
                        }
                        else if (type == typeof(int))
                        {
                            DBtype = DbType.Int32;
                        }

                        if (value == null)
                        {
                            value = DBNull.Value;
                        }
                        cmd.Parameters.Add("@" + name, DBtype);  //添加参数
                        cmd.Parameters["@" + name].Value = value;//为参数赋值
                    }
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch(Exception ex)
            {
                return false;
            }
        }

 

 

如果插入数据需要返回自增ID：

        /// <summary>
        /// 指定列插入数据并获取自增长主键值
        /// </summary>
        public bool AddDatas<T>(T testClass, string tableName, out int id)
        {
            string colStr = "";
            string tmpValueStr = "";
            id = 0;
            Type t = testClass.GetType();//获得该类的Type
            foreach (PropertyInfo pi in t.GetProperties())
            {
                string name = pi.Name;
                if (name.ToLower() == "id")//主表主键不赋值
                {
                    continue;
                }
                colStr += pi.Name + ",";
                tmpValueStr += "@" + pi.Name + ",";
            }
            colStr = colStr.Substring(0, colStr.LastIndexOf(',')); //删掉最后一个逗号
            tmpValueStr = tmpValueStr.Substring(0, tmpValueStr.LastIndexOf(','));

            string cmdStr = string.Format("insert into {0}({1}) values({2});" + "select last_insert_rowid() from {0};", tableName, colStr, tmpValueStr);

            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand(cmdStr, SQL_Conn))
                {
                    foreach (PropertyInfo pi in t.GetProperties())
                    {
                        string name = pi.Name;
                        if (name.ToLower() == "id")
                        {
                            continue;
                        }

                        DbType DBtype = DbType.String;
                        Type type = pi.PropertyType;
                        object value = pi.GetValue(testClass, null);//用GetValue获得值

                        if (type == typeof(DateTime))
                        {
                            DBtype = DbType.DateTime;
                        }
                        else if (type == typeof(bool))
                        {
                            DBtype = DbType.Boolean;
                            value = (((bool)value == true) ? 1 : 0);
                        }
                        else if (type == typeof(int))
                        {
                            DBtype = DbType.Int32;
                        }

                        if (value == null)
                        {
                            value = DBNull.Value;
                        }
                        cmd.Parameters.Add("@" + name, DBtype);  //添加参数
                        cmd.Parameters["@" + name].Value = value;//为参数赋值
                    }
                    DbDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        id = int.Parse(reader[0].ToString());
                        reader.Close();
                        return true;
                    }
                    else
                    {
                        id = 0;
                        return false;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

 

二、高效批量插入

using  (DbConnection conn = DbProviderFactories.GetFactory( "System.Data.SQLite" ).CreateConnection())
{
     conn.ConnectionString = "Data Source = "  + m_datasourceFile + ";Version = 3" ;
     
     using  (SQLiteCommand insertRngCmd = (SQLiteCommand)conn.CreateCommand())
     {
         insertRngCmd.CommandText = @"INSERT INTO shuffled_tickets (ticket_idx, seed, win_credits, [timestamp], redeemed, prog_levels)
                                         VALUES  (@ticket_idx, @seed, @win_credits, @timestamp, @redeemed, @prog_levels)" ;
         conn.Open();
         var  transaction = conn.BeginTransaction();   
 
         foreach  ( var  ticket in  tickets)
         {
             insertRngCmd.Parameters.AddWithValue( "@ticket_idx" , ticket.ticket_idx);
             insertRngCmd.Parameters.AddWithValue( "@seed" , ticket.seed);
             insertRngCmd.Parameters.AddWithValue( "@win_credits" , ticket.win_credits);
             insertRngCmd.Parameters.AddWithValue( "@timestamp" , ticket.timestamp);
             insertRngCmd.Parameters.AddWithValue( "@redeemed" , ticket.redeemed);
             insertRngCmd.Parameters.AddWithValue( "@prog_levels" , ticket.prog_levels);
 
             insertRngCmd.ExecuteNonQuery();
         }
         transaction.Commit();
     }

出处：https://blog.csdn.net/wowotuo/article/details/41941001

 

三、自增ID

1、获取自增ID

 https://www.cnblogs.com/keitsi/p/5558985.html

 

2、自增ID清零

如果想把某个表自增列的序号归零，只需要修改 sqlite_sequence表就可以了。
delete from TableName;  //清空数据
update sqlite_sequence SET seq =0 where name ='TableName';//自增长ID为0

在Sqlite Developer界面菜单调出编辑器执行SQL语句：

工具>>SQL编辑器

 

输入：

 

 点击最左边的查询按钮执行指令。

 

3、删除数据之后db文件大小没变化

造成这种情况的原因是：sqlite在之前使用的时候，随着数据的插入，会分配磁盘空间来供使用，在删除数据以后，未使用的磁盘空间会被添加到一个内在的“空闲列表”中用于存储下次插入的数据，磁盘空间不会随着数据的删除而释放给操作系统。

需要进行sqlite文件磁盘空间的释放，可以在命令界面，执行一下命令：VACUUM

 http://www.chenxm.cc/article/1167.html

 

四、常见错误

1、NOT NULL constraint failed: .id

解决方法：
（1）设置主键
（2）主键自增

https://www.jianshu.com/p/3ce7dc4ecd48

 

2、无法加载 DLL“SQLite.Interop.dll”，找不到指定的模块

下载：https://www.mzwu.com/article.asp?id=3180
①sqlite-netFx35-binary-bundle-Win32-2008-1.0.79.0.zip
②sqlite-netFx35-binary-Win32-2008-1.0.79.0.zip

带bundle的表示动态库是按混合模式编译的，在使用的时候只需要System.Data.SQLite.dll就可以了，而不带bundle的则是将非托管部分和托管部分分别编译，System.Data.SQLite.dll不能独立使用，还需要有SQLite.Interop.dll才能使用。

我用的是不带bundle包中的System.Data.SQLite.dll，解决方法是要么手工复制SQLite.Interop.dll和exe放一块(不能用引用)，要么换成引用bundle包中的System.Data.SQLite.dll。