Sqlite 常用函数封装：修改密码，创建数据库、获取表段、字段，取值、插入、删除数据、执行Sqlite指令

原文地址：http://blog.csdn.net/codeeer/article/details/8440751

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

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

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

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

修改密码

[csharp] view plain copy print ?

1. /// <summary>   
2. /// Change Password   
3. /// </summary>   
4. /// <param name="DataSource"></param>   
5. /// <param name="OldPassword"></param>   
6. /// <param name="NewPassword"></param>   
7. /// <returns></returns>   
8. public static bool PasswordChange(string DataSource, string OldPassword, string NewPassword)  
9. {  
10.     try  
11.     {  
12.         using (SQLiteConnection conn = new SQLiteConnection())  
13.         {  
14.             SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();  
15.             SCS.DataSource = DataSource;  
16.             SCS.Password = OldPassword;  
17.             conn.ConnectionString = SCS.ToString();  
18.   
19.             conn.Open();  
20.   
21.             //Change Password   
22.             conn.ChangePassword(NewPassword);  
23.         }  
24.         return true;  
25.     }  
26.     catch (Exception E)  
27.     {  
28.         MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
29.         return false;  
30.     }  
31. }  

/// <summary>
/// Change Password
/// </summary>
/// <param name="DataSource"></param>
/// <param name="OldPassword"></param>
/// <param name="NewPassword"></param>
/// <returns></returns>
public static bool PasswordChange(string DataSource, string OldPassword, string NewPassword)
{
    try
    {
        using (SQLiteConnection conn = new SQLiteConnection())
        {
            SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
            SCS.DataSource = DataSource;
            SCS.Password = OldPassword;
            conn.ConnectionString = SCS.ToString();

            conn.Open();

            //Change Password
            conn.ChangePassword(NewPassword);
        }
        return true;
    }
    catch (Exception E)
    {
        MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return false;
    }
}

创建数据库

[csharp] view plain copy print ?

1. /// <summary>   
2. /// Creat New Sqlite File   
3. /// </summary>   
4. /// <param name="NewTable">New Table Name</param>   
5. /// <param name="NewWords">Words list of the New Table</param>   
6. /// <param name="CreatNew">Creat or Add to the Database(Using to make Multi Tables)</param>   
7. /// <returns></returns>   
8. public static bool Creat(string DataSource, string NewTable, List<string> NewWords, string PassWord = null, bool CreatNew = true)  
9. {  
10.     try  
11.     {  
12.         if (CreatNew)  
13.         {  
14.             //Creat Data File   
15.             SQLiteConnection.CreateFile(DataSource);  
16.         }  
17.   
18.         //Creat Table   
19.         using (SQLiteConnection conn = new SQLiteConnection())  
20.         {  
21.             SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();  
22.             SCS.DataSource = DataSource;  
23.             SCS.Password = PassWord;  
24.             conn.ConnectionString = SCS.ToString();  
25.   
26.             conn.Open();  
27.   
28.             //Creat   
29.             string Bazinga = "create table [" + NewTable + "] (";  
30.             foreach (string Words in NewWords)  
31.             {  
32.                 Bazinga += "[" + Words + "] TEXT COLLATE NOCASE,";  
33.             }  
34.   
35.             //Set Primary Key   
36.             //The Top item from the "NewWords"   
37.             Bazinga += @"PRIMARY KEY ([" + NewWords[0] + "]))";  
38.             DbCommand cmd = conn.CreateCommand();  
39.             cmd.Connection = conn;  
40.             cmd.CommandText = Bazinga;  
41.             cmd.ExecuteNonQuery();  
42.         }  
43.         return true;  
44.     }  
45.     catch (Exception E)  
46.     {  
47.         MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
48.         return false;  
49.     }  
50. }  

