原文地址:http://blog.csdn.net/codeeer/article/details/8440751
以下是频繁用到的Sqlite函数,内容格式相对固定,封装一下有助于提高开发效率(^_^至少提高Codeeer的效率了)
而且,我发现Sqlite中文资料比较少,起码相对其他找起来要复杂些,服务一下大众~
我没有封装读取部分,因为数据库读取灵活性太大,封装起来难度也大,而且就算封装好了,也难以应付所有情况,还是建议根据实际情况设计代码逻辑。
解释下,为啥代码中的注释基本都用英文写了,因为这段时间在学双拼- -。可是还不太熟悉,打字超慢,而且Code的时候容易打断思路,好在~英文不多,而且这些都看不懂的话你……你要向我解释一下你是怎么一路学到数据库的 0。0
修改密码
- /// <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;
- }
- }
/// <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;
}
}
创建数据库
- /// <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;
- }
- }
/// <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;
}
}
获取表段
- /// <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;
- }
/// <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;
}
获取字段
- /// <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;
- }
/// <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;
}
取值
- /// <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;
- }
/// <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;
}
插入数据
- /// <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;
- }
- }
/// <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;
}
}
删除数据
- /// <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;
- }
- }
/// <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指令
- /// <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;
- }
- }
/// <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;
}
}