asp.net SQLite数据库操作类,

配置<add key="SQLString" value="~\demo.db"/>

/**************************************
* 作用: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, null)) || (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, 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();
                    
object obj = cmd.ExecuteScalar();
                    
if ((Object.Equals(obj, null)) || (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, null)) || (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, connection);
                    command.Fill(ds, 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, params SQLiteParameter[] cmdParms)
        {
            
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                
using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    
try
                    {
                        PrepareCommand(cmd, connection, 
null, SQLString, cmdParms);
                        
object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        
if ((Object.Equals(obj, null)) || (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, connection, 
null, SQLString, 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, connection, 
null, SQLString, 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, DbType type, object value)
        {
            SQLiteParameter parm 
= new SQLiteParameter(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", DbType.String,30,name),
                    SQLiteHelper.MakeSQLiteParameter(
"@Password", DbType.String,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", DbType.String,30,name),
                    SQLiteHelper.MakeSQLiteParameter(
"@Password", DbType.String,128,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", DbType.Int32,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

 

 
posted @ 2009-04-06 12:33  Neo.Yan  阅读(7060)  评论(4编辑  收藏  举报