How does the dataset designer determine the return type of a scalar query?

Question

I am attempting to add a scalar query to a dataset. The query is pretty straight forward, it's just adding up some decimal values in a few columns and returning them. I am 100% confident that only one row and one column is returned, and that it is of decimal type (SQL money type). The problem is that for some reason, the generated method (in the .designer.cs code file) is returning a value of type object, when it should be decimal. What's strange is that there's another scalar query that has the exact same SQL but is returning decimal like it should.

How does the dataset designer determine the data type, and how can I tell it to return decimal?

Answer 1

Instead of using a scalar stored procedure, use a scalar function instead. The dataset designer will correctly detect the data type for the scalar function. You only need to use a scalar stored procedure if you are making changes to data during the query. A scalar function is read-only. You can also very conveniently drag the function into your dataset as a query instead of having to go through the wizard.

If you insist on using a stored procedure, or a regular query, you can always cast your result like so (in VB)...

    Dim ta As New DataSet1TableAdapters.QueriesTableAdapter
    Dim result As Decimal = DirectCast(ta.StoredProcedure1, Decimal)

or With Option Infer On

    Dim resultInfer = DirectCast(ta.StoredProcedure1, Decimal)

Answer 2

First of all fill the schema of the dataset.tables("") to "schematype.source" using dataadapters. Ex: sqladp.fillschema(ds.tables(0),schematype.source)

And then fill the dataset sqladp.fill(ds.tables(0))

Now i think it should return datatype of source table.

Is that u were looking for?