Execute sql function

Hello ,

Is there a more elegant way to execute a function than this:

IDataReader r = UnitOfWork.PrepareCommand(new SqlCommand { CommandText = "SELECT * FROM FuncGetCustomers()" }).ExecuteReader();   ??

I am looking forward to your reply.

Thank you.

Not really -- LightSpeed isn't a general-purpose replacement for ADO.NET code, it's really primarily about object-relational mapping.  So it doesn't go to particular effort to simplify the calling of arbitrary SQL or the creation of data readers.

However, there are a couple of things that may help depending on your scenario:

* If you want to load the results of FuncGetCustomers as entities, you can use Find(ProcedureQuery) to do so, e.g. IList<Customer> customers = unitOfWork.Find<Customer>(new ProcedureQuery("dbo.FuncGetCustomers"));.  Updated: this does not work directly, though it can be made to work by creating stored procedure that invokes the TVF, and calling the sproc from LightSpeed.

* If you want to execute truly arbitrary SQL, the latest nightly builds include a FindBySql method which takes an IDbCommand.  Again this returns an entity list rather than an IDataReader.

* You can use LightSpeedContext.DataProviderObjectFactory to create the command so as to avoid the hard dependency on SqlCommand, e.g. IDbCommand cmd = context.DataProviderObjectFactory.CreateCommand();.  (You would still need to use PrepareCommand to wire it up to the UnitOfWork's connection.)

I know you have answered this above - but it has raised its head for me several times. 

Every-now and again the Lightspeed generated TSQL is just not right and I want to execute and return a scalar value from an arbitary scalar command against the database using the current UOW's connection and transaction.  

I am forced to use FindBySQL and returning a list of entities from which I pick the first - but its a bit cumbersome for something that I imagine would be a pretty simple method to build - something like ExecuteArbitrarySQL...

Just a suggestion.

Hmm, fair point -- we allow you execute scalar stored procs so there's no real reason we couldn't do it for scalar arbitrary SQL.  I'll take a look.

Following on from this...

I have a "Select Procedure" on my design surface that returns a scalar value from a stored proc with a single field name "Result" - it loads an 'entity type' of "StoredProcResult" built with a single field "Result".  To get the value I run the UOW method for the proc, returning a list with a single row, from which I get my value from the Result property on that row.

This appeared to be working well - until I needed to call the stored proc again within the same UOW.  It appears the new value returned is ignored and the value from the first call is never overwritten - despite the round-trip to the database returning a different value. 

Is it because the entity doesn't have a primary key, so once loaded it can never be re-loaded as the UOW thinks its already been done...?

So how can I run a stored proc that returns a scalar value within the current UOW scope?

All LightSpeed entities have IDs.  It doesn't matter whether these are represented as primary keys in the database or not: something has to act as the ID.  So one of your stored proc's result columns -- probably named Id, but you can check the Identity Column Name on StoredProcResult -- is being mapped to the LightSpeed ID.  If that comes back with the same value on a second call, LightSpeed thinks you're referring to the same entity, and therefore returns the entity from the identity map (as I think we've discussed previously).  LightSpeed doesn't know that the entity isn't really an entity and that you're interested only in the Result field.

So there are two possible solutions:

1. Hack your sproc to return a GUID or other unique value as the ID.  Then LightSpeed won't recognise it as an existing entity and will materialise a new entity.  But yuck.

2. Bypass the designer and implement the scalar procedure directly on the strong-typed unit of work class (via a partial class).  Your implementation just needs to call Calculate(ProcedureQuery), e.g.

public int DoWhatever(int input) {
  object scalarResult = this.Calculate(new ProcedureQuery("do_whatever",  // sproc name
    new ProcedureParameter("input", input)  // sproc parameters
  ));
  return (int)scalarResult;
}

Calculate in LightSpeed eventually comes down to an ADO.NET ExecuteScalar() which is what you're looking for.  This will also allow you to get rid of the kludgy StoredProcResult entity type.

Note this assumes you control the sproc and that you can make it return a single column instead of the current presumably multi-column row -- from your description I'm assuming the only reason you return the multi-column row is to enable mapping into an entity but I could be wrong of course!

