This will be a really simple guide for you to get stored procedures going - the steps of installing and getting the MySQL ADO.NET connector is not described - that might be another article. Instead, just how to create stored procedures - and how to call them from .NET (ASP.NET, Windows Forms).
Creating a stored procedure
While this should be really simple I got into trouble which tool me about half an hour to solve. I want to spare you that time so:
if you are using MySQL Administrator gui you might have seen there is a tab for Stored Procedures. Great! A button with Create Stored Proc.
You first enter the name, lets say ListTest:
And you get to the sp editor.Ok, lets create a really simple stored procedure - no parameters or anythhing and just a simple select:
So easy what could go wrong? We click on OK and:
I couldn't for my life understand what was wrong. After searching around on the Internet and the MySQL support forum I almost started thinking - well, maybe returning resultsets aren't supported in MySQL SP:s, maybe it's just for update/insert you should use SP:s. Finally I found it: missed the the dreaded ';' in the end
And it's there :)
Calling it from .NET code
Now, lets look at calling it from your ADO.NET code:
MySqlConnection oConn = new MySqlConnection( "Database=admentornet;Data Source=localhost;" +
"User id=root;Password=stefan" );
try
{
MySqlCommand oCommand = oConn.CreateCommand();
oCommand.CommandType = System.Data.CommandType.StoredProcedure;
oCommand.CommandText = "ListTest";
DataSet oDataSet = new DataSet();
MySqlDataAdapter oAdapter = new MySqlDataAdapter();
oAdapter.SelectCommand = oCommand;
oConn.Close();
oAdapter.Fill(oDataSet);
dataGrid1.DataSource = oDataSet.Tables[0];
}
catch(MySqlException ex)
{
}
The example is a Windows Forms application where I fill a datagrid - but the same principle is of course used for ASP.NET.
Now, while I said I shouldn't, here are some links for things you need:
MySQL ADO driver
Download, install, find MySQL.Data.Dll and reference it from your application
MySQL Administration
I can't say I like the tool it more or less does it job, but better than a black blinking command prompt
MySQL Administration
I can't say I like the tool it more or less does it job, but better than a black blinking command prompt
Query browser
Same goes as above
