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


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.

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?
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Return to dotConnect for MySQL