SqlParameter of a SqlType

A

Aleksei

SqlParameter converts the value to a CLR type. But this is a problem
while storing OUTPUT parameter values to CLR variable of a type from
System.Data.SqlTypes namespace. For example, the following obviously
does not work

SqlParameter p = new SqlParameter ("@id",SqlDbType.UniqueIdentifier);
SqlGuid id = (SqlGuid) p.Value;

Replacing the second line helps

SqlGuid id;
if (p.Value is DBNull) id=SqlGuid.Null;
else id.Value=(Guid) p.Value;

But this solution implies two conversions and the performance gain
(noted in MSDN library, "System.Data.SqlTypes") could not be achieved.

Using CLR type System.Guid instead of System.Data.SqlTypes.SqlGuid
denies accepting DBNull values.

Is there any way to get SqlType from a SqlParameter directly? Maybe I
have missed something?

Aleksei
 
V

Val Mazur \(MVP\)

Hi,

I would not expect that Guid type should allow to accept NULL values. What
you could do is to set it to Nothing in a case if output parameter is NULL.
In this case you will keep result in one variable, but in a case of NULL it
will be set to Nothing
 
A

Aleksei

Is "Nothing" equivalent to C# 'null'? If so how could a variable of
value type System.Guid be assigned to 'null'?

And Your recipe does not provide the performance gain mentioned in MSDN
library

"The System.Data.SqlTypes namespace provides classes for native data
types within SQL Server. These classes provide a safer, faster
alternative to other data types. Using the classes in this namespace
helps prevent type conversion errors caused in situations where loss of
precision could occur. Because other data types are converted to and
from SqlTypes behind the scenes, explicitly creating and using objects
within this namespace results in faster code as well.

Aleksei Guzev
 
A

Aleksei

:) This problem does not prevent any functionality of my program, of
course. I actually use the "slow method". But I don't like computers
poughing the sands. I am sure computer should not convert data back and
forth just to display it on screen.

Aleksei Guzev
 
V

Val Mazur \(MVP\)

To be sure that it really works slower you could use some profiling tool.
From my personal experience it is not always works as described on a paper.
For example StringBuilder suppose to work faster than *old* concatenation
way, but when you do profiling you actually could see that it is always
slower
 
A

Aleksei

Ok. But what about the following line from MSDN library?

"Using the classes in this namespace (System.Data.SqlTypes)helps
prevent type conversion errors caused in situations where loss of
precision could occur.

I am sure those guys from Microsoft would not waste time on creating
useless classes. So the question is how can one exploit
System.Data.SqlTypes.*?
For example StringBuilder suppose to work faster than *old*
concatenation way, but when you do profiling you actually could see
that it is always slower.
Click to expand...

I hope You do not use StringBuilder to contatenate two strings or to
replace "System.String.Format" method. ;-) Those operations are really
slow with StringBuilder

http://www.codeproject.com/dotnet/strings.asp

Aleksei.
 
G

Gabriel Lozano-Morán

SqlParameter p = new SqlParameter ("@id",SqlDbType.UniqueIdentifier);
SqlGuid id = new SqlGuid(p.Value);

Gabriel Lozano-Morán
 
M

Mary Chipman [MSFT]

I am sure those guys from Microsoft would not waste time on creating
useless classes. So the question is how can one exploit
System.Data.SqlTypes.*?
Click to expand...

The benefit of using SqlTypes on the client instead of CLR types is
that there is no implicit conversion involved, and no loss of
precision for say, decimal data types. See
ms-help://MS.VSCC.2003/MS.MSDNQTR.2004APR.1033/cpref/html/frlrfSystemDataSqlTypes.htm
in the Help file for an example of how they can be used when
retrieving data.

ADO.NET 2.0/ Whidbey will have enhanced support for SqlTypes. For
example, you'll be able to explicitly define a column in a DataTable
as a SqlType. You also get database null semantics with SqlTypes.

--Mary
 
A

Aleksei

Mary said:
The benefit of using SqlTypes on the client instead of CLR types is
that there is no implicit conversion involved, and no loss of
precision for say, decimal data types.
Click to expand...

That is why I recon to use SqlTypes.
See ms-help://MS.VSCC.2003/MS.MSDN­-
TR.2004APR.1033/cpref/html/fr­lrfSystemDataSqlTypes.htm
in the Help file for an example of how they can be used when
retrieving data.
Click to expand...

The example illustrates use of SqlTypes with DataReader. But take a
look a the original post and the topic, please. How can one use
SqlTypes with SqlParameter?

Aleksei Guzev
 
M

Mary Chipman [MSFT]

There's a code snippet in the SqlParameter Class topic in the help
file that illustrates the syntax:
ms-help://MS.VSCC.2003/MS.MSDNQTR.2004APR.1033/cpref/html/frlrfSystemDataSqlClientSqlParameterClassTopic.htm

--Mary
 
A

Aleksei

Mary said:
There's a code snippet in the SqlParameter Class topic
in the help file that illustrates the syntax:
ms-help://MS.VSCC.2003/MS.MSDN­QTR.2004APR.1033/cpref/
html/fr­lrfSystemDataSqlClientSqlParam­eterClassTopic.htm
Click to expand...

