Now, the next step when using stored procedures is of course adding parameters. It turned out to be easier than I thought although there are some things to remember. I'll show you an example from AdMentor PRO development - listing banners belonging to a special advertiser:
Now what's so hard with that. Nothing exept that you should note that no @ is prefixed the parameter name.
Now calling from code:
string strSQLorSP = "a_ListBannersForAdvertiser";
MySqlConnection oConn = new MySqlConnection( "Database=admentor4;Data Source=localhost;User id=root;Password=stefan" );
try
{
MySqlCommand oCommand = oConn.CreateCommand();
oCommand.CommandType = System.Data.CommandType.StoredProcedure;
oCommand.CommandText = strSQLorSP;
oCommand.Parameters.Add( "in_advid", MySql.Data.MySqlClient.MySqlDbType.Int32 ).Value = 1;
DataSet oDataSet = new DataSet();
MySqlDataAdapter oAdapter = new MySqlDataAdapter();
oAdapter.SelectCommand = oCommand;
oConn.Close();
oAdapter.Fill(oDataSet);
dataGrid1.DataSource = oDataSet.Tables[0];
}
catch(MySqlException ex)
{
}
Please note that the driver seems to bind parameters by their name and not by ordinal so it's important the oCommand.Parameters.Add( "in_advid", ... matches the stored procedure parameter name.
Also as you can see, in this example I create the parameter and set it's value to 1 in the same line.
