// // GET: /DB/ /// <summary> /// 表列表 /// </summary> /// <returns></returns> public ActionResult Index() { DataTable dt = DBManage.SqlDbHelper.ExecuteDataTable("select * from db_talbe_list order by tableName"); string Json = DataTableConvertJson.DataTable2Json(dt); ViewBag.PageData = Json; return(View()); }
/// <summary> /// 获取查询人员 /// </summary> private string GetSearchedEmps() { String curUserNo = BP.Web.WebUser.No; String deptId = getUTF8ToString("DeptId"); bool searchChildDept = Convert.ToBoolean(getUTF8ToString("SearchChild")); String name = getUTF8ToString("KeyWord"); string sql = ""; if (searchChildDept) { deptId = GetDeptAndChild(deptId); } else { deptId = "'" + deptId + "'"; } if (BP.WF.Glo.OSModel == BP.Sys.OSModel.OneMore) { string filter_dept = deptId == "0" ? String.Empty : String.Format(" and Port_Emp.No in (Select FK_Emp from Port_DeptEmp where FK_Dept in ({0}))", deptId); string filter_name = String.IsNullOrEmpty(name) ? String.Empty : String.Format(" and Port_Emp.Name+','+Port_Emp.NO like '%{0}%'", name); if (BP.Sys.SystemConfig.AppCenterDBType == BP.DA.DBType.MySQL) { filter_name = String.IsNullOrEmpty(name) ? String.Empty : String.Format(" and CONCAT(Port_Emp.Name,',',Port_Emp.NO) like '%{0}%'", name); } sql = String.Format("select Port_Emp.*,Port_Dept.Name as DeptName from Port_Emp,Port_Dept where Port_Emp.FK_Dept = Port_Dept.No {0}{1}", filter_dept, filter_name); } else { string filter_dept = deptId == "0" ? String.Empty : String.Format(" and Port_Emp.No in (Select FK_Emp from Port_EmpDept where FK_Dept in ({0}))", deptId); string filter_name = String.IsNullOrEmpty(name) ? String.Empty : String.Format(" and Port_Emp.Name+','+Port_Emp.NO like '%{0}%'", name); if (BP.Sys.SystemConfig.AppCenterDBType == BP.DA.DBType.MySQL) { filter_name = String.IsNullOrEmpty(name) ? String.Empty : String.Format(" and CONCAT(Port_Emp.Name,',',Port_Emp.NO) like '%{0}%'", name); } sql = String.Format("select Port_Emp.*,Port_Dept.Name as DeptName from Port_Emp,Port_Dept where Port_Emp.FK_Dept = Port_Dept.No {0}{1}", filter_dept, filter_name); } DataTable dt = DBAccess.RunSQLReturnTable(sql); return(DataTableConvertJson.DataTable2Json(ColumnName2UpperCase(dt))); }
/// <summary> /// 以下算法只包含 oracle mysql sqlserver 三种类型的数据库 qin /// </summary> /// <param name="dataSource">表名</param> /// <param name="pageNumber">当前页</param> /// <param name="pageSize">当前页数据条数</param> /// <param name="key">计算总行数需要</param> /// <param name="orderKey">排序字段</param> /// <returns></returns> public string DBPaging(string dataSource, int pageNumber, int pageSize, string key, string orderKey) { string sql = ""; string orderByStr = ""; if (!string.IsNullOrEmpty(orderKey)) { orderByStr = " ORDER BY " + orderKey; } switch (DBAccess.AppCenterDBType) { case DBType.Oracle: int beginIndex = (pageNumber - 1) * pageSize + 1; int endIndex = pageNumber * pageSize; sql = "SELECT * FROM ( SELECT A.*, ROWNUM RN " + "FROM (SELECT * FROM " + dataSource + orderByStr + ") A WHERE ROWNUM <= " + endIndex + " ) WHERE RN >=" + beginIndex; break; case DBType.MSSQL: sql = "SELECT TOP " + pageSize + " * FROM " + dataSource + " WHERE " + key + " NOT IN (" + "SELECT TOP (" + pageSize + "*(" + pageNumber + "-1)) " + key + " FROM " + dataSource + " )" + orderByStr; break; case DBType.MySQL: pageNumber -= 1; sql = "select * from " + dataSource + orderByStr + " limit " + pageNumber + "," + pageSize; break; default: throw new Exception("暂不支持您的数据库类型."); } DataTable DTable = DBAccess.RunSQLReturnTable(sql); int totalCount = DBAccess.RunSQLReturnCOUNT("select " + key + " from " + dataSource); return(DataTableConvertJson.DataTable2Json(DTable, totalCount)); }
/// <summary> /// /// </summary> /// <returns></returns> public string loadData() { string FK_Flow = getUTF8ToString("FK_Flow"); string rpt = "ND" + int.Parse(FK_Flow) + "Rpt"; DataTable rptDt = DBAccess.GetTableSchema(rpt); rptDt.Columns.Add(""); MapAttrs attrs = new MapAttrs(rpt); DataTable dt = new DataTable(); dt.Columns.Add("ZD", typeof(string)); dt.Columns.Add("ZDMC", typeof(string)); dt.Columns.Add("LX", typeof(string)); foreach (MapAttr attr in attrs) { dt.Rows.Add(attr.Name, attr.KeyOfEn, attr.MyDataTypeStr); } return(DataTableConvertJson.DataTable2Json(dt, dt.Rows.Count)); }