C# SqlParameterHelper.ExecuteReader方法代碼示例

 public static IDataReader GetActivePolls(Guid siteGuid)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_Polls_SelectActive", 2);
     sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
     sph.DefineSqlParameter("@CurrentTime", SqlDbType.DateTime, ParameterDirection.Input, DateTime.UtcNow);
     return sph.ExecuteReader();
 }

        public static IDataReader GetAllByPage(Guid pageQuestionGuid)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_SurveyQuestions_SelectAllByPage", 1);
            sph.DefineSqlParameter("@PageGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, pageQuestionGuid);

            return sph.ExecuteReader();
        }

 /// <summary>
 /// Gets an IDataReader with one row from the mp_GeoZone table.
 /// </summary>
 /// <param name="guid"> guid </param>
 public static IDataReader GetByCode(Guid countryGuid, string code)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_GeoZone_SelectByCode", 2);
     sph.DefineSqlParameter("@CountryGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, countryGuid);
     sph.DefineSqlParameter("@Code", SqlDbType.NVarChar, 255, ParameterDirection.Input, code);
     return sph.ExecuteReader();
 }

 public static IDataReader Find(string loginProvider, string providerKey)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_UserLogins_Find", 2);
     sph.DefineSqlParameter("@LoginProvider", SqlDbType.NVarChar, 128, ParameterDirection.Input, loginProvider);
     sph.DefineSqlParameter("@ProviderKey", SqlDbType.NVarChar, 128, ParameterDirection.Input, providerKey);
     return sph.ExecuteReader();
 }

 public static IDataReader GetByName(int siteId, string roleName)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_Roles_SelectOneByName", 2);
     sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
     sph.DefineSqlParameter("@RoleName", SqlDbType.NVarChar, 50, ParameterDirection.Input, roleName);
     return sph.ExecuteReader();
 }

 /// <summary>
 /// Gets an IDataReader with one row from the mp_SurveyQuestionAnswers table.
 /// </summary>
 /// <param name="answerGuid"> answerGuid </param>
 public static IDataReader GetOne(Guid responseGuid, Guid questionGuid)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_SurveyQuestionAnswers_SelectOne", 2);
     sph.DefineSqlParameter("@ResponseGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, responseGuid);
     sph.DefineSqlParameter("@QuestionGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, questionGuid);
     return sph.ExecuteReader();
 }

        public static IDataReader Get(int projectId, int blockId, int workId, int groupId, int itemId, int subItemId)
        {
            StringBuilder sb = new StringBuilder();
            string prefix = DBHelper.Instance.TablePrefix;

            sb.Append("SELECT s.IdProyecto ProjectId, ");
            sb.Append("s.IdBloque BlockId, ");
            sb.Append("s.IdObra WorkId, ");
            sb.Append("s.IdGrupo GroupId, ");
            sb.Append("s.IdItem ItemId, ");
            sb.Append("s.Id SubItemId, ");
            sb.Append("s.Nombre Name,");
            sb.Append("s.Cantidad Quantity ");
            sb.AppendFormat("FROM {0}SubItem s ", prefix);
            sb.AppendFormat("WHERE s.IdProyecto={0} ", projectId);
            sb.AppendFormat("AND s.IdBloque={0} ", blockId);
            sb.AppendFormat("AND s.IdObra={0} ", workId);
            sb.AppendFormat("AND s.IdGrupo={0} ", groupId);
            sb.AppendFormat("AND s.IdItem={0} ", itemId);
            sb.AppendFormat("AND s.Id={0} ", subItemId);

            SqlParameterHelper sph = new SqlParameterHelper(DBHelper.Instance.ConnectionString, sb.ToString(), CommandType.Text, 0);

            return sph.ExecuteReader();
        }

 /// <summary>
 /// Gets an IDataReader with all rows in the mp_Currency table.
 /// </summary>
 public static IDataReader GetSpecificSettingAllModulesWithTheDefinition(string srcPath, string settingName)
 {
     SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "mp_ModulePagePath_SelectByControlSrcPathSnippet", 2);
     sph.DefineSqlParameter("@FeaturePath", SqlDbType.UniqueIdentifier, ParameterDirection.Input, srcPath);
     sph.DefineSqlParameter("@SettingName", SqlDbType.NVarChar, ParameterDirection.Input, settingName);
     return sph.ExecuteReader();
 }

        public static IDataReader Get(int projectId, int blockId)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT Nombre Name ");
            sb.AppendFormat("FROM {0}Bloque ", DBHelper.Instance.TablePrefix);
            sb.AppendFormat("WHERE IdProyecto={0} AND Id={1}", projectId, blockId);

            SqlParameterHelper sph = new SqlParameterHelper(DBHelper.Instance.ConnectionString, sb.ToString(), CommandType.Text, 0);

            return sph.ExecuteReader();
        }

        //public static IDataReader Get(int projectId, int blockId, int workId, int groupId)
        //{
        //    string prefix = DBHelper.Instance.TablePrefix;
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append("SELECT g.Id GroupId,");
        //    sb.Append("l.Nombre Name ");
        //    sb.AppendFormat("FROM {0}Grupo g INNER JOIN {0}ListaGrupo l ON g.IdListaGrupo=l.Id ", prefix);
        //    sb.AppendFormat("WHERE g.IdProyecto={0} ", projectId);
        //    sb.AppendFormat("AND g.IdBloque={0} ", blockId);
        //    sb.AppendFormat("AND g.IdObra={0} ", workId);
        //    sb.AppendFormat("AND g.Id={0}", groupId);
        //    SqlParameterHelper sph = new SqlParameterHelper(DBHelper.Instance.ConnectionString, sb.ToString(), CommandType.Text, 0);
        //    return sph.ExecuteReader();
        //}
        public static IDataReader GetAll()
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT * ");
            sb.AppendFormat("FROM uListaGrupo ");
            sb.Append("ORDER BY Nombre");

            SqlParameterHelper sph = new SqlParameterHelper(DBHelper.Instance.ConnectionString, sb.ToString(), CommandType.Text, 0);

            return sph.ExecuteReader();
        }

        public static IDataReader GetAll()
        {
            SqlParameterHelper sph = new SqlParameterHelper(DBHelper.Instance.ConnectionString,
                "SELECT Id AS ProjectId," +
                "Nombre AS Name," +
                "FechaCreacion AS DateCreated," +
                "FechaModificacion AS DateModified," +
                "Usuario AS CreatedBy," +
                "UsuarioModificador AS ModifiedBy " +
                "FROM uProyecto " +
                "ORDER BY Nombre",
                CommandType.Text, 0);

            return sph.ExecuteReader();
        }

        public static IDataReader GetAll(int projectId, int blockId)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT Id AS WorkId,");
            sb.Append("Nombre AS Name ");
            sb.AppendFormat("FROM {0}Obra ", DBHelper.Instance.TablePrefix);
            sb.AppendFormat("WHERE IdProyecto={0} AND ", projectId);
            sb.AppendFormat("IdBloque={0} ", blockId);
            sb.Append("ORDER BY Nombre");

            SqlParameterHelper sph = new SqlParameterHelper(DBHelper.Instance.ConnectionString, sb.ToString(), CommandType.Text, 0);

            return sph.ExecuteReader();
        }

        public static IDataReader GetAll(int projectId, int blockId, int workId)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT g.Id AS GroupId,");
            sb.Append("l.Nombre AS Name ");
            sb.AppendFormat("FROM {0}Grupo g INNER JOIN {0}ListaGrupo l ON g.IdListaGrupo=l.Id ", DBHelper.Instance.TablePrefix);
            sb.AppendFormat("WHERE g.IdProyecto={0} AND ", projectId);
            sb.AppendFormat("g.IdBloque={0} AND ", blockId);
            sb.AppendFormat("g.IdObra={0} ", workId);
            sb.Append("ORDER BY l.Nombre");

            SqlParameterHelper sph = new SqlParameterHelper(DBHelper.Instance.ConnectionString, sb.ToString(), CommandType.Text, 0);

            return sph.ExecuteReader();
        }

        public static IDataReader Get(int projectId)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT Id AS ProjectId,");
            sb.Append("Nombre AS Name,");
            sb.Append("FechaCreacion AS DateCreated,");
            sb.Append("FechaModificacion AS DateModified,");
            sb.Append("Usuario AS CreatedBy,");
            sb.Append("UsuarioModificador AS ModifiedBy ");
            sb.AppendFormat("FROM {0}Proyecto ", DBHelper.Instance.TablePrefix);
            sb.AppendFormat("WHERE Id={0}", projectId);

            SqlParameterHelper sph = new SqlParameterHelper(DBHelper.Instance.ConnectionString, sb.ToString(), CommandType.Text, 0);

            return sph.ExecuteReader();
        }

        public static IDataReader Get(int projectId, int blockId, int workId, int groupId, int itemId)
        {
            string prefix = DBHelper.Instance.TablePrefix;
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT i.Id ItemId,");
            sb.Append("l.Nombre Name ");
            sb.AppendFormat("FROM {0}Item i INNER JOIN {0}ListaItem l ON i.IdListaItem=l.Id ", prefix);
            sb.AppendFormat("WHERE i.IdProyecto={0} ", projectId);
            sb.AppendFormat("AND i.IdBloque={0} ", blockId);
            sb.AppendFormat("AND i.IdObra={0} ", workId);
            sb.AppendFormat("AND i.IdGrupo={0} ", groupId);
            sb.AppendFormat("AND i.Id={0}", itemId);

            SqlParameterHelper sph = new SqlParameterHelper(DBHelper.Instance.ConnectionString, sb.ToString(), CommandType.Text, 0);

            return sph.ExecuteReader();
        }