• 首页 >编程 >ASP编程

    Sqlite 常用函数封装提高Codeeer的效率

    以下是频繁用到的Sqlite函数,内容格式相对固定,封装一下有助于提高开发效率^_^至少提高Codeeer的效率了

    以下是频繁用到的Sqlite函数,内容格式相对固定,封装一下有助于提高开发效率(^_^至少提高Codeeer的效率了)

    而且,我发现Sqlite中文资料比较少,起码相对其他找起来要复杂些,服务一下大众~
    我没有封装读取部分,因为数据库读取灵活性太大,封装起来难度也大,而且就算封装好了,也难以应付所有情况,还是建议根据实际情况设计代码逻辑。

    创建
    复制代码 代码如下:

    ///
    /// Creat New Sqlite File
    ///

    /// New Table Name
    /// Words list of the New Table
    /// IsSuccessful
    public static bool Creat(string DataSource, string NewTable, List NewWords)
    {
    try
    {
    //Creat Data File
    SQLiteConnection.CreateFile(DataSource);
    //Creat Table
    using (DbConnection conn = SQLiteFactory.Instance.CreateConnection())
    {
    //Connect
    conn.ConnectionString = "Data Source=" + DataSource;
    conn.Open();
    //Creat
    string Bazinga = "create table [" + NewTable + "] (";
    foreach (string Words in NewWords)
    {
    Bazinga += "[" + Words + "] BLOB COLLATE NOCASE,";
    }
    //Set Primary Key
    //The Top item from the "NewWords"
    Bazinga += @"PRIMARY KEY ([" + NewWords[0] + "]))";
    DbCommand cmd = conn.CreateCommand();
    cmd.Connection = conn;
    cmd.CommandText = Bazinga;
    cmd.ExecuteNonQuery();
    }
    return true;
    }
    catch (Exception E)
    {
    MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
    return false;
    }
    }

    删除
    复制代码 代码如下:

    ///
    /// Delete Date
    ///

    ///
    ///
    ///
    ///
    ///
    public static bool Delete(string DataSource, string TargetTable, string Word, string Value)
    {
    try
    {
    //Connect
    using (DbConnection conn = SQLiteFactory.Instance.CreateConnection())
    {
    conn.ConnectionString = "Data Source=" + DataSource;
    conn.Open();
    DbCommand cmd = conn.CreateCommand();
    cmd.Connection = conn;
    //Delete
    cmd.CommandText = "Delete From " + TargetTable + " where [" + Word + "] = '" + Value + "'";
    cmd.ExecuteNonQuery();
    }
    return true;
    }
    catch (Exception E)
    {
    MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
    return false;
    }
    }

    插入
    这里要说明下,因为存在多字段同时插入的情况(何止存在,很普遍- -。没见过谁的数据库像意大利面条一样)

    在这里设计了Insert结构用以储存字段和值的关系(曾考虑过用数组的办法实现,可是那玩意不太方便调用,瞅着挺抽象的,不太好用,如果有更好的建议,欢迎留言~)
    复制代码 代码如下:

    ///
    /// Use to format Insert column's value
    ///

    public struct InsertBag
    {
    public string ColumnName;
    public string Value;
    public InsertBag(string Column, string value)
    {
    ColumnName = Column;
    Value = value;
    }
    }

    以下为插入模块的主函数
    复制代码 代码如下:

    ///
    /// Insert Data
    ///

    ///
    ///
    /// struck of InsertBag
    ///
    public static bool Insert(string DataSource, string TargetTable, List InsertBags)
    {
    try
    {
    using (DbConnection conn = SQLiteFactory.Instance.CreateConnection())
    {
    //Connect Database
    conn.ConnectionString = "Data Source=" + DataSource;
    conn.Open();
    //Deal InsertBags
    StringBuilder ColumnS = new StringBuilder();
    StringBuilder ValueS = new StringBuilder();
    for (int i = 0; i {
    ColumnS.Append(InsertBags[i].ColumnName + ",");
    ValueS.Append("'" + InsertBags[i].Value + "',");
    }
    if (InsertBags.Count == 0)
    {
    throw new Exception("InsertBag 数据包为空,睁大你的狗眼……");
    }
    else
    {
    //Drop the last "," from the ColumnS and ValueS
    ColumnS = ColumnS.Remove(ColumnS.Length - 1, 1);
    ValueS = ValueS.Remove(ValueS.Length - 1, 1);
    }
    //Insert
    DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "insert into [" + TargetTable + "] (" + ColumnS.ToString() + ") values (" + ValueS.ToString() + ")";
    cmd.ExecuteNonQuery();
    return true;
    }
    }
    catch (Exception E)
    {
    MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
    return false;
    }
    }

    目测有点复杂呢,来个Demo,有必要说下,“W2”和“W44”是已经设计好的字段,而“TableTest”是已经添加好的表段
    复制代码 代码如下:

    List Lst = new List();
    Lst.Add(new Sqlite.InsertBag("W2", "222222222"));
    Lst.Add(new Sqlite.InsertBag("W44", "4444444"));
    Sqlite.Insert(@"D:\1.Sql3", "TableTest", Lst);

    表段获取
    复制代码 代码如下:

    ///
    /// Get Tables From Sqlite
    ///

    /// list of Tables
    public static List GetTables(string DataSource)
    {
    List ResultLst = new List();
    using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + DataSource))
    {
    conn.Open();
    using (SQLiteCommand tablesGet = new SQLiteCommand("SELECT name from sqlite_master where type='table'", conn))
    {
    using (SQLiteDataReader tables = tablesGet.ExecuteReader())
    {
    while (tables.Read())
    {
    try
    {
    ResultLst.Add(tables[0].ToString());
    }
    catch (Exception E)
    {
    MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    }
    }
    }
    }
    return ResultLst;
    }

    字段获取
    复制代码 代码如下:

    ///
    /// Get Words From Table->Sqlite
    ///

    /// Target Table
    /// list of Words
    public static List GetWords(string DataSource,string TargetTable)
    {
    List WordsLst = new List();
    using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + DataSource))
    {
    conn.Open();
    using (SQLiteCommand tablesGet = new SQLiteCommand(@"SELECT * FROM " + TargetTable, conn))
    {
    using (SQLiteDataReader Words = tablesGet.ExecuteReader())
    {
    try
    {
    for (int i = 0; i {
    WordsLst.Add(Words.GetName(i));
    }
    }
    catch (Exception E)
    {
    MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    }
    }
    }
    return WordsLst;
    }

    解释下,为啥代码中的注释基本都用英文写了,因为这段时间在学双拼- -。可是还不太熟悉,打字超慢,而且Code的时候容易打断思路,好在~英文不多,而且这些都看不懂的话你……你要向我解释一下你是怎么一路学到数据库的 0。

    相关文章