C#操作SQLite数据库

SQLite介绍

SQLite is a software library that implements aself-contained,serverless,zero-configuration,transactionalSQL 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)        {