Strongly-typed entities from stored procs
Toby Johnson
I am using DbMetal with the --sprocs option to generate methods from
Stored Procedures but I see that this returns DataSets and not Linq
objects. I have found this workaround:
http://groups.google.com/group/dblinq/browse_thread/thread/100b87265520b820/8163163aaf04b3e8
which suggests calling the SP with ExecuteQuery<T> and set T to the
desired return type. Unfortunately in this case the DataReader is
still open when looping through the results so making additional
database calls is not possible.
There is also a method here
http://groups.google.com/group/dblinq/browse_thread/thread/2f3c88b8863f729a/00f39486317e597f
which uses reflection to make this cast for each row but seems like a
roundabout and undesirable way to accomplish this.
Am I missing something here? Is there any possible way to accomplish
getting all results in an in-memory array of strongly typed objects,
even if I need to specify the object/shape to use?
Toby Johnson
> I am using DbMetal with the --sprocs option to generate methods from
> Stored Procedures but I see that this returns DataSets and not Linq
> objects. I have found this workaround:
>
>
> which suggests calling the SP with ExecuteQuery<T> and set T to the
> desired return type. Unfortunately in this case the DataReader is
> still open when looping through the results so making additional
> database calls is not possible.
So this is pretty much the solution I ended up using, and just create
a strongly-typed List of whatever my desired return-type is.
I've hacked together a Perl script that reads in a list of Sproc-to-
type mappings, then goes through the generated file and replaces the
sproc methods with a call to this method, using the appropriate return
type and parameters.
So, if I actually modified the DbLinq sources to do this instead (say,
add a command-line parameter to accept a sproc-to-type mapping file
name, then use that to generate the sproc methods), would such a patch
be considered for inclusion?
Toby
Jonathan Pryor
> On Jan 10, 4:37 pm, Toby Johnson <tobia...@gmail.com> wrote:
> > I am using DbMetal with the --sprocs option to generate methods from
> > Stored Procedures but I see that this returns DataSets and not Linq
> > objects.
> I've hacked together a Perl script that reads in a list of Sproc-to-
> type mappings, then goes through the generated file and replaces the
> sproc methods with a call to this method, using the appropriate return
> type and parameters.
>
> So, if I actually modified the DbLinq sources to do this instead (say,
> add a command-line parameter to accept a sproc-to-type mapping file
> name, then use that to generate the sproc methods), would such a patch
> be considered for inclusion?
No. The correct fix is to drop the DataSet and generate a *correct*
binding for the stored procedures.
For example, .NET's sqlmetal generates the following method for a stored
procedure:
[Function(Name="dbo.CustOrderHist")]
public ISingleResult<CustOrderHistResult> CustOrderHist([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);
return ((ISingleResult<CustOrderHistResult>)(result.ReturnValue));
}
// ...
public partial class CustOrderHistResult
{
private string _ProductName;
private System.Nullable<int> _Total;
public CustOrderHistResult()
{
}
[Column(Storage="_ProductName", DbType="NVarChar(40)")]
public string ProductName
{
get
{
return this._ProductName;
}
set
{
if ((this._ProductName != value))
{
this._ProductName = value;
}
}
}
[Column(Storage="_Total", DbType="Int")]
public System.Nullable<int> Total
{
get
{
return this._Total;
}
set
{
if ((this._Total != value))
{
this._Total = value;
}
}
}
}
DbMetal should do likewise, and generate a strongly typed return type
for stored procedures, NOT return DataSets. This is the correct fix.
I'd *love* a patch that did *that*. :-)
Thanks,
- Jon
Muhammad Niyaz Rana
--
You received this message because you are subscribed to the Google Groups "DbLinq" group.
To post to this group, send email to dbl...@googlegroups.com.
To unsubscribe from this group, send email to dblinq+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/dblinq?hl=en.
--
Best Regards,
Muhammad Niaz
Sr. Software Engineer
BITLogix (Pvt) Limited
www.ebitlogix.com | ( +9242 3577 4941-3 | ) +92 321 569 4195 | muhamm...@ebitlogix.com
702 Al-Hafeez Shopping Mall,
Main Boulevard Gulberg, Lahore
Jonathan Pryor
> Jon,
> can you please send the link of this helping patch. which
> generate the strongly type entities against the sps.
I didn't mention that I had such a patch. I said that this is what any
patch should *DO*. I haven't started looking into what it would take to
support it, though I'd love it if someone else did. :-)
- Jon
Toby Johnson
I think you misunderstood my solution. I completely agree that
DataSets are undesirable; that's not what my script does. It's a sort
of "halfway-there" solution.
Determining the shape of the results would indeed be rather difficult,
but in cases where the sproc is returning fields from one table only,
it seems better to return a strongly-typed collection of the entities
that are already defined.
So if my database already has a "Customer" table, dbmetal would have
generated a corresponding Customer class. Then if I have (for
simplicity sake) a "GetAllCustomers" SP, and I can tell dbmetal that
that SP returns Customer rows, then it can instead call
ExecuteQuery<Customer> and return an IEnumerable<Customer> collection.
Yes, it only solves a subset of SPs, and may be a temporary solution
until a better one is found, but still seems better than returning
DataSets.
Toby
Toby Johnson
> I didn't mention that I had such a patch. I said that this is what any
> patch should *DO*. I haven't started looking into what it would take to
> support it, though I'd love it if someone else did. :-)
Maybe I'm being overly simplistic here, but couldn't it just grab the
SP definition, add something like "AND 1=2" to the WHERE clause,
execute it, and then inspect the return field types? Of course that
wouldn't work if the SP used branching to return different row types
but again, it'd be closer...
Jonathan Pryor
> I think you misunderstood my solution. I completely agree that
> DataSets are undesirable; that's not what my script does. It's a sort
> of "halfway-there" solution.
I think I'm mis-understanding lots of things, unfortunately.
The big one: how would stored procedure return type detection work?
> Determining the shape of the results would indeed be rather difficult,
> but in cases where the sproc is returning fields from one table only,
> it seems better to return a strongly-typed collection of the entities
> that are already defined.
Right, but again, how do we tell this? From some googling around, it
looks like what SqlMetal does is extract the actual SQL used in the
stored procedure, parse it, and try to determine the structure of the
data returned.
I'm sure this is a cool piece of coding, but I don't see DbLinq doing
that anytime soon...
> So if my database already has a "Customer" table, dbmetal would have
> generated a corresponding Customer class. Then if I have (for
> simplicity sake) a "GetAllCustomers" SP, and I can tell dbmetal that
> that SP returns Customer rows, then it can instead call
> ExecuteQuery<Customer> and return an IEnumerable<Customer> collection.
Right, and this requires manual intervention (hence your helper script).
> Yes, it only solves a subset of SPs, and may be a temporary solution
> until a better one is found, but still seems better than returning
> DataSets.
So it's a matter of tradeoffs. Just to easily support `DbMetal /sprocs`
now, we need to support DataSet as the return type for the "no manual
intervention required" scenario. For a manual mapping, that presents
(at least) two solutions:
1. Add a DbMetal option to allow the user to specify a return type for a
stored procedure. DbMetal could then provide a decent return type.
2. Take advantage of the fact that all DbMetal-generated types are
partial classes, and just add new methods that use your nice return
types in separate partial class definitions.
The advantage of (2) over (1) is that DbMetal doesn't need to do
anything to support it. :-) (Yay less code!) It can also be done
today.
Of course the disadvantage is that DbMetal will thus be generating
methods that will show up in IntelliSense/etc. that you may not want to
be shown, and there's no existing mechanism to hide these methods
(except for dropping /sprocs, which will prevent any stored procedure
from being generated, and perhaps /generate-type so you could
generate/not generate stored procedures on a per-type basis).
Of these, I'm inclined toward the "less code" solution, though it might
also be possible to always generate some internal helper methods in the
generated DataContext/etc. to more easily support wrapping the stored
procedure calls.
- Jon
Toby Johnson
On Jan 21, 4:21 pm, Jonathan Pryor <jonpr...@vt.edu> wrote:
> So it's a matter of tradeoffs. Just to easily support `DbMetal /sprocs`
> now, we need to support DataSet as the return type for the "no manual
> intervention required" scenario. For a manual mapping, that presents
> (at least) two solutions:
>
> 1. Add a DbMetal option to allow the user to specify a return type for a
> stored procedure. DbMetal could then provide a decent return type.
>
> 2. Take advantage of the fact that all DbMetal-generated types are
> partial classes, and just add new methods that use your nice return
> types in separate partial class definitions.
>
> The advantage of (2) over (1) is that DbMetal doesn't need to do
> anything to support it. :-) (Yay less code!) It can also be done
> today.
>
> Of course the disadvantage is that DbMetal will thus be generating
> methods that will show up in IntelliSense/etc. that you may not want to
> be shown, and there's no existing mechanism to hide these methods
> (except for dropping /sprocs, which will prevent any stored procedure
> from being generated, and perhaps /generate-type so you could
> generate/not generate stored procedures on a per-type basis).
Thanks for the replies Jon. I'm currently doing a sort of hybrid of
these two approaches, and while it works for me it sounds like it may
not be the most general solution, so I won't worry about trying to
modify the program itself.
However you hadn't replied to my other response... is there any reason
that for SELECT queries, DbMetal can't just run the query (maybe with
adding "WHERE 1=2" to prevent long execution times) and see what
result types it gets? I'd think that this would work for the majority
of cases where the return type is consistent (i.e. the sproc doesn't
use branching logic to return multiple possible result types).
Toby
Andrus
adding "WHERE 1=2" to prevent long execution times) and see what
result types it gets?
In this case it would be better to use CommandBehavior.SchemaOnly
flag to invoke IDataReader.ExecuteReader(CommandBehavior behavior)
Andrus.