C# SQlite 操作小结

 最近项目需求用C#保存一些数据，如此先总结一下。需要下载Sqlite 库 SourceForge 链接网址http://sourceforge.net/projects/sqlite-dotnet2/或到官方网http://www.sqlite.org/download.html下载都可以，下载之后安装。在C#项目中添加引用 引入安装目录bin中的System.Data.SQLite.dll。添加命名空间using System.Data.SQLite;便可以在你的项目中

对扩平台的微型数据库SQlite 进行使用了，

主要注意一点是：

数据库若未创建则使用：

 SQLiteConnection.CreateFile(databaseName);

数据库已经创建，并要进行访问： 

SQLiteConnection m_conn = new SQLiteConnection("DataSource="+m_dbName+";Version=3;New=False;Compress=True;");

下面是项目中封装的操作数据库代码，使用时可稍微修改便可在项目中使用。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
namespace Toolbar
{
      public class  CSPDatabase
    {
        protected string m_dbName;
        protected string m_tablename;
        protected string m_password;

        public CSPDatabase(string dbName) 
        { 
            m_dbName    = dbName;
            m_tablename = "MhtInfo";
            m_password  = "";
        }
        //Create DataBase
        public virtual void Init() { }
        public virtual void CreateDataBase() { }
        public virtual void OpenDataBase() { }
        public virtual void SetPassWord(string password) { }
        //Connect DataBase
        public virtual  void ConnectDataBase() { }
        //Create Table
        public virtual  void CreateTable(string tableName) { }
        //Insert Data
        public virtual  void Insert(string mhtlocation) { }
    }
}

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;
namespace Toolbar
{
    class SqliteDatabase : CSPDatabase
    {
        private  SQLiteConnection m_conn= null;
        private  SQLiteCommand m_cmd=null;
        public SqliteDatabase(string dbName):base(dbName)
        {
      
        }
        public override void Init()
        {
            if(m_conn == null)
                m_conn = new SQLiteConnection();
            m_cmd = new SQLiteCommand();
            m_cmd.Connection = m_conn;
        }
        public override void CreateDataBase()
        {
            //Create Database
            try
            {
                SQLiteConnection.CreateFile(m_dbName);
                Init();
                ConnectDataBase();
            }
            catch (System.Exception e)
            {
                MessageBox.Show("Create DataBase Failed!");
            }
           
        }
        public override void OpenDataBase()
        {
            m_conn = new SQLiteConnection("Data Source="+m_dbName+";Version=3;New=False;Compress=True;");
            Init();
            ConnectDataBase();
        }
        public override void SetPassWord(string password) 
        {
            m_password = password;
        }
        public override void ConnectDataBase()
        {
           //Connect to DataBase
           try
           {
               SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
               connstr.DataSource = m_dbName;
               if(m_password != "")
                    connstr.Password = m_password;
               m_conn.ConnectionString = connstr.ToString();
               
           }
           catch (System.Exception e)
           {
               MessageBox.Show("Fail to Connect to the database");
           }

        }
        //Create Table
        public override void CreateTable(string tableName)
        {
            try
            {
                m_tablename = tableName;
                m_conn.Open();
                string sql = "CREATE TABLE " + tableName + "(mhtlocation varchar(20))";
                m_cmd.CommandText = sql;
                m_cmd.ExecuteNonQuery();
                m_conn.Close();
            }
            catch (System.Exception e)
            {
                MessageBox.Show("Create Table Failed!");
            }

        }
        public override void Insert(string mhtlocation)
        {
           try
           {
               //Insert Data
               m_conn.Open();
               string sql = "insert into [" + m_tablename + "] values('" + mhtlocation + "')";
               m_cmd.CommandText = sql;
               m_cmd.ExecuteNonQuery();
               m_conn.Close();
           }
           catch (System.Exception e)
           {
               MessageBox.Show(e.ToString());
           }

        }
    }
}