Sqlite 常用函数封装：修改密码，创建数据库、获取表段、字段，

原文地址：http://www.52php.cn/article/p-unrjqlwc-gu.html

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

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

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

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

修改密码

1. ///<summary>
2. ///ChangePassword
3. ///</summary>
4. ///<paramname="DataSource"></param>
5. ///<paramname="OldPassword"></param>
6. ///<paramname="NewPassword"></param>
7. ///<returns></returns>
8. publicstaticboolPasswordChange(stringDataSource,stringOldPassword,stringNewPassword)
9. {
10. try
11. {
12. using(SQLiteConnectionconn=newSQLiteConnection())
13. {
14. SQLiteConnectionStringBuilderSCS=newSQLiteConnectionStringBuilder();
15. SCS.DataSource=DataSource;
16. SCS.Password=OldPassword;
17. conn.ConnectionString=SCS.ToString();
19. conn.Open();
21. //ChangePassword
22. conn.ChangePassword(NewPassword);
23. }
24. returntrue;
25. }
26. catch(ExceptionE)
27. {
28. MessageBox.Show(E.Message,"提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
29. returnfalse;
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,"提示",MessageBoxIcon.Information);
        return false;
    }
}

创建数据库

1. ///<summary>
2. ///CreatNewSqliteFile
3. ///</summary>
4. ///<paramname="NewTable">NewTableName</param>
5. ///<paramname="NewWords">WordslistoftheNewTable</param>
6. ///<paramname="CreatNew">CreatorAddtotheDatabase(UsingtomakeMultiTables)</param>
7. ///<returns></returns>
8. publicstaticboolCreat(stringDataSource,stringNewTable,List<string>NewWords,stringPassWord=null,boolCreatNew=true)
9. {
10. try
11. {
12. if(CreatNew)
13. {
14. //CreatDataFile
15. SQLiteConnection.CreateFile(DataSource);
16. }
18. //CreatTable
19. using(SQLiteConnectionconn=newSQLiteConnection())
20. {
21. SQLiteConnectionStringBuilderSCS=newSQLiteConnectionStringBuilder();
22. SCS.DataSource=DataSource;
23. SCS.Password=PassWord;
24. conn.ConnectionString=SCS.ToString();
26. conn.Open();
28. //Creat
29. stringBazinga="createtable["+NewTable+"](";
30. foreach(stringWordsinNewWords)
31. {
32. Bazinga+="["+Words+"]TEXTCOLLATENOCASE,";
33. }
35. //SetPrimaryKey
36. //TheTopitemfromthe"NewWords"
37. Bazinga+=@"PRIMARYKEY(["+NewWords[0]+"]))";
38. DbCommandcmd=conn.CreateCommand();
39. cmd.Connection=conn;
40. cmd.CommandText=Bazinga;
41. cmd.ExecuteNonQuery();
42. }
43. returntrue;
44. }
45. catch(ExceptionE)
46. {
47. MessageBox.Show(E.Message,MessageBoxIcon.Information);
48. returnfalse;
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,MessageBoxIcon.Information);
        return false;
    }
}

获取表段

1. ///<summary>
2. ///GetTablesFromSqlite
3. ///</summary>
4. ///<returns>listofTables</returns>
5. publicstaticList<string>GetTables(stringDataSource,stringPassWord=null)
6. {
7. List<string>ResultLst=newList<string>();
9. try
10. {
11. using(SQLiteConnectionconn=newSQLiteConnection())
12. {
13. SQLiteConnectionStringBuilderSCS=newSQLiteConnectionStringBuilder();
14. SCS.DataSource=DataSource;
15. SCS.Password=PassWord;
16. conn.ConnectionString=SCS.ToString();
18. conn.Open();
19. using(SQLiteCommandtablesGet=newSQLiteCommand("SELECTnamefromsqlite_masterwheretype='table'",conn))
20. {
21. using(SQLiteDataReadertables=tablesGet.ExecuteReader())
22. {
23. while(tables.Read())
24. {
25. try
26. {
27. ResultLst.Add(tables[0].ToString());
28. }
29. catch(ExceptionE)
30. {
31. MessageBox.Show(E.Message,MessageBoxIcon.Information);
32. }
33. }
34. }
35. }
36. }
37. }
38. catch(ExceptionE)
39. {
40. MessageBox.Show(E.Message,MessageBoxIcon.Information);
41. }
43. returnResultLst;
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,MessageBoxIcon.Information);
                        }
                    }
                }
            }
        }
    }
    catch (Exception E)
    {
        MessageBox.Show(E.Message,MessageBoxIcon.Information);
    }

    return ResultLst;
}

获取字段

1. ///<summary>
2. ///GetWordsFromTable->Sqlite
3. ///</summary>
4. ///<paramname="TargetTable">TargetTable</param>
5. ///<returns>listofWords</returns>
6. publicstaticList<string>GetWords(stringDataSource,stringTargetTable,stringPassWord=null)
7. {
8. List<string>WordsLst=newList<string>();
10. using(SQLiteConnectionconn=newSQLiteConnection())
11. {
12. SQLiteConnectionStringBuilderSCS=newSQLiteConnectionStringBuilder();
13. SCS.DataSource=DataSource;
14. SCS.Password=PassWord;
15. conn.ConnectionString=SCS.ToString();
17. conn.Open();
18. using(SQLiteCommandtablesGet=newSQLiteCommand(@"SELECT*FROM"+TargetTable,conn))
19. {
20. using(SQLiteDataReaderWords=tablesGet.ExecuteReader())
21. {
22. try
23. {
24. for(inti=0;i<Words.FieldCount;i++)
25. {
26. WordsLst.Add(Words.GetName(i));
27. }
28. }
29. catch(ExceptionE)
30. {
31. MessageBox.Show(E.Message,MessageBoxIcon.Information);
32. }
33. }
34. }
35. }
37. returnWordsLst;
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,MessageBoxIcon.Information);
                }
            }
        }
    }

    return WordsLst;
}

