Executing Stored Procedures using Entity Framework

Entity Framework allows you to map stored procedures onto so-called Function Imports. However, C# code for these function imports is only generated if the function import accepts as parameter or returns as result an entity table. (I noticed this in VS 2008, cannot tell about VS 2010)

Entity Framework provides the DbConnection and DbCommand classes to connection to an edmx database and execute stored procedures.

I’ll show to extract the necessary information from the edmx file and generate C# code to invoke the function imports.  Since an edmx file is in XML format, code generation is handled by an XSLT style sheet.

Let’s start with the XSLT header which defines all namespaces used by edmx:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"
 xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"
 xmlns:ssdl="http://schemas.microsoft.com/ado/2006/04/edm/ssdl"
 xmlns:cs="urn:schemas-microsoft-com:windows:storage:mapping:CS"
 xmlns:edm="http://schemas.microsoft.com/ado/2006/04/edm"
 xmlns:a="http://schemas.microsoft.com/ado/2006/04/codegeneration"
 xml:space="default" >
 <xsl:output method="text" omit-xml-declaration="yes"   />
 
 <xsl:template match="/">
//    
// automatically generated by StoredProcedures.xslt
//

namespace My.Namespace
{
 public partial class MyEntities 
 {
 <xsl:apply-templates select="/edmx:Edmx/edmx:Runtime/edmx:ConceptualModels/edm:Schema/edm:EntityContainer/edm:FunctionImport[not(@EntitySet)]" />
 }
}
 </xsl:template>s

The namespace and class name of the ObjectContext are not stored in the edmx, so we have to hard-code them in the XSLT. The root template matches all function imports that do not return EntitySets.

The sub-template

• generates a void method for each function import
• opens a DbConnection and sets the funtion import name
• translates the SQL Server data types of the stored procedure’s parameters into C# data types
• creates a C# parameter for every stored procedure parameter
• handles Nullable parameters and IN and OUT parameters

<xsl:template match="edm:FunctionImport">
 <xsl:variable name="method" select="@Name"></xsl:variable>
 <xsl:variable name="proc" select="/edmx:Edmx/edmx:Runtime/edmx:Mappings/cs:Mapping/cs:EntityContainerMapping/cs:FunctionImportMapping[@FunctionImportName=$method]/@FunctionName"></xsl:variable>
public void <xsl:value-of select="@Name"/>(
 <xsl:for-each select="edm:Parameter">
  <xsl:if test="position() != 1">, </xsl:if>
  <xsl:if test="@Mode = 'InOut'">out </xsl:if>
  <xsl:choose>
   <xsl:when test="@Type = 'String'">string</xsl:when>
   <xsl:when test="@Type = 'Binary'">byte[]</xsl:when>
   <xsl:otherwise>global::System.Nullable&lt;System.<xsl:value-of select="@Type"/>&gt;</xsl:otherwise>
  </xsl:choose>
  <xsl:text> </xsl:text>
  <xsl:value-of select="@Name"></xsl:value-of>
 </xsl:for-each>
)
{
  System.Data.Common.DbConnection conn = base.Connection;
  conn.Open();
  System.Data.Common.DbCommand cmd = conn.CreateCommand();
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.CommandText = "MyEntities.<xsl:value-of select="@Name"/>"; 
  System.Data.Common.DbParameter par;
 <xsl:for-each select="edm:Parameter">
  par = cmd.CreateParameter();
  par.ParameterName = "<xsl:value-of select="@Name" />";
  par.DbType = System.Data.DbType.<xsl:value-of select="@Type" />;<xsl:if test="@Mode = 'InOut'">
  par.Direction = System.Data.ParameterDirection.InputOutput;
 </xsl:if>
 <xsl:if test="@Mode != 'InOut'">
  <xsl:if test="@Type != 'String'">
  if (<xsl:value-of select="@Name"></xsl:value-of>.HasValue)
    par.Value = <xsl:value-of select="@Name"></xsl:value-of>.Value;
  </xsl:if>
 <xsl:if test="@Type = 'String'">
  if (<xsl:value-of select="@Name"></xsl:value-of> != null)
    par.Value = <xsl:value-of select="@Name"></xsl:value-of>;</xsl:if>
  else
    par.Value = System.DBNull.Value;
 </xsl:if>
 <xsl:if test="@Mode = 'InOut'">
  par.Value = System.DBNull.Value;
 </xsl:if>
  cmd.Parameters.Add(par);
 </xsl:for-each>
  cmd.ExecuteNonQuery();
 <xsl:for-each select="edm:Parameter">
  <xsl:if test="@Mode = 'InOut'">
  if (cmd.Parameters["<xsl:value-of select="@Name" />"].Value != System.DBNull.Value)
   <xsl:value-of select="@Name"/> = <xsl:choose>
    <xsl:when test="@Type = 'Binary'">(byte[])</xsl:when>
    <xsl:otherwise>(System.<xsl:value-of select="@Type" />)</xsl:otherwise>
   </xsl:choose>cmd.Parameters["<xsl:value-of select="@Name" />"].Value;
  else
   <xsl:value-of select="@Name"/> = null;
  </xsl:if>
 </xsl:for-each>
 conn.Close();
 }
 </xsl:template>

End of stylesheet

</xsl:stylesheet>

Load this XSLT in Visual Studio, select your edmx file as Input File, and execute.