PetaPoco stored procedure error “Incorrect syntax

问题:

I'm using C# with TSQL and SQL Server 2005

I'm trying to use PetaPoco to return a dataset as a list of objects. this is the code I'm using just now

var s = PetaPoco.Sql.Builder.Append("USE [BI] EXEC [dbo].[TestProcedure2];");
            var result =  db.Query<dynamic>(s);

var result2 = db.Query<dynamic>("USE [BI] EXEC [dbo].[TestProcedure2];");

I think the error message is a generic sql error for when petaPoco fails.

At first I was using a stored procedure with paramaters and the @ character was causing a problem, once that was fixed with @@ i started getting this error so I made a stored procedure with a simple select statement. The procedure executes completely fine in Management Studio.

Using PetaPoco with select statements is fine and the data is mapped both to a dynamic or an object model completely fine. I created a garbage SQL string and it returned the same error which is where I'm getting the generic error idea from.

This is the select I'm using which works fine

var dynTest =
                db.Query<dynamic>(
                   "SELECT TOP 10 * FROM [BI].[dbo].[Managers] ORDER  BY [ConsecutiveDays] desc");

回答1:

Its trying to append the select clause in front of it.
If you put a ";" at the start of your query it won't try to append it.

回答2:

PetaPoco assumes that you want to perform a SELECT and will infer one if you don't include one. To avoid doing the automatic SELECT you should use:

db.EnableAutoSelect = false;

Prior to your query.