Using Stored Procedures That Return Non Entity Type

Using Stored Procedures That Return Non Entity Type

  • Comments 1

In  the previous post I explained how to use a Stored Procedure in Entity Framework that returns an Entity Type.  However, if you try to map a stored procedure that is of a scalar type or returns an Array or List type then this won't work automatically with the current release of Entity Framework.

In order to get this working you might need to implement a partial class and then specify the method. So lets take a look at how to use a Sproc that returns a Distinct Country names from Northwind.Customers

CREATE PROCEDURE dbo.GetCountryList
AS
    BEGIN
     SET NOCOUNT ON 
      SELECT DISTINCT COUNTRY FROM CUSTOMERS ORDER BY COUNTRY ASC
     
  END 

 

First we might need to add the stored procedure inside the EDMX schema as we did in the previous stored procedure demo. So in the Model browser go ahead and the stored procedure by first adding it to Store Model and the adding the Function Import. Here is a screenshot

image

 

Notice that I have the return type as Scalar.  This will generate the following CSDL element

<FunctionImport Name="GetCountryList" ReturnType="Collection(String)" /></EntityContainer>

The return type element specify that its a Collection of String. Lets implement the partial class and write the code for calling the stored procedure

public partial class NorthwindEntities :global::System.Data.Objects.ObjectContext
 {
    /// <summary>
    /// Implementation for calling a method that returns distinct country list
    /// </summary>
    /// <returns>String collection </returns>
    public Collection<string> GetCountryList()
    {
        Collection<string> results = new Collection<string>();

        try
        {
            //get the connection object from the DataContext
            using (DbConnection conn = this.Connection)
            {
                DbCommand cmd = conn.CreateCommand();
                //command text is of the format of 'ContainerName.Stored Proc Name' 
                cmd.CommandText = String.Format("{0}.{1}", this.DefaultContainerName, "GetCountryList");
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                if (this.Connection.State != System.Data.ConnectionState.Open)
                {
                    this.Connection.Open();
                }

                var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                while (reader.Read())
                {
                    results.Add((string)reader[0]);
                }

                return results;
            }
        }
        catch (Exception ex)
        {
            //Todo: meaning full exception handling 
        }



        return (Collection<string>)null;
       
    }
 }

Once this is compile you will be able to call this method from the front end using intellisense as shown in the screenshot below


image

Here is the code for forms binding

 

private void Form1_Load(object sender, EventArgs e)
       {
           using (NorthwindEntities entities = new NorthwindEntities())
           {
               this.customersBindingSource.DataSource = entities.CustByCountry("Germany");
               //bind the combo box
               //get the list of countries
               var countries = entities.GetCountryList();
               //for each country item in countries 
               foreach (var c in countries)
               {
                   //add the country name
                   this.toolStripComboBox1.Items.Add(c);

               }

           }
       }

 

And here is the output of the forms binding

image

Comments
Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post