Print Page | Close Window

F1703 - Out parms in stored procs issues and questions

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2815
Printed Date: 15-Oct-2026 at 2:57pm


Topic: F1703 - Out parms in stored procs issues and questions
Posted By: pk55
Subject: F1703 - Out parms in stored procs issues and questions
Date Posted: 06-Jul-2011 at 11:00am

These are questions or issues with the new feature for 6.1.2.0 that adds output parameters to stored procedure calls to DevForce.  Entity Framework already supported out parms but DevForce never did.  The patch build I tested was 6.1.2.0(e).

At first when I installed the build and regenerated the models, nothing looked or acted differently but it built and ran and the SPs executed.  However, still no values in the out parms.  After a day or two of trying everything I could think of, I checked our T4 templates which extensively customize the normal T4s and we did in fact override a method that has changed  (WriteEdmFunctionExecuteDefinition) so that we could add a 'virtual' keyword to allow testing SPs with disconnected entity managers. Since the method that this method called just added an extra overloaded signature with an extra "parameterModes" argument it still worked like before for the patch but it might have been better to force that to be a breaking change so it would be obvious.

Second, (and this has been the same even pre-patch), DevForce always generates all SP parms (in or in/out) as a nullable type where as in LinqPad using EF, I can type them as the non-nullable types they really are.

Here's the definition of an output parm in LinqPad for a SQL SP that just takes "int":


ObjectParameter ReturnValue = new ObjectParameter("ai_return_value", typeof(Int32));

Here's the definition DevForce generates:


System.Nullable<int> ai_return_value

Third, and I think this is a bug, you have to initialize the output parms to NULL prior to executing the stored procedure or you won't get any results back out of them.  SQL server 2008 doesn't require this, EF doesn't require this but that's the only way it seems to work with DevForce.

Here's that same parm in LinqPad being set and EF happily returns data in it but when I do this from C#, the value never gets set to what the procedure returns:


ReturnValue.Value = -1;

Output parms that are System.String also have to be initialized to null; not empty string but null.  But if the procedure doesn't set them to a non-null string (like '') on returning (typically for us, it's an error message output parm), you'll get an exception:

Exception Type: IdeaBlade.EntityModel.EntityServerException 
Message: Unable to cast object of type 'System.DBNull' to type 'System.String'. 

Again, SQL server 2008 and EF don't have a problem with nullable values as out parms.

Next, as an FYI, calls to the SP function (when calling it on the Entity Server running sync) need to add the "ref" before the out parms but since the compiler will complain if you miss it, maybe in the doc you can just give a valid example like:


manager.GetSomeSPFunction(myInputParm, ref myOutputParm);

Lastly, I haven't been able to figure out how to call the same procedure from SL async.  In the case where the SP doesn't return any collection (it only sets the output parms), you need to execute it with ExecuteQueryForObjectAsync but the xxxQuery method DevForce builds returns a StoredProcQuery and ExecuteQueryForObjectAsync wants an IEntityScalarQuery so you get a compile error.  The sync version, ExecuteQueryForObject, takes an IEntityQuery.

Summary:

  • Check your T4 templates for overrides of WriteEdmFunctionExecuteDefinition as it has new parameters on the method used to build the stored proc function/query.  It would be nice if the release notes mentioned changes to any T4 method in the DevForce DomainModelTemplate.
  • You have to initialize your local variables to NULL or you won't get values back.
  • If you have a string out parm, the procedure can't leave it NULL (needs to be set to empty string) or you'll get an exception thrown.
  • Can't seem to call these from SL Async (at least when it doesn't have a return collection)

 

 


Replies:
Posted By: DenisK
Date Posted: 06-Jul-2011 at 3:33pm
Hi pk55;

There are many questions/points here. I haven't investigated all of them to have a response to each but I do have answers for some.

1. "DevForce always generates all SP parms (in or in/out) as a nullable type where as in LinqPad using EF, I can type them as the non-nullable types they really are."

