/// <summary> /// 获取实体 /// </summary> /// <param name="GUID"></param> /// <returns></returns> public ThreadTaskLogEntity GetEntity(string GUID) { try { ThreadTaskLogEntity entity = new ThreadTaskLogEntity(); DataTable dt = AccessHelper.ExecuteDataSet(AccessHelper.conn, "select * from ThreadLog where [GUID]=@GUID", new OleDbParameter("@GUID", GUID)).Tables[0]; if (dt.Rows.Count > 0) { PropertyInfo[] properties = entity.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); foreach (PropertyInfo item in properties) {//循环遍历实体,取出字段和字段对应的值 if (item.GetValue(entity, null) != null) { item.SetValue(entity, dt.Rows[0][item.Name].ToString(), null); } } return(entity); } } catch (Exception ex) { } return(null); }
// 获取主表数据 protected DataTable GetMainData(string fuelType) { string tableName = string.Empty; if (fuelType == "CTNY") { tableName = "CTNY_MAIN"; } else if (fuelType == "FCDS") { tableName = "FCDS_MAIN"; } else if (fuelType == "CDS") { tableName = "CDS_MAIN"; } else if (fuelType == "CDD") { tableName = "CDD_MAIN"; } else if (fuelType == "RLDC") { tableName = "RLDC_MAIN"; } //else if (fuelType == "FCDS") //{ // tableName = "MAIN_FCDSHHDL"; //} string sqlMain = string.Format(@"SELECT * FROM {0}", tableName); DataTable dt = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlMain, null).Tables[0];; return(dt); }
/// <summary> /// 显示更新后的主表参数信息 /// </summary> /// <param name="mainUpdateList"></param> protected void SearchUpdatedMainData(List <string> mainUpdateList) { string mainIds = string.Empty; try { if (mainUpdateList != null && mainUpdateList.Count > 0) { foreach (string id in mainUpdateList) { mainIds += string.Format(",'{0}'", id); } if (!string.IsNullOrEmpty(mainIds)) { mainIds = mainIds.Substring(1); } } else { mainIds = "''"; } // 获取本地主表信息 string sqlCtny = string.Format(@"SELECT * FROM CTNY_MAIN WHERE CLXH IN ({0})", mainIds); DataSet dsCtny = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlCtny, null); DataTable dtCtny = dsCtny.Tables[0]; dtCtny.Columns.Add("check", System.Type.GetType("System.Boolean")); gcCljbxx.DataSource = dtCtny; Utils.SelectItem(this.gvCljbxx, true); lblCtSum.Text = string.Format("共{0}条", dtCtny.Rows.Count); } catch (Exception ex) { MessageBox.Show("查询出现错误:" + ex.Message); } }
private bool CheckLogin(string userId, string password) { bool flg = false; try { if (string.IsNullOrEmpty(userId) || string.IsNullOrEmpty(password)) { return(false); } string sql = String.Format(@"SELECT * FROM LOCAL_USER WHERE USER_ID='{0}' AND USER_PASSWORD='******'", userId, password); DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql, null); int count = ds.Tables[0].Rows.Count; if (count > 0) { flg = true; } } catch (Exception) { flg = false; } return(flg); }
protected void SearchParamData() { string sql = "SELECT * FROM ANNOUNCE_PARAM WHERE 1=1 "; if (!string.IsNullOrEmpty(this.txt_BATCH.Text)) { sql += string.Format(@"AND BATCH like '%{0}%'", this.txt_BATCH.Text); } if (!string.IsNullOrEmpty(this.txt_vehModel.Text)) { sql += string.Format(@"AND MODEL_VEHICLE like '%{0}%'", this.txt_vehModel.Text); } DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql, null); if (ds != null && ds.Tables[0].Rows.Count > 0) { dgcNotice.DataSource = ds.Tables[0]; this.dgvNotice.BestFitColumns(); foreach (GridColumn col in this.dgvNotice.Columns) { col.AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center; } this.lblSum.Text = string.Format("共{0}条", ds.Tables[0].Rows.Count); } else { MessageBox.Show("该企业公告参数不存在", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
//绑定企业数据 private void BindQyData() { string sql_Name = @"select DISTINCT MFRSName from NerdsMFRSName"; string sql_Year = @"select DISTINCT ClearYear from NerdsMFRSName"; DataSet ds_Name = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql_Name, null); DataSet ds_Year = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql_Year, null); if (ds_Name != null && ds_Name.Tables[0].Rows.Count > 0) { cb_Company.Properties.Items.Clear(); foreach (DataRow company in ds_Name.Tables[0].Rows) { cb_Company.Properties.Items.Add(company[0]); } } else { cb_Company.Properties.Items.Clear(); } if (ds_Year != null && ds_Year.Tables[0].Rows.Count > 0) { cb_ClearYear.Properties.Items.Clear(); foreach (DataRow ClearYear in ds_Year.Tables[0].Rows) { cb_ClearYear.Properties.Items.Add(ClearYear[0]); } } else { cb_ClearYear.Properties.Items.Clear(); } }
//获取全部数据 private DataTable queryAll() { string sqlAll = String.Format("select * from INFOMATION_ENTITIES where JZNF='{0}' {1} order by CLYXDW desc,VIN desc", Settings.Default.ClearYear, this.queryParam()); var ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlAll, null); return(ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null); }
//从源数据(VIN,COC)中查询待生成国环数据 protected DataSet GetReadyGHDataFromLocalCoc(string fuelType) { // 获取本地车辆基本信息 DataSet ds = null; string sql = string.Empty; try { if (fuelType == Utils.CTNY) { sql = @"SELECT VI.VIN,VI.CLZZRQ,VI.GH_FDJXLH,VI.TGRQ,VI.DATA_TYPE, CI.COC_ID,CI.CLXH,CI.GH_FDJSCC FROM VIN_INFO VI,COC_INFO CI WHERE CI.STATUS='1' AND VI.DATA_TYPE='GH' AND CI.COC_ID=VI.COC_ID"; } if (fuelType == Utils.FCDS) { sql = @"SELECT VI.VIN,VI.CLZZRQ,VI.GH_FDJXLH,VI.TGRQ,VI.DATA_TYPE, CI.COC_ID,CI.CLXH,CI.GH_FDJSCC FROM VIN_INFO VI, COC_FCDS CI WHERE CI.STATUS='1' AND VI.DATA_TYPE='GH' AND CI.COC_ID=VI.COC_ID"; } ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql, null); } catch (Exception ex) { throw ex; } return(ds); }
//获取本地能源数据 private DataTable GetLocalALL(string tableName) { xtraTabControl1.SelectedTabPageIndex = 0; string sql = string.Empty; if (this.cbTimeType.Text.Trim().Equals("上报日期")) { sql = String.Format(@"select * from {0} where USER_ID = '{1}' and (UPDATETIME>=#{2}#) and (UPDATETIME<#{3}#)", tableName, Utils.userId, Convert.ToDateTime(dtStartTime.Text), Convert.ToDateTime(dtEndTime.Text).Add(new TimeSpan(24, 0, 0))); } else { sql = String.Format(@"select * from {0} where USER_ID = '{1}' and (CLZZRQ>=#{2}#) and (CLZZRQ<#{3}#)", tableName, Utils.userId, Convert.ToDateTime(dtStartTime.Text), Convert.ToDateTime(dtEndTime.Text).Add(new TimeSpan(24, 0, 0))); } DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql, null); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0) { DataTable dt = ds.Tables[0].Copy(); dt.Columns.Remove("USER_ID"); if (tableName.Equals("VIEW_T_ALL") || tableName.Equals("VIEW_T_ALL_ADC")) { dt.Columns.Remove("CT_QTXX"); } return(dt); } return(null); }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/html"; int channel_id = int.Parse(context.Request.Form["channel_id"]); DataTable oldData = AccessHelper.ExecuteDataSet("select id,title,class_layer,parent_id from dt_article_category where channel_id=@channel_id order by sort_id,id", new OleDbParameter("@channel_id", channel_id)).Tables[0]; DataTable dt = oldData.Clone(); //调用迭代组合成DAGATABLE GetChilds(oldData, dt, 0, channel_id); StringBuilder sb = new StringBuilder(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { string Id = dr["id"].ToString(); int ClassLayer = int.Parse(dr["class_layer"].ToString()); string Title = dr["title"].ToString().Trim(); if (ClassLayer == 1) { sb.Append(string.Format("<option value=\"{0}\">{1}</option>", Id, Title)); } else { Title = "├ " + Title; Title = Common.Utils.StringOfChar(ClassLayer - 1, " ") + Title; sb.Append(string.Format("<option value=\"{0}\">{1}</option>", Id, Title)); } } } context.Response.Write(sb); }
public WebApiResult <string> GetThreadList(ThreadLogReadRequest Request) { System.Data.DataTable dt; if (Request.GUID == "ALL") { dt = AccessHelper.ExecuteDataSet(AccessHelper.conn, "select * from ThreadLog order by AddTime").Tables[0]; dt.Columns.Add("Type"); dt.Columns.Add("filePath"); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["Type"] = AccessHelper.ExecuteDataSet(AccessHelper.conn, "select * from Log order by AddTime").Tables[0].Rows[i]["Type"]; dt.Rows[i]["filePath"] = AccessHelper.ExecuteDataSet(AccessHelper.conn, "select * from Log order by AddTime").Tables[0].Rows[i]["SavePath"]; } } else { dt = AccessHelper.ExecuteDataSet(AccessHelper.conn, "select * from ThreadLog where [GUID]=@GUID", new OleDbParameter("@GUID", Request.GUID)).Tables[0]; dt.Columns.Add("Type"); dt.Columns.Add("filePath"); dt.Rows[0]["Type"] = AccessHelper.ExecuteDataSet(AccessHelper.conn, "select * from Log where [GUID]=@GUID", new OleDbParameter("@GUID", Request.GUID)).Tables[0].Rows[0]["Type"]; dt.Rows[0]["filePath"] = AccessHelper.ExecuteDataSet(AccessHelper.conn, "select * from Log where [GUID]=@GUID", new OleDbParameter("@GUID", Request.GUID)).Tables[0].Rows[0]["SavePath"]; } return(new WebApiResult <string>() { success = 1, msg = "", results = JsonHelper.ToJson(dt) }); }
static public TaskLogEntity GetLogEntity(string GUID) { TaskLogEntity entity = new TaskLogEntity(); //try //{ // XmlDocument xmlDoc = new XmlDocument(); // xmlDoc.Load(HttpContext.Current.Server.MapPath("/Log/Log.xml")); // XmlNode node = xmlDoc.SelectSingleNode("/DataItem/Log[@GUID='" + GUID + "']"); // if (node != null) // {//日志存在就进行修改 // //node.SelectSingleNode("ErrorMsg").InnerText = entity.ErrorMsg; // //node.SelectSingleNode("Status").InnerText = entity.Status; // //node.SelectSingleNode("Type").InnerText = entity.Type; // //node.SelectSingleNode("ErrorDate").InnerText = DateTime.Now.ToString(); // PropertyInfo[] properties = entity.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); // foreach (PropertyInfo item in properties) // {//循环遍历实体,取出字段和字段对应的值 // if (item.GetValue(entity, null) != null) // { // item.SetValue(entity, node.SelectSingleNode(item.Name).InnerText, null); // } // } // } //} //catch (Exception ex) //{ //} //return entity; try { System.Data.DataTable dt = AccessHelper.ExecuteDataSet(AccessHelper.conn, "select * from Log where [GUID]=@GUID", new OleDbParameter("@GUID", GUID)).Tables[0]; if (dt.Rows.Count > 0) { PropertyInfo[] properties = entity.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); foreach (PropertyInfo item in properties) {//循环遍历实体,取出字段和字段对应的值 if (item.GetValue(entity, null) != null) { item.SetValue(entity, dt.Rows[0][item.Name].ToString(), null); } } return(entity); } } catch (Exception ex) { } return(null); }
private void btnAdd_Click(object sender, EventArgs e) { try { string userId = this.txtUserName.Text.Trim(); if (string.IsNullOrEmpty(this.txtUserName.Text)) { MessageBox.Show("请输入用户名"); return; } if (string.IsNullOrEmpty(userId)) { MessageBox.Show("请输入密码"); return; } DataView dv = (DataView)this.gridView1.DataSource; if (dv != null) { if (dv.Table.Select(string.Format("USER_ID='{0}'", userId)).Length > 0) { MessageBox.Show("该用户已存在"); return; } } string sql = string.Format("insert into LOCAL_USER(USER_ID,USER_PASSWORD,IS_ADMIN) values('{0}','{1}','否')", this.txtUserName.Text.Trim(), this.txtPassWord.Text); AccessHelper.ExecuteDataSet(con, sql, null); } catch { } DataBinds(); Clear(); }
/// <summary> /// 查询用户信息 /// </summary> /// <returns></returns> private DataTable QueryUserData() { string sql = "SELECT ID, USERNAME AS 用户名, NAME AS 姓名, PHONE AS 电话,IIF( STATUS=1 ,'正常' ,'禁用') AS 状态 FROM SYS_USERINFO ORDER BY ID"; DataSet dsLocal = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql, null); return(dsLocal.Tables[0]); }
private void getUploadList() { string sqlJbxx = "SELECT QCSCQY,VIN,CLXH FROM FC_CLJBXX WHERE STATUS ='" + 2 + "'"; DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlJbxx, null); ds.Tables[0].Columns.Add("check", System.Type.GetType("System.Boolean")); this.gridControl1.DataSource = ds.Tables[0]; }
/// <summary> /// 查询本地主表数据 /// </summary> /// <param name="rllx"></param> protected void SearchMainData(string rllx) { try { string tableName = string.Empty; string paramNO = string.Empty; string clxh = string.Empty; string clzl = string.Empty; if (rllx == "CTNY") { tableName = "CTNY_MAIN"; clxh = tbCtClxh.Text; clzl = tbCtClzl.Text; } //else if (rllx == "FCDS") //{ // tableName = "MAIN_FCDSHHDL"; // paramNO = tbFcdsParam.Text; // clxh = tbFcdsClxh.Text; // clzl = tbFcdsClzl.Text; //} // 获取本地主表信息 string sql = string.Format(@"SELECT * FROM {0} where 1=1 ", tableName); string sw = ""; if (!"".Equals(clxh)) { sw += " and (CLXH like '%" + tbCtClxh.Text + "%')"; } if (!"".Equals(clzl)) { sw += " and (CLZL like '%" + tbCtClzl.Text + "%')"; } DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql + sw, null); DataTable dt = ds.Tables[0]; dt.Columns.Add("check", System.Type.GetType("System.Boolean")); if (rllx == "CTNY") { dgvCtny.DataSource = dt; Utils.SelectItem(this.gvCtny, false); lblCtSum.Text = string.Format("共{0}条", dt.Rows.Count); } //else if (rllx == "FCDS") //{ // dgvFcds.DataSource = dt; // Utils.SelectItem(this.gvFcds, false); // lblFcdsSum.Text = string.Format("共{0}条", dt.Rows.Count); //} } catch (Exception ex) { MessageBox.Show("查询出现错误:" + ex.Message); } }
private void searchLocal() { try { try { if (Convert.ToDateTime(this.dtEndTime.Text) < Convert.ToDateTime(this.dtStartTime.Text)) { MessageBox.Show("结束时间不能小于开始时间", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } } catch { MessageBox.Show("时间格式有误", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } // 获取本地车辆基本信息 string sql = @"SELECT * FROM FC_CLJBXX WHERE STATUS='1' "; string sw = ""; if (!string.IsNullOrEmpty(tbVin.Text)) { sw += String.Format(" and (vin like '%{0}%')", tbVin.Text); } if (!string.IsNullOrEmpty(tbClxh.Text)) { sw += String.Format(" and (CLXH like '%{0}%')", tbClxh.Text); } if (!string.IsNullOrEmpty(tbClzl.Text)) { sw += String.Format(" and (CLZL like '%{0}%')", tbClzl.Text); } if (!string.IsNullOrEmpty(cbRllx.Text)) { sw += String.Format(" and (rllx like '%{0}%')", cbRllx.Text); } if (!string.IsNullOrEmpty(this.dtStartTime.Text)) { sw += String.Format(" and (CREATETIME>=#{0}#)", Convert.ToDateTime(this.dtStartTime.Text)); } if (!string.IsNullOrEmpty(this.dtEndTime.Text)) { sw += String.Format(" and (CREATETIME<=#{0}#)", Convert.ToDateTime(this.dtEndTime.Text)); } DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql + sw, null); DataTable dt = ds.Tables[0]; dt.Columns.Add("check", System.Type.GetType("System.Boolean")); this.gcCljbxx.DataSource = dt; this.gvCljbxx.BestFitColumns(); this.lblSum.Text = String.Format("共{0}条", dt.Rows.Count); Utils.SelectItem(this.gvCljbxx, false); } catch (Exception ex) { MessageBox.Show("查询出现错误:" + ex.Message); } }
public WebApiResult <string> GetStatusList(LogSelectRequest Request) { System.Data.DataTable dt = AccessHelper.ExecuteDataSet(AccessHelper.conn, "select * from ThreadLog where [TStatus]=@TStatus", new OleDbParameter("@TStatus", Request.status)).Tables[0]; return(new WebApiResult <string>() { success = 1, msg = "", results = JsonHelper.ToJson(dt) }); }
//查询刚刚更新的COC数据 protected void SearchUpdatedCocData(List <string> cocIdList) { string cocIds = string.Empty; try { if (cocIdList != null && cocIdList.Count > 0) { foreach (string id in cocIdList) { cocIds += string.Format(",'{0}'", id); } if (!string.IsNullOrEmpty(cocIds)) { cocIds = cocIds.Substring(1); } } else { cocIds = "''"; } // 获取本地主表信息 string sqlCtny = string.Format(@"SELECT * FROM COC_INFO WHERE STATUS='1' AND COC_ID IN ({0})", cocIds); string sqlFcds = string.Format(@"SELECT * FROM COC_FCDS WHERE STATUS='1' AND COC_ID IN ({0})", cocIds); string sw = ""; if (!string.IsNullOrEmpty(tbCoc.Text.Trim())) { sw += " and (COC_ID like '%" + tbCoc.Text.Trim() + "%')"; } if (!string.IsNullOrEmpty(tbClxh.Text.Trim())) { sw += " and (CLXH like '%" + tbClxh.Text.Trim() + "%')"; } if (!string.IsNullOrEmpty(tbClzl.Text.Trim())) { sw += " and (CLZL like '%" + tbClzl.Text.Trim() + "%')"; } DataSet dsCtny = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlCtny + sw, null); DataTable dtCtny = dsCtny.Tables[0]; dtCtny.Columns.Add("check", System.Type.GetType("System.Boolean")); dgvCtny.DataSource = dtCtny; Utils.SelectItem(this.gvCtny, true); DataSet dsFcds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlFcds + sw, null); DataTable dtFcds = dsFcds.Tables[0]; dtFcds.Columns.Add("check", System.Type.GetType("System.Boolean")); dgvFcds.DataSource = dtFcds; Utils.SelectItem(this.gvFcds, true); lblSum.Text = string.Format("共{0}条", dtCtny.Rows.Count + dtFcds.Rows.Count); } catch (Exception ex) { MessageBox.Show("查询出现错误:" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
//附件5 private void Annex5_Export(string path, string selectedParamEntityIds) { StringBuilder sql = new StringBuilder(); sql.AppendFormat("select * from CONTRACT_User where AutoFill_Manufacturer='{0}'", strManufacturer); DataTable dt = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql.ToString(), null).Tables[0]; ExportPDF(path + @"\", dt, selectedParamEntityIds); }
private void frmLog_Load(object sender, EventArgs e) { DataTable dt = AccessHelper.ExecuteDataSet("select * from S_log", null).Tables[0]; this.dataGridView1.DataSource = dt; this.dataGridView1.Columns[1].HeaderText = "用户名"; this.dataGridView1.Columns[2].HeaderText = "操作"; this.dataGridView1.Columns[3].HeaderText = "时间"; }
//检查COC数据是否收集完整 protected bool CheckIfCocDataReady() { string cocMsg = string.Empty; DataSet dsCtny = null; DataSet dsFcds = null; DataSet ds = null; string sqlCtny = @"SELECT CI.COC_ID FROM COC_INFO CI WHERE CI.COC_ID IN (SELECT DISTINCT(VI.COC_ID) FROM VIN_INFO VI)"; string sqlFcds = @"SELECT CI.COC_ID FROM COC_FCDS CI WHERE CI.COC_ID IN (SELECT DISTINCT(VI.COC_ID) FROM VIN_INFO VI)"; string sql = @"SELECT T1.COC_ID FROM (SELECT DISTINCT(COC_ID) FROM VIN_INFO) T1 WHERE T1.COC_ID NOT IN (SELECT COC_ID FROM (SELECT CI.COC_ID FROM COC_INFO CI UNION SELECT CF.COC_ID FROM COC_FCDS CF) )"; try { dsCtny = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlCtny, null); dsFcds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlFcds, null); if (dsCtny.Tables[0].Rows.Count + dsFcds.Tables[0].Rows.Count <= 0) { MessageBox.Show("VIN文件中的所有COC数据不存在,请先导入\n", "数据未准备就绪", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(false); } else { ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql, null); if (ds.Tables[0].Rows.Count > 0) { cocMsg += "缺少以下COC数据,只能生成部分数据\r\n"; if (ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { cocMsg += dr["COC_ID"].ToString().Trim() + "\r\n"; } } MessageForm mf = new MessageForm(cocMsg); Utils.SetFormMid(mf); mf.Text = "数据未准备就绪"; mf.ShowDialog(); } } } catch (Exception ex) { MessageBox.Show(ex.Message, "数据未准备就绪", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(false); } return(true); }
/// <summary> /// 显示所有油耗数据 /// </summary> protected void ShowCposFuelData() { try { string cpos = this.tbFuelCpos.Text.Trim(); string yearMode = this.tbFuelYear.Text.Trim(); string clxh = this.tbFuelClxh.Text.Trim(); string bsx = this.tbFuelBsx.Text.Trim(); string pl = this.tbFuelPl.Text.Trim(); string qcscqy = this.tbFuelQcscqy.Text.Trim(); // 获取本地轮胎信息信息 string sql = string.Format(@"SELECT * FROM MAIN_FUEL WHERE 1=1"); string sqlOrder = " ORDER BY CPOS"; string sw = ""; if (!string.IsNullOrEmpty(cpos)) { sw += " AND (CPOS LIKE '%" + cpos + "%')"; } if (!string.IsNullOrEmpty(yearMode)) { sw += " AND (YEAR_MODE LIKE '%" + yearMode + "%')"; } if (!string.IsNullOrEmpty(clxh)) { sw += " AND (CLXH LIKE '%" + clxh + "%')"; } if (!string.IsNullOrEmpty(pl)) { sw += " AND (CT_PL LIKE '%" + pl + "%')"; } if (!string.IsNullOrEmpty(bsx)) { sw += " AND (BSX LIKE '%" + bsx + "%')"; } if (!string.IsNullOrEmpty(qcscqy)) { sw += " AND (QCSCQY LIKE '%" + qcscqy + "%')"; } DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql + sw + sqlOrder, null); DataTable dt = ds.Tables[0]; dt.Columns.Add("check", System.Type.GetType("System.Boolean")); this.gcCpos.DataSource = dt; this.gvCpos.BestFitColumns(); Utils.SelectItem(this.gvCpos, false); lblCposSum.Text = string.Format("共{0}条", dt.Rows.Count); tabPage_PARAM.SelectedTabPage = xtraTabPage2; } catch (Exception ex) { MessageBox.Show("查询出现错误:" + ex.Message); } }
// 查询 private void searchLocal() { try { // 验证查询时间:结束时间不能小于开始时间 if (!this.VerifyStartEndTime()) { MessageBox.Show("结束时间不能小于开始时间"); return; } // 获取本地车辆基本信息 string sql = string.Format(@"SELECT iif(status= '1 ', '未导出 ',iif(status= '0 ', '已导出 ')) as STATUS, GH_DATA.VIN,CLXH,COC_ID,GH_FDJSCC,CLZZRQ,TGRQ,GH_FDJXLH,tt.PARAM_VALUE FROM GH_DATA left join (select * from rllx_param_entity where PARAM_CODE='CT_FDJXH' or PARAM_CODE='FCDS_HHDL_FDJXH') as tt on gh_data.vin=tt.vin WHERE 1=1"); string sw = ""; if (!ceExported.Checked) { sw += " AND STATUS='1'"; } if (!string.IsNullOrEmpty(tbVin.Text)) { sw += " and (gh_data.vin = '" + tbVin.Text + "')"; } string timeFiled = "TGRQ"; if (cbTimeType.Text.Trim() == "车辆制造日期") { timeFiled = "CLZZRQ"; } if (!string.IsNullOrEmpty(dtStartTime.Text)) { sw += " and (" + timeFiled + ">=#" + Convert.ToDateTime(this.dtStartTime.Text) + "#)"; } if (!string.IsNullOrEmpty(dtEndTime.Text)) { sw += " and (" + timeFiled + "<#" + Convert.ToDateTime(this.dtEndTime.Text).Add(new TimeSpan(24, 0, 0)) + "#)"; } sw += " ORDER BY TGRQ DESC"; DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql + sw, null); DataTable dt = ds.Tables[0]; dt.Columns.Add("check", System.Type.GetType("System.Boolean")); this.gcGH.DataSource = dt; this.gvGH.BestFitColumns(); this.gcGHide.DataSource = dt; // 导出用 lblSum.Text = string.Format("共{0}条", dt.Rows.Count); Utils.SelectItem(this.gvGH, false); } catch (Exception ex) { MessageBox.Show("查询出现错误:" + ex.Message); } }
/// <summary> /// 保存数据 /// </summary> /// <param name="GUID"></param> /// <param name="entity"></param> /// <returns></returns> public int FormSave(TaskLogEntity entity) { try { List <OleDbParameter> parameter = new List <OleDbParameter>(); string SqlStr = ""; if (AccessHelper.ExecuteDataSet(AccessHelper.conn, "select * from " + entity.GetType().Name + " where [GUID]=@GUID", new OleDbParameter("@GUID", entity.GUID)).Tables[0].Rows.Count > 0) { SqlStr = "update " + entity.GetType().Name + " set "; PropertyInfo[] properties = entity.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); foreach (PropertyInfo item in properties) {//循环遍历实体,取出字段和字段对应的值 if (item.GetValue(entity, null) != null) { if (item.Name != "GUID") { SqlStr += (SqlStr.EndsWith("set ") ? "" : ",") + "[" + item.Name + "]=@" + item.Name; parameter.Add(new OleDbParameter("@" + item.Name, item.GetValue(entity, null).ToString())); } } } SqlStr += " where [GUID]=@GUID"; } else { string Field = "", Variable = ""; PropertyInfo[] properties = entity.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); foreach (PropertyInfo item in properties) {//循环遍历实体,取出字段和字段对应的值 if (item.GetValue(entity, null) != null) { Field += (Field == "" ? "" : ",") + "[" + item.Name + "]"; Variable += (Variable == "" ? "" : ",") + "@" + item.Name; parameter.Add(new OleDbParameter("@" + item.Name, item.GetValue(entity, null).ToString())); } } SqlStr = "insert into " + entity.GetType().Name + "(" + Field + ") values(" + Variable + ")"; } int mm = AccessHelper.ExecuteNonQuery(AccessHelper.conn, SqlStr, parameter.ToArray()); return(mm); } catch (Exception ex) { } return(0); }
//验证VIN,CLXZ,是否为空GGPC private string VerifyData(string selectedParamEntityIds) { string msg = string.Empty; StringBuilder sqlStr = new StringBuilder(); sqlStr.AppendFormat(" SELECT *"); sqlStr.AppendFormat(" FROM INFOMATION_ENTITIES "); sqlStr.AppendFormat(" WHERE JZNF = '{0}' ", Settings.Default.ClearYear); if (!string.IsNullOrEmpty(selectedParamEntityIds)) { sqlStr.AppendFormat(" AND VIN in ({0}) ", selectedParamEntityIds); } sqlStr.Append(" order by VIN desc "); DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlStr.ToString(), null); DataTable dt = ds.Tables[0]; if (dt != null && ds.Tables[0].Rows.Count > 0) { Dictionary <string, string> mapData = new Dictionary <string, string>(); string vin = ""; foreach (DataRow dr in ds.Tables[0].Rows) { vin = dr["VIN"].ToString(); mapData.Clear(); for (int i = 0; i < dr.ItemArray.Count(); i++) { mapData.Add(dt.Columns[i].ColumnName, dr.ItemArray[i].ToString()); } string msgdata = ValidateParam.CheckParameter(mapData); if (msgdata != string.Empty) { msg += String.Format("vin:{0} 数据错误:{1}{2}", vin, Environment.NewLine, msgdata); } } /* * string vin = string.Empty; * var Values = dt.AsEnumerable().Where(d => string.IsNullOrEmpty(d.Field<string>("CLXZ")) || string.IsNullOrEmpty(d.Field<string>("CLXZ"))).Select(d => d.Field<string>("VIN")).ToArray(); * vin = string.Join(",", Values); * if (!string.IsNullOrEmpty(vin)) * { * msg = String.Format("VIN为{0}中的车辆性质或公告批次不能为空", vin); * } * else * { * msg = string.Empty; * }*/ } else { msg = "不存在导出数据"; } return(msg); }
//获取本地能源数据 private DataTable GetLocalALL(string tableName) { string sql = String.Format(@"select * from {0} where USER_ID = '{1}' and (CLZZRQ>=#{2}#) and (CLZZRQ<#{3}#)", tableName, Utils.userId, Convert.ToDateTime(dtStartTime.Text), Convert.ToDateTime(dtEndTime.Text).Add(new TimeSpan(24, 0, 0))); DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sql, null); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0) { return(ds.Tables[0].Copy()); } return(null); }
//获取当前页数据 private DataTable queryByPage(int pageNum) { int pageSize = Convert.ToInt32(this.spanNumber.Text); string sqlWhere = this.queryParam(); string sqlVins = string.Format(@"select top {0} * from INFOMATION_ENTITIES where JZNF='{1}' {2} order by CLYXDW desc,VIN desc", (pageSize * pageNum), Settings.Default.ClearYear, sqlWhere); string sqlStr = string.Format(@"select top {0} * from ({1}) order by CLYXDW asc,VIN asc", pageSize, sqlVins); string sqlOrder = string.Format(@"select * from ({0}) order by CLYXDW desc,VIN desc", sqlStr); var ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlOrder, null); return(ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null); }
// 上报成功,返回本地数据库的VID public string GetSussVID(string strBah) { string strVid = ""; string strSql = "SELECT V_ID FROM FC_CLJBXX WHERE VIN ='" + strBah + "'"; DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, strSql, null); if (ds != null) { strVid = ds.Tables[0].Rows[0]["V_ID"].ToString(); } return(strVid); }
public bool GetSaveFlg(string strBah) { bool flgSave = false; string sqlJbxx = "SELECT * FROM FC_CLJBXX WHERE VIN = '" + strBah + "' AND STATUS='1'"; DataSet ds = AccessHelper.ExecuteDataSet(AccessHelper.conn, sqlJbxx, null); if (ds.Tables[0].Rows.Count > 0) { flgSave = true; } return(flgSave); }