ExamGuru IT/.Net Guru : SqlCommandBuilder.DeriveParameters against SQL 2005 Beta on microsoft.public.dotnet.framework.adonet

.NETGURU

SqlCommandBuilder.DeriveParameters against SQL 2005 Beta

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.adonet.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Post a new message to this list...

Gad Rosenthal

Hi,
I'm testing work against SQL 2005. I encounter InvalidCastException in
System.Data.SqlClient.SqlCommand.DeriveParameters when attempting to call on
SqlCommandBuilder.DeriveParameters.
Is this familiar? If so, what workaround did you implement?

Thank you, Gad.

I think I will implement something with the use of
sp_procedure_params_rowset.

Reply to this message...

Sushil Chordia (VIP)

Gad, Can you give more information/Code snippet on what exactly you are
trying to do? I am not aware of any such exception with SqlServer 2005,
additional information might help us solve your problem.
-Sushil.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gad Rosenthal" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Gad Rosenthal

I'm miserably failing to sent you email. Let's try from here.
This is an example code (it's a click in a tiny winform app):

        private void button1_Click(object sender, System.EventArgs e)
        {
            if( sprocNametextBox.Text != null && sprocNametextBox.Text.Length > 0
&& connString.Text != null && connString.Text.Length > 0 )
            {
                using (SqlConnection cn = new SqlConnection( connString.Text ))
                using (SqlCommand cmd = new SqlCommand(sprocNametextBox.Text, cn))
                {
                    try
                    {
                        cn.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        SqlCommandBuilder.DeriveParameters( cmd );
                        MessageBox.Show("Passed SqlCommandBuilder.DeriveParameters");
                    }
                    catch( Exception ee )
                    {
                        MessageBox.Show( String.Format( "Exception in
SqlCommandBuilder.DeriveParameters:\n{0}\n Stack:\n{1}", ee.Message,
ee.StackTrace ) );
                    }

                }
            }
        }

Environments in use:
My computer:
Win2K professional build 2195, sp 4.
I have .Net framework versions: v1.0.3705, v1.1.4322, v2.0.40607.
SQL 2005 Beta:
Microsoft SQL Server Management Studio        9.00.852.00
Microsoft Analysis Services Client Tools        2000.090.0852.00
Microsoft Data Access Components (MDAC)        2000.085.1025.00 built by:
(_sqlbld)
Microsoft MSXML                        2.6 3.0 4.0 6.0
Microsoft Internet Explorer                6.0.2800.1106
Microsoft .NET Framework                2.0.40607.42
Operating System                        5.0.2195

Developer studio: 2003 build 7.1.3088(Microsoft Visual C# .NET
69586-335-0000007-18837)
Microsoft Visual C# .NET 69586-335-0000007-18837

SQL server:
From the connection string you can see I was talking to a remote SQL
2005(I have the same results when working against my local install of
SQL 2005), here is its info:
Microsoft SQL Server Management Studio            9.00.852.00
Microsoft Analysis Services Client Tools        2000.090.0852.00
Microsoft Data Access Components (MDAC)            2000.085.1022.00
(srv03_rtm.030324-2048)
Microsoft MSXML                        2.6 3.0 6.0
Microsoft Internet Explorer                6.0.3790.0
Microsoft .NET Framework                2.0.40607.42
Operating System                    5.2.3790

Gad.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Reply to this message...

Sushil Chordia (VIP)

I dont see any problem with your code. I am still unable to reproduce this
problem here. Here is the C# code snippet I used..

<TSQL>
create procedure test (@a nvarchar(20)) as select 1;
</TSQL>
<code>
sqlcommand1.CommandType = CommandType.StoredProcedure;
sqlcommand1.CommandText = "test";
SqlCommandBuilder.DeriveParameters(sqlcommand1);
SqlParameterCollection sqlparametercollection1 = sqlcommand1.Parameters; //
new SqlParameterCollection{@RETURN_VALUE, @a}
</code>
HTH,
Sushil.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gad Rosenthal" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Gad Rosenthal

Further investigation showed that the problem lies in the use of a
user-defined-type.
Try the following:
<TSQL>
drop procedure test
go
sp_droptype @typename = myInt
go
sp_addtype @typename = myInt,
@phystype = int
go
create procedure test (@a myInt) as select 1;
go
</TSQL>

Now it fails.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Reply to this message...

Sushil Chordia (VIP)

Gad, I can reproduce the Exception with the alias datatype that you mention
below.
On a different note: Just to make sure you know, user-defined-type is
different from alias datatype mentioned below. User defined types in
SqlServer 2005 is a SQL-CLR functionality which allows storing Mananged(CLR)
object in Yukon.
Sorry for the inconvenience that this exception has caused.
--
Thanks,
Sushil Chordia.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gad Rosenthal" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Gad Rosenthal

Hi,
1. You are correct about the misuse of terms (alias vs. user defined
datatype).
2. Will you open a bug on this matter? If so, could you please publish
it.
3. I encountered the problem while using the data access application
block. I had worked around the issue through an implementation that
works against sp_procedure_params_rowset stored-procedure. If you want I
can send you the code, so that it will be available to others.

Gad.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Reply to this message...

Sushil Chordia (VIP)

Gad, I will escalate this problem to our appropriate component team. If you
are registered via MSDN you can report the bug via LadyBug at
http://lab.msdn.microsoft.com/productfeedback/. This would help you track
the bug.

I would also be interested to see the workaround that you used for your
problem.
--
Thanks for your patience,
Sushil Chordia.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gad Rosenthal" <Click here to reveal e-mail address> wrote in message
news:OrlP%Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Gad Rosenthal

1. My suggestion for workaround.
I'm referring to the following code in function DiscoverSpParameterSet() in Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache:

Original:
....

connection.Open();
SqlCommandBuilder.DeriveParameters(cmd);
connection.Close();

if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}

SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

cmd.Parameters.CopyTo(discoveredParameters, 0);
....

New code:
....
try
{
using( DataSet ds = SqlHelper.ExecuteDataset( connection, CommandType.Text, String.Concat( @"exec sp_procedure_params_rowset @procedure_name = N'", spName, "'" ) ) )
{
if( ds != null && ds.Tables != null && ds.Tables.Count > 0 )
{
DataTable spParams = ds.Tables[0];
if( spParams != null && spParams.Rows != null && spParams.Rows.Count > 0 )
{
cmd.Parameters.Clear();
Hashtable paramsOfSp = new Hashtable();
short i = 0;
byte hasDefault = 0;
bool hd = true;
foreach( DataRow dr in spParams.Rows )
{
hasDefault = ( byte )DbNullToDefault( dr["PARAMETER_HASDEFAULT"], ( byte )0 );
if( hasDefault == 0 )
hd = false;
paramsOfSp.Add(DbNullToDefault( dr["ORDINAL_POSITION"], i),
CreateParameterNoValue(
( String )DbNullToDefault( dr["PARAMETER_NAME"], String.Empty ),
( short )DbNullToDefault( dr["DATA_TYPE"], ( short )4 ),
( short )DbNullToDefault( dr["PARAMETER_TYPE"], ( short )1 ),
( bool )DbNullToDefault( dr["IS_NULLABLE"], false ),
( int )DbNullToDefault( dr["CHARACTER_MAXIMUM_LENGTH"], ( int )4 ),
( short )DbNullToDefault( dr["NUMERIC_PRECISION"], ( short )4 ),
( short )DbNullToDefault( dr["NUMERIC_SCALE"], ( short )0 ),
String.Empty,
hd,
DbNullToDefault( dr["PARAMETER_DEFAULT"], null ),
( String )DbNullToDefault( dr["TYPE_NAME"], String.Empty )) );
i++;
}
for( short j = 0; j< i; j++ )
{
cmd.Parameters.Add( paramsOfSp[j] );
}
}
else
return null;
}
else
return null;
}
}
catch( Exception ee )
{
System.Diagnostics.Debug.WriteLine( String.Format("{0}\n{1}", ee.Message, ee.StackTrace ) );
return null;
}
....