Here is the snippet. Where is any SqlType?
Those parameters' Value property will return CLR types but not
SqlTypes. "@CategoryName".Value will return a System.String.
"@SerialNum".Value will return int. But I want to get
System.Data.SqlTypes.SqlString and System.Data.SqlTypes.SqlInt32. How
can I obtain the references? Maybe I should use something else than
SqlParameter to invoke a stored procedure?

[C#]
public void AddSqlParameters()
{
// ...
// create myDataSet and myDataAdapter
// ...

myDataAdapter.SelectCommand.Parameters.Add("@CategoryName",
SqlDbType.VarChar, 80).Value = "toasters";
myDataAdapter.SelectCommand.Parameters.Add("@SerialNum",
SqlDbType.Int).Value = 239;
myDataAdapter.Fill(myDataSet);

}

Aleksei
 
M

Mary Chipman [MSFT]

You can always explicitly declare SqlTypes variables to receive your
output, if that's what you're asking (and I'm again completely
misunderstanding your question). There's an example in the
System.Data.SqlTypes Namespace topic.

--Mary

Mary said:
There's a code snippet in the SqlParameter Class topic
in the help file that illustrates the syntax:
ms-help://MS.VSCC.2003/MS.MSDN­QTR.2004APR.1033/cpref/
html/fr­lrfSystemDataSqlClientSqlParam­eterClassTopic.htm
Click to expand...

Here is the snippet. Where is any SqlType?
Those parameters' Value property will return CLR types but not
SqlTypes. "@CategoryName".Value will return a System.String.
"@SerialNum".Value will return int. But I want to get
System.Data.SqlTypes.SqlString and System.Data.SqlTypes.SqlInt32. How
can I obtain the references? Maybe I should use something else than
SqlParameter to invoke a stored procedure?

[C#]
public void AddSqlParameters()
{
// ...
// create myDataSet and myDataAdapter
// ...

myDataAdapter.SelectCommand.Parameters.Add("@CategoryName",
SqlDbType.VarChar, 80).Value = "toasters";
myDataAdapter.SelectCommand.Parameters.Add("@SerialNum",
SqlDbType.Int).Value = 239;
myDataAdapter.Fill(myDataSet);

}

Aleksei
Click to expand...
 
A

Aleksei

Mary said:
(and I'm again completely
misunderstanding your question).
Click to expand...

I'm afraid that is I who does not understand System.Data. That is why I
am writing the posts ;)

Let us dissect the process. Suppose we have a table:

CREATE TABLE t1 (ID uniqueidentifier, Amount decimal)

The following stored procedure returns amount for given id.

CREATE PROCEDURE get_amount
(
@id uniqueidentifier,
@amount decimal OUTPUT
)
AS
SELECT @amount=Amount
FROM t1
WHERE (ID=@id)

Suppose SqlCommand cmdGetAmount envelopes call to the procedure
get_amount. When we call cmdGetAmount.ExecuteNonQuery (), the
SqlCommand passes @id to the stored procedure and receives @amount of
SQL data type, i.e. SqlDecimal.

Then cmdGetAmount.get_Value checks if @amount returned from get_amount
is NULL. If so the accessor returns DBNull.Value, else converts @amount
to System.Decimal.

I need to get the original SqlDecimal. Someone proposes to check if
returned value is DBNull. If so set the local variable of type
SqlDecimal to SqlDecimal.Null, else convert System.Decimal value back
to SqlDecimal! It seems being queer. I see this proposal being
equivalent to "take this barrel, bear it to that building a mile away,
then bring it back immediately, because it should stay here".

Can I obtain the original value of @amount returned by the stored
procedure bypassing those double checks and conversions?

Is it really worth anything to use SqlTypes?

Aleksei
 
M

Mary Chipman [MSFT]

Is it really worth anything to use SqlTypes?
Click to expand...

In the case of decimal, yes, because of the possible loss of precision
when doing conversions (implicit or otherwise) between CLR types and
SqlTypes.

--Mary
 
A

Aleksei

Thanks for patience.

I have used decimal intentionally, "because of the possible loss of
precision when doing conversions (implicit or otherwise) between CLR
types and SqlTypes" in the case of Decimal. :)

But what about the former question? ("Can I obtain the original value
of @amount returned by the stored procedure..?")

Thanks.
 
M

Mary Chipman [MSFT]

Yes, by retrieving it explicitly into a SqlDecimal variable with the
same scale and precision.

--Mary
 
M

Mary Chipman [MSFT]

Sorry, posted too soon -- You can also call GetSqlDecimal on a
SqlDataReader.
 
A

Aleksei

Mary wrote:

You can also call GetSqlDecimal on a SqlDataReader.

How can I use SqlDataReader to retrieve SqlParameter value ???

How can I obtain the original value
of @amount returned by the stored procedure?

Aleksei.
 
M

Mary Chipman [MSFT]

Um, I'd like to help more, but I've already pointed you to the Help
topics that illustrate the syntax you need to use, yet you keep coming
back repeating the same questions. I'm sorry if you feel your
questions aren't being addressed, but there's not much more I can do
on my end.

--Mary
 
Top