public DataTable Get() { sSQL = "SELECT * FROM " + tablename + " where 1=1 "; DataTable dt = clsSQLCommond.ExecQuery(sSQL); return(dt); }
//public static string GetSerialNumberRules(string TableName, string FieldName) //{ // string sSQL = "select * from _SerialNumber where TableID='" + TableName + "' and Code='" + FieldName + "'"; // DataTable dt = clsSQLCommond.ExecQuery(sSQL); // if (dt.Rows.Count > 0 && dt.Rows[0]["Type"].ToString()!="") // { // return dt.Rows[0]["Type"].ToString(); // } // else // { // return ""; // } //} /// <summary> /// 得到树状结构 /// </summary> /// <param name="treeList1"></param> /// <param name="TableID"></param> /// <param name="TopName"></param> //public static void GetTree(DevExpress.XtraTreeList.TreeList treeList1, string TableID, string FieldName, string TopName) //{ // try // { // treeList1.ClearNodes(); // } // catch // { // } // string sSQL = "select * from _SerialNumber where TableID='" + TableID + "' and Code='" + FieldName + "'"; // DataTable dts = clsSQLCommond.ExecQuery(sSQL); // string Code = dts.Rows[0]["Code"].ToString(); // string Name = dts.Rows[0]["Name"].ToString(); // sSQL = "select * from " + TableID + " order by " + Code; // DataTable dt = clsSQLCommond.ExecQuery(sSQL); // string 序列号规则 = 系统服务.序号.GetSerialNumberRules(TableID,FieldName); // string[] 序列号规则数组 = 序列号规则.Split('-'); // int top = 序列号规则数组[0].Length; // object[] obj1 = new object[2]; // obj1[0] = TopName; // obj1[1] = ""; // DevExpress.XtraTreeList.Nodes.TreeListNode tn1 = treeList1.AppendNode(obj1, null); // tn1.Tag = ""; // for (int i = 0; i < dt.Rows.Count; i++) // { // if (dt.Rows[i][Code].ToString().Length == top) // { // object[] obj = new object[2]; // obj[0] = dt.Rows[i][Code].ToString().Trim(); // obj[1] = dt.Rows[i][Name].ToString().Trim(); // DevExpress.XtraTreeList.Nodes.TreeListNode tn = treeList1.AppendNode(obj, tn1); // tn.Tag = dt.Rows[i][Code].ToString(); // GetTreeNode(treeList1, tn, dt, Code, Name, 序列号规则数组, 1, top + 序列号规则数组[1].Length); // } // } // //if (treeList1.FocusedNode != null) // //{ // // try // // { // // treeList1.FocusedNode = null; // // } // // catch // // { // // } // //} //} //private static void DeleteTreeListNode(DevExpress.XtraTreeList.Nodes.TreeListNode tn) //{ // for (int i = tn.Nodes.Count - 1; i >= 0; i--) // { // if (tn.Nodes[i].Nodes.Count > 0) // { // DeleteTreeListNode(tn.Nodes[i]); // } // tn.TreeList.DeleteNode(tn.Nodes[i]); // } //} //private static void GetTreeNode(DevExpress.XtraTreeList.TreeList treeList1, DevExpress.XtraTreeList.Nodes.TreeListNode tn, DataTable dt, string Code, string Name, string[] seq, int lev, int len) //{ // if (tn.Tag.ToString().Trim() != "") // { // DataRow[] dw = dt.Select(Code + " like '" + tn.Tag.ToString().Trim() + "%' and len(" + Code + ")=" + len); // for (int i = 0; i < dw.Length; i++) // { // object[] obj = new object[2]; // obj[0] = dw[i][Code].ToString().Trim(); // obj[1] = dw[i][Name].ToString().Trim(); // DevExpress.XtraTreeList.Nodes.TreeListNode tn1 = treeList1.AppendNode(obj, tn); // tn1.Tag = dw[i][Code].ToString(); // if (seq.Length > lev + 1) // { // GetTreeNode(treeList1, tn1, dt, Code, Name, seq, lev + 1, len + seq[lev + 1].Length); // } // } // } //} /// <summary> /// 判断编码原则 -- TH 2012-12-25 20:52 /// </summary> /// <param name="TableID"></param> /// <param name="Code"></param> /// <param name="thisID"></param> /// <returns></returns> //public static string CheckSerialNumber(string TableID, string thisID) //{ // string sSQL = "SELECT TableID, TableName, Code, Name, Type FROM _SerialNumber where TableID = '" + TableID + "'"; // DataTable dt = clsSQLCommond.ExecQuery(sSQL); // string sType = dt.Rows[0]["Type"].ToString().Trim(); // int iLength = thisID.Trim().Length; // string[] sList = sType.Split('-'); // int i编码规则长度 = 0; // string oldID = ""; // for (int i = 0; i < sList.Length; i++) // { // i编码规则长度 = i编码规则长度 + sList[i].Length; // if (i编码规则长度 < iLength) // { // oldID = thisID.Substring(0, i编码规则长度); // } // if (i编码规则长度 == iLength) // { // break; // } // if (i编码规则长度 > iLength) // { // return "编码长度不符合编码原则"; // } // } // if (iLength > i编码规则长度) // { // return "编码长度超出编码原则"; // } // int iCou = 0; // switch (TableID.ToLower()) // { // case "inventoryclass": // sSQL = "select count(1) from dbo.InventoryClass where cInvClassCode = '" + thisID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 1) // { // return thisID + "已经存在\n"; // } // if (thisID.Length > sList[0].Length) // { // sSQL = "select count(1) from dbo.InventoryClass where cInvClassCode = '" + oldID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 0) // { // return thisID + "没有上级编码\n"; // } // } // break; // case "dealerclass": // sSQL = "select count(1) from dbo.DealerClass where cDCode = '" + thisID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 1) // { // return thisID + "已经存在\n"; // } // if (thisID.Length > sList[0].Length) // { // sSQL = "select count(1) from dbo.DealerClass where cDCode = '" + oldID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 0) // { // return thisID + "没有上级编码\n"; // } // } // break; // case "Vendorclass": // sSQL = "select count(1) from dbo.VendorClass where cCCode = '" + thisID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 1) // { // return thisID + "已经存在\n"; // } // if (thisID.Length > sList[0].Length) // { // sSQL = "select count(1) from dbo.VendorClass where cCCode = '" + oldID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 0) // { // return thisID + "没有上级编码\n"; // } // } // break; // case "department": // sSQL = "select count(1) from dbo.Department where cDepCode = '" + thisID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 1) // { // return thisID + "已经存在\n"; // } // if (thisID.Length > sList[0].Length) // { // sSQL = "select count(1) from dbo.Department where cDepCode = '" + oldID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 0) // { // return thisID + "没有上级编码\n"; // } // } // break; // case "districtclass": // sSQL = "select count(1) from dbo.DistrictClass where cDCCode = '" + thisID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 1) // { // return thisID + "已经存在\n"; // } // if (thisID.Length > sList[0].Length) // { // sSQL = "select count(1) from dbo.DistrictClass where cDCCode = '" + oldID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 0) // { // return thisID + "没有上级编码\n"; // } // } // break; // case "Vendorclass": // sSQL = "select count(1) from dbo.VendorClass where cVCCode = '" + thisID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 1) // { // return thisID + "已经存在\n"; // } // if (thisID.Length > sList[0].Length) // { // sSQL = "select count(1) from dbo.VendorClass where cVCCode = '" + oldID + "'"; // iCou = Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL)); // if (iCou == 0) // { // return thisID + "没有上级编码\n"; // } // } // break; // } // return ""; //} /// <summary> /// 判断编码原则 /// </summary> /// <param name="TableID"></param> /// <param name="Code"></param> /// <param name="oldID"></param> /// <param name="thisID"></param> /// <returns></returns> //public static string CheckSerialNumber(string TableID, string oldID, string thisID) //{ // if (oldID == thisID.Substring(0, oldID.Length)) // { // string sSQL = "select * from _SerialNumber where TableID='" + TableID + "'"; // DataTable dt = clsSQLCommond.ExecQuery(sSQL); // int t=0; // string[] type = dt.Rows[0]["Type"].ToString().Split('-'); // for (int i = 0; i < type.Length; i++) // { // if (t < oldID.Length) // { // t = t + type[i].Length; // } // else // { // if (t + type[i].Length != thisID.Length) // { // return "不符合编码原则,编码原则" + dt.Rows[0]["Type"].ToString(); // } // else // { // return ""; // } // } // } // } // else // { // return "不符合编码原则,子类别编码左侧必须等于类别编码"; // } // return ""; //} //public static void GetTreeFoucse(DevExpress.XtraTreeList.TreeList treeList1,string foucseID) //{ // for (int i = 0; i < treeList1.Nodes.Count; i++) // { // if (treeList1.Nodes[i].Tag.ToString().Trim() == foucseID) // { // treeList1.Nodes[i].Selected = true; // } // GetTreeFoucse(treeList1.Nodes[i], foucseID); // } //} //public static void GetTreeFoucse(DevExpress.XtraTreeList.Nodes.TreeListNode tn, string foucseID) //{ // for (int i = 0; i < tn.Nodes.Count; i++) // { // if (tn.Nodes[i].Tag.ToString().Trim() == foucseID) // { // tn.Nodes[i].Selected = true; // } // GetTreeFoucse(tn, foucseID); // } //} /// <summary> /// 得到连续的序列号 /// </summary> /// <param name="TableName"></param> /// <param name="FieldName"></param> /// <param name="sID"></param> /// <returns></returns> public static string GetNewSerialNumberContinuous(string TableName, string FieldName) { ClsDataBase clsSQLCommond = new ClsDataBase(); string sSQL = "select * from _SerialNumberContinuous where TableID='" + TableName + "' and Code='" + FieldName + "'"; DataTable dts = clsSQLCommond.ExecQuery(sSQL); string left = ""; string middle = ""; string right = ""; if (dts.Rows[0]["LeftType"].ToString() != "") { left = dts.Rows[0]["LeftType"].ToString(); } if (dts.Rows[0]["MiddleType"].ToString() == "DateTime") { middle = DateTime.Now.ToString("yyMM"); } sSQL = "select isnull(max(" + FieldName + "),0) from " + TableName + " where left(" + FieldName + ",len('" + left + middle + "'))='" + left + middle + "'"; DataTable dt = clsSQLCommond.ExecQuery(sSQL); if (dt.Rows[0][0].ToString() != "0") { right = dt.Rows[0][0].ToString(); right = right.Substring(left.Length + middle.Length, dts.Rows[0]["RightType"].ToString().Length); int iright = int.Parse(right) + 1; right = GetIsEnoughNumber(iright, dts.Rows[0]["RightType"].ToString().Length); } else { right = GetIsEnoughNumber(1, dts.Rows[0]["RightType"].ToString().Length); } return(left + middle + right); }
public string dtReportSecurity(DateTime dDate1, DateTime dDate2, DateTime dDate3, DateTime dDate4, string cPCCode, string cDCCode, string PersonCode, string cSCode, string cECode) { string s = ""; try { sSQL = @"select a.cPCCode,a.cDCCode,a.cECode,b.cSCode,b.PersonCaptainDept,sum(b.iCount) as iCount,sum(b.iScore) as iScore from Project a left join ProjectSecurity b on a.iID=b.iID where b.iID is not null"; if (dDate1 != DateTime.MinValue) { sSQL = sSQL + " and convert(varchar(10),a.dDate,120)>='" + dDate1.ToString("yyyy-MM-dd") + "'"; } if (dDate2 != DateTime.MinValue) { sSQL = sSQL + " and convert(varchar(10),a.dDate,120)<='" + dDate2.ToString("yyyy-MM-dd") + "'"; } if (dDate3 != DateTime.MinValue) { sSQL = sSQL + " and convert(varchar(10),b.dDate,120)>='" + dDate3.ToString("yyyy-MM-dd") + "'"; } if (dDate4 != DateTime.MinValue) { sSQL = sSQL + " and convert(varchar(10),b.dDate,120)<='" + dDate4.ToString("yyyy-MM-dd") + "'"; } if (cPCCode != "") { sSQL = sSQL + " and isnull(a.cPCCode,'')='" + cPCCode + "'"; } if (cDCCode != "") { sSQL = sSQL + " and isnull(a.cDCCode,'')='" + cDCCode + "'"; } if (cECode != "") { sSQL = sSQL + " and isnull(a.cECode,'')='" + cECode + "'"; } if (PersonCode != "") { sSQL = sSQL + " and isnull(b.PersonCode,'')='" + PersonCode + "'"; } if (cSCode != "") { sSQL = sSQL + " and isnull(b.cSCode,'')='" + cSCode + "'"; } sSQL = sSQL + " group by a.cPCCode,a.cDCCode,a.cECode,b.cSCode,b.PersonCaptainDept"; DataTable dt = clsSQLCommond.ExecQuery(sSQL);; s = Cls序列化.SerializeDataTableXml(dt); } catch (Exception ee) { s = ee.Message; } return(s); }
public DataTable GetLookUpType() { sSQL = @"SELECT convert(int,iID) as iID,iType as iText into #a FROM _LookUpType insert into #a(iID,iText) values('','') select * from #a order by iID"; return(clsSQLCommond.ExecQuery(sSQL)); }
public string ShowlookUpDate(string code, string iType) { string s = ""; string sSQL = ""; try { if (iType == "2") { sSQL = @"select cast(0 as bit) as 选择,iID as iID,iID as 工程类型编码,iText as 工程类型名称 from _LookUpDate where 1=1 and bClose=1 and iType='" + iType + "' order by iID"; } if (code != "") { sSQL = sSQL.Replace("1=1", "1=1 and iID like '%" + code + "%' or iText='" + code + "'"); } DataTable dt = clsSQLCommond.ExecQuery(sSQL); s = Cls序列化.SerializeDataTableXml(dt); } catch (Exception ee) { s = ee.Message; } return(s); }
public void GetcInv() { sSQL = "select cInvCCode as iID, cInvCName as iName from viewInvClass where 1=1 "; if (ASPxTextBox1.Text != "") { sSQL = sSQL + " and (cInvCCode like '%" + ASPxTextBox1.Text + "%' or cInvCName like '%" + ASPxTextBox1.Text + "%')"; } sSQL = sSQL + " and ((len(cInvCCode)=4 and cInvCCode like '02%') or cInvCCode='04' ) group by cInvCCode,cInvCName"; ASPxGridViewSel.DataSource = clsSQLCommond.ExecQuery(sSQL); ASPxGridViewSel.DataBind(); }
protected void Bind() { GetTitle(); sSQL = "Select Name FROM Master..SysDatabases order by Name "; dt = clsSQLCommond.ExecQuery(sSQL); DropDownList1.DataValueField = "Name"; DropDownList1.DataTextField = "Name"; DropDownList1.DataSource = dt; DropDownList1.DataBind(); DropDownList1.Items.Insert(0, new ListItem("", "")); }
public DataTable Get(string iType) { sSQL = "SELECT * FROM " + tablename + " where 1=1 "; if (iType != "") { sSQL = sSQL + " and iType='" + iType + "'"; } DataTable dt = clsSQLCommond.ExecQuery(sSQL); return(dt); }
/// <summary> /// 则显示数形结构 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void ToLoadTree() { //連接數據庫,把表中數據放入DataSet中 ParentDt = new DataTable(); Dictionary <string, TreeNode> dic = new Dictionary <string, TreeNode>(); string Id; //第一級子節點ID string Name; // string ParentId; //第一級字節點以后的ID string Flag = ""; //TreeNode nodeOne=new TreeNode() ;//添加根結點 //nodeOne.Text ="工程管理系統"; //string Pid = "0"; //nodeOne.Value = "0"; //nodeOne.NavigateUrl = "Directory_List.aspx?comID=" + Pid +"&FLAG=" + Flag; //nodeOne.Target = "middle"; //dic.Add("0", nodeOne); //this.treeDir.Nodes.Add(nodeOne); string sql = "select fchrFrmNameID,fchrFrmText,fchrFrmText2,fchrNameSpace,fchrFrmUpName,fIntOrderID,0 as SELECT_FLAG from _Form"; ParentDt = clsSQLCommond.ExecQuery(sql); sql = "select fchrFrmNameID,btnCode,vchrBtnText as btnName,vchrBtnText2 as btnName,iOrder from _FormBtnInfo left join _BtnBaseInfo on _BtnBaseInfo.btnCode=_FormBtnInfo.vchrBtnID"; btnDt = clsSQLCommond.ExecQuery(sql); sql = "select * from _RoleRight where vchrRoleID='" + YxBtn.HidID + "'"; rigDt = clsSQLCommond.ExecQuery(sql); DataRow[] dw = ParentDt.Select("fchrFrmUpName='O'", "fIntOrderID"); for (int i = 0; i < dw.Length; i++) { TreeNode pnd = new TreeNode(); pnd.SelectAction = TreeNodeSelectAction.None; //pnd.NavigateUrl = "CreateFormMenu_List.aspx?dirid=" + dw[i]["fchrFrmNameID"].ToString() + "&FLAG=0"; pnd.Target = "middle"; pnd.Text = dw[i]["fchrFrmText"].ToString(); pnd.Value = dw[i]["fchrFrmNameID"].ToString(); treeDir.Nodes.Add(pnd); DataRow[] dws = rigDt.Select("vchrRoleRight='0|" + pnd.Value + "'"); if (dws.Length > 0) { pnd.Checked = true; } GetTree(pnd, pnd.Text); } treeDir.Attributes.Add("onclick", "HandleCheckEvent()"); if (hidtrue == false) { //treeDir.CheckedNodes = false; } }
protected void UpdateBind() { sSQL = "select * from " + tablename + " where " + tableid + "='" + YxBtn.HidID + "'"; dt = clsSQLCommond.ExecQuery(sSQL); if (dt.Rows.Count > 0) { #region 绑定表 ASPxTextBox1.Text = dt.Rows[0]["vchrRoleID"].ToString(); ASPxTextBox2.Text = dt.Rows[0]["vchrRoleText"].ToString(); #endregion } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Session["url"] = GetUrl(2); Session["backurl"] = GetUrl(2); //string sql = "select RightName from EmployeeRight where Path='" + GetUrl(2) + "'"; //lblTitle.Text = clsSQLCommond.String(sql); //if (Request.UrlReferrer != null) //{ // string requestPage = (Request.UrlReferrer.Segments)[Request.UrlReferrer.Segments.Length - 1]; // //if (requestPage != "Left.aspx") // //{ // geturl(); // //} //} //else //{ // geturl(); //} PublicClass pc = new PublicClass(); //string ltext = pc.GetLanguageLookUp(); //sSQL = "select * from _LookUpDate where iType=4"; //DataTable dtlookup = clsSQLCommond.ExecQuery(sSQL); //DropDownList1.DataSource = dtlookup; //DropDownList1.DataValueField = "iID"; //DropDownList1.DataTextField = ltext; //DropDownList1.DataBind(); //sSQL = "select vlanguage from _UserInfo where vchrUid='" + HttpContext.Current.Session["uID"].ToString() + "'"; //string vlanguage = clsSQLCommond.String(sSQL); //if (vlanguage == "" || vlanguage == "1") //{ // DropDownList1.Items[0].Selected = true; //} //else //{ // DropDownList1.Items[1].Selected = true; //} string ftext = pc.GetLanguageForm(); sSQL = "select * from _Form where fchrFrmNameID='" + hidformID.Value.Trim() + "'"; DataTable dttitle = clsSQLCommond.ExecQuery(sSQL); if (dttitle.Rows.Count > 0) { Title = dttitle.Rows[0][ftext].ToString(); } GetRoleShow(); } }
public DataTable GetAllDt() { DataTable dt = null; try { string sSQL = "select vchrRoleID,vchrRoleText,vchrRemark,' ' as bChoosed from dbo._RoleInfo where isnull(bClosed,0) <>1 order by vchrRoleID"; dt = clsSQLCommond.ExecQuery(sSQL); } catch { throw new Exception("获得角色信息失败!"); } return(dt); }
protected void UpdateBind() { sSQL = "select * from " + tablename + " where " + tableid + "='" + YxBtn.HidID + "'"; dt = clsSQLCommond.ExecQuery(sSQL); if (dt.Rows.Count > 0) { #region 绑定表 TextBox1.Text = dt.Rows[0]["iID"].ToString(); TextBox1.Enabled = false; TextBox2.Text = dt.Rows[0]["iType"].ToString(); TextBox3.Text = dt.Rows[0]["iType2"].ToString(); TextBox7.Text = dt.Rows[0]["Remark"].ToString(); #endregion } }
public DataTable GetAllDt() { DataTable dt = null; try { string sSQL = "SELECT vchrUid, vchrRemark, dtmCreate, dtmClose, ' ' AS bChoosed FROM _UserInfo where dtmCreate <= getdate() and dtmClose >= getdate() order by vchrUid"; dt = clsSQLCommond.ExecQuery(sSQL); } catch { throw new Exception("获得角色信息失败!"); } return(dt); }
public string dt() { string s = ""; try { sSQL = "select *, 'edit' as iSave from " + tablename; DataTable dt = clsSQLCommond.ExecQuery(sSQL);; s = Cls序列化.SerializeDataTableXml(dt); } catch (Exception ee) { s = ee.Message; } return(s); }
// private void GetMenu2() // { // ClsDataBase clsSQLCommond = new ClsDataBase(); // try // { // uid = Session["uID"].ToString().Trim(); // PublicClass pc = new PublicClass(); // string btext = pc.GetLanguageForm(); // string sSQL = @"SELECT fchrFrmNameID, fchrFrmText as fchrFrmText, fPath,fchrNameSpace,fchrFrmUpName,fIntOrderID,Flag into #a FROM dbo._Form //WHERE 1=-1 ORDER BY fIntOrderID //insert into #a(fchrFrmNameID,fchrFrmText,fPath,fchrNameSpace,fchrFrmUpName,fIntOrderID) values ('登陆','登陆','Login/Login.aspx','s','s','100000') //insert into #a(fchrFrmNameID,fchrFrmText,fPath,fchrNameSpace,fchrFrmUpName,fIntOrderID) values ('厂务报修','厂务报修','CW/Update.aspx','s','s','100001') //select * from #a"; // dt = clsSQLCommond.ExecQuery(sSQL); // DataTable dttop = Tables.SelectTable(dt, new string[,] { { "fchrFrmUpName", "s" } }, "fIntOrderID"); // rpItemList.DataSource = dttop; // rpItemList.DataBind(); // } // catch (Exception ee) // { // throw new Exception(ee.Message); // } // } //private void GetMenu() //{ // ClsDataBase clsSQLCommond = new ClsDataBase(); // try // { // uid = Session["uID"].ToString().Trim(); // PublicClass pc = new PublicClass(); // string btext = pc.GetLanguageForm(); // string sSQL2 = @"select count(*) from dbo._UserRoleInfo where vchrRoleID = 'administrator' and vchrUserID = '" + uid + "'"; // if (uid == "admin" || uid == "system") // { // sSQL = "SELECT fchrFrmNameID, " + btext + " as fchrFrmText, fPath, savePath, backPath, newPath, selPath, fchrNameSpace, fchrFrmUpName, fbitHide, fbitNoUse, fIntOrderID, fImage, vchrFormBel, bSystem, bUse, delPath,Flag FROM dbo._Form WHERE (1 = 1) AND (fbitNoUse = 0) AND (fbitHide = 0) and (vchrFormBel='' or vchrFormBel is null) ORDER BY fIntOrderID"; // } // else if (clsSQLCommond.Int(sSQL2) != 0) // { // sSQL = "SELECT fchrFrmNameID, " + btext + " as fchrFrmText, fPath, savePath, backPath, newPath, selPath, fchrNameSpace, fchrFrmUpName, fbitHide, fbitNoUse, fIntOrderID, fImage, vchrFormBel, bSystem, bUse, delPath,Flag FROM dbo._Form WHERE (isnull(bUse,0) = 1) AND (fbitNoUse = 0) AND (fbitHide = 0) and (vchrFormBel='' or vchrFormBel is null) ORDER BY fIntOrderID"; // } // else // { // sSQL = "SELECT DISTINCT " + // " dbo._Form.fchrFrmNameID, dbo._Form. " + btext + " as fchrFrmText, dbo._Form.fchrNameSpace, " + // " dbo._Form.fchrFrmUpName, " + // " dbo._Form.fbitHide, dbo._Form.fbitNoUse, dbo._Form.fIntOrderID,dbo._Form.fPath,Flag " + // "FROM dbo._RoleInfo INNER JOIN " + // " dbo._RoleRight ON dbo._RoleInfo.vchrRoleID = dbo._RoleRight.vchrRoleID INNER JOIN " + // " dbo._UserRoleInfo ON dbo._RoleInfo.vchrRoleID = dbo._UserRoleInfo.vchrRoleID and dbo._UserRoleInfo.vchrUserID='" + uid + "' INNER JOIN " + // " dbo._Form ON 1=1 " + // " AND dbo._Form.fchrFrmNameID = RTRIM(LTRIM(RIGHT(dbo._RoleRight.vchrRoleRight, LEN(dbo._RoleRight.vchrRoleRight) - CHARINDEX('|', dbo._RoleRight.vchrRoleRight)))) " + // "WHERE (isnull(bUse,0) = 1) AND (fbitNoUse = 0) AND (fbitHide = 0) and (vchrFormBel='' or vchrFormBel is null) " + // "ORDER BY fIntOrderID "; // } // dt = clsSQLCommond.ExecQuery(sSQL); // DataTable dttop = Tables.SelectTable(dt, new string[,] { { "fchrFrmUpName", "s" } }, "fIntOrderID"); // rpItemList.DataSource = dttop; // rpItemList.DataBind(); // } // catch (Exception ee) // { // throw new Exception(ee.Message); // } //} public DataTable List() { ClsDataBase clsSQLCommond = new ClsDataBase(); uid = Session["uID"].ToString().Trim(); PublicClass pc = new PublicClass(); string btext = pc.GetLanguageForm(); string sSQL2 = @"select count(*) from dbo._UserRoleInfo where vchrRoleID = 'administrator' and vchrUserID = '" + uid + "'"; if (uid == "admin" || uid == "system") { sSQL = "SELECT fchrFrmNameID, " + btext + " as fchrFrmText, fPath, savePath, backPath, newPath, selPath, fchrNameSpace, fchrFrmUpName, fbitHide, fbitNoUse, fIntOrderID, fImage, vchrFormBel, bSystem, bUse, delPath,Flag FROM dbo._Form WHERE (1 = 1) AND (fbitNoUse = 0) AND (fbitHide = 0) and (vchrFormBel='' or vchrFormBel is null) ORDER BY fIntOrderID"; } else { sSQL = "SELECT DISTINCT " + " dbo._Form.fchrFrmNameID, dbo._Form. " + btext + " as fchrFrmText, dbo._Form.fchrNameSpace, " + " dbo._Form.fchrFrmUpName, "+ " dbo._Form.fbitHide, dbo._Form.fbitNoUse, dbo._Form.fIntOrderID,dbo._Form.fPath,Flag " + "FROM dbo._RoleInfo INNER JOIN " + " dbo._RoleRight ON dbo._RoleInfo.vchrRoleID = dbo._RoleRight.vchrRoleID INNER JOIN " + " dbo._UserRoleInfo ON dbo._RoleInfo.vchrRoleID = dbo._UserRoleInfo.vchrRoleID and dbo._UserRoleInfo.vchrUserID='" + uid + "' INNER JOIN " + " dbo._Form ON 1=1 " + " AND dbo._Form.fchrFrmNameID = RTRIM(LTRIM(RIGHT(dbo._RoleRight.vchrRoleRight, LEN(dbo._RoleRight.vchrRoleRight) - CHARINDEX('|', dbo._RoleRight.vchrRoleRight)))) "+ "WHERE (isnull(bUse,0) = 1) AND (fbitNoUse = 0) AND (fbitHide = 0) and (vchrFormBel='' or vchrFormBel is null) " + "ORDER BY fIntOrderID "; } return(clsSQLCommond.ExecQuery(sSQL)); }
public string dt(string iType) { string s = ""; try { sSQL = "select *,'edit' as iSave from dbo." + tablename + " where iType = '" + iType + "' order by " + Code; DataTable dt = clsSQLCommond.ExecQuery(sSQL);; s = Cls序列化.SerializeDataTableXml(dt); } catch (Exception ee) { s = ee.Message; } return(s); }
/// <summary> /// 则显示数形结构 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void ToLoadTree() { //連接數據庫,把表中數據放入DataSet中 ParentDt = new DataTable(); Dictionary <string, TreeNode> dic = new Dictionary <string, TreeNode>(); string Id; //第一級子節點ID string Name; // string ParentId; //第一級字節點以后的ID string Flag = ""; //TreeNode nodeOne=new TreeNode() ;//添加根結點 //nodeOne.Text ="工程管理系統"; //string Pid = "0"; //nodeOne.Value = "0"; //nodeOne.NavigateUrl = "Directory_List.aspx?comID=" + Pid +"&FLAG=" + Flag; //nodeOne.Target = "middle"; //dic.Add("0", nodeOne); //this.treeDir.Nodes.Add(nodeOne); string sql = "select fchrFrmNameID,fchrFrmText,fchrFrmText2,fchrNameSpace,fchrFrmUpName,fIntOrderID,0 as SELECT_FLAG from _Form"; ParentDt = clsSQLCommond.ExecQuery(sql); DataRow[] dw = ParentDt.Select("fchrFrmUpName='O'", "fIntOrderID"); for (int i = 0; i < dw.Length; i++) { TreeNode pnd = new TreeNode(); //pnd.NavigateUrl = "CreateFormMenu_List.aspx?dirid=" + dw[i]["fchrFrmNameID"].ToString() + "&FLAG=0"; pnd.Text = dw[i][ftext].ToString() + "/" + dw[i]["fchrFrmNameID"].ToString(); pnd.Value = dw[i]["fchrFrmNameID"].ToString(); treeDir.Nodes.Add(pnd); GetTree(pnd, dw[i]["fchrNameSpace"].ToString()); } }
public DataTable GetVisitors() { string uid = Session["uID"].ToString(); sSQL = "SELECT * FROM _UserRoleInfo "; DataTable dtrole = clsSQLCommond.ExecQuery(sSQL); return(dtrole); }
public string dt() { string s = ""; try { sSQL = @"select a.vchrUid, a.vchrName,isnull(S1,'1') as S1, isnull(S2,'1') as S2, isnull(S3,'1') as S3, isnull(S4,'1') as S4, isnull(S5,'1') as S5, isnull(S6,'1') as S6, isnull(S7,'1') as S7, isnull(S8,'1') as S8, isnull(S9,'1') as S9, isnull(B1,0) as B1, isnull(B2,0) as B2, case when b.vchrUid is null then 'add' else 'edit' end as iSave from _UserInfo a left join " + tablename + " b on a.vchrUid=b.vchrUid"; DataTable dt = clsSQLCommond.ExecQuery(sSQL);; s = Cls序列化.SerializeDataTableXml(dt); } catch (Exception ee) { s = ee.Message; } return(s); }
protected void UpdateBind() { if (YxBtn.HidID != "") { sSQL = "select * from _Form where fchrFrmNameID = '" + YxBtn.HidID + "' ORDER BY fIntOrderID"; DataTable dtFormNew = clsSQLCommond.ExecQuery(sSQL); if (dtFormNew.Rows.Count > 0) { TextBox1.Text = dtFormNew.Rows[0]["fchrFrmNameID"].ToString().Trim(); TextBox2.Text = dtFormNew.Rows[0]["fIntOrderID"].ToString().Trim(); TextBox3.Text = dtFormNew.Rows[0]["fchrFrmText"].ToString().Trim(); TextBox4.Text = dtFormNew.Rows[0]["fchrFrmText2"].ToString().Trim(); } //if (dtFormNew.Rows[0]["fchrFrmNameID"].ToString().Trim().ToLower().StartsWith("frm")) //{ sSQL = "select 0 as bChoose,*,'' as FormText,'' as FormText2,'' as FormOrder,'' as FormEnable from _BtnBaseInfo order by iOrder "; DataTable dts = clsSQLCommond.ExecQuery(sSQL); sSQL = "select *,vchrBtnText as FormText,intOrder as FormOrder from dbo._FormBtnInfo where fchrFrmNameID = '" + YxBtn.HidID + "'"; DataTable dt = clsSQLCommond.ExecQuery(sSQL); for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dts.Rows.Count; j++) { if (dt.Rows[i]["vchrBtnID"].ToString().Trim().ToLower() == dts.Rows[j]["btnCode"].ToString().Trim().ToLower()) { dts.Rows[j]["bChoose"] = true; dts.Rows[j]["FormText"] = dt.Rows[i]["vchrBtnText"].ToString().Trim(); dts.Rows[j]["FormText2"] = dt.Rows[i]["vchrBtnText2"].ToString().Trim(); dts.Rows[j]["FormOrder"] = dt.Rows[i]["FormOrder"].ToString().Trim(); dts.Rows[j]["FormEnable"] = dt.Rows[i]["intOrder"].ToString().Trim(); break; } } } SmartGridView1.DataSource = dts; SmartGridView1.DataBind(); //} } }
private void GetTitle() { PublicClass pc = new PublicClass(); string ctext = pc.GetLanguageColumn(); string ltext = pc.GetLanguageLookUpData(); string atext = pc.GetLanguageAlert(); sSQL = "select * from _TableColInfo where TABLE_NAME in ('_LookUpDate')"; DataTable dt = clsSQLCommond.ExecQuery(sSQL); for (int i = 0; i < dt.Rows.Count; i++) { switch (dt.Rows[i]["COLUMN_NAME"].ToString()) { case "iID": Label1.Text = "*" + dt.Rows[i][ctext].ToString(); break; case "iText": Label2.Text = "*" + dt.Rows[i][ctext].ToString(); break; case "iText2": Label3.Text = dt.Rows[i][ctext].ToString(); break; case "bClose": Label4.Text = dt.Rows[i][ctext].ToString(); break; case "bSystem": Label5.Text = dt.Rows[i][ctext].ToString(); break; case "Remark": Label7.Text = dt.Rows[i][ctext].ToString(); break; } } }
protected void UpdateBind() { YxBtn.boolDel = false; sSQL = "select * from " + tablename + " where " + tableid + "='" + YxBtn.HidID + "'"; dt = clsSQLCommond.ExecQuery(sSQL); if (dt.Rows.Count > 0) { #region 绑定表 TextBox1.Text = dt.Rows[0]["vchrUid"].ToString(); TextBox1.Enabled = false; TextBox2.Text = dt.Rows[0]["vchrName"].ToString(); #endregion } }
/// <summary> /// 读取AD用户信息 /// </summary> /// <param name= "ADUsername "> 用户 </param> /// <param name= "ADPassword "> 密码 </param> /// <param name= "domain "> 域名 </param> /// <returns> </returns> public DataTable AdUserInfo(string ADUsername, string ADPassword, string domain)//System.Collections.SortedList { ClsDataBase clsSQLCommond = new ClsDataBase(); DataTable dt = new DataTable(); dt.Columns.Add("Name"); System.DirectoryServices.DirectorySearcher src; string ADPath = "LDAP:// " + domain; System.Collections.SortedList sl = new System.Collections.SortedList(); System.DirectoryServices.DirectoryEntry de = new System.DirectoryServices.DirectoryEntry(ADPath, ADUsername, ADPassword); src = new System.DirectoryServices.DirectorySearcher(de); src.PageSize = 10000;// 此参数可以任意设置,但不能不设置,如不设置读取AD数据为0~999条数据,设置后可以读取大于1000条数据。 src.SearchScope = System.DirectoryServices.SearchScope.Subtree; src.Filter = "(&(&(objectCategory=person)))"; DataTable dtadlist = clsSQLCommond.ExecQuery("select * from _LookUpDate where iType=6"); foreach (System.DirectoryServices.SearchResult res in src.FindAll()) { DirectoryEntry users = res.GetDirectoryEntry(); string str = ""; string str3 = ""; string str4 = ""; str = res.GetDirectoryEntry().Properties["distinguishedName"].Value.ToString().Trim(); string[] str2 = System.Text.RegularExpressions.Regex.Split(str, ",OU=", System.Text.RegularExpressions.RegexOptions.IgnoreCase); if (str2.Length > 1) { str3 = str2[1]; } if (str3 != "") { str4 = str3.Split(',')[0].Trim(); } if (res.GetDirectoryEntry().Properties["CN"].Value.ToString().Trim() == "yx") { string s = "s"; } DataRow[] dwadlist = dtadlist.Select("iText='" + str4 + "'"); if (dwadlist.Length > 0) { DataRow dw = dt.NewRow(); dw["Name"] = res.GetDirectoryEntry().Properties["Name"].Value.ToString().Trim(); dt.Rows.Add(dw); } } return(dt); }
public DataTable GetAllDt() { DataTable dt = null; try { string sSQL = "SELECT vchrRoleID, vchrRoleText, vchrRemark, bClosed, dtmCreate FROM _RoleInfo ORDER BY vchrRoleID "; dt = clsSQLCommond.ExecQuery(sSQL); } catch { throw new Exception("获得用户信息失败!"); } return(dt); }
public DataTable GetAllDt() { DataTable dt = null; try { string sSQL = "SELECT vchrUid, vchrPwd,vchrName, vchrRemark, tstamp, dtmCreate, dtmClose FROM _UserInfo order by vchrUid "; dt = clsSQLCommond.ExecQuery(sSQL); } catch { throw new Exception("获得用户信息失败!"); } return(dt); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { //LogName.Attributes.Add("onkeyup", "javascript:SelectLanguange('" + LogName.ClientID + "','" + LogPwd.ClientID + "');"); //LogName.Attributes.Add("onmouseout", "javascript:SelectLanguange('" + LogName.ClientID + "','" + LogPwd.ClientID + "');"); //LogPwd.Attributes.Add("onkeyup", "javascript:SelectLanguange('" + LogName.ClientID + "','" + LogPwd.ClientID + "');"); //DropDownList1.Attributes.Add("onchange", "javascript:SelectLanguangeChange();"); PublicClass pc = new PublicClass(); string title = pc.GetCompany(); Page.Title = title; //compname.Value = Session["CompanyName"].ToString(); Session["uID"] = "游客"; Session["uName"] = "游客"; Session["uDeptID"] = ""; Session["uDeptName"] = ""; string ltext = pc.GetLanguageLookUpData(); sSQL = "select * from _LookUpDate where iType=4"; DataTable dtlookup = clsSQLCommond.ExecQuery(sSQL); //ASPxDateEdit1.Value = DateTime.Now; //DropDownList1.DataSource = dtlookup; //DropDownList1.DataValueField = "iID"; //DropDownList1.DataTextField = ltext; //DropDownList1.DataBind(); //LogName.Focus(); //sSQL = "select vlanguage from _UserInfo where vchrUid='" + HttpContext.Current.Session["uID"].ToString() + "'"; //string vlanguage = clsSQLCommond.String(sSQL); //if (vlanguage == "" || vlanguage == "1") //{ // DropDownList1.Items[0].Selected = true; //} //else //{ // DropDownList1.Items[1].Selected = true; //} //Response.Redirect("../Default.aspx"); //PublicClass.GetCookie(); //if (Request.Cookies["OnlineuID"] != null && Request.Cookies["OnlineuID"].Value != "") //{ // Response.Redirect("../Frame/Index.aspx"); //} } }
public string dtList(string iCode1, string iCode2, string dDate1, string dDate2, string cInvCode1, string cInvCode2, string cRsCode) { string s = ""; try { sSQL = "select *,convert(varchar(10),dDate,120) as 单据日期, 'edit' as iSave from " + tablename + " a left join " + tablenames + " b on a." + tableid + "=b." + tableid + " where 1=1 and cRsCode='" + cRsCode + "'"; if (iCode1 != "") { sSQL = sSQL + " and a.cRdCode>='" + iCode1 + "'"; } if (iCode2 != "") { sSQL = sSQL + " and a.cRdCode<='" + iCode2 + "'"; } if (dDate1 != "") { sSQL = sSQL + " and convert(varchar(10),dDate,120)>='" + DateTime.Parse(dDate1).ToString("yyyy-MM-dd") + "'"; } if (dDate2 != "") { sSQL = sSQL + " and convert(varchar(10),dDate,120)<='" + DateTime.Parse(dDate2).ToString("yyyy-MM-dd") + "'"; } if (cInvCode1 != "") { sSQL = sSQL + " and cInvCode>='" + cInvCode1 + "'"; } if (cInvCode2 != "") { sSQL = sSQL + " and cInvCode<='" + cInvCode2 + "'"; } DataTable dt = clsSQLCommond.ExecQuery(sSQL); dt.TableName = tablename; s = Cls序列化.SerializeDataTableXml(dt); } catch (Exception ee) { s = ee.Message; } return(s); }
public DataTable Get() { // sSQL = @"insert into _UserInfo(vchrUid,vchrName,dtmCreate,dtmClose,vchrPwd) select cPersonCode,cPersonName,getdate(),null,'B8BF1F5E2A325363' from //( //select a.cUser_Id as cPersonCode,a.cUser_Name as cPersonName from @u8.UA_User a, @u8.UserHRPersonContro b,@u8.hr_hi_person c where a.cUser_Id = b.cUser_Id and b.cPsn_Num = c.cPsn_Num and nState=0 //)a " + //@"where a.cPersonCode not in (select vchrUid from _UserInfo) // //SELECT * FROM " + tablename + " a left join @u8.UA_User b on a.vchrUid=b.cUser_Id where 1=1 and nState=0 "; sSQL = "SELECT * FROM " + tablename + " where 1=1 "; DataTable dt = clsSQLCommond.ExecQuery(sSQL); return(dt); }
public DataTable GetTreeInfo(string sPK) { DataTable dt = null; try { string sSQL = string.Concat(new string[] { "select distinct fIntOrderID,sInfo,fchrFrmNameID,fchrFrmUpName,fchrFrmText,dt1.vchrRoleRight,dt1.vchrRoleRight,bChoosed = case isnull(dt1.vchrRoleRight,'') when '' then '' else '√' end from ( (SELECT fchrFrmUpName+'|'+fchrFrmNameID+'/'+fchrFrmText as sInfo,fchrFrmNameID,fchrFrmUpName,fIntOrderID,fchrFrmText FROM dbo._Form WHERE (1 = 1) AND (fbitNoUse = 0) AND (fbitHide = 0) and (vchrFormBel='", ClsBaseDataInfo.sProForm, "' or vchrFormBel is null) union all select _FormBtnInfo.fchrFrmNameID+'|'+vchrBtnID,vchrBtnID,_FormBtnInfo.fchrFrmNameID,intOrder,vchrBtnText from dbo._FormBtnInfo left join _Form on _FormBtnInfo.fchrFrmNameID=_Form.fchrFrmNameID where 1=1 )) dtTree left join dbo._RoleRight dt1 on dt1.vchrRoleRight = sInfo and dt1.vchrRoleID='", sPK, "' order by fIntOrderID" }); dt = clsSQLCommond.ExecQuery(sSQL); } catch { throw new Exception("获得角色信息失败!"); } return(dt); }