Sqlite common function encapsulation improves Codeeer Efficiency

The following are frequently used Sqlite functions. The content format is relatively fixed. encapsulation helps improve development efficiency (^_^ at least improves Codeeer efficiency)

In addition, I found that Sqlite has fewer Chinese materials, and at least it is more complicated than other Chinese materials ~
I did not encapsulate the read part, because the read flexibility of the database is too great, and it is difficult to encapsulate it. Even if it is encapsulated, it is difficult to cope with all situations. We recommend that you design the code logic based on the actual situation.

Create:Copy codeThe Code is as follows: // <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:Copy codeThe Code is as follows: // <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:
It should be noted that because multiple fields are inserted at the same time (it is common --. I have never seen anyone whose databases are like spaghetti)

Here we have designed the Insert structure to store the relationship between fields and values (we have considered using Arrays for implementation, but it is not convenient to call it. It is very abstract and not easy to use, if you have better suggestions, please leave a message ~)Copy codeThe Code is as follows: // <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 are the main functions of the insert module::Copy codeThe Code is as follows: // <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 ("The InsertBag packet is empty. Open your dog eye ...... ");
}
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;
}
}

The visual test is a bit complicated. For a Demo, it is necessary to say that "W2" and "W44" are designed fields, while "TableTest" is the added table segment.Copy codeThe Code is as follows: 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:Copy codeThe Code is as follows: // <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:Copy codeThe Code is as follows: // <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;
}

I explained why the comments in the Code are basically written in English, because during this time, I learned how to work hard --. However, I am not familiar with it yet. Typing is too slow and it is easy to interrupt my thinking when I Code it. Fortunately ~ There are not many English words, and you can't understand them ...... You need to explain to me how you learned database 0 all the way.