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

本文实例讲述了C#操作SQLite数据库方法。分享给大家供大家参考，具体如下：

SQLite介绍

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

SQLite是一个开源、免费的小型RDBMS(关系型数据库)，能独立运行、无服务器、零配置、支持事物，用C实现，内存占用较小，支持绝大数的SQL92标准。

SQLite数据库官方主页：http://www.sqlite.org/index.html

C#操作SQLite Database

C#下SQLite操作驱动dll下载：System.Data.SQLite

C#使用SQLite步骤：

（1）新建一个project
（2）添加SQLite操作驱动dll引用
（3）使用API操作SQLite DataBase

				?

									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();

									    }

									  }

									}

关于SQLite的connection string说明：http://www.connectionstrings.com/sqlite/

SQLite GUI客户端列表：http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

SQLite Administrator下载地址：http://download.orbmu2k.de/files/sqliteadmin.zip

操作SQLite Database的C#帮助类SQLite Helper

将一些常用的功能封装一下，封装成SQLite Helper类

				?

									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++;

									        }

									      }

									    }

									  }

									}

Codeproject上的一个封装：http://www.codeproject.com/Articles/746191/SQLite-Helper-Csharp

希望本文所述对大家C#程序设计有所帮助。

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

相关文章

热门资讯