C# SQLiteHelper
2024-10-19 01:28:36
using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Text.RegularExpressions;
using System.Xml; /// <summary>
/// SQLiteHelper is a utility class similar to "SQLHelper" in MS
/// Data Access Application Block and follows similar pattern.
/// </summary>
public class SQLiteHelper
{
/// <summary>
/// Creates a new <see cref="SQLiteHelper"/> instance. The ctor is marked private since all members are static.
/// </summary>
private SQLiteHelper()
{
} /// <summary>
/// Creates the command.
/// </summary>
/// <param name="connection">Connection.</param>
/// <param name="commandText">Command text.</param>
/// <param name="commandParameters">Command parameters.</param>
/// <returns>SQLite Command</returns>
public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
{
SQLiteCommand cmd = new SQLiteCommand(commandText, connection);
if (commandParameters.Length > )
{
foreach (SQLiteParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
return cmd;
} /// <summary>
/// Creates the command.
/// </summary>
/// <param name="connectionString">Connection string.</param>
/// <param name="commandText">Command text.</param>
/// <param name="commandParameters">Command parameters.</param>
/// <returns>SQLite Command</returns>
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 > )
{
foreach (SQLiteParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
return cmd;
} /// <summary>
/// Creates the parameter.
/// </summary>
/// <param name="parameterName">Name of the parameter.</param>
/// <param name="parameterType">Parameter type.</param>
/// <param name="parameterValue">Parameter value.</param>
/// <returns>SQLiteParameter</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;
} /// <summary>
/// Executes the dataset from a populated Command object.
/// </summary>
/// <param name="cmd">Fully populated SQLiteCommand</param>
/// <returns>DataSet</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;
} /// <summary>
/// Executes the dataset in a SQLite Transaction
/// </summary>
/// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. </param>
/// <param name="commandText">Command text.</param>
/// <param name="commandParameters">Sqlite Command parameters.</param>
/// <returns>DataSet</returns>
/// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</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;
} /// <summary>
/// Executes the dataset with Transaction and object array of parameter values.
/// </summary>
/// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. </param>
/// <param name="commandText">Command text.</param>
/// <param name="commandParameters">object[] array of parameter values.</param>
/// <returns>DataSet</returns>
/// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</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;
} /// <summary>
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
/// </summary>
/// <param name="connectionString">SQLite Connection string</param>
/// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList)
{
using (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;
}
} /// <summary>
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
/// </summary>
/// <param name="cn">Connection.</param>
/// <param name="commandText">Command text.</param>
/// <param name="paramList">Param list.</param>
/// <returns></returns>
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;
} #region UpdateDataset /// <summary>
/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
/// </summary>
/// <remarks>
/// e.g.:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
/// </remarks>
/// <param name="insertCommand">A valid SQL statement to insert new records into the data source</param>
/// <param name="deleteCommand">A valid SQL statement to delete records from the data source</param>
/// <param name="updateCommand">A valid SQL statement used to update records in the data source</param>
/// <param name="dataSet">The DataSet used to update the data source</param>
/// <param name="tableName">The DataTable used to update the data source.</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 == ) 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 UpdateDataset /// <summary>
/// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values
/// </summary>
/// <param name="connectionString">SQLite Connection String</param>
/// <param name="commandText">Sql Statement with embedded "@param" style parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns></returns>
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;
} /// <summary>
/// Executes non-query sql Statment with Transaction
/// </summary>
/// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. </param>
/// <param name="commandText">Command text.</param>
/// <param name="paramList">Param list.</param>
/// <returns>Integer</returns>
/// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
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;
} /// <summary>
/// Executes the non query.
/// </summary>
/// <param name="cmd">CMD.</param>
/// <returns></returns>
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;
} /// <summary>
/// Executes non query typed params from a DataRow
/// </summary>
/// <param name="command">Command.</param>
/// <param name="dataRow">Data row.</param>
/// <returns>Integer result code</returns>
public static int ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow)
{
int retVal = ; // If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > )
{
// Set the parameters values
AssignParameterValues(command.Parameters, dataRow); retVal = ExecuteNonQuery(command);
}
else
{
retVal = ExecuteNonQuery(command);
} return retVal;
} /// <summary>
/// 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.
/// </summary>
/// <param name="cmd">SQLiteCommand Object</param>
/// <param name="commandText">SQL Statement with optional embedded "@param" style parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns>IDataReader</returns>
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;
} public static IDataReader ExecuteReader(string connectionString, string commandText, object[] paramList)
{
SQLiteConnection cn = new SQLiteConnection(connectionString);
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = commandText;
AttachParameters(cmd, commandText, paramList);
if (cmd.Connection.State == ConnectionState.Closed)
cmd.Connection.Open(); DbDataReader ddr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return ddr;
}
} public static IDataReader ExecuteReader(string connectionString, string commandText)
{
return ExecuteReader(connectionString, commandText, null);
} /// <summary>
/// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values
/// </summary>
/// <param name="connectionString">SQLite Connection String</param>
/// <param name="commandText">SQL statment with embedded "@param" style parameters</param>
/// <param name="paramList">object[] array of param values</param>
/// <returns></returns>
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;
} /// <summary>
/// Execute XmlReader with complete Command
/// </summary>
/// <param name="command">SQLite Command</param>
/// <returns>XmlReader</returns>
public static XmlReader ExecuteXmlReader(IDbCommand command)
{ // open the connection if necessary, but make sure we
// know to close it when we�re 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);
} /// <summary>
/// This method assigns dataRow column values to an IDataParameterCollection
/// </summary>
/// <param name="commandParameters">The IDataParameterCollection to be assigned values</param>
/// <param name="dataRow">The dataRow used to hold the command's parameter values</param>
/// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>
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 = ;
// Set the parameters values
foreach (IDataParameter commandParameter in commandParameters)
{
// Check the parameter name
if (commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= )
throw new InvalidOperationException(string.Format(
"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
i, commandParameter.ParameterName)); if (columns.Contains(commandParameter.ParameterName))
commandParameter.Value = dataRow[commandParameter.ParameterName];
else if (columns.Contains(commandParameter.ParameterName.Substring()))
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring()]; i++;
}
} /// <summary>
/// This method assigns dataRow column values to an array of IDataParameters
/// </summary>
/// <param name="commandParameters">Array of IDataParameters to be assigned values</param>
/// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
/// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>
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 = ;
// Set the parameters values
foreach (IDataParameter commandParameter in commandParameters)
{
// Check the parameter name
if (commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= )
throw new InvalidOperationException(string.Format(
"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
i, commandParameter.ParameterName)); if (columns.Contains(commandParameter.ParameterName))
commandParameter.Value = dataRow[commandParameter.ParameterName];
else if (columns.Contains(commandParameter.ParameterName.Substring()))
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring()]; i++;
}
} /// <summary>
/// This method assigns an array of values to an array of IDataParameters
/// </summary>
/// <param name="commandParameters">Array of IDataParameters to be assigned values</param>
/// <param name="parameterValues">Array of objects holding the values to be assigned</param>
/// <exception cref="System.ArgumentException">Thrown if an incorrect number of parameters are passed.</exception>
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 = , j = commandParameters.Length, k = ; i < j; i++)
{
if (commandParameters[i].Direction != ParameterDirection.ReturnValue)
{
// If the current array value derives from IDataParameter, then assign its Value property
if (parameterValues[k] is IDataParameter)
{
IDataParameter paramInstance;
paramInstance = (IDataParameter)parameterValues[k];
if (paramInstance.Direction == ParameterDirection.ReturnValue)
{
paramInstance = (IDataParameter)parameterValues[++k];
}
if (paramInstance.Value == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = paramInstance.Value;
}
}
else if (parameterValues[k] == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = parameterValues[k];
}
k++;
}
}
} /// <summary>
/// Parses parameter names from SQL Statement, assigns values from object array , /// and returns fully populated ParameterCollection.
/// </summary>
/// <param name="commandText">Sql Statement with "@param" style embedded parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns>SQLiteParameterCollection</returns>
/// <remarks>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.</remarks>
private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
{
if (paramList == null || paramList.Length == ) 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 = ;
foreach (Match m in mc)
{
paramNames[i] = m.Value;
i++;
} // now let's type the parameters
int j = ;
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;
}
}
using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml; /// <summary>
/// SQLiteHelper is a utility class similar to "SQLHelper" in MS
/// Data Access Application Block and follows similar pattern.
/// </summary>
public class SQLiteHelper
{
/// <summary>
/// Creates the command.
/// </summary>
/// <param name="connection">Connection.</param>
/// <param name="commandText">Command text.</param>
/// <param name="commandParameters">Command parameters.</param>
/// <returns>SQLite Command</returns>
public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
{
SQLiteCommand cmd = new SQLiteCommand(commandText, connection);
if (commandParameters.Length > )
{
foreach (SQLiteParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
return cmd;
} public static SQLiteCommand CreateCommand(string commandText, params SQLiteParameter[] commandParameters)
{
SQLiteCommand cmd = new SQLiteCommand(commandText);
if (commandParameters.Length > )
{
foreach (SQLiteParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
return cmd;
} public static SQLiteCommand CreateCommand(string CmdText, params object[] objs)
{
SQLiteCommand cmd = new SQLiteCommand(CmdText);
if (objs != null && objs.Length > )
{
objs = CheckParameters(objs);
CmdText = MyAddRange(CmdText, objs);
}
if (CheckIntrusion(CmdText))
{
throw new ArgumentNullException("检测到脚本入侵");
}
cmd.CommandText = CmdText.Replace("\r\n", " ").Replace("\t", " ");
return cmd;
} /// <summary>
/// Creates the parameter.
/// </summary>
/// <param name="parameterName">Name of the parameter.</param>
/// <param name="parameterType">Parameter type.</param>
/// <param name="parameterValue">Parameter value.</param>
/// <returns>SQLiteParameter</returns>
public static SQLiteParameter CreateParameter(string parameterName, DbType parameterType, object parameterValue)
{
SQLiteParameter parameter = new SQLiteParameter();
parameter.DbType = parameterType;
parameter.ParameterName = parameterName;
parameter.Value = parameterValue;
return parameter;
} /// <summary>
/// Execute XmlReader with complete Command
/// </summary>
/// <param name="command">SQLite Command</param>
/// <returns>XmlReader</returns>
public static XmlReader ExecuteXmlReader(IDbCommand command)
{ // open the connection if necessary, but make sure we
// know to close it when we're 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);
} public static T SQLCommand<T>(SQLiteCommand sqlCmd, SQLiteConnection conn)
{
T result = default(T); if (conn == null)
throw new ArgumentNullException("SQLiteConnection == null"); sqlCmd.Connection = conn;
if (sqlCmd.Connection.State == ConnectionState.Closed)
sqlCmd.Connection.Open(); string Ttype = typeof(T).ToString();
SQLiteDataAdapter sqlAdp;
DataSet ds;
switch (Ttype)
{
case "System.Data.DataSet":
sqlAdp = new SQLiteDataAdapter(sqlCmd);
ds = new DataSet();
sqlAdp.Fill(ds);
result = (T)Convert.ChangeType(ds, typeof(T));
break; case "System.Data.DataTable":
sqlAdp = new SQLiteDataAdapter(sqlCmd);
ds = new DataSet();
sqlAdp.Fill(ds);
result = (T)Convert.ChangeType(ds.Tables[], typeof(T));
break; case "System.Object":
result = (T)Convert.ChangeType(sqlCmd.ExecuteScalar(), typeof(T));
break; case "System.Int32":
result = (T)Convert.ChangeType(sqlCmd.ExecuteNonQuery(), typeof(T));
break; case "System.Data.SQLite.SQLiteDataReader":
result = (T)Convert.ChangeType(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection), typeof(T));
break;
}
sqlCmd.Connection.Close();
return result;
} /// <summary>
/// This method assigns dataRow column values to an IDataParameterCollection
/// </summary>
/// <param name="commandParameters">The IDataParameterCollection to be assigned values</param>
/// <param name="dataRow">The dataRow used to hold the command's parameter values</param>
/// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>
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 = ;
// Set the parameters values
foreach (IDataParameter commandParameter in commandParameters)
{
// Check the parameter name
if (commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= )
throw new InvalidOperationException(string.Format(
"Please provide a valid parameter name on the parameter #{0},the ParameterName property has the following value: '{1}'.",
i, commandParameter.ParameterName)); if (columns.Contains(commandParameter.ParameterName))
commandParameter.Value = dataRow[commandParameter.ParameterName];
else if (columns.Contains(commandParameter.ParameterName.Substring()))
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring()]; i++;
}
} /// <summary>
/// This method assigns dataRow column values to an array of IDataParameters
/// </summary>
/// <param name="commandParameters">Array of IDataParameters to be assigned values</param>
/// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
/// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>
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 = ;
// Set the parameters values
foreach (IDataParameter commandParameter in commandParameters)
{
// Check the parameter name
if (commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= )
throw new InvalidOperationException(string.Format(
"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
i, commandParameter.ParameterName)); if (columns.Contains(commandParameter.ParameterName))
commandParameter.Value = dataRow[commandParameter.ParameterName];
else if (columns.Contains(commandParameter.ParameterName.Substring()))
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring()]; i++;
}
} /// <summary>
/// This method assigns an array of values to an array of IDataParameters
/// </summary>
/// <param name="commandParameters">Array of IDataParameters to be assigned values</param>
/// <param name="parameterValues">Array of objects holding the values to be assigned</param>
/// <exception cref="System.ArgumentException">Thrown if an incorrect number of parameters are passed.</exception>
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 = , j = commandParameters.Length, k = ; i < j; i++)
{
if (commandParameters[i].Direction != ParameterDirection.ReturnValue)
{
// If the current array value derives from IDataParameter, then assign its Value property
if (parameterValues[k] is IDataParameter)
{
IDataParameter paramInstance;
paramInstance = (IDataParameter)parameterValues[k];
if (paramInstance.Direction == ParameterDirection.ReturnValue)
{
paramInstance = (IDataParameter)parameterValues[++k];
}
if (paramInstance.Value == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = paramInstance.Value;
}
}
else if (parameterValues[k] == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = parameterValues[k];
}
k++;
}
}
} /// <summary>
/// Parses parameter names from SQL Statement, assigns values from object array , /// and returns fully populated ParameterCollection.
/// </summary>
/// <param name="commandText">Sql Statement with "@param" style embedded parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns>SQLiteParameterCollection</returns>
/// <remarks>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.</remarks>
private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
{
if (paramList == null || paramList.Length == ) 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 = ;
foreach (Match m in mc)
{
paramNames[i] = m.Value;
i++;
} // now let's type the parameters
int j = ;
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;
} private static bool CheckIntrusion(string sql)
{
if (sql.ToLower().Contains("xp_cmdshell"))
{
return true;
}
return false;
} private static object[] CheckParameters(object[] objs)
{
for (int i = ; i < objs.Length; i++)
{
bool isok = false; string tmpParameters = objs[i].NullableToString().ToLower();
if (tmpParameters.StartsWith("'") && tmpParameters.EndsWith("'"))
{
if (!tmpParameters.Contains(" or ") && tmpParameters.Replace(" ", "").Contains("','"))
isok = true;
}
else if (tmpParameters.StartsWith("('") && tmpParameters.EndsWith("')"))
{
isok = true;
}
else if (objs[i].NullableToString().Contains("','"))
{
tmpParameters = objs[i].NullableToString().Replace(" ", "");
int aqty = tmpParameters.Length - tmpParameters.Replace("','", "',").Length;
int bqty = tmpParameters.Length - tmpParameters.Replace("'", "").Length;
if (bqty % == )
{
if (aqty + == bqty / )
{
isok = true;
}
}
}
if (!isok && objs[i].NullableToString().Contains("'"))
{
char[] chars = objs[i].NullableToString().ToCharArray();
StringBuilder sb = new StringBuilder();
for (int j = ; j < chars.Length; j++)
{
if (chars[j].ToString() == "'")
{
sb.Append("''");
}
else
{
sb.Append(chars[j]);
}
}
objs[i] = sb.ToString();
}
}
return objs;
} private static string MyAddRange(string cmd, object[] objs)
{
if (objs.Length > )
{
cmd = string.Format(cmd, args: objs);
}
return cmd;
} #region UpdateDataset /// <summary>
/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
/// </summary>
/// <remarks>
/// e.g.:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
/// </remarks>
/// <param name="insertCommand">A valid SQL statement to insert new records into the data source</param>
/// <param name="deleteCommand">A valid SQL statement to delete records from the data source</param>
/// <param name="updateCommand">A valid SQL statement used to update records in the data source</param>
/// <param name="dataSet">The DataSet used to update the data source</param>
/// <param name="tableName">The DataTable used to update the data source.</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 == ) 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 UpdateDataset
}
最新文章
- 表单reset无法重置hidden的解决方案
- logging 模块
- [CentOS]安装命令行终端Terminator工具
- putty ssh连接老断
- ArcGIS补丁包下载
- 如何更改java应用程序标题栏默认图标
- WEKA使用教程(界面工具的用法)
- 北大ACM(POJ1007-DNA Sorting)
- cocos2d-x拖动滑块控件CCControlSlider
- Light OJ 1037 - Agent 47（预处理状态压缩DP）
- easyui给select控件绑定change事件
- Bootstrap fileinput：文件上传插件的基础用法
- linux使用.net core 创建简单的MVC
- 十五、springcloud(一)注册中心Eureka
- 【工具相关】Web-Sublime Text2-安装 Package Control
- 在PC上像普通winform程序调试WINCE程序
- LeetCode: Reverse Integer 解题报告
- [转载]struts1小项目
- Syncthing源码解析 - 第三方库
- linux -- camera shot 拍照功能