Posted by: Cirilo Meggiolaro | 02/18/2009

Tip of the day #127 – Executing non query stored procedures with ADO.NET Entity Framework

You have two ways to execute stored procedures with ADO.NET Entity Framework that don’t retrieve data:

  • You may map a stored procedure to an entity so you can have more control over the inserts, updates and deletes statements. Once you have your stored procedure created on the database and you have imported the stored procedure to your EDM model you have to map to a specific entity operation such as insert, update or delete (Tip #126 describes a step by step on getting stored procedures and add them to the EDM) ;
  • To start, open your edmx file, select the entity and right-click on it. Select the option Stored Procedure Mapping to display the mapping details on the bottom part of the screen. The following picture shows that:
Picture 1 - Stored procedure mapping option selected on context menu

Picture 1 - Stored procedure mapping option selected on context menu

  • You will see that three functions are added to the Mapping Details section: Insert, Update and Delete functions;
  • The next step is to select a procedure for each operation you need and mapped the parameters to the entity property. Good news. Once the names match, Visual Studio suggests the mapping for you. The next picture shows the final mapping;
Picture 2 - Stored procedure mapped with operations and columns x parameters.

Picture 2 - Stored procedure mapped with operations and columns x parameters.

  • You may have noticed that the stored procedure was mapped to an entity operation and its parameters mapped to the entity properties;
  • The insert, update and delete operations are going to use the stored procedure instead of internal T-SQL statements. The code at that point is similar to any other operation. The following code snippet shows the code to insert a new customer to our table:

/// Instantiates the entity context.
using (SmoTestEntities ctx = new SmoTestEntities())
{
    /// Creates a new customer
    ctx.AddToCustomer(new Customer()
    {
        customer_name = “George Mayers”,
        customer_dob = new DateTime(1970, 5, 12),
        customer_active = true
    });

    /// Commit all changes to the database.
    ctx.SaveChanges();
}

Now, if you want to execute a procedure other than the ones associated to an entity operation you’ll have to use the regular Connection, Command and Parameter objects. There are implementations of those methods for the Entity Framework as well:

/// Instantiates the entity context.
using (SmoTestEntities ctx = new SmoTestEntities())
{
    /// Creates the entity connection object.
    EntityConnection entityConnection = (EntityConnection)ctx.Connection;

    /// Creates the entity command.
    EntityCommand command = entityConnection.CreateCommand();

    /// Sets the procedure name.
    command.CommandText = “SmoTestEntities.InsertCustomer”;

    /// Defines the command type.
    command.CommandType = CommandType.StoredProcedure;

    /// Adds the parameters.
    command.Parameters.Add(new EntityParameter(“customer_name”, DbType.String, 250));
    command.Parameters[0].Value = “George Mayers”;

    command.Parameters.Add(new EntityParameter(“customer_dob”, DbType.DateTime));
    command.Parameters[1].Value = new DateTime(1970, 5, 12);

    command.Parameters.Add(new EntityParameter(“customer_active”, DbType.Boolean));
    command.Parameters[2].Value = true;

    /// Opens connection.
    entityConnection.Open();

    /// Executes stored procedure.
    command.ExecuteNonQuery();

    /// Closes connection.
    entityConnection.Close();
}

To make this work you need to make sure:

  • The CommandText property from the Command object has the container name;
  • The parameter name doesn’t have the @ symbol as we are used for regular database parameters.

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

Categories

%d bloggers like this: