Here is class structure and procedures.
public class Books_Result
{
public int BookId{ get; set; }
public string BookName{ get; set; }
public string AuthorName{ get; set; }
public decimal BookRate{ get; set; }
public string Description{ get; set; }
public bool Active{ get; set; }
}
CREATE PROCEDURE YourStoredProcedureName
@SortBy VARCHAR(5000) ,
@FilterBy VARCHAR(5000) ,
@BookName VARCHAR(5000)
@TotalRecords INT OUT ,
@TotalDisplayRecords INT OUT
AS
BEGIN
SET @TotalRecords = 0
SET @TotalDisplayRecords = 0
DECLARE @sqlQuery AS NVARCHAR(MAX)
set @sqlQuery =N'SELECT BookId,BookName,AuthorName,BookRate,Description FROM [Books]
WHERE BookName= @BookName
set @TotalDisplayOutputRecords=( SELECT COUNT(*) [Books]
WHERE BookName= @BookName )
'
EXECUTE sp_executesql @sqlQuery,
N'@TotalDisplayOutputRecords INT OUT',
@TotalDisplayOutputRecords = @TotalDisplayRecords OUT
SET @TotalRecords = @TotalDisplayRecords
END
This method will return an DbRawSqlQuery, which you can enumerate using For / ForEach loop. For executing procedure with output parameter.
DataTable dt = new DataTable();
using (var Entities = new YourEntities())
{
var TotalRecords = new ObjectParameter("TotalRecords", typeof(int));
var TotalDisplayRecords = new ObjectParameter("TotalDisplayRecords", typeof(int));
System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[5];
param[0] = new System.Data.SqlClient.SqlParameter();
param[0].ParameterName = "@SortBy";
param[0].Size = 5000;
param[0].SqlDbType = SqlDbType.VarChar;
param[0].Value = "BookId asc";
param[1] = new System.Data.SqlClient.SqlParameter();
param[1].ParameterName = "@FilterBy";
param[1].Size = 5000;
param[1].SqlDbType = SqlDbType.VarChar;
param[1].Value = "";
param[2] = new System.Data.SqlClient.SqlParameter();
param[2].ParameterName = "@BookName";
param[2].Size = 5000;
param[2].SqlDbType = SqlDbType.VarChar;
param[2].Value = "Entity Framework";
param[3] = new System.Data.SqlClient.SqlParameter();
param[3].ParameterName = "@TotalRecords";
param[3].SqlDbType = SqlDbType.Int;
param[3].Value = DBNull.Value;
param[3].Direction = ParameterDirection.InputOutput;
param[4] = new System.Data.SqlClient.SqlParameter();
param[4].ParameterName = "@TotalDisplayRecords";
param[4].SqlDbType = SqlDbType.Int;
param[4].Value = DBNull.Value;
param[4].Direction = ParameterDirection.InputOutput;
var BookCollectionList= Entities.ExecuteStoreQuery<Books_Result> ("YourStoredProcedureName @SortBy, @FilterBy, @BookName, @TotalRecords out , @TotalDisplayRecords out", param).ToList();
dt = BookCollectionList.ToDataTable();
TotalRecords = Convert.ToInt32(param[3].Value);
TotalDisplayRecords = Convert.ToInt32(param[4].Value);
}
public class Books_Result
{
public int BookId{ get; set; }
public string BookName{ get; set; }
public string AuthorName{ get; set; }
public decimal BookRate{ get; set; }
public string Description{ get; set; }
public bool Active{ get; set; }
}
CREATE PROCEDURE YourStoredProcedureName
@SortBy VARCHAR(5000) ,
@FilterBy VARCHAR(5000) ,
@BookName VARCHAR(5000)
@TotalRecords INT OUT ,
@TotalDisplayRecords INT OUT
AS
BEGIN
SET @TotalRecords = 0
SET @TotalDisplayRecords = 0
DECLARE @sqlQuery AS NVARCHAR(MAX)
set @sqlQuery =N'SELECT BookId,BookName,AuthorName,BookRate,Description FROM [Books]
WHERE BookName= @BookName
set @TotalDisplayOutputRecords=( SELECT COUNT(*) [Books]
WHERE BookName= @BookName )
'
EXECUTE sp_executesql @sqlQuery,
N'@TotalDisplayOutputRecords INT OUT',
@TotalDisplayOutputRecords = @TotalDisplayRecords OUT
SET @TotalRecords = @TotalDisplayRecords
END
This method will return an DbRawSqlQuery, which you can enumerate using For / ForEach loop. For executing procedure with output parameter.
DataTable dt = new DataTable();
using (var Entities = new YourEntities())
{
var TotalRecords = new ObjectParameter("TotalRecords", typeof(int));
var TotalDisplayRecords = new ObjectParameter("TotalDisplayRecords", typeof(int));
System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[5];
param[0] = new System.Data.SqlClient.SqlParameter();
param[0].ParameterName = "@SortBy";
param[0].Size = 5000;
param[0].SqlDbType = SqlDbType.VarChar;
param[0].Value = "BookId asc";
param[1] = new System.Data.SqlClient.SqlParameter();
param[1].ParameterName = "@FilterBy";
param[1].Size = 5000;
param[1].SqlDbType = SqlDbType.VarChar;
param[1].Value = "";
param[2] = new System.Data.SqlClient.SqlParameter();
param[2].ParameterName = "@BookName";
param[2].Size = 5000;
param[2].SqlDbType = SqlDbType.VarChar;
param[2].Value = "Entity Framework";
param[3] = new System.Data.SqlClient.SqlParameter();
param[3].ParameterName = "@TotalRecords";
param[3].SqlDbType = SqlDbType.Int;
param[3].Value = DBNull.Value;
param[3].Direction = ParameterDirection.InputOutput;
param[4] = new System.Data.SqlClient.SqlParameter();
param[4].ParameterName = "@TotalDisplayRecords";
param[4].SqlDbType = SqlDbType.Int;
param[4].Value = DBNull.Value;
param[4].Direction = ParameterDirection.InputOutput;
var BookCollectionList= Entities.ExecuteStoreQuery<Books_Result> ("YourStoredProcedureName @SortBy, @FilterBy, @BookName, @TotalRecords out , @TotalDisplayRecords out", param).ToList();
dt = BookCollectionList.ToDataTable();
TotalRecords = Convert.ToInt32(param[3].Value);
TotalDisplayRecords = Convert.ToInt32(param[4].Value);
}
No comments:
Post a Comment