private void GvBind() { SqlParameter[] p = new SqlParameter[] { new SqlParameter("@ProjectNo", cbbXM.SelectedValue), new SqlParameter("@PartNo", cbbLJ.SelectedValue) }; var dbc = db.GetSqlStringCommand("SELECT [MeasReportID],[ProjectNo],[PartNo],[PartName],[CreateTime] FROM [MeasurementReports] WHERE (''=@ProjectNo OR [ProjectNo]=@ProjectNo) AND (''=@PartNo OR [PartNo]=@PartNo)"); dbc.Parameters.AddRange(p); var ds = db.ExecuteDataSet(dbc); GvList.DataSource = ds.Tables[0]; }
private void bind() { string status = ""; string PreWwarningTimeStart = ""; string PreWwarningTimeEnd = ""; if (!cbbStatus.SelectedItem.ToString().Contains("请选择")) { if (cbbStatus.SelectedItem.ToString().Contains("未处理")) { status = "0"; } else { status = "1"; } } if (!(dateTimePicker1.CustomFormat == " ")) { PreWwarningTimeStart = dateTimePicker1.Value.ToString().Trim(); } if (!(dateTimePicker2.CustomFormat == " ")) { PreWwarningTimeEnd = dateTimePicker2.Value.ToString().Trim(); } SqlParameter[] p = new SqlParameter[] { new SqlParameter("@PreWwarningTimeStart", PreWwarningTimeStart), new SqlParameter("@PreWwarningTimeEnd", PreWwarningTimeEnd), new SqlParameter("@status", status) }; string sql = @"SELECT [id],[Message],[Filed1],[Filed2],[WarningType],[PreWwarningTime],[DoTime],case when status = 0 then '未处理' else '已处理' end as [status] FROM [dbo].[Warnings] where (''=@PreWwarningTimeStart or ''=@PreWwarningTimeEnd or (PreWwarningTime>=@PreWwarningTimeStart and PreWwarningTime<=@PreWwarningTimeEnd) ) and (''=@status or status=@status) "; var dbc = db.GetSqlStringCommand(sql); dbc.Parameters.AddRange(p); var ds = db.ExecuteDataSet(dbc); GvList.DataSource = ds.Tables[0]; }
private void bind() { string strSqlReport = "select distinct [ProjectNo] text,[ProjectNo] value from [MeasurementReports]"; var ds = db.ExecuteDataSet(CommandType.Text, strSqlReport); if (null != ds) { var dt = ds.Tables[0]; DataRow newRow; newRow = dt.NewRow(); newRow["text"] = "==请选择项目名称=="; newRow["value"] = ""; dt.Rows.InsertAt(newRow, 0); cbbXM.ValueMember = "value"; cbbXM.DisplayMember = "text"; cbbXM.DataSource = dt; } }
/// <summary> /// Exceute an sql statement (or multiple) and return a dataset /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataSet ExecuteDataSet(string sql) { try { Log.Debug("\tSQL: " + sql); DataSet ret = ODHR.Utils.Caching.CallStackCaching.GetFromCache <DataSet>(sql); if (ret != null) { return(ret); } ret = Db.ExecuteDataSet(CommandType.Text, sql); ODHR.Utils.Caching.CallStackCaching.SaveToCache(sql, ret); return(ret); } catch (Exception e) { Log.Fatal("\tExecuteDataSet - " + e.Message + " SQL:[" + (sql ?? "Null") + "]", e); throw; } }
private void bind() { SqlParameter[] p = new SqlParameter[] { new SqlParameter("@MeasReportID", id) }; var dbc = db.GetSqlStringCommand("SELECT [MeasItemID],[MeasReportID],[MeasItemNO] FROM [MeasurementItems] where MeasReportID=@MeasReportID"); dbc.Parameters.AddRange(p); var ds = db.ExecuteDataSet(dbc); GvList.DataSource = ds.Tables[0]; }
private void btnOk_Click(object sender, EventArgs e) { if (string.IsNullOrWhiteSpace(txtUser.Text.Trim()) || string.IsNullOrWhiteSpace(txtPwd.Text)) { MessageBox.Show("用户名,密码不能是空"); } else { SqlParameter[] p = new SqlParameter[] { new SqlParameter("@username", txtUser.Text.Trim()), new SqlParameter("@userType", UserType.Application) }; var dbc = db.GetSqlStringCommand("select * from Users where username=@username and userType=@userType"); dbc.Parameters.AddRange(p); var ds = db.ExecuteDataSet(dbc); if (null != ds && ds.Tables[0].Rows.Count > 0) { var pwd = ds.Tables[0].Rows[0]["password"].ToString(); if (txtPwd.Text.Trim() != pwd) { MessageBox.Show("用户名,密码不正确"); } else { base.DialogResult = DialogResult.OK; } } else { MessageBox.Show("用户名,密码不正确"); } } }
// GET: api/MeasureDatas public IQueryable <JsonProject> GetCreateJson(string ids, string username) { try { string sql = @" select a.* from [Vw_ProjectSettingWithId] a left join [Vw_ProjectSettingWithId] b on a.ProjectSetting_ProjectNO=b.ProjectSetting_ProjectNO and a.ProjectSetting_PartsNO=b.ProjectSetting_PartsNO where b.ProjectSetting_ID in ({0}); select b.* from [PointSetting] b left join [ProjectSetting] c on b.PointSetting_ProjectSetting_ID=c.ProjectSetting_ID left join [ProjectSetting] d on c.ProjectSetting_ProjectNO=d.ProjectSetting_ProjectNO and c.ProjectSetting_PartsNO =d.ProjectSetting_PartsNO where d.ProjectSetting_ID in ({0}) and b.PointSetting_IsDeleted=0 order by b.PointSetting_Index asc ; select [UserInfo_RealName],[UserInfo_password] from [User_Info] where [UserInfo_Type]=2 "; var ds = _db.ExecuteDataSet(CommandType.Text, string.Format(sql, ids)); var tab1 = ds.Tables[0]; var tab2 = ds.Tables[1]; var tab3 = ds.Tables[2]; var jsonProject = new List <JsonProject>() { }; DataTable MeasurementReport = tab1.DefaultView.ToTable(true, "ProjectSetting_ProjectNO", "ProjectId"); foreach (DataRow row in MeasurementReport.Rows) { jsonProject.Add(GetJsonProject(tab1, tab2, int.Parse(row["ProjectId"].ToString()), row["ProjectSetting_ProjectNO"].ToString())); } List <JsonUsers> jsonUser = new List <JsonUsers>() { }; foreach (DataRow row in tab3.Rows) { jsonUser.Add(new JsonUsers() { user_id = 0, username = row["UserInfo_RealName"].ToString(), password = row["UserInfo_password"].ToString() }); } JavaScriptSerializer jsonSerialize = new JavaScriptSerializer(); #region project var TEMP = jsonSerialize.Serialize(jsonProject); var mappedPath = System.Web.Hosting.HostingEnvironment.MapPath("~/res/" + username + ".json"); if (System.IO.File.Exists(mappedPath)) { System.IO.File.Delete(mappedPath); } FileStream fs = new FileStream(mappedPath, FileMode.Create); //获得字节数组 //byte[] data = System.Text.Encoding.Default.GetBytes(TEMP.Replace("\\u0026", "&")); byte[] data = System.Text.Encoding.GetEncoding("UTF-8").GetBytes(TEMP.Replace("\\u0026", "&")); //开始写入 fs.Write(data, 0, data.Length); //清空缓冲区、关闭流 fs.Flush(); fs.Close(); #endregion #region User var tempUser = jsonSerialize.Serialize(jsonUser); var userpath = System.Web.Hosting.HostingEnvironment.MapPath("~/res/user.json"); if (System.IO.File.Exists(userpath)) { System.IO.File.Delete(userpath); } FileStream fsUser = new FileStream(userpath, FileMode.Create); //获得字节数组 //byte[] data = System.Text.Encoding.Default.GetBytes(TEMP.Replace("\\u0026", "&")); byte[] data1 = System.Text.Encoding.GetEncoding("UTF-8").GetBytes(tempUser.Replace("\\u0026", "&")); //开始写入 fsUser.Write(data1, 0, data1.Length); //清空缓冲区、关闭流 fsUser.Flush(); fsUser.Close(); return(jsonProject.AsQueryable()); #endregion } catch (Exception ex) { throw ex; } }
private void btnDown_Click(object sender, EventArgs e) { string sql = @"SELECT [ProjectName] 项目号 ,[ProductName] 零件号 ,[TargetName] 检测项 ,[MeasurePoint] 检测点 ,[Direction] 方向 ,[UpperTolerance] 上公差 ,[LowerTolerance] 下公差 ,[Value1] 值1 ,[Value2] 值2 ,[Value3] 值3 ,[Value4] 值4 ,[Value5] 值5 ,[Value6] 值6 ,[Value7] 值7 ,[Value8] 值8 ,[Value9] 值9 ,[Value10] 值10 ,[Username] 用户 ,CONVERT(varchar(100), DATEADD(s,convert(int,Timestamp/1000),'1970-01-01 08:00:00'), 112) 时间 FROM [MeasureDatas] where (''=@ProjectName or ProjectName like @ProjectName) and (''=@ProductName or ProductName like @ProductName) and (''=@TargetName or TargetName like @TargetName) " ; SqlParameter[] p = new SqlParameter[] { new SqlParameter("@ProjectName", "%" + txtXM.Text.Trim() + "%"), new SqlParameter("@ProductName", "%" + txtCP.Text.Trim() + "%"), new SqlParameter("@TargetName", "%" + txtJCX.Text.Trim() + "%") }; var dbc = db.GetSqlStringCommand(sql); dbc.Parameters.AddRange(p); var ds = db.ExecuteDataSet(dbc); if (null == ds || ds.Tables[0].Rows.Count <= 0) { MessageBox.Show("没有可下载文件"); } FolderBrowserDialog path = new FolderBrowserDialog(); path.ShowDialog(); var dir = path.SelectedPath; var FillPath = string.Format("{0}/{1}", dir, Guid.NewGuid().ToString() + ".xlsx"); ExcelHelper1 excel = new ExcelHelper1(FillPath); excel.DataTableToExcel(ds.Tables[0], "检测值", true); excel.Dispose(); MessageBox.Show("下载成功"); }
/// <summary> /// 查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(comboBox2.SelectedValue.ToString().Trim())) { MessageBox.Show("检验项目不能为空!"); return; } if (string.IsNullOrEmpty(comboBox1.SelectedValue.ToString())) { MessageBox.Show("报告单号不能为空!"); return; } try { this.dataGridView1.DataSource = null; this.pictureBox1.Image = null; //测量报告表 StringBuilder tempSql = new StringBuilder(); tempSql.Append(" select c.MeasPageNo,d.PointNo,d.PointType,d.XAxis,d.YAxis,d.ZAxis,d.UpperTol,d.LowerTol,d.Direct,d.[AVG],d.CorrectDirect"); tempSql.Append(" from ((MeasurementReports a left join MeasurementItems b on a.MeasReportID=b.MeasReportID) "); tempSql.Append(" left join MeasurementPages c on b.MeasItemID=c.MeasItemID)"); tempSql.Append(" left join MeasurementPoints d on c.MeasPageID=d.MeasPageID"); tempSql.Append(" where 1=1 "); tempSql.Append(" and a.ProjectNo='" + comboBox1.SelectedValue.ToString() + "'"); tempSql.Append(" and b.MeasItemName='" + comboBox2.SelectedValue.ToString() + "'"); if (!string.IsNullOrEmpty(txtYM.Text.Trim())) { tempSql.Append(" and c.MeasPageNo=" + txtYM.Text.Trim()); } tempSql.Append(" order by a.MeasReportID,b.MeasItemID,c.MeasPageID,d.MeasPointID"); Os.Brain.Common.Debug.WriteLog("./debug.txt", tempSql.ToString()); var ds = db.ExecuteDataSet(CommandType.Text, tempSql.ToString()); if (null == ds && ds.Tables.Count <= 0) { MessageBox.Show("查无数据"); return; } DataTable dt = ds.Tables[0]; if (dt != null && dt.Rows.Count > 0) { this.dataGridView1.DataSource = dt; } for (int i = 0; i < this.dataGridView1.Columns.Count; i++) { this.dataGridView1.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable; } if (!string.IsNullOrEmpty(txtYM.Text.Trim())) { StringBuilder tempSqlimage = new StringBuilder(); tempSqlimage.Append(" select c.MeasPageImage"); tempSqlimage.Append(" from (MeasurementReports a left join MeasurementItems b on a.MeasReportID=b.MeasReportID) "); tempSqlimage.Append(" left join MeasurementPages c on b.MeasItemID=c.MeasItemID"); tempSqlimage.Append(" where 1=1 "); tempSqlimage.Append(" and a.ProjectNo='" + comboBox1.SelectedValue.ToString() + "'"); tempSqlimage.Append(" and b.MeasItemName='" + comboBox2.SelectedValue.ToString() + "'"); tempSqlimage.Append(" and c.MeasPageNo=" + txtYM.Text.Trim()); tempSqlimage.Append(" order by a.MeasReportID,b.MeasItemID,c.MeasPageID"); var dsimg = db.ExecuteDataSet(CommandType.Text, tempSqlimage.ToString()); DataTable dtimage = dsimg.Tables[0]; if (dtimage != null && dtimage.Rows.Count > 0) { Byte[] bytes = dtimage.Rows[0][0] as Byte[]; if (null != bytes) { MemoryStream ms = new MemoryStream(bytes); //核心方法 将图片加载到内存流中 this.pictureBox1.Image = Image.FromStream(ms); } } } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void GvBind() { if (this.pid <= 1) { this.pid = 1; } if (this.pid >= max) { this.pid = max; } SqlParameter[] p = new SqlParameter[] { new SqlParameter("@MeasPageNo", pid), new SqlParameter("@MeasItemID", iid) }; string sql = @"SELECT a.MeasPageNo,b.MeasItemNO,c.ProjectNo,c.PartNo,c.PartName FROM [QfbServer].[dbo].[MeasurementPages] a left join [dbo].[MeasurementItems] b on a.MeasItemID=b.MeasItemID left join [dbo].[MeasurementReports] c on b.MeasReportID=c.MeasReportID where a.MeasPageNo=@MeasPageNo and b.MeasItemID=@MeasItemID; select [MeasPointID],[PointNo],[UpperTol],[LowerTol],[Direct] from [dbo].[MeasurementPoints] a left join [dbo].[MeasurementPages] b on a.MeasPageID=b.MeasPageID where b.MeasPageNo=@MeasPageNo and b.MeasItemID=@MeasItemID; SELECT count(*) num FROM [MeasurementPages] where [MeasItemID]=@MeasItemID "; var dbc = db.GetSqlStringCommand(sql); dbc.Parameters.AddRange(p); var ds = db.ExecuteDataSet(dbc); if (null != ds) { lbl1.Text = string.Format(_temp, ds.Tables[0].Rows[0]["ProjectNo"].ToString(), ds.Tables[0].Rows[0]["PartNo"].ToString(), ds.Tables[0].Rows[0]["PartName"].ToString(), ds.Tables[0].Rows[0]["MeasItemNO"].ToString() ); var _num = ds.Tables[2].Rows[0][0].ToString(); if (!string.IsNullOrEmpty(_num)) { max = int.Parse(_num); } if (pid > 1) { btnPre.Enabled = true; } else { btnPre.Enabled = false; } if (pid < max) { btnNext.Enabled = true; } else { btnNext.Enabled = false; } lblPage.Text = string.Format("{0} of {1}", pid, max); GvList.DataSource = ds.Tables[1]; } }