sqlite helper

            //
            -------------------------------------------------------------------------- 

            //
            //
              Copyright (c) BUSHUOSX.  All rights reserved.  

            //
            //
              File: SqliteDbManager.cs 

            //
            //
              Version:1.0.0.0

            //
            //
              Datetime:

            //
            //
            ---------------------------------------------------------------------------
            
/*
            
 * 
 * 修改时间：20140829 211000
 * 
 * 
 * 
 * 
 
            */
            using
             System;

            using
             System.Configuration;

            using
             System.Data.SQLite;

            using
             System.Text;

            using
             System.Text.RegularExpressions;

            using
             System.IO;


            namespace
             BUSHUOSX.Helper
{

    
            public
            sealed
            class
             SqliteDbManager
    {
        
            ///
            <summary>
            ///
             获取连接字符串中某项的值
        
            ///
            </summary>
            ///
            <param name="connectionString">
            连接字符串
            </param>
            ///
            <param name="itemName">
            项目
            </param>
            ///
            <returns>
            项目值
            </returns>
            private
            static
            string GetItemValueFromConnectionString(string connectionString, string itemName)
        {
            if (!connectionString.EndsWith(";"))
                connectionString += ";";

            // s* 匹配0个或多个空白字符
            // .*? 匹配0个或多个除 "n" 之外的任何字符(?指尽可能少重复)string regexStr = itemName + @"s*=s*(?<key>.*?);";
            Regex r = new Regex(regexStr, RegexOptions.IgnoreCase);
            Match mc = r.Match(connectionString);
            return mc.Groups["key"].Value;
        }

        ///<summary>/// 创建System.Data.Sqlite数据库样式的连接字符串
        ///</summary>///<param name="dbName">数据库名</param>///<param name="dbFileExtName">数据库文件扩展名</param>///<param name="dbPassword">数据库密码</param>///<param name="dbDirectory">数据库目录路径</param>///<returns>dbName为空时，返回空</returns>publicstaticstring GenerateSqliteConnectionString(string dbFileName, string dbPassword = "", string dbDirectory = "")
        {
            if (string.IsNullOrEmpty(dbFileName))
            {
                return"";
            }

            StringBuilder sb = new StringBuilder("Data Source=");
            if (!string.IsNullOrEmpty(dbDirectory))
            {
                sb.Append(dbDirectory);
                if (!dbDirectory.EndsWith("\"))
                    sb.Append(‘\‘);
            }

            sb.Append(dbFileName).Append(‘;‘);

            if (!string.IsNullOrEmpty(dbPassword))
                sb.AppendFormat("Password={0};", dbPassword);

            return sb.ToString();
        }

        ///<summary>/// 尝试用sqliteConnectionString打开已存在的数据库，或者用sqliteConnectionString创建新的数据库
        ///</summary>///<param name="dbFileName"></param>///<param name="dbPassword"></param>///<returns></returns>publicstaticbool OpenOrCreateSqliteDateBase(string dbFileName, string dbPassword)
        {
            return OpenOrCreateSqliteDateBase(GenerateSqliteConnectionString(dbFileName, dbPassword));
        }

        ///<summary>/// 尝试用sqliteConnectionString打开已存在的数据库，或者用sqliteConnectionString创建新的数据库
        ///</summary>///<param name="sqliteConnectionString"></param>///<returns></returns>publicstaticbool OpenOrCreateSqliteDateBase(string sqliteConnectionString)
        {
            string dbFileName = GetItemValueFromConnectionString(sqliteConnectionString, "Data Source");
            string dbPassword = GetItemValueFromConnectionString(sqliteConnectionString, "Password");
            if (string.IsNullOrEmpty(dbFileName))
            {
                returnfalse;
            }

            try
            {
                if (!File.Exists(dbFileName))
                {
                    //创建目录var parent = Directory.GetParent(dbFileName);
                    if (null != parent && !parent.Exists)
                    {
                        Directory.CreateDirectory(parent.FullName);
                    }
                    //var fs = File.Create(dbName);
                    //fs.Close();

                    //创建数据库
                    SQLiteConnection sqlconn = new SQLiteConnection("Data Source=" + dbFileName);
                    sqlconn.Open();
                    //设置密码if (!string.IsNullOrEmpty(dbPassword))
                        sqlconn.ChangePassword(dbPassword);
                    sqlconn.Close();

                    returntrue;
                }
            }
            catch (SQLiteException e)
            {
                returnfalse;
            }
            catch (Exception e)
            {
                returnfalse;
            }

            try
            {
                //尝试打开数据库
                SQLiteConnection sqlconn = new SQLiteConnection(sqliteConnectionString);
                sqlconn.Open();
                sqlconn.Close();
            }
            catch (SQLiteException e)
            {
                returnfalse;
            }

            returntrue;
        }

        publicstaticbool TableExists(string sqliteConnectionString, string tableName)
        {
            bool result = false;
            SQLiteConnection sqlconn = new SQLiteConnection(sqliteConnectionString);
            try
            {
                SQLiteCommand scmd = new SQLiteCommand(sqlconn);
                scmd.CommandText = string.Format(@"select count(*) from sqlite_master where type=‘table‘ and name=‘{0}‘", tableName);

                //打开数据库                sqlconn.Open();

                var v = scmd.ExecuteScalar();
                if (1 == Convert.ToInt32(v))
                {
                    result = true;
                }
            }
            catch (Exception)
            {
                //throw;            }
            sqlconn.Close();
            return result;
        }

        publicstaticbool DropTable(string sqliteConnectionString, string tableName)
        {
            returnnull != ExecuteNonQuery(sqliteConnectionString, string.Format("drop table if exists {0}", tableName));
        }

        publicstaticbool RenameTable(string sqliteConnectionString, string oldTableName, string newTableName)
        {
            returnnull != ExecuteNonQuery(sqliteConnectionString, string.Format("alter table {0} rename to {1}", oldTableName, newTableName));
        }

        ///<summary>/// 执行sql命令
        ///</summary>///<param name="sqliteConnectionString"></param>///<param name="sql"></param>///<returns></returns>publicstaticobject ExecuteNonQuery(string sqliteConnectionString, string sql)
        {
            object result = null;

            SQLiteConnection sqlconn = new SQLiteConnection(sqliteConnectionString);
            try
            {
                //打开数据库                sqlconn.Open();

                SQLiteCommand scmd = new SQLiteCommand(sqlconn);
                scmd.CommandText = sql;
                result = scmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                //throw;            }
            sqlconn.Close();

            return result;
        }
    }
}