The third option is of course for me to add support for scalar procedures to the designer.  I'm a bit heads down on other stuff at the moment but I will try to get onto this after Easter.  Note this is just designer pixie dust over option 2 and has the same caveat about the design of the sproc.

A great answer - thanks Ivan.  How come you guys are so helpful - I don't know how you do it with the tens of thousands of customers you must have by now!  :)

I had forgotton about the Calculate method - will use that...  (funny name for a method that runs a query IMHO)

Thanks again!

[quote user="KiwiRod"]Every-now and again the Lightspeed generated TSQL is just not right and I want to execute and return a scalar value from an arbitary scalar command against the database using the current UOW's connection and transaction.[/quote]

Well, I was just about to commit this when my brain suddenly woke up and pointed out that all I was doing was calling IUnitOfWork.PrepareCommand -- there's no real benefit in creating a new API.  The only reason we created a new API for FindBySql was because it had to materialise the results as entities rather than returning an IDataReader.

So what is this IUnitOfWork.PrepareCommand?  Well, it enrols a command in the UOW's connection and transaction.  Which is basically exactly what you're asking for.

With that in mind, here's how to execute arbitrary SQL on the current UOW's connection and transaction:

using (IDbCommand cmd = uow.Context.DataProviderObjectFactory.CreateCommand()) {
  cmd.CommandText = "select count(*) from bibble";
  cmd.CommandType = CommandType.Text;
  // set up any command parameters, etc. etc.

  uow.PrepareCommand();  // this is the crucial line

  int count = (int)(cmd.ExecuteScalar());
}

Of course, this will work for ExecuteReader and ExecuteNonQuery as well.  If you find yourself using this pattern a lot you could easily create an extension method on IUnitOfWork to encapsulate it.

Given this, I'm planning not to commit a new API to support this feature.

Sorry for not thinking of this before.  Apparently it's been Friday all week in my head.

By the way, in case you want to stick with the stored procedure approach for this or other cases, I've added scalar (and nonquery) stored procedure support to the designer (though drag and drop may not be supported for all databases).  This will be included in nightly builds from tonight.

If I can give a suggestion to Mindscape, it would be very nice to have a support for Table-valued-functions. In many of my project, the function cannot be replaced by a stored procedure because the function is used inside the where of another query.

Here is a sample of inline query Table-valued-functions named PersonToDate:

(SELECT PersonAudit.*
FROM PersonAudit
WHERE PersonAudit.ID IN(
    SELECT MAX(PersonAudit.ID) AS ID
    FROM PersonAudit
    WHERE PersonAudit.EffectiveDate <= @DateEffective
    GROUP BY PersonAudit.IndividuID))

The table PersonAudit is an audit to the table Person. Each time a changed is made in the Person table, an entry in added to the PersonAudit table.

The customer want to have a list of person but with the information at a defined date.

After that, we have many many queries that used PersonAudit instead of Person.

Here is an example of used:

SELECT COUNT(PersonToDate.IndividuID) 
FROM PersonToDate(?) 
WHERE COALESCE(PersonToDate.IsInactive, 0) = 0  

This give my the list of Person at a defined date that was inactive.

As I say, this type of query was used in the day to day of the programmer.

Here is a link to a sample in Linq to SQL:

http://msdn.microsoft.com/en-us/library/bb386954.aspx

Here another sample:

http://weblogs.asp.net/zeeshanhirani/archive/2008/05/21/table-valued-functions-in-linq-to-sql.aspx

In these links, you will see samples like that:

var q =
from p in db.ProductsCostingMoreThan(80.50m)
join s in db.Products on p.ProductID equals s.ProductID
select new { p.ProductID, s.UnitPrice };

It's very sad but if I'm not able to use these type of query, 90% of the queries of my projects will not work with LighSpeed.

Have you any solution?

Sorry for my poor knowledges of Linq but I have found a solution to replace functions used all over the places in my projects.

I have found that I can use IQueryable.

I have created functions (in c#) that query in the unitOfWork and return a IQueryable(of T) with all the parameters that I need. After that, I can create another query that use in the From clause the return of the IQueryable function instead of the table directly.

This work very nice. I don't know if the performance an issue but now I can migrate a lot of code from a couple of projects.

I can also extract a couple of business rules in the database that can now be in the code. This will correct an anti-pattern call Soft Code where I had storing business rules in many places other than the code.