asp.net 链接SQLite数据库通用类与web.config的配置

首先下载System.Data.SQLite（本文后面有下载），添加引用System.Data.SQLite到项目中。

①web.config中的配置：

<?xml version="1.0"?>
<!--
  有关如何配置 ASP.NET 应用程序的详细信息，请访问
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <appSettings>

    <add key="SQLiteString" value="~\App_Data\risfeng.db3"/>


  </appSettings>
  <system.web>
        <compilation debug="true" targetFramework="4.0">
            <assemblies>
                <add assembly="System.Data.SQLite, Version=1.0.66.0, Culture=neutral, PublicKeyToken=DB937BC2D44FF139"/></assemblies></compilation>
    </system.web>
</configuration>

②通用类代码：

//===============================================================================
// This file is based on the SQLite Application Block for .NET
// For more information please go to 
// http://www.cnblogs.com/baidu-com
//===============================================================================

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Data.SQLite;

    /// <summary>
    /// DataExecute 的摘要说明。
    /// </summary>
    public class DataExecute
    {
        //Database connection strings
        public static readonly string sqlite = "Data Source=" + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["SQLiteString"]);
   

     // Hashtable to store cached parameters
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
        /// <summary>
        /// Execute a SQLiteCommand (that returns no resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms) 
        {

            int val;
            SQLiteCommand cmd = new SQLiteCommand();
            
            using (SQLiteConnection conn = new SQLiteConnection(connString)) 
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

                try
                {
                    val = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    string szErrMsg = ex.Message;
                    val = 0;
                }
                finally
                {
                    //清除cmd的参数
                    cmd.Parameters.Clear();
                    conn.Close();
                }
                return val;
            }
        }

        /// <summary>
        /// Execute a SQLiteCommand (that returns no resultset) against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(SQLiteConnection conn, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms) 
        {
            int val;

            SQLiteCommand cmd = new SQLiteCommand();

            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            try
            {
                val = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string szErrMsg = ex.Message;
                val = 0;
            }
            finally
            {
                //清除cmd的参数
                cmd.Parameters.Clear();
                conn.Close();

            }
            return val;
        }

        /// <summary>
        /// Execute a SQLiteCommand (that returns no resultset) using an existing SQL Transaction 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing sql transaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(SQLiteTransaction trans, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms) 
        {
            int val;
       
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            try
            {
                val = cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                string szErrMsg = ex.Message;
                val = 0;
            }
            finally
            {
                //清除cmd的参数
                cmd.Parameters.Clear();
            }
            return val;
        }

        /// Execute a SQLiteCommand (that returns a resultset and takes no parameters) against the database specified in 
        /// the connection string. 
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SQLiteDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <returns>a SQLiteDataReader containing the resultset generated by the command</returns>
        public static SQLiteDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
        {
            //pass through the call providing null for the set of SQLiteParameters
            return ExecuteReader(connectionString, commandType, commandText, (SQLiteParameter[])null);
        }

        /// <summary>
        /// Execute a SQLiteCommand that returns a resultset against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SQLiteDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>A SQLiteDataReader containing the results</returns>
        public static SQLiteDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms) 
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteConnection conn = new SQLiteConnection(connString);

            try 
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch 
            {
                conn.Close();
                throw;
            }
        }
        

        /// <summary>
        /// Execute a SQLiteCommand (that returns a resultset and takes no parameters) against the database specified in 
        /// the connection string. 
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <returns>a dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connectionString, commandType, commandText, (SQLiteParameter[])null);
        }

        /// <summary>
        /// Execute a SQLiteCommand (that returns a resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>a dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
        {
            using (SQLiteConnection cn = new SQLiteConnection(connectionString))
            {
                cn.Open();

                //调用重载方法
                return ExecuteDataset(cn, commandType, commandText, commandParameters);
            }
        }



        /// <summary>
        /// Execute a SQLiteCommand (that returns a resultset and takes no parameters) against the provided SQLiteConnection. 
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connection">a valid SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <returns>a dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataset(SQLiteConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connection, commandType, commandText, (SQLiteParameter[])null);
        }
        
        /// <summary>
        /// Execute a SQLiteCommand (that returns a resultset) against the specified SQLiteConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">a valid SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>a dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataset(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
        {
            //创建一个SQLiteCommand对象，并对其进行初始化
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, connection, (SQLiteTransaction)null, commandType, commandText, commandParameters);
            
            //创建SQLiteDataAdapter对象以及DataSet
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);  
            DataSet ds = new DataSet(); 

            //填充ds
            da.Fill(ds);
            
            // 清除cmd的参数集合    
            cmd.Parameters.Clear();
            
            //返回ds
            return ds;                        
        }
        



        /// <summary>
        /// Execute a SQLiteCommand that returns the first column of the first record against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms) 
        {
            SQLiteCommand cmd = new SQLiteCommand();

            using (SQLiteConnection conn = new SQLiteConnection(connString)) 
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a SQLiteCommand that returns the first column of the first record against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(SQLiteConnection conn, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms) 
        {
            
            SQLiteCommand cmd = new SQLiteCommand();

            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// add parameter array to the cache
        /// </summary>
        /// <param name="cacheKey">Key to the parameter cache</param>
        /// <param name="cmdParms">an array of SqlParamters to be cached</param>
        public static void CacheParameters(string cacheKey, params SQLiteParameter[] cmdParms) 
        {
            parmCache[cacheKey] = cmdParms;
        }

        /// <summary>
        /// Retrieve cached parameters
        /// </summary>
        /// <param name="cacheKey">key used to lookup parameters</param>
        /// <returns>Cached SqlParamters array</returns>
        public static SQLiteParameter[] GetCachedParameters(string cacheKey) 
        {
            SQLiteParameter[] cachedParms = (SQLiteParameter[])parmCache[cacheKey];
            
            if (cachedParms == null)
                return null;
            
            SQLiteParameter[] clonedParms = new SQLiteParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SQLiteParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">SQLiteCommand object</param>
        /// <param name="conn">SQLiteConnection object</param>
        /// <param name="trans">SQLiteTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SQLiteParameters to use in the command</param>
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, CommandType cmdType, string cmdText, SQLiteParameter[] cmdParms)
        {
            //判断连接的状态。如果是关闭状态，则打开
            if (conn.State != ConnectionState.Open)
                conn.Open();
            //cmd属性赋值
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            //是否需要用到事务处理
            if (trans != null)
                cmd.Transaction = trans;
                
            cmd.CommandType = cmdType;
            //添加cmd需要的存储过程参数
            if (cmdParms != null) 
            {
                foreach (SQLiteParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }

登录调用例子：（using using System.Data.SQLite;）

　　　　　　string userloginname = txtLoginName.Text.Trim();
           string userpassword = txtPWD.Text.Trim();
           string sqlcomtext = "select * from U_Login where U_Name='" + userloginname + "'and U_PWD ='" + userpassword + "'";
           SQLiteDataReader dr = DataExecute.ExecuteReader(DataExecute.sqlite, CommandType.Text, sqlcomtext);
           if (dr.Read())
           {
               Session["name"] = txtLoginName.Text.Trim();
               Response.Write("<script>alert('登录成功！" + Session["name"] + "')</script>");
           }
           else
           {
               Response.Write("<script>alert('密码不正确或用户不存在')</script>");
           }

源码和System.Data.SQLite下载 DOWNLOAD