Stored procedure with MySQL
//MySQL Database supports stored procedure
//starting from the version 5.0
//stored procedure is one of the new features
//in MySQL
//Create a class library for MySQL database operation
//database operation
//for connecting to MySQL with dotnet
//you need to download the MySQL driver for dotnet
//from MySQL website,add MySQL.Net.dll to project
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient; //namespace for conecting to MySQL
namespace CustomerDAL_MySQL
{
public class DataAccessSP
{
//function which is used to store the customer name in a table
//parameters connectionstring,refid,name
public long SaveCustomer(string sConnString, string id,string name)
{
try
{
using (MySqlConnection oConn = new MySqlConnection(sConnString))
{
oConn.Open();
MySqlCommand oCommand = oConn.CreateCommand();
oCommand.CommandText = "cust_SaveCustomer";
oCommand.CommandType = System.Data.CommandType.StoredProcedure;
oCommand.Connection = oConn;
MySqlParameter custid = oCommand.Parameters.Add("in_id", MySqlDbType.Int32);
custid.Value = Convert.ToInt64(id);
MySqlParameter custname = oCommand.Parameters.Add("in_custname", MySqlDbType.VarChar, 255);
custname.Value = name;
long res = oCommand.ExecuteNonQuery();
return res;
}
}
catch (MySqlException mx)
{
throw mx;
}
}
}
}
//Create a web application,place a textbox and a button
//In the button click write the following code
protected void btnsave_Click(object sender, EventArgs e)
{
string conString = ConfigurationManager.ConnectionStrings["mainConn"].ConnectionString;
DataAccessSP sp = new DataAccessSP();
sp.SaveCustomer(conString, "-1", txcustname.Text.Trim());
}