Stored Procedure Parameter Types and Performance Question

Stored Procedure Parameter Types and Performance Question

Postby JafaRykos » Sun 03 Jun 2007 17:24

I have two questions.

1) When using a stored procedure, using a command object and parameters like the following:

(pVotes is an unsigned mediumint in MySql)

Code: Select all
oCommand.Parameters.Add("pVotes", 0);


This will fail, because internally the Parameter is being stored as a VarChar, not an unsigned int, so the value that 0 becomes is 'null'. You cant store a 'null' in an int column in the database. So, the solution is to either do:

Code: Select all
oCommand.Parameters.Add("pVotes", "0");


And let the database convert from the VarChar to the proper unsigned mediumint

or

Code: Select all
MySqlParameter oMySqlParameter = new MySqlParameter("pVotes", MySqlType.Int);
oMySqlParameter.Value = 0;


This creates another problem, as the MySqlType enumeration does not contain unsigned enumerations, and is missing the MediumInt enumeration as well.

This is addressed on these forums in this post:
http://www.crlab.com/forums/viewtopic.php?t=9104

Desired Solution:
I would like to simply have another constructor overloaded for MySqlParameter that accepts the "parameterName", "dbType", and "value"

So that I can do the following:

Code: Select all
oCommand.Parameters.Add("pVotes", MySqlType.Int, 0);

or

oCommand.Parameters.Add(new MySqlParameter("pVotes", MySqlType.Int, 0) );


2)
What are the performance differences between letting the Server handle the type conversions versus passing the correct data types from the c# codebehind?

I'm assuming giving the server the correct datatypes is the desired method.

Thanks!
JafaRykos
 
Posts: 2
Joined: Sun 03 Jun 2007 17:04

Postby Alexey » Mon 04 Jun 2007 09:02

1) Use the following constructor to create parameter:
Code: Select all
public MySqlParameter(
   string parameterName,
   MySqlType dbType,
   int size,
   ParameterDirection direction,
   bool isNullable,
   byte precision,
   byte scale,
   string sourceColumn,
   DataRowVersion sourceVersion,
   object value
);

2) What is the intend of letting the Server handle the type conversions?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL