How to Pass in a SQL Decimal with Precision and Scale using DAAB?
I'm trying to portmy websites to the DAAB and I have a question: how do you get the output of a SQL Server DECIMAL(8,2) datatype in the AddOutParameter() method? Here's my original ADO.NET code:
SqlParameter pPrice =new SqlParameter("@Price", SqlDbType.Decimal);
pPrice.Precision = 8;
pPrice.Scale = 2;
pPrice.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(pPrice);
Which is passed to a SqlCommand named "myCommand" and executed blah blah blah. How can you do the same thing in the DAAB? The DAAB does not include precision and scale in any of the AddOutParameter() method overrides.
SqlDatabase has a generic AddParameter() method that does contain a precision/scale but it also includes other things that don't matter to me (or don't make sense when using an OUTPUT param) such as DataRowVersion, SourceColumn, and value. It just doesn't seem to fit what I need: an output parameter for which I can define the precision and scale of a SQL Decimal datatype.
Help!
PS: I'm using the 2006 Data Access Application Block.
Here's a "workaround" (if that's the correct way to think of it);
protected void Page_Load(object sender, EventArgs e){
SqlDatabase sqlDatabase = new SqlDatabase("data source=(local);initial catalog=tempdb;Integrated Security = SSPI;");
DbCommand myCommand = sqlDatabase.GetStoredProcCommand("dbo.MyProc"); // CREATE PROC MyProc (@p1 DECIMAL(8,2) OUTPUT) AS SET @p1 = 8.35
// sqlDatabase.AddOutParameter(myCommand, "p1", DbType.Decimal, 10); // this returns only the number 8 and uses NUMERIC(29,0)
SqlParameter pOut = new SqlParameter("@p1", SqlDbType.Decimal);
pOut.Precision = 8;
pOut.Scale = 2;
pOut.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(pOut);
sqlDatabase.ExecuteNonQuery(myCommand);
Response.Write(sqlDatabase.GetParameterValue(myCommand, "@p1").ToString());
}
This is for the DAAB 2.0:
Dim db As Database = DatabaseFactory.CreateDatabase()
dbComm = db.GetStoredProcCommand("dbo.MyProc")
db.AddParameter(dbComm, "@p1", DbType.Decimal, 8, ParameterDirection.Output, True, 8, 2, "p1", DataRowVersion.Current, value)
You feel for it too, eh? That was my first try as well :)
Your code, unfortunately, submits the following to SQL Server:
declare @p1 numeric(29,0)
set @p1=8
exec dbo.MyProc @p1=@p1 output
select @p1
That ain't what we want... Notice how it ignores your precision and scale parameters.
And, as I mentioned in the first entry, it's illogical for me to pass in DataRowVersion, nullability, and "value" for an output parameter. What "value" should I pass in to an output parameter?
Also, how did you calculate the Length? I see that you choose "8" as the length. Why 8? Why not 10 or 19? I honestly don't have an answer; I'm asking because I don't know why you chose 8.
Just to make sure I have it right, here's what I used:
// CREATE PROC MyProc(@p1 DECIMAL(8,2) OUTPUT) AS SET @p1 = 8.35;
SqlDatabase sqlDatabase = new SqlDatabase("data source=(local);initial catalog=tempdb;Integrated Security = SSPI;");
DbCommand myCommand = sqlDatabase.GetStoredProcCommand("dbo.MyProc");
sqlDatabase.AddParameter(myCommand, "@p1", DbType.Decimal, 8, ParameterDirection.Output, true, 8, 2, "p1", DataRowVersion.Current, 666);
sqlDatabase.ExecuteNonQuery(myCommand);
decimal p1 = (Decimal)sqlDatabase.GetParameterValue(myCommand, "p1");
