Page 1 of 1

Stored Procedure Parameter Types and Performance Question

Posted: Sun 03 Jun 2007 17:24
by JafaRykos
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


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:

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);


oCommand.Parameters.Add(new MySqlParameter("pVotes", MySqlType.Int, 0) );
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.


Posted: Mon 04 Jun 2007 09:02
by Alexey
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?