The following are frequently used SQLite functions, with a relatively fixed content format that can be encapsulated to improve development efficiency (^_^ at least improve codeeer efficiency)
Moreover, I found that SQLite Chinese information is relatively small, at least relative to other find it more complicated, to serve the public ~
I did not encapsulate the read part, because the database read flexibility is too high, the encapsulation is very difficult, and even if the package is good, it is difficult to cope with all the situation, or recommend the design of the code based on the actual situation.
Create：
Copy Code code 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, "hint", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
}
Remove：
Copy Code code 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, "hint", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
}
Insert：
Here's the explanation, because there are multiple fields that are inserted at the same time (more than exist, very common). I've never seen a database like spaghetti.
Here I designed the insert structure to store the relationship between the field and the value (once considered using an array method, but that thing is not very convenient to call, look very abstract, not very good, if there are better suggestions, welcome message ~)
Copy Code code 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 is the main function of the Insert module：
Copy Code code 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 ("Insertbag 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, "hint", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
}
The visual is a bit complicated, to a demo, it is necessary to say, "W2" and "W44" are already designed fields, and "tabletest" is already added to the table section
Copy Code code 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 Fetch：
Copy Code code 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, "hint", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
}
return resultlst;
}
Field gets：
Copy Code code 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, "hint", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
return wordslst;
}
Explain why the comments in the code are basically written in English, because this is the time to learn a double spell. But not quite familiar with, typing is very slow, and the code when easy to interrupt the idea, fortunately ~ English is not much, and these are not understand words you ... You have to explain to me how you have learned the database 0.
