It turned out that I would like to know the stored procedure of the SQL Server and specify the type of parameters to return the value, but I have encountered problems when I really used it today.
Store procedure:
Create Procedure Sqlmembership_GetallUsers (@ApplicationName VARCHAR (255), @ PageSize Int, @ PageIndex Int, @ TotalRecords Int Output) AS
SELECT @totalRecords = count (id) from sqlmembership_users where proper applicationname = @ ApplicationName
declare @indextable table (id int identity (1,1), nid int) declare @PageLowerBound intdeclare @PageUpperBound intset @PageLowerBound = (@ pageindex-1) * @ pagesizeset @ PageUpperBound = @ PageLowerBound @ pagesizeset rowcount @PageUpperBoundinsert into @indextable ( nid) select id from SqlMembership_Users where applicationname = @ applicationname order by id descselect O. * from SqlMembership_Users O, @ indextable t where o.id = t.nidand t.id> @PageLowerBound and t.id <= @ PageUpperBound order by t .id .id
Call code:
MembershipUserCollection members = new MembershipUserCollection (); SqlDataReader dr; try {SqlCommand cmd = new SqlCommand ( "SqlMembership_GetAllUsers", conn); cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add ("@ ApplicationName", SqldbType.varchar, 255) .value = this.applicationname; cmd.parameters.add ("@ pageindex", sqldbtype.int) .value = pageindex; cmd.parameters.add ( "@PageSize", sqldbtype.int) .value = pageSize; Sqlparameter Parm = new SqlParameter ("@ TotalRecords", SqldbType.INT); PARM.DIRECTION = ParameterDirection.Output; cmd.Parameters.Add (PARM);
Conn (); DR = cmd.executeRead (); while (Dr.Read ()) {membershipUserFromreader (DR));}} (DR! = null) {Dr.close ();} TotalRecords = (int) pARM.VALUE;} catch {throw;} finally {conn.close ();} returnembers;
I just started the sentence that was marked in Dr.Close, the result kept the return value, and finally checked the DataReader.close method to suddenly realize:
MSDN:
The Close method will fill in the value of the output parameter, return value, and RecordsaffECTED, increasing the time used to shut down the SQLDataReader used to handle large or complex queries. If the number of records affected by the return value and query is not important, you can call the Cancel method of the associated SQLCommand object before calling the Close method, thereby reducing the time required to turn off SqlDataReader.
It seems that the practice is lacking.
