DROP PROCEDURE IF EXISTS SP_OUTLETCREATION;
CREATE PROCEDURE SP_OUTLETCREATION(
IN SP_OutletId smallint,
IN SP_OutletName varchar(20),
out SP_MSG varchar(50)
)
Begin
IF NOT EXISTS(select 1 from outletcreation where OutLetName=SP_OutletName) then
if SP_OutletId=0 then
INSERT INTO OUTLETCREATION (OutletName,CreatedDate,ModifiedDate) values(SP_OutletName,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP());
else
UPDATE OUTLETCREATION SET outletName=SP_OutletName,ModifiedDate=CURDATE() where OutletId=SP_OutletId;
end if;
else
set SP_MSG="OutletName is Already Exist!!!";
End if;
End;
C#:
protected void btnCreateOutlet_Click(object sender, EventArgs e)
{
try
{
using (MySqlConnection ObjCon = new MySqlConnection(new CommonClass().GetConnectionString()))
{
ObjCon.Open();
using (MySqlCommand ObjCmd = new MySqlCommand("SP_OUTLETCREATION", ObjCon))
{
ObjCmd.CommandType = CommandType.StoredProcedure;
ObjCmd.Parameters.Add("SP_OutletId", MySqlDbType.Int64).Value = 0;
ObjCmd.Parameters.Add("SP_OutletName", MySqlDbType.VarChar, 20).Value = txtOutletName.Text.Trim();
MySqlParameter parm2 = new MySqlParameter("SP_MSG", MySqlDbType.VarChar);
parm2.Size = 50;
parm2.Direction = ParameterDirection.Output;
ObjCmd.Parameters.Add(parm2);
ObjCmd.ExecuteNonQuery();
lblMsg.Text = ObjCmd.Parameters["SP_MSG"].Value.ToString();
}
Bind();
}
}
catch (Exception ex)
{
lblMsg.Text=ex.Message;
}
}
No comments:
Post a Comment