[开发笔记]-sqlite数据库在使用时遇到的奇葩问题记录

有时候做些简单的项目一般都会选择sqlite数据库，优点有很多，这里就不详细说了。

在此主要记录一些平时在使用时遇到的问题及解决方法。希望能对大家有所帮助。

-------------------------------------------------------------------------

一:sqlite一直提示 the database file is locked

解决：

-----------------------------------------------------------------------------

二：错误提示-混合模式程序集是针对v2.0........

解决：

------------------------------------------------------------------

三：在IIS中发布网站程序时，如果操作系统是64位的，有时候会出现访问错误的问题，这时可以将应用程序高级设置中的“启用32位应用程序”设置项改为True，再测试是否成功。(如果“启用32位应用程序项为True” 时网站提示下面的错误黄页，则可以将该项设置为False试试看。2014-5-24 更新：今天在测试一个网站时，发现在True的情况下网站报错，则将该项切换为了False，则可以访问了。至于如何来解释这种问题，待研究。)

这种情况的一种错误现象是：

在IIS中发布使用sqlite数据库的网站项目时，配置好IIS后访问，页面可能就是显示下面的：

这时就需要你去修改应用程序池的模式了：

---------------------------------------------------------------------------

 四：使用sql语句插入当前时间

在sqlserver中，如果在操作数据时需要插入当前时间的情况，可以使用 GETDATE() 来插入，而在sqlite中则不同：

INSERT INTO MWaitPlayList(Msongid,Mtitle,Mauthor,Mtime) VALUES(@songid,@title,@author,datetime('now', 'localtime'))

要用 datetime('now','localtime') 来插入。

项目实例：

       #region 将歌曲添加到待播放列表中
        /// <summary>
        /// 将歌曲添加到待播放列表中
        /// </summary>
        /// <param name="songid">歌曲id</param>
        /// <param name="songtitle">歌曲名</param>
        /// <param name="songauthor">歌手</param>
        /// <returns></returns>
        public static bool XMusicAddtoWaitList(long songid, string songtitle, string songauthor)
        {
            string sql = "INSERT INTO MWaitPlayList(Msongid,Mtitle,Mauthor,Mtime) VALUES(@songid,@title,@author,datetime('now','localtime'))";

            SQLiteParameter[] parameter ={
                                         new SQLiteParameter("@songid",DbType.Int64),
                                         new SQLiteParameter("@title",DbType.String,200),
                                         new SQLiteParameter("@author",DbType.String,200)
                                         };

            parameter[0].Value = songid;
            parameter[1].Value = songtitle;
            parameter[2].Value = songauthor;

            int row = ZXSQLiteHelper.ExecuteSql(sql, parameter);
            if (row > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        } 

-------------------------------------------------------------------------

 五. 数据库配置

Web.Config或App.Config文件中的设置：

 <appSettings>
    <add key="SQLiteConn" value="Data Source=|DataDirectory|Music.db;Version=3;Pooling=False;Max Pool Size=100;"/>
  </appSettings>

数据库文件要放在App_Data文件夹中：

-------------------------------------------------------------------------

 六. SQLite中获取最新添加自增ID,last_insert_rowid()的使用

今天在用sqlite数据库时，想要在新插入数据的同时获取自增的id值，从网上找了找，发现可以用last_insert_rowid() 这个函数来获取，但是在sql语句中执行时却一直返回0。于是又在网上找到了一个相关的文章，经测试可行。遂记录一下。

　　出现上面所提问题的主要原因是“last_insert_rowid()” 函数必须要和insert语句一起使用，说的再明白点，就是必须是由同一个“SQLiteConnection” 来操作。

修改后的sqliteHelper :

        /// <summary>
        /// 执行插入语句，并获取最新的一条数据的id
        /// </summary>
        /// <param name="SQLString"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static long ExecuteGetInsertId(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    long result = 0;
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        //result = cmd.ExecuteNonQuery();
                        //查询 select用executeScalar() ,如果用executeNonQuery返回的永远是1. 这里由于肯定能转换为数字 ，所以可以直接转换
                        result = Convert.ToInt64(cmd.ExecuteScalar());
                        cmd.Parameters.Clear();

                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        result = -1;
                        throw new Exception(E.Message);
                    }
                    return result;
                }
            }
        }

