locked
Stored procedures without result sets RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am trying to use a stored procedure to update a database. the SP doesn't have a result set.

    I have the following

     

    CSDL

    <FunctionImport Name="SetPrincipalPreference">

    <Parameter Name="principalTypeCode" Mode="In" Type="String" />

    <Parameter Name="principalId" Mode="In" Type="Guid" />

    <Parameter Name="preferenceName" Mode="In" Type="String" />

    <Parameter Name="preferenceTypeFullName" Mode="In" Type="String" />

    <Parameter Name="preferenceDataXml" Mode="In" Type="String" />

    </FunctionImport>

     

    SSDL

    <Function Name="SetPrincipalPreference" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">

    <Parameter Name="principalTypeCode" Type="char" Mode="In" />

    <Parameter Name="principalId" Type="uniqueidentifier" Mode="In" />

    <Parameter Name="preferenceName" Type="varchar" Mode="In" />

    <Parameter Name="preferenceTypeFullName" Type="varchar" Mode="In" />

    <Parameter Name="preferenceDataXml" Type="ntext" Mode="In" />

    </Function>

     

    MSL

    <FunctionImportMapping FunctionImportName="SetPrincipalPreference" FunctionName="UserSystemModel.Store.SetPrincipalPreference" />

     

    I cannot see this function/SP unless I change the CSDL to be:

    <FunctionImport Name="SetPrincipalPreference" EntitySet="SomeEntitySet" ReturnType="SomeReturnType">

     

    however I dont have an EntitySet for it because it doesnt return anything.

     

    Thursday, March 13, 2008 8:26 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Found an answer to my problem here:

     

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2643093&SiteID=1

     

     Noam Ben-Ami // MSFT wrote:

    Unfortunately, due to time constraints, we do not do codegen for stored procedures that return primitive types such as String/Int32, etc.

     

    For now, you can create your object context and get its connection. Then, use the connection's "CreateCommand" method, and create a command of type stored procedure whose name is the name of the function import. Then you can execute that command and get your string back. E.g. if your function is called "getXMLAsString" then in your DataContext's partial class you would write:

     

    System.Data.Common.DbConnection connection = this.Connection;

    System.Data.Common.DbCommand command = connection.CreateCommand();

    command.CommandType = System.Data.CommandType.StoredProcedure;

    command.CommandText = "getXMLAsString";

    string result = command.ExecuteScalar() as string;

     

    We will make this experience better in the future.

     

    HTH,

      Noam

     

     

    Long story short, there is no support for SP's that dont have result sets that are mapped to EntitySets. You can use them if you generate the function in the ssdl, functionimport in csdl, and the mapping in the msl. Then create a partial class of your entity model and add your sp call to a method in there using more or less traditional ado.net code. you will need the container name of your entity which is one of the first nodes in the conceptual model or can be seen in the properties window of your edmx file.

     

    Thursday, March 13, 2008 10:40 PM