C# VzfSqlCommand.ExecuteDataTableFromReader方法代码示例

        /// <summary>
        /// Gets stats row on album.
        /// </summary>
        /// <param name="mid">
        /// The module ID.
        /// </param>
        /// <param name="userId">
        /// The user ID.
        /// </param>
        /// <param name="albumId">
        /// The album Id.
        /// </param>
        /// <returns>
        /// Returns a <see cref="T:System.Int32"/> array with stats row.
        /// </returns>
        public static int[] album_getstats(
            int? mid, 
            object userId, 
            object albumId)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                sc.Parameters.Add(
                    sc.CreateParameter(
                        DbType.Int32,
                        "i_UserID",
                        (userId == null || userId.ToString() == "0") ? null : userId));
                sc.Parameters.Add(
                    sc.CreateParameter(
                        DbType.Int32,
                        "i_AlbumID",
                        (albumId == null || albumId.ToString() == "0") ? null : albumId));

                sc.CommandText.AppendObjectQuery("album_getstats", mid);
                var strow =
                    sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, true).Rows[0];
                return new[] { Convert.ToInt32(strow["AlbumNumber"]), Convert.ToInt32(strow["ImageNumber"]) };
            }
        }

    /// <summary>
    /// The get user name by email.
    /// </summary>
    /// <param name="appName">
    /// The app name.
    /// </param>
    /// <param name="email">
    /// The email.
    /// </param>
    /// <returns>
    /// </returns>
    public DataTable GetUserNameByEmail(string connectionStringName, [NotNull] string appName, [NotNull] string email)
    {
      using (var sc = new VzfSqlCommand(connectionStringName))
      {
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Email", email));

          sc.CommandText.AppendObjectQuery("prov_getusernamebyemail", connectionStringName);
          return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);
      }      
    }

        public static DataTable __GetProfiles(string connectionStringName, object appName, object pageIndex, object pageSize, object userNameToMatch, object inactiveSinceDate)
         {
            using (var sc = new VzfSqlCommand(connectionStringName))
             {
                 sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName));
                 sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PageIndex", pageIndex));
                 sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PageSize", pageSize));
                 sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserNameToMatch", userNameToMatch));
                 sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_InactiveSinceDate", inactiveSinceDate));
                 sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));

                 sc.CommandText.AppendObjectQuery("prov_profile_getprofiles", connectionStringName);

                 return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);                
             }
        }

        /// <summary>
        /// The get user password info.
        /// </summary>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="username">
        /// The username.
        /// </param>
        /// <param name="updateUser">
        /// The update user.
        /// </param>
        /// <returns>
        /// The <see cref="DataTable"/>.
        /// </returns>
        public DataTable GetUserPasswordInfo(string connectionStringName, string appName, string username, bool updateUser)
        {
            // connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
            using (var sc = new VzfSqlCommand(connectionStringName))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_APPLICATIONNAME", appName));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERNAME", username));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERKEY", DBNull.Value));
                sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "I_USERISONLINE", updateUser));
                sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "I_UTCTIMESTAMP", DateTime.UtcNow));              

                sc.CommandText.AppendObjectQuery("P_getuser", connectionStringName);
                return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);
            }             
        }

    /// <summary>
    /// The get all users.
    /// </summary>
    /// <param name="appName">
    /// The app name.
    /// </param>
    /// <param name="pageIndex">
    /// The page index.
    /// </param>
    /// <param name="pageSize">
    /// The page size.
    /// </param>
    /// <returns>
    /// </returns>
    public DataTable GetAllUsers(string connectionStringName, [NotNull] string appName, int pageIndex, int pageSize)
    {
      using (var sc = new VzfSqlCommand(connectionStringName))
      {
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
          sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@PageIndex", pageIndex));
          sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@PageSize", pageSize));          

          sc.CommandText.AppendObjectQuery("prov_getallusers", connectionStringName);
          return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);
      }    
    }

        /// <summary>
        /// The create user.
        /// </summary>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="username">
        /// The username.
        /// </param>
        /// <param name="password">
        /// The password.
        /// </param>
        /// <param name="passwordSalt">
        /// The password salt.
        /// </param>
        /// <param name="passwordFormat">
        /// The password format.
        /// </param>
        /// <param name="email">
        /// The email.
        /// </param>
        /// <param name="passwordQuestion">
        /// The password question.
        /// </param>
        /// <param name="passwordAnswer">
        /// The password answer.
        /// </param>
        /// <param name="isApproved">
        /// The is approved.
        /// </param>
        /// <param name="providerUserKey">
        /// The provider user key.
        /// </param>
        public void CreateUser(string connectionStringName, string appName, string username, string password, string passwordSalt, int passwordFormat, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey)
        {
            // connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
            using (var sc = new VzfSqlCommand(connectionStringName))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_APPLICATIONNAME", appName));
           
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERNAME", username));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORD", password));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORDSALT", passwordSalt));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORDFORMAT", passwordFormat.ToString()));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_EMAIL", email));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORDQUESTION", passwordQuestion));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PASSWORDANSWER", passwordAnswer));
                sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "I_ISAPPROVED", isApproved));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERKEY", providerUserKey));
                sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "I_UTCTIMESTAMP", DateTime.UtcNow));

                sc.CommandText.AppendObjectQuery("P_CREATEUSER", connectionStringName);
                providerUserKey = sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false).Rows[0][0];                
            }      
        }

        /// <summary>
        /// The get all users.
        /// </summary>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="pageIndex">
        /// The page index.
        /// </param>
        /// <param name="pageSize">
        /// The page size.
        /// </param>
        /// <returns>
        /// The <see cref="DataTable"/>.
        /// </returns>
        public DataTable GetAllUsers(string connectionStringName, string appName, int pageIndex, int pageSize)
        {
            // connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
            using (var sc = new VzfSqlCommand(connectionStringName))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_APPLICATIONNAME", appName));            
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_pageindex", pageIndex));
                // TODO:fix overflow bug
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_pagesize", pageSize == int.MaxValue ? 1 : pageSize));

                sc.CommandText.AppendObjectQuery("P_getallusers", connectionStringName);
                return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);
            }  
        }

