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

Writing the SVN Reversion to a File in TeamCity

I spent hours trying to figure this one out. It turned out brain dead simple.

  1. Add a build step
  2. Select the command line runner type
  3. Set the working path to the path you’d like to save the version file too.
  4. In the Custom Script Text Field add the following text: ECHO %build.vcs.number% > version.txt

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:BuildArtifactseserveDEVQAQA.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:BuildArtifactsMomntzDEVBuildsQA</code>

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