您的位置: 首页  >  IT文章  >  Sqlite 惯用函数封装：创建，删除，插入，表段、字段获取

Sqlite 惯用函数封装：创建，删除，插入，表段、字段获取

分类: IT文章 • 2022-05-07 15:41:13
Sqlite 常用函数封装：创建，删除，插入，表段、字段获取

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

而且，我发现Sqlite中文资料比较少，起码相对其他找起来要复杂些，服务一下大众~

我没有封装读取部分，因为数据库读取灵活性太大，封装起来难度也大，而且就算封装好了，也难以应付所有情况，还是建议根据实际情况设计代码逻辑。

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


创建

/// <summary>
/// Creat New Sqlite File
/// </summary>
/// <param name="NewTable">New Table Name</param>
/// <param name="NewWords">Words list of the New Table</param>
/// <returns>IsSuccessful</returns>
public static bool Creat(string DataSource, string NewTable, List<string> 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;
    }
}

删除

/// <summary>
/// Delete Date
/// </summary>
/// <param name="DataSource"></param>
/// <param name="TargetTable"></param>
/// <param name="Word"></param>
/// <param name="Value"></param>
/// <returns></returns>
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结构用以储存字段和值的关系（曾考虑过用数组的办法实现，可是那玩意不太方便调用，瞅着挺抽象的，不太好用，如果有更好的建议，欢迎留言~）

/// <summary>
/// Use to format Insert column's value
/// </summary>
public struct InsertBag
{
    public string ColumnName;
    public string Value;

    public InsertBag(string Column, string value)
    {
        ColumnName = Column;
        Value = value;
    }
}
以下为插入模块的主函数： 

/// <summary>
/// Insert Data
/// </summary>
/// <param name="DataSource"></param>
/// <param name="TargetTable"></param>
/// <param name="InsertBags">struck of InsertBag</param>
/// <returns></returns>
public static bool Insert(string DataSource, string TargetTable, List<InsertBag> 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 < InsertBags.Count; 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<Sqlite.InsertBag> Lst = new List<Sqlite.InsertBag>();
Lst.Add(new Sqlite.InsertBag("W2", "222222222"));
Lst.Add(new Sqlite.InsertBag("W44", "4444444"));

Sqlite.Insert(@"D:\1.Sql3", "TableTest", Lst);


表段获取

/// <summary>
/// Get Tables From Sqlite
/// </summary>
/// <returns>list of Tables</returns>
public static List<string> GetTables(string DataSource)
{
    List<string> ResultLst = new List<string>();

    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;
}

字段获取

/// <summary>
/// Get Words From Table->Sqlite
/// </summary>
/// <param name="TargetTable">Target Table</param>
/// <returns>list of Words</returns>
public static List<string> GetWords(string DataSource,string TargetTable)
{
    List<string> WordsLst = new List<string>();

    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 < Words.FieldCount; i++)
                    {
                        WordsLst.Add(Words.GetName(i));
                    }
                }
                catch (Exception E)
                {
                    MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }
    }

    return WordsLst;
}

相关推荐