sql语句：

 string sql = "INSERT INTO UMembers(QId,UName,UPwd,UEmail,UPhoto,UTime) VALUES (5,@uname,@upwd,@uemail,@uphoto,datetime('now','localtime'));SELECT last_insert_rowid() from UMembers";

                          -----------------------------------

参考：

今天在我的数据类中给Add方法完善一下.想要实现添加之后返回添加的实体的自增ID,遂想起了select last_insert_rowid(),可是用了之后就是不好使,各种返回0,

后来经度娘指教,我发现一句话"在同一个SQLiteConnection中...",原来如此.修改代码,搞定!

我之前是这么写的

DBHelperSQLite.ExecuteSql(sql,parameters);
return Convert.ToInt32(DBHelperSQLite.GetSingle("select last_insert_rowid()"));

注意:由于我的DBHelper写法的原因,这样的话就变成了两个SQLiteConnection

改进后

return DBHelperSQLite.ExecuteSql(sql+ ";select last_insert_rowid();", parameters);

DBHelper修改

public static int ExecuteSql(string SQLString, List<SQLiteParameter> para)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
            {
                try
                {
                    connection.Open();
                    foreach (SQLiteParameter p in para)
                    {
                        cmd.Parameters.Add(p);
                    }
                    int rows =0;
                    if(SQLString.IndexOf("insert") != -1)
                        rows = Convert.ToInt32(cmd.ExecuteScalar());
                    else
                        rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (SQLiteException e)
                {
                    connection.Close();
                    throw e;
                }
            }
        }
    }

注意15行到18行,判断是否是insert操作.然后执行cmd.ExecuteScalar(),而不是cmd.ExecuteNonQuery().这样就实现在同一个SQLiteConnection啦!

链接： SQLite中获取最新添加自增ID,last_insert_rowid()的使用 - 饭 - 博客园

-------------------------------------------------------------------------

 七. sqlite查询特定时间段的数据

　　1.查询某一天的数据 

SELECT Utitle,Uurl,Utime FROM Urls WHERE Uisok=1 AND date(Utime)=date('2014-02-10')

　　2.查询今天的数据  （待测试）

select  time>=datetime('now','start of day','+0 day') and time<datetime('now','start of day','+1 day') from 表

　　3.查询昨天的数据  （待测试）

select  time>=datetime('now','start of day','-1 day') and time<datetime('now','start of day','+0 day') from 表

　　4.查询本周的数据   （待测试） 

select  time>=datetime('now','start of day','-7 day','weekday 1') AND time<datetime('now','start of day','+0 day','weekday 1') from 表

　　　　（时间取的是 周一到周日为一周）

　　5.查询本月的数据 

select * FROM UMembers WHERE UTime >=datetime('now', 'start of month', '+0 month', '-0 day') and UTime<datetime('now', 'start of month', '+1 month', '0 day')

　　6.查询上一月的数据 （待测试）

select Time>=datetime('now','start of month','-1 month','-0 day') AND Time <datetime('now','start of month','+0 month','-1 day')  from 表

-------------------------------------------------------------------------

八.Sqlite分页数据查询

sqlite中 limit 一般的语法格式为:

 Select * From Person  Limit 9 Offset 10;

表示从 数据库Person 中 第10条开始 共获取9条数据

也可以使用 简写形式：

 Select * From Person  Limit 10,9;

--查询相应条数数据 相当于sql中的 top  
--0,2   从第几条开始   共查询多少条 
SELECT Bid,BTitle,BContent,BLink,BImg,BDate FROM XBooks WHERE BDel=0 ORDER BY BDate DESC LIMIT 0,2

 分页查询

 string sql2 = string.Format("SELECT Bid,BTitle,BContent,BLink,BImg,BDate FROM XBooks WHERE BDel=0 ORDER BY BDate DESC LIMIT {0}*{1},{1}", pageIndex - 1, pageSize);

 string sql = string.Format("SELECT Bid,BTitle,BContent,BLink,BImg,BDate FROM XBooks WHERE BDel=0 ORDER BY BDate DESC LIMIT {0} offset {0}*{1}", pageSize, pageIndex - 1);//pageSize:每页显示条数，pageIndex页码

------------------------------------------------------------------------

-------------------------------------------------------------------------

未完待续。。。

 

转载请注明出处。