public static void LoadLanguageLinkData(ReadOnlyCollection<IWebElement> weListPrimary) { using (SQLiteConnection conn = new SQLiteConnection("data source=C:\\Users\\Don\\Music\\Documents\\SeleniumProjects\\SeleniumDemoCSharp\\SeleniumDemoTests\\src\\selenium\\site\\wikipedia\\wikidata\\wikidata.db")) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); Debug.WriteLine(sh.ExecuteScalar("select Title from ResultLanguageLinks;")); conn.Close(); } } Debug.WriteLine("============================================"); foreach (IWebElement we in weListPrimary) { Debug.WriteLine("id=" + we.GetAttribute("id")); Debug.WriteLine("class=" + we.GetAttribute("class")); Debug.WriteLine("lang=" + we.GetAttribute("lang")); Debug.WriteLine("dir=" + we.GetAttribute("dir")); Debug.WriteLine("title=" + we.GetAttribute("title")); Debug.WriteLine("href=" + we.GetAttribute("href")); Debug.WriteLine("data-convert-hans=" + we.GetAttribute("data-convert-hans")); Debug.WriteLine("Text=" + we.Text); Debug.WriteLine("============================================"); } }
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(); } } }
private int SaveOrder(string Production, int OrderQty, string Consignee, string Phone, string sheng, string shi, string xian, string Address, string Description) { int vcount = 0; using (SQLiteConnection conn = new SQLiteConnection(config.DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.SetPassword(config.DBPwd); conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); var dic = new Dictionary<string, object>(); dic["Production"] = Production; dic["OrderQty"] = OrderQty; dic["Consignee"] = Consignee; dic["Phone"] = Phone; dic["sheng"] = sheng; dic["shi"] = shi; dic["xian"] = xian; dic["Address"] = Address; dic["Description"] = Description; vcount = sh.Insert("T_Order", dic); conn.Close(); } } return vcount; }
// http://stackoverflow.com/questions/7002806/system-data-datasetextensions-in-mono public static IEnumerable<DataPoint> LoadData(string dataSource, string tableName) { try { string sql = String.Format("select * from {0}", tableName) ; using (SQLiteConnection conn = new SQLiteConnection(dataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); DataTable res = sh.Select(sql); IEnumerable<DataPoint> ms = from row in res.AsEnumerable() orderby row["responsetime"] select new DataPoint(Convert.ToDouble(row["count"]), Convert.ToDouble(row["responsetime"])); ; conn.Close(); return ms; } } } catch (Exception ex) { Console.Error.WriteLine(ex.ToString()); return (IEnumerable<DataPoint>)null; } }
private void frmAtleta_Load(object sender, EventArgs e) { tbAtletas listanomes = new tbAtletas.getNomes(); try { using (SQLiteConnection gatlDBconn = new SQLiteConnection("data source=C:\\Users\\Mario Lima\\Documents\\Visual Studio 2015\\Projects\\GestorAtletismo_v2\\GestorAtletismo_v2\\gestatl.db3")) { using (SQLiteCommand comando = new SQLiteCommand()) { comando.Connection = gatlDBconn; gatlDBconn.Open(); SQLiteHelper sh = new SQLiteHelper(comando); DataTable Atletas = sh.Select("SELECT nome_atleta FROM atleta ORDER BY nome_atleta;"); foreach (DataRow linha in Atletas.Rows) { foreach (var campo in linha.ItemArray) { lstboxAtletas.Items.Add(campo.ToString()); } } } } } catch (Exception erro) { MessageBox.Show("Erro: " + erro.ToString()); } }
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture) { using (SQLiteConnection conn = new SQLiteConnection("StaticConfig.DataSource")) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); int countCurrent = 0; string sqlCommandCurrent = String.Format("select count(*) from QuestionInfo where Q_Num={0};", value);//判断当前题号是否存在 try { Int32.Parse((string)value); countCurrent = sh.ExecuteScalar<int>(sqlCommandCurrent); conn.Close(); if (countCurrent > 0) { return "更新"; } else { return "保存"; } } catch (Exception) { return "保存"; } } } }
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; } }
/// <summary> /// 判断库是否为加密库 /// </summary> /// <param name="db"></param> /// <returns></returns> private bool CheckLegacyDBIsKeyed(SQLiteHelper keyedDB) { try { keyedDB.ExecuteNonQuery("PRAGMA user_version;", ConnectionState.KeepOpen); return true; } catch { return false; } }
/// <summary> /// 打开数据库 /// </summary> public static void OpenDB(){ conn = new SQLiteConnection(config.DataSource);//连接数据库,config.DataSource数据库连接字符串 Log("dbpath:" + config.DataSource); //打印日志 cmd = new SQLiteCommand(); cmd.Connection = conn; if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); Log("conn_open"); } sqliteHelper = new SQLiteHelper(cmd); Log("new sqliteHelper"); }
public string GetOrderList(int pageRows,int page) { Dictionary<string, object> pageResult = new Dictionary<string, object>(); using (SQLiteConnection conn = new SQLiteConnection(config.DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.SetPassword(config.DBPwd); conn.Open(); //conn.ChangePassword(config.DBPwd); SQLiteHelper sh = new SQLiteHelper(cmd); //总记录数 string totalSql = "Select count(1) From T_order"; long total = (long)sh.ExecuteScalar(totalSql); int offset = (page - 1) * pageRows; string sql = string.Format(@"SELECT ID,[Production] ,[Consignee] ,[Phone] ,[sheng] ,[shi] ,[xian] ,[Address] ,[Description], case when status = 1 then '√' else ' ' end as status, BuildTime FROM [T_Order] Order by Status asc ,buildTime desc limit {0} offset {1} ",pageRows,offset); DataTable dt = sh.Select(sql); conn.Close(); pageResult.Add("total", total); pageResult.Add("rows", dt); } } return JSON.Encode(pageResult); }
public void DeleteQuery(string Table, string Id) { using (SQLiteConnection conn = new SQLiteConnection("data source=" + DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); sh.Execute("Delete from " + Table + " WHERE ID = '" + Id + "';"); conn.Close(); } } }
public void ExecuteSQL(string ExecuteScript) { using (SQLiteConnection conn = new SQLiteConnection("data source=" + DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); sh.Execute(ExecuteScript + ";"); conn.Close(); } } }
/// <summary> /// 递归列举出指定目录的所有文件 /// </summary> public void ListFiles(FileSystemInfo info) { { if (!info.Exists) return; DirectoryInfo dir = info as DirectoryInfo; //不是目录 if (dir == null) return; FileSystemInfo[] files = dir.GetFileSystemInfos(); for (int i = 0; i < files.Length; i++) { FileInfo file = files[i] as FileInfo; //是文件 if (file != null) { if ((file.FullName.Substring(file.FullName.LastIndexOf(".")) == ".db")) { Config.DatabaseFile = file.FullName; this.statusStrip1.Text = Config.DataSource; try { 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(); dataGridView1.DataSource = dt; conn.Close(); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } //else //{ // ListFiles(files[i]); //} } } }
protected void btnOK_Click(object sender, EventArgs e) { string loginName = txtUser.Text; string pwd = txtPwd.Text; int count = 0; using (SQLiteConnection conn = new SQLiteConnection(config.DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.SetPassword(config.DBPwd); conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); Dictionary<string,object> para = new Dictionary<string,object>(); para.Add("@LoginName",loginName); para.Add("@PassWord",pwd); count = sh.ExecuteScalar<int>("select count(1) from T_User Where LoginName=@LoginName And PassWord=@PassWord;",para); conn.Close(); } } if (count > 0) { FormsAuthentication.SetAuthCookie(loginName, false); if(string.IsNullOrEmpty(Request["ReturnUrl"])) { Response.Redirect("~/Admin/Order.aspx"); } else { Response.Redirect(Request["ReturnUrl"]); } System.Web.Security.FormsAuthentication.RedirectFromLoginPage(loginName, false); } else { Response.Write("<script>alert('用户名或密码错误!')</script>"); } }
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(); } } }
/// <summary> /// 处理遗留版本动作 /// </summary> protected override void OnDoLegacy() { //======处理数据库加密, 转库操作====== var isKeyDB = true; //1.判断库是否为加密库 using (var db = new SQLiteHelper(SQLConfig.ConnectionString)) { isKeyDB = CheckLegacyDBIsKeyed(db); } //2.如果为不加密的库设置密码 if (!isKeyDB) { using (var db = new SQLiteHelper(SQLConfig.NormalConnectionString)) { var conn = db.DbConnection as SQLiteConnection; conn.Open(); conn.ChangePassword(SQLConfig.DBConnectionKey); } } }
/// <summary> /// 验证lib文件是不是一个有效的库文件 /// </summary> /// <param name="DataSource"></param> /// <returns></returns> public static bool Validate(string DataSource) { using (SQLiteConnection conn = new SQLiteConnection(DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); try { DataTable dt = sh.Select("select * from QuestionInfo"); return true; } catch (Exception) { return false; } } } }
// http://stackoverflow.com/questions/7002806/system-data-datasetextensions-in-mono public static IEnumerable<DataPoint> LoadData(string dataSource, string tableName) { try { string sql = String.Format("select * from {0}", tableName) ; using (SQLiteConnection conn = new SQLiteConnection(dataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); DataTable res = sh.Select(sql); IEnumerable<DataPoint> ms = from row in res.AsEnumerable() orderby row["responsetime"] select new DataPoint(Convert.ToDouble(row["count"]), Convert.ToDouble(row["responsetime"])); ; // DataSetExtensions is not available for some reason, loop through the data /* */ List<DataPoint> ms2 = new List<DataPoint>(); foreach (DataRow row in res.Rows) { ms2.Add(new DataPoint(Convert.ToDouble(row["count"]), Convert.ToDouble(row["responsetime"]))); } conn.Close(); return ms; } } } catch (Exception ex) { Console.Error.WriteLine(ex.ToString()); return (IEnumerable<DataPoint>)null; } }
public static bool InsertPlotData(string dataSource, string tableName, Dictionary<string, object> dic) { try { using (SQLiteConnection conn = new SQLiteConnection(dataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); sh.Insert(tableName, dic); conn.Close(); return true; } } } catch (Exception ex) { Console.Error.WriteLine(ex.ToString()); return false; } }
/// <summary> /// 此处设置datasource较为方便所以不再调整 /// </summary> /// <param name="sender">事件触发者</param> /// <param name="e">jsonArgs</param> /// <returns></returns> public bool query(object sender, EventArgs e) { DataTable dt; using (SQLiteConnection conn = new SQLiteConnection(string.Format("data source={0}",DataBase.Replace(@"\", @"/")))) { using (SQLiteCommand cmd = new SQLiteCommand()) { conn.Open(); cmd.Connection = conn; SQLiteHelper sh = new SQLiteHelper(cmd); try { dt = sh.Select(((jsonArges)e).querystr); ((jsonArges)e).dt= dt; } catch (Exception ex) { dt = new DataTable(); dt.Columns.Add("Error"); dt.Rows.Add(ex.ToString()); ((jsonArges)e).dt= dt; } } } foreach (DataRow i in dt.Rows) ((jsonArges)e).logp.Add( new geopoint( i.ItemArray[1].ToString(), double.Parse(i.ItemArray[2].ToString()), double.Parse(i.ItemArray[3].ToString()) )); return true; }
public int DeleteOrder(string orderId) { int count = 0; using (SQLiteConnection conn = new SQLiteConnection(config.DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.SetPassword(config.DBPwd); conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); string sql = @"Delete From [T_Order] Where ID = @ID"; Dictionary<string, object> para = new Dictionary<string, object>(); para.Add("@ID", orderId); count = sh.Execute(sql,para); conn.Close(); } } return count; }
public int SaveInfo(HomeModel home) { string sql = "insert into info(name, increment,distance,site,time,fet)values('" + home.Name + "','" + home.Increment + "','" + home.Distance + "','" + home.Site + "','" + home.Time + "','" + home.Fet + "')"; return(SQLiteHelper.ExecuteNonQuery(sql)); }
public QuestionInfo GetAllQuestionInfo(int questionNumber, string DataSource) { //Config.DataBaseFile = Path.Combine(System.Windows.Forms.Application.StartupPath, "MyData.db"); using (SQLiteConnection conn = new SQLiteConnection(DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); DataTable dt = sh.Select("select * from QuestionInfo where Q_Num=@questionNumber;", new SQLiteParameter[] { new SQLiteParameter("questionNumber", questionNumber) }); foreach (DataRow dr in dt.Rows) { int otherOption; int musterEnter; int repeat; int jump; QuestionInfo questionInfo = new QuestionInfo(); questionInfo.QuestionNumber = dr["Q_Num"].ToString(); questionInfo.QuestionTypeID = Convert.ToInt32(dr["TypeID"]); questionInfo.QuestionContent = dr["Q_Content"].ToString(); questionInfo.QuestionField = dr["Q_Field"].ToString(); questionInfo.QuestionLable = dr["Q_Lable"].ToString(); questionInfo.OptionsCount = dr["Q_OptionsCount"].ToString(); otherOption = Convert.ToInt32(dr["Q_OtherOption"]); if (otherOption==0) { questionInfo.OtherOption = false; } else { questionInfo.OtherOption = true; } questionInfo.ValueLable = dr["Q_ValueLable"].ToString(); questionInfo.DataTypeID = Convert.ToInt32(dr["DataTypeID"]); questionInfo.ValueRange = dr["Q_ValueRange"].ToString(); questionInfo.Pattern = dr["Q_Pattern"].ToString(); musterEnter = Convert.ToInt32(dr["Q_MustEnter"]); if (musterEnter == 0) { questionInfo.IsMustEnter = false; } else { questionInfo.IsMustEnter = true; } repeat = Convert.ToInt32(dr["Q_Repeat"]); if (repeat == 0) { questionInfo.IsRepeat = false; } else { questionInfo.IsRepeat = true; } jump = Convert.ToInt32(dr["Q_Jump"]); if (jump == 0) { questionInfo.IsJump = false; } else { questionInfo.IsJump = true; } questionInfo.JumpConditions = dr["Q_JumpConditions"].ToString(); questionInfo.JumpTarget = dr["Q_JumpTarget"].ToString(); questionInfo.DataBaseFile = DataSource.Substring(12); //if (DataSource == string.Format("data source={0}", Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), "MyData.db"))) //{ int count = 0; string sqlString = String.Format("select count(*) from QuestionInfo where Q_Field='{0}' COLLATE NOCASE", questionInfo.QuestionField.ToLower());//判断当前字段是否存在,注意字段值一定要加单引号 count = sh.ExecuteScalar<int>(sqlString); if (count==1) { IsQuestionFieldExist.Instance.IsExist = true; } else { IsQuestionFieldExist.Instance.IsExist = false; } //} return questionInfo; } conn.Close(); return null; } } }
/// <summary> /// 更新一条数据 /// </summary> public void Update(tdps.Model.NSRXXDS model, object trans) { StringBuilder strSql = new StringBuilder(); strSql.Append("update NSRXXDS set "); strSql.Append("SSHY=@SSHY,"); strSql.Append("SSYH=@SSYH,"); strSql.Append("QYDJZCL=@QYDJZCL,"); strSql.Append("NSLX=@NSLX,"); strSql.Append("SFTYHSJG=@SFTYHSJG,"); strSql.Append("ZGSWJG=@ZGSWJG,"); strSql.Append("ZGSWJG_MC=@ZGSWJG_MC,"); strSql.Append("NSR_SWJG_DM=@NSR_SWJG_DM,"); strSql.Append("JKBZ=@JKBZ,"); strSql.Append("XY=@XY,"); strSql.Append("LSGX=@LSGX,"); strSql.Append("NSRBM=@NSRBM,"); strSql.Append("NSRZT=@NSRZT,"); strSql.Append("ZGY=@ZGY"); strSql.Append("SSHY_MC=@SSHY_MC,"); strSql.Append("QYDJZCL_MC=@QYDJZCL_MC,"); strSql.Append("LSGX_MC=@LSGX_MC"); strSql.Append(" where NSRSBH=@NSRSBH "); SQLiteParameter[] parameters = { new SQLiteParameter("@NSRSBH", DbType.String, 20), new SQLiteParameter("@SSHY", DbType.String, 60), new SQLiteParameter("@SSYH", DbType.String, 50), new SQLiteParameter("@QYDJZCL", DbType.String, 60), new SQLiteParameter("@NSLX", DbType.String, 50), new SQLiteParameter("@SFTYHSJG", DbType.Int32, 4), new SQLiteParameter("@ZGSWJG", DbType.String, 20), new SQLiteParameter("@ZGSWJG_MC", DbType.String, 100), new SQLiteParameter("@NSR_SWJG_DM", DbType.String, 20), new SQLiteParameter("@JKBZ", DbType.Int32, 4), new SQLiteParameter("@XY", DbType.String, 50), new SQLiteParameter("@LSGX", DbType.String, 100), new SQLiteParameter("@NSRBM", DbType.String, 20), new SQLiteParameter("@NSRZT", DbType.String, 2), new SQLiteParameter("@ZGY", DbType.String, 30), new SQLiteParameter("@SSHY_MC", DbType.String, 50), new SQLiteParameter("@QYDJZCL_MC", DbType.String, 50), new SQLiteParameter("@LSGX_MC", DbType.String, 50) }; parameters[0].Value = model.NSRSBH; parameters[1].Value = model.SSHY; parameters[2].Value = model.SSYH; parameters[3].Value = model.QYDJZCL; parameters[4].Value = model.NSLX; parameters[5].Value = model.SFTYHSJG; parameters[6].Value = model.ZGSWJG; parameters[7].Value = model.ZGSWJG_MC; parameters[8].Value = model.NSR_SWJG_DM; parameters[9].Value = model.JKBZ; parameters[10].Value = model.XY; parameters[11].Value = model.LSGX; parameters[12].Value = model.NSRBM; parameters[13].Value = model.NSRZT; parameters[14].Value = model.ZGY; parameters[15].Value = model.SSHY_MC; parameters[16].Value = model.QYDJZCL_MC; parameters[17].Value = model.LSGX_MC; SQLiteHelper.ExecuteNonQueryByTransaction(strSql.ToString(), (SQLiteTransaction)trans, parameters); }
/// <summary> /// 得到一个对象实体 /// </summary> public tdps.Model.SBXX GetModel(int SBXXID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select SBXXID,DLZH,NSRSBH,SZ,SBZLCODE,SBZLMC,SSQQ,SSQZ,SBBWJ,BBZT,TBSJ,SBJSZT,SLSJ,CLSJ,JSSJ,SBQNF,SBQYF,FileName,SBCS,SSQLX,SBSE,SBLSH,DYMM,SheetZT,SBFSSJ,CELLVERSION,DKFPRQQ,DKFPRQZ,SJBS,BBTXCS,CWBBFSJG,CWBB_FileName from SBXX "); strSql.Append(" where SBXXID=@SBXXID "); SQLiteParameter[] parameters = { new SQLiteParameter("@SBXXID", DbType.Int32, 4) }; parameters[0].Value = SBXXID; tdps.Model.SBXX model = new tdps.Model.SBXX(); DataSet ds = SQLiteHelper.ExecuteDataSet(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["SBXXID"].ToString() != "") { model.SBXXID = int.Parse(ds.Tables[0].Rows[0]["SBXXID"].ToString()); } model.DLZH = ds.Tables[0].Rows[0]["DLZH"].ToString(); model.NSRSBH = ds.Tables[0].Rows[0]["NSRSBH"].ToString(); model.SZ = ds.Tables[0].Rows[0]["SZ"].ToString(); model.SBZLCODE = ds.Tables[0].Rows[0]["SBZLCODE"].ToString(); model.SBZLMC = ds.Tables[0].Rows[0]["SBZLMC"].ToString(); model.SSQQ = ds.Tables[0].Rows[0]["SSQQ"].ToString(); model.SSQZ = ds.Tables[0].Rows[0]["SSQZ"].ToString(); model.SBBWJ = ds.Tables[0].Rows[0]["SBBWJ"].ToString(); model.BBZT = ds.Tables[0].Rows[0]["BBZT"].ToString(); model.TBSJ = ds.Tables[0].Rows[0]["TBSJ"].ToString(); model.SBJSZT = ds.Tables[0].Rows[0]["SBJSZT"].ToString(); model.SLSJ = ds.Tables[0].Rows[0]["SLSJ"].ToString(); model.CLSJ = ds.Tables[0].Rows[0]["CLSJ"].ToString(); model.JSSJ = ds.Tables[0].Rows[0]["JSSJ"].ToString(); model.SBQNF = ds.Tables[0].Rows[0]["SBQNF"].ToString(); model.SBQYF = ds.Tables[0].Rows[0]["SBQYF"].ToString(); model.FileName = ds.Tables[0].Rows[0]["FileName"].ToString(); model.SBFSSJ = ds.Tables[0].Rows[0]["SBFSSJ"].ToString(); if (ds.Tables[0].Rows[0]["SBCS"].ToString() != "") { model.SBCS = int.Parse(ds.Tables[0].Rows[0]["SBCS"].ToString()); } if (ds.Tables[0].Rows[0]["SSQLX"].ToString() != "") { model.SSQLX = int.Parse(ds.Tables[0].Rows[0]["SSQLX"].ToString()); } if (ds.Tables[0].Rows[0]["SBSE"].ToString() != "") { model.SBSE = decimal.Parse(ds.Tables[0].Rows[0]["SBSE"].ToString()); } model.SBLSH = ds.Tables[0].Rows[0]["SBLSH"].ToString(); model.DYMM = ds.Tables[0].Rows[0]["DYMM"].ToString(); model.SheetZT = ds.Tables[0].Rows[0]["SheetZT"].ToString(); model.CELLVERSION = ds.Tables[0].Rows[0]["CELLVERSION"].ToString(); model.DKFPRQQ = ds.Tables[0].Rows[0]["DKFPRQQ"].ToString(); model.DKFPRQZ = ds.Tables[0].Rows[0]["DKFPRQZ"].ToString(); model.SJBS = ds.Tables[0].Rows[0]["SJBS"].ToString(); model.BBTXCS = ds.Tables[0].Rows[0]["BBTXCS"].ToString(); model.CWBBFSJG = ds.Tables[0].Rows[0]["CWBBFSJG"].ToString(); model.CWBBFileName = ds.Tables[0].Rows[0]["CWBB_FileName"].ToString(); return(model); } else { return(null); } }
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 SimpleEntryApplication_Closing(object sender, System.ComponentModel.CancelEventArgs e) { string dataBaseFile = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), "MyData.db"); if (File.Exists(dataBaseFile)) { string DataSource = string.Format("data source={0}", dataBaseFile); try { using (SQLiteConnection conn = new SQLiteConnection(DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); int count = sh.ExecuteScalar<int>("select count(*) from QuestionInfo"); if (count > 0) { var DialogResult = MessageBoxPlus.Show(App.Current.MainWindow, "建库的库文件没有保存,是否返回保存?", "警告", MessageBoxButton.YesNo, MessageBoxImage.Warning); if (DialogResult == MessageBoxResult.Yes) { e.Cancel = true; } else { sh.ExecuteScalar("delete from QuestionInfo"); sh.ExecuteScalar("update sqlite_sequence SET seq = 0 where name ='QuestionInfo'"); } } } } } catch (Exception ex) { LogWritter.Log(ex,"程序退出错误"); e.Cancel = false; } } else { MessageBoxPlus.Show(App.Current.MainWindow,"丢失文件“Mydata.db”","错误",MessageBoxButton.OK,MessageBoxImage.Error); e.Cancel = false; } }
private DataTable selectFromDB(string query) { DataTable dt; using (SQLiteConnection conn = new SQLiteConnection(CONNECT)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); dt = sh.Select(query); conn.Close(); } } return dt; }
public static void querySystemDetail() { using (SQLiteCommand sqlCommand = new SQLiteCommand()) { string CommandText = "SELECT * " + "FROM zSystem_Detail "; sqlCommand.Connection = sqlConnection; sqlConnection.Open(); SQLiteHelper sqlhelper = new SQLiteHelper(sqlCommand); DataTable dt = sqlhelper.Select(CommandText); zSystemDetailModel.SYSTEM_ID = dt.Rows[0]["SYSTEM_ID"].ToString(); zSystemDetailModel.SYSTEM_NAME = dt.Rows[0]["SYSTEM_NAME"].ToString(); zSystemDetailModel.SYSTEM_FULLNAME = dt.Rows[0]["SYSTEM_FULLNAME"].ToString(); zSystemDetailModel.SYSTEM_VERSION = dt.Rows[0]["SYSTEM_VERSION"].ToString(); zSystemDetailModel.SYSTEM_VERSION_NAME = dt.Rows[0]["SYSTEM_VERSION_NAME"].ToString(); zSystemDetailModel.SYSTEM_TIMEZONE = dt.Rows[0]["SYSTEM_TIMEZONE"].ToString(); zSystemDetailModel.SYSTEM_LAST_SYNC_TIME = dt.Rows[0]["SYSTEM_LAST_SYNC_TIME"].ToString(); zSystemDetailModel.SYSTEM_LAST_SYNC_DATE = dt.Rows[0]["SYSTEM_LAST_SYNC_DATE"].ToString(); sqlConnection.Close(); /* * var myEnumerable = dt.AsEnumerable(); * * List<AccountModel> myClassList = * (from item in myEnumerable * select new AccountModel * { * account_id = item.Field<string>("account_id"), * account_name = item.Field<string>("account_name"), * account_roles = item.Field<string>("account_roles") * }).ToList(); */ } //string CommandText = "SELECT * " + // "FROM zSystem_Detail "; //sqlConnection.Open(); //sqlCommand = sqlConnection.CreateCommand(); //using (SQLiteCommand cmd = new SQLiteCommand(CommandText, DatabaseConnection.sqlConnection)) //{ // using (SQLiteDataReader rdr = cmd.ExecuteReader()) // { // while (rdr.Read()) // { // zSystemDetailModel.SYSTEM_ID = (rdr["SYSTEM_ID"].ToString()); // zSystemDetailModel.SYSTEM_NAME = (rdr["SYSTEM_NAME"].ToString()); // zSystemDetailModel.SYSTEM_FULLNAME = (rdr["SYSTEM_FULLNAME"].ToString()); // zSystemDetailModel.SYSTEM_VERSION = (rdr["SYSTEM_VERSION"].ToString()); // zSystemDetailModel.SYSTEM_VERSION_NAME = (rdr["SYSTEM_VERSION_NAME"].ToString()); // zSystemDetailModel.SYSTEM_TIMEZONE = (rdr["SYSTEM_TIMEZONE"].ToString()); // zSystemDetailModel.SYSTEM_LAST_SYNC_TIME = (rdr["SYSTEM_LAST_SYNC_TIME"].ToString()); // zSystemDetailModel.SYSTEM_LAST_SYNC_DATE = (rdr["SYSTEM_LAST_SYNC_DATE"].ToString()); // } // } //} //sqlConnection.Close(); }
public int UpdateRecord(Source item) { return(SQLiteHelper.ExecuteNonQuery(CommandType.Text, "update source set name='" + item.Name + "',othername='" + item.OtherName + "',code='" + item.Code + "' where id=" + item.ID)); }
public void EnterExecuteChanged() { try { if (OrderVersion.DefaultLot > OrderVersion.MaxLot && OrderVersion.MaxLot > 0) { MessageBox.Show("下单默认数量不能大于最大数量!", "操作提示", MessageBoxButton.OK, MessageBoxImage.Warning); return; } CommParameterSetting.StopLossModel.BuyNum = StopLossModelViewModel.BuyNum; CommParameterSetting.StopLossModel.CkBuy = StopLossModelViewModel.CkBuy; CommParameterSetting.StopLossModel.CkSell = StopLossModelViewModel.CkSell; CommParameterSetting.StopLossModel.SellNum = StopLossModelViewModel.SellNum; CommParameterSetting.StopLossModel.StopLossDelegation = StopLossModelViewModel.StopLossDelegation; CommParameterSetting.StopLossModel.StopLossPrice = StopLossModelViewModel.StopLossPrice; IniHelper.ProfileWriteValue("CheckFullStop", "BuyNum", CommParameterSetting.StopLossModel.BuyNum.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("CheckFullStop", "CkBuy", CommParameterSetting.StopLossModel.CkBuy.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("CheckFullStop", "CkSell", CommParameterSetting.StopLossModel.CkSell.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("CheckFullStop", "SellNum", CommParameterSetting.StopLossModel.SellNum.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("CheckFullStop", "StopLossDelegation", CommParameterSetting.StopLossModel.StopLossDelegation.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("CheckFullStop", "StopLossPrice", CommParameterSetting.StopLossModel.StopLossPrice.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("CheckFullStop", "StopProfitDelegation", CommParameterSetting.StopLossModel.StopProfitDelegation.ToString(), IniHelper.parameterSetting); //加载自动止盈止损 自动盈亏是用;分割行,分割列; if (Aslmvm != null) { string para = ""; lock (CommParameterSetting.AutoStopLossModel) { CommParameterSetting.AutoStopLossModel.Clear(); } List <string> AgreementList = new List <string>(); foreach (AutoStopLossModelViewModel item in Aslmvm) { if (string.IsNullOrEmpty(item.Agreement) || string.IsNullOrEmpty(item.VarietySelectedItem) || string.IsNullOrEmpty(item.Direction)) { continue; } AutoStopLossModel asmvm = new AutoStopLossModel(); asmvm.Variety = item.VarietySelectedItem; asmvm.Agreement = item.Agreement; asmvm.Direction = item.Direction == "买" ? "B" : "S"; asmvm.FloatingProfitAndLoss = item.FloatingProfitAndLoss; asmvm.StopLossPotion = item.StopLossPotion; asmvm.StopProfitPotion = item.StopProfitPotion; CommParameterSetting.AutoStopLossModel.Add(asmvm); if (!AgreementList.Contains(asmvm.Agreement)) { AgreementList.Add(asmvm.Agreement); } para += item.VarietySelectedItem + "," + item.Agreement + "," + asmvm.Direction + "," + item.FloatingProfitAndLoss + "," + item.StopLossPotion + "," + item.StopProfitPotion + ";"; //将新增的添加到现有的集合中 if (!ContractVariety.ContracPostionID.ContainsKey(asmvm.Agreement)) { ContractVariety.ContracPostionID.Add(asmvm.Agreement, new List <string>()); } } List <string> tempKeys = ContractVariety.ContracPostionID.Keys.ToList(); string Agreements = ""; foreach (string item in tempKeys) { if (!AgreementList.Contains(item)) { lock (ContractVariety.ContracPostionID) { ContractVariety.ContracPostionID.Remove(item); } Agreements += item + ","; } } if (!string.IsNullOrEmpty(Agreements)) { int count = SQLiteHelper.ExecuteNonQuery(SQLiteHelper.DBPath, CommandType.Text, "delete from AutoStopLoss where ContractID in ('" + Agreements.TrimEnd(',') + "')"); } IniHelper.ProfileWriteValue("AutoCheckFullStop", "AutoStopLoss", para.TrimEnd(';'), IniHelper.parameterSetting); } //} //下单版设置 CommParameterSetting.OrderVersion.BeforeOrderEnter = OrderVersion.BeforeOrderEnter; CommParameterSetting.OrderVersion.DefaultLot = OrderVersion.DefaultLot; CommParameterSetting.OrderVersion.MaxLot = OrderVersion.MaxLot; IniHelper.ProfileWriteValue("OrderVersion", "BeforeOrderEnter", CommParameterSetting.OrderVersion.BeforeOrderEnter.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("OrderVersion", "DefaultLot", CommParameterSetting.OrderVersion.DefaultLot.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("OrderVersion", "MaxLot", CommParameterSetting.OrderVersion.MaxLot.ToString(), IniHelper.parameterSetting); if (OrderVersion.DefaultLot > 0) { TransactionViewModel.Instance().FTNum = OrderVersion.DefaultLot; } if (OrderVersion.MaxLot > 0) { TransactionViewModel.Instance().MaxLot = OrderVersion.MaxLot; } //快捷键设置 CommParameterSetting.ShortcutKey.LockMenu = ShortcutKey.LockMenu; CommParameterSetting.ShortcutKey.BuyOpen = ShortcutKey.BuyOpen; CommParameterSetting.ShortcutKey.Clearance = ShortcutKey.Clearance; CommParameterSetting.ShortcutKey.ClosingBuy = ShortcutKey.ClosingBuy; CommParameterSetting.ShortcutKey.ClosingSell = ShortcutKey.ClosingSell; CommParameterSetting.ShortcutKey.Revoke = ShortcutKey.Revoke; CommParameterSetting.ShortcutKey.SellOpen = ShortcutKey.SellOpen; IniHelper.ProfileWriteValue("ShortcutKey", "LockMenu", CommParameterSetting.ShortcutKey.LockMenu.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "BuyOpen", CommParameterSetting.ShortcutKey.BuyOpen.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "Clearance", CommParameterSetting.ShortcutKey.Clearance.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "ClosingBuy", CommParameterSetting.ShortcutKey.ClosingBuy.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "ClosingSell", CommParameterSetting.ShortcutKey.ClosingSell.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "Revoke", CommParameterSetting.ShortcutKey.Revoke.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "SellOpen", CommParameterSetting.ShortcutKey.SellOpen.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "IntBuyOpen", CommParameterSetting.ShortcutKey.IntBuyOpen.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "IntClearance", CommParameterSetting.ShortcutKey.IntClearance.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "IntClosingBuy", CommParameterSetting.ShortcutKey.IntClosingBuy.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "IntClosingSell", CommParameterSetting.ShortcutKey.IntClosingSell.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "IntRevoke", CommParameterSetting.ShortcutKey.IntRevoke.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("ShortcutKey", "IntSellOpen", CommParameterSetting.ShortcutKey.IntSellOpen.ToString(), IniHelper.parameterSetting); //消息提示 CommParameterSetting.MessageAlert.OrderAlert = MessageAlert.OrderAlert; CommParameterSetting.MessageAlert.RevokeAlert = MessageAlert.RevokeAlert; CommParameterSetting.MessageAlert.TradeAlert = MessageAlert.TradeAlert; IniHelper.ProfileWriteValue("MessageAlert", "OrderAlert", CommParameterSetting.MessageAlert.OrderAlert.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("MessageAlert", "RevokeAlert", CommParameterSetting.MessageAlert.RevokeAlert.ToString(), IniHelper.parameterSetting); IniHelper.ProfileWriteValue("MessageAlert", "TradeAlert", CommParameterSetting.MessageAlert.TradeAlert.ToString(), IniHelper.parameterSetting); this.Close(); } catch (Exception ex) { LogHelper.Info(ex.Message); } }
public int InsertRecord(Source item) { return(SQLiteHelper.ExecuteNonQuery(CommandType.Text, "insert into source (name,otherName,code)values('" + item.Name + "','" + item.OtherName + "','" + item.Code + "')")); }
public SQLiteAdapter openToWrite() /* throws android.database.SQLException */ { sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION); sqLiteDatabase = sqLiteHelper.getWritableDatabase(); return(this); }
public Main() { InitializeComponent(); Text = Application.ProductName + " " + Application.ProductVersion; timerCheckJig.Interval = 1000 * Convert.ToUInt16(ConfigurationManager.AppSettings["refreshTime"]); #region UI //测试模式 if (ConfigurationManager.AppSettings["testMode"] == "SQLite") { new DBView().Show(); } //gbLeft gbOvenLeft.Text = gbOvenLeft.Name = ConfigurationManager.AppSettings["ovenLeftName"]; int[] ovenLeftLocationArr = Array.ConvertAll(ConfigurationManager.AppSettings["ovenLeftLocation"].Split(','), int.Parse); int[] ovenLeftSizeArr = Array.ConvertAll(ConfigurationManager.AppSettings["ovenLeftSize"].Split(','), int.Parse); Point ovenLeftLocation = new Point(ovenLeftLocationArr[0], ovenLeftLocationArr[1]); Size ovenLeftSize = new Size(ovenLeftSizeArr[0], ovenLeftSizeArr[1]); int ovenLeftRow = Convert.ToUInt16(ConfigurationManager.AppSettings["ovenLeftRow"]); int ovenLeftColumn = Convert.ToUInt16(ConfigurationManager.AppSettings["ovenLeftColumn"]); CreatOvenUI(gbOvenLeft, ovenLeftLocation, ovenLeftSize, ovenLeftRow, ovenLeftColumn); //gbRight gbOvenRight.Text = gbOvenRight.Name = ConfigurationManager.AppSettings["ovenRightName"]; int[] ovenRightLocationArr = Array.ConvertAll(ConfigurationManager.AppSettings["ovenRightLocation"].Split(','), int.Parse); int[] ovenRightSizeArr = Array.ConvertAll(ConfigurationManager.AppSettings["ovenRightSize"].Split(','), int.Parse); Point ovenRightLocation = new Point(ovenRightLocationArr[0], ovenRightLocationArr[1]); Size ovenRightSize = new Size(ovenRightSizeArr[0], ovenRightSizeArr[1]); int ovenRightRow = Convert.ToUInt16(ConfigurationManager.AppSettings["ovenRightRow"]); int ovenRightColumn = Convert.ToUInt16(ConfigurationManager.AppSettings["ovenRightColumn"]); CreatOvenUI(gbOvenRight, ovenRightLocation, ovenRightSize, ovenRightRow, ovenRightColumn); if (gbOvenLeft.Name == gbOvenRight.Name) { MessageBox.Show("配置文件中的ovenLeftName与ovenRightName值不可相同", "配置错误", MessageBoxButtons.OK, MessageBoxIcon.Error); Environment.Exit(0); } //lblRunningState lblRunningState.Text = ConfigurationManager.AppSettings["runningStateText"]; lblRunningState.Location = new Point(gbOvenRight.Location.X, gbOvenRight.Location.Y + gbOvenRight.Height); lblRunningState.Font = new Font(lblRunningState.Font.FontFamily.Name, Convert.ToSingle(ConfigurationManager.AppSettings["runningStateFontSize"])); //导入没完成的数据 string sql = @"WITH alldata AS( SELECT*,ROW_NUMBER() OVER (PARTITION BY jig_id,machine_id,location ORDER BY creat_time DESC) IS 1 AS last_operate FROM operate_history ) SELECT jig_id,machine_id,location,oven_time,creat_time FROM alldata WHERE last_operate is TRUE AND operate_type='IN'"; DataSet ds = SQLiteHelper.ExecuteDataSet(jigDB, sql, null); DataTable dt = ds.Tables[0]; foreach (DataRow dr in dt.Rows) { Jig jig = new Jig(dr); GroupBox oven = (GroupBox)Controls[jig.Machine]; Label label = (Label)oven.Controls[jig.Location]; label.Tag = jig; label.Text = string.Format("{0}\r\n{1}", jig.ID, jig.StartTime.ToString("yy/MM/dd\r\nHH:mm:ss")); label.BackColor = jig.TimeOut ? Color.Red : Control.DefaultBackColor; } #endregion }
private void sfButtonNextPage_Click(object sender, EventArgs e) { calculateSa(); calculateSo(); if (MessageBox.Show("Are you sure From Data You Entered ?", "", MessageBoxButtons.YesNo) == DialogResult.Yes) { using (SQLiteConnection conn = new SQLiteConnection(config.DataSource)) using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); // sh.Execute("DELETE FROM Site_and_Location"); var dic = new Dictionary <string, object>(); dic["id"] = lastId; dic["sa"] = TotalSafity; dic["so"] = TotalSocial; sh.Insert("safetyandsocialside", dic); conn.Close(); } getData(); BAC = projectdata[0]; Console.WriteLine("BAC=" + BAC); percentOfCompleate = projectdata[4]; Console.WriteLine("percentOfCompleate=" + (percentOfCompleate)); EV = BAC * (percentOfCompleate); Console.WriteLine("EV=" + EV); AC = projectdata[1]; Console.WriteLine("AC=" + AC); CPI = EV / AC; Console.WriteLine("CPI=" + CPI); contarctDuration = projectdata[2]; Console.WriteLine("contarctDuration=" + contarctDuration); PV = projectdata[5]; Console.WriteLine("PV=" + PV); SPI = (EV / PV); Console.WriteLine("SPI=" + (EV / PV)); #region Befor ETC = (BAC - EV) / CPI; EAC = ETC + AC; VAC = BAC - EAC; using (SQLiteConnection conn = new SQLiteConnection(config.DataSource)) using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); // sh.Execute("DELETE FROM Site_and_Location"); var dic = new Dictionary <string, object>(); dic["id"] = lastId; dic["contractvalue"] = BAC; dic["actualcost"] = AC; dic["contractduration"] = contarctDuration; dic["actualduration"] = projectdata[3]; dic["percentageofcompleate"] = projectdata[4]; dic["PV"] = PV; dic["EV"] = EV; dic["CPI"] = CPI; dic["SPI"] = SPI; dic["ETC"] = ETC; dic["EAC"] = EAC; dic["VAC"] = VAC; sh.Insert("report1", dic); } #endregion #region After ETC = (BAC - EV) / (CPI + SPI + costandtime[0] + costandtime[1] + qualityandrisk[0] + qualityandrisk[1] + safetyandsocialside[0] + safetyandsocialside[1]); EAC = ETC + AC; VAC = BAC - EAC; using (SQLiteConnection conn = new SQLiteConnection(config.DataSource)) using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); // sh.Execute("DELETE FROM Site_and_Location"); var dic2 = new Dictionary <string, object>(); dic2["id"] = lastId; dic2["CPI"] = CPI; dic2["SPI"] = SPI; dic2["CO"] = costandtime[0]; dic2["TI"] = costandtime[1]; dic2["QU"] = qualityandrisk[0]; dic2["RI"] = qualityandrisk[1]; dic2["SA"] = safetyandsocialside[0]; dic2["SO"] = safetyandsocialside[1]; dic2["ETC"] = ETC; dic2["EAC"] = EAC; dic2["VAC"] = VAC; sh.Insert("report2", dic2); #endregion conn.Close(); } Category.ReportProjectInformation_Form5 frm = new ReportProjectInformation_Form5(); frm.Show(); this.Hide(); } }
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 PublishDataToLocal(int taskindex, int threadindex) { try { string str = string.Empty; string LocalSQLiteName = "Data\\Collection\\" + ModelTask.TaskName + "\\SpiderResult.db"; DataTable dtData = SQLiteHelper.Query1(LocalSQLiteName, "Select * From Content").Tables[0]; if (!Directory.Exists(ModelTask.SaveDirectory2)) { Directory.CreateDirectory(ModelTask.SaveDirectory2); } if (ModelTask.SaveFileFormat2.ToLower() == ".html") { foreach (DataRow dr in dtData.Rows) { string fileName = dr["标题"].ToString(); str = dr["内容"].ToString(); try { fileName = fileName.Replace(".", ""); fileName = fileName.Replace(",", ""); fileName = fileName.Replace("、", ""); fileName = fileName.Replace(" ", ""); fileName = fileName.Replace("*", "_"); fileName = fileName.Replace("?", "_"); fileName = fileName.Replace("/", "_"); fileName = fileName.Replace("\\", "_"); fileName = fileName.Replace(":", "_"); fileName = fileName.Replace("|", "_"); fileName += ".html"; using (StreamWriter sw = new StreamWriter(ModelTask.SaveDirectory2 + "\\" + fileName, false, Encoding.UTF8)) { sw.Write(str); sw.Flush(); sw.Close(); } } catch { continue; } } } else if (ModelTask.SaveFileFormat2.ToLower() == ".txt") { foreach (DataRow dr in dtData.Rows) { foreach (ModelTaskLabel mTaskLabel in ModelTask.ListTaskLabel) { str += mTaskLabel.LabelName + ":" + dr[mTaskLabel.LabelName] + "\r\n"; } str += "\r\n\r\n"; } using (StreamWriter sw = new StreamWriter(ModelTask.SaveDirectory2 + "\\采集结果文本保存.txt", false, Encoding.UTF8)) { sw.Write(str); sw.Flush(); sw.Close(); } } else if (ModelTask.SaveFileFormat2.ToLower() == ".sql") { try { string strTemplateContent = File.ReadAllText(ModelTask.SaveHtmlTemplate2, Encoding.UTF8); StringBuilder sbContent = new StringBuilder(); foreach (DataRow dr in dtData.Rows) { string sql = strTemplateContent; foreach (ModelTaskLabel mTaskLabel in ModelTask.ListTaskLabel) { string content = dr[mTaskLabel.LabelName].ToString().Replace("'", "''"); sql = sql.Replace("[" + mTaskLabel.LabelName + "]", content); } sbContent.AppendLine(sql); } using (StreamWriter sw = new StreamWriter(ModelTask.SaveDirectory2 + "\\sql.sql", false, Encoding.UTF8)) { sw.Write(sbContent.ToString()); sw.Flush(); sw.Close(); } } catch (Exception ex) { gatherEv.Message = "错误!" + ex.Message; PublishCompalteDelegate(this, gatherEv); } } } catch (Exception ex) { Log4Helper.Write(V5_Utility.Utility.LogLevel.Error, ex); } }
/// <summary> /// 增加一条数据 /// </summary> public void Add(ModelTask model) { StringBuilder strSql = new StringBuilder(); StringBuilder strSql1 = new StringBuilder(); StringBuilder strSql2 = new StringBuilder(); if (model.ID != null) { strSql1.Append("ID,"); strSql2.Append("" + model.ID + ","); } if (model.Status != null) { strSql1.Append("Status,"); strSql2.Append("" + model.Status + ","); } if (model.TaskClassID != null) { strSql1.Append("TaskClassID,"); strSql2.Append("" + model.TaskClassID + ","); } if (model.TaskName != null) { strSql1.Append("TaskName,"); strSql2.Append("'" + model.TaskName + "',"); } if (model.IsSpiderUrl != null) { strSql1.Append("IsSpiderUrl,"); strSql2.Append("" + model.IsSpiderUrl + ","); } if (model.IsSpiderContent != null) { strSql1.Append("IsSpiderContent,"); strSql2.Append("" + model.IsSpiderContent + ","); } if (model.IsPublishContent != null) { strSql1.Append("IsPublishContent,"); strSql2.Append("" + model.IsPublishContent + ","); } if (model.PageEncode != null) { strSql1.Append("PageEncode,"); strSql2.Append("'" + model.PageEncode + "',"); } if (model.CollectionType != null) { strSql1.Append("CollectionType,"); strSql2.Append("" + model.CollectionType + ","); } if (model.CollectionContent != null) { strSql1.Append("CollectionContent,"); strSql2.Append("'" + model.CollectionContent + "',"); } if (model.LinkUrlMustIncludeStr != null) { strSql1.Append("LinkUrlMustIncludeStr,"); strSql2.Append("'" + model.LinkUrlMustIncludeStr + "',"); } if (model.LinkUrlNoMustIncludeStr != null) { strSql1.Append("LinkUrlNoMustIncludeStr,"); strSql2.Append("'" + model.LinkUrlNoMustIncludeStr + "',"); } if (model.LinkSpliceUrlStr != null) { strSql1.Append("LinkSpliceUrlStr,"); strSql2.Append("'" + model.LinkSpliceUrlStr + "',"); } if (model.LinkUrlCutAreaStart != null) { strSql1.Append("LinkUrlCutAreaStart,"); strSql2.Append("'" + model.LinkUrlCutAreaStart + "',"); } if (model.LinkUrlCutAreaEnd != null) { strSql1.Append("LinkUrlCutAreaEnd,"); strSql2.Append("'" + model.LinkUrlCutAreaEnd + "',"); } if (model.TestViewUrl != null) { strSql1.Append("TestViewUrl,"); strSql2.Append("'" + model.TestViewUrl + "',"); } if (model.IsWebOnlinePublish1 != null) { strSql1.Append("IsWebOnlinePublish1,"); strSql2.Append("" + model.IsWebOnlinePublish1 + ","); } if (model.IsSaveLocal2 != null) { strSql1.Append("IsSaveLocal2,"); strSql2.Append("" + model.IsSaveLocal2 + ","); } if (model.SaveFileFormat2 != null) { strSql1.Append("SaveFileFormat2,"); strSql2.Append("'" + model.SaveFileFormat2 + "',"); } if (model.SaveDirectory2 != null) { strSql1.Append("SaveDirectory2,"); strSql2.Append("'" + model.SaveDirectory2 + "',"); } if (model.SaveHtmlTemplate2 != null) { strSql1.Append("SaveHtmlTemplate2,"); strSql2.Append("'" + model.SaveHtmlTemplate2 + "',"); } if (model.SaveIsCreateIndex2 != null) { strSql1.Append("SaveIsCreateIndex2,"); strSql2.Append("" + model.SaveIsCreateIndex2 + ","); } if (model.IsSaveDataBase3 != null) { strSql1.Append("IsSaveDataBase3,"); strSql2.Append("" + model.IsSaveDataBase3 + ","); } if (model.SaveDataType3 != null) { strSql1.Append("SaveDataType3,"); strSql2.Append("" + model.SaveDataType3 + ","); } if (model.SaveDataUrl3 != null) { strSql1.Append("SaveDataUrl3,"); strSql2.Append("'" + model.SaveDataUrl3 + "',"); } if (model.SaveDataSQL3 != null) { strSql1.Append("SaveDataSQL3,"); strSql2.Append("'" + model.SaveDataSQL3 + "',"); } if (model.IsSaveSQL4 != null) { strSql1.Append("IsSaveSQL4,"); strSql2.Append("" + model.IsSaveSQL4 + ","); } if (model.SaveSQLContent4 != null) { strSql1.Append("SaveSQLContent4,"); strSql2.Append("'" + model.SaveSQLContent4 + "',"); } if (model.SaveSQLDirectory4 != null) { strSql1.Append("SaveSQLDirectory4,"); strSql2.Append("'" + model.SaveSQLDirectory4 + "',"); } if (model.SaveSQLDirectory4 != null) { strSql1.Append("Guid,"); strSql2.Append("'" + model.Guid + "',"); } // if (model.PluginSpiderUrl != null) { strSql1.Append("PluginSpiderUrl,"); strSql2.Append("'" + model.PluginSpiderUrl + "',"); } if (model.PluginSpiderContent != null) { strSql1.Append("PluginSpiderContent,"); strSql2.Append("'" + model.PluginSpiderContent + "',"); } if (model.PluginSaveContent != null) { strSql1.Append("PluginSaveContent,"); strSql2.Append("'" + model.PluginSaveContent + "',"); } if (model.PluginPublishContent != null) { strSql1.Append("PluginPublishContent,"); strSql2.Append("'" + model.PluginPublishContent + "',"); } //======================================2012 2-6 if (model.CollectionContentThreadCount != null) { strSql1.Append("CollectionContentThreadCount,"); strSql2.Append("" + model.CollectionContentThreadCount + ","); } if (model.CollectionContentStepTime != null) { strSql1.Append("CollectionContentStepTime,"); strSql2.Append("" + model.CollectionContentStepTime + ","); } if (model.PublishContentThreadCount != null) { strSql1.Append("PublishContentThreadCount,"); strSql2.Append("" + model.PublishContentThreadCount + ","); } if (model.PublishContentStepTimeMin != null) { strSql1.Append("PublishContentStepTimeMin,"); strSql2.Append("" + model.PublishContentStepTimeMin + ","); } if (model.PublishContentStepTimeMax != null) { strSql1.Append("PublishContentStepTimeMax,"); strSql2.Append("" + model.PublishContentStepTimeMax + ","); } if (model.IsHandGetUrl != null) { strSql1.Append("IsHandGetUrl,"); strSql2.Append("" + model.IsHandGetUrl + ","); } if (model.HandCollectionUrlRegex != null) { strSql1.Append("HandCollectionUrlRegex,"); strSql2.Append("'" + model.HandCollectionUrlRegex + "',"); } if (model.DemoListUrl != null) { strSql1.Append("DemoListUrl,"); strSql2.Append("'" + model.DemoListUrl + "',"); } // if (model.IsPlan != null) { strSql1.Append("IsPlan,"); strSql2.Append("" + model.IsPlan + ","); } if (model.PlanFormat != null) { strSql1.Append("PlanFormat,"); strSql2.Append("'" + model.PlanFormat + "',"); } strSql.Append("insert into S_Task("); strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1)); strSql.Append(")"); strSql.Append(" values ("); strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1)); strSql.Append(")"); SQLiteHelper.Execute(dbStr, strSql.ToString()); }
private void SendChanges(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' and sql like '%RowId%';"); StringBuilder sqlitesync_SyncDataToSend = new StringBuilder(); sqlitesync_SyncDataToSend.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?><SyncData xmlns=\"urn:sync-schema\">"); foreach (DataRow table in tables.Rows) { string tableName = table["tbl_Name"].ToString(); if (tableName.ToLower() != "MergeDelete".ToLower()) { try { sqlitesync_SyncDataToSend.Append("<tab n=\"" + tableName + "\">"); #region new records DataTable newRecords = sh.Select("select * from " + tableName + " where RowId is null;"); sqlitesync_SyncDataToSend.Append("<ins>"); foreach (DataRow record in newRecords.Rows) { sqlitesync_SyncDataToSend.Append("<r>"); foreach (DataColumn column in newRecords.Columns) { if (column.ColumnName != "MergeUpdate") { sqlitesync_SyncDataToSend.Append("<" + column.ColumnName + ">"); sqlitesync_SyncDataToSend.Append("<![CDATA[" + record[column.ColumnName].ToString() + "]]>"); sqlitesync_SyncDataToSend.Append("</" + column.ColumnName + ">"); } } sqlitesync_SyncDataToSend.Append("</r>"); } sqlitesync_SyncDataToSend.Append("</ins>"); #endregion #region updated records DataTable updRecords = sh.Select("select * from " + tableName + " where MergeUpdate > 0 and RowId is not null;"); sqlitesync_SyncDataToSend.Append("<upd>"); foreach (DataRow record in updRecords.Rows) { sqlitesync_SyncDataToSend.Append("<r>"); foreach (DataColumn column in updRecords.Columns) { if (column.ColumnName != "MergeUpdate") { sqlitesync_SyncDataToSend.Append("<" + column.ColumnName + ">"); if (record[column.ColumnName].GetType().Name == "Byte[]") { sqlitesync_SyncDataToSend.Append("<![CDATA[" + Convert.ToBase64String((byte[])record[column.ColumnName]) + "]]>"); } else { sqlitesync_SyncDataToSend.Append("<![CDATA[" + record[column.ColumnName].ToString() + "]]>"); } sqlitesync_SyncDataToSend.Append("</" + column.ColumnName + ">"); } } sqlitesync_SyncDataToSend.Append("</r>"); } sqlitesync_SyncDataToSend.Append("</upd>"); #endregion sqlitesync_SyncDataToSend.Append("</tab>"); } catch (Exception ex) { throw ex; } } } #region deleted records DataTable delRecords = sh.Select("select * from MergeDelete;"); sqlitesync_SyncDataToSend.Append("<delete>"); foreach (DataRow record in delRecords.Rows) { sqlitesync_SyncDataToSend.Append("<r>"); sqlitesync_SyncDataToSend.Append("<tb>" + record["TableId"].ToString() + "</tb>"); sqlitesync_SyncDataToSend.Append("<id>" + record["RowId"].ToString() + "</id>"); sqlitesync_SyncDataToSend.Append("</r>"); } sqlitesync_SyncDataToSend.Append("</delete>"); #endregion sqlitesync_SyncDataToSend.Append("</SyncData>"); #region send changes to server JsonObject inputObject = new JsonObject(); inputObject.Add("subscriber", subscriberId); inputObject.Add("content", sqlitesync_SyncDataToSend.ToString()); inputObject.Add("version", "3"); System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding(); byte[] bytes = encoding.GetBytes(inputObject.ToString()); var request = new RestRequest("Send", Method.POST); request.AddHeader("Content-Type", "application/json"); request.AddHeader("Accept", "*/*"); request.AddHeader("charset", "utf-8"); request.AddHeader("Content-Length", bytes.Length.ToString()); request.AddParameter("application/json; charset=utf-8", inputObject.ToString(), ParameterType.RequestBody); request.RequestFormat = DataFormat.Json; IRestResponse response = wsClient.Execute(request); #endregion #region clear update marker foreach (DataRow table in tables.Rows) { string tableName = table["tbl_Name"].ToString().ToLower(); if (tableName != "MergeDelete".ToLower() && tableName != "MergeIdentity".ToLower()) { string updTriggerSQL = (string)sh.ExecuteScalar("select sql from sqlite_master where type='trigger' and name like 'trMergeUpdate_" + tableName + "'"); sh.Execute("drop trigger trMergeUpdate_" + tableName + ";"); sh.Execute("update " + tableName + " set MergeUpdate=0 where MergeUpdate > 0;"); sh.Execute(updTriggerSQL); } if (tableName == "MergeIdentity".ToLower()) { sh.Execute("update MergeIdentity set MergeUpdate=0 where MergeUpdate > 0;"); } } #endregion #region clear delete marker sh.Execute("delete from MergeDelete"); #endregion conn.Close(); } } }
/// <summary> /// 某个市的某个台站的某个节目的某年某月的播出时长 /// 从数据库读取信息;从界面上的复选框(chkBoxLastTuesday)读取最后周二是否停机检修 /// </summary> /// <param name="year"></param> /// <param name="month"></param> /// <param name="chkBoxLastTuesday"></param> /// <param name="nostop2"></param> /// <param name="nostop3"></param> /// <param name="city"></param> /// <param name="station"></param> /// <param name="frequency"></param> /// <returns></returns> public string GetFrequencyHours(SQLiteHelper sqliteHelper, int year, int month, CheckBox chkBoxLastTuesday, int nostop2, int nostop3, string city, string station, string frequency) { try { double off_time_hour, off_time_min, on_time_hour, on_time_min; bool stop3, stop2, stopLast2; double off_time_2_hour, off_time_2_min, on_time_2_hour, on_time_2_min; double off_time_3_hour, off_time_3_min, on_time_3_hour, on_time_3_min; // 从数据库读取信息 // 停止播出 string off_time = sqliteHelper.GetTimeValue(city, station, frequency, BCstatsHelper.STR_OFF_TIME); off_time_hour = Convert.ToDouble(off_time.Substring(0, 2)); off_time_min = Convert.ToDouble(off_time.Substring(2, 2)); // 开始播出 string on_time = sqliteHelper.GetTimeValue(city, station, frequency, BCstatsHelper.STR_ON_TIME); on_time_hour = Convert.ToDouble(on_time.Substring(0, 2)); on_time_min = Convert.ToDouble(on_time.Substring(2, 2)); // 周三是否停机检修 int stop_3 = sqliteHelper.GetIntValue(city, station, frequency, BCstatsHelper.STR_STOP_3); if (stop_3 == 1) { stop3 = true; } else { stop3 = false; } // 周三停播 string off_time_3 = sqliteHelper.GetTimeValue(city, station, frequency, BCstatsHelper.STR_OFF_TIME_3); off_time_3_hour = Convert.ToDouble(off_time_3.Substring(0, 2)); off_time_3_min = Convert.ToDouble(off_time_3.Substring(2, 2)); // 周三播出 string on_time_3 = sqliteHelper.GetTimeValue(city, station, frequency, BCstatsHelper.STR_ON_TIME_3); on_time_3_hour = Convert.ToDouble(on_time_3.Substring(0, 2)); on_time_3_min = Convert.ToDouble(on_time_3.Substring(2, 2)); // 周二是否停机检修 int stop_2 = sqliteHelper.GetIntValue(city, station, frequency, BCstatsHelper.STR_STOP_2); if (stop_2 == 1) { stop2 = true; } else { stop2 = false; } // 周二停播 string off_time_2 = sqliteHelper.GetTimeValue(city, station, frequency, BCstatsHelper.STR_OFF_TIME_2); off_time_2_hour = Convert.ToDouble(off_time_2.Substring(0, 2)); off_time_2_min = Convert.ToDouble(off_time_2.Substring(2, 2)); // 周二播出 string on_time_2 = sqliteHelper.GetTimeValue(city, station, frequency, BCstatsHelper.STR_ON_TIME_2); on_time_2_hour = Convert.ToDouble(on_time_2.Substring(0, 2)); on_time_2_min = Convert.ToDouble(on_time_2.Substring(2, 2)); if (chkBoxLastTuesday == null) { //从数据库读取信息:最后一个周二是否停机检修 int stop_last_2 = sqliteHelper.GetIntValue(city, station, frequency, BCstatsHelper.STR_STOP_LAST_2); if (stop_last_2 == 1) { stopLast2 = true; // 最后一个周二停机检修 } else { stopLast2 = false; // 最后一个周二不停机检修 } } else { // 从界面上的复选框读取信息:最后一个周二,是否停机检修? if (chkBoxLastTuesday.IsChecked == true) { stopLast2 = true; } else { stopLast2 = false; } } Tuple <double, string> t = BCstatsHelper.TotalHoursOfTheMonth( year, month, nostop2, nostop3, off_time_hour, off_time_min, on_time_hour, on_time_min, stop3, off_time_3_hour, off_time_3_min, on_time_3_hour, on_time_3_min, stop2, off_time_2_hour, off_time_2_min, on_time_2_hour, on_time_2_min, stopLast2); // 当月播出总时长 return(t.Item1.ToString()); } catch { return("0"); } }
private void ClearDapperTestEntity() { SQLiteHelper.ExecuteSQL(@"delete from ""dapper_all_test""", command => command.ExecuteNonQuery()); }
void Start() { //创建名为sqlite4unity的数据库 //sql = new SQLiteHelper("data source= data/sqlite4unity.db"); if (true) { return; } sql = SQLiteHelper.GetIns(); sql.OpenConnection("data source= data/sqlite4unity.db"); //创建名为table1的数据表 sql.CreateTable("table1", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER PRIMARY KEY", "TEXT", "INTEGER", "TEXT" }); //插入两条数据 sql.InsertValues("table1", new string[] { "null", "'张三'", "'22'", "'*****@*****.**'" }); sql.InsertValues("table1", new string[] { "null", "'李四'", "'25'", "'*****@*****.**'" }); //更新数据,将Name="张三"的记录中的Name改为"Zhang3" //sql.UpdateValues("table1", new string[]{"Name"}, new string[]{"'Zhang3'"}, "Name", "=", "'张三'"); //插入3条数据 sql.InsertValues("table1", new string[] { "null", "'王五1'", "25", "'*****@*****.**'" }); sql.InsertValues("table1", new string[] { "null", "'王五2'", "26", "'*****@*****.**'" }); sql.InsertValues("table1", new string[] { "null", "'王五3'", "27", "'*****@*****.**'" }); //sql.InsertIntoSpecific("table1",new string[]{"Name","Age","Email"},new string[]{"'王八'","25","'WangBa'"}); //删除Name="王五"且Age=26的记录,DeleteValuesOR方法类似 sql.DeleteValuesAND("table1", new string[] { "Name", "Age" }, new string[] { "=", "=" }, new string[] { "'王五2'", "'26'" }); sql.InsertValues("table1", new string[] { "null", "'999'", "21", "'*****@*****.**'" }); string s = "select last_insert_rowid() from table1"; var r = sql.ExecuteQuery(s); while (r.Read()) { Debug.Log("nafio--->" + r.GetInt32(0)); //Debug.Log (r.GetOrdinal("ID")); //Debug.Log(r.GetInt32(r.GetOrdinal("ID"))); } //读取整张表 SqliteDataReader reader = sql.ReadFullTable("table1"); while (reader.Read()) { //读取ID Debug.Log(reader.GetInt32(reader.GetOrdinal("ID"))); //读取Name Debug.Log(reader.GetString(reader.GetOrdinal("Name"))); //读取Age Debug.Log(reader.GetInt32(reader.GetOrdinal("Age"))); //读取Email Debug.Log(reader.GetString(reader.GetOrdinal("Email"))); } //读取数据表中Age>=25的所有记录的ID和Name reader = sql.ReadTable("table1", new string[] { "ID", "Name" }, new string[] { "Age" }, new string[] { ">=" }, new string[] { "'25'" }); while (reader.Read()) { //读取ID Debug.Log(reader.GetInt32(reader.GetOrdinal("ID"))); //读取Name Debug.Log(reader.GetString(reader.GetOrdinal("Name"))); } //自定义SQL,删除数据表中所有Name="王五"的记录 sql.ExecuteQuery("DELETE FROM table1 WHERE NAME='王五'"); //关闭数据库连接 sql.CloseConnection(); }
/// <summary> /// 验证单个属性输入是否合法 /// </summary> /// <param name="propertyName"></param> /// <returns>Single Property IsValid</returns> private string IsValid(string propertyName) { switch (propertyName) { case "QuestionContent": if (string.IsNullOrWhiteSpace(QuestionContent)) { return "内容不能为空"; } break; case "QuestionNumber": if (string.IsNullOrWhiteSpace(QuestionNumber) || !isValidNumber(QuestionNumber)) { return "只能输入大于0的数字"; } break; case "QuestionTypeID": if (QuestionTypeID == -1) { return "请选择"; } break; case "QuestionField": { if (!string.IsNullOrEmpty(QuestionField)) { if (Regex.IsMatch(QuestionField.Substring(0, 1), @"^[\u4e00-\u9fa5a-zA-Z]") && !QuestionField.EndsWith(".") && !QuestionField.EndsWith("_")) { char[] variableName = QuestionField.ToCharArray(); if (!variableName.Contains('!') && !variableName.Contains('?') && !variableName.Contains('*')) { string[] reservedKeywords = new string[] { "ALL", "NE", "EQ", "TO", "LE", "LT", "BY", "OR", "GT", "AND", "NOT", "GE", "WITH" }; for (int i = 0; i < reservedKeywords.Length; i++) { if (string.Compare(QuestionField, reservedKeywords[i], true) != 0) { int length = System.Text.Encoding.Default.GetByteCount(QuestionField); if (length > 65) { return "长度不能超过64字节"; } else { //string DataBaseFile = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), "MyData.db"); //string DataSource = string.Format("data source={0}", DataBaseFile); using (SQLiteConnection conn = new SQLiteConnection(DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); int count = 0; string sqlString = String.Format("select count(*) from QuestionInfo where Q_Field='{0}' COLLATE NOCASE", QuestionField);//判断当前字段是否存在,注意字段值一定要加单引号 count = sh.ExecuteScalar<int>(sqlString); conn.Close(); if (count > 0) { if (count == 1) { if (!IsQuestionFieldExist.Instance.IsExist) { return "字段已存在"; } } else { return "字段已存在"; } } } } } } else { return "不能使用以下保留关键字(不区分大小写):ALL, NE, EQ, TO, LE, LT, BY, OR, GT, AND, NOT, GE, WITH"; } } } else { return "不能包含特殊字符(!?*)"; } } else { return "只能以字母或汉字开头,不能以“.”或“_”结尾"; } } } break; case "QuestionLable": { if (QuestionTypeID == 1) { if (!string.IsNullOrEmpty(QuestionLable)) { string[] lables = Regex.Split(QuestionLable, "\\s*,\\s*"); if (lables.Length.ToString() == OptionsCount) { foreach (var lable in lables) { if (string.IsNullOrWhiteSpace(lable)) { return "标签个数必须与选项个数相等"; } } } else { return "标签个数必须与选项个数相等"; } } } } break; case "OptionsCount": if ((string.IsNullOrWhiteSpace(OptionsCount) || !isValidNumber(OptionsCount)) && (QuestionTypeID == 0 || QuestionTypeID == 1)) { return "只能输入大于0的数字"; } break; case "ValueLable": { if (!string.IsNullOrEmpty(ValueLable) && QuestionTypeID == 0) { string[] lables = Regex.Split(ValueLable, "\\s*,\\s*"); if (lables.Length.ToString() == OptionsCount) { foreach (var lable in lables) { if (string.IsNullOrWhiteSpace(lable)) { return "值标签个数必须与选项个数相等"; } } } else { return "值标签个数必须与选项个数相等"; } } } break; case "DataTypeID": if ((DataTypeID == -1) && QuestionTypeID == 3) { return "请选择"; } break; case "JumpTarget": if (((string.IsNullOrWhiteSpace(JumpTarget) || !isValidNumber(JumpTarget))) && (IsJump == true)) { return "只能输入大于0的数字"; } break; case "JumpConditions": { if (QuestionTypeID != 3) { if (QuestionTypeID != 2) { string jumpConditionsPattern = @"^\d+(?:-\d+)?(?:,\d+(?:-\d+)?)*$"; //@"^\d+(\d+)?$|^\d+(\,\d+)+(\d+)?$"; if (!(Regex.IsMatch(JumpConditions, jumpConditionsPattern)) && (IsJump == true)) { return "只能输入以英文逗号或横杆分隔的数字"; } } else { if ((JumpConditions == "对" || JumpConditions == "错")) { } else if (IsJump == true) { return "只能输入“对”或“错”"; } } } } break; case "ValueRange": { if (!string.IsNullOrEmpty(valueRange) && QuestionTypeID == 3 && DataTypeID != 1) { if (DataTypeID == 0) { string valueRangePattern = @"^-?\d+(\.\d+)?(?:--?\d+(\.\d+)?)?(?:,-?\d+(\.\d+)?(?:--?\d+(\.\d+)?)?)*$"; if (!Regex.IsMatch(ValueRange, valueRangePattern) && !string.IsNullOrEmpty(ValueRange)) { return "逗号匹配单个值,横杠匹配区间,例如:1,2,3-10"; } } if (DataTypeID == 2) { string datePattern = @"^(?:(?!0000)[0-9]{4}/(?:(?:0[1-9]|1[0-2])/(?:0[1-9]|1[0-9]|2[0-8])|(?:0[13-9]|1[0-2])/(?:29|30)|(?:0[13578]|1[02])/31)|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])|(?:0[48]|[2468][048]|[13579][26])00)/02/29)$"; string errorMessage = "按以下格式输入:2000/02/29-2001/02/28"; Dictionary<char, int> dic = new Dictionary<char, int>(); char[] charData = ValueRange.ToCharArray(); foreach (char c in charData) { if (!dic.ContainsKey(c)) { dic.Add(c, 1); } else { dic[c] = dic[c] + 1; } } if (!string.IsNullOrEmpty(ValueRange)) { if (dic.ContainsKey('-')) { if (dic['-'] == 1) { int count = 0; string[] dates = valueRange.Split('-'); foreach (var item in dates) { if (item != "") { if (Regex.IsMatch(item, datePattern)) { } else { return errorMessage; } } else { count++; } } if (count == 2) { return errorMessage; } else { } } else { return errorMessage; } } else { return errorMessage; } } } } } break; case "Pattern": { if ((Pattern != null) && (Pattern.Trim().Length > 0)) { try { Regex.Match("", Pattern); } catch (ArgumentException) { // BAD PATTERN: Syntax error return "无效的正则表达式"; } } } break; default: break; } return null; }
void Update() { // if (Input.GetKeyUp (KeyCode.B)) { // sql.OpenConnection("data source= data/sqlite4unity.db"); // sql.BeTableExist ("table1"); // sql.CloseConnection(); // } if (Input.GetKeyUp(KeyCode.S)) { Debug.Log("Start"); sql = SQLiteHelper.GetIns(); sql.OpenConnection("data source= data/test.db"); } if (Input.GetKeyUp(KeyCode.T)) { //创建名为table1的数据表 sql.CreateTable("table1", new string[] { "ID", "Name", "Email" }, new string[] { "INTEGER PRIMARY KEY", "TEXT", "TEXT" }); } if (Input.GetKeyUp(KeyCode.N)) { Debug.Log("End"); sql.CloseConnection(); } //测试新增一条数据 if (Input.GetKeyUp(KeyCode.C)) { Debug.Log("Create"); string s = "name3333333"; GEditorDataMgr.CreateKVSqlData("table1", new string[] { "null", s, "*****@*****.**" }); } //测试查询 if (Input.GetKeyUp(KeyCode.Q)) { Debug.Log("Query"); //GEditorDataMgr.QueryKVSqlData("table1",new string[]{"ID","Name","Email"},1); } //测试修改 if (Input.GetKeyUp(KeyCode.M)) { Debug.Log("Motify"); GEditorDataMgr.ModifyKVSqlData("table1", new string[] { "Name" }, new string[] { "'f**k'" }, 1); } //测试删除 if (Input.GetKeyUp(KeyCode.D)) { Debug.Log("Del"); GEditorDataMgr.DelKVSqlData("table1", 1); } //测试copy // if(Input.GetKeyUp(KeyCode.P)) // { // Debug.Log ("Copy"); // string s = "INSERT INTO table1 SELECT * FROM table1"; // SQLiteHelper.GetIns ().ExecuteQuery (s); // } }
/// <summary> /// 增加一条数据 /// </summary> public void Add(tdps.Model.SBXX model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into SBXX("); strSql.Append("DLZH,NSRSBH,SZ,SBZLCODE,SBZLMC,SSQQ,SSQZ,SBBWJ,BBZT,TBSJ,SBJSZT,SLSJ,CLSJ,JSSJ,SBQNF,SBQYF,FileName,SBCS,SSQLX,SBSE,SheetZT,BBTXCS,CWBBFSJG,CWBB_FileName)"); strSql.Append(" values ("); strSql.Append("@DLZH,@NSRSBH,@SZ,@SBZLCODE,@SBZLMC,@SSQQ,@SSQZ,@SBBWJ,@BBZT,@TBSJ,@SBJSZT,@SLSJ,@CLSJ,@JSSJ,@SBQNF,@SBQYF,@FileName,@SBCS,@SSQLX,@SBSE,@SheetZT,@BBTXCS,@CWBBFSJG,@CWBB_FileName)"); SQLiteParameter[] parameters = { new SQLiteParameter("@DLZH", DbType.String, 20), new SQLiteParameter("@NSRSBH", DbType.String, 20), new SQLiteParameter("@SZ", DbType.String, 20), new SQLiteParameter("@SBZLCODE", DbType.String, 10), new SQLiteParameter("@SBZLMC", DbType.String, 80), new SQLiteParameter("@SSQQ", DbType.String, 20), new SQLiteParameter("@SSQZ", DbType.String, 20), new SQLiteParameter("@SBBWJ", DbType.String), new SQLiteParameter("@BBZT", DbType.String, 4), new SQLiteParameter("@TBSJ", DbType.String, 20), new SQLiteParameter("@SBJSZT", DbType.String, 4), new SQLiteParameter("@SLSJ", DbType.String, 20), new SQLiteParameter("@CLSJ", DbType.String, 20), new SQLiteParameter("@JSSJ", DbType.String, 20), new SQLiteParameter("@SBQNF", DbType.String, 4), new SQLiteParameter("@SBQYF", DbType.String, 2), new SQLiteParameter("@FileName", DbType.String, 200), new SQLiteParameter("@SBCS", DbType.Int32, 4), new SQLiteParameter("@SSQLX", DbType.Int32, 4), new SQLiteParameter("@SBSE", DbType.Decimal, 9), new SQLiteParameter("@SheetZT", DbType.String, 50), new SQLiteParameter("@BBTXCS", DbType.String, 200), new SQLiteParameter("@CWBBFSJG", DbType.String, 1), new SQLiteParameter("@CWBB_FileName", DbType.String, 200) }; parameters[0].Value = model.DLZH; parameters[1].Value = model.NSRSBH; parameters[2].Value = model.SZ; parameters[3].Value = model.SBZLCODE; parameters[4].Value = model.SBZLMC; parameters[5].Value = model.SSQQ; parameters[6].Value = model.SSQZ; parameters[7].Value = model.SBBWJ; parameters[8].Value = model.BBZT; parameters[9].Value = model.TBSJ; parameters[10].Value = model.SBJSZT; parameters[11].Value = model.SLSJ; parameters[12].Value = model.CLSJ; parameters[13].Value = model.JSSJ; parameters[14].Value = model.SBQNF; parameters[15].Value = model.SBQYF; parameters[16].Value = model.FileName; parameters[17].Value = model.SBCS; parameters[18].Value = model.SSQLX; parameters[19].Value = model.SBSE; parameters[20].Value = model.SheetZT; parameters[21].Value = model.BBTXCS; parameters[22].Value = model.CWBBFSJG; parameters[23].Value = model.CWBBFileName; SQLiteHelper.ExecuteNonQuery(strSql.ToString(), parameters); }
internal List <ImageLogoInfo> FindAll(bool haslogo, bool hasnologo, bool hasconfusedimages, bool hasError) { List <ImageLogoInfo> items = new List <ImageLogoInfo>(); if (!haslogo && !hasnologo & !hasError) { return(items); } using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; _OpenDBConn(); SQLiteHelper sh = new SQLiteHelper(cmd); var sql = "select * from " + tb.TableName + " where "; if (hasError) { sql += " Error is not null"; } else { sql += " Error is null"; } if (!haslogo || !hasnologo) { if (hasError && haslogo) { sql += " or HasLogo=1 "; } else if (!hasError && hasnologo) { sql += " and HasLogo=0 "; } if (hasError && hasnologo) { sql += " or HasLogo=0 "; } else if (!hasError && hasnologo) { sql += " and HasLogo=0 "; } } else if (hasError && haslogo && hasnologo) { sql += " or HasLogo=0 or haslogo=1 "; } else if (haslogo && hasnologo) { sql += " and (HasLogo=0 or haslogo=1 )"; } var dt = sh.Select(sql); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { var info = new ImageLogoInfo { Confidence = int.Parse(row["Confidence"] + ""), ImagePath = row["ImagePath"] + "", ProcessingTime = long.Parse(row["ProcessingTime"] + ""), HasLogo = row["HasLogo"].ToString() == "1" ? true : false }; if (info.ConfusedImage && !hasconfusedimages) { continue; } if (!string.IsNullOrWhiteSpace(row["Error"] + "")) { info.Error = new Exception(row["Error"] + ""); } items.Add(info); } } return(items); } }
/// <summary> /// 更新一条数据 /// </summary> public void Update(tdps.Model.SBXX model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update SBXX set "); strSql.Append("DLZH=@DLZH,"); strSql.Append("NSRSBH=@NSRSBH,"); strSql.Append("SZ=@SZ,"); strSql.Append("SBZLCODE=@SBZLCODE,"); strSql.Append("SBZLMC=@SBZLMC,"); strSql.Append("SSQQ=@SSQQ,"); strSql.Append("SSQZ=@SSQZ,"); strSql.Append("SBBWJ=@SBBWJ,"); strSql.Append("BBZT=@BBZT,"); strSql.Append("TBSJ=@TBSJ,"); strSql.Append("SBJSZT=@SBJSZT,"); strSql.Append("SLSJ=@SLSJ,"); strSql.Append("CLSJ=@CLSJ,"); strSql.Append("JSSJ=@JSSJ,"); strSql.Append("SBQNF=@SBQNF,"); strSql.Append("SBQYF=@SBQYF,"); strSql.Append("FileName=@FileName,"); strSql.Append("SBCS=@SBCS,"); strSql.Append("SSQLX=@SSQLX,"); strSql.Append("SBSE=@SBSE,"); strSql.Append("DYMM=@DYMM,"); strSql.Append("SBLSH=@SBLSH,"); strSql.Append("SBFSSJ=@SBFSSJ,"); strSql.Append("CELLVERSION=@CELLVERSION,"); strSql.Append("DKFPRQQ=@DKFPRQQ,"); strSql.Append("DKFPRQZ=@DKFPRQZ,"); strSql.Append("SJBS=@SJBS,"); strSql.Append("BBTXCS = @BBTXCS,"); strSql.Append("CWBBFSJG = @CWBBFSJG,"); strSql.Append("CWBB_FileName = @CWBB_FileName"); strSql.Append(" where SBXXID=@SBXXID "); SQLiteParameter[] parameters = { new SQLiteParameter("@SBXXID", DbType.Int32, 4), new SQLiteParameter("@DLZH", DbType.String, 20), new SQLiteParameter("@NSRSBH", DbType.String, 20), new SQLiteParameter("@SZ", DbType.String, 20), new SQLiteParameter("@SBZLCODE", DbType.String, 10), new SQLiteParameter("@SBZLMC", DbType.String, 80), new SQLiteParameter("@SSQQ", DbType.String, 20), new SQLiteParameter("@SSQZ", DbType.String, 20), new SQLiteParameter("@SBBWJ", DbType.String), new SQLiteParameter("@BBZT", DbType.String, 4), new SQLiteParameter("@TBSJ", DbType.String, 20), new SQLiteParameter("@SBJSZT", DbType.String, 4), new SQLiteParameter("@SLSJ", DbType.String, 20), new SQLiteParameter("@CLSJ", DbType.String, 20), new SQLiteParameter("@JSSJ", DbType.String, 20), new SQLiteParameter("@SBQNF", DbType.String, 4), new SQLiteParameter("@SBQYF", DbType.String, 2), new SQLiteParameter("@FileName", DbType.String, 200), new SQLiteParameter("@SBCS", DbType.Int32, 4), new SQLiteParameter("@SSQLX", DbType.Int32, 4), new SQLiteParameter("@SBSE", DbType.Decimal, 9), new SQLiteParameter("@SheetZT", DbType.String, 50), new SQLiteParameter("@SBLSH", DbType.String, 100), new SQLiteParameter("@DYMM", DbType.String, 50), new SQLiteParameter("@SBFSSJ", DbType.String, 20), new SQLiteParameter("@CELLVERSION", DbType.String, 50), new SQLiteParameter("@DKFPRQQ", DbType.String, 50), new SQLiteParameter("@DKFPRQZ", DbType.String, 50), new SQLiteParameter("@SJBS", DbType.String, 16), new SQLiteParameter("@BBTXCS", DbType.String, 200), new SQLiteParameter("@CWBBFSJG", DbType.String, 1), new SQLiteParameter("@CWBB_FileName", DbType.String, 200) }; parameters[0].Value = model.SBXXID; parameters[1].Value = model.DLZH; parameters[2].Value = model.NSRSBH; parameters[3].Value = model.SZ; parameters[4].Value = model.SBZLCODE; parameters[5].Value = model.SBZLMC; parameters[6].Value = model.SSQQ; parameters[7].Value = model.SSQZ; parameters[8].Value = model.SBBWJ; parameters[9].Value = model.BBZT; parameters[10].Value = model.TBSJ; parameters[11].Value = model.SBJSZT; parameters[12].Value = model.SLSJ; parameters[13].Value = model.CLSJ; parameters[14].Value = model.JSSJ; parameters[15].Value = model.SBQNF; parameters[16].Value = model.SBQYF; parameters[17].Value = model.FileName; parameters[18].Value = model.SBCS; parameters[19].Value = model.SSQLX; parameters[20].Value = model.SBSE; parameters[21].Value = model.SheetZT; parameters[22].Value = model.SBLSH; parameters[23].Value = model.DYMM; parameters[24].Value = model.SBFSSJ; parameters[25].Value = model.CELLVERSION; parameters[26].Value = model.DKFPRQQ; parameters[27].Value = model.DKFPRQZ; parameters[28].Value = model.SJBS; parameters[29].Value = model.BBTXCS; parameters[30].Value = model.CWBBFSJG; parameters[31].Value = model.CWBBFileName; SQLiteHelper.ExecuteNonQuery(strSql.ToString(), parameters); }
private void PublishDataToSQL(int taskindex, int threadindex) { string LocalSQLiteName = "Data\\Collection\\" + ModelTask.TaskName + "\\SpiderResult.db"; DataTable dtData = SQLiteHelper.Query1(LocalSQLiteName, "Select * From Content").Tables[0]; var listDiyUrl = DALDiyWebUrlHelper.GetList(" And SelfId=" + ModelTask.ID, "", 0); HttpHelper4 http = new HttpHelper4(); int taskId = ModelTask.ID; foreach (DataRow dr in dtData.Rows) { int resultId = int.Parse(dr["Id"].ToString()); foreach (var m in listDiyUrl) { try { string getUrl = m.Url; string postParams = m.UrlParams; StringBuilder sbContent = new StringBuilder(); foreach (ModelTaskLabel mTaskLabel in ModelTask.ListTaskLabel) { string pageEncodeContent = dr[mTaskLabel.LabelName].ToString().Replace("'", "''"); //可能需要编码实际测试才知道 getUrl = getUrl.Replace("[" + mTaskLabel.LabelName + "]", pageEncodeContent); postParams = postParams.Replace("[" + mTaskLabel.LabelName + "]", pageEncodeContent); sbContent.Append(pageEncodeContent); } string md5key = StringHelper.Instance.MD5(taskId.ToString() + resultId.ToString() + sbContent.ToString(), 32).ToLower(); //判断该条记录这个weburl是否发过 if (!DALDataPublishLogHelper.ChkRecord( ModelTask.ID, resultId, md5key)) { //记录日志 DALDataPublishLogHelper.Insert(new ModelDataPublishLog() { TaskId = taskId, ResultId = resultId, DesKey = md5key, CreateTime = DateTime.Now.ToString() }); } else { continue; } //开始发布网站 var result = http.GetHtml(new HttpItem() { URL = getUrl, Postdata = postParams, ContentType = "application/x-www-form-urlencoded" }); var html = result.Html; } catch (Exception ex) { continue; } } } if (PublishCompalteDelegate != null) { gatherEv.Message = "发布到自定义Web网站完成!"; PublishCompalteDelegate(this, gatherEv); } }
/// <summary> /// 得到一个对象实体 /// </summary> public ModelTask GetModel(int ID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select "); strSql.Append(" * "); strSql.Append(" from S_Task "); strSql.Append(" where ID=" + ID + " "); ModelTask model = new ModelTask(); DataSet ds = SQLiteHelper.Query1(dbStr, strSql.ToString()); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "") { model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString()); } if (ds.Tables[0].Rows[0]["TaskClassID"] != null && ds.Tables[0].Rows[0]["TaskClassID"].ToString() != "") { model.TaskClassID = int.Parse(ds.Tables[0].Rows[0]["TaskClassID"].ToString()); } if (ds.Tables[0].Rows[0]["TaskName"] != null && ds.Tables[0].Rows[0]["TaskName"].ToString() != "") { model.TaskName = ds.Tables[0].Rows[0]["TaskName"].ToString(); } if (ds.Tables[0].Rows[0]["IsSpiderUrl"] != null && ds.Tables[0].Rows[0]["IsSpiderUrl"].ToString() != "") { model.IsSpiderUrl = int.Parse(ds.Tables[0].Rows[0]["IsSpiderUrl"].ToString()); } if (ds.Tables[0].Rows[0]["IsSpiderContent"] != null && ds.Tables[0].Rows[0]["IsSpiderContent"].ToString() != "") { model.IsSpiderContent = int.Parse(ds.Tables[0].Rows[0]["IsSpiderContent"].ToString()); } if (ds.Tables[0].Rows[0]["IsPublishContent"] != null && ds.Tables[0].Rows[0]["IsPublishContent"].ToString() != "") { model.IsPublishContent = int.Parse(ds.Tables[0].Rows[0]["IsPublishContent"].ToString()); } if (ds.Tables[0].Rows[0]["PageEncode"] != null && ds.Tables[0].Rows[0]["PageEncode"].ToString() != "") { model.PageEncode = ds.Tables[0].Rows[0]["PageEncode"].ToString(); } if (ds.Tables[0].Rows[0]["CollectionType"] != null && ds.Tables[0].Rows[0]["CollectionType"].ToString() != "") { model.CollectionType = int.Parse(ds.Tables[0].Rows[0]["CollectionType"].ToString()); } if (ds.Tables[0].Rows[0]["CollectionContent"] != null && ds.Tables[0].Rows[0]["CollectionContent"].ToString() != "") { model.CollectionContent = ds.Tables[0].Rows[0]["CollectionContent"].ToString(); } if (ds.Tables[0].Rows[0]["LinkUrlMustIncludeStr"] != null && ds.Tables[0].Rows[0]["LinkUrlMustIncludeStr"].ToString() != "") { model.LinkUrlMustIncludeStr = ds.Tables[0].Rows[0]["LinkUrlMustIncludeStr"].ToString(); } if (ds.Tables[0].Rows[0]["LinkUrlNoMustIncludeStr"] != null && ds.Tables[0].Rows[0]["LinkUrlNoMustIncludeStr"].ToString() != "") { model.LinkUrlNoMustIncludeStr = ds.Tables[0].Rows[0]["LinkUrlNoMustIncludeStr"].ToString(); } if (ds.Tables[0].Rows[0]["LinkSpliceUrlStr"] != null && ds.Tables[0].Rows[0]["LinkSpliceUrlStr"].ToString() != "") { model.LinkSpliceUrlStr = ds.Tables[0].Rows[0]["LinkSpliceUrlStr"].ToString(); } if (ds.Tables[0].Rows[0]["LinkUrlCutAreaStart"] != null && ds.Tables[0].Rows[0]["LinkUrlCutAreaStart"].ToString() != "") { model.LinkUrlCutAreaStart = ds.Tables[0].Rows[0]["LinkUrlCutAreaStart"].ToString(); } if (ds.Tables[0].Rows[0]["LinkUrlCutAreaEnd"] != null && ds.Tables[0].Rows[0]["LinkUrlCutAreaEnd"].ToString() != "") { model.LinkUrlCutAreaEnd = ds.Tables[0].Rows[0]["LinkUrlCutAreaEnd"].ToString(); } if (ds.Tables[0].Rows[0]["TestViewUrl"] != null && ds.Tables[0].Rows[0]["TestViewUrl"].ToString() != "") { model.TestViewUrl = ds.Tables[0].Rows[0]["TestViewUrl"].ToString(); } if (ds.Tables[0].Rows[0]["IsWebOnlinePublish1"] != null && ds.Tables[0].Rows[0]["IsWebOnlinePublish1"].ToString() != "") { model.IsWebOnlinePublish1 = int.Parse(ds.Tables[0].Rows[0]["IsWebOnlinePublish1"].ToString()); } if (ds.Tables[0].Rows[0]["IsSaveLocal2"] != null && ds.Tables[0].Rows[0]["IsSaveLocal2"].ToString() != "") { model.IsSaveLocal2 = int.Parse(ds.Tables[0].Rows[0]["IsSaveLocal2"].ToString()); } if (ds.Tables[0].Rows[0]["SaveFileFormat2"] != null && ds.Tables[0].Rows[0]["SaveFileFormat2"].ToString() != "") { model.SaveFileFormat2 = ds.Tables[0].Rows[0]["SaveFileFormat2"].ToString(); } if (ds.Tables[0].Rows[0]["SaveDirectory2"] != null && ds.Tables[0].Rows[0]["SaveDirectory2"].ToString() != "") { model.SaveDirectory2 = ds.Tables[0].Rows[0]["SaveDirectory2"].ToString(); } if (ds.Tables[0].Rows[0]["SaveHtmlTemplate2"] != null && ds.Tables[0].Rows[0]["SaveHtmlTemplate2"].ToString() != "") { model.SaveHtmlTemplate2 = ds.Tables[0].Rows[0]["SaveHtmlTemplate2"].ToString(); } if (ds.Tables[0].Rows[0]["SaveIsCreateIndex2"] != null && ds.Tables[0].Rows[0]["SaveIsCreateIndex2"].ToString() != "") { model.SaveIsCreateIndex2 = int.Parse(ds.Tables[0].Rows[0]["SaveIsCreateIndex2"].ToString()); } if (ds.Tables[0].Rows[0]["IsSaveDataBase3"] != null && ds.Tables[0].Rows[0]["IsSaveDataBase3"].ToString() != "") { model.IsSaveDataBase3 = int.Parse(ds.Tables[0].Rows[0]["IsSaveDataBase3"].ToString()); } if (ds.Tables[0].Rows[0]["SaveDataType3"] != null && ds.Tables[0].Rows[0]["SaveDataType3"].ToString() != "") { model.SaveDataType3 = int.Parse(ds.Tables[0].Rows[0]["SaveDataType3"].ToString()); } if (ds.Tables[0].Rows[0]["SaveDataUrl3"] != null && ds.Tables[0].Rows[0]["SaveDataUrl3"].ToString() != "") { model.SaveDataUrl3 = ds.Tables[0].Rows[0]["SaveDataUrl3"].ToString(); } if (ds.Tables[0].Rows[0]["SaveDataSQL3"] != null && ds.Tables[0].Rows[0]["SaveDataSQL3"].ToString() != "") { model.SaveDataSQL3 = ds.Tables[0].Rows[0]["SaveDataSQL3"].ToString(); } if (ds.Tables[0].Rows[0]["IsSaveSQL4"] != null && ds.Tables[0].Rows[0]["IsSaveSQL4"].ToString() != "") { model.IsSaveSQL4 = int.Parse(ds.Tables[0].Rows[0]["IsSaveSQL4"].ToString()); } if (ds.Tables[0].Rows[0]["SaveSQLContent4"] != null && ds.Tables[0].Rows[0]["SaveSQLContent4"].ToString() != "") { model.SaveSQLContent4 = ds.Tables[0].Rows[0]["SaveSQLContent4"].ToString(); } if (ds.Tables[0].Rows[0]["SaveSQLDirectory4"] != null && ds.Tables[0].Rows[0]["SaveSQLDirectory4"].ToString() != "") { model.SaveSQLDirectory4 = ds.Tables[0].Rows[0]["SaveSQLDirectory4"].ToString(); } if (ds.Tables[0].Rows[0]["Guid"] != null && ds.Tables[0].Rows[0]["Guid"].ToString() != "") { model.Guid = ds.Tables[0].Rows[0]["Guid"].ToString(); } // if (ds.Tables[0].Rows[0]["PluginSpiderUrl"] != null && ds.Tables[0].Rows[0]["PluginSpiderUrl"].ToString() != "") { model.PluginSpiderUrl = ds.Tables[0].Rows[0]["PluginSpiderUrl"].ToString(); } if (ds.Tables[0].Rows[0]["PluginSpiderContent"] != null && ds.Tables[0].Rows[0]["PluginSpiderContent"].ToString() != "") { model.PluginSpiderContent = ds.Tables[0].Rows[0]["PluginSpiderContent"].ToString(); } if (ds.Tables[0].Rows[0]["PluginSaveContent"] != null && ds.Tables[0].Rows[0]["PluginSaveContent"].ToString() != "") { model.PluginSaveContent = ds.Tables[0].Rows[0]["PluginSaveContent"].ToString(); } if (ds.Tables[0].Rows[0]["PluginPublishContent"] != null && ds.Tables[0].Rows[0]["PluginPublishContent"].ToString() != "") { model.PluginPublishContent = ds.Tables[0].Rows[0]["PluginPublishContent"].ToString(); } //===============================2012 2-16 if (ds.Tables[0].Rows[0]["CollectionContentThreadCount"] != null && ds.Tables[0].Rows[0]["CollectionContentThreadCount"].ToString() != "") { model.CollectionContentThreadCount = int.Parse(ds.Tables[0].Rows[0]["CollectionContentThreadCount"].ToString()); } if (ds.Tables[0].Rows[0]["CollectionContentStepTime"] != null && ds.Tables[0].Rows[0]["CollectionContentStepTime"].ToString() != "") { model.CollectionContentStepTime = int.Parse(ds.Tables[0].Rows[0]["CollectionContentStepTime"].ToString()); } if (ds.Tables[0].Rows[0]["PublishContentThreadCount"] != null && ds.Tables[0].Rows[0]["PublishContentThreadCount"].ToString() != "") { model.PublishContentThreadCount = int.Parse(ds.Tables[0].Rows[0]["PublishContentThreadCount"].ToString()); } if (ds.Tables[0].Rows[0]["PublishContentStepTimeMin"] != null && ds.Tables[0].Rows[0]["PublishContentStepTimeMin"].ToString() != "") { model.PublishContentStepTimeMin = int.Parse(ds.Tables[0].Rows[0]["PublishContentStepTimeMin"].ToString()); } if (ds.Tables[0].Rows[0]["PublishContentStepTimeMax"] != null && ds.Tables[0].Rows[0]["PublishContentStepTimeMax"].ToString() != "") { model.PublishContentStepTimeMax = int.Parse(ds.Tables[0].Rows[0]["PublishContentStepTimeMax"].ToString()); } if (ds.Tables[0].Rows[0]["IsHandGetUrl"] != null && ds.Tables[0].Rows[0]["IsHandGetUrl"].ToString() != "") { model.IsHandGetUrl = int.Parse(ds.Tables[0].Rows[0]["IsHandGetUrl"].ToString()); } if (ds.Tables[0].Rows[0]["HandCollectionUrlRegex"] != null) { model.HandCollectionUrlRegex = ds.Tables[0].Rows[0]["HandCollectionUrlRegex"].ToString(); } if (ds.Tables[0].Rows[0]["DemoListUrl"] != null) { model.DemoListUrl = ds.Tables[0].Rows[0]["DemoListUrl"].ToString(); } // if (ds.Tables[0].Rows[0]["IsPlan"] != null && ds.Tables[0].Rows[0]["IsPlan"].ToString() != "") { model.IsPlan = int.Parse(ds.Tables[0].Rows[0]["IsPlan"].ToString()); } if (ds.Tables[0].Rows[0]["PlanFormat"] != null) { model.PlanFormat = ds.Tables[0].Rows[0]["PlanFormat"].ToString(); } return(model); } else { return(null); } }
private void PublishDataToDB(int taskindex, int threadindex) { string LocalSQLiteName = "Data\\Collection\\" + ModelTask.TaskName + "\\SpiderResult.db"; DataTable dtData = SQLiteHelper.Query1(LocalSQLiteName, "Select * From Content").Tables[0]; int saveDateType = ModelTask.SaveDataType3.Value; string connectionString = ModelTask.SaveDataUrl3; string exeSQL = ModelTask.SaveDataSQL3; string sql = string.Empty; switch (saveDateType) { case 1: //ACCESS break; case 2: //MSSQL foreach (DataRow dr in dtData.Rows) { try { sql = exeSQL; foreach (ModelTaskLabel mTaskLabel in ModelTask.ListTaskLabel) { sql = sql.Replace("[" + mTaskLabel.LabelName + "]", dr[mTaskLabel.LabelName].ToString().Replace("'", "''")); } sql = sql.Replace("[Guid]", Guid.NewGuid().ToString()); sql = sql.Replace("[Url]", dr["HrefSource"].ToString()); DbHelperSQL.connectionString = ModelTask.SaveDataUrl3; DbHelperSQL.ExecuteSql(sql); gatherEv.Message = dr["HrefSource"].ToString() + ":保存数据库成功!"; PublishCompalteDelegate(this, gatherEv); } catch (Exception ex) { Log4Helper.Write(V5_Utility.Utility.LogLevel.Error, dr["HrefSource"].ToString() + ":保存数据库失败!", ex); continue; } } break; case 3: //SQLITE break; case 4: //MYSQL MySqlConnection conn = new MySqlConnection(ModelTask.SaveDataUrl3); try { conn.Open(); MySqlScript script = new MySqlScript(conn); foreach (DataRow dr in dtData.Rows) { try { sql = exeSQL; foreach (ModelTaskLabel mTaskLabel in ModelTask.ListTaskLabel) { sql = sql.Replace("[" + mTaskLabel.LabelName + "]", dr[mTaskLabel.LabelName].ToString().Replace("'", "''")); } sql = sql.Replace("[Guid]", Guid.NewGuid().ToString()); sql = sql.Replace("[Url]", dr["HrefSource"].ToString()); script.Query = sql; script.Execute(); gatherEv.Message = dr["HrefSource"].ToString() + ":保存数据库成功!"; PublishCompalteDelegate(this, gatherEv); /* * DbHelperSQL.connectionString = ModelTask.SaveDataUrl3; * DbHelperSQL.ExecuteSql(sql); * gatherEv.Message = dr["HrefSource"].ToString() + ":保存数据库成功!"; * PublishCompalteDelegate(this, gatherEv); */ } catch (Exception ex) { Log4Helper.Write(V5_Utility.Utility.LogLevel.Error, dr["HrefSource"].ToString() + ":保存数据库失败!", ex); continue; } } } catch (Exception ex) { } conn.Close(); break; case 5: //Oracle break; } }
public int DeleteRecord(string id) { return(SQLiteHelper.ExecuteNonQuery(CommandType.Text, "delete from source where id=" + id)); }
// 创建所有的表 public static void CreateTables() { // name: 比赛名称, time:比赛时间,project:比赛项目(男子团体,女子团体...),group_name:比赛组别(A组,B组...) // PRE_ROUNDS: 预赛轮次, FINAL_ROUNDS: 决赛轮次,NUM_ONE_ROUND: 一轮球道数, MAX_EVERY_FAIRWAY:每球道最高杆数 // FINAL_NUM:录取决赛人数, SCORING_METHOD:团体成绩计分方法 var createProjectTableSQL = @" create table MATCH( ID varchar(255), NAME varchar(255), TIME varchar(255), PROJECT_NAME varchar(255), GROUP_NAME varchar(255), PRE_ROUNDS varchar(255), FINAL_ROUNDS varchar(255), NUM_ONE_ROUND varchar(255), MAX_EVERY_FAIRWAY varchar(255), FINAL_NUM varchar(255), SCORING_METHOD varchar(255), CREATE_TIME varchar(255) ); "; var createOthersTableSQL = @" CREATE TABLE OTHERS( ID VARCHAR(255), MATCH_ID VARCHAR(255), TEAM_NAME VARCHAR(255), TEAM_SHORT_NAME VARCHAR(255), GROUP_NAME VARCHAR(255), TITLE VARCHAR(255), NAME VARCHAR(255), SEX VARCHAR(255), PHONE VARCHAR(255), ACCOMMODATION_REQUIREMENTS VARCHAR(255), IS_VEGETARIAN VARCHAR(255), CLOTHING_SIZE VARCHAR(255), IDCARD VARCHAR(255))"; var createAthletesTableSQL = @" create table ATHLETES( ID VARCHAR(255), MATCH_ID VARCHAR(255), TEAM_NAME VARCHAR(255), TEAM_SHORT_NAME VARCHAR(255), GROUP_NAME VARCHAR(255), NAME varchar(255), SEX varchar(255), BAR_GROUP_GAMES_STATUS varchar(255), BAR_INDIVIDUAL_GAMES_STATUS varchar(255), WAY_GROUP_GAMES_STATUS varchar(255), WAY_INDIVIDUAL_GAMES_STATUS varchar(255), IDCARD varchar(255), PHONE VARCHAR(255), ACCOMMODATION_REQUIREMENTS varchar(255), IS_VEGETARIAN VARCHAR(255), CLOTHING_SIZE VARCHAR(255), PRELIMINARIES_RESULTS_ONE varchar(255), PRELIMINARIES_RESULTS_TWO varchar(255), PRELIMINARIES_RESULTS_THREE varchar(255), PRELIMINARIES_RESULTS_FOUR varchar(255), PRELIMINARIES_RESULTS_FIVE varchar(255), FINAL_RESULTS_ONE varchar(255), FINAL_RESULTS_TWO varchar(255), FINAL_RESULTS_THREE varchar(255), FINAL_RESULTS_FOUR varchar(255), FINAL_RESULTS_FIVE varchar(255) );"; var createMatchGroupTableSQL = @" create table MATCH_GROUP( ID varchar(255), MATCH_ID varchar(255), GROUP_NAME varchar(255), SEX_NAME varchar(255), TEAM_NAME varchar(255), TEAM_SHORT_NAME VARCHAR(255), NAME varchar(255), ROW varchar(255), COLUMN varchar(255), ISINDIVIDUAL varchar(255), MEN_WOMEN_COLUMN varchar(255), DOUBLE_COLUMN varchar(255), IS_FINAL varchar(255) ); "; if (!TableExist("MATCH")) // 比赛表 { // create PROJECT table SQLiteHelper.ExecuteNonQuery(SQLiteHelper.LocalDbConnectionString, createProjectTableSQL, CommandType.Text); } if (!TableExist("OTHERS")) // 其他人员信息表 { // create OTHERS table SQLiteHelper.ExecuteNonQuery(SQLiteHelper.LocalDbConnectionString, createOthersTableSQL, CommandType.Text); } if (!TableExist("ATHLETES")) // 运动员信息表 { // create ATHLETES table SQLiteHelper.ExecuteNonQuery(SQLiteHelper.LocalDbConnectionString, createAthletesTableSQL, CommandType.Text); } if (!TableExist("MATCH_GROUP")) // 竞赛分组表 { // create MATCH_GROUP table SQLiteHelper.ExecuteNonQuery(SQLiteHelper.LocalDbConnectionString, createMatchGroupTableSQL, CommandType.Text); } }
//*********** GENERIC METHODS ***************** private void insertIntoDB(String table, Dictionary<string, object> dic) { using (SQLiteConnection conn = new SQLiteConnection(CONNECT)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); sh.Insert(table, dic); conn.Close(); } } }
void Start() { /* * //各平台下数据库存储的绝对路径(通用) * //PC:sql = new SQLiteHelper("data source=" + Application.dataPath + "/sqlite4unity.db"); * //Mac:sql = new SQLiteHelper("data source=" + Application.dataPath + "/sqlite4unity.db"); * //Android:sql = new SQLiteHelper("URI=file:" + Application.persistentDataPath + "/sqlite4unity.db"); * //iOS:sql = new SQLiteHelper("data source=" + Application.persistentDataPath + "/sqlite4unity.db"); */ //创建名为sqlite4unity的数据库 sql = new SQLiteHelper("data source=sqlite4unity.db"); //创建名为table1的数据表 sql.CreateTable("table1", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" }); //插入两条数据 sql.InsertValues("table1", new string[] { "'1'", "'张三'", "'22'", "'*****@*****.**'" }); sql.InsertValues("table1", new string[] { "'2'", "'李四'", "'25'", "'*****@*****.**'" }); //更新数据,将Name="张三"的记录中的Name改为"Zhang3" sql.UpdateValues("table1", new string[] { "Name" }, new string[] { "'Zhang3'" }, "Name", "=", "'张三'"); //插入3条数据 sql.InsertValues("table1", new string[] { "3", "'王五'", "25", "'*****@*****.**'" }); sql.InsertValues("table1", new string[] { "4", "'王五'", "26", "'*****@*****.**'" }); sql.InsertValues("table1", new string[] { "5", "'王五'", "27", "'*****@*****.**'" }); //删除Name="王五"且Age=26的记录,DeleteValuesOR方法类似 sql.DeleteValuesAND("table1", new string[] { "Name", "Age" }, new string[] { "=", "=" }, new string[] { "'王五'", "'26'" }); //读取整张表 SqliteDataReader reader = sql.ReadFullTable("table1"); while (reader.Read()) { //读取ID Debug.Log(reader.GetInt32(reader.GetOrdinal("ID"))); //读取Name Debug.Log(reader.GetString(reader.GetOrdinal("Name"))); //读取Age Debug.Log(reader.GetInt32(reader.GetOrdinal("Age"))); //读取Email Debug.Log(reader.GetString(reader.GetOrdinal("Email"))); } //读取数据表中Age>=25的所有记录的ID和Name reader = sql.ReadTable("table1", new string[] { "ID", "Name" }, new string[] { "Age" }, new string[] { ">=" }, new string[] { "'25'" }); while (reader.Read()) { //读取ID Debug.Log(reader.GetInt32(reader.GetOrdinal("ID"))); //读取Name Debug.Log(reader.GetString(reader.GetOrdinal("Name"))); } //自定义SQL,删除数据表中所有Name="王五"的记录 sql.ExecuteQuery("DELETE FROM table1 WHERE NAME='王五'"); //关闭数据库连接 sql.CloseConnection(); }
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(); } } }
public Workspaces(string filename) { sqlite = new SQLiteHelper(filename); CreateTable(defaultTable); }
private void RepostTaskCoreFrm_Load(object sender, EventArgs e) { selectCommitType.SelectedIndex = 0; using (SQLiteConnection conn = new SQLiteConnection(DBConfig.DataSource)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); int count = sh.ExecuteScalar<int>("select count(*) from WeiboTaskInfo;") + 1; this.TaskInfoDataView.DataSource = sh.Select("select * from WeiboTaskInfo order by task_id desc;"); //加入后更新DataGridView conn.Close(); } } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(ModelTask model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update S_Task set "); if (model.Status != null) { strSql.Append("Status=" + model.Status + ","); } if (model.TaskClassID != null) { strSql.Append("TaskClassID=" + model.TaskClassID + ","); } if (model.TaskName != null) { strSql.Append("TaskName='" + model.TaskName + "',"); } if (model.IsSpiderUrl != null) { strSql.Append("IsSpiderUrl=" + model.IsSpiderUrl + ","); } if (model.IsSpiderContent != null) { strSql.Append("IsSpiderContent=" + model.IsSpiderContent + ","); } if (model.IsPublishContent != null) { strSql.Append("IsPublishContent=" + model.IsPublishContent + ","); } if (model.PageEncode != null) { strSql.Append("PageEncode='" + model.PageEncode + "',"); } if (model.CollectionType != null) { strSql.Append("CollectionType=" + model.CollectionType + ","); } if (model.CollectionContent != null) { strSql.Append("CollectionContent='" + model.CollectionContent + "',"); } if (model.LinkUrlMustIncludeStr != null) { strSql.Append("LinkUrlMustIncludeStr='" + model.LinkUrlMustIncludeStr + "',"); } if (model.LinkUrlNoMustIncludeStr != null) { strSql.Append("LinkUrlNoMustIncludeStr='" + model.LinkUrlNoMustIncludeStr + "',"); } if (model.LinkSpliceUrlStr != null) { strSql.Append("LinkSpliceUrlStr='" + model.LinkSpliceUrlStr + "',"); } if (model.LinkUrlCutAreaStart != null) { strSql.Append("LinkUrlCutAreaStart='" + model.LinkUrlCutAreaStart + "',"); } if (model.LinkUrlCutAreaEnd != null) { strSql.Append("LinkUrlCutAreaEnd='" + model.LinkUrlCutAreaEnd + "',"); } if (model.TestViewUrl != null) { strSql.Append("TestViewUrl='" + model.TestViewUrl + "',"); } if (model.IsWebOnlinePublish1 != null) { strSql.Append("IsWebOnlinePublish1=" + model.IsWebOnlinePublish1 + ","); } if (model.IsSaveLocal2 != null) { strSql.Append("IsSaveLocal2=" + model.IsSaveLocal2 + ","); } if (model.SaveFileFormat2 != null) { strSql.Append("SaveFileFormat2='" + model.SaveFileFormat2 + "',"); } if (model.SaveDirectory2 != null) { strSql.Append("SaveDirectory2='" + model.SaveDirectory2 + "',"); } if (model.SaveHtmlTemplate2 != null) { strSql.Append("SaveHtmlTemplate2='" + model.SaveHtmlTemplate2 + "',"); } if (model.SaveIsCreateIndex2 != null) { strSql.Append("SaveIsCreateIndex2=" + model.SaveIsCreateIndex2 + ","); } if (model.IsSaveDataBase3 != null) { strSql.Append("IsSaveDataBase3=" + model.IsSaveDataBase3 + ","); } if (model.SaveDataType3 != null) { strSql.Append("SaveDataType3=" + model.SaveDataType3 + ","); } if (model.SaveDataUrl3 != null) { strSql.Append("SaveDataUrl3='" + model.SaveDataUrl3 + "',"); } if (model.SaveDataSQL3 != null) { strSql.Append("SaveDataSQL3='" + model.SaveDataSQL3 + "',"); } if (model.IsSaveSQL4 != null) { strSql.Append("IsSaveSQL4=" + model.IsSaveSQL4 + ","); } if (model.SaveSQLContent4 != null) { strSql.Append("SaveSQLContent4='" + model.SaveSQLContent4 + "',"); } if (model.SaveSQLDirectory4 != null) { strSql.Append("SaveSQLDirectory4='" + model.SaveSQLDirectory4 + "',"); } if (model.Guid != null) { strSql.Append("Guid='" + model.Guid + "',"); } // if (model.PluginSpiderUrl != null) { strSql.Append("PluginSpiderUrl='" + model.PluginSpiderUrl + "',"); } if (model.PluginSpiderContent != null) { strSql.Append("PluginSpiderContent='" + model.PluginSpiderContent + "',"); } if (model.PluginSaveContent != null) { strSql.Append("PluginSaveContent='" + model.PluginSaveContent + "',"); } if (model.PluginPublishContent != null) { strSql.Append("PluginPublishContent='" + model.PluginPublishContent + "',"); } //============================================2012 2-6 if (model.CollectionContentThreadCount != null) { strSql.Append("CollectionContentThreadCount=" + model.CollectionContentThreadCount + ","); } if (model.CollectionContentStepTime != null) { strSql.Append("CollectionContentStepTime=" + model.CollectionContentStepTime + ","); } if (model.PublishContentThreadCount != null) { strSql.Append("PublishContentThreadCount=" + model.PublishContentThreadCount + ","); } if (model.PublishContentStepTimeMin != null) { strSql.Append("PublishContentStepTimeMin=" + model.PublishContentStepTimeMin + ","); } if (model.PublishContentStepTimeMax != null) { strSql.Append("PublishContentStepTimeMax=" + model.PublishContentStepTimeMax + ","); } if (model.IsHandGetUrl != null) { strSql.Append("IsHandGetUrl=" + model.IsHandGetUrl + ","); } if (model.HandCollectionUrlRegex != null) { strSql.Append("HandCollectionUrlRegex='" + model.HandCollectionUrlRegex + "',"); } if (model.DemoListUrl != null) { strSql.Append("DemoListUrl='" + model.DemoListUrl + "',"); } // if (model.IsPlan != null) { strSql.Append("IsPlan=" + model.IsPlan + ","); } if (model.PlanFormat != null) { strSql.Append("PlanFormat='" + model.PlanFormat + "',"); } int n = strSql.ToString().LastIndexOf(","); strSql.Remove(n, 1); strSql.Append(" where ID=" + model.ID + " "); int rowsAffected = SQLiteHelper.Execute(dbStr, strSql.ToString()); if (rowsAffected > 0) { return(true); } else { return(false); } }
public void createTable() { 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)); tb.Columns.Add(new SQLiteColumn("url", ColType.Text)); tb.Columns.Add(new SQLiteColumn("referer", ColType.Text)); tb.Columns.Add(new SQLiteColumn("status", ColType.Integer)); tb.Columns.Add(new SQLiteColumn("duration", ColType.Decimal)); sh.CreateTable(tb); conn.Close(); } } }
public void Remove(int id) { string sql = "update Comment set enable = 0 where Commentid = ?"; SQLiteHelper.ExecuteNonQuery(sql, id); }