This relies on:

public static object DbNullToDefault(object value, object defualtReturn)
{
if((value == null) || DBNull.Value.Equals(value))
return defualtReturn;
return value;
}

Couple of remarks:
1. It may be that it won't compile! Appologies, but this is extracted from code that was modified from Microsoft.ApplicationBlocks.Data.
2. It totally relies on the structure of sp_procedure_params_rowset.
3. Any corrections-suggestions are very welcomed.

2. I opened a bug.

Gad.

"Sushil Chordia" <Click here to reveal e-mail address> wrote in message news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Sushil Chordia (VIP)

Gad, The code looks good. One comment: It would be good to use DataReader in this case, as you dont want to store the data in the cache.
--
HTH,
Sushil Chordia.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gad Rosenthal" <Click here to reveal e-mail address> wrote in message news:Click here to reveal e-mail address...
1. My suggestion for workaround.
I'm referring to the following code in function DiscoverSpParameterSet() in Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache:

Original:
...

connection.Open();
SqlCommandBuilder.DeriveParameters(cmd);
connection.Close();

if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}

SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

cmd.Parameters.CopyTo(discoveredParameters, 0);
...

New code:
...
try
{
using( DataSet ds = SqlHelper.ExecuteDataset( connection, CommandType.Text, String.Concat( @"exec sp_procedure_params_rowset @procedure_name = N'", spName, "'" ) ) )
{
if( ds != null && ds.Tables != null && ds.Tables.Count > 0 )
{
DataTable spParams = ds.Tables[0];
if( spParams != null && spParams.Rows != null && spParams.Rows.Count > 0 )
{
cmd.Parameters.Clear();
Hashtable paramsOfSp = new Hashtable();
short i = 0;
byte hasDefault = 0;
bool hd = true;
foreach( DataRow dr in spParams.Rows )
{
hasDefault = ( byte )DbNullToDefault( dr["PARAMETER_HASDEFAULT"], ( byte )0 );
if( hasDefault == 0 )
hd = false;
paramsOfSp.Add(DbNullToDefault( dr["ORDINAL_POSITION"], i),
CreateParameterNoValue(
( String )DbNullToDefault( dr["PARAMETER_NAME"], String.Empty ),
( short )DbNullToDefault( dr["DATA_TYPE"], ( short )4 ),
( short )DbNullToDefault( dr["PARAMETER_TYPE"], ( short )1 ),
( bool )DbNullToDefault( dr["IS_NULLABLE"], false ),
( int )DbNullToDefault( dr["CHARACTER_MAXIMUM_LENGTH"], ( int )4 ),
( short )DbNullToDefault( dr["NUMERIC_PRECISION"], ( short )4 ),
( short )DbNullToDefault( dr["NUMERIC_SCALE"], ( short )0 ),
String.Empty,
hd,
DbNullToDefault( dr["PARAMETER_DEFAULT"], null ),
( String )DbNullToDefault( dr["TYPE_NAME"], String.Empty )) );
i++;
}
for( short j = 0; j< i; j++ )
{
cmd.Parameters.Add( paramsOfSp[j] );
}
}
else
return null;
}
else
return null;
}
}
catch( Exception ee )
{
System.Diagnostics.Debug.WriteLine( String.Format("{0}\n{1}", ee.Message, ee.StackTrace ) );
return null;
}
...

This relies on:

public static object DbNullToDefault(object value, object defualtReturn)
{
if((value == null) || DBNull.Value.Equals(value))
return defualtReturn;
return value;
}

Couple of remarks:
1. It may be that it won't compile! Appologies, but this is extracted from code that was modified from Microsoft.ApplicationBlocks.Data.
2. It totally relies on the structure of sp_procedure_params_rowset.
3. Any corrections-suggestions are very welcomed.

2. I opened a bug.

Gad.

"Sushil Chordia" <Click here to reveal e-mail address> wrote in message news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Gad Rosenthal

