C#操作SQLite数据库方法小结(创建,连接,插入,查询,删除等)

using System;using System.Data.SQLite;namespace SQLiteSamples{  class Program  {    //数据库连接    SQLiteConnection m_dbConnection;    static void Main(string[] args)    {      Program p = new Program();    }    public Program()    {      createNewDatabase();      connectToDatabase();      createTable();      fillTable();      printHighscores();    }    //创建一个空的数据库    void createNewDatabase()    {      SQLiteConnection.CreateFile("MyDatabase.sqlite");    }    //创建一个连接到指定数据库    void connectToDatabase()    {      m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");      m_dbConnection.Open();    }    //在指定数据库中创建一个table    void createTable()    {      string sql = "create table highscores (name varchar(20), score int)";      SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);      command.ExecuteNonQuery();    }    //插入一些数据    void fillTable()    {      string sql = "insert into highscores (name, score) values ('Me', 3000)";      SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);      command.ExecuteNonQuery();      sql = "insert into highscores (name, score) values ('Myself', 6000)";      command = new SQLiteCommand(sql, m_dbConnection);      command.ExecuteNonQuery();      sql = "insert into highscores (name, score) values ('And I', 9001)";      command = new SQLiteCommand(sql, m_dbConnection);      command.ExecuteNonQuery();    }    //使用sql查询语句，并显示结果    void printHighscores()    {      string sql = "select * from highscores order by score desc";      SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);      SQLiteDataReader reader = command.ExecuteReader();      while (reader.Read())        Console.WriteLine("Name: " + reader["name"] + "/tScore: " + reader["score"]);      Console.ReadLine();    }  }}

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{  /// <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 > 0)      {        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 > 0)      {        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>    /// 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)    {      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;    }    /// <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;    }    #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 == 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    /// <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;    }    /// <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>    /// 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>    /// 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 == 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;    }    /// <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 = 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;    }    /// <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 = 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: '{1}'.",           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++;      }    }    /// <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 = 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: '{1}'.",           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++;      }    }    /// <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 = 0, j = commandParameters.Length, k = 0; 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++;        }      }    }  }}