C#基于SQLiteHelper类似SqlHelper类实现存取Sqlite数据库办法

后端开发|C#.Net教程 本文实例讲述了C#基于SQLiteHelper类似SqlHelper类实现存取Sqlite数据库的方法。分享给大家供大家参考。具体如下：这个类不是我实现的，英文原文地址为http://www.eggheadcafe.com/articles/20050315.asp，这里修改了原文中分析sql语句参数的方法，将方法名修改为AttachParameters，将其修饰符修改为private，并直接传递command到这个方法，直接绑定参数到comand。修改后的代码如下

using System;using System.Data;using System.Text.RegularExpressions;using System.Xml;using System.IO;using System.Collections;using System.Data.SQLite;namespace DBUtility.SQLite{ ///

/// SQLiteHelper is a utility class similar to "SQLHelper" in MS /// Data Access Application Block and follows similar pattern. ///

public class SQLiteHelper { ///

/// Creates a new instance. The ctor is marked private since all members are static. ///

private SQLiteHelper() { } ///

/// Creates the command. ///

/// Connection. /// Command text. /// Command parameters. /// SQLite Command public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters) { SQLiteCommand cmd = new SQLiteCommand(commandText, connection); if (commandParameters.Length > 0) { foreach (SQLiteParameter parm in commandParameters) cmd.Parameters.Add(parm); } return cmd; } ///

/// Creates the command. ///

/// Connection string. /// Command text. /// Command parameters. /// SQLite Command public static SQLiteCommand CreateCommand(string connectionString, string commandText, params SQLiteParameter[] commandParameters) { SQLiteConnection cn = new SQLiteConnection(connectionString); SQLiteCommand cmd = new SQLiteCommand(commandText, cn); if (commandParameters.Length > 0) { foreach (SQLiteParameter parm in commandParameters) cmd.Parameters.Add(parm); } return cmd; } ///

/// Creates the parameter. ///

/// Name of the parameter. /// Parameter type. /// Parameter value. /// SQLiteParameter 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; } ///

/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values ///

/// SQLite Connection string /// SQL Statement with embedded "@param" style parameter names /// object[] array of parameter values /// public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList) { SQLiteConnection cn = new SQLiteConnection(connectionString); SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; if (paramList != null) { AttachParameters(cmd,commandText, paramList); } DataSet ds = new DataSet(); if (cn.State == ConnectionState.Closed) cn.Open(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Dispose(); cn.Close(); return ds; } ///

/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values ///

/// Connection. /// Command text. /// Param list. /// public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList) { SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; if (paramList != null) { AttachParameters(cmd,commandText, paramList); } DataSet ds = new DataSet(); if (cn.State == ConnectionState.Closed) cn.Open(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Dispose(); cn.Close(); return ds; } ///

/// Executes the dataset from a populated Command object. ///

/// Fully populated SQLiteCommand /// DataSet 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; } ///

/// Executes the dataset in a SQLite Transaction ///

/// SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. /// Command text. /// Sqlite Command parameters. /// DataSet /// user must examine Transaction Object and handle transaction.connection .Close, etc. 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; } ///

/// Executes the dataset with Transaction and object array of parameter values. ///

/// SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. /// Command text. /// object[] array of parameter values. /// DataSet /// user must examine Transaction Object and handle transaction.connection .Close, etc. 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; } #region UpdateDataset ///

/// Executes the respective command for each inserted, updated, or deleted row in the DataSet. ///

/// /// e.g.: /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); /// /// A valid SQL statement to insert new records into the data source /// A valid SQL statement to delete records from the data source /// A valid SQL statement used to update records in the data source /// The DataSet used to update the data source /// The DataTable used to update the data source. 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 ///

/// ShortCut method to return IDataReader /// NOTE: You should explicitly close the Command.connection you passed in as /// well as call Dispose on the Command after reader is closed. /// We do this because IDataReader has no underlying Connection Property. ///

/// SQLiteCommand Object /// SQL Statement with optional embedded "@param" style parameters /// object[] array of parameter values /// IDataReader public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList) { if (cmd.COnnection== null) throw new ArgumentException("Command must have live connection attached.", "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; } ///

/// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values ///

/// SQLite Connection String /// Sql Statement with embedded "@param" style parameters /// object[] array of parameter values /// public static int ExecuteNonQuery(string connectionString, string commandText, params object[] paramList) { SQLiteConnection cn = new SQLiteConnection(connectionString); SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; AttachParameters(cmd,commandText, paramList); if (cn.State == ConnectionState.Closed) cn.Open(); int result = cmd.ExecuteNonQuery(); cmd.Dispose(); cn.Close(); return result; } public static int ExecuteNonQuery(SQLiteConnection cn, string commandText, params object[] paramList) { SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; AttachParameters(cmd,commandText, paramList); if (cn.State == ConnectionState.Closed) cn.Open(); int result = cmd.ExecuteNonQuery(); cmd.Dispose(); cn.Close(); return result; } ///

/// Executes non-query sql Statment with Transaction ///

/// SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. /// Command text. /// Param list. /// Integer /// user must examine Transaction Object and handle transaction.connection .Close, etc. public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params object[] paramList) { 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, paramList); if (transaction.Connection.State == ConnectionState.Closed) transaction.Connection.Open(); int result = cmd.ExecuteNonQuery(); cmd.Dispose(); return result; } ///

/// Executes the non query. ///

/// CMD. /// public static int ExecuteNonQuery(IDbCommand cmd) { if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open(); int result = cmd.ExecuteNonQuery(); cmd.Connection.Close(); cmd.Dispose(); return result; } ///

/// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values ///

/// SQLite Connection String /// SQL statment with embedded "@param" style parameters /// object[] array of param values /// public static object ExecuteScalar(string connectionString, string commandText, params object[] paramList) { SQLiteConnection cn = new SQLiteConnection(connectionString); SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; AttachParameters(cmd,commandText, paramList); if (cn.State == ConnectionState.Closed) cn.Open(); object result = cmd.ExecuteScalar(); cmd.Dispose(); cn.Close(); return result; } ///

/// Execute XmlReader with complete Command ///

/// SQLite Command /// XmlReader public static XmlReader ExecuteXmlReader(IDbCommand command) { // open the connection if necessary, but make sure we // know to close it when were done. if (command.Connection.State != ConnectionState.Open) { command.Connection.Open(); } // get a data adapter SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)command); DataSet ds = new DataSet(); // fill the data set, and return the schema information da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.Fill(ds); // convert our dataset to XML StringReader stream = new StringReader(ds.GetXml()); command.Connection.Close(); // convert our stream of text to an XmlReader return new XmlTextReader(stream); } ///

/// Parses parameter names from SQL Statement, assigns values from object array , /// and returns fully populated ParameterCollection. ///

/// Sql Statement with "@param" style embedded parameters /// object[] array of parameter values /// SQLiteParameterCollection /// Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement. private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList) { if (paramList == null || paramList.Length == 0) return null; SQLiteParameterCollection coll = cmd.Parameters; string parmString = commandText.Substring(commandText.IndexOf("@")); // pre-process the string so always at least 1 space after a comma. parmString = parmString.Replace(",", " ,"); // get the named parameters into a match collection string pattern = @"(@)\S*(.*?)\b"; Regex ex = new Regex(pattern, RegexOptions.IgnoreCase); MatchCollection mc = ex.Matches(parmString); string[] paramNames = new string[mc.Count]; int i = 0; foreach (Match m in mc) { paramNames[i] = m.Value; i++; } // now let's type the parameters int j = 0; Type t = null; foreach (object o in paramList) { t = o.GetType(); SQLiteParameter parm = new SQLiteParameter(); switch (t.ToString()) { case ("DBNull"): case ("Char"): case ("SByte"): case ("UInt16"): case ("UInt32"): case ("UInt64"): throw new SystemException("Invalid data type"); case ("System.String"): parm.DbType = DbType.String; parm.ParameterName = paramNames[j]; parm.Value = (string)paramList[j]; coll.Add(parm); break; case ("System.Byte[]"): parm.DbType = DbType.Binary; parm.ParameterName = paramNames[j]; parm.Value = (byte[])paramList[j]; coll.Add(parm); break; case ("System.Int32"): parm.DbType = DbType.Int32; parm.ParameterName = paramNames[j]; parm.Value = (int)paramList[j]; coll.Add(parm); break; case ("System.Boolean"): parm.DbType = DbType.Boolean; parm.ParameterName = paramNames[j]; parm.Value = (bool)paramList[j]; coll.Add(parm); break; case ("System.DateTime"): parm.DbType = DbType.DateTime; parm.ParameterName = paramNames[j]; parm.Value = Convert.ToDateTime(paramList[j]); coll.Add(parm); break; case ("System.Double"): parm.DbType = DbType.Double; parm.ParameterName = paramNames[j]; parm.Value = Convert.ToDouble(paramList[j]); coll.Add(parm); break; case ("System.Decimal"): parm.DbType = DbType.Decimal; parm.ParameterName = paramNames[j]; parm.Value = Convert.ToDecimal(paramList[j]); break; case ("System.Guid"): parm.DbType = DbType.Guid; parm.ParameterName = paramNames[j]; parm.Value = (System.Guid)(paramList[j]); break; case ("System.Object"): parm.DbType = DbType.Object; parm.ParameterName = paramNames[j]; parm.Value = paramList[j]; coll.Add(parm); break; default: throw new SystemException("Value is of unknown data type"); } // end switch j++; } return coll; } ///

/// Executes non query typed params from a DataRow ///

/// Command. /// Data row. /// Integer result code public static int ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow) { int retVal = 0; // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Set the parameters values AssignParameterValues(command.Parameters, dataRow); retVal = ExecuteNonQuery(command); } else { retVal = ExecuteNonQuery(command); } return retVal; } ///

/// This method assigns dataRow column values to an IDataParameterCollection ///

/// The IDataParameterCollection to be assigned values /// The dataRow used to hold the command's parameter values /// Thrown if any of the parameter names are invalid. protected internal static void AssignParameterValues(IDataParameterCollection commandParameters, DataRow dataRow) { if (commandParameters == null || dataRow == null) { // Do nothing if we get no data return; } DataColumnCollection columns = dataRow.Table.Columns; int i = 0; // Set the parameters values foreach (IDataParameter commandParameter in commandParameters) { // Check the parameter name if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1) throw new InvalidOperationException(string.Format( "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: &＃039;{1}&＃039;.", i, commandParameter.ParameterName)); if (columns.Contains(commandParameter.ParameterName)) commandParameter.Value = dataRow[commandParameter.ParameterName]; else if (columns.Contains(commandParameter.ParameterName.Substring(1))) commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)]; i++; } } ///

/// This method assigns dataRow column values to an array of IDataParameters ///

/// Array of IDataParameters to be assigned values /// The dataRow used to hold the stored procedure's parameter values /// Thrown if any of the parameter names are invalid. protected void AssignParameterValues(IDataParameter[] commandParameters, DataRow dataRow) { if ((commandParameters == null) || (dataRow == null)) { // Do nothing if we get no data return; } DataColumnCollection columns = dataRow.Table.Columns; int i = 0; // Set the parameters values foreach (IDataParameter commandParameter in commandParameters) { // Check the parameter name if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1) throw new InvalidOperationException(string.Format( "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: &＃039;{1}&＃039;.", i, commandParameter.ParameterName)); if (columns.Contains(commandParameter.ParameterName)) commandParameter.Value = dataRow[commandParameter.ParameterName]; else if (columns.Contains(commandParameter.ParameterName.Substring(1))) commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)]; i++; } } ///

/// This method assigns an array of values to an array of IDataParameters ///

/// Array of IDataParameters to be assigned values /// Array of objects holding the values to be assigned /// Thrown if an incorrect number of parameters are passed. protected void AssignParameterValues(IDataParameter[] commandParameters, params object[] parameterValues) { if ((commandParameters == null) || (parameterValues == null)) { // Do nothing if we get no data return; } // We must have the same number of values as we pave parameters to put them in if (commandParameters.Length != parameterValues.Length) { throw new ArgumentException("Parameter count does not match Parameter Value count."); } // Iterate through the IDataParameters, assigning the values from the corresponding position in the // value array for (int i = 0, j = commandParameters.Length, k = 0; i

织梦免费源码博彩,ubuntu内置蓝牙驱动,好看的爬虫宠物,网站php结构,全国seo优化lzw

更多C#基于SQLiteHelper类似SqlHelper类实现存取Sqlite数据库的方法相关文章请关注PHP中文网！