public static void createTable(string dataSource, string tableName)
{
using (SQLiteConnection conn = new SQLiteConnection(dataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.DropTable(tableName);
SQLiteTable tb = new SQLiteTable(tableName);
tb.Columns.Add(new SQLiteColumn("id", true)); // auto increment
tb.Columns.Add(new SQLiteColumn("count"));
tb.Columns.Add(new SQLiteColumn("responsetime", ColType.Decimal));
sh.CreateTable(tb);
conn.Close();
}
}
}
private void createTable3()
{
using (var conn = new SQLiteConnection(logDataSource))
{
using (var cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
var sh = new SQLiteHelper(cmd);
if (!sh.ExistsTable("SfxFiddlerProductLog"))
{
var tb = new SQLiteTable("SfxFiddlerProductLog");
tb.Columns.Add(new SQLiteColumn("Id", true));
tb.Columns.Add(new SQLiteColumn("EndKeyword", ColType.Text));
tb.Columns.Add(new SQLiteColumn("duration", ColType.Integer));
tb.Columns.Add(new SQLiteColumn("InsertTime", ColType.DateTime));
sh.CreateTable(tb);
conn.Close();
}
}
}
}
/// <summary>
/// Gets the local item.
/// </summary>
/// <returns><c>true</c>, if local item was gotten, <c>false</c> otherwise.</returns>
/// <param name="sql">Variable sql.</param>
/// <param name="varOutItems">Variable out items.</param>
public SQLiteTable GetTable(string sql)
{
SQLiteTable table = null;
if (string.IsNullOrEmpty(sql) || IsOpen() == false)
{
return(table);
}
SQLiteQuery query = new SQLiteQuery(mDbConnect, sql);
if (query != null)
{
table = new SQLiteTable(query);
return(table);
}
else
{
query.Release();
return(table);
}
}
private void button1_Click(object sender, EventArgs e)
{
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
SQLiteTable tb = new SQLiteTable("AAAA");
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("name"));
sh.CreateTable(tb);
GetTableStatus(sh);
conn.Close();
}
}
}
private void CreateTable(string filename)// read file create database table
{
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
DataTable dt = sh.GetTableList();
dt.ToString();
SQLiteTable tb = new SQLiteTable(filename);
tb.Columns.Add(new SQLiteColumn("CmdID", ColType.Integer, false, true, true, "0"));
tb.Columns.Add(new SQLiteColumn("CmdType", ColType.Text, false, false, true, ""));
tb.Columns.Add(new SQLiteColumn("CmdValue", ColType.Text, false, false, true, ""));
sh.CreateTable(tb);
conn.Close();
}
}
}
private void Create_Empty_DataBase_old()
{
//SQLiteHelper sqlite = new SQLiteHelper(new SQLiteCommand(config.DataSource));
//using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
//{
// conn.Open();
// SQLiteTable table = new SQLiteTable(config.TableName_Users);
// sqlite.CreateTable(table);
// conn.Close();
//}
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sqlite = new SQLiteHelper(cmd);
SQLiteTable table = new SQLiteTable(config.TableName_Users);
SQLiteColumn column_ID = new SQLiteColumn("ID", ColType.Integer, true, true, true, "");
SQLiteColumn column_FirstName = new SQLiteColumn("FirstName", ColType.Text);
SQLiteColumn column_LastName = new SQLiteColumn("LastName", ColType.Text);
SQLiteColumn column_Age = new SQLiteColumn("Age", ColType.Integer);
table.Columns.Add(column_ID);
table.Columns.Add(column_FirstName);
table.Columns.Add(column_LastName);
table.Columns.Add(column_Age);
sqlite.CreateTable(table);
conn.Close();
}
}
}
private static void CreateTable(string tableName)
{
try
{
// Creating table....
SQLiteTable tb = new SQLiteTable(tableName);
tb.Columns.Add(new SQLiteColumn("ID", ColType.Integer, true, true, true, ""));
tb.Columns.Add(new SQLiteColumn("qh", ColType.Text));
tb.Columns.Add(new SQLiteColumn("jh", ColType.Text));
//tb.Columns.Add(new SQLiteColumn("TotalCost", ColType.Integer, false, false, true, "0"));
//tb.Columns.Add(new SQLiteColumn("LastModiTime", ColType.Text));
//tb.Columns.Add(new SQLiteColumn("CreateTime", ColType.Text));
// Execute Table Creation
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.DropTable(tableName);
sh.CreateTable(tb);
conn.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static void CreateTable()
{
var table = new SQLiteTable("DBCONF");
var cols = new SQLiteColumnList();
cols.Add(new SQLiteColumn("id", ColType.Text, true, false, true, ""));
cols.Add(new SQLiteColumn("dbType"));
cols.Add(new SQLiteColumn("dbHost"));
cols.Add(new SQLiteColumn("dbPort"));
cols.Add(new SQLiteColumn("dbName"));
cols.Add(new SQLiteColumn("dbUserName"));
cols.Add(new SQLiteColumn("dbPassword"));
cols.Add(new SQLiteColumn("updateDate", ColType.DateTime));
table.SetCols(cols);
db.CreateTable(table);
table = new SQLiteTable("PATHCONF");
cols = new SQLiteColumnList();
cols.Add(new SQLiteColumn("code"));
cols.Add(new SQLiteColumn("name"));
cols.Add(new SQLiteColumn("value"));
table.SetCols(cols);
db.CreateTable(table);
}
private void Register(IDataTable data)
{
if (data == null)
{
return;
}
if (mDataTables.ContainsKey(data.name) == false)
{
string sql = string.Format("select * from {0}", data.name.ToString());
SQLiteTable table = SQLite.Instance.GetTable(sql);
if (table != null)
{
mDataTables.Add(data.name, data);
data.Read(table);
table.Close();
}
else
{
Debug.LogError("Can't find table:" + data.name);
}
}
}
private void CreateNewDataBase()
{
if (File.Exists(DatabaseFile))
{
File.Delete(DatabaseFile);
}
SQLiteConnection.CreateFile(DatabaseFile);
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + DatabaseFile))
{
using (SQLiteCommand command = new SQLiteCommand())
{
command.Connection = connection;
connection.Open();
SQLiteHelper helper = new SQLiteHelper(command);
SQLiteTable tb = new SQLiteTable("RssList");
tb.Columns.Add(new SQLiteColumn("Name", ColType.Text));
tb.Columns.Add(new SQLiteColumn("Url", ColType.Text));
tb.Columns.Add(new SQLiteColumn("UpdateTime", ColType.DateTime));
tb.Columns.Add(new SQLiteColumn("Md5", ColType.Text, true, false, true, null));
tb.Columns.Add(new SQLiteColumn("Selected", ColType.Text));
helper.CreateTable(tb);
SQLiteTable tb2 = new SQLiteTable("DownloadedList");
tb2.Columns.Add(new SQLiteColumn("RssUrl", ColType.Text));
tb2.Columns.Add(new SQLiteColumn("Title", ColType.Text));
tb2.Columns.Add(new SQLiteColumn("UpdateTime", ColType.DateTime));
tb2.Columns.Add(new SQLiteColumn("MagnetLink", ColType.Text));
tb2.Columns.Add(new SQLiteColumn("Guid"));
tb2.Columns.Add(new SQLiteColumn("Md5", ColType.Text, true, false, true, null));
helper.CreateTable(tb2);
connection.Close();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
#region 判断IP的合法性
foreach (Control c in this.Controls)
{
if (c is TextBox)
{
if (string.IsNullOrEmpty((c as TextBox).Text))
{
MessageBox.Show("有设置选项未填写。", "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
foreach (Control c in this.panel1.Controls)
{
if (c is TextBox)
{
if (string.IsNullOrEmpty((c as TextBox).Text))
{
MessageBox.Show("COS服务器IP未填写。", "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if (Convert.ToInt32(c.Text) >= 256)
{
MessageBox.Show("请输入正确的IP地址。", "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
#endregion
#region 务程序数据库操作
string ip = textBox1.Text + "." + textBox2.Text + "." + textBox3.Text + "." + textBox4.Text;
int interval = Convert.ToInt32(textBox5.Text);
int timeout = Convert.ToInt32(textBox6.Text);
using (SQLiteConnection conn = new SQLiteConnection(@"data source=.\CloudAgent.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
SQLiteTable tb = new SQLiteTable("server");
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("ip", ColType.Text));
sh.CreateTable(tb);
SQLiteTable tb2 = new SQLiteTable("setting");
tb2.Columns.Add(new SQLiteColumn("id", true));
tb2.Columns.Add(new SQLiteColumn("key", ColType.Text));
tb2.Columns.Add(new SQLiteColumn("value", ColType.Integer));
sh.CreateTable(tb2);
sh.BeginTransaction();
try
{
var dic = new Dictionary <string, object>();
dic["ip"] = ip;
sh.Insert("server", dic);
var dicData = new Dictionary <string, object>();
var dicData2 = new Dictionary <string, object>();
var dicData3 = new Dictionary <string, object>();
dicData["key"] = "interval";
dicData["value"] = interval;
dicData2["key"] = "timeout";
dicData2["value"] = timeout;
dicData3["key"] = "hostset";
dicData3["value"] = "";
DataTable dt = sh.Select("select * from setting where key='interval';");
if (dt.Rows.Count > 0)
{
sh.Update("setting", dicData, "key", "interval");
}
else
{
sh.Insert("setting", dicData);
}
DataTable dt2 = sh.Select("select * from setting where key='timeout';");
if (dt2.Rows.Count > 0)
{
sh.Update("setting", dicData2, "key", "timeout");
}
else
{
sh.Insert("setting", dicData2);
}
DataTable dt3 = sh.Select("select * from setting where key='hostset';");
if (dt3.Rows.Count == 0)
{
sh.Insert("setting", dicData3);
}
sh.Commit();
}
catch
{
sh.Rollback();
}
conn.Close();
}
}
#endregion
#region 是否打开服务
if (radioButton1.Checked)
{
Thread ce = new Thread(delegate()
{
Process p = new Process();
string path = System.Environment.CurrentDirectory;
p.StartInfo.UseShellExecute = false;
p.StartInfo.FileName = path + @"\CloudAgent.exe";
//MessageBox.Show(p.StartInfo.FileName);
p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardOutput = true;
p.EnableRaisingEvents = true;
p.StartInfo.Arguments = "-install";
try
{
// MessageBox.Show(radioButton1.Checked.ToString());
p.Start();
p.WaitForExit();
}
catch (System.ComponentModel.Win32Exception err)
{
MessageBox.Show("系统找不到指定的程序文件。\r{2}");
p.Close();
return;
}
p.Close();
});
ce.IsBackground = false;
ce.Start();
Thread ce2 = new Thread(delegate()
{
Process p = new Process();
string path = System.Environment.CurrentDirectory;
p.StartInfo.UseShellExecute = false;
p.StartInfo.FileName = path + @"\AutoOnOffLine.exe";
//MessageBox.Show(p.StartInfo.FileName);
p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardOutput = true;
p.EnableRaisingEvents = true;
p.StartInfo.Arguments = "-install";
try
{
// MessageBox.Show(radioButton1.Checked.ToString());
p.Start();
p.WaitForExit();
}
catch (System.ComponentModel.Win32Exception err)
{
MessageBox.Show("系统找不到指定的程序文件。\r{2}");
p.Close();
return;
}
p.Close();
});
ce2.IsBackground = false;
ce2.Start();
}
if (radioButton2.Checked)
{
Thread ce = new Thread(delegate()
{
Process p = new Process();
string path = System.Environment.CurrentDirectory;
p.StartInfo.UseShellExecute = false;
p.StartInfo.FileName = path + @"\CloudAgent.exe";
p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardOutput = true;
p.EnableRaisingEvents = true;
p.StartInfo.Arguments = "-remove";
try
{
p.Start();
p.WaitForExit();
}
catch (System.ComponentModel.Win32Exception err)
{
MessageBox.Show("系统找不到指定的程序文件。\r{2}");
p.Close();
return;
}
p.Close();
});
ce.IsBackground = false;
ce.Start();
Thread ce2 = new Thread(delegate()
{
Process p = new Process();
string path = System.Environment.CurrentDirectory;
p.StartInfo.UseShellExecute = false;
p.StartInfo.FileName = path + @"\AutoOnOffLine.exe";
p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardOutput = true;
p.EnableRaisingEvents = true;
p.StartInfo.Arguments = "-remove";
try
{
p.Start();
p.WaitForExit();
}
catch (System.ComponentModel.Win32Exception err)
{
MessageBox.Show("系统找不到指定的程序文件。\r{2}");
p.Close();
return;
}
p.Close();
});
ce2.IsBackground = false;
ce2.Start();
}
#endregion
/* Environment.Exit(1);*/
Application.Exit();
}
private void btDropCreate_Click(object sender, EventArgs e)
{
try
{
// Check if the table name is blank
if (textBox1.Text.Trim().Length == 0)
{
MessageBox.Show("Table Name cannot be blank.");
return;
}
// Check if all the columns' name is filled
foreach (Control c in flowLayoutPanel1.Controls)
{
if (c is Column)
{
Column col = (Column)c;
if (col.ColumnName.Trim().Length == 0)
{
MessageBox.Show("Some column's name is blank. Cannot create table.");
return;
}
}
}
// Creating table....
SQLiteTable tb = new SQLiteTable(textBox1.Text);
foreach (Control c in flowLayoutPanel1.Controls)
{
if (c is Column)
{
Column col = (Column)c;
tb.Columns.Add(new SQLiteColumn(col.ColumnName, col.ColumnType, col.PrimaryKey, col.AutoIncrement, col.NotNUll, col.DefaultValue));
}
}
// Execute Table Creation
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.DropTable(textBox1.Text);
sh.CreateTable(tb);
conn.Close();
}
}
MessageBox.Show("Table created.");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
static void ExportDataTable(string tableName, string fileName, string className)
{
if (string.IsNullOrEmpty(tableName))
{
return;
}
if (SQLite.Instance.IsOpen() == false)
{
if (SQLite.Instance.Open(database) == false)
{
Debug.LogError("无法打开数据库:" + database);
return;
}
}
SQLiteTable info = SQLite.Instance.GetTableInfo(tableName);
if (info == null)
{
Debug.LogError("无法读取表" + tableName + "信息");
return;
}
string path = string.Format("{0}/Scripts/Data/Tables/{1}.cs", Application.dataPath, fileName);
string definition = "";
string read = string.Format("\t{0} o = new {1}();\n", className, className);
string keyName = null;
string keyType = null;
while (info.Read())
{
string columnName = info.GetByColumnName("name", "");
string columnType = GetType(info.GetByColumnName("type", ""));
if (string.IsNullOrEmpty(keyName))
{
keyName = columnName;
}
if (string.IsNullOrEmpty(keyType))
{
keyType = columnType;
}
definition += string.Format("\tpublic {0} {1};\n", columnType, columnName);
read += string.Format("\t\t\to.{0} = table.GetByColumnName(\"{1}\", {2});\n", columnName, columnName, GetDefaultValue(columnType));
}
read += string.Format("\t\t\tdic.Add(o.{0},o);\n\t\t\tlist.Add(o);", keyName);
info.Close();
if (File.Exists(path) == false)
{
string code = template.Replace("{filename}", fileName)
.Replace("{definition}", definition)
.Replace("{classname}", className)
.Replace("{tablename}", tableName)
.Replace("{read}", read)
.Replace("{key}", keyType);
Debug.Log(code);
FileEx.SaveFile(path, code);
}
else
{
string datatable = File.ReadAllText(path);
datatable = datatable.ReplaceEx("//TABLE_DEFINITION_BEGIN", "//TABLE_DEFINITION_END", definition);
datatable = datatable.ReplaceEx("//TABLE_READ_BEGIN", "//TABLE_READ_END", "\t\t" + read + "\n");
FileEx.SaveFile(path, datatable);
}
Debug.Log("成功导出数据表:" + tableName);
}
private static void CreateSpatialTable()
{
try
{
#region 创建点层
SQLiteTable table = new SQLiteTable();
table.Name = "PointT";
SQLiteColumn col = new SQLiteColumn();
col.AutoIncrement = true;
col.DataType = FieldType.Integer;
col.Name = "PK_UID";
col.NotNull = true;
table.Columns.Add(col);
col = new SQLiteColumn();
col.DataType = FieldType.Text;
col.Name = "name";
table.Columns.Add(col);
sh.CreateTable(table);
string sql = "CREATE TABLE PointT (PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT)";
//sh.Execute(sql);
/* 添加图形列 AddGeometryColumn('你的表名', '你的几何列名',4326, 'POINT','XY') XY为二维数据*/
sql = "SELECT AddGeometryColumn('PointT', 'GEOMETRY',2346, 'POINT','XY')"; //2346 为EPSG码
sh.Execute(sql);
/* 添加空间索引 */
sql = "SELECT CreateSpatialIndex('PointT', 'GEOMETRY')";
sh.Execute(sql);
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
sql = "INSERT INTO PointT(PK_UID, name, GEOMETRY) "
+ "VALUES (1, 'first point',GeomFromText('POINT(636000 5209340)',2346))";
sh.Execute(sql);
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
sql = "INSERT INTO PointT(GEOMETRY, name, PK_UID) "
+ "VALUES (GeomFromText('POINT(636100 5209340)',2346),'eleventh point', 2)";
sh.Execute(sql);
#endregion
#region 创建线层
sql = "CREATE TABLE PolylineT(PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL)";
sh.Execute(sql);
//bRet = ExecuteSql(sql, conn);
/* 添加图形列 */
sql = "SELECT AddGeometryColumn('PolylineT', 'GEOMETRY',2346, 'LINESTRING', 2)"; //2346 为EPSG码
sh.Execute(sql);
/* 添加空间索引 */
sql = "SELECT CreateSpatialIndex('PolylineT', 'GEOMETRY')";
sh.Execute(sql);
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
sql = "INSERT INTO PolylineT(PK_UID, name, GEOMETRY) "
+ "VALUES (1, 'line1', GeomFromText('LINESTRING(636000 5209340,633950 5212200,634400 5207800)',2346))";
sh.Execute(sql);
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
//sql = "INSERT INTO Xian(GEOMETRY, meas_value, name, PK_UID) VALUES (GeomFromText('POINT(636100 5209340)',2346),11.123456789, 'eleventh point', 2)";
//cmd.CommandText = sql;
//cmd.ExecuteNonQuery();
#endregion
#region 创建面层
sql = "CREATE TABLE PolygonT (PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL)";
sh.Execute(sql);
//bRet = ExecuteSql(sql, conn);
/* 添加图形列 */
sql = "SELECT AddGeometryColumn('PolygonT', 'GEOMETRY',2346, 'POLYGON', 2)"; //2346 为EPSG码
sh.Execute(sql);
/* 添加空间索引 */
sql = "SELECT CreateSpatialIndex('PolygonT', 'GEOMETRY')";
sh.Execute(sql);
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
sql = "INSERT INTO PolygonT(PK_UID, name, GEOMETRY) "
+ "VALUES (1, 'Poly1',GeomFromText('POLYGON((636000 5209340,633950 5212200,634400 5207800,632409 5209760,636000 5209340))',2346))";
sh.Execute(sql);
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
//sql = "INSERT INTO Xian(GEOMETRY, meas_value, name, PK_UID) VALUES (GeomFromText('POINT(636100 5209340)',2346),11.123456789, 'eleventh point', 2)";
//cmd.CommandText = sql;
//cmd.ExecuteNonQuery();
Console.WriteLine("按任意键返回.");
Console.ReadKey();
DisplayMenu();
#endregion
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("#错误：" + ex.Message);
Console.ResetColor();
Console.WriteLine("按任意键返回.");
Console.ReadKey();
DisplayMenu();
}
}
/// <summary>
/// DisCover all the DataBase Columns and put them in Table object, return <see cref="IEnumerable{T}"/> contain all Columns
/// </summary>
/// <param name="table">the Table object</param>
public static IEnumerable <SQLiteTableColumn> TableColumns(SQLiteTable table) =>
table.GetObjectArray2D(table.ExecuteReaderQuery(table.Query.GetTableColumns))
.Select(column => new SQLiteTableColumn(column[1].ToString(), column[2].ToString()));
public void CreateTable(SQLiteTable table)
{
StringBuilder sb = new StringBuilder();
sb.Append("create table if not exists `");
sb.Append(table.TableName);
sb.AppendLine("`(");
bool firstRecord = true;
foreach (SQLiteColumn col in table.Columns)
{
if (col.ColumnName.Trim().Length == 0)
{
throw new Exception("Column name cannot be blank.");
}
if (firstRecord)
{
firstRecord = false;
}
else
{
sb.AppendLine(",");
}
sb.Append(col.ColumnName);
sb.Append(" ");
if (col.AutoIncrement)
{
sb.Append("integer primary key autoincrement");
continue;
}
switch (col.ColDataType)
{
case ColType.Text:
sb.Append("text"); break;
case ColType.Integer:
sb.Append("integer"); break;
case ColType.Decimal:
sb.Append("decimal"); break;
case ColType.DateTime:
sb.Append("datetime"); break;
case ColType.BLOB:
sb.Append("blob"); break;
}
if (col.PrimaryKey)
{
sb.Append(" primary key");
}
else if (col.NotNull)
{
sb.Append(" not null");
}
else if (col.DefaultValue.Length > 0)
{
sb.Append(" default ");
if (col.DefaultValue.Contains(" ") || col.ColDataType == ColType.Text || col.ColDataType == ColType.DateTime)
{
sb.Append("'");
sb.Append(col.DefaultValue);
sb.Append("'");
}
else
{
sb.Append(col.DefaultValue);
}
}
}
sb.AppendLine(");");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
/// <summary> /// initialize new instance of <see cref="ObjectReader"/> withe Table /// </summary> /// <param name="table">the Table That Contain the Columns that comes with the object</param> public ObjectReader(SQLiteTable table) => Table = table;
public static void init()
{
using (SQLiteConnection conn = new SQLiteConnection(config.DB_FILE))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
//系统信息表
SQLiteTable SystemConfig = new SQLiteTable("SystemConfig");
SystemConfig.Columns.Add(new SQLiteColumn("configid", ColType.Text));
SystemConfig.Columns.Add(new SQLiteColumn("value", ColType.Text));
SystemConfig.Columns.Add(new SQLiteColumn("remark", ColType.Text));
//收银员表
SQLiteTable cashier = new SQLiteTable("Cashier");
cashier.Columns.Add(new SQLiteColumn("id", ColType.Text));
cashier.Columns.Add(new SQLiteColumn("name", ColType.Text));
cashier.Columns.Add(new SQLiteColumn("code", ColType.Text));
cashier.Columns.Add(new SQLiteColumn("pwd", ColType.Text));
//键盘配置表
SQLiteTable keyboard = new SQLiteTable("KeyBoard");
keyboard.Columns.Add(new SQLiteColumn("commandkey", ColType.Text));
keyboard.Columns.Add(new SQLiteColumn("commandname", ColType.Text));
keyboard.Columns.Add(new SQLiteColumn("keycode", ColType.Text));
//支付方式表
SQLiteTable paytype = new SQLiteTable("PayType");
paytype.Columns.Add(new SQLiteColumn("code", ColType.Text));
paytype.Columns.Add(new SQLiteColumn("name", ColType.Text));
paytype.Columns.Add(new SQLiteColumn("isenable", ColType.Text));
paytype.Columns.Add(new SQLiteColumn("isearn", ColType.Text));
//售卖单据流水号
SQLiteTable sequence = new SQLiteTable("OrderSequence");
sequence.Columns.Add(new SQLiteColumn("date", ColType.DateTime));
sequence.Columns.Add(new SQLiteColumn("seq", ColType.Integer));
SQLiteTable pos = new SQLiteTable("PosConfig");
pos.Columns.Add(new SQLiteColumn("id", ColType.Text));
pos.Columns.Add(new SQLiteColumn("posname", ColType.Text));
pos.Columns.Add(new SQLiteColumn("poscode", ColType.Text));
pos.Columns.Add(new SQLiteColumn("shopcode", ColType.Text));
pos.Columns.Add(new SQLiteColumn("posid", ColType.Text));
pos.Columns.Add(new SQLiteColumn("isenable", ColType.Text));
pos.Columns.Add(new SQLiteColumn("shopname", ColType.Text));
pos.Columns.Add(new SQLiteColumn("initcode", ColType.Text));
pos.Columns.Add(new SQLiteColumn("createdate", ColType.Text));
pos.Columns.Add(new SQLiteColumn("lastping", ColType.Text));
pos.Columns.Add(new SQLiteColumn("lastsync", ColType.Text));
pos.Columns.Add(new SQLiteColumn("ipaddr", ColType.Text));
//商品档案表
SQLiteTable product = new SQLiteTable("Product");
product.Columns.Add(new SQLiteColumn("id", ColType.Integer, true, false, true, ""));
product.Columns.Add(new SQLiteColumn("barcode", ColType.Text));
product.Columns.Add(new SQLiteColumn("name", ColType.Text));
product.Columns.Add(new SQLiteColumn("spec", ColType.Text));
product.Columns.Add(new SQLiteColumn("unit", ColType.Text));
product.Columns.Add(new SQLiteColumn("tintype", ColType.Text));
product.Columns.Add(new SQLiteColumn("midtype", ColType.Text));
product.Columns.Add(new SQLiteColumn("bigtype", ColType.Text));
product.Columns.Add(new SQLiteColumn("classtype", ColType.Text));
product.Columns.Add(new SQLiteColumn("depttype", ColType.Text));
product.Columns.Add(new SQLiteColumn("price", ColType.Decimal));
//售卖单据
SQLiteTable saleorder = new SQLiteTable("SaleOrder");
saleorder.Columns.Add(new SQLiteColumn("id", ColType.Text, true, false, true, null));
saleorder.Columns.Add(new SQLiteColumn("ordercode"));
saleorder.Columns.Add(new SQLiteColumn("shopcode"));
saleorder.Columns.Add(new SQLiteColumn("poscode"));
saleorder.Columns.Add(new SQLiteColumn("cashier"));
saleorder.Columns.Add(new SQLiteColumn("amount"));
saleorder.Columns.Add(new SQLiteColumn("count"));
saleorder.Columns.Add(new SQLiteColumn("disamount"));
saleorder.Columns.Add(new SQLiteColumn("createdate"));
saleorder.Columns.Add(new SQLiteColumn("updatedate"));
saleorder.Columns.Add(new SQLiteColumn("state"));
//售卖明细
SQLiteTable saleorderList = new SQLiteTable("SaleOrderList");
saleorderList.Columns.Add(new SQLiteColumn("id", ColType.Text, true, false, true, null));
saleorderList.Columns.Add(new SQLiteColumn("ordercode"));
saleorderList.Columns.Add(new SQLiteColumn("orderid"));
saleorderList.Columns.Add(new SQLiteColumn("productid"));
saleorderList.Columns.Add(new SQLiteColumn("barcode"));
saleorderList.Columns.Add(new SQLiteColumn("name"));
saleorderList.Columns.Add(new SQLiteColumn("spec"));
saleorderList.Columns.Add(new SQLiteColumn("unit"));
saleorderList.Columns.Add(new SQLiteColumn("tintype"));
saleorderList.Columns.Add(new SQLiteColumn("midtype"));
saleorderList.Columns.Add(new SQLiteColumn("bigtype"));
saleorderList.Columns.Add(new SQLiteColumn("classtype"));
saleorderList.Columns.Add(new SQLiteColumn("depttype"));
saleorderList.Columns.Add(new SQLiteColumn("price"));
saleorderList.Columns.Add(new SQLiteColumn("count"));
saleorderList.Columns.Add(new SQLiteColumn("discount"));
saleorderList.Columns.Add(new SQLiteColumn("disprice"));
saleorderList.Columns.Add(new SQLiteColumn("amount"));
saleorderList.Columns.Add(new SQLiteColumn("disamount"));
//收银流水表
SQLiteTable account = new SQLiteTable("Account");
account.Columns.Add(new SQLiteColumn("id", ColType.Text, true, false, true, null));
account.Columns.Add(new SQLiteColumn("ordercode", ColType.Text));
account.Columns.Add(new SQLiteColumn("orderid", ColType.Text));
account.Columns.Add(new SQLiteColumn("sum", ColType.Text));
account.Columns.Add(new SQLiteColumn("type", ColType.Text));
account.Columns.Add(new SQLiteColumn("date", ColType.DateTime));
sh.BeginTransaction();
try
{
sh.CreateTable(SystemConfig);
sh.CreateTable(cashier);
sh.CreateTable(keyboard);
sh.CreateTable(paytype);
sh.CreateTable(sequence);
sh.CreateTable(pos);
sh.CreateTable(product);
sh.CreateTable(saleorder);
sh.CreateTable(saleorderList);
sh.CreateTable(account);
sh.Commit();
}
catch (Exception e)
{
System.Console.WriteLine(e);
sh.Rollback();
}
finally
{
conn.Close();
}
initData();
}
}
}
/// <summary>
/// create new instance of <see cref="SQLiteDataBase" /> initialized to Work with specific Table
/// </summary>
/// <param name="table">the Table Name that will Make Queries for it</param>
public SQLiteQuery(SQLiteTable table)
{
Table = table ?? throw new ArgumentException("message", nameof(table));
ObjectReader = new ObjectReader(Table);
}