SQLite for Net 学习笔记（1）

1、使用 SQLite Administrator（sqliteadmin.exe）图形管理工具

2、SQLite Help基类

using System;
 using System.Collections.Generic;
 using System.Text;
 using System.Web;
 using System.Configuration;
 using System.Data;
 using System.Data.SQLite;
 namespace DAL
 
 {
     public class Sqlite
 
     {
         /// <summary>
         /// 获得连接对象
         /// </summary>
         /// <returns></returns>
         public static SQLiteConnection GetSQLiteConnection()
         {
             return new SQLiteConnection("Data Source=" + System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["db"].ToString()));
         }
 
         private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)
         {
             if (conn.State != ConnectionState.Open)
                 conn.Open();
             cmd.Parameters.Clear();
             cmd.Connection = conn;
             cmd.CommandText = cmdText;
             cmd.CommandType = CommandType.Text;
             cmd.CommandTimeout = 30;
             if (p != null)
 
             {
                 foreach (object parm in p)
                     cmd.Parameters.AddWithValue(string.Empty, parm);
                 //for (int i = 0; i < p.Length; i++)
                 //    cmd.Parameters[i].Value = p[i];
             }
         }
         public static DataSet ExecuteDataset(string cmdText, params object[] p)
         {
             DataSet ds = new DataSet();
             SQLiteCommand command = new SQLiteCommand();
             using (SQLiteConnection connection = GetSQLiteConnection())
             {
                 PrepareCommand(command, connection, cmdText, p);
                 SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                 da.Fill(ds);
             }
             return ds;
         }
         public static DataRow ExecuteDataRow(string cmdText, params object[] p)
         {
             DataSet ds = ExecuteDataset(cmdText, p);
             if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                 return ds.Tables[0].Rows[0];
             return null;
         }
         /// <summary>
         /// 返回受影响的行数
         /// </summary>
         /// <param name="cmdText">a</param>
         /// <param name="commandParameters">传入的参数</param>
         /// <returns></returns>
         public static int ExecuteNonQuery(string cmdText, params object[] p)
         {
             SQLiteCommand command = new SQLiteCommand();
             using (SQLiteConnection connection = GetSQLiteConnection())
             {
                 PrepareCommand(command, connection, cmdText, p);
                 return command.ExecuteNonQuery();
             }
         }
         /// <summary>
         /// 返回SqlDataReader对象
         /// </summary>
         /// <param name="cmdText"></param>
         /// <param name="commandParameters">传入的参数</param>
         /// <returns></returns>
         public static SQLiteDataReader ExecuteReader(string cmdText, params object[] p)
         {
             SQLiteCommand command = new SQLiteCommand();
             SQLiteConnection connection = GetSQLiteConnection();
             try
             {
                 PrepareCommand(command, connection, cmdText, p);
                 SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                 return reader;
             }
             catch
             {
                 connection.Close();
                 throw;
             }
         }
         /// <summary>
         /// 返回结果集中的第一行第一列，忽略其他行或列
         /// </summary>
         /// <param name="cmdText"></param>
         /// <param name="commandParameters">传入的参数</param>
         /// <returns></returns>
         public static object ExecuteScalar(string cmdText, params object[] p)
         {
             SQLiteCommand cmd = new SQLiteCommand();
             using (SQLiteConnection connection = GetSQLiteConnection())
             {
                 PrepareCommand(cmd, connection, cmdText, p);
                 return cmd.ExecuteScalar();
             }
         }
         /// <summary>
         /// 分页
         /// </summary>
         /// <param name="recordCount"></param>
         /// <param name="pageIndex"></param>
         /// <param name="pageSize"></param>
         /// <param name="cmdText"></param>
         /// <param name="countText"></param>
         /// <param name="p"></param>
         /// <returns></returns>
         public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params object[] p)
         {
             if (recordCount < 0)
                 recordCount = int.Parse(ExecuteScalar(countText, p).ToString());
             DataSet ds = new DataSet();
             SQLiteCommand command = new SQLiteCommand();
             using (SQLiteConnection connection = GetSQLiteConnection())
             {
                 PrepareCommand(command, connection, cmdText, p);
                 SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                 da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
             }
             return ds;
         }
     }
 }

