none
Dynamic SQL in SPROCS RRS feed

  • Question

  • It seems that LINQ to SQL does not work with stored procedures that have dynamic sql inside because it cannot discover the return types. Is this true? In VS2008 I have dragged one such sp into my project and discovered that its return type is defined as 'None' and I don't have the ability to change it.

    I am working on a large project with all db queries defined in hundreds of sprocs by a very competent db team. Changing the sprocs to not have dynamic sql is out of the question since the db guys will never agree to that.

    What options do I have if any?
    Friday, May 9, 2008 3:37 PM

Answers

  •  

    Thanks for reporting this.

    This is related to a LinqDesigner tool bug that we are following up on. Meanwhile as a workaround you can update the DBML directly and have the SPROC return the type you intend to.

    Friday, May 9, 2008 11:15 PM
  •  

     

    You don't need to change your sprocs.

     

    What you can do is define methods corresponding to the sprocs but decorated appropriately with the ResultType attributes. Here's an example of a sproc that returns a different result set based on the value of the input parameter:

     

     

    Code Snippet

               

                [Function(Name="dbo.SprocThatReturnsProductsOrCustomers")]

                [ResultType(typeof(Product))]

                [ResultType(typeof(Customer))]

                public IMultipleResults MySproc(

                            [Parameter(DbType="Int")] int? id)

                {

                      IExecuteResult result = this.ExecuteMethodCall(

                             this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));

                      return ((IMultipleResults)(result.ReturnValue));

                }

     

     

     

    You can then use it as follows:

     

    Code Snippet

             

                IMultipleResults sprocResults = db.MySproc(1);

                List prods = sprocResults.GetResult().ToList();

     

                IMultipleResults otherResults = db.MySproc(2);

                List custs = otherResults.GetResult().ToList();

     

     

     

    Hope that helps,

     

    --Samir

    Friday, May 9, 2008 11:40 PM

All replies

  •  

    Thanks for reporting this.

    This is related to a LinqDesigner tool bug that we are following up on. Meanwhile as a workaround you can update the DBML directly and have the SPROC return the type you intend to.

    Friday, May 9, 2008 11:15 PM
  •  

     

    You don't need to change your sprocs.

     

    What you can do is define methods corresponding to the sprocs but decorated appropriately with the ResultType attributes. Here's an example of a sproc that returns a different result set based on the value of the input parameter:

     

     

    Code Snippet

               

                [Function(Name="dbo.SprocThatReturnsProductsOrCustomers")]

                [ResultType(typeof(Product))]

                [ResultType(typeof(Customer))]

                public IMultipleResults MySproc(

                            [Parameter(DbType="Int")] int? id)

                {

                      IExecuteResult result = this.ExecuteMethodCall(

                             this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));

                      return ((IMultipleResults)(result.ReturnValue));

                }

     

     

     

    You can then use it as follows:

     

    Code Snippet

             

                IMultipleResults sprocResults = db.MySproc(1);

                List prods = sprocResults.GetResult().ToList();

     

                IMultipleResults otherResults = db.MySproc(2);

                List custs = otherResults.GetResult().ToList();

     

     

     

    Hope that helps,

     

    --Samir

    Friday, May 9, 2008 11:40 PM