Sqlite function encapsulation is commonly used to improve the efficiency of Codeeer

The following is the frequently used Sqlite function. The content format is relatively fixed, and encapsulation 1 helps to improve the development efficiency (^_^ at least improves the efficiency of Codeeer).

Moreover, I found that Sqlite has less Chinese data, at least it is more complicated to find compared with other data. It serves the public
I did not encapsulate the read part, because the flexibility of the database read is too large, it is difficult to encapsulate, and even if the encapsulation is good, it is difficult to deal with all the cases, or suggest to design the code logic according to the actual situation.

Create:

 
/// <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, " prompt ", MessageBoxButtons.OK, MessageBoxIcon.Information); 
return false; 
} 
} 

Delete:

 
/// <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, " prompt ", MessageBoxButtons.OK, MessageBoxIcon.Information); 
return false; 
} 
} 

Insert:
This is because multiple fields are inserted at the same time. Never seen a database like spaghetti 1)

The Insert structure is designed here to store the relationship between fields and values. (we have considered using arrays, but they are not easy to call.

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

The following is the main function of the insert module:

 
/// <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  The packet is empty, open your dog's eyes... "); 
} 
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, " prompt ", MessageBoxButtons.OK, MessageBoxIcon.Information); 
return false; 
} 
} 

It is necessary to say that "W2" and "W44" are already designed fields, while "TableTest" is already added table segment

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

Table segment acquisition:

 
/// <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, " prompt ", MessageBoxButtons.OK, MessageBoxIcon.Information); 
} 
} 
} 
} 
} 
return ResultLst; 
} 

Field acquisition:

 
/// <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, " prompt ", MessageBoxButtons.OK, MessageBoxIcon.Information); 
} 
} 
} 
} 
return WordsLst; 
} 

Explain why the comments in the code are mostly written in English, because I've been learning how to spell. However, I am not familiar with it. I type very slowly, and I can easily break my train of thought when using Code. You're going to have to explain to me how you learned how to get to zero in the database.