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)        {