Calling Stored Procedures with Code First

codeOne of the weaknesses of Entity Framework 6 Code First is the lack of support for natively calling database constructs (views, stored procedures… etc). For those who have not heard of or used Code-First in Entity Framework (EF), Code-First is simply a Fluent mapping API. The idea is to create all your database mappings in code (i.e. C#) and the framework then creates and track the changes in the database schema.

In traditional Entity Framework to call a stored procedure you’d map it in your EDMX file. This is a multi-step process. Once the process is completed a method is created, which hangs off the DataContext.

I sought to making a calling stored procedure easier. At the heart of a stored procedure you have a procedure name, N number of parameters and a results set. I’ve written a small extension method that takes a procedure name, parameters and a return type. It just works. No mapping the procedure and it’s parameters.


public static List<TReturn> CallStoredProcedure<TParameters, TReturn>(this DataContext context, string storedProcedure, TParameters parameters) where TParameters : class where TReturn : class, new()
{
IDictionary<string,object> procedureParameters = new Dictionary<string, object>();
PropertyInfo[] properties = parameters.GetType().GetProperties();

var ps = new List<object>();

foreach (var property in properties)
{
object value = property.GetValue(parameters);
string name = property.Name;

procedureParameters.Add(name, value);

ps.Add(new SqlParameter(name, value));
}

var keys = procedureParameters.Select(p => string.Format("@{0}", p.Key)).ToList();
var parms = string.Join(", ", keys.ToArray());

return context.Database.SqlQuery<TReturn>(storedProcedure + " " + parms, ps.ToArray()).ToList();
}

 

usage


var context = new DataContext();

List<User> users = context.CallStoredProcedure<object,User>("User_GetUserById", new{userId = 3});

Deploying with MsDeploy Outside of Visual Studio

Building the msdeploy package with MSBuild.

This requires MsDeploy to be installed on the build machine.

MSBUILD /T:Package /P:Configuration=QA;PackageLocation="C:\Build\Artifacts\eserve\DEV\QA\QA.zip"

Deploying the package with MsDeploy to a web site

How to get the msdeploy command.

-source:package='C:BuildArtifactseserveDEVQAQA.zip' -dest:auto,ComputerName='https://eserve-dev.sacda.org:8172/MsDeploy.axd?site=eserve-dev',UserName='conwayc',Password='austin_1',IncludeAcls='False',AuthType='Basic'
-verb:sync
-disableLink:AppPoolExtension
-disableLink:ContentExtension
-disableLink:CertificateExtension
-allowUntrusted
-retryAttempts=2

Copying the package with ROBOCOPY

Copying the package to another folder with robocopy has an issue. Robocopy uses exit codes as success/error codes. CI servers look at the exit code of a command to determine success or failure. Robocopy breaks this model. Luckliy the sql team posted a code snippet to get around this issue.

rem http://weblogs.sqlteam.com/robv/archive/2010/02/17/61106.aspx
robocopy %*
rem suppress successful robocopy exit statuses, only report genuine errors (bitmask 16 and 8 settings)
set/A errlev="%ERRORLEVEL% & 24"
rem exit batch file with errorlevel so SQL job can succeed or fail appropriately
exit/B %errlev%

Deploying from folder to site

-verb:sync -source:contentPath=C:BuildArtifactsSSOClientDEV -dest:contentPath="C:inetpubadfsls",computerName='http://customer.dev.myconsolidated.net
/MsDeployAgentService',userName=ccadmin,password=$urewest123

Change App Path at Commandline via MSBuild

/T:Package
/P:Configuration=DEV;PackageLocation="C:\BuildArtifacts\Grover\Dev\Builds\DEV\Grover.zip";DeployIISAppPath=dev.grover.winnemen.com

Using MsBuild to deploy contents to folder

/T:PipelinePreDeployCopyAllFilesToOneFolder /P:Configuration=QA;_PackageTempDir="C:Build\Artifacts\Momntz\DEV\Builds\QA

Deploying Local with MSDeploy

"C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -verb:sync -source:package='C:\BuildArtifacts\AlSupport.zip' -dest:auto,computerName='localhost' -allowUntrusted -retryAttempts=2 -verbose

Deploying folder to Azure with MSDeploy

The following command line is for deploying a folder to windows azure websites.

"C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -verb:sync -source:contentPath="C:\TeamCity\buildAgent\work\d018513aed1c09f\Build" -dest:contentPath="groverqa",wmsvc=waws-prod-bay-005.publish.azurewebsites.windows.net/msdeploy.axd?site=groverqa,userName=$groverqa,password=secret,authtype='Basic' -AllowUntrusted