亲宝软件园·资讯

展开

C# SQLite库使用

程序猿evint 人气:1

1、SQLite介绍

SQLite,是一款轻型的数据库,是遵守的ACID关系型数据库管理系统,它包含在一个相对小的C库中。它的设计目标嵌入式是的,而且已经在很多中使用了它,它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如 Tcl、C#、PHP、Java等。

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

SQLite是一个开源、免费的小型RDBMS(关系型数据库),能独立运行、无服务器、零配置、支持事物,用C实现,内存占用较小,支持绝大数的SQL92标准。

SQLite数据库官方主页:http://www.sqlite.org/index.html

2、C#下调用SQLite数据库

在NuGet程序包内,搜索System.Data.Sqlite,安装Sqlite类库

3、在C#程序内添加SqliteHelper

sqliteHelper中主要用到2个方法:

  a、ExecuteNonQuery 执行Insert,Update、Delete、创建库等操作,返回值是数据库影响的行数

  b、ExecuteDataSet执行Select操作,返回查询数据集

    public class SQLiteHelper
    {
        public static string ConnectionString = "Data Source =" + Environment.CurrentDirectory + @"\database.db" + ";Pooling = true; FailIfMissing = true";
 
        /// <summary>
        /// 执行数据库操作(新增、更新或删除)
        /// </summary>
        /// <param name="cmdstr">连接字符串</param>
        /// <param name="cmdParms">SqlCommand对象</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string cmdstr, params SQLiteParameter[] cmdParms)
        {
            int result = 0;
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                SQLiteTransaction trans = null;
                SQLiteCommand cmd = new SQLiteCommand(cmdstr);
                PrepareCommand(cmd, conn, ref trans, true, cmd.CommandType, cmd.CommandText, cmdParms);
                try
                {
                    result = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            return result;
        }
 
        /// <summary>
        /// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <param name="conn">Connection对象</param>
        /// <param name="trans">Transcation对象</param>
        /// <param name="useTrans">是否使用事务</param>
        /// <param name="cmdType">SQL字符串执行类型</param>
        /// <param name="cmdText">SQL Text</param>
        /// <param name="cmdParms">SQLiteParameters to use in the command</param>
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, ref SQLiteTransaction trans, bool useTrans, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
 
                cmd.Connection = conn;
                cmd.CommandText = cmdText;
 
                if (useTrans)
                {
                    trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                    cmd.Transaction = trans;
                }
 
                cmd.CommandType = cmdType;
 
                if (cmdParms != null)
                {
                    foreach (SQLiteParameter parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
            }
            catch
            {
 
            }
        }
 
        /// <summary>
        /// 数据库查询
        /// </summary>
        /// <param name="cmdstr">sql语句</param>
        /// <param name="tableName">表名</param>
        /// <returns>DataSet对象</returns>
        public DataSet ExecuteDataSet(string cmdstr)
        {
            DataSet ds = new DataSet();
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);
            SQLiteTransaction trans = null;
            SQLiteCommand cmd = new SQLiteCommand(cmdstr);
            PrepareCommand(cmd, conn, ref trans, false, cmd.CommandType, cmd.CommandText);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd.Connection != null)
                {
                    if (cmd.Connection.State == ConnectionState.Open)
                    {
                        cmd.Connection.Close();
                    }
                }
            }
            return ds;
        }

4、Sqlite部分技巧 

  a、SQLiteConnection类的CreateFile方法,在程序内动态创建数据库文件,通过下面的方法即可创建出Analysis.db名称的数据库

        /// <summary>
        /// 数据库路径
        /// </summary>
        private static string databasepath = AppDomain.CurrentDomain.BaseDirectory + "DataBase\\";
        /// <summary>
        /// 数据库名称
        /// </summary>
        private const string databasename = "Analysis.db";
        /// <summary>
        /// 创建数据库
        /// </summary>
        public static void CreateDataBase()
        {
            try
            {
                if (!File.Exists(databasepath + databasename))
                {
                    if (!Directory.Exists(databasepath))
                        Directory.CreateDirectory(databasepath);
                    SQLiteConnection.CreateFile(databasepath + databasename);
                    LogHelper.Info("创建数据库:" + databasename + "成功!");
                }
            }
            catch (Exception ex)
            {
                LogHelper.Debug(ex);
            }
        }

  b、在写入高频数据的时候,需要使用事务,如果反复进行(打开->插入>关闭)操作,sqlite效率1秒钟插入也就2条,使用程序进行插入就会发现输入的频率远低于获取到的数据,大量的数据被缓存到内存中,为了处理入库的速度慢,就要用到事务,事务流程:

    ①打开连接

    ②开始事务

    ③循环在内存中执行插入命令

    ④提交事务写入本地文件,如果出错回滚事务

    ⑤关闭连接

代码见下图,开始事务后通过SQLiteCommand的ExecuteNonQuery()方法进行内存提交

            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    DbTransaction trans = null;
                    try
                    {
                        cmd.Connection = conn;
                        conn.Open();
                        //开启事务
                        using (trans = conn.BeginTransaction())
                        {
                            while (_list.Count > 0)
                            {
                                GpsDataClass _gps = _list[0];
                                try
                                {
                                    if (_gps != null)
                                    {
                                        SQLiteHelper sh = new SQLiteHelper(cmd);
                                        var dic = new Dictionary<string, object>();
                                        dic["CarPlate"] = _gps.CarPlate;
                                        dic["CarIpAddress"] = _gps.CarIpAddress;
                                        dic["PosX1"] = _gps.PosX1;
                                        dic["PosY1"] = _gps.PosY1;
                                        dic["PosZ1"] = _gps.PosZ1;
                                        dic["Heading1"] = _gps.Heading1;
                                        dic["PosStatus1"] = _gps.PosStatus1;
                                        dic["NumF1"] = _gps.NumF1;
                                        dic["NumB1"] = _gps.NumB1;
                                        dic["PosX2"] = _gps.PosX2;
                                        dic["PosY2"] = _gps.PosY2;
                                        dic["PosZ2"] = _gps.PosZ2;
                                        dic["Heading2"] = _gps.Heading2;
                                        dic["PosStatus2"] = _gps.PosStatus2;
                                        dic["NumF2"] = _gps.NumF2;
                                        dic["NumB2"] = _gps.NumB2;
                                        dic["Speed"] = _gps.Speed;
                                        dic["Signal"] = _gps.Signal;
                                        dic["NowTime"] = _gps.NowTime;
                                        sh.Insert("GpsRecord", dic);
                                        _list.RemoveAt(0);
                                    }
                                }
                                catch (Exception ex)
                                {
                                    LogHelper.Debug(ex);
                                }
                            }
                            trans.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        LogHelper.Debug(ex);
                    }
                    conn.Close();
                }
            }

加载全部内容

相关教程
猜你喜欢
用户评论