//.........这里部分代码省略.........
        /// The show reputation.
        /// </param>
        /// <param name="sincePostedDate">
        /// The since posted date.
        /// </param>
        /// <param name="toPostedDate">
        /// The to posted date.
        /// </param>
        /// <param name="sinceEditedDate">
        /// The since edited date.
        /// </param>
        /// <param name="toEditedDate">
        /// The to edited date.
        /// </param>
        /// <param name="pageIndex">
        /// The page index.
        /// </param>
        /// <param name="pageSize">
        /// The page size.
        /// </param>
        /// <param name="sortPosted">
        /// The sort posted.
        /// </param>
        /// <param name="sortEdited">
        /// The sort edited.
        /// </param>
        /// <param name="sortPosition">
        /// The sort position.
        /// </param>
        /// <param name="showThanks">
        /// The show thanks.
        /// </param>
        /// <param name="messagePosition">
        /// The message position.
        /// </param>
        /// <param name="messageId">
        /// The message id.
        /// </param>
        /// <param name="lastRead">
        /// The last read.
        /// </param>
        /// <returns>
        /// The <see cref="T:System.Data.DataTable"/>.
        /// </returns> 
        public static DataTable post_list(
            int? mid,
            object topicId,
            object currentUserId,
            object authoruserId,
            object updateViewCount,
            bool showDeleted,
            bool styledNicks,
            bool showReputation,
            DateTime sincePostedDate,
            DateTime toPostedDate,
            DateTime sinceEditedDate,
            DateTime toEditedDate,
            int pageIndex,
            int pageSize,
            int sortPosted,
            int sortEdited,
            int sortPosition,
            bool showThanks,
            int messagePosition,
            int messageId,
            DateTime lastRead)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                if (updateViewCount == null)
                {
                    updateViewCount = 1;
                }

                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_TopicID", topicId));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PageUserID", currentUserId));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_AuthorUserID", authoruserId));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int16, "i_UpdateViewCount", updateViewCount));
                sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_ShowDeleted", showDeleted));
                sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_StyledNicks", styledNicks));
                sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_ShowReputation", showReputation));
                sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_SincePostedDate", sincePostedDate));
                sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_ToPostedDate", toPostedDate));
                sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_SinceEditedDate", sinceEditedDate));
                sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_ToEditedDate", toEditedDate));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PageIndex", pageIndex));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PageSize", pageSize));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_SortPosted", sortPosted));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_SortEdited", sortEdited));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_SortPosition", sortPosition));
                sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_ShowThanks", showThanks));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_MessagePosition", messagePosition));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_MessageID", messageId));
                sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_LastRead", lastRead));
                sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_UTCTIMESTAMP", DateTime.UtcNow));

                sc.CommandText.AppendObjectQuery("post_list", mid);
                return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, true);
            }
        }

        /// <summary>
        /// The post_list_reverse 10.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="topicId">
        /// The topic id.
        /// </param>
        /// <returns>
        /// The <see cref="T:System.Data.DataTable"/>.
        /// </returns>
        public static DataTable post_list_reverse10(int? mid, object topicId)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_TopicID", topicId));
                // sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_UTCTIMESTAMP", DateTime.UtcNow));

                sc.CommandText.AppendObjectQuery("post_list", mid);
                return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, true);
            }
        }

        /// <summary>
        /// The pollvote_check.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="pollid">
        /// The pollid.
        /// </param>
        /// <param name="userid">
        /// The userid.
        /// </param>
        /// <param name="remoteip">
        /// The remoteip.
        /// </param>
        /// <returns>
        /// The <see cref="T:System.Data.DataTable"/>.
        /// </returns>   
        public static DataTable pollvote_check(int? mid, object pollid, object userid, object remoteip)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PolID", pollid));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_UserID", userid));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_RemoteIP", remoteip));

                sc.CommandText.AppendObjectQuery("pollvote_check", mid);
                return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, true);
            }
        }

        /// <summary>
        /// The post_alluser.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="boardid">
        /// The boardid.
        /// </param>
        /// <param name="userid">
        /// The userid.
        /// </param>
        /// <param name="pageUserId">
        /// The page user id.
        /// </param>
        /// <param name="topCount">
        /// The top count.
        /// </param>
        /// <returns>
        /// The <see cref="T:System.Data.DataTable"/>.
        /// </returns>
        public static DataTable post_alluser(
            int? mid,
            object boardid,
            object userid,
            object pageUserId,
            object topCount)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_BoardID", boardid));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_UserID", userid));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PageUserID", pageUserId));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_NumberOfMessages", topCount));

                sc.CommandText.AppendObjectQuery("post_alluser", mid);
                DataTable dt1 = sc.ExecuteDataTableFromReader(
                    CommandBehavior.Default,
                    CommandType.StoredProcedure,
                    true);
                foreach (DataRow dr in dt1.Rows)
                {
                    if (dr["ReadAccess"].ToString() == "0")
                    {
                        dr.Delete();
                    }

                }
                dt1.AcceptChanges();

                return dt1;
            }
        }

        /// <summary>
        /// The poll group list.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="userId">
        /// The user id.
        /// </param>
        /// <param name="forumId">
        /// The forum id.
        /// </param>
        /// <param name="boardId">
        /// The board id.
        /// </param>
        /// <returns>
        /// The <see cref="IEnumerable{T}"/>.
        /// </returns>    
        public static IEnumerable<TypedPollGroup> PollGroupList(int? mid, int userId, int? forumId, int boardId)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_UserID", userId));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_ForumID", forumId));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_BoardID", boardId));

                sc.CommandText.AppendObjectQuery("pollgroup_list", mid);
                return
                    sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, true)
                        .AsEnumerable()
                        .Select(r => new TypedPollGroup(r));
            }
        }

        /// <summary>
        /// The pollgroup_stats.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="pollGroupId">
        /// The poll group id.
        /// </param>
        /// <returns>
        /// The <see cref="T:System.Data.DataTable"/>.
        /// </returns>  
        public static DataTable pollgroup_stats(int? mid, int? pollGroupId)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PollGroupID", pollGroupId));

                sc.CommandText.AppendObjectQuery("pollgroup_stats", mid);
                return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, true);
            }
        }

        /// <summary>
        /// The poll_stats.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="pollId">
        /// The poll id.
        /// </param>
        /// <returns>
        /// The <see cref="T:System.Data.DataTable"/>.
        /// </returns>
        public static DataTable poll_stats(int? mid, int? pollId)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PollID", pollId));

                sc.CommandText.AppendObjectQuery("poll_stats", mid);
                var dt = sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, true);
                var dtRet = new DataTable();
                foreach (DataColumn dc in dt.Columns)
                {
                    DataColumn dcRet;
                    dcRet = dc.DataType == typeof(decimal) 
                        ? new DataColumn(dc.ColumnName, typeof(int)) 
                        : new DataColumn(dc.ColumnName, dc.DataType);

                    dtRet.Columns.Add(dcRet);
                }

                dtRet.AcceptChanges();
                foreach (DataRow dr in dt.Rows)
                {
                    DataRow drRet = dtRet.NewRow();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        drRet[dc.ColumnName] = dr[dc];

                    }

                    dtRet.Rows.Add(drRet);
                }

                dtRet.AcceptChanges();
                return dtRet;
            }
        }

        /// <summary>
        /// The shoutbox_getmessages.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="boardId">
        /// The board id.
        /// </param>
        /// <param name="numberOfMessages">
        /// The number of messages.
        /// </param>
        /// <param name="useStyledNicks">
        /// The use styled nicks.
        /// </param>
        /// <returns>
        /// The <see cref="T:System.Data.DataTable"/>.
        /// </returns> 
        public static DataTable shoutbox_getmessages(int? mid, int boardId, int numberOfMessages, object useStyledNicks)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_BoardId", boardId));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_NumberOfMessages", numberOfMessages));
                sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_StyledNicks", useStyledNicks));

                sc.CommandText.AppendObjectQuery("shoutbox_getmessages", mid);
                return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, true);
            }
        }