取值

1. ///<summary>
2. ///GetValuesFromSqlite
3. ///</summary>
4. ///<returns>listofValues</returns>
5. publicstaticList<string>GetValues(stringDataSource,stringSql,stringGetColumu,stringPassWord=null)
6. {
7. List<string>ResultLst=newList<string>();
9. using(SQLiteConnectionconn=newSQLiteConnection())
10. {
11. SQLiteConnectionStringBuilderSCS=newSQLiteConnectionStringBuilder();
12. SCS.DataSource=DataSource;
13. SCS.Password=PassWord;
14. conn.ConnectionString=SCS.ToString();
16. conn.Open();
17. using(SQLiteCommandcmd=newSQLiteCommand(Sql,conn))
18. {
19. using(SQLiteDataReaderdr=cmd.ExecuteReader())
20. {
21. while(dr.Read())
22. {
23. try
24. {
25. ResultLst.Add(dr[GetColumu].ToString());
26. }
27. catch(ExceptionE)
28. {
29. MessageBox.Show(E.Message,MessageBoxIcon.Information);
30. }
31. }
32. }
33. }
34. }
36. returnResultLst;
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,MessageBoxIcon.Information);
                    }
                }
            }
        }
    }

    return ResultLst;
}

插入数据

1. ///<summary>
2. ///InsertData
3. ///</summary>
4. ///<paramname="DataSource"></param>
5. ///<paramname="TargetTable"></param>
6. ///<returns></returns>
7. publicstaticboolInsert(stringDataSource,stringColumnS,stringValueS,stringPassWord=null)
8. {
9. try
10. {
11. using(SQLiteConnectionconn=newSQLiteConnection())
12. {
13. SQLiteConnectionStringBuilderSCS=newSQLiteConnectionStringBuilder();
14. SCS.DataSource=DataSource;
15. SCS.Password=PassWord;
16. conn.ConnectionString=SCS.ToString();
18. conn.Open();
20. //Insert
21. DbCommandcmd=conn.CreateCommand();
22. cmd.CommandText="insertinto["+TargetTable+"]("+ColumnS+")values("+ValueS+")";
23. cmd.ExecuteNonQuery();
25. returntrue;
26. }
27. }
28. catch(ExceptionE)
29. {
30. MessageBox.Show(E.Message,MessageBoxIcon.Information);
31. returnfalse;
32. }
33. }

/// <summary>
/// Insert Data
/// </summary>
/// <param name="DataSource"></param>
/// <param name="TargetTable"></param>
/// <returns></returns>
public static bool Insert(string DataSource,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,MessageBoxIcon.Information);
        return false;
    }
}

删除数据

1. ///<summary>
2. ///DeleteDate
3. ///</summary>
4. ///<paramname="DataSource"></param>
5. ///<paramname="TargetTable"></param>
6. ///<paramname="Word"></param>
7. ///<paramname="Value"></param>
8. ///<returns></returns>
9. publicstaticboolDelete(stringDataSource,stringWord,stringValue,stringPassWord=null)
10. {
11. try
12. {
13. //Connect
14. using(SQLiteConnectionconn=newSQLiteConnection())
15. {
16. SQLiteConnectionStringBuilderSCS=newSQLiteConnectionStringBuilder();
17. SCS.DataSource=DataSource;
18. SCS.Password=PassWord;
19. conn.ConnectionString=SCS.ToString();
21. conn.Open();
23. DbCommandcmd=conn.CreateCommand();
24. cmd.Connection=conn;
25. //Delete
26. cmd.CommandText="DeleteFrom"+TargetTable+"where["+Word+"]='"+Value+"'";
27. cmd.ExecuteNonQuery();
28. }
30. returntrue;
31. }
32. catch(ExceptionE)
33. {
34. MessageBox.Show(E.Message,MessageBoxIcon.Information);
35. returnfalse;
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 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,MessageBoxIcon.Information);
        return false;
    }
}

执行Sqlite指令

1. ///<summary>
2. ///SQLiteCommand
3. ///</summary>
4. ///<paramname="DataSource"></param>
5. ///<paramname="Sql"></param>
6. ///<returns></returns>
7. publicstaticboolSqlCommand(stringDataSource,stringPassWord=null)
8. {
9. try
10. {
11. using(SQLiteConnectionconn=newSQLiteConnection())
12. {
13. SQLiteConnectionStringBuilderSCS=newSQLiteConnectionStringBuilder();
14. SCS.DataSource=DataSource;
15. SCS.Password=PassWord;
16. conn.ConnectionString=SCS.ToString();
18. conn.Open();
19. using(SQLiteCommandcmd_Re=newSQLiteCommand(Sql,conn))
20. {
21. cmd_Re.ExecuteNonQuery();
22. }
23. }
24. returntrue;
25. }
26. catch(ExceptionE)
27. {
28. MessageBox.Show(E.Message,MessageBoxIcon.Information);
29. returnfalse;
30. }
31. }

/// <summary>
/// SQLiteCommand
/// </summary>
/// <param name="DataSource"></param>
/// <param name="Sql"></param>
/// <returns></returns>
public static bool SqlCommand(string DataSource,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,MessageBoxIcon.Information);
        return false;
    }
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!