EF uses an ObjectParameter class to encapsulate whatever logic they have under the covers to determine whether the parameter passed in is a nullable type or not. Looking at SQL 2005 and 2008 MSDN docs, one of the remarks for Stored Procedures is that "By default, parameters are nullable".  http://msdn.microsoft.com/en-us/library/ms187926%28v=SQL.100%29.aspx - http://msdn.microsoft.com/en-us/library/ms187926(v=SQL.100).aspx

This might be why DevForce generates a nullable type as well for the parameters. But don't take my word for it until I confirm with the development team.

2. "you have to initialize the output parms to NULL prior to executing the stored procedure or you won't get any results back out of them"

Yes, this is a bug and I've submitted it as high priority.

3. "calls to the SP function (when calling it on the Entity Server running sync) need to add the "ref" before the out parms..."

It looks like this is by design. But, as I will show you later, the key to populate your out parms resides in the StoredProcQuery.Parameters list. So it's not a requirement to have the "ref" keyword if you want to implement this yourself.

4. "how to call the same procedure from SL async"

You don't have to use ExecuteForQueryObject to retrieve your out parms. You can simply execute your StoredProcQuery asynchronously, and when it's done, retrieve them from the StoredProcQuery.Parameters.

Example 1:

var sprocQuery = manager.SomeStoredProcQuery(.....)
sprocQuery.ExecuteAsync(op => {
	var outParm1 = sprocQuery.Parameters[1].Value;
	var outParm2 = sprocQuery.Parameters[2].Value;

	//etc.....
});

Unfortunately, lambda expression can't handle ref parameters so if you need to pass in ref parms, example 1 won't work. The only other way that I can think of to handle this cleaner is to use Coroutine where you return the StoredProcQuery object after it has been executed and retrieve your out parms.

Example 2:

    public void AsyncStoredProcQueryWithOutputParms() {
      var op = Coroutine.Start(GetCompanyOutput);
      op.Completed += (s, args) => {
        if (args.Result is StoredProcQuery) {
          var query = args.Result as StoredProcQuery;
          var companyName = ((string)query.Parameters[1].Value);
          var rowVersion = ((System.Nullable<int>)query.Parameters[2].Value);
        }
      };
    }

    private IEnumerable<INotifyCompleted> GetCompanyOutput() {
      String companyName = null;
      Nullable<Int32> rowVersion = null;
      var query = _em1.GetCompanyNameOutputQuery(1, companyName, rowVersion);
      var storedProcOp = _em1.ExecuteQueryAsync(query);
      yield return storedProcOp;

      yield return Coroutine.Return(query);
    }

Note: Coroutine will not accept ref parameters as well.

I will discuss this with a senior engineer to see how we can handle this better.

I'll update you again once I have a proper response for the remaining questions. I'll also make sure that the StoredProcQuery documentation gets updated properly with this new feature.

Posted By: pk55
Date Posted: 06-Jul-2011 at 4:11pm
For #1: While it's true that the parms are nullable in SQL server, I can still create variables for input or input output  parms when calling the SP in LinqPad that specify the non-nullable type and EF doesn't complain and executes correctly.  In the C# code, I'd rather be creating variables that are not nulllable so future developers don't think the parm is optional (even though 'technically' it can be null on the call) but in the grand scheme of things, having nullable parms isn't that big of a deal.
 
For #3: it's not a problem to add "ref" and using the direct function call on the entity server works just fine.  It was more of an FYI for documentation.
 
For #4: Just a note about your example #1; your array index to get the output parms would be 0 and 1 if you weren't passing in any other parms. 
 
Thanks for the example and I was able to use that from SL with a SP query that takes one input parm and returns two output parms correctly so I'm not sure where you'd need a lambda expression.  


Int32? _returnValue = null;


Int64? _count = null;


string _inputArg = "some value";


var countQuery = manager.GetCountQuery(_inputArg, _returnValue, _count);


countQuery.ExecuteAsync(operation =>


{


_returnValue = (Int32?)(countQuery.Parameters[1].Value);


_count = (Int64?)(countQuery.Parameters[2].Value);


});