A minute before taking your advise - There are times where I don't take the DataReader path because it holds exclusively on to the connection object. When working on a server application it seems bad manners to do so. What do you think?
Remark - One can argue that since we are filling cache of a closed list (no more 200 stored-procedures) this is a "one time" thing.
Thank you, Gad.
"Sushil Chordia" <Click here to reveal e-mail address> wrote in message news:%Click here to reveal e-mail address...
Gad, The code looks good. One comment: It would be good to use DataReader in this case, as you dont want to store the data in the cache.
--
HTH,
Sushil Chordia.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gad Rosenthal" <Click here to reveal e-mail address> wrote in message news:Click here to reveal e-mail address...
1. My suggestion for workaround.
I'm referring to the following code in function DiscoverSpParameterSet() in Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache:

Original:
...

connection.Open();
SqlCommandBuilder.DeriveParameters(cmd);
connection.Close();

if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}

SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

cmd.Parameters.CopyTo(discoveredParameters, 0);
...

New code:
...
try
{
using( DataSet ds = SqlHelper.ExecuteDataset( connection, CommandType.Text, String.Concat( @"exec sp_procedure_params_rowset @procedure_name = N'", spName, "'" ) ) )
{
if( ds != null && ds.Tables != null && ds.Tables.Count > 0 )
{
DataTable spParams = ds.Tables[0];
if( spParams != null && spParams.Rows != null && spParams.Rows.Count > 0 )
{
cmd.Parameters.Clear();
Hashtable paramsOfSp = new Hashtable();
short i = 0;
byte hasDefault = 0;
bool hd = true;
foreach( DataRow dr in spParams.Rows )
{
hasDefault = ( byte )DbNullToDefault( dr["PARAMETER_HASDEFAULT"], ( byte )0 );
if( hasDefault == 0 )
hd = false;
paramsOfSp.Add(DbNullToDefault( dr["ORDINAL_POSITION"], i),
CreateParameterNoValue(
( String )DbNullToDefault( dr["PARAMETER_NAME"], String.Empty ),
( short )DbNullToDefault( dr["DATA_TYPE"], ( short )4 ),
( short )DbNullToDefault( dr["PARAMETER_TYPE"], ( short )1 ),
( bool )DbNullToDefault( dr["IS_NULLABLE"], false ),
( int )DbNullToDefault( dr["CHARACTER_MAXIMUM_LENGTH"], ( int )4 ),
( short )DbNullToDefault( dr["NUMERIC_PRECISION"], ( short )4 ),
( short )DbNullToDefault( dr["NUMERIC_SCALE"], ( short )0 ),
String.Empty,
hd,
DbNullToDefault( dr["PARAMETER_DEFAULT"], null ),
( String )DbNullToDefault( dr["TYPE_NAME"], String.Empty )) );
i++;
}
for( short j = 0; j< i; j++ )
{
cmd.Parameters.Add( paramsOfSp[j] );
}
}
else
return null;
}
else
return null;
}
}
catch( Exception ee )
{
System.Diagnostics.Debug.WriteLine( String.Format("{0}\n{1}", ee.Message, ee.StackTrace ) );
return null;
}
...

This relies on:

public static object DbNullToDefault(object value, object defualtReturn)
{
if((value == null) || DBNull.Value.Equals(value))
return defualtReturn;
return value;
}

Couple of remarks:
1. It may be that it won't compile! Appologies, but this is extracted from code that was modified from Microsoft.ApplicationBlocks.Data.
2. It totally relies on the structure of sp_procedure_params_rowset.
3. Any corrections-suggestions are very welcomed.

2. I opened a bug.

Gad.

"Sushil Chordia" <Click here to reveal e-mail address> wrote in message news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

	System.Collections.Hashtable
	System.Data.CommandType
	System.Data.DataRow
	System.Data.DataSet
	System.Data.DataTable
	System.Data.SqlClient.SqlCommand
	System.Data.SqlClient.SqlCommandBuilder
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlParameter
	System.Data.SqlClient.SqlParameterCollection
	System.DBNull
	System.Diagnostics.Debug
	System.EventArgs
	System.InvalidCastException
	System.String
	System.Windows.Forms.MessageBox

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification