Skip to content
master
Switch branches/tags
Go to file
Code

Latest commit

 

Git stats

Files

Permalink
Failed to load latest commit information.
Type
Name
Latest commit message
Commit time
 
 
 
 
 
 
 
 

README.md

Snickler.EFCore

Fluent Methods for mapping Stored Procedure results to objects in EntityFrameworkCore

NuGet

Usage

Executing A Stored Procedure

Add the using statement to pull in the extension method. E.g: using Snickler.EFCore

      var dbContext = GetDbContext();
      dbContext.LoadStoredProc("dbo.SomeSproc")
               .WithSqlParam("fooId", 1)              
               .ExecuteStoredProc((handler) =>
                {                  
                    var fooResults = handler.ReadToList<FooDto>();      
                    // do something with your results.
                });

Handling Multiple Result Sets

      var dbContext = GetDbContext();
      dbContext.LoadStoredProc("dbo.SomeSproc")
               .WithSqlParam("fooId", 1)              
               .ExecuteStoredProc((handler) =>
                {                  
                    var fooResults = handler.ReadToList<FooDto>();      
                    handler.NextResult();
                    var barResults = handler.ReadToList<BarDto>();
                    handler.NextResult();
                    var bazResults = handler.ReadToList<BazDto>()
                });

Handling Output Parameters

      DbParameter outputParam = null;
    
      var dbContext = GetDbContext();
      dbContext.LoadStoredProc("dbo.SomeSproc")
               .WithSqlParam("fooId", 1)  
               .WithSqlParam("myOutputParam", (dbParam) =>
               {                 
                 dbParam.Direction = System.Data.ParameterDirection.Output;
                 dbParam.DbType = System.Data.DbType.Int32;          
                 outputParam = dbParam;
               })
               .ExecuteStoredProc((handler) =>
                {                  
                    var fooResults = handler.ReadToList<FooDto>();      
                    handler.NextResult();
                    var barResults = handler.ReadToList<BarDto>();
                    handler.NextResult();
                    var bazResults = handler.ReadToList<BazDto>()
                });
                
                int outputParamValue = (int)outputParam?.Value;

Using output parameters without returning a result set

      DbParameter outputParam = null;

      var dbContext = GetDbContext();

      await dbContext.LoadStoredProc("dbo.SomeSproc")
            .WithSqlParam("InputParam1", 1)
            .WithSqlParam("myOutputParam", (dbParam) =>
            {
                  dbParam.Direction = System.Data.ParameterDirection.Output;
                  dbParam.DbType = System.Data.DbType.Int16;
                  outputParam = dbParam;
            })

            .ExecuteStoredNonQueryAsync();

      int outputParamValue = (short)outputParam.Value;

Using output parameters without returning a result set but also getting the number of rows affected

Make sure your stored procedure does not contain SET NOCOUNT ON.

      int numberOfRowsAffected = -1;

      DbParameter outputParam = null;

      var dbContext = GetDbContext();

      numberOfRowsAffected = await dbContext.LoadStoredProc("dbo.SomeSproc")
            .WithSqlParam("InputParam1", 1)
            .WithSqlParam("myOutputParam", (dbParam) =>
            {
                  dbParam.Direction = System.Data.ParameterDirection.Output;
                  dbParam.DbType = System.Data.DbType.Int16;
                  outputParam = dbParam;
            })

            .ExecuteStoredNonQueryAsync();

      int outputParamValue = (short)outputParam.Value;

Changing the execution timeout when waiting for a stored procedure to return

      DbParameter outputParam = null;

      var dbContext = GetDbContext();

      // change timeout from 30 seconds to 300 seconds (5 minutes)
      await dbContext.LoadStoredProc("dbo.SomeSproc", commandTimeout:300)
            .WithSqlParam("InputParam1", 1)
            .WithSqlParam("myOutputParam", (dbParam) =>
            {
                  dbParam.Direction = System.Data.ParameterDirection.Output;
                  dbParam.DbType = System.Data.DbType.Int16;
                  outputParam = dbParam;
            })

            .ExecuteStoredNonQueryAsync();

      int outputParamValue = (short)outputParam.Value;

About

EFCore Extension that allows a means to map a stored procedure to a class, fluently.

Topics

Resources

License

Packages

No packages published

Languages