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

 

 

转载于:https://www.cnblogs.com/nick4/archive/2009/04/06/1430208.html