MSSqlServer paging stored procedures and C # Calls

Previously, I wrote the paging Stored Procedure of Oracle. Here, the paging Stored Procedure of MSSQLServer is also posted. However, the flexibility of this stored procedure is not as strong as that of Oracle. If you have good suggestions or methods, remember to leave a message and leave it empty. paste the code: 1. Stored Procedure: CreateorprocedureAspNetPage @ tblNamevarchar (1000)

Previously, I wrote the paging Stored Procedure of Oracle. Here I also posted the paging Stored Procedure of ms SQL Server. However, the flexibility of this stored procedure is not as strong as that of Oracle. If you have good suggestions or methods, remember to leave a message and leave it empty. paste the code: 1. Stored procedure: Create or procedure AspNetPage @ tblNamevarchar (1000)

Previously, I wrote the paging stored procedure in Oracle and rented the Hong Kong Server. Here, the paging stored procedure in ms SQL Server is also posted. However, the flexibility of this stored procedure is not as strong as that in Oracle, if you have good suggestions or methods, please leave a message on the Hong Kong server.

Don't talk about it, paste the Code:

1. Stored Procedure:

Create or procedure AspNetPage
@ TblNamevarchar (1000), -- table name
@ SelectFieldName varchar (4000), -- field name to be displayed (do not add select)
@ StrWherevarchar (4000), -- Query condition (Note: Do not add where)
@ OrderFieldNamevarchar (255), -- Sort index field name
@ PageSizeint, -- page size
@ PageIndexint = 1, -- page number
@ IRowCountint output, -- total number of returned records
@ OrderTypebit = 0 -- set the sorting type. If the value is not 0, the sorting type is descending.

AS
Declare @ strSQL varchar (4000) -- subject sentence
Declare @ strTmp varchar (4000) -- Temporary Variable
Declare @ strOrder varchar (400) -- sort type
Declare @ strRowCount nvarchar (4000) -- The statement used to query the total number of records
Set @ OrderFieldName = ltrim (rtrim (@ OrderFieldName ))
If @ OrderType! = 0
Begin
Set @ strTmp = '<(select min'
Set @ strOrder = 'ORDER BY' + @ OrderFieldName + 'desc'
End
Else
Begin
Set @ strTmp = '> (select max'
Set @ strOrder = 'ORDER BY' + @ OrderFieldName + 'asc'
End
Set @ strSQL = 'select top '+ str (@ PageSize) + @ SelectFieldName + 'from'
+ @ TblName + 'where' + @ OrderFieldName + @ strTmp + '('
+ Right (@ OrderFieldName, len (@ OrderFieldName)-charindex ('. ', @ OrderFieldName) +') from (select top '+ str (@ PageIndex-1) * @ PageSize)
+ @ OrderFieldName + 'from' + @ tblName + @ strOrder + ') as tblTmp )'
+ @ StrOrder
If @ strWhere! =''
Set @ strSQL = 'select top '+ str (@ PageSize) + @ SelectFieldName + 'from'
+ @ TblName + 'where' + @ OrderFieldName + @ strTmp + '('
+ Right (@ OrderFieldName, len (@ OrderFieldName)-charindex ('. ', @ OrderFieldName) +') from (select top '+ str (@ PageIndex-1) * @ PageSize)
+ @ OrderFieldName + 'from' + @ tblName + 'where' + @ strWhere +''
+ @ StrOrder + ') as tblTmp) and' + @ strWhere + ''+ @ strOrder
If @ PageIndex = 1
Begin
Set @ strTmp =''
If @ strWhere! =''
Set @ strTmp = 'where' + @ strWhere
Set @ strSQL = 'select top '+ str (@ PageSize) + @ SelectFieldName + 'from'
+ @ TblName + @ strTmp + ''+ @ strOrder
End
Exec (@ strSQL)
If @ strWhere! =''
Begin
Set @ strRowCount = 'select @ iRowCount = count (*) from '+ @ tblName + 'where' + @ strWhere
End
Else
Begin
Set @ strRowCount = 'select @ iRowCount = count (*) from' + @ tblName
End
Exec sp_executesql @ strRowCount, n' @ iRowCount int out', @ iRowCount out

2. C # call:

///

/// Paging data
///
/// Indicates
/// Returned Field
/// Condition
/// Number of records per page
/// Current page number
/// Total number of records
/// Sorting Field
///
Public static DataTable GetPageList (string TableName, string RetureFields, string strWhere, int PageSize, int CurPage, out int RowCount, string sortField)
{
SqlCommand cmd = new SqlCommand ("AspNetPage"); // name of the stored procedure
Cmd. CommandType = CommandType. StoredProcedure;
Cmd. Parameters. AddWithValue ("@ tblName", TableName); // table name
Cmd. Parameters. AddWithValue ("@ OrderFieldName", sortField); // sort the index field name
Cmd. Parameters. AddWithValue ("@ PageIndex", CurPage); // current page, Hong Kong Vm, page number
Cmd. Parameters. AddWithValue ("@ PageSize", PageSize); // number of data entries displayed on each page
Cmd. Parameters. AddWithValue ("@ SelectFieldName", RetureFields); // field name to be displayed (do not add Select)
Cmd. Parameters. AddWithValue ("@ OrderType", 1); // set the sorting type. If the value is not 0, the sorting type is descending.
Cmd. Parameters. AddWithValue ("@ strWhere", strWhere); // query condition. Do not add where
Cmd. Parameters. Add (new SqlParameter ("@ iRowCount", SqlDbType. Int ));
Cmd. Parameters ["@ iRowCount"]. Direction = ParameterDirection. Output;
DataTable dt = RunProcedureCmd (cmd );
RowCount = Convert. ToInt32 (cmd. Parameters ["@ iRowCount"]. Value. ToString (); // The total number of returned pages
Return dt;
}

///

/// Execute the stored procedure and return the DataTable
///
///
///
Public static DataTable RunProcedureCmd (SqlCommand cmd)
{
DataTable result = new DataTable ();
SqlConnection conn = new SqlConnection (ConnectionString); // your own link string
Try
{
If (conn. State = ConnectionState. Closed ))
{
Conn. Open ();
}
Cmd. Connection = conn;
WriteLog (cmd. CommandText );
SqlDataAdapter da = new SqlDataAdapter (cmd );
Da. Fill (result );
Da. Dispose ();
Conn. Close ();
Conn. Dispose ();

Return result;
}
Catch (Exception ex)
{
Conn. Close ();
Conn. Dispose ();
Throw ex;
}
}

OK!