public List <ZzhaStation> GetAllStations() { List <ZzhaStation> list = new List <ZzhaStation>(); string selectstr = "select stationaddress,stationheadaddress,stationheadx,stationheady from Station_Head_Info order by stationaddress,stationheadaddress"; //SqlDataReader dr = dba.GetDataReader(selectstr); DataTable dt = dba.GetDataSet(selectstr).Tables[0]; //while (dr.Read()) //{ // ZzhaStation zzhastation = new ZzhaStation(); // zzhastation.StationAddressNum = dr.GetInt32(0).ToString() + "." + dr.GetInt32(1).ToString(); // zzhastation.Position = Convert.ToString(dr.GetDouble(2)) + "," + Convert.ToString(dr.GetDouble(3)); // //zzhastation.Position = Convert.ToString(dr.GetDouble(2) + 25) + "," + Convert.ToString(dr.GetDouble(3) + 20); // list.Add(zzhastation); //} for (int i = 0; i < dt.Rows.Count; i++) { ZzhaStation zzhastation = new ZzhaStation(); //zzhastation.StationAddressNum = dr.GetInt32(0).ToString() + "." + dr.GetInt32(1).ToString(); zzhastation.StationAddressNum = dt.Rows[i][0].ToString() + "." + dt.Rows[i][1].ToString(); zzhastation.Position = Convert.ToString(dt.Rows[i][2]) + "," + Convert.ToString(dt.Rows[i][3]); //zzhastation.Position = Convert.ToString(dr.GetDouble(2) + 25) + "," + Convert.ToString(dr.GetDouble(3) + 20); list.Add(zzhastation); } return(list); }
/* * 方法 */ #region [ 方法: 查询设备信息 ] public DataSet N_GetEquInfo(string strEquNO, string strEquName, string strFacID) { strSql = " Select " + " EquNO as 设备编号, " + " EquName as 设备名称, " + " DeptName as 所属部门, " + " Et1.Title as 设备类型, " + " Et2.Title as 设备状态, " + " FactoryName as 生产厂家, " + " Ebi.Remark as 备注 " + " From " + " Equ_BaseInfo as Ebi left join EnumTable Et1 on Ebi.EquType=Et1.EnumID and Et1.FunID=9 " + " left join EnumTable Et2 on Ebi.EquState=Et2.EnumID and Et2.FunID=10 " + " left join Dept_Info as Di on Di.DeptID=Ebi.DeptID " + " left join FactoryInfo as Fi on Fi.FactoryID=Ebi.FactoryID " + " Where " + " EquID<>0 "; if (!(strEquNO.Equals("") | strEquNO.Equals(null))) { strSql += " And EquNO like '%" + strEquNO + "%'"; } if (!(strEquName.Equals("") | strEquName.Equals(null))) { strSql += " And EquName like '%" + strEquName + "%'"; } if (!strFacID.Equals("0")) { strSql += " And Ebi.FactoryID = " + strFacID; } return(dbacc.GetDataSet(strSql)); }
public void setCboUserGroup(ComboBox comUserName, string user) { string strSql = "Select UGname,id From UserGroups"; //if (user == "3shine") //{ // strSql = " Select UGname,id From UserGroups "; //} //else //{ // strSql = " getUserGroupsByAccount " + user; //} using (ds = new DataSet()) { ds = dbacc.GetDataSet(strSql); if (ds != null) { if (ds.Tables[0].Rows.Count > 0) { comUserName.DataSource = ds.Tables[0].DefaultView; comUserName.DisplayMember = "UGname"; comUserName.ValueMember = "id"; comUserName.SelectedIndex = 0; } else { DataRow dr = ds.Tables[0].NewRow(); dr["UGname"] = "无"; dr["id"] = "0"; ds.Tables[0].Rows.InsertAt(dr, 0); } } } }
public DataSet GetHisBadStationInfo(string strStartTime, string strEndTime, string strStationAddress) { if (strStationAddress.Trim().Equals("")) { strSQL = " Select DISTINCT StationAddress as 分站编号, " + " Count(StationAddress)as '故障次数', " + " (Select dbo.FunConvertTime( sum(BadTime) ) From HistoryBadStations as Hbs2 " + " Where Hbs1.StationAddress=Hbs2.StationAddress and Hbs2.StationHeadAddress=0 " + " And Hbs2.BadBeginTime >='" + strStartTime + "' And Hbs2.BadBeginTime <='" + strEndTime + "' ) as '故障时间' " + " From HistoryBadStations as Hbs1 " + " Where StationHeadAddress =0 And Hbs1.BadBeginTime >='" + strStartTime + "' And Hbs1.BadBeginTime <='" + strEndTime + "' " + " Group by StationAddress "; } else { strSQL = " Select DISTINCT StationAddress as 分站编号, " + " Count(StationAddress)as '故障次数', " + " (Select dbo.FunConvertTime( sum(BadTime) ) From HistoryBadStations as Hbs2 " + " Where Hbs1.StationAddress=Hbs2.StationAddress and Hbs2.StationHeadAddress=0 " + " And Hbs2.BadBeginTime >='" + strStartTime + "' And Hbs2.BadBeginTime <='" + strEndTime + "' And Hbs2.StationAddress=" + strStationAddress + " ) as '故障时间' " + " From HistoryBadStations as Hbs1 " + " Where StationHeadAddress =0 And Hbs1.BadBeginTime >='" + strStartTime + "' And Hbs1.BadBeginTime <='" + strEndTime + "' And Hbs1.StationAddress=" + strStationAddress + " Group by StationAddress "; } return(dbacc.GetDataSet(strSQL)); }
public DataSet Get_EmpInWellStatement(string strDate) { SqlParameter[] para = { new SqlParameter("@strDate", SqlDbType.NVarChar, 20), new SqlParameter("@strTable", SqlDbType.NVarChar, 20) }; para[0].Value = strDate; para[1].Value = DateTime.Parse(strDate).ToString("yyyyM"); return(dba.GetDataSet("A_Statement_EmpInWell", para)); }
/// <summary> /// 获取部门树信息 /// </summary> public DataSet GetHisDeptTree(string strWhere) { string strWhereSql = "1=1"; if (!strWhere.Trim().Equals("")) { strWhereSql = strWhere; } string strSQL = "select Dei.DeptID as ID,Dei.DeptName as Name ,Dei.ParentDeptID as ParentID,'true' as IsChild ,'false' as IsUserNum ,0 as Num From Dept_Info as Dei order by SerialNo, [name]"; return(dba.GetDataSet(strSQL)); }
/// <summary> /// 根据FunID获得对应的数据 /// </summary> /// <param name="funId"></param> /// <returns>Title,EnumID</returns> public DataTable getEnumInfo(int funId) { strSQL = string.Format("select Title,EnumID from EnumTable where FunID = {0}", funId); using (DataSet ds = dba.GetDataSet(strSQL)) { if (ds.Tables.Count > 0) { return(ds.Tables[0]); } } return(new DataTable()); }
/* * 方法 */ #region [ 方法: 查询分站信息 ] /// <summary> /// 查询分站信息 /// </summary> /// <returns></returns> public DataSet N_GetStationInfo() { strSql = " Select " + " StationAddress as " + HardwareName.Value(CorpsName.StationAddress) + ", " + " StationPlace as " + HardwareName.Value(CorpsName.StationSplace) + ", " + " StationTel as 联系电话, " + " Et1.Title as 状态 " + " From " + " Station_Info as Si left join EnumTable as Et1 on Si.StationState=Et1.EnumID and Et1.FunID=7 " + " left join EnumTable as Et2 on Si.StationTypeID=Et2.EnumID and Et2.FunID=1 " + " Order By " + " StationAddress "; return(dbacc.GetDataSet(strSql)); }
public DataTable GetAllRoute() { string sqlstr = "select routefrom,routeto,routelength from route"; DataSet ds = dba.GetDataSet(sqlstr); if (ds != null) { return(ds.Tables[0]); } else { return(new DataTable()); } }
public DataTable GetUserGroups() { string sqlstr = "select [id],ugname from UserGroups"; DataSet ds = dba.GetDataSet(sqlstr); if (ds != null) { return(ds.Tables[0]); } else { return(new DataTable()); } }
public DataTable GetAllFileName() { string sqlstr = "select Filename from ConfigFile_ZZHA"; DataSet ds = dba.GetDataSet(sqlstr); if (ds != null) { return(ds.Tables[0]); } else { return(null); } }
public DataTable GetTerrialInfo() { string sqlstr = "select Territorialid,territorialName from Territorial_Info"; DataSet ds = dba.GetDataSet(sqlstr); if (ds != null) { return(ds.Tables[0]); } else { return(new DataTable()); } }
/// <summary> /// 获取部门信息 /// </summary> /// <returns>返回 部门信息(DataSet)</returns> public DataSet N_GetDeptInfo() { string strSql; strSql = " select DeptID,DeptName,ParentDeptID, " + " (select count(distinct EmpID ) from (select EmpID From dbo.Emp_NowCompany where DeptID=Di.DeptID or DeptID in(select DeptID From Dept_Info Where ParentDeptID=Di.DeptID ) " + " or DeptID in(Select DeptID From Dept_Info where DeptID in(Select DeptID From Dept_Info Where ParentDeptID=Di.DeptID)) " + " or DeptID in(select DeptID From Dept_Info Where DeptID in(select DeptID From Dept_Info Where DeptID in(Select DeptID From Dept_Info where ParentDeptID =Di.DeptID))) " + " )as T1 ) " + " as Counts " + " from Dept_Info as Di " + " Order By DeptLevelID "; return(dbacc.GetDataSet(strSql)); }
public byte[] GetBackMapByFileName(string filename) { string sqlstr = string.Format("select fileimg from A_GraphicsMapFile where [filename]='{0}'", filename); DataSet ds = dba.GetDataSet(sqlstr); if (ds != null) { return((byte[])ds.Tables[0].Rows[0][0]); } else { return(null); } }
public DataTable GetAllStation() { string sqlstr = "select StationAddress,stationplace from Station_Info"; DataSet ds = dba.GetDataSet(sqlstr); if (ds != null) { return(ds.Tables[0]); } else { return(new DataTable()); } }
public DataSet N_SearchDirectionalManage(string txtDetection, string strWhere) { bool blIsFirst = false; strSql = " Select " + " DetectionInfo as 标识, " + " dbo.FunTrendInfo(DetectionInfo) as 位置, " + " Directional as 方向性描述 " + " From " + " CodeSender_Directional "; if (!(txtDetection.Equals("") | txtDetection.Equals(null))) { strSql += " Where DetectionInfo like '%" + txtDetection + "%'"; blIsFirst = true; } if (!(strWhere.Equals("") | strWhere.Equals(null))) { if (blIsFirst) { strSql += " And Directional like '%" + strWhere + "%'"; } else { strSql += " Where Directional like '%" + strWhere + "%'"; } } return(dbacc.GetDataSet(strSql)); }
public DataTable GetHisStationHeadInfo(string sdate, string edate, int stationid, int stationheadid) { string sqlstr = string.Format("SELECT dbo.His_InOutStation.CodeSenderAddress as 标识卡,dbo.Emp_Info.EmpName as 姓名, dbo.Dept_Info.DeptName as 部门, " + "dbo.His_InOutStation.StationHeadDetectTime as 监测时间 " + "FROM dbo.His_InOutStation INNER JOIN " + "dbo.Emp_Info ON " + "dbo.His_InOutStation.UserID = dbo.Emp_Info.EmpID INNER JOIN " + "dbo.Emp_NowCompany ON " + "dbo.Emp_Info.EmpID = dbo.Emp_NowCompany.EmpID INNER JOIN " + "dbo.Dept_Info ON dbo.Emp_NowCompany.DeptID = dbo.Dept_Info.DeptID " + "WHERE (dbo.His_InOutStation.codesenderaddress IN " + "(SELECT DISTINCT codesenderaddress " + "FROM His_InOutMine " + "WHERE outtime > '{0}' AND intime < '{1}')) AND " + "(dbo.His_InOutStation.StationHeadDetectTime < '{1}') " + "and dbo.His_InOutStation.StationAddress={2} and His_InOutStation.StationHeadAddress={3} " + "order by dbo.His_InOutStation.StationHeadDetectTime", sdate, edate, stationid, stationheadid); DataSet ds = dba.GetDataSet(sqlstr); if (ds != null) { return(ds.Tables[0]); } else { return(null); } }
/* * 方法 */ #region [ 方法: 获取低电量信息 ] /// <summary> /// 获取低电量信息 /// </summary> /// <param name="CodeSenderStateID">发码器状态,4:低电量</param> /// <param name="CsTypeID">发码器配置类型;0:人员;1:设备;2:发码器</param> /// <returns></returns> public DataSet N_GetAlarmElectricity(int CodeSenderStateID, int intCsTypeID) { //string procName = "KJ128N_CodeAlarmElectricity_Select"; //SqlParameter[] sqlParmeters ={ // new SqlParameter("CodeSenderStateID",SqlDbType.Int) //}; //sqlParmeters[0].Value = CodeSenderStateID; //return dbacc.GetDataSet(procName, sqlParmeters); strSql = " Select Csi.CodeSenderAddress as 发码器, " + " Et.Title as 发码器状态, " + " 配置类型= case when CsTypeID= 0 then '人员' when CsTypeID=1 then '设备' else '' end, " + " 名称=case when CsTypeID=0 then EmpName when CsTypeID=1 then EquName else '' end, " + " 部门=case when CsTypeID=0 then Di1.DeptName else '' end " + " From CodeSender_Info as Csi left join CodeSender_Set as Css on Csi.CodeSenderAddress =Css.CodeSenderAddress " + " Left Join Emp_Info as Ei on Ei.EmpID=Css.UserID and CsTypeID=0 " + " Left Join Emp_NowCompany as Enc on Enc.EmpID=Ei.EmpID " + " Left Join Dept_Info as Di1 on Di1.DeptID=Enc.DeptID " + " Left Join Equ_BaseInfo as Ebi on Ebi.EquID=Css.UserID and Css.CsTypeID=1 " + " Left Join Dept_Info as Di2 on Di2.DeptID=Ebi.DeptID " + " Left Join EnumTable as Et on Et.EnumID=Csi.CodeSenderStateID and Et.FunID=2 " + " Where Csi.CodeSenderStateID = " + CodeSenderStateID; if (intCsTypeID.Equals(0) || intCsTypeID.Equals(1)) { strSql += " And CsTypeID =" + intCsTypeID; } else if (intCsTypeID.Equals(2)) { strSql += " And CsTypeID is Null "; } return(dbacc.GetDataSet(strSql)); }
/* * 方法 */ #region [ 方法: 获取人员信息 ] public DataSet GetEmpInfo(string strID, bool blIsEmp) { strSql = " Select Css.CodeSenderAddress as 发码器编号, Ei.EmpName as 姓名, " + " 身份证号= case when Ed.Idcard is null or Ed.Idcard='' then '无' else Ed.Idcard end, " + " 出生年月= case when Ed.BirthDay is null then '无' else substring(convert(varchar(10), Ed.BirthDay,120),1,7) end ," + " 职务= case when Dui.DutyName is null then '无' else Dui.DutyName end , " + " 工种=case when Wti.WtName is null then '无' else Wti.WtName end, " + " 所在区队班组= case when Enc.ClassGroup is null or Enc.ClassGroup='' then '无' else Enc.ClassGroup end, " + " 工作地点= case when Enc.WorkPlace is null or Enc.WorkPlace='' then '无' else Enc.WorkPlace end " + " From Emp_Info as Ei Left Join Emp_Detail as Ed on Ei.EmpID= Ed.EmpID " + " Left Join Emp_NowCompany as Enc on Ei.EmpID=Enc.EmpID " + " Left Join Emp_WorkType as Ewt on Ei.EmpID=Ewt.EmpID " + " Left Join WorkType_Info as Wti on Ewt.WorkTypeID=Wti.WorkTypeID " + " Left Join Duty_Info as Dui on Enc.DutyID=Dui.DutyID " + " Left Join CodeSender_Set as Css on Ei.EmpID=Css.UserID and CsTypeID=0 "; if (blIsEmp) { strSql += " Where Ei.EmpID =" + strID; } else { strSql += " Where Css.CodeSenderAddress =" + strID; } return(dbacc.GetDataSet(strSql)); }
public DataSet A_RT_PathCheck(string StrWhere) { string sql = "SELECT r.ID, dbo.CodeSender_Set.CodeSenderAddress AS 标识卡号, e.EmpName AS 姓名,wtif.WtName as 工种," + "dbo.Dept_Info.DeptName AS 部门,du.DutyName 职务, t.IntervalName AS 班次," + "dbo.Path_Info.PathName AS 巡检路线, r.CheckTime AS 开始巡检时间, " + "dbo.RealTimeCodeSender.LastPlace AS 现处位置, " + "dbo.RealTimeCodeSender.StationHeadDetectTime AS 入现处位置时间, r.EmpID, " + "dbo.Path_Info.Id AS pid, t.ID AS tid " + "FROM dbo.Path_Info INNER JOIN " + "dbo.RealTimePathCheck r INNER JOIN " + "dbo.Emp_Info e ON r.EmpID = e.EmpID left join " + "dbo.Emp_NowCompany as enow on e.EmpID=enow.EmpID left join " + "dbo.Duty_Info as du on enow.DutyID=du.DutyID LEFT JOIN " + "dbo.Path_Emp_Relation p ON r.EmpID = p.EmpID left JOIN " + "dbo.Emp_WorkType as empw on p.EmpID=empw.EmpID left join " + "dbo.WorkType_Info as wtif on empw.WorkTypeID=wtif.WorkTypeID left join " + "dbo.TimerInterval t ON r.[Interval] = t.ID INNER JOIN " + "dbo.CodeSender_Set ON e.EmpID = dbo.CodeSender_Set.UserID ON " + "dbo.Path_Info.PathNo = p.PathNo INNER JOIN " + "dbo.RealTimeCodeSender ON " + "dbo.CodeSender_Set.CsSetID = dbo.RealTimeCodeSender.CsSetID LEFT JOIN " + "dbo.Dept_Info INNER JOIN " + "dbo.Emp_NowCompany ON " + "dbo.Dept_Info.DeptID = dbo.Emp_NowCompany.DeptID ON " + "e.EmpID = dbo.Emp_NowCompany.EmpID where " + StrWhere; return(db.GetDataSet(sql)); }
public DataSet SelectSpeedConfig(string strWhere) { strSQL = " Select FirstStationAddress as 起始传输分站编号, " + " FirstStationHeadAddress as 起始读卡分站编号, " + " Shi1.StationHeadPlace as 起始读卡分站位置, " + " LastStationAddress as 终点传输分站编号, " + " LastStationHeadAddress as 终点读卡分站编号, " + " Shi2.StationHeadPlace as 终点读卡分站位置, " + " OverSpeedTime = case when WalkTime=-1 then null else dbo.FunConvertTime(WalkTime) end, " + " LackSpeedTime = case When LackWalkTime =-1 then null else dbo.FunConvertTime(LackWalkTime) end," + " Os.Remark as 备注,OverSpeedID " + " From OverSpeed as Os left join Station_Head_Info as Shi1 on Os.FirstStationAddress=Shi1.StationAddress and Os.FirstStationHeadAddress=Shi1.StationHeadAddress " + " Left join Station_Head_Info as Shi2 on Os.LastStationAddress = Shi2.StationAddress and Os.LastStationHeadAddress=Shi2.StationHeadAddress " + " Where " + strWhere; return(dba.GetDataSet(strSQL)); }
public DataRow getStationInfo(int stationAddress) { string sqlString = string.Format("select * from Station_Info where StationAddress = {0}", stationAddress); DataSet ds = dba.GetDataSet(sqlString); if (ds.Tables != null && ds.Tables[0].Rows.Count != 0) { return(ds.Tables[0].Rows[0]); } return(ds.Tables[0].Rows[0]); }
/// <summary> /// 根据分站状态获取接收器信息 /// </summary> /// <param name="strState">分站状态</param> /// <returns></returns> public DataSet N_GetStaHeadBreakInfo(string strState) { strSql = " Select * From KJ128N_RTStaHead_Info_View "; if (!(strState.Equals("所有") | strState.Equals(null))) { strSql += " Where 接收器状态='" + strState + "' "; } strSql += " Order By 分站地址,接收器地址 "; return(dbacc.GetDataSet(strSql)); }
/// <summary> /// 查询 实时分站信息 /// </summary> /// <returns></returns> public DataSet N_StaBreakInfo(string strState) { strSql = " Select * From KJ128N_RTStation_Info_View "; if (!(strState == "" | strState.Equals("所有") | strState.Equals(null))) { strSql += " Where 分站状态='" + strState + "' "; } strSql += " Order By 分站地址 "; return(dbacc.GetDataSet(strSql)); }
public DataSet N_SearchRTInOutAntennaInfo( string strStartTime, string strEndTime, string strCard, string strStationAddress, string strStationHeadAddress, int intUserType) { strSql = " Select " + " Ri.CodeSenderAddress As " + HardwareName.Value(CorpsName.CodeSenderAddress) + ", " + " StationAddress As " + HardwareName.Value(CorpsName.StationAddress) + ", " + " StationHeadAddress As " + HardwareName.Value(CorpsName.StaHeadAddress) + ", " + " StationHeadDetectTime As 接收器监测时间, " + " StationHeadAntennaA As 天线A, " + " StationHeadAntennaB As 天线B, " + " 进出状态=case InStationHeadAntenna when 1 then '进读卡分站' when 2 then '进读卡分站' else '出读卡分站' end, " + " LastStationAddress As 上次" + HardwareName.Value(CorpsName.StationAddress) + ", " + " LastStationHeadAddress As 上次" + HardwareName.Value(CorpsName.StaHeadAddress) + ", " + " LastStationHeadAntennaA As 上次天线A, " + " LastStationHeadAntennaB As 上次天线B " + " From " + " RT_InOutStation as Ri " + " left join CodeSender_Set as Css on Ri.CodeSenderAddress=Css.CodeSenderAddress " + " Where " + " StationHeadDetectTime >= '" + strStartTime + "' And StationHeadDetectTime <= '" + strEndTime + "' "; if (!(strCard.Equals("") | strCard.Equals(null))) { strSql += " And Ri.CodeSenderAddress = " + strCard; } ////发码器类型 if (intUserType.Equals(2)) //未登记发码器 { strSql += " And CsTypeID is Null "; } else if (intUserType.Equals(1) || intUserType.Equals(0)) //人员、设备 { strSql += " And CsTypeID =" + intUserType; } if (!(strStationAddress.Equals("0"))) { strSql += " And StationAddress = " + strStationAddress; } if (!(strStationHeadAddress.Equals("0"))) { strSql += " And StationHeadAddress = " + strStationHeadAddress; } return(dbacc.GetDataSet(strSql)); }
public string GetDeptCounts(string strDeptID, string strStartTime, string strEndTime) { string strSql; if (strDeptID == "0") { strSql = " Select count(distinct Hi.UserID) as Counts " + " From His_InOutMine as Hi left join Emp_NowCompany as Enc on Enc.EmpID=Hi.UserID and CsTypeID=0 " + " Where InTime>='" + strStartTime + "' And InTime<='" + strEndTime + "'" + " And CsTypeID=0 "; } else { strSql = " Select count(distinct Hi.UserID) as Counts " + " From His_InOutMine as Hi left join Emp_NowCompany as Enc on Enc.EmpID=Hi.UserID and CsTypeID=0 " + " Where ( DeptID=" + strDeptID + " or DeptID in(select DeptID From Dept_Info Where ParentDeptID=" + strDeptID + " ) " + " or DeptID in(Select DeptID From Dept_Info where DeptID in(Select DeptID From Dept_Info Where ParentDeptID=" + strDeptID + ")) " + " or DeptID in(select DeptID From Dept_Info Where DeptID in(select DeptID From Dept_Info Where DeptID in(Select DeptID From Dept_Info where ParentDeptID =" + strDeptID + ")))) " + " And InTime>='" + strStartTime + "' And InTime<='" + strEndTime + "'" + " And CsTypeID=0 "; } DataSet tempDs; using (tempDs = new DataSet()) { tempDs = dbacc.GetDataSet(strSql); if (tempDs != null && tempDs.Tables.Count > 0) { if (tempDs.Tables[0].Rows.Count > 0) { return(tempDs.Tables[0].Rows[0]["Counts"].ToString()); } } } return(null); }
public DataSet GetDeptTree_Emp(bool bl) { //if (bl) //显示上井口信息 //{ // strSQL = " Select * From A_DeptTree_RTStaHead_Emp "; //} //else //不显示上井口信息 //{ // //(Select count(1) From A_RTStaHead_Emp as Rt Where Rt.DeptID=Dei.DeptID and StationHeadTypeID=32) // strSQL = " Select Dei.DeptID as ID, " + // " Dei.DeptName as Name , " + // " Dei.ParentDeptID as ParentID, " + // " 'true' as IsChild , " + // " 'false' as IsUserNum , " + // " 0 as Num " + // " From Dept_Info as Dei "; //} strSQL = " Select Dei.DeptID as ID, " + " Dei.DeptName as Name , " + " Dei.ParentDeptID as ParentID, " + " 'true' as IsChild , " + " 'false' as IsUserNum , " + " 0 as Num " + " From Dept_Info as Dei order by serialno,DeptName "; return(dba.GetDataSet(strSQL)); }
/// <summary> /// 得到所有探头信息 /// </summary> /// <returns>信息表</returns> public DataTable GetStationInfo() { string selectstring = "select * from A_Graphics_StationHeadState"; DataSet ds = dba.GetDataSet(selectstring); if (ds != null) { return(ds.Tables[0]); } else { return(null); } }
public DataSet SelectOverEmp(int intOverEmpType) { strSql = " Select " + " RT_RatingEmployeesCount as 额定下井人数 , " + " RT_FactEmployeeCount as 当前下井人数, " + " RT_FactEmployeeCount-RT_RatingEmployeesCount as 超员人数, " + " RT_OverEmployeeBeginTime as 超员开始时间, " + " dbo.FunConvertTime((Select DateDiff(ss, Ro.RT_OverEmployeeBeginTime, getDate()))) As 持续时间" + " From " + " RT_OverEmployees as Ro" + " Where " + " Ro.RT_OverEmployeeTypeID = " + intOverEmpType.ToString(); return(dbacc.GetDataSet(strSql)); }
/// <summary> /// 根据部门获取标识卡号信息 /// </summary> /// <param name="deptID"></param> /// <returns></returns> public DataSet GetEmpInfo(string deptID) { string strWhere = ""; if (deptID.Trim() != "") { strWhere = "e.DeptID=" + deptID; } else { strWhere = "1=1"; } string strSql = "select convert(varchar(20),cs.CodeSenderAddress) as CodeSenderAddress,e.empName from emp_info e join CodeSender_Set cs ON e.empid=cs.userID AND cs.CsTypeID = 0 where " + strWhere; return(DB.GetDataSet(strSql)); }
/// <summary> /// 备份指定数据库文件 /// </summary> /// <param name="strDbName">数据库名称</param> /// <param name="strFileName">备份数据库的路径</param> /// <param name="strServerName">服务器名称</param> /// <param name="strUserName">用户名</param> /// <param name="strPassword">密码</param> /// <param name="prosBar">进度条</param> /// <returns></returns> public static bool BackUPDB(string strDbName, string strFileName, string strServerName, string strUserName, string strPassword, ProgressBar bar) { #region [ 判断磁盘剩余空间是否可进行备份 ] DBAcess dba = new DBAcess(); DataSet tmpds = dba.GetDataSet("exec sp_spaceused"); if (tmpds.Tables.Count > 0 && tmpds.Tables[0] != null) { // 得到数据库大小 string tmpStr = tmpds.Tables[0].Rows[0]["database_size"].ToString(); int dbSize = Convert.ToInt32(tmpStr.Substring(0, tmpStr.LastIndexOf("."))); // 获取磁盘剩余空间大小 try { DriveInfo d = new DriveInfo(strFileName.Substring(0, 1)); int diskSize = Convert.ToInt32(d.AvailableFreeSpace / 1024 / 1024); if (diskSize < dbSize) { MessageBox.Show("备份数据库所需空间不能小于" + dbSize + "M\r\n磁盘[" + d.Name + "]存储空间过小,无法备份"); return false; } } catch (Exception) { MessageBox.Show("路径不正确"); return false; //throw; } } #endregion pBar = bar; string strTmp = ""; string tmpPath = strFileName.Substring(0, strFileName.LastIndexOf("\\")).ToString(); int isEmpty = tmpPath.IndexOf(" "); SQLDMO.SQLServer svr = null; try { svr = new SQLDMO.SQLServerClass(); // 连接到数据库 svr.Connect(strServerName, strUserName, strPassword); SQLDMO.Backup bak = new SQLDMO.BackupClass(); bak.Action = 0; bak.Initialize = true; #region 进度条处理 if (pBar != null) { pBar.Visible = true; SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step); bak.PercentComplete += pceh; } #endregion #region [ 文件夹名称中有空格: 备份前的处理 ] // 文件夹不存在时自动创建 if (!Directory.Exists(tmpPath)) { Directory.CreateDirectory(tmpPath); } // 文件夹名称 中有空格 备份文件路径设置为根目录的临时文件夹tmpBackup中 if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1) { strTmp = strFileName.Substring(0, 1).ToString() + ":\\tmp_backup.kj"; } else { strTmp = strFileName; } #endregion // 数据库的备份的名称及文件存放位置 bak.Files = strTmp; bak.Database = strDbName; // 备份 bak.SQLBackup(svr); } catch (Exception err) { if (SqlErrRepair(err.Message.ToString())) { BackUPDB(strDbName, strFileName, strServerName, strUserName, strPassword, pBar); return true; } return false; //MessageBox.Show("备份数据库失败"); } finally { if (svr != null) { svr.DisConnect(); } #region [ 文件夹名称中有空格: 备份完成后的处理 ] // 文件夹名称 中有空格 将备份的文件移动到用户指定的文件夹并将临时目录删除 if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1) { // 文件存在则替换 if (File.Exists(strFileName.Substring(strFileName.LastIndexOf("\\") + 2))) { File.Delete(strFileName.Substring(strFileName.LastIndexOf("\\") + 2)); } File.Move(strTmp, strFileName); } #endregion } return true; }
public static void ShrinkDataBase() { DBAcess dba = new DBAcess(); try { // 数据库名从连接字符串中得到 string dbName = ConfigurationSettings.AppSettings["ConnectionString"];//dba.ConnectionStringKJ128N; dbName = dbName.Substring(dbName.IndexOf("database=") + 9); dbName = dbName.Substring(0, dbName.IndexOf(";")); dba.GetDataSet("DUMP TRANSACTION " + dbName + " WITH NO_LOG"); MessageBox.Show("清空日志成功"); } catch (Exception ex) { MessageBox.Show("清空日志失败"); } }