public static DataTable GetDictionaryTable(string tableName)
{
DataTable dt = null;
using (SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\DataBase\Dictionary.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
dt = sh.Select("Select * From" + " " + tableName);
sh.Commit();
}
catch
{
sh.Rollback();
}
//var dic1 = new Dictionary<string, object>();
//dic1["orderId"] = "20";
//DataTable dt = sh.Select("select * from Log where orderId = @orderId", dic1);
conn.Close();
}
}
return(dt);
}
void CreateSampleTable_OriginalTable()
{
sh.DropTable("ori");
sh.DropTable("ori2");
SQLiteTable tb = new SQLiteTable("ori");
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("name"));
tb.Columns.Add(new SQLiteColumn("code"));
tb.Columns.Add(new SQLiteColumn("location"));
tb.Columns.Add(new SQLiteColumn("category"));
sh.CreateTable(tb);
sh.BeginTransaction();
for (int i = 0; i < 5; i++)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
dic["name"] = "aaaa";
dic["code"] = "aaaa";
dic["location"] = "aaaa";
dic["category"] = "aaaa";
sh.Insert("ori", dic);
}
sh.Commit();
dgvTableOri.DataSource = sh.GetColumnStatus("ori");
dgvDataOri.DataSource = sh.Select("select * from ori;");
}
public static void InitTestCase()
{
using (SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\DataBase\TestCase.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
Global.Parameters.dtPOSTestCase = sh.Select("Select * From PosTestCase");
Global.Parameters.dtOnlineDeviceCase = sh.Select("Select * From OnlineDeviceTestCase");
Global.Parameters.dtOfflineDeviceCase = sh.Select("Select * From OfflineDeviceTestCase");
sh.Commit();
}
catch
{
sh.Rollback();
}
//var dic1 = new Dictionary<string, object>();
//dic1["orderId"] = "20";
//DataTable dt = sh.Select("select * from Log where orderId = @orderId", dic1);
conn.Close();
}
}
}
public static void DeleteALLLog(string databaseName, bool isSqlite_Sequence)
{
using (SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\DataBase\" + databaseName + ".db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
sh.Execute("delete from Log");
if (isSqlite_Sequence)
{
sh.Execute("delete from sqlite_sequence");
}
sh.Commit();
}
catch
{
sh.Rollback();
}
conn.Close();
}
}
}
public static void InsertLog(Dictionary <string, object> dic_Log, string dataBaseName, string tableName)
{
using (SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\DataBase\" + dataBaseName + ".db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
var dic = new Dictionary <string, object>(dic_Log);
sh.Insert(tableName, dic);
sh.Commit();
}
catch
{
sh.Rollback();
}
//var dic1 = new Dictionary<string, object>();
//dic1["orderId"] = "20";
//DataTable dt = sh.Select("select * from Log where orderId = @orderId", dic1);
conn.Close();
}
}
}
public static void updataMessageResult(Dictionary <string, object> updata_Log, string queryId, ref string errormessage)
{
using (SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\DataBase\TransLog.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
sh.Update("Log", updata_Log, "queryId", queryId);
sh.Commit();
}
catch
{
sh.Rollback();
}
//var dic1 = new Dictionary<string, object>();
//dic1["orderId"] = "20";
//DataTable dt = sh.Select("select * from Log where orderId = @orderId", dic1);
conn.Close();
}
}
}
public void ReinitializeDatabase(string subscriberId)
{
Dictionary<string, string> dbSchema = JsonConvert.DeserializeObject<Dictionary<string, string>>(wsClient.GetFullDBSchema(JsonConvert.SerializeObject(subscriberId)));
using (SQLiteConnection conn = new SQLiteConnection(this.connString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
foreach (KeyValuePair<string, string> entry in dbSchema)
if (!entry.Key.StartsWith("00000"))
{
sh.Execute(entry.Value);
}
sh.Commit();
}
catch (Exception ex)
{
sh.Rollback();
throw ex;
}
conn.Close();
}
}
}
public static void UpdateLog(Dictionary <string, object> dic_Log, string tableName)
{
using (SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\DataBase\Dictionary.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
var dic = new Dictionary <string, object>(dic_Log);
sh.Update(tableName, dic_Log, "testCaseName", dic_Log["testCaseName"]);
sh.Commit();
}
catch
{
sh.Rollback();
}
conn.Close();
}
}
}
private void btCommit_Click(object sender, EventArgs e)
{
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
int count = sh.ExecuteScalar <int>("select count(*) from product;") + 1;
sh.BeginTransaction();
for (int i = 0; i < 5; i++)
{
var dic = new Dictionary <string, object>();
dic["name"] = "Product " + (count + i);
dic["description"] = "Some description";
dic["status"] = "Active";
sh.Insert("product", dic);
}
sh.Commit();
LoadData(sh);
conn.Close();
}
}
}
void CreateTablePerson(SQLiteHelper sh)
{
sh.DropTable("person");
SQLiteTable tb = new SQLiteTable("person");
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("name"));
tb.Columns.Add(new SQLiteColumn("code"));
tb.Columns.Add(new SQLiteColumn("tel"));
sh.CreateTable(tb);
sh.BeginTransaction();
for (int i = 0; i < 5; i++)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
dic["name"] = "Hello hello";
dic["code"] = "########";
dic["tel"] = "11111111111";
sh.Insert("person", dic);
}
sh.Commit();
}
public ReturnObj run(string dbfile, List<QueryObj> queries)
{
var ret = new ReturnObj();
try
{
using (SQLiteConnection conn = new SQLiteConnection(dbfile))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
foreach (QueryObj query in queries)
{
if (query != null)
{
switch (query.caseid)
{
case 0: ret.datatable = sh.Select(query.select, query.selectionary); break; //select
case 1: sh.CreateTable(query.newtable); break; // create tables
case 2: ret.datatable = sh.GetTableStatus(); break; // get table status
case 3: sh.Update(query.table, query.dictionary, query.selectionary); tryGetId(ref ret, query.selectionary, 3, null); break; //update item, pass id for record
case 4: sh.Insert(query.table, query.dictionary); tryGetId(ref ret, null, 4, sh.LastInsertRowId()); break; //insert item, get id
case 5: query.dictionary["itemid"] = ret.id; sh.Insert(query.table, query.dictionary); break; //insert record with last item id
case 6: sh.Insert(query.table, query.dictionary); break; //simple insert
case 7: query.dictionary["childid"] = ret.id; sh.Insert(query.table, query.dictionary); break; //insert new parent
case 8: sh.UpdateTableStructure(query.newtable.TableName, query.newtable); break;
}
}
}
sh.Commit();
conn.Close();
}
catch (Exception ex)
{
sh.Rollback();
ret.completed = false;
return ret;
}
}
}
ret.completed = true;
return ret;
}
catch (Exception ex)
{
ret.completed = false;
return ret;
}
}
public static void PostUser()
{
SQLiteHelper sh = new SQLiteHelper();
sh = sh.Init();
DataTable dt = sh.Select("SELECT * FROM USERS WHERE uid is null or uid = ''");
sh.BeginTransaction();
int total = 0;
foreach (DataRow dr in dt.Rows)
{
total++;
if (total >= 3000)
{
break;
}
//string url = "http://*****:*****@123";
user["email"] = user["username"] + "@alobhxh.com";
string jSONContent = user.ToString();
//
JObject obj = util.HTTP_POST_JSON(url, apiKey, jSONContent);
if (obj != null && obj["code"].ToString() == "ok")
{
Console.WriteLine("OK: " + user["username"]);
//
string uid = obj["payload"]["uid"].ToString();
sh.Execute("UPDATE USERS SET UID = '" + uid + "' WHERE USERNAME = '******'");
//
url += "/" + uid;
//username, email, fullname, website, location, birthday, signature
user = new JObject();
user["username"] = GetString(dr["username"]);
user["email"] = user["username"] + "@alobhxh.com";
user["fullname"] = GetString(dr["fullname"]);
user["location"] = GetString(dr["address"]);
user["birthday"] = GetString(dr["birthday"]);
jSONContent = user.ToString();
//
util.HTTP_PUT_JSON(url, apiKey, jSONContent);
}
}
sh.Commit();
sh.Close();
}
public static void initData()
{
using (SQLiteConnection conn = new SQLiteConnection(config.DB_FILE))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
try
{
Object o = sh.ExecuteScalar("select count(*) from KeyBoard");
Int32 i = Int32.Parse(o.ToString());
sh.BeginTransaction();
if (i == 0)
{
//键盘命令初始化
sh.Execute("insert into KeyBoard values('pay','结算','Space')");
sh.Execute("insert into KeyBoard values('member','查询会员','H')");
sh.Execute("insert into KeyBoard values('pay_alipay','支付宝结算','A')");
sh.Execute("insert into KeyBoard values('pay_yl','银联结算','Y')");
sh.Execute("insert into KeyBoard values('pay_cash','现金结算','X')");
sh.Execute("insert into KeyBoard values('pay_wx','微信结算','W')");
sh.Execute("insert into KeyBoard values('pay_mcard','M卡结算','M')");
sh.Execute("insert into KeyBoard values('hold_order','挂单','Control+G')");
sh.Execute("insert into KeyBoard values('single_del','单品删除','Control+D')");
sh.Execute("insert into KeyBoard values('lock','锁定POS','L')");
}
o = sh.ExecuteScalar("select count(*) from PayType");
i = Int32.Parse(o.ToString());
if (i == 0)
{
//支付方式初始化
sh.Execute("insert into PayType values('1','现金','1','1')");
sh.Execute("insert into PayType values('2','银联','1','0')");
sh.Execute("insert into PayType values('3','M卡','1','0')");
sh.Execute("insert into PayType values('4','微信','1','0')");
sh.Execute("insert into PayType values('5','支付宝','1','0')");
sh.Execute("insert into PayType values('6','自定义','1', '1')");
}
sh.Commit();
}
catch (Exception e)
{
System.Console.WriteLine(e);
sh.Rollback();
}
finally
{
conn.Close();
}
}
}
}
// 添加到自选库
public void addProductList(List <ProductInfo> productList)
{
try
{
DateTime now = DateTime.Now;
string addTime = now.ToString();
_helper.BeginTransaction();
foreach (ProductInfo item in productList)
{
addProduct(item._title, item._auctionUrl, item._zkPrice, item._tkRate, item._tkCommFee, item._Sale30, addTime);
}
_helper.Commit();
}
catch (Exception)
{
_helper.Rollback();
}
}
/// <summary>
/// 随机生成多义线
/// </summary>
private static void CreatePolylines()
{
Console.Write("\n 请输入要生成的线数目：");
string s = Console.ReadLine();
int n = 0;
if (!Int32.TryParse(s, out n))
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine("提示：输入的数字无效，请输入有效的正整数! ");
Console.ResetColor();
Console.ReadKey();
Console.WriteLine("按任意键返回.");
DisplayMenu();
}
try
{
//清除数据
sh.Execute("delete from PolylineT");
/* 通过OGC标准的WTK 文件描述格式插入一个点记录*/
string sql;
int l = n / 1000;
for (int j = 0; j <= l; j++)
{
sh.BeginTransaction();
int t = (n < (j + 1) * 1000 ? n : (j + 1) * 1000);
for (int i = j * 1000; i < t; i++)
{
sql = String.Format("INSERT INTO PolylineT(PK_UID, name, GEOMETRY) "
+ "VALUES ({0}, 'line{1}', GeomFromText('LINESTRING({2})',2346))"
, i, i, CreatePolyline());
sh.Insert(sql);
//sh.Execute(sql);
OverWrite("当前进度：" + i * 100 / n + "%");
}
sh.Commit();
}
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("#错误：" + ex.Message);
Console.ResetColor();
}
finally
{
Console.WriteLine("按任意键返回.");
Console.ReadKey();
DisplayMenu();
}
}
public static void GetorgLog(Dictionary <string, object> org_Log, string orderId, string txnTime, string queryId, ref string errormessage)
{
using (SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\DataBase\TransLog.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
DataTable dt;
if (queryId.Equals(""))
{
dt = sh.Select("select * from Log where txnTime = @txnTime", new SQLiteParameter[] {
new SQLiteParameter("@txnTime", txnTime),
});
}
else
{
dt = sh.Select("select * from Log where queryId = @queryId", new SQLiteParameter[] {
new SQLiteParameter("@queryId", queryId)
});
}
sh.Commit();
org_Log.Add("queryId", dt.Rows[0]["queryId"]);
org_Log.Add("messageName", dt.Rows[0]["messageName"]);
org_Log.Add("traceNo", dt.Rows[0]["traceNo"]);
org_Log.Add("orderId", dt.Rows[0]["orderId"]);
org_Log.Add("txnType", dt.Rows[0]["txnType"]);
org_Log.Add("txnSubType", dt.Rows[0]["txnSubType"]);
org_Log.Add("txnTime", dt.Rows[0]["txnTime"]);
org_Log.Add("txnAmt", dt.Rows[0]["txnAmt"]);
org_Log.Add("qrCode", dt.Rows[0]["qrCode"]);
org_Log.Add("messageResult", dt.Rows[0]["messageResult"]);
}
catch (Exception e)
{
sh.Rollback();
errormessage += "查询不到原交易，请=确认原交易信息是否正确";
}
//var dic1 = new Dictionary<string, object>();
//dic1["orderId"] = "20";
//DataTable dt = sh.Select("select * from Log where orderId = @orderId", dic1);
conn.Close();
}
}
}
private string caijingwang()
{
string url = "https://zst.cjcp.com.cn/cjwk3/view/kuai3_zonghe-jilin-3-70000.html";
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
req.Method = "GET";
req.AllowAutoRedirect = false;
req.ContentType = "application/x-www-form-urlencoded";
HttpWebResponse res = (HttpWebResponse)req.GetResponse();
string html = new StreamReader(res.GetResponseStream()).ReadToEnd();
//return html;
//Regex reg = new Regex(@"\d{8,}</td><td class='z_bg_13'>\d{3,}");
//Match match = reg.Match(html);
string pattern = @"\d{8,}</td><td class='z_bg_13'>\d{3,}";
MatchCollection result = Regex.Matches(html, pattern);
//birthdayMonthCB.SelectedValue = match.Groups[1].Value;
//birthdayDayCB.SelectedValue = match.Groups[2].Value;
//MessageBox.Show(match.Groups[10].Value);
//MessageBox.Show(match.Groups[1].Value);
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
conn.Open();
cmd.Connection = conn;
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
foreach (var str in result)
{
string[] sArray = Regex.Split(str.ToString(), "</td><td class='z_bg_13'>", RegexOptions.IgnoreCase);
// MessageBox.Show(sArray[0]);
//MessageBox.Show(sArray[1]);
string qh = sArray[0].ToString();
string jh = sArray[1].ToString();
sh.Insert("fcjlk3", initDic(qh, jh));
// Core.SqlAction.AddH(initDic(qh, jh));
}
sh.Commit();
conn.Close();
}
}
return(html);
}
private void Form1_Load(object sender, EventArgs e)
{
using (SQLiteConnection conn = new SQLiteConnection(@"data source=.\CloudAgent.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try {
DataTable dt = sh.Select("select * from setting where key='interval';");
if (dt.Rows.Count > 0)
{
textBox5.Text = dt.Rows[0]["value"].ToString();
}
DataTable dt2 = sh.Select("select * from setting where key='timeout';");
if (dt2.Rows.Count > 0)
{
textBox6.Text = dt2.Rows[0]["value"].ToString();
}
DataTable dt3 = sh.Select("select * from server ;");
if (dt3.Rows.Count > 0)
{
string cosip = dt3.Rows[0]["ip"].ToString();
string[] s = cosip.Split(new char[] { '.' });
textBox1.Text = s[0];
textBox2.Text = s[1];
textBox3.Text = s[2];
textBox4.Text = s[3];
for (int i = 0; i < dt3.Rows.Count; i++)
{
comboBox1.Items.Add(dt3.Rows[i]["ip"].ToString());
}
}
sh.Commit();
}
catch
{
sh.Rollback();
}
conn.Close();
}
}
}
private void button保存_Click(object sender, EventArgs e)
{
if (MetroMessageBox.Show(this, "确定把这些项替换数据库里的内容？", "", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.OK)
{
using (SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\DataBase\TestCase.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
string sql_update = string.Format("Update {0} Set Deleting = 1", Database);
sh.Execute(sql_update);
sh.Commit();
}
catch
{
sh.Rollback();
}
foreach (ListViewItem item in this.listView案例.Items)
{
string sql = string.Format("Insert Into " + Database + " (ID, ParentID, DisplayOrder, NodeText, [Character], Deleting) Values ({0},{1},{2},'{3}','{4}',0)",
int.Parse(item.SubItems[0].Text),
int.Parse(item.SubItems[1].Text),
int.Parse(item.SubItems[2].Text),
item.SubItems[3].Text,
item.SubItems[4].Text);
sh.Execute(sql);
}
string sql_delete = string.Format("delete from {0} where Deleting = 1", Database);
sh.Execute(sql_delete);
conn.Close();
}
}
MetroMessageBox.Show(this, "写入成功！");
}
}
public void ReinitializeDatabase(string subscriberId)
{
//getting data from server
var request = new RestRequest("InitializeSubscriber/{subscriberUUID}", Method.GET);
request.AddUrlSegment("subscriberUUID", subscriberId);
request.AddHeader("Accept", "*/*");
IRestResponse response = wsClient.Execute(request);
Dictionary <string, string> dbSchema = JsonConvert.DeserializeObject <Dictionary <string, string> >(response.Content);
//we need to sort by key
var tmp = dbSchema.OrderBy(key => key.Key);
var dbSchemaSorted = tmp.ToDictionary((keyItem) => keyItem.Key, (valueItem) => valueItem.Value);
using (SQLiteConnection conn = new SQLiteConnection(this.connString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
foreach (KeyValuePair <string, string> entry in dbSchemaSorted)
{
if (!entry.Key.StartsWith("00000"))
{
sh.Execute(entry.Value);
}
}
sh.Commit();
}
catch (Exception ex)
{
sh.Rollback();
throw ex;
}
conn.Close();
}
}
}
public static void CreateUser()
{
string path = Path.Combine(Application.StartupPath, "Data", "alo_users.xls");
//DataTable dt = util.Excel_To_DataTable(path,0);
DataTable dt = util.GetRequestsDataFromExcel(path);
SQLiteHelper sh = new SQLiteHelper();
sh = sh.Init();
sh.BeginTransaction();
foreach (DataRow dr in dt.Rows)
{
string username = dr["HoTen"].ToString().Replace("'", "");
username = fullname2username(username).ToLower();
string mail = username + "@alobhxh.com";
string birthDay = dr["NgaySinh"].ToString();
//
//
if (Convert.ToInt32(sh.ExecuteScalar("SELECT COUNT(*) FROM USERS WHERE USERNAME = '******'")) > 0)
{
continue;
}
//
userInfo user = new userInfo();
user.Username = username.Replace("'", "");
user.Birthday = birthDay;
user.Fullname = dr["HoTen"].ToString().Replace("'", "");
user.Address = dr["DiaChi"].ToString().Replace("'", "");
user.Sex = dr["GioiTinh"].ToString().Replace("'", "");
//
sh.Insert("USERS", user.ToDictionary());
//
}
sh.Commit();
sh.Close();
}
public static DataTable GetTestCase(string tableName)
{
DataTable dt = new DataTable();
using (SQLiteConnection conn = new SQLiteConnection("data source=" + Application.StartupPath + @"\DataBase\TestCase.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
string sql = string.Format("Select * From {0}", tableName);
dt = Global.Parameters.dtPOSTestCase = sh.Select(sql);
sh.Commit();
}
catch
{
sh.Rollback();
}
//var dic1 = new Dictionary<string, object>();
//dic1["orderId"] = "20";
//DataTable dt = sh.Select("select * from Log where orderId = @orderId", dic1);
conn.Close();
}
}
return(dt);
}
public void ReinitializeDatabase(string subscriberId)
{
Dictionary <string, string> dbSchema = JsonConvert.DeserializeObject <Dictionary <string, string> >(wsClient.GetFullDBSchema(JsonConvert.SerializeObject(subscriberId)));
using (SQLiteConnection conn = new SQLiteConnection(this.connString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
foreach (KeyValuePair <string, string> entry in dbSchema)
{
if (!entry.Key.StartsWith("00000"))
{
sh.Execute(entry.Value);
}
}
sh.Commit();
}
catch (Exception ex)
{
sh.Rollback();
throw ex;
}
conn.Close();
}
}
}
public void Post2Site_Alo(List <postInfo> posts, string categoryId)
{
//return;
SQLiteHelper sh = new SQLiteHelper();
sh = sh.Init();
sh.BeginTransaction();
//Luu DB local - SQLLITE, de check trung
//string root = "http://localhost:4567/api/v2";
string root = "https://alobhxh.com/api/v2";
string postUrl = "/topics";
string replyUrl = "";
//string apiKey = "Bearer 94cdf3b2-7b9c-4c67-9b98-e0e20682a5fb"; //Master
string apiKey = String.Format("Bearer {0}", Program.API_MASTER_KEY);// 15baf25c-5d82-47c8-a660-71b4abb7b675"; //Master
//string apiKey = "Bearer 02ca7aea-28e5-4f04-b6c6-02861c1c6d36";
//string apiKey = "Bearer f440c24f-a7b1-401e-a788-31c1dc1df19c";
string cid = categoryId, title = "", content = "";
string url = root + postUrl;
foreach (postInfo post in posts)
{
string sqlExist = "SELECT COUNT(*) FROM POSTS WHERE siteId = '" + post.SiteId + "' AND postId='" + post.PostId + "'";
if (post.PostId == "0" || post.PostId == "")
{
sqlExist = "SELECT COUNT(*) FROM POSTS WHERE siteId = '" + post.SiteId + "' AND title='" + post.Title + "'";
}
if (Convert.ToInt32(sh.ExecuteScalar(sqlExist)) > 0)
{
continue;
}
//1. Insert local DB
sh.Insert("POSTS", post.ToDictionary());
if (post.SiteId == "bhxhhn.com.vn" && Program.lstExistPost != null)
{
string checkExist = String.Format("{0}|{1}", post.CategoryId, post.Title);
if (Program.lstExistPost.Contains(checkExist))
{
continue;
}
}
//2. Post site
//string strData = "api_key=" + apiKey + "&api_username="******"&title=" + post.Title + "&category=" + category + "&raw=" + post.Content + "";
try
{
var converter = new Converter();
JObject o = new JObject();
o["cid"] = categoryId;
o["title"] = post.Title;
//
string contentMD = post.Content;
contentMD = converter.Convert(contentMD).Trim();
contentMD = contentMD.Replace("\\r\\n\\", "");
//
string contentRepMD = post.ContentReply;
if (!String.IsNullOrEmpty(contentRepMD))
{
contentRepMD = converter.Convert(contentRepMD).Trim();
contentRepMD = contentRepMD.Replace("\\r\\n\\", "");
}
//Nếu bài dài quá, thì cắt làm 2
if (contentMD.Length > 32000 && String.IsNullOrEmpty(post.ContentReply))
{
string initContent = contentMD;
contentMD = initContent.Substring(0, 32000);
contentRepMD = initContent.Substring(32001, 32000);
}
o["content"] = contentMD;
string userId = userhelper.GetRandomUID(post.SiteId);
o["_uid"] = userId;
//var jsonContent = String.Format("{'cid': '{0}','title': '{1}', 'content': '{2}'}", cid, post.Title, post.Content);
string jsonContent = o.ToString();
JObject obj = util.HTTP_POST_JSON(url, apiKey, jsonContent);
if (obj != null && obj["code"].ToString() == "ok" && !String.IsNullOrEmpty(contentRepMD))
{
string tId = obj["payload"]["topicData"]["tid"].ToString();
if (!String.IsNullOrEmpty(tId))
{
string url2 = url + "/" + tId;
o = new JObject();
//contentMD = post.ContentReply;
//
//contentMD = converter.Convert(post.ContentReply);
o["content"] = contentRepMD.Trim();
o["_uid"] = userhelper.GetRandomUIDReply(post.SiteId, userId);
jsonContent = o.ToString();
//
if (!String.IsNullOrEmpty(contentRepMD))
{
string modAPI = apiKey;
obj = util.HTTP_POST_JSON(url2, modAPI, jsonContent);
if (obj["code"].ToString() == "ok")
{
Console.WriteLine("OK: " + post.Title);
}
}
}
}
}
catch (Exception ex)
{
string s = ex.ToString();
}
}
sh.Commit();
sh.Close();
}
private void GetChangesFromServer(string subscriberId)
{
using (SQLiteConnection conn = new SQLiteConnection(this.connString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
DataTable tables = sh.Select("select tbl_Name from sqlite_master where type='table';");
foreach (DataRow table in tables.Rows)
{
try
{
sh.BeginTransaction();
var request = new RestRequest("Sync/{subscriberUUID}/{tableName}", Method.GET);
request.AddUrlSegment("subscriberUUID", subscriberId);
request.AddUrlSegment("tableName", table["tbl_Name"].ToString());
request.AddHeader("Accept", "*/*");
IRestResponse response = wsClient.Execute(request);
List <DataObject> tablesData = JsonConvert.DeserializeObject <List <DataObject> >(response.Content);
foreach (DataObject tableData in tablesData)
{
if (tableData.SyncId > 0)
{
sh.Execute(tableData.TriggerDeleteDrop);
sh.Execute(tableData.TriggerInsertDrop);
sh.Execute(tableData.TriggerUpdateDrop);
XmlDocument xmlRecords = new XmlDocument();
xmlRecords.LoadXml(tableData.Records);
XPathNavigator oRecordsPathNavigator = xmlRecords.CreateNavigator();
XPathNodeIterator oRecordsNodesIterator = oRecordsPathNavigator.Select("/records/r");
foreach (XPathNavigator oCurrentRecord in oRecordsNodesIterator)
{
string action = oCurrentRecord.GetAttribute("a", "");
XmlDocument xmlRecord = new XmlDocument();
xmlRecord.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\"?><columns>" + oCurrentRecord.InnerXml + "</columns>");
XPathNavigator oColumnsPathNavigator = xmlRecord.CreateNavigator();
XPathNodeIterator oColumnsNodesIterator = oColumnsPathNavigator.Select("/columns/c");
int coumnsCount = oColumnsNodesIterator.Count;
SQLiteParameter[] parameters = new SQLiteParameter[coumnsCount];
int idx = 0;
foreach (XPathNavigator oCurrentColumn in oColumnsNodesIterator)
{
SQLiteParameter parameter = new SQLiteParameter();
parameter.Value = oCurrentColumn.InnerXml;
parameters[idx] = parameter;
idx++;
}
switch (action)
{
case "1": //insert
sh.Execute(tableData.QueryInsert, parameters);
break;
case "2": //update
sh.Execute(tableData.QueryUpdate, parameters);
break;
case "3": //delete
sh.Execute(tableData.QueryDelete, parameters);
break;
}
}
sh.Execute(tableData.TriggerDelete);
sh.Execute(tableData.TriggerInsert);
sh.Execute(tableData.TriggerUpdate);
request = new RestRequest("CommitSync/{syncId}", Method.GET);
request.AddUrlSegment("syncId", tableData.SyncId.ToString());
request.AddHeader("Accept", "*/*");
IRestResponse responseCommit = wsClient.Execute(request);
}
}
sh.Commit();
}
catch (Exception ex)
{
sh.Rollback();
throw ex;
}
}
conn.Close();
}
}
}
private void GetChangesFromServer(string subscriberId)
{
using (SQLiteConnection conn = new SQLiteConnection(this.connString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
DataTable tables = sh.Select("select tbl_Name from sqlite_master where type='table';");
foreach (DataRow table in tables.Rows)
{
try
{
sh.BeginTransaction();
List<DataObject> tablesData = JsonConvert.DeserializeObject<List<DataObject>>(wsClient.GetDataForSync(JsonConvert.SerializeObject(subscriberId), JsonConvert.SerializeObject(table["tbl_Name"].ToString())));
foreach (DataObject tableData in tablesData)
if (tableData.SyncId > 0)
{
sh.Execute(tableData.TriggerDeleteDrop);
sh.Execute(tableData.TriggerInsertDrop);
sh.Execute(tableData.TriggerUpdateDrop);
XmlDocument xmlRecords = new XmlDocument();
xmlRecords.LoadXml(tableData.Records);
XPathNavigator oRecordsPathNavigator = xmlRecords.CreateNavigator();
XPathNodeIterator oRecordsNodesIterator = oRecordsPathNavigator.Select("/records/r");
foreach (XPathNavigator oCurrentRecord in oRecordsNodesIterator)
{
string action = oCurrentRecord.GetAttribute("a", "");
XmlDocument xmlRecord = new XmlDocument();
xmlRecord.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\"?><columns>" + oCurrentRecord.InnerXml + "</columns>");
XPathNavigator oColumnsPathNavigator = xmlRecord.CreateNavigator();
XPathNodeIterator oColumnsNodesIterator = oColumnsPathNavigator.Select("/columns/c");
int coumnsCount = oColumnsNodesIterator.Count;
SQLiteParameter[] parameters = new SQLiteParameter[coumnsCount];
int idx = 0;
foreach (XPathNavigator oCurrentColumn in oColumnsNodesIterator)
{
SQLiteParameter parameter = new SQLiteParameter();
parameter.Value = oCurrentColumn.InnerXml;
parameters[idx] = parameter;
idx++;
}
switch (action)
{
case "1"://insert
sh.Execute(tableData.QueryInsert, parameters);
break;
case "2"://update
sh.Execute(tableData.QueryUpdate, parameters);
break;
case "3"://delete
sh.Execute(tableData.QueryDelete, parameters);
break;
}
}
sh.Execute(tableData.TriggerDelete);
sh.Execute(tableData.TriggerInsert);
sh.Execute(tableData.TriggerUpdate);
wsClient.SyncCompleted(JsonConvert.SerializeObject(tableData.SyncId));
}
sh.Commit();
}
catch (Exception ex)
{
sh.Rollback();
throw ex;
}
}
conn.Close();
}
}
}
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();
}
}
}
public void TestDB()
{
var path = Environment.CurrentDirectory;
var fileName = path + "\\testDB.db";
string databaseFileName = fileName;
string connectionString = "data source = " + databaseFileName;
//Sqlite使用事务批量操作 极大的提高速度
DateTime starttime = DateTime.Now;
using (SQLiteConnection conn = new SQLiteConnection(connectionString, true))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try
{
// INSERT.....
// INSERT.....
// UPDATE....
// ... skip for another 50,000 queries....
// DELETE....
// UPDATE...
// INSERT.....
// do something...
#region Select
//var dic = new Dictionary<string, object>();
//dic["@aaa"] = "PM1825H10054030";
//DataTable dt = sh.Select("select * from SN where sn = @aaa", dic);
//string strName = string.Empty;
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// strName = dt.Rows[i]["sn"].ToString();
// Control.LogHelper.WriteLog(strName);
//}
//sh.Commit();//提交事务
//DateTime endtime = DateTime.Now;
//MessageBox.Show("Select成功，用时" + (endtime - starttime).TotalMilliseconds + strName);
#endregion
#region Insert
//sn Table
//var dic = new Dictionary<string, object>();
//for (int i = 0; i < 100000; i++)
//{
// dic["sn"] = "PM1825H10" + string.Format("{0:d6}", i);
// dic["time"] = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
// sh.Insert("SN", dic);
//}
//sh.Commit();//提交事务
//DateTime endtime = DateTime.Now;
//MessageBox.Show("插入成功，用时" + (endtime - starttime).TotalMilliseconds);
//Edition Table
//var dic = new Dictionary<string, object>();
//for (int i = 0; i < 2; i++)
//{
// dic["editionID"] = i;
// dic["editionDesc"] = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
// sh.Insert("Edition", dic);
//}
//sh.Commit();//提交事务
//DateTime endtime = DateTime.Now;
//MessageBox.Show("插入成功，用时" + (endtime - starttime).TotalMilliseconds);
#endregion
}
catch
{
sh.Rollback();
}
conn.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 Form1_Load(object sender, EventArgs e)
{
using (SQLiteConnection conn = new SQLiteConnection(@"data source=.\CloudAgent.db"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
sh.BeginTransaction();
try {
DataTable dt = sh.Select("select * from setting where key='interval';");
if (dt.Rows.Count > 0)
{
textBox5.Text = dt.Rows[0]["value"].ToString();
}
DataTable dt2 = sh.Select("select * from setting where key='timeout';");
if (dt2.Rows.Count > 0)
{
textBox6.Text = dt2.Rows[0]["value"].ToString();
}
DataTable dt3 = sh.Select("select * from server ;");
if (dt3.Rows.Count > 0)
{
string cosip = dt3.Rows[0]["ip"].ToString();
string[] s = cosip.Split(new char[] { '.' });
textBox1.Text = s[0];
textBox2.Text = s[1];
textBox3.Text = s[2];
textBox4.Text = s[3];
for (int i=0;i< dt3.Rows.Count;i++)
{
comboBox1.Items.Add(dt3.Rows[i]["ip"].ToString());
}
}
sh.Commit();
}
catch
{
sh.Rollback();
}
conn.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();
}
public void Post2Site(List <postInfo> posts)
{
SQLiteHelper sh = new SQLiteHelper();
sh = sh.Init();
sh.BeginTransaction();
//Luu DB local - SQLLITE, de check trung
string strUrrl = "https://bds5s.com/posts";
string apiKey = "a82841644696ebb6381bd74b41c81fb4d5bddc0b91de59a932da5a9fb87ce9be";
string user = "", catid = "", category = "";
foreach (postInfo post in posts)
{
if (Convert.ToInt32(sh.ExecuteScalar("SELECT COUNT(*) FROM POSTS WHERE title='" + post.Title + "'")) > 0)
{
continue;
}
//1. Insert local DB
sh.Insert("POSTS", post.ToDictionary());
//2. Post site
string purl = util.RemoveVietnamese(post.Province);
if (purl.Equals("ha-noi"))
{
user = "******";
catid = util.getQuanHuyen(util.RemoveVietnamese(post.District));
if (catid.Equals(""))
{
category = "5";
}
else
{
category = catid;
}
//
}
else if (purl.Equals("ho-chi-minh"))
{
user = "******";
catid = util.getQuanHuyen(util.RemoveVietnamese(post.District));
if (catid.Equals(""))
{
category = "35";
}
else
{
category = catid;
}
}
else if (purl.Equals("da-nang"))
{
user = "******";
catid = util.getQuanHuyen(util.RemoveVietnamese(post.District));
if (catid.Equals(""))
{
category = "61";
}
else
{
category = catid;
}
}
if (!category.Equals(""))
{
string api_username = user;
string strData = "api_key=" + apiKey + "&api_username="******"&title=" + post.Title + "&category=" + category + "&raw=" + post.Content + "";
util.HTTP_POST(strUrrl, strData);
}
}
sh.Commit();
sh.Close();
}