/// <summary>
/// Creat New Sqlite File
/// </summary>
/// <param name="NewTable">New Table Name</param>
/// <param name="NewWords">Words list of the New Table</param>
/// <param name="CreatNew">Creat or Add to the Database(Using to make Multi Tables)</param>
/// <returns></returns>
public static bool Creat(string DataSource, string NewTable, List<string> NewWords, string PassWord = null, bool CreatNew = true)
{
    try
    {
        if (CreatNew)
        {
            //Creat Data File
            SQLiteConnection.CreateFile(DataSource);
        }

        //Creat Table
        using (SQLiteConnection conn = new SQLiteConnection())
        {
            SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
            SCS.DataSource = DataSource;
            SCS.Password = PassWord;
            conn.ConnectionString = SCS.ToString();

            conn.Open();

            //Creat
            string Bazinga = "create table [" + NewTable + "] (";
            foreach (string Words in NewWords)
            {
                Bazinga += "[" + Words + "] TEXT 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;
    }
}

获取表段

[csharp] view plain copy print ?

1. /// <summary>   
2. /// Get Tables From Sqlite   
3. /// </summary>   
4. /// <returns>list of Tables</returns>   
5. public static List<string> GetTables(string DataSource, string PassWord = null)  
6. {  
7.     List<string> ResultLst = new List<string>();  
8.   
9.     try  
10.     {  
11.         using (SQLiteConnection conn = new SQLiteConnection())  
12.         {  
13.             SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();  
14.             SCS.DataSource = DataSource;  
15.             SCS.Password = PassWord;  
16.             conn.ConnectionString = SCS.ToString();  
17.   
18.             conn.Open();  
19.             using (SQLiteCommand tablesGet = new SQLiteCommand("SELECT name from sqlite_master where type='table'", conn))  
20.             {  
21.                 using (SQLiteDataReader tables = tablesGet.ExecuteReader())  
22.                 {  
23.                     while (tables.Read())  
24.                     {  
25.                         try  
26.                         {  
27.                             ResultLst.Add(tables[0].ToString());  
28.                         }  
29.                         catch (Exception E)  
30.                         {  
31.                             MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
32.                         }  
33.                     }  
34.                 }  
35.             }  
36.         }  
37.     }  
38.     catch (Exception E)  
39.     {  
40.         MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
41.     }  
42.   
43.     return ResultLst;  
44. }  

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

    try
    {
        using (SQLiteConnection conn = new SQLiteConnection())
        {
            SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
            SCS.DataSource = DataSource;
            SCS.Password = PassWord;
            conn.ConnectionString = SCS.ToString();

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

    return ResultLst;
}

获取字段

[csharp] view plain copy print ?

1. /// <summary>   
2. /// Get Words From Table->Sqlite   
3. /// </summary>   
4. /// <param name="TargetTable">Target Table</param>   
5. /// <returns>list of Words</returns>   
6. public static List<string> GetWords(string DataSource, string TargetTable, string PassWord = null)  
7. {  
8.     List<string> WordsLst = new List<string>();  
9.   
10.     using (SQLiteConnection conn = new SQLiteConnection())  
11.     {  
12.         SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();  
13.         SCS.DataSource = DataSource;  
14.         SCS.Password = PassWord;  
15.         conn.ConnectionString = SCS.ToString();  
16.   
17.         conn.Open();  
18.         using (SQLiteCommand tablesGet = new SQLiteCommand(@"SELECT * FROM " + TargetTable, conn))  
19.         {  
20.             using (SQLiteDataReader Words = tablesGet.ExecuteReader())  
21.             {  
22.                 try  
23.                 {  
24.                     for (int i = 0; i < Words.FieldCount; i++)  
25.                     {  
26.                         WordsLst.Add(Words.GetName(i));  
27.                     }  
28.                 }  
29.                 catch (Exception E)  
30.                 {  
31.                     MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
32.                 }  
33.             }  
34.         }  
35.     }  
36.   
37.     return WordsLst;  
38. }  

/// <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, string PassWord = null)
{
    List<string> WordsLst = new List<string>();

    using (SQLiteConnection conn = new SQLiteConnection())
    {
        SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
        SCS.DataSource = DataSource;
        SCS.Password = PassWord;
        conn.ConnectionString = SCS.ToString();

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

取值

[csharp] view plain copy print ?

1. /// <summary>   
2. /// Get Values From Sqlite   
3. /// </summary>   
4. /// <returns>list of Values</returns>   
5. public static List<string> GetValues(string DataSource, string Sql, string GetColumu, string PassWord = null)  
6. {  
7.     List<string> ResultLst = new List<string>();  
8.   
9.     using (SQLiteConnection conn = new SQLiteConnection())  
10.     {  
11.         SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();  
12.         SCS.DataSource = DataSource;  
13.         SCS.Password = PassWord;  
14.         conn.ConnectionString = SCS.ToString();  
15.   
16.         conn.Open();  
17.         using (SQLiteCommand cmd = new SQLiteCommand(Sql, conn))  
18.         {  
19.             using (SQLiteDataReader dr = cmd.ExecuteReader())  
20.             {  
21.                 while (dr.Read())  
22.                 {  
23.                     try  
24.                     {  
25.                         ResultLst.Add(dr[GetColumu].ToString());  
26.                     }  
27.                     catch (Exception E)  
28.                     {  
29.                         MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
30.                     }  
31.                 }  
32.             }  
33.         }  
34.     }  
35.   
36.     return ResultLst;  
37. }  

/// <summary>
/// Get Values From Sqlite
/// </summary>
/// <returns>list of Values</returns>
public static List<string> GetValues(string DataSource, string Sql, string GetColumu, string PassWord = null)
{
    List<string> ResultLst = new List<string>();

    using (SQLiteConnection conn = new SQLiteConnection())
    {
        SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
        SCS.DataSource = DataSource;
        SCS.Password = PassWord;
        conn.ConnectionString = SCS.ToString();

        conn.Open();
        using (SQLiteCommand cmd = new SQLiteCommand(Sql, conn))
        {
            using (SQLiteDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    try
                    {
                        ResultLst.Add(dr[GetColumu].ToString());
                    }
                    catch (Exception E)
                    {
                        MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
        }
    }

    return ResultLst;
}

插入数据

[csharp] view plain copy print ?

1. /// <summary>   
2. /// Insert Data   
3. /// </summary>   
4. /// <param name="DataSource"></param>   
5. /// <param name="TargetTable"></param>   
6. /// <returns></returns>   
7. public static bool Insert(string DataSource, string TargetTable, string ColumnS, string ValueS, string PassWord = null)  
8. {  
9.     try  
10.     {  
11.         using (SQLiteConnection conn = new SQLiteConnection())  
12.         {  
13.             SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();  
14.             SCS.DataSource = DataSource;  
15.             SCS.Password = PassWord;  
16.             conn.ConnectionString = SCS.ToString();  
17.   
18.             conn.Open();  
19.   
20.             //Insert   
21.             DbCommand cmd = conn.CreateCommand();  
22.             cmd.CommandText = "insert into [" + TargetTable + "] (" + ColumnS + ") values (" + ValueS + ")";  
23.             cmd.ExecuteNonQuery();  
24.   
25.             return true;  
26.         }  
27.     }  
28.     catch (Exception E)  
29.     {  
30.         MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
31.         return false;  
32.     }  
33. }  

/// <summary>
/// Insert Data
/// </summary>
/// <param name="DataSource"></param>
/// <param name="TargetTable"></param>
/// <returns></returns>
public static bool Insert(string DataSource, string TargetTable, string ColumnS, string ValueS, string PassWord = null)
{
    try
    {
        using (SQLiteConnection conn = new SQLiteConnection())
        {
            SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
            SCS.DataSource = DataSource;
            SCS.Password = PassWord;
            conn.ConnectionString = SCS.ToString();

            conn.Open();

            //Insert
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandText = "insert into [" + TargetTable + "] (" + ColumnS + ") values (" + ValueS + ")";
            cmd.ExecuteNonQuery();

            return true;
        }
    }
    catch (Exception E)
    {
        MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return false;
    }
}

删除数据

[csharp] view plain copy print ?

1. /// <summary>   
2. /// Delete Date   
3. /// </summary>   
4. /// <param name="DataSource"></param>   
5. /// <param name="TargetTable"></param>   
6. /// <param name="Word"></param>   
7. /// <param name="Value"></param>   
8. /// <returns></returns>   
9. public static bool Delete(string DataSource, string TargetTable, string Word, string Value, string PassWord = null)  
10. {  
11.     try  
12.     {  
13.         //Connect   
14.         using (SQLiteConnection conn = new SQLiteConnection())  
15.         {  
16.             SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();  
17.             SCS.DataSource = DataSource;  
18.             SCS.Password = PassWord;  
19.             conn.ConnectionString = SCS.ToString();  
20.   
21.             conn.Open();  
22.   
23.             DbCommand cmd = conn.CreateCommand();  
24.             cmd.Connection = conn;  
25.             //Delete   
26.             cmd.CommandText = "Delete From " + TargetTable + " where [" + Word + "] = '" + Value + "'";  
27.             cmd.ExecuteNonQuery();  
28.         }  
29.   
30.         return true;  
31.     }  
32.     catch (Exception E)  
33.     {  
34.         MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
35.         return false;  
36.     }  
37. }  

/// <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, string PassWord = null)
{
    try
    {
        //Connect
        using (SQLiteConnection conn = new SQLiteConnection())
        {
            SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
            SCS.DataSource = DataSource;
            SCS.Password = PassWord;
            conn.ConnectionString = SCS.ToString();

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

执行Sqlite指令

[csharp] view plain copy print ?

1. /// <summary>   
2. /// SQLiteCommand   
3. /// </summary>   
4. /// <param name="DataSource"></param>   
5. /// <param name="Sql"></param>   
6. /// <returns></returns>   
7. public static bool SqlCommand(string DataSource, string Sql, string PassWord = null)  
8. {  
9.     try  
10.     {  
11.         using (SQLiteConnection conn = new SQLiteConnection())  
12.         {  
13.             SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();  
14.             SCS.DataSource = DataSource;  
15.             SCS.Password = PassWord;  
16.             conn.ConnectionString = SCS.ToString();  
17.   
18.             conn.Open();  
19.             using (SQLiteCommand cmd_Re = new SQLiteCommand(Sql, conn))  
20.             {  
21.                 cmd_Re.ExecuteNonQuery();  
22.             }  
23.         }  
24.         return true;  
25.     }  
26.     catch (Exception E)  
27.     {  
28.         MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);  
29.         return false;  
30.     }  
31. }  

/// <summary>
/// SQLiteCommand
/// </summary>
/// <param name="DataSource"></param>
/// <param name="Sql"></param>
/// <returns></returns>
public static bool SqlCommand(string DataSource, string Sql, string PassWord = null)
{
    try
    {
        using (SQLiteConnection conn = new SQLiteConnection())
        {
            SQLiteConnectionStringBuilder SCS = new SQLiteConnectionStringBuilder();
            SCS.DataSource = DataSource;
            SCS.Password = PassWord;
            conn.ConnectionString = SCS.ToString();

            conn.Open();
            using (SQLiteCommand cmd_Re = new SQLiteCommand(Sql, conn))
            {
                cmd_Re.ExecuteNonQuery();
            }
        }
        return true;
    }
    catch (Exception E)
    {
        MessageBox.Show(E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return false;
    }
}