C# SQLHandler.ExecuteAsDataReader方法代码示例

        public static List<Roles> GetAllRoles(int portalID,bool isAll,string userName)
        {
            List<Roles> lstRoles = new List<Roles>();
            string StoredProcedureName = "sp_PortalRoleList";
            SQLHandler sagesql = new SQLHandler();
            List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", portalID));
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsAll", isAll));
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@UserName", userName));

            SqlDataReader SQLReader = null;
            try
            {
                SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
                while (SQLReader.Read())
                {
                    Roles obj = new Roles();
                    obj.RoleID = new Guid(SQLReader["RoleID"].ToString());
                    obj.RoleName = SQLReader["RoleName"].ToString();
                    lstRoles.Add(obj);
                }
            }
            catch(Exception)
            {

            }
            finally
            {
                if (SQLReader != null)
                {
                    SQLReader.Close();
                }
            }
            return lstRoles;
        }

 /// <summary>
 /// Connect to the database and check condition for suspended IP.
 /// </summary>
 /// <param name="IpAddress">IpAddress</param>
  /// <returns>Returns True for SuspendedIP Address</returns>
  public bool IsSuspendedIP(string IpAddress)
  {
      string sp = "[dbo].[usp_IsSuspendedIP]";
      SQLHandler sagesql = new SQLHandler();
      List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
      ParamCollInput.Add(new KeyValuePair<string, object>("@IpAddress", IpAddress));
      SqlDataReader reader = null;
      bool isSuspended = true;
      try
      {
          reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
          while (reader.Read())
          {
              isSuspended = bool.Parse(reader["Suspended"].ToString());
          }
          reader.Close();
          return isSuspended;
      }
      catch (Exception ex)
      {
          throw (ex);
      }
      finally
      {
          if (reader != null)
          {
              reader.Close();
          }
      }
  }

        public  string GetUserAgent(int PortalID, bool IsActive)
        {

            string sp = "[dbo].[usp_UserAgentGetType]";
            SQLHandler sagesql = new SQLHandler();
            string content = "";
            SqlDataReader reader = null;
            try
            {
                List<KeyValuePair<string, object>> paramColl = new List<KeyValuePair<string, object>>();
                paramColl.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
                paramColl.Add(new KeyValuePair<string, object>("@IsActive", IsActive));
                reader = sagesql.ExecuteAsDataReader(sp, paramColl);
                while (reader.Read())
                {
                    content = reader["AgentMode"] as string;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
            return content;
        }

        public static List<TemplateInfo> GetTemplateList(int PortalID, string UserName)
        {
            string sp = "[dbo].[sp_TemplateGetList]";
            SQLHandler sagesql = new SQLHandler();

            List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
            ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
            ParamCollInput.Add(new KeyValuePair<string, object>("@Username", UserName));

            List<TemplateInfo> lstTemplate = new List<TemplateInfo>();
            try
            {
                SqlDataReader reader;
                reader = sagesql.ExecuteAsDataReader(sp, ParamCollInput);
                while (reader.Read())
                {
                    TemplateInfo obj = new TemplateInfo();
                    obj.TemplateID = int.Parse(reader["TemplateID"].ToString());
                    obj.TemplateTitle = reader["TemplateTitle"].ToString();
                    obj.PortalID = int.Parse(reader["PortalID"].ToString());
                    obj.Author = reader["Author"].ToString();
                    obj.AuthorURL = reader["AuthorURL"].ToString();
                    obj.Description = reader["Description"].ToString();
                    lstTemplate.Add(obj);
                }
                reader.Close();
                return lstTemplate;

            }
            catch (Exception ex)
            {

                throw (ex);
            }
        }

        /// <summary>
        /// Connects to database and returns RobotsInfo list for given PortalID.
        /// </summary>
        /// <param name="PortalID">PortalID</param>
        /// <returns>RobotsInfo List</returns>
        public static List<RobotsInfo> GetRobots(int PortalID)
        {
            List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
            SqlDataReader reader = null;
            try
            {
                SQLHandler Objsql = new SQLHandler();

                reader = Objsql.ExecuteAsDataReader("[dbo].[usp_SEOGetRobots]", ParaMeterCollection);
                List<RobotsInfo> lstRobots = new List<RobotsInfo>();
                while (reader.Read())
                {
                    lstRobots.Add(new RobotsInfo(reader["PageName"].ToString(), reader["TabPath"].ToString(), reader["SEOName"].ToString(), reader["Description"].ToString()));
                }
                return lstRobots;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

        }

        /// <summary>
        /// Connects to database and returns roles details by role name.
        /// </summary>
        /// <param name="RoleName">Role name.</param>
        /// <returns>Role details.</returns>
        public RolesManagementInfo GetRoleIDByRoleName(string RoleName)
        {
            SqlDataReader reader = null;
            try
            {

                SQLHandler SQLH = new SQLHandler();
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@RoleName", RoleName));
                reader = SQLH.ExecuteAsDataReader("[dbo].[sp_GetRoleIDByRoleName]", ParamCollInput);
                RolesManagementInfo objList = new RolesManagementInfo();
                while (reader.Read())
                {
                    objList.ApplicationId = new Guid(reader["ApplicationId"].ToString());
                    objList.RoleId = new Guid(reader["RoleId"].ToString());
                    objList.RoleName = reader["RoleName"].ToString();
                    objList.LoweredRoleName = reader["LoweredRoleName"].ToString();
                    objList.Description = reader["Description"].ToString();
                }
                reader.Close();
                return objList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
        }

        public static List<MenuInfo> GetFooterMenu(int PortalID, string UserName,string CultureCode)
        {
            List<MenuInfo> lstPages = new List<MenuInfo>();
            string StoredProcedureName = "[dbo].[usp_SageMenuGetFooter]";
            List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@Username", UserName));
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@CultureCode", CultureCode));
            SqlDataReader SQLReader;
            try
            {
                SQLHandler sagesql = new SQLHandler();
                SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName,ParaMeterCollection);
            }
            catch (Exception e)
            {
                throw e;
            }

            while (SQLReader.Read())
            {
                lstPages.Add(new MenuInfo(int.Parse(SQLReader["PageID"].ToString()), int.Parse(SQLReader["PageOrder"].ToString()), SQLReader["PageName"].ToString(), int.Parse(SQLReader["ParentID"].ToString()), int.Parse(SQLReader["Level"].ToString()), SQLReader["LevelPageName"].ToString(), SQLReader["SEOName"].ToString(), SQLReader["TabPath"].ToString(), bool.Parse(SQLReader["IsVisible"].ToString()), bool.Parse(SQLReader["ShowInMenu"].ToString())));                               
               
               
            }
            return lstPages;

        }

       public static List<SiteMapInfo> GetSiteMap(string prefix, bool IsActive, bool IsDeleted, int PortalID, string Username, bool IsVisible, bool IsRequiredPage)
       {

           List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
           ParaMeterCollection.Add(new KeyValuePair<string, object>("@prefix", prefix));
           ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsActive", IsActive));
           ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsDeleted", IsDeleted));
           ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
           ParaMeterCollection.Add(new KeyValuePair<string, object>("@UserName", Username));
           ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsVisible", IsVisible));
           ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsRequiredPage", IsRequiredPage));
           SqlDataReader reader = null;
           try
           {
               SQLHandler Objsql = new SQLHandler();

               reader = Objsql.ExecuteAsDataReader("[dbo].[sp_PageGetByCustomPrefix]", ParaMeterCollection);
               List<SiteMapInfo> lstSetting = new List<SiteMapInfo>();


               while (reader.Read())
               {


                   SiteMapInfo obj = new SiteMapInfo();
                   obj.PageID = reader["PageID"].ToString();
                   obj.PageName = reader["PageName"].ToString();
                   obj.TabPath = reader["TabPath"].ToString();
                   obj.SEOName = reader["SEOName"].ToString();
                   obj.LevelPageName = reader["LevelPageName"].ToString();
                   obj.Description = reader["Description"].ToString();
                   if (reader["UpdatedOn"].ToString() == string.Empty)
                   {
                       obj.UpdatedOn = DateTime.Parse(reader["AddedOn"].ToString());
                   }
                   else
                   {
                       obj.UpdatedOn = DateTime.Parse(reader["UpdatedOn"].ToString());
                   }
                   obj.AddedOn = DateTime.Parse(reader["AddedOn"].ToString());

                   lstSetting.Add(obj);
               }
               return lstSetting;
           }
           catch (Exception)
           {

               throw;
           }
           finally
           {
               if (reader != null)
               {
                   reader.Close();
               }
           }

       }

 public static List<MenuInfo> GetMenuFront(int PortalID, string UserName, string CultureCode)
 {
     List<MenuInfo> lstPages = new List<MenuInfo>();
     string StoredProcedureName = "[dbo].[usp_SageMenuGetClientView]";
     List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
     ParaMeterCollection.Add(new KeyValuePair<string, object>("@prefix", "---"));
     ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsDeleted", 0));
     ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
     ParaMeterCollection.Add(new KeyValuePair<string, object>("@UserName", UserName));
     ParaMeterCollection.Add(new KeyValuePair<string, object>("@CultureCode", CultureCode));
     SqlDataReader SQLReader = null;
     try
     {
         SQLHandler sagesql = new SQLHandler();
         SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
         while (SQLReader.Read())
         {
             MenuInfo objMenu = new MenuInfo();
             objMenu.PageID = int.Parse(SQLReader["PageID"].ToString());
             objMenu.PageOrder = int.Parse(SQLReader["PageOrder"].ToString());
             objMenu.ParentID = int.Parse(SQLReader["ParentID"].ToString());
             objMenu.Level = int.Parse(SQLReader["Level"].ToString());
             objMenu.LevelPageName = SQLReader["LevelPageName"].ToString();
             objMenu.SEOName = SQLReader["SEOName"].ToString();
             objMenu.TabPath = SQLReader["TabPath"].ToString();
             objMenu.IsVisible = bool.Parse(SQLReader["IsVisible"].ToString());
             objMenu.ShowInMenu = bool.Parse(SQLReader["ShowInMenu"].ToString());
             objMenu.IconFile = SQLReader["IconFile"].ToString();
             lstPages.Add(objMenu);
         }
     }
     catch (Exception e)
     {
         throw e;
     }
     finally
     {
         if (SQLReader != null)
         {
             SQLReader.Close();
         }
     }
     return lstPages;
 }

        /// <summary>
        /// Connects to database and returns portal details by portal ID.
        /// </summary>
        /// <param name="PortalID">Portal ID.</param>
        /// <param name="UserName"> User's name.</param>
        /// <returns>Returns portal </returns>
        public static PortalInfo GetPortalByPortalID(int PortalID, string UserName)
        {
            string sp = "[dbo].[sp_PortalGetByPortalID]";
            SqlDataReader reader = null;
            try
            {

                SQLHandler SQLH = new SQLHandler();
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@UserName", UserName));


                reader = SQLH.ExecuteAsDataReader(sp, ParamCollInput);
                PortalInfo objList = new PortalInfo();

                while (reader.Read())
                {

                    objList.PortalID = int.Parse(reader["PortalID"].ToString());
                    objList.Name = reader["Name"].ToString();
                    objList.SEOName = reader["SEOName"].ToString();
                    objList.IsParent = bool.Parse(reader["IsParent"].ToString());
                    objList.ParentPortalName = reader["ParentPortalName"].ToString();
                }
                return objList;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

        }

        public static string LoadProviderSetting(int providerId, AspxCommonInfo aspxCommonObj)
        {
            List<KeyValuePair<string, object>> parameter = new List<KeyValuePair<string, object>>();
            parameter.Add(new KeyValuePair<string, object>("@ShippingProviderID", providerId));
            parameter.Add(new KeyValuePair<string, object>("@StoreID", aspxCommonObj.StoreID));
            parameter.Add(new KeyValuePair<string, object>("@PortalID", aspxCommonObj.PortalID));
            SQLHandler sqlH = new SQLHandler();
            var reader = sqlH.ExecuteAsDataReader("[dbo].[usp_Aspx_GetShippingProviderSetting]", parameter);


            var dataQuery = from d in reader.Cast<System.Data.Common.DbDataRecord>()
                            select new
                            {
                                SettingKey = (String)d["SettingKey"],
                                SettingValue = (String)d["SettingValue"]

                            };
            var data = dataQuery.ToDictionary(z => z.SettingKey, z => z.SettingValue);
            string retString = JSONHelper.Serialize<Dictionary<string, string>>(data);
            return retString;
        }

        public static List<MenuInfo> GetAdminMenu()
        {
            List<MenuInfo> lstPages = new List<MenuInfo>();
            string StoredProcedureName = "[dbo].[usp_sagemenugetadminmenu]";
            SqlDataReader SQLReader;
            try
            {
                SQLHandler sagesql = new SQLHandler();
                SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName);
            }
            catch (Exception e)
            {
                throw e;
            }

            while (SQLReader.Read())
            {
                lstPages.Add(new MenuInfo(int.Parse(SQLReader["PageID"].ToString()), int.Parse(SQLReader["PageOrder"].ToString()), SQLReader["PageName"].ToString(), int.Parse(SQLReader["ParentID"].ToString()), int.Parse(SQLReader["Level"].ToString()), SQLReader["LevelPageName"].ToString(), SQLReader["SEOName"].ToString(), SQLReader["TabPath"].ToString(), bool.Parse(SQLReader["IsVisible"].ToString()), bool.Parse(SQLReader["ShowInMenu"].ToString())));                               
               
                
            }
            return lstPages;

        }

        public static string GetNewsContent()
        {
            string sp = "[dbo].[usp_NewsRssContentGet]";
            SQLHandler sagesql = new SQLHandler();
            string content = "";
            SqlDataReader reader = null;
            try
            {
                reader = sagesql.ExecuteAsDataReader(sp);

                while (reader.Read())
                {
                    content = reader["NewsContent"] as string;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
            return content;
        }

        public static List<Folder> GetFolders()
        {
            List<Folder> lstFolders = new List<Folder>();
            string StoredProcedureName = "usp_FileManagerGetFolders";
            SqlDataReader SQLReader;
            SQLHandler sagesql = new SQLHandler();
            try
            {
               
                SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName);
                while (SQLReader.Read())
                {
                    Folder obj = new Folder();
                    obj.FolderId = int.Parse(SQLReader["FolderId"].ToString());
                    obj.FolderPath = SQLReader["FolderPath"].ToString();
                    obj.StorageLocation = int.Parse(SQLReader["StorageLocation"].ToString());
                    lstFolders.Add(obj);
                }
                SQLReader.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
             

            return lstFolders;
        }

        public MessageManagementInfo GetUserActivationCode(string Username, int PortalID)
        {
            SqlDataReader reader = null;
            try
            {
                string sp = "[dbo].[sp_GetUserActivationCode]";
                SQLHandler SQLH = new SQLHandler();

                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@UserName", Username));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));

                reader = SQLH.ExecuteAsDataReader(sp, ParamCollInput);
                MessageManagementInfo objInfo = new MessageManagementInfo();

                while (reader.Read())
                {
                    objInfo.UserId = new Guid(reader["UserId"].ToString());
                }
                return objInfo;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
        }