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> public static string 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> public static bool OpenOrCreateSqliteDateBase(string dbFileName, string dbPassword) { return OpenOrCreateSqliteDateBase(GenerateSqliteConnectionString(dbFileName, dbPassword)); } /// <summary> /// 尝试用sqliteConnectionString打开已存在的数据库，或者用sqliteConnectionString创建新的数据库 /// </summary> /// <param name="sqliteConnectionString"></param> /// <returns></returns> public static bool OpenOrCreateSqliteDateBase(string sqliteConnectionString) { string dbFileName = GetItemValueFromConnectionString(sqliteConnectionString, "Data Source"); string dbPassword = GetItemValueFromConnectionString(sqliteConnectionString, "Password"); if (string.IsNullOrEmpty(dbFileName)) { return false; } 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(); return true; } } catch (SQLiteException e) { return false; } catch (Exception e) { return false; } try { //尝试打开数据库 SQLiteConnection sqlconn = new SQLiteConnection(sqliteConnectionString); sqlconn.Open(); sqlconn.Close(); } catch (SQLiteException e) { return false; } return true; } public static bool 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; } public static bool DropTable(string sqliteConnectionString, string tableName) { return null != ExecuteNonQuery(sqliteConnectionString, string.Format("drop table if exists {0}", tableName)); } public static bool RenameTable(string sqliteConnectionString, string oldTableName, string newTableName) { return null != 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> public static object 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; } } }

 

sqlite helper

标签：public   option   with   app   .exe   bool   连接字符串   reserve   scala