Stored Procedure-paging and C # code call

Stored Procedure-paging and C # code call
Stored Procedure: SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ============================ ============ -- Author: <Author, QiangWang> -- Create date: <Create Date,> -- Description: <Description, paging,> -- ===================================================== ===== alter procedure OrderInfoPage @ startRecordIndex INT, -- page number @ pagesize int, -- number of pages @ strWhere varchar (500), -- Query condition @ strOrder varchar (200), -- sorting condition @ OUTpageCount INT OU T -- number of output records ASBEGINDECLARE @ SBegin int -- number of start records DECLARE @ EEnd int -- number of end records DECLARE @ strTmp NVARCHAR (1000) -- The data column read under the current condition DECLARE @ strSQL NVARCHAR (3000) SET @ SBegin = (@ startRecordIndex-1) * @ pagesize + 1 SET @ EEnd = @ pagesize * @ startRecordIndexBEGINSET @ strTmp = 'select @ OUTpageCount = count (1) FROM OrderInfo oi left join OrderDetail od on oi. orderNum = od. orderNum left join ProductInfo pi onod. productID = pi. ID left joi N Store s on pi. storeID = s. ID left join RandomCode rc on pi. fromID = rc. random left join UserInfo ui on oi. userID = ui. ID left join UserAddress ua on oi. addressID = ua. id' + @ strWhereexec sp_executesql @ strTmp, n' @ OUTpageCount int out', @ OUTpageCount OUTENDBEGIN -- with as subquery part -- ROW_NUMBER () OVER generates an ordered row number, the standard for generating the sequence is the order by id followed BY the OVER statement, which must also be added to tell SQL Server how you want to add the row sequence number. Set @ strSQL = 'with temptbl as (SELECT ROW_NUMBER () OVER (order by' + @ strOrder + ') AS Row, oi. ID as oiID, oi. title, oi. addTime, oi. address, oi. courierNumber, oi. goodsTime, oi. isPrint2, oi. orderNum, oi. payTime, oi. payType, oi. pricePay, oi. priceMust, oi. securityCode, oi. sendType, oi. status, oi. soureType, oi. userID, od. barCode, od. productID, od. productName, od. count, s. storeName, pi. brand, ui. weiXinName, ua. consigneeName, ua. consigneeMobile from OrderInfo oi left join OrderDetail od on oi. orderNum = od. orderNum left join ProductInfo pi onod. productID = pi. ID left join Store s on pi. storeID = s. ID left join UserInfo ui on oi. userID = ui. ID left join UserAddress ua on oi. addressID = ua. id' + @ strWhere + ') 'set @ strSQL + = 'select * FROM temptbl where Row between' + STR (@ SBegin) + 'and' + STR (@ EEnd) exec sp_executesql @ strSQL, n' @ startRecordIndex int, @ strWhere varchar (500), @ strOrder varchar (200) ', @ startRecordIndex, @ strWhere, @ strorderendgo C # code call: dataTable dt = Common. dbHelperSQL. execStoreProcedureForGettingTable (dir, "OrderInfoPage"); // execute the Stored Procedure int total = Common. dbHelperSQL. execStoreProcedureForGettingResult (dir, "OrderInfoPage"); // total records

/// <Summary> /// encapsulate and execute the Stored Procedure /// </summary> /// <param name = "parametersInstance"> stored procedure parameters </param> // /<param name = "storedProcedureName"> stored procedure name </param> // <returns> </returns> public static System. data. dataTable ExecStoreProcedureForGettingTable (System. collections. generic. dictionary <string, object> parametersInstance, string storedProcedureName) {using (SqlConnection con = new SqlConnection (connectionS Tring) {try {// set SQL SqlCommand cmd = new SqlCommand (storedProcedureName, con); cmd. CommandType = CommandType. StoredProcedure; cmd. CommandTimeout = 999; if (parametersInstance! = Null) {foreach (KeyValuePair <string, object> item in parametersInstance) {SqlParameter parm = new SqlParameter (item. key, item. value); cmd. parameters. add (parm) ;}} DataTable dt = new DataTable (Guid. newGuid (). toString (); SqlDataAdapter sdap = new SqlDataAdapter (cmd); sdap. fill (dt); return dt;} catch (Exception er) {throw er ;}}} /// <summary> /// returns the number of affected rows /// </summary> /// <param name = "para MetersInstance "> stored procedure parameters </param> /// <param name =" storedProcedureName "> stored procedure name </param> /// <returns> </returns> public static int ExecStoreProcedureForGettingResult (System. collections. generic. dictionary <string, object> parametersInstance, string storedProcedureName) {using (SqlConnection con = new SqlConnection (connectionString) {try {// set SQL SqlCommand cmd = new SqlCommand (storedProcedureName, co N); cmd. CommandType = CommandType. StoredProcedure; cmd. CommandTimeout = 999; if (parametersInstance! = Null) {foreach (KeyValuePair <string, object> item in parametersInstance) {SqlParameter parm = new SqlParameter (item. key, item. value); cmd. parameters. add (parm);} cmd. parameters ["@ OUTpageCount"]. direction = ParameterDirection. output;} DataTable dt = new DataTable (Guid. newGuid (). toString (); con. open (); cmd. executeNonQuery (); int num = (int) cmd. parameters ["@ OUTpageCount"]. value; con. close (); return num;} catch (Exception er) {throw er ;}}}