Reading from Stored Procedure

Apr 19, 2012 at 9:17 PM
Edited Apr 19, 2012 at 10:13 PM

Hi,

 

First of all - great work !
The generator fits my current dev strategy completly.

But I can't get a stored procedure to execute and read from it.

Inside the procedure - i'm just calling a dummy Select Statement.

 

var sp1 = DBExecQuery.Exec("GetPerson")
			.WithParamValue("Id", 1);
sp1.Owner = "dbo";

db.ExecuteRead(sp1, delegate(DbDataReader reader) {
 while (reader.Read())
 {...}
});

The SqlCommand (with type stored procedure) is generated with the CommandText "Exec [dbo].[GetPerson]" But an Exception is thrown "Could not find Stored Procedure '' "

Am I doing something wrong?
Could someone please provide a short sample how to use a stored procedure correctly?

BR, Van

Apr 20, 2012 at 10:15 AM

Hey Vanongart

Glad you like it, and it looks like your code is good from a DynaSQL perspective.

As a test you might want to use DbCommand to definitely exclude it, as below.

DbCommand cmd = db.CreateCommand("dbo.GetPerson", CommandType.StoredProcedure);

DbParameter param = cmd.CreateParameter();
param.ParameterName = "@Id";
param.DbType = DbType.Int32;
param.Value = 1;

cmd.Parameters.Add(cmd);

db.ExecuteRead(cmd, delegate(DbDataReader reader)

{

    //...

});

 

Apart from that - I'd be looking at the connections, stored procedure owner on the Db, and permissions.

Apr 20, 2012 at 11:02 AM

Hey,

Thanks for the hint - executing the stored procedure with a DbCommand works fine.

I took a look at the resulting SqlCommand for both scenarios:

When using the DbExecQuery - the CommandText is like "EXEC [dbo].[GetPerson] @Id"

When using DbCommad its just "dbo.GetPerson"

 

The Parameters added to the SqlCommand are in both cases the same and correct.

Could it be that the DbExecQuery (and DbQuery.Exec) are misbuilding their commands? (Bug?)
Because the syntax from DbExec query looks a bit like the command you type in Management Studio to run a SP "EXEC [dbo].[GetPerson] @Id = 2".

BTW i'm on SQL Server 2008.

Apr 24, 2012 at 8:18 AM

Vanongart - can you check the source that you are using. There were initial issues with stored procedure execution in early 2011, but these should have been fixed in release 93582 (http://dynasql.codeplex.com/SourceControl/changeset/changes/93582), and the current version, 95687, should be ok too http://dynasql.codeplex.com/SourceControl/changeset/changes/95687).

I have replicated a similar query against Server 2008 and it worked for me - but, I never exclude the possibilitiy of a bug.

 

Apr 24, 2012 at 11:06 AM

Hi,

Now I'm feeling stupid - sorry but I was using an old version. Just had hit the download btn - not browsing the source code...

But another question rised - when adding multiple paramters to the stored procedure, there seem to be colons missing after each paramter.

(or i'm using it wrong?)

var query = DBQuery.Exec("GetFilteredEntryPersons")
			        .WithParamValue("Year", 2012)
				.WithParamValue("WeekOfYear", 14)
				.WithParamValue("ProfitCenterCode", "A73");

Gets built as "EXEC [GetFilteredEntryPersons] @Year @WeekOfYear @ProfitCenterCode"

After each paramter there should be a colon, when adding it manually everything works fine.

Besides that, could it be possible that you add an ctor with Owner and SpProcName to the DbQuery.Exec / DbExecQuery?

Apr 28, 2012 at 6:05 PM

Don't - you were correct there was a bug.

Fixed and checked in along with a unit test. Should be OK.

            DBConst begin = DBConst.DateTime(1900, 1, 1);
            DBConst end = DBConst.DateTime(2012, 1, 1);

            //Execute a stored procedure with multiple parameters
            DBQuery exec = DBQuery.Exec("Employee Sales by Country")
                                  .WithParamValue("Beginning_Date", begin)
                                  .WithParamValue("Ending_Date", end);
            decimal grandtotal = 0;
            db.ExecuteRead(exec, reader =>
             {
                 int amountindex = reader.GetOrdinal("SaleAmount");

                 while (reader.Read())
                 {
                     decimal amount = reader.GetDecimal(amountindex);
                     grandtotal += amount;
                 }

             });

             Console.WriteLine("Grand Total:{0}", grandtotal);