C# DBManager.getDataTable方法代码示例

 public static DataTable GetUserModuls(string userGroups, out string sErr)
 {
     sErr = "";
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     DataTable dt = new DataTable();
     try
     {
         string sql = string.Concat("EXEC spGetUserModuls ", DBManager.quoteForDB(userGroups));
         dt = db.getDataTable(sql, out sErr);
         db.CloseOleDB();
     }
     catch (MembershipCreateUserException membershipCreateUserException)
     {
         sErr = membershipCreateUserException.Message;
     }
     return dt;
 }

 public static DataTable GetUserGroups(string userName)
 {
     string sErr = "";
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     DataTable dt = new DataTable();
     try
     {
         string sql = string.Concat("exec spSysGetUserGroups @UserName='", userName, "'");
         dt = db.getDataTable(sql, out sErr);
         db.CloseOleDB();
         if (sErr != "")
         {
             HttpContext.Current.Response.Write(sErr);
         }
     }
     catch (MembershipCreateUserException membershipCreateUserException)
     {
         HttpContext.Current.Response.Write(membershipCreateUserException.Message);
     }
     return dt;
 }

 public static DataTable GetUserMenus(string lang, string menuId, string userName, out string sErr)
 {
     sErr = "";
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     DataTable dt = new DataTable();
     try
     {
         string[] strArrays = new string[] { "EXEC spGetUserMenus ", DBManager.quoteForDB(lang), ",", menuId, ",", DBManager.quoteForDB(userName) };
         dt = db.getDataTable(string.Concat(strArrays), out sErr);
         db.CloseOleDB();
     }
     catch (MembershipCreateUserException membershipCreateUserException)
     {
         sErr = membershipCreateUserException.Message;
     }
     return dt;
 }

 public static Table CreateTableEntitySearchFields(DataTable oDT)
 {
     Table oTbl = new Table()
     {
         Width = Unit.Percentage(100),
         ID = "SearchFiledsContainer",
         CellPadding = 1,
         CellSpacing = 0
     };
     TableRow oRow = null;
     TableCell oCell = null;
     for (int i = 0; i < oDT.Rows.Count; i++)
     {
         oRow = new TableRow();
         oCell = new TableCell()
         {
             CssClass = "ContentStyle",
             Width = Unit.Percentage(30),
             Text = oDT.Rows[i]["FieldCaption"].ToString()
         };
         oRow.Cells.Add(oCell);
         oCell = new TableCell()
         {
             Width = Unit.Percentage(70)
         };
         if (oDT.Rows[i]["ControlType"].ToString() == "combo")
         {
             DropDownList lst = new DropDownList()
             {
                 ID = oDT.Rows[i]["FieldName"].ToString(),
                 Width = Unit.Pixel(155)
             };
             DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
             string sErr = string.Empty;
             DataTable tblList = db.getDataTable(string.Concat("exec ", oDT.Rows[i]["ComboSource"].ToString()), out sErr);
             db.CloseOleDB();
             for (int j = 0; j < tblList.Rows.Count; j++)
             {
                 lst.Items.Add(new ListItem(tblList.Rows[j][1].ToString(), tblList.Rows[j][0].ToString()));
             }
             lst.Attributes.Add("onchange", oDT.Rows[i]["ValidateFunc"].ToString());
             oCell.Controls.Add(lst);
             oRow.Cells.Add(oCell);
         }
         else if (oDT.Rows[i]["ControlType"].ToString() == "text")
         {
             TextBox oText = new TextBox()
             {
                 Width = Unit.Pixel(155),
                 ID = oDT.Rows[i]["FieldName"].ToString()
             };
             JObject jo = new JObject();
             JObject joContextKey = new JObject();
             joContextKey["FrameName"] = oDT.Rows[i]["FrameName"].ToString();
             joContextKey["FieldName"] = oDT.Rows[i]["FieldName"].ToString();
             string strValidateFunc = oDT.Rows[i]["ValidateFunc"].ToString();
             General.setContextKey(ref joContextKey, strValidateFunc);
             jo.Add("contextKey", joContextKey);
             jo.Add("methodUrl", VirtualPathUtility.ToAbsolute("~/Tools/EntitySearch.asmx/GetFieldComplition"));
             jo.Add("minLength", 1);
             oText.Attributes.Add("AC_Options", JsonConvert.SerializeObject(jo, Formatting.None));
             oCell.Controls.Add(oText);
             oRow.Cells.Add(oCell);
         }
         oTbl.Rows.Add(oRow);
     }
     return oTbl;
 }

 public static int GetUserEntityPermission(string userName, string layerName)
 {
     string sErr = "";
     int retVal = 0;
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     try
     {
         string[] strArrays = new string[] { "exec spGetUserEntityPermission @UserName='", userName, "', @LayerName='", layerName, "'" };
         string sql = string.Concat(strArrays);
         DataTable dt = db.getDataTable(sql, out sErr);
         db.CloseOleDB();
         if (sErr != "")
         {
             HttpContext.Current.Response.Write(sErr);
         }
         if (dt.Rows.Count > 0)
         {
             retVal = int.Parse(dt.Rows[0]["UserRole"].ToString());
         }
     }
     catch (Exception exception)
     {
         HttpContext.Current.Response.Write(exception.Message);
     }
     return retVal;
 }

 public static List<UserManager.UserData> GetUsersListDataExtended(string name_part, int group_id)
 {
     List<UserManager.UserData> users = new List<UserManager.UserData>();
     string sErr = "";
     string UserName = "";
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     try
     {
         string sql = string.Concat("exec spSysGetUsers @SearchString='", name_part, "',@GroupId=", group_id.ToString());
         DataTable dt = db.getDataTable(sql, out sErr);
         for (int i = 0; i < dt.Rows.Count; i++)
         {
             UserManager.UserData ud = new UserManager.UserData();
             MembershipUser oUser = Membership.GetUser(dt.Rows[i]["UserName"].ToString());
             UserName = dt.Rows[i]["UserName"].ToString();
             ud.userName = dt.Rows[i]["UserName"].ToString();
             ud.IsOnline = oUser.IsOnline;
             ud.firstName = dt.Rows[i]["FirstName"].ToString();
             ud.lastName = dt.Rows[i]["LastName"].ToString();
             object day = oUser.LastLoginDate.Day;
             object month = oUser.LastLoginDate.Month;
             DateTime lastLoginDate = oUser.LastLoginDate;
             ud.LastLoginDate = string.Format("{0:00}/{1:00}/{2:0000}", day, month, lastLoginDate.Year);
             ud.Email = oUser.Email;
             ud.Remarks = dt.Rows[i]["Comment"].ToString();
             users.Add(ud);
         }
         db.CloseOleDB();
     }
     catch (Exception exception)
     {
         Exception ex = exception;
         HttpContext.Current.Response.Write(string.Concat(ex.Message, " - Wrong data forUser:", UserName));
     }
     return users;
 }

 public static bool IsGroupPermissionExists(string name, string permission)
 {
     string message;
     string sErr = "";
     bool retVal = false;
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     try
     {
         string sql = string.Concat("exec spSysIsGroupPermissionExists @GroupName='", name, "', @Permission=", permission);
         DataTable dt = db.getDataTable(sql, out sErr);
         db.CloseOleDB();
         if (sErr != "")
         {
             message = string.Format("<script>alert('{0}\\n\\n{1}')</script>", "הוספת קבוצה נכשלה", sErr);
             HttpContext.Current.Response.Write(message);
         }
         retVal = (dt.Rows.Count <= 0 ? false : true);
     }
     catch (Exception exception)
     {
         message = string.Format("<script>alert('{0}\\n\\n{1}')</script>", "הוספת קבוצה נכשלה", exception.Message);
         HttpContext.Current.Response.Write(message);
     }
     return retVal;
 }

 public static bool IsAppSessionExpired(Page oPage)
 {
     string sErr = "";
     oPage.Session["user_name"] = (oPage.User.Identity.IsAuthenticated ? oPage.User.Identity.Name : "Anonymous");
     oPage.Session["user_name"] = oPage.Session["user_name"].ToString().Substring(oPage.Session["user_name"].ToString().IndexOf("\\") + 1);
     oPage.Session["user_groups"] = General.GetUserGroups(oPage.Session["user_name"].ToString());
     oPage.Session["AppSession"] = oPage.Session.SessionID;
     oPage.Session["Direction"] = "rtl";
     if (oPage.Request["lang"] == null)
     {
         oPage.Session["Language"] = "heb";
     }
     else
     {
         oPage.Session["Language"] = oPage.Request["lang"];
     }
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     string sql = string.Concat("exec spSysGetUserByName @UserName='", oPage.Session["user_name"].ToString(), "'");
     DataTable dt = db.getDataTable(sql, out sErr);
     db.CloseOleDB();
     if ((sErr != "" ? false : dt.Rows.Count > 0))
     {
         oPage.Session["EMAIL"] = dt.Rows[0]["EMAIL"].ToString();
         oPage.Session["user_full_name"] = string.Concat(dt.Rows[0]["FirstName"].ToString(), " ", dt.Rows[0]["LastName"].ToString());
     }
     return false;
 }

 private void GetZoomDetails(out string LayerName, out string MapSqlWhr)
 {
     int i;
     string[] str;
     LayerName = "";
     MapSqlWhr = "";
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     string sErr = string.Empty;
     string sqlWhr = "";
     string sql = "";
     string mapKey = "";
     string mapKeyType = "";
     DataTable dt1 = db.getDataTable(string.Concat("exec spGetDynamicSearchMapKeyField ", base.Request["searchid"]), out sErr);
     if (!(sErr != ""))
     {
         if (dt1.Rows.Count > 0)
         {
             mapKey = dt1.Rows[0]["field_name"].ToString();
             mapKeyType = dt1.Rows[0]["field_type_id"].ToString();
         }
         DataTable dt = db.getDataTable(string.Concat("exec spGetDynamicSearch_SearchFields ", base.Request["searchid"]), out sErr);
         if (!(sErr != ""))
         {
             for (i = 0; i < dt.Rows.Count; i++)
             {
                 if ((base.Request[dt.Rows[i]["field_name"].ToString()] == null ? false : base.Request[dt.Rows[i]["field_name"].ToString()] != ""))
                 {
                     sqlWhr = string.Concat(sqlWhr, dt.Rows[i]["field_name"].ToString(), "=");
                     sqlWhr = (!(dt.Rows[i]["field_type_id"].ToString() == "2") ? string.Concat(sqlWhr, base.Request[dt.Rows[i]["field_name"].ToString()]) : string.Concat(sqlWhr, "'", base.Request[dt.Rows[i]["field_name"].ToString()].Replace("'", "''"), "'"));
                     sqlWhr = string.Concat(sqlWhr, " and ");
                 }
             }
             if (!(sqlWhr == ""))
             {
                 sqlWhr = sqlWhr.TrimEnd(" and ".ToCharArray());
                 DataTable oDT = null;
                 if (dt.Rows.Count > 0)
                 {
                     if (!(dt.Rows[0]["server_type"].ToString() == "2"))
                     {
                         str = new string[] { "select ", mapKey, " from ", dt.Rows[0]["table_name"].ToString(), " where ", sqlWhr };
                         sql = string.Concat(str);
                     }
                     else
                     {
                         str = new string[] { "select ", mapKey, " from ", dt.Rows[0]["table_name"].ToString(), " where ", sqlWhr };
                         sql = string.Concat(str);
                         str = new string[] { "select * from openquery(", dt.Rows[0]["linked_server_name"].ToString(), ",'", sql.Replace("'", "''"), "')" };
                         sql = string.Concat(str);
                     }
                     oDT = db.getDataTable(sql, out sErr);
                     if (!(sErr != ""))
                     {
                         MapSqlWhr = string.Concat(dt.Rows[0]["target_layer_key_field"].ToString(), " in (");
                         LayerName = dt.Rows[0]["layer_name"].ToString();
                         for (i = 0; i < oDT.Rows.Count; i++)
                         {
                             if (!(mapKeyType == "2"))
                             {
                                 MapSqlWhr = string.Concat(MapSqlWhr, oDT.Rows[i][0].ToString(), ",");
                             }
                             else
                             {
                                 MapSqlWhr = string.Concat(MapSqlWhr, "'", oDT.Rows[i][0].ToString(), "',");
                             }
                         }
                         if (oDT.Rows.Count != 0)
                         {
                             MapSqlWhr = string.Concat(MapSqlWhr.TrimEnd(",".ToCharArray()), ")");
                         }
                         else if (!(mapKeyType == "2"))
                         {
                             MapSqlWhr = string.Concat(MapSqlWhr, "0");
                         }
                         else
                         {
                             MapSqlWhr = string.Concat(MapSqlWhr, "'0'");
                         }
                     }
                     else
                     {
                         base.Response.Write(sErr);
                     }
                 }
             }
         }
         else
         {
             base.Response.Write(sErr);
         }
     }
     else
     {
         base.Response.Write(sErr);
     }
 }

 public static string GetUserGroups(string UserLogonName)
 {
     string UserGroupIDs = "";
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     string sErr = string.Empty;
     DataTable tblList = db.getDataTable(string.Concat("exec spSysGetUserPermitedGroups '", UserLogonName, "'"), out sErr);
     db.CloseOleDB();
     for (int i = 0; i < tblList.Rows.Count; i++)
     {
         UserGroupIDs = (!(UserGroupIDs == "") ? string.Concat(UserGroupIDs, ",", tblList.Rows[i]["GroupId"].ToString()) : tblList.Rows[i]["GroupId"].ToString());
     }
     return UserGroupIDs;
 }

 public static DataTable InitApplicationTabs(string lang, string SubSystemIds, out string sErr)
 {
     DataTable oDT = null;
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     string strSQL = string.Format("exec spGetTabs '{0}', '{1}'", lang, SubSystemIds);
     oDT = db.getDataTable(strSQL, out sErr);
     db.CloseOleDB();
     return oDT;
 }

 public static List<int> GetPermitedSearches(string sGroups)
 {
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     string sErr = string.Empty;
     DataTable tblList = db.getDataTable(string.Concat("exec spGetPermitedSearches '", sGroups, "'"), out sErr);
     db.CloseOleDB();
     List<int> lstIDs = new List<int>();
     for (int i = 0; i < tblList.Rows.Count; i++)
     {
         lstIDs.Add(int.Parse(tblList.Rows[i][0].ToString()));
     }
     return lstIDs;
 }

 public static string GetParamVal(DBManager db, string ParamName)
 {
     string str;
     string sErr = "";
     string strSQL = string.Concat("exec spGetParamByName", DBManager.quoteForDB(ParamName));
     DataTable oDT = db.getDataTable(strSQL, out sErr);
     if (!(sErr != ""))
     {
         str = (oDT.Rows.Count <= 0 ? "" : oDT.Rows[0]["sys_param_value"].ToString());
     }
     else
     {
         str = sErr;
     }
     return str;
 }

 public static DataTable GetApplicationHeaders(string lang, string projectId, out string sErr)
 {
     DataTable oDT = null;
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     string strSQL = string.Format("exec spGetApplicationHeaders '{0}', '{1}'", lang, projectId);
     oDT = db.getDataTable(strSQL, out sErr);
     db.CloseOleDB();
     return oDT;
 }

 public static string GetUserPosition(string userName)
 {
     string sErr = "";
     string retVal = "";
     DBManager db = new DBManager(ConfigurationManager.AppSettings["SYS_DSN"]);
     DataTable dt = new DataTable();
     try
     {
         string sql = string.Concat("exec spSysGetUserPosition @UserName='", userName, "'");
         dt = db.getDataTable(sql, out sErr);
         db.CloseOleDB();
         if (sErr != "")
         {
             HttpContext.Current.Response.Write(sErr);
         }
         if (dt.Rows.Count > 0)
         {
             retVal = dt.Rows[0]["Position"].ToString();
         }
     }
     catch (MembershipCreateUserException membershipCreateUserException)
     {
         HttpContext.Current.Response.Write(membershipCreateUserException.Message);
     }
     return retVal;
 }