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.

Advertisements

6 Responses to Executing Stored Procedures using Entity Framework

  1. […] Executing Oracle Stored Procedures using Entity Framework In a previous post I discussed an XSLT file which generates code to execute Stored Procedures on an SQL Server database using Entity Framework. […]

  2. Filipe Jacinto says:

    Hi!
    greetings from Lisbon!

    I need to automate the precess of getting my SP’s accessible on my MVC app which is accessing a dll produced by a VS ClassLibrary Project where my model is being constantly updated…

    I didn’t understand the process you described:
    -what should I do?
    –save just what comes after “handles Nullable parameters and IN and OUT parameters” to a SomeName.xsl file and than what? drag the file to my ClassLibrary project in VS?

    And do you have any idea of how could I automate de process?

    Thanks in advance!

    Filipe

  3. devio says:

    Filipe,

    you create the .xsl file as listed here, and add it to your VS project. Set input file and output file in the .xsl properties, and Execute to generate the output file. The output file needs to be added to the project as well.

    For automated use, you can call an XSLT processor in the pre-build event of your project (see https://devio.wordpress.com/2009/09/15/command-line-xslt-processor-with-powershell/ for a Powershell script).

    (As far as I know, the problems I tried to solve in this article do not apply to EF4, but I have not verified this)

  4. Nelson says:

    Hi, greetings from Colombia, thx for the post, I modified a little the xslt for my project.

    #region .Net NameSpaces

    using System;
    using System.Data;
    using System.Data.EntityClient;

    #endregion

    namespace My.Namespace
    {
    public partial class MyEntities
    {

    }
    }

    public void (

    ,
    out

    string
    byte[]

    global::System.Nullable<System.>

    )
    {
    if (this.Connection.State != ConnectionState.Open)
    {
    this.Connection.Open();
    }

    // The EntityCommand.CommandText value must be of the form ‘ContainerName.FunctionImportName’.
    EntityCommand cmd = ((EntityConnection)this.Connection).CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = @”MyEntities.”;
    cmd.Connection = (EntityConnection)this.Connection;

    EntityParameter par;

    par = cmd.CreateParameter();
    par.ParameterName = “”;
    par.DbType = DbType.;
    par.Direction = ParameterDirection.InputOutput;

    if (.HasValue)
    {
    par.Value = .Value;
    }

    if ( != null)
    {
    par.Value = ;
    }

    else
    {
    par.Value = DBNull.Value;
    }

    par.Value = DBNull.Value;

    cmd.Parameters.Add(par);

    cmd.ExecuteNonQuery();

    if (cmd.Parameters[“”].Value != DBNull.Value)
    =
    (byte[])

    (System.)

    cmd.Parameters[“”].Value;
    else
    = null;

    }

    • Nelson says:

      Sorry for the last post, the xslt is this:

      #region .Net NameSpaces

      using System;
      using System.Data;
      using System.Data.EntityClient;

      #endregion

      namespace My.Namespace
      {
      public partial class MyEntities
      {

      }
      }

      public void (

      ,
      out

      string
      byte[]

      global::System.Nullable<System.>

      )
      {
      if (this.Connection.State != ConnectionState.Open)
      {
      this.Connection.Open();
      }

      // The EntityCommand.CommandText value must be of the form ‘ContainerName.FunctionImportName’.
      EntityCommand cmd = ((EntityConnection)this.Connection).CreateCommand();
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandText = @”MyEntities.”;
      cmd.Connection = (EntityConnection)this.Connection;

      EntityParameter par;

      par = cmd.CreateParameter();
      par.ParameterName = “”;
      par.DbType = DbType.;
      par.Direction = ParameterDirection.InputOutput;

      if (.HasValue)
      {
      par.Value = .Value;
      }

      if ( != null)
      {
      par.Value = ;
      }

      else
      {
      par.Value = DBNull.Value;
      }

      par.Value = DBNull.Value;

      cmd.Parameters.Add(par);

      cmd.ExecuteNonQuery();

      if (cmd.Parameters[“”].Value != DBNull.Value)
      =
      (byte[])

      (System.)

      cmd.Parameters[“”].Value;
      else
      = null;

      }

  5. devio says:

    seems that xslt cannot be posted as wp comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: