广告合作QQ：76874919

SQLite数据库操作类

Sqlite 2020-06-23

转自：http://www.csharpwin.com/csharpresource/196.shtml


/**************************************
* 作用：sqlLite Server操作实现
* 作者：Nick.Yan
* 日期: 2009-03-29
* 网址：www.redglove.net
**************************************/

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.sqlite;//这个可以去网上下载
using System.Configuration;

public class sqliteHelper
{
    //数据库连接字符串(web.config来配置)，可以动态更改sqlString支持多数据库.        
    public static string connectionString = "Data Source=" + 
               System.Web.HttpContext.Current.Server.MapPath(
                    ConfigurationManager.AppSettings["sqlString"]);

    public sqliteHelper() { }

    #region 公用方法

    public static int GetMaxID(string FieldName,string TableName)
    {
        string strsql = "select max(" + FieldName + ")+1 from " + TableName;
        object obj = GetSingle(strsql);
        if (obj == null)
        {
            return 1;
        }
        else
        {
            return int.Parse(obj.ToString());
        }
    }

    public static bool Exists(string strsql)
    {
        object obj = GetSingle(strsql);
        int cmdresult;
        if ((Object.Equals(obj,null)) || (Object.Equals(obj,System.DBNull.Value)))
        {
            cmdresult = 0;
        }
        else
        {
            cmdresult = int.Parse(obj.ToString());
        }
        if (cmdresult == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    public static bool Exists(string strsql,params sqliteParameter[] cmdParms)
    {
        object obj = GetSingle(strsql,cmdParms);
        int cmdresult;
        if ((Object.Equals(obj,System.DBNull.Value)))
        {
            cmdresult = 0;
        }
        else
        {
            cmdresult = int.Parse(obj.ToString());
        }
        if (cmdresult == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    #endregion

    #region  执行简单sql语句

    /// <summary>
    /// 执行sql语句，返回影响的记录数
    /// </summary>
    /// <param name="sqlString">sql语句</param>
    /// <returns>影响的记录数</returns>
    public static int Executesql(string sqlString)
    {
        using (sqliteConnection connection = new sqliteConnection(connectionString))
        {
            using (sqliteCommand cmd = new sqliteCommand(sqlString,connection))
            {
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.sqlite.sqliteException E)
                {
                    connection.Close();
                    throw new Exception(E.Message);
                }
            }
        }
    }

    /// <summary>
    /// 执行sql语句，设置命令的执行等待时间
    /// </summary>
    /// <param name="sqlString"></param>
    /// <param name="Times"></param>
    /// <returns></returns>
    public static int ExecutesqlByTime(string sqlString,int Times)
    {
        using (sqliteConnection connection = new sqliteConnection(connectionString))
        {
            using (sqliteCommand cmd = new sqliteCommand(sqlString,connection))
            {
                try
                {
                    connection.Open();
                    cmd.CommandTimeout = Times;
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.sqlite.sqliteException E)
                {
                    connection.Close();
                    throw new Exception(E.Message);
                }
            }
        }
    }

    /// <summary>
    /// 执行多条sql语句，实现数据库事务。
    /// </summary>
    /// <param name="sqlStringList">多条sql语句</param>        
    public static void ExecutesqlTran(ArrayList sqlStringList)
    {
        using (sqliteConnection conn = new sqliteConnection(connectionString))
        {
            conn.Open();
            sqliteCommand cmd = new sqliteCommand();
            cmd.Connection = conn;
            sqliteTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int n = 0; n < sqlStringList.Count; n++)
                {
                    string strsql = sqlStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
            }
            catch (System.Data.sqlite.sqliteException E)
            {
                tx.Rollback();
                throw new Exception(E.Message);
            }
        }
    }

    /// <summary>
    /// 执行带一个存储过程参数的的sql语句。
    /// </summary>
    /// <param name="sqlString">sql语句</param>
    /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
    /// <returns>影响的记录数</returns>
    public static int Executesql(string sqlString,string content)
    {
        using (sqliteConnection connection = new sqliteConnection(connectionString))
        {
            sqliteCommand cmd = new sqliteCommand(sqlString,connection);
            sqliteParameter myParameter = new sqliteParameter("@content",DbType.String);
            myParameter.Value = content;
            cmd.Parameters.Add(myParameter);
            try
            {
                connection.Open();
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
            catch (System.Data.sqlite.sqliteException E)
            {
                throw new Exception(E.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
    }

    /// <summary>
    /// 执行带一个存储过程参数的的sql语句。
    /// </summary>
    /// <param name="sqlString">sql语句</param>
    /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
    /// <returns>影响的记录数</returns>
    public static object ExecutesqlGet(string sqlString,DbType.String);
            myParameter.Value = content;
            cmd.Parameters.Add(myParameter);
            try
            {
                connection.Open();
                object obj = cmd.ExecuteScalar();
                if ((Object.Equals(obj,System.DBNull.Value)))
                {
                    return null;
                }
                else
                {
                    return obj;
                }
            }
            catch (System.Data.sqlite.sqliteException E)
            {
                throw new Exception(E.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
    }

    /// <summary>
    /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
    /// </summary>
    /// <param name="strsql">sql语句</param>
    /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
    /// <returns>影响的记录数</returns>
    public static int ExecutesqlInsertImg(string strsql,byte[] fs)
    {
        using (sqliteConnection connection = new sqliteConnection(connectionString))
        {
            sqliteCommand cmd = new sqliteCommand(strsql,connection);
            sqliteParameter myParameter = new sqliteParameter("@fs",DbType.Binary);
            myParameter.Value = fs;
            cmd.Parameters.Add(myParameter);
            try
            {
                connection.Open();
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
            catch (System.Data.sqlite.sqliteException E)
            {
                throw new Exception(E.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
    }

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="sqlString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string sqlString)
    {
        using (sqliteConnection connection = new sqliteConnection(connectionString))
        {
            using (sqliteCommand cmd = new sqliteCommand(sqlString,connection))
            {
                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj,System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.sqlite.sqliteException e)
                {
                    connection.Close();
                    throw new Exception(e.Message);
                }
            }
        }
    }

    /// <summary>
    /// 执行查询语句，返回sqliteDataReader(使用该方法切记要手工关闭sqliteDataReader和连接)
    /// </summary>
    /// <param name="strsql">查询语句</param>
    /// <returns>sqliteDataReader</returns>
    public static sqliteDataReader ExecuteReader(string strsql)
    {
        sqliteConnection connection = new sqliteConnection(connectionString);
        sqliteCommand cmd = new sqliteCommand(strsql,connection);
        try
        {
            connection.Open();
            sqliteDataReader myReader = cmd.ExecuteReader();
            return myReader;
        }
        catch (System.Data.sqlite.sqliteException e)
        {
            throw new Exception(e.Message);
        }
        //finally //不能在此关闭，否则，返回的对象将无法使用
        //{
        //    cmd.Dispose();
        //    connection.Close();
        //}    
    }

    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="sqlString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet Query(string sqlString)
    {
        using (sqliteConnection connection = new sqliteConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                sqliteDataAdapter command = new sqliteDataAdapter(sqlString,connection);
                command.Fill(ds,"ds");
            }
            catch (System.Data.sqlite.sqliteException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }

    public static DataSet Query(string sqlString,string TableName)
    {
        using (sqliteConnection connection = new sqliteConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                sqliteDataAdapter command = new sqliteDataAdapter(sqlString,TableName);
            }
            catch (System.Data.sqlite.sqliteException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }

    /// <summary>
    /// 执行查询语句，返回DataSet,设置命令的执行等待时间
    /// </summary>
    /// <param name="sqlString"></param>
    /// <param name="Times"></param>
    /// <returns></returns>
    public static DataSet Query(string sqlString,int Times)
    {
        using (sqliteConnection connection = new sqliteConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                sqliteDataAdapter command = new sqliteDataAdapter(sqlString,connection);
                command.SelectCommand.CommandTimeout = Times;
                command.Fill(ds,"ds");
            }
            catch (System.Data.sqlite.sqliteException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }

    #endregion

    #region 执行带参数的sql语句

    /// <summary>
    /// 执行sql语句，返回影响的记录数
    /// </summary>
    /// <param name="sqlString">sql语句</param>
    /// <returns>影响的记录数</returns>
    public static int Executesql(string sqlString,params sqliteParameter[] cmdParms)
    {
        using (sqliteConnection connection = new sqliteConnection(connectionString))
        {
            using (sqliteCommand cmd = new sqliteCommand())
            {
                try
                {
                    PrepareCommand(cmd,connection,null,sqlString,cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (System.Data.sqlite.sqliteException E)
                {
                    throw new Exception(E.Message);
                }
            }
        }
    }

    /// <summary>
    /// 执行多条sql语句，实现数据库事务。
    /// </summary>
    /// <param name="sqlStringList">sql语句的哈希表（key为sql语句，value是该语句的sqliteParameter[]）</param>
    public static void ExecutesqlTran(Hashtable sqlStringList)
    {
        using (sqliteConnection conn = new sqliteConnection(connectionString))
        {
            conn.Open();
            using (sqliteTransaction trans = conn.BeginTransaction())
            {
                sqliteCommand cmd = new sqliteCommand();
                try
                {
                    //循环
                    foreach (DictionaryEntry myDE in sqlStringList)
                    {
                        string cmdText = myDE.Key.ToString();
                        sqliteParameter[] cmdParms = (sqliteParameter[]) myDE.Value;
                        PrepareCommand(cmd,conn,trans,cmdText,cmdParms);
                        int val = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();

                        trans.Commit();
                    }
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }
        }
    }

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="sqlString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string sqlString,cmdParms);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj,System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.sqlite.sqliteException e)
                {
                    throw new Exception(e.Message);
                }
            }
        }
    }

    /// <summary>
    /// 执行查询语句，返回sqliteDataReader (使用该方法切记要手工关闭sqliteDataReader和连接)
    /// </summary>
    /// <param name="strsql">查询语句</param>
    /// <returns>sqliteDataReader</returns>
    public static sqliteDataReader ExecuteReader(string sqlString,params sqliteParameter[] cmdParms)
    {
        sqliteConnection connection = new sqliteConnection(connectionString);
        sqliteCommand cmd = new sqliteCommand();
        try
        {
            PrepareCommand(cmd,cmdParms);
            sqliteDataReader myReader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            return myReader;
        }
        catch (System.Data.sqlite.sqliteException e)
        {
            throw new Exception(e.Message);
        }
        //finally //不能在此关闭，否则，返回的对象将无法使用
        //{
        //    cmd.Dispose();
        //    connection.Close();
        //}   

    }

    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="sqlString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet Query(string sqlString,params sqliteParameter[] cmdParms)
    {
        using (sqliteConnection connection = new sqliteConnection(connectionString))
        {
            sqliteCommand cmd = new sqliteCommand();
            PrepareCommand(cmd,cmdParms);
            using (sqliteDataAdapter da = new sqliteDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds,"ds");
                    cmd.Parameters.Clear();
                }
                catch (System.Data.sqlite.sqliteException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
    }

    public static void PrepareCommand(sqliteCommand cmd,sqliteConnection conn,sqliteTransaction trans,string cmdText,sqliteParameter[] cmdParms)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        if (trans != null)
            cmd.Transaction = trans;
        cmd.CommandType = CommandType.Text;//cmdType;
        if (cmdParms != null)
        {


            foreach (sqliteParameter parameter in cmdParms)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput 
                        || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                cmd.Parameters.Add(parameter);
            }
        }
    }

    #endregion

    #region 参数转换
    /// <summary>
    /// 放回一个sqliteParameter
    /// </summary>
    /// <param name="name">参数名字</param>
    /// <param name="type">参数类型</param>
    /// <param name="size">参数大小</param>
    /// <param name="value">参数值</param>
    /// <returns>sqliteParameter的值</returns>
    public static sqliteParameter MakesqliteParameter(string name,DbType type,int size,object value)
    {
        sqliteParameter parm = new sqliteParameter(name,type,size);
        parm.Value = value;
        return parm;
    }

    public static sqliteParameter MakesqliteParameter(string name,type);
        parm.Value = value;
        return parm;
    }

    #endregion
}


调用方法

复制  保存
/// <summary>
/// 判断用户是否存在
/// </summary>
/// <param name="name">用户名称</param>
/// <returns></returns>
public bool UserExists(string name)
{
    StringBuilder strsql = new StringBuilder();
    strsql.Append("select count(*) n from Users");
    strsql.Append(" where UserName=@UserName ");
    sqliteParameter[] parameters = {
                sqliteHelper.MakesqliteParameter("@UserName",DbType.String,30,name)};

    return sqliteHelper.Exists(strsql.ToString(),parameters);
}

/// <summary>
/// 增加一个用户
/// </summary>
/// <param name="name">用户名</param>
/// <param name="pwd">密码</param>
/// <returns></returns>
public int CreateUser(string name,string pwd)
{
    int ret = 0;
    if (!UserExists(name))
    {
        StringBuilder strsql = new StringBuilder();
        strsql.Append("insert into t_UserGroup(");
        strsql.Append("UserName,Password)");
        strsql.Append(" values (");
        strsql.Append("@UserName,@Password)");
        sqliteParameter[] parameters = {
                sqliteHelper.MakesqliteParameter("@UserName",name),sqliteHelper.MakesqliteParameter("@Password",128,pwd)
                };

        if (sqliteHelper.Executesql(strsql.ToString(),parameters) >= 1)
        {
            ret = 1;
        }
    }
    else
    {
        ret = 2;
    }
    return ret;
}

/// <summary>
/// 更新一条数据
/// </summary>
/// <param name="model">用户分组实体类</param>
/// <returns></returns>
public bool UpdateUser(int id,string name,string pwd)
{
    StringBuilder strsql = new StringBuilder();
    strsql.Append("update Users set ");
    strsql.Append("UserName=@UserName,");
    strsql.Append("Password=@Password");
    strsql.Append(" where UserID=@UserID");
    sqliteParameter[] parameters = {
                sqliteHelper.MakesqliteParameter("@UserID",DbType.Int32,11,id),sqliteHelper.MakesqliteParameter("@UserName",pwd)};

    if (sqliteHelper.Executesql(strsql.ToString(),parameters) >= 1)
    {
        return true;
    }
    else
    {
        return false;
    }
}

/// <summary>
/// 删除用户
/// </summary>
/// <param name="ID">用户ID</param>
/// <returns></returns>
public int DeleteUser(int id)
{
    int ret = 0;
    string strsql3 = "delete from Users where UserID=@UserID";
    sqliteParameter[] parameters = {
                sqliteHelper.MakesqliteParameter("@UserID",4,id)};

    if (sqliteHelper.Executesql(strsql3,parameters) >= 1)
    {
        ret = 1;
    }
    else
    {
        ret = 0;
    }

    return ret;
}

/// <summary>
/// 获得用户分组数据列表
/// </summary>
/// <param name="strWhere">Where条件</param>
/// <returns></returns>
public DataSet GetUserList(string strWhere)
{
    StringBuilder strsql = new StringBuilder();
    strsql.Append("select * FROM Users ");

    if (strWhere.Trim() != "")
    {
        strsql.Append(" where " + strWhere);
    }
    strsql.Append(" order by UserID desc");

    return sqliteHelper.Query(strsql.ToString());
}


注意事项

1. @@IDENTITY LAST_INSERT_ROWID() 2. SELECT cn = COUNT(*) FROM ... SELECT COUNT(*) cn FROM ... 3. LIMIT startIndex,itemCn 这儿的startIndex是从0开始的，而ROW_NUMBER()是从1开始的 4. sqlite中没有SELECT TOP,用LIMIT即可 5. sqlite自增字段，如果在事务中插入数据失败，并不会占用增长后的id，而sql server中对应的id将无效 6. sqlite中没有GETDATE日期函数，在类中加入参数如下DbType.DateTime,DateTime.Now.ToString("s") 7. sqlite支持REPLACE INTO语法,sql server 2008中支持merge to

相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压缩包。 创建s...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Windows下的包下...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 6.查找会员  ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、延迟时间和整...