Wednesday, August 2, 2017

How to execute a Stored Procedure with EF Code First

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);
            }

No comments:

Post a Comment

Mixed Content: The page at xxx was loaded over HTTPS, but requested an insecure

 Mixed Content: The page at ' https ://www.test.com/signup.aspx' was loaded over HTTPS, but requested an insecure script ' http ...