SQLiteHelper

SQLiteHelper 帮助类 [三]
View Code
  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Data.SQLite;//
  6 using System.Data;//
  7 using System.IO;//
  8 using System.Data.Common;//
  9 using System.Configuration;//
 10 
 11 namespace SQLiteHelper
 12 {
 13     public class SQLiteHelper43
 14     {
 15         
 16         #region 参数化查询
 17 
 18         /// <summary>
 19         /// 获得连接对象
 20         /// </summary>
 21         /// <returns></returns>
 22         public static SQLiteConnection GetSQLiteConnection()
 23         {
 24             return new SQLiteConnection("Data Source=" + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["db"].ToString()));
 25         }
 26 
 27         private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)
 28         {
 29             if (conn.State != ConnectionState.Open)
 30                 conn.Open();
 31             cmd.Parameters.Clear();
 32             cmd.Connection = conn;
 33             cmd.CommandText = cmdText;
 34             cmd.CommandType = CommandType.Text;
 35             cmd.CommandTimeout = 30;
 36             if (p != null)
 37             {
 38                 foreach (object parm in p)
 39                     cmd.Parameters.AddWithValue(string.Empty, parm);
 40                 //for (int i = 0; i < p.Length; i++)
 41                 //    cmd.Parameters[i].Value = p[i];
 42             }
 43         }
 44 
 45         public static DataSet ExecuteDataset(string cmdText, params object[] p)
 46         {
 47             DataSet ds = new DataSet();
 48             SQLiteCommand command = new SQLiteCommand();
 49             using (SQLiteConnection connection = GetSQLiteConnection())
 50             {
 51                 PrepareCommand(command, connection, cmdText, p);
 52                 SQLiteDataAdapter da = new SQLiteDataAdapter(command);
 53                 da.Fill(ds);
 54             }
 55             return ds;
 56         }
 57 
 58         public static DataRow ExecuteDataRow(string cmdText, params object[] p)
 59         {
 60             DataSet ds = ExecuteDataset(cmdText, p);
 61             if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
 62                 return ds.Tables[0].Rows[0];
 63             return null;
 64         }
 65 
 66         /// <summary>
 67         /// 返回受影响的行数
 68         /// </summary>
 69         /// <param name="cmdText">a</param>
 70         /// <param name="commandParameters">传入的参数</param>
 71         /// <returns></returns>
 72         public static int ExecuteNonQuery(string cmdText, params object[] p)
 73         {
 74             SQLiteCommand command = new SQLiteCommand();
 75             using (SQLiteConnection connection = GetSQLiteConnection())
 76             {
 77                 PrepareCommand(command, connection, cmdText, p);
 78                 return command.ExecuteNonQuery();
 79             }
 80         }
 81 
 82         /// <summary>
 83         /// 返回SqlDataReader对象
 84         /// </summary>
 85         /// <param name="cmdText"></param>
 86         /// <param name="commandParameters">传入的参数</param>
 87         /// <returns></returns>
 88         public static SQLiteDataReader ExecuteReader(string cmdText, params object[] p)
 89         {
 90             SQLiteCommand command = new SQLiteCommand();
 91             SQLiteConnection connection = GetSQLiteConnection();
 92             try
 93             {
 94                 PrepareCommand(command, connection, cmdText, p);
 95                 SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
 96                 return reader;
 97             }
 98             catch
 99             {
100                 connection.Close();
101                 throw;
102             }
103         }
104 
105         /// <summary>
106         /// 返回结果集中的第一行第一列，忽略其他行或列
107         /// </summary>
108         /// <param name="cmdText"></param>
109         /// <param name="commandParameters">传入的参数</param>
110         /// <returns></returns>
111         public static object ExecuteScalar(string cmdText, params object[] p)
112         {
113             SQLiteCommand cmd = new SQLiteCommand();
114             using (SQLiteConnection connection = GetSQLiteConnection())
115             {
116                 PrepareCommand(cmd, connection, cmdText, p);
117                 return cmd.ExecuteScalar();
118             }
119         }
120 
121         /// <summary>
122         /// 分页
123         /// </summary>
124         /// <param name="recordCount"></param>
125         /// <param name="pageIndex"></param>
126         /// <param name="pageSize"></param>
127         /// <param name="cmdText"></param>
128         /// <param name="countText"></param>
129         /// <param name="p"></param>
130         /// <returns></returns>
131         public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params object[] p)
132         {
133             if (recordCount < 0)
134                 recordCount = int.Parse(ExecuteScalar(countText, p).ToString());
135             DataSet ds = new DataSet();
136             SQLiteCommand command = new SQLiteCommand();
137             using (SQLiteConnection connection = GetSQLiteConnection())
138             {
139                 PrepareCommand(command, connection, cmdText, p);
140                 SQLiteDataAdapter da = new SQLiteDataAdapter(command);
141                 da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
142             }
143             return ds;
144         }
145         #endregion
146     }
147 }