3.操作类

using System;
 using System.Collections.Generic;
 using System.Web;
 using System.Web.UI;
 using System.Web.UI.WebControls;
 using System.Data.SQLite;
 
 namespace Demo
 {
     public partial class TestSqlLite : System.Web.UI.Page
     {
         protected void Page_Load(object sender, EventArgs e)
         {
 
         }
 
         protected void btnTest_Click(object sender, EventArgs e)
         {
             SQLiteConnection.ClearAllPools();
             SQLiteConnection.CreateFile(Server.MapPath("~") + "/UserData.dbx");
             SQLiteConnection conn = new SQLiteConnection("Data Source=" + Server.MapPath("~" + "/UserData.dbx"));
             conn.Open();
             Response.Write("打开数据库成功~~<br />");
 
             SQLiteCommand cmd = new SQLiteCommand();
             cmd.CommandText = "create table Users (UserID int primary key,UserName varchar(100) not null,UserPassword varchar(100) not null)";
             cmd.Connection = conn;
             cmd.ExecuteNonQuery();
 
             for (int i = 0; i < 100; i++)
             {
                 cmd.CommandText = "insert into Users (UserID,UserName,UserPassword) values (" + i + ",'TestUser_" + i + "','" + DateTime.Now.ToString().Replace(" ", "-").Replace(":", "-") + "')";
                 cmd.ExecuteNonQuery();
             }
 
             Response.Write("插入成功~~<br />");
 
             cmd.CommandText = "select Username from Users where UserID=1";
             cmd.Connection = conn;
             string tempUserName = cmd.ExecuteScalar().ToString();
 
             Response.Write("单个值查询结果为:" + tempUserName + "<br /><br />");
 
 
             cmd.CommandText = "select * from Users ";
             cmd.Connection = conn;
             SQLiteDataReader sdrInfo = cmd.ExecuteReader();
             if (sdrInfo != null)
             {
                 int userID = 0;
                 string userName = string.Empty;
                 string userPassword = string.Empty;
                 while (sdrInfo.Read())
                 {
                     userID = Convert.ToInt32(sdrInfo["UserID"]);
                     userName = sdrInfo["UserName"].ToString();
                     userPassword = sdrInfo["UserPassword"].ToString();
 
                     Response.Write("UserID:" + userID + "<br />");
                     Response.Write("UserName:" + userName + "<br />");
                     Response.Write("UserPassword:" + userPassword + "<br />");
                     Response.Write("<br />");
                 }
                 sdrInfo.Close();
                 sdrInfo.Dispose();
             }
 
             cmd.CommandText = "update Users set UserPassword='linxiang'";
             cmd.Connection = conn;
             cmd.ExecuteNonQuery();
             Response.Write("更新数据库中的数据成功.");
 
             Response.Write("以下结果为查询从数据库中经过编辑过后的数据项<br /><br />");
             cmd.CommandText = "select * from Users ";
             cmd.Connection = conn;
             sdrInfo = cmd.ExecuteReader();
             if (sdrInfo != null)
             {
                 int userID = 0;
                 string userName = string.Empty;
                 string userPassword = string.Empty;
                 while (sdrInfo.Read())
                 {
                     userID = Convert.ToInt32(sdrInfo["UserID"]);
                     userName = sdrInfo["UserName"].ToString();
                     userPassword = sdrInfo["UserPassword"].ToString();
 
                     Response.Write("UserID:" + userID + "<br />");
                     Response.Write("UserName:" + userName + "<br />");
                     Response.Write("UserPassword:" + userPassword + "<br />");
                     Response.Write("<br />");
                 }
                 sdrInfo.Close();
                 sdrInfo.Dispose();
             }
 
             conn.Clone();
             conn.Dispose();
         }
     }
 }