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 =