using System;
using System.Data;
using System.Text.RegularExpressions;
using System.Xml;
using System.IO;
using System.Collections;
using System.Data.SQLite;
using System.Configuration;//添加.net參考
namespace Utills
{
/// <summary>
/// 對SQLite操作的類
/// 參考：System.Data.SQLite.dll【版本：3.6.23.1（原始檔案名：SQlite3.DLL）】
/// </summary>
public class SQLiteHelper
{
/// <summary>
/// 所有成員函式都是靜態的，建構式定義為私有
/// </summary>
private SQLiteHelper()
{
}
/// <summary>
/// 連接字串
/// </summary>
public static string ConnectionString
{//"Data Source=Test.db3;Pooling=true;FailIfMissing=false";
get
{
////(AppSettings節點下的"SQLiteConnectionString")
string text = ConfigurationManager.AppSettings["SQLiteConnectionString"];
//string str2 = ConfigurationManager.AppSettings["IsEncrypt"];
//if (str2 == "true")
//{
// text = DesEncrypt.Decrypt(text);
//}
return text;
}
}
private static SQLiteConnection _Conn = null;
/// <summary>
/// 連接物件
/// </summary>
public static SQLiteConnection Conn
{
get
{
if (_Conn == null) _Conn = new SQLiteConnection(ConnectionString);
return SQLiteHelper._Conn;
}
set { SQLiteHelper._Conn = value; }
}
#region CreateCommand(commandText,SQLiteParameter[])
/// <summary>
/// 創建命令
/// </summary>
/// <param name="connection">連接</param>
/// <param name="commandText">陳述句</param>
/// <param name="commandParameters">陳述句引數.</param>
/// <returns>SQLite Command</returns>
public static SQLiteCommand CreateCommand(string commandText, params SQLiteParameter[] commandParameters)
{
SQLiteCommand cmd = new SQLiteCommand(commandText, Conn);
if (commandParameters.Length > 0)
{
foreach (SQLiteParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
return cmd;
}
#endregion
#region CreateParameter(parameterName,parameterType,parameterValue)
/// <summary>
/// 創建引數
/// </summary>
/// <param name="parameterName">引數名</param>
/// <param name="parameterType">引數型別</param>
/// <param name="parameterValue">引數值</param>
/// <returns>回傳創建的引數</returns>
public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue)
{
SQLiteParameter parameter = new SQLiteParameter();
parameter.DbType = parameterType;
parameter.ParameterName = parameterName;
parameter.Value = parameterValue;
return parameter;
}
#endregion
#region ExecuteDataSet(commandText,paramList[])
/// <summary>
/// 查詢資料集
/// </summary>
/// <param name="cn">連接.</param>
/// <param name="commandText">查詢陳述句.</param>
/// <param name="paramList">object引數串列.</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandText, params object[] paramList)
{
SQLiteCommand cmd = Conn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null)
{
AttachParameters(cmd, commandText, paramList);
}
DataSet ds = new DataSet();
if (Conn.State == ConnectionState.Closed)
Conn.Open();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
Conn.Close();
return ds;
}
#endregion
#region ExecuteDataSet(SQLiteCommand)
/// <summary>
/// 查詢資料集
/// </summary>
/// <param name="cmd">SQLiteCommand物件</param>
/// <returns>回傳資料集</returns>
public static DataSet ExecuteDataSet(SQLiteCommand cmd)
{
if (cmd.Connection.State == ConnectionState.Closed)
cmd.Connection.Open();
DataSet ds = new DataSet();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Connection.Close();
cmd.Dispose();
return ds;
}
#endregion
#region ExecuteDataSet(SQLiteTransaction,commandText,params SQLiteParameter[])
/// <summary>
/// 查詢資料集
/// </summary>
/// <param name="transaction">SQLiteTransaction物件. </param>
/// <param name="commandText">查詢陳述句.</param>
/// <param name="commandParameters">命令的引數串列.</param>
/// <returns>DataSet</returns>
/// <remarks>必須手動執行關閉連接transaction.connection.Close</remarks>
public static DataSet ExecuteDataSet(SQLiteTransaction transaction, string commandText, params SQLiteParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
IDbCommand cmd = transaction.Connection.CreateCommand();
cmd.CommandText = commandText;
foreach (SQLiteParameter parm in commandParameters)
{
cmd.Parameters.Add(parm);
}
if (transaction.Connection.State == ConnectionState.Closed)
transaction.Connection.Open();
DataSet ds = ExecuteDataSet((SQLiteCommand)cmd);
return ds;
}
#endregion
#region ExecuteDataSet(SQLiteTransaction,commandText,object[] commandParameters)
/// <summary>
/// 查詢資料集
/// </summary>
/// <param name="transaction">SQLiteTransaction物件 </param>
/// <param name="commandText">查詢陳述句.</param>
/// <param name="commandParameters">命令引數串列</param>
/// <returns>回傳資料集</returns>
/// <remarks>必須手動執行關閉連接transaction.connection.Close</remarks>
public static DataSet ExecuteDataSet(SQLiteTransaction transaction, string commandText, object[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
IDbCommand cmd = transaction.Connection.CreateCommand();
cmd.CommandText = commandText;
AttachParameters((SQLiteCommand)cmd, cmd.CommandText, commandParameters);
if (transaction.Connection.State == ConnectionState.Closed)
transaction.Connection.Open();
DataSet ds = ExecuteDataSet((SQLiteCommand)cmd);
return ds;
}
#endregion
#region UpdateDataset(insertCommand,deleteCommand,updateCommand,dataSet,tableName)
/// <summary>
/// 更新資料集中資料到資料庫
/// </summary>
/// <param name="insertCommand">insert陳述句</param>
/// <param name="deleteCommand">delete陳述句</param>
/// <param name="updateCommand">update陳述句</param>
/// <param name="dataSet">要更新的DataSet</param>
/// <param name="tableName">資料集中要更新的table名</param>
public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null) throw new ArgumentNullException("insertCommand");
if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
if (updateCommand == null) throw new ArgumentNullException("updateCommand");
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
// Create a SQLiteDataAdapter, and dispose of it after we are done
using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter())
{
// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data source
dataAdapter.Update(dataSet, tableName);
// Commit all the changes made to the DataSet
dataSet.AcceptChanges();
}
}
#endregion
#region ExecuteReader(SQLiteCommand,commandText, object[] paramList)
/// <summary>
/// ExecuteReader方法
/// </summary>
/// <param name="cmd">查詢命令</param>
/// <param name="commandText">含有類似@colume引數的sql陳述句</param>
/// <param name="paramList">陳述句引數串列</param>
/// <returns>IDataReader</returns>
public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList)
{
if (cmd.Connection == null)
throw new ArgumentException("沒有為命令指定活動的連接.", "cmd");
cmd.CommandText = commandText;
AttachParameters(cmd, commandText, paramList);
if (cmd.Connection.State == ConnectionState.Closed)
cmd.Connection.Open();
IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rdr;
}
#endregion
#region ExecuteNonQuery(commandText,para
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/11848.html
標籤：其他數據庫
上一篇：求 一句 SQL, 謝謝大家。