Posted By: DenisK
Date Posted: 06-Jul-2011 at 8:56pm
pk55;

For #1: I agree with your rationale and we always strive for our API to match as close as possible to that of EF. I was just trying to guess DF developers' reasoning behind the design. This could yet turn out to be a bug. But don't worry, either way, I'll confirm.

Re: T4 template change causing F1703 to not work.

Just to add to pk55's explanation, F1703 causes a change to the generated code. The change is that it will create a new stored proc query method inside your sub-typed EntityManager class, that you can only call synchronously by passing your output parameters by reference.

If you had overridden the WriteEdmFunctionExecuteDefinition function to customize your T4 template, be mindful that the MethodDef class now accepts a new parameter of type ParameterMode ( http://msdn.microsoft.com/en-us/library/system.data.metadata.edm.parametermode.aspx - http://msdn.microsoft.com/en-us/library/system.data.metadata.edm.parametermode.aspx ) which defaults to null. The new stored proc query method mentioned above requires this ParameterMode to be ParameterMode.InOut.

Posted By: DenisK
Date Posted: 07-Jul-2011 at 4:01pm
Hi pk55;

Re: "DevForce always generates all SP parms (in or in/out) as a nullable type"

I'm going to try to explain the reasoning behind this without going into too much detail.

During code generation we basically scan the CSDL part of the EDMX and generate our IB.Designer.cs based on that. In the case of stored procs, it becomes a FunctionImport element in CSDL listed with all of its Parameter elements. One of the ways we determine how we do our code generation is by analyzing the Parameter types and facets. As it turns out, all EDMSimpleTypes have Nullable as one of their facets.  http://msdn.microsoft.com/en-us/library/bb399548.aspx - http://msdn.microsoft.com/en-us/library/bb399548.aspx

Obviously, we can probably change our implementation to that of EF but as you have noted, this particular difference is not really a big deal. In EF, you have to create an ObjectParameter to pass in your parameter whereas in DF you can simply pass in the primitive type. I believe it is possible to create an encapsulation of similar logic so that you can pass in non-nullable types.

I would say that we will re-consider this implementation if we receive a lot of objections but for the time being, you have to implement the workaround suggested. I hope I've provided a clear explanation.

Posted By: DenisK
Date Posted: 07-Jul-2011 at 5:42pm
Output parms that are System.String also have to be initialized to null; not empty string but null.  But if the procedure doesn't set them to a non-null string (like '') on returning (typically for us, it's an error message output parm), you'll get an exception:

Exception Type: IdeaBlade.EntityModel.EntityServerException  
Message: Unable to cast object of type 'System.DBNull' to type 'System.String'.

This seems to be a bug as well. We should be able to handle DBNull and convert them appropriately. For now, the workaround is to check and convert each value manually.

For example:

if (Convert.IsDBNull(query.Parameters[0].Value)) {
	someParameter = null;
}

Please share if you find a better way to check this. In the mean time, I'll file a bug report.

I believe I have addressed all your questions and issues now. Please let me know if I'm missing anything.

Posted By: DenisK
Date Posted: 11-Jul-2011 at 1:27pm
Hi pk55;

The following bugs have been fixed.

- Stored proc output parms don't get updated when not initialized to null
- Stored procs with output parms query should be able to handle System.DBNull

Please let me know if you'd like a patch for this.

Posted By: pk55
Date Posted: 11-Jul-2011 at 1:36pm

I guess it depends on when the official 6.1.2 release is scheduled for.  If it's <= 2 weeks, we can wait for that otherwise, I'd take a patch build.

Posted By: DenisK
Date Posted: 11-Jul-2011 at 2:33pm
Official 6.1.2 is scheduled to be released near end of July.

Posted By: pk55
Date Posted: 11-Jul-2011 at 2:40pm
Sounds like the official date is still a little 'squishy'  :-) so I will take a patch build now if you've got one.  Thanks.

Posted By: DenisK
Date Posted: 11-Jul-2011 at 4:39pm
Sent to your pm inbox.


Print Page | Close Window