Executing MySQL Stored Procedure using C#.NET - Part 2

In this blog contains business layer code and error description.

Business Layer

Class Admin - used to call Stored Procedure class, get result and pass to the client page

class Admin
{
    #region "Member Variables"
    private int iRecordID;
    private string sDesc;
    private string sDML;
    private string sResult;
    private DataSet dsList = new DataSet();
    #endregion "Member Variables"

    #region "Properties"
    public int RecordID
    {
        get { return iRecordID; }
        set { iRecordID = value; }
    }
    public string Desc
    {
        get { return sDesc; }
        set { sDesc = value; }
    }
    public string DML
    {
        get { return sDML; }
        set { sDML = value; }
    }
    public DataSet AdminList
    {
        get { return dsList; }
        set { dsList = value; }
    }
    public string Result
    {
        get { return sResult; }
        set { sResult = value; }
    }
    #endregion "Properties"

    #region "Public Functions"
    public Admin() { }

    #region "Attachement Type List"
    public void AttachementTypeList()
    {
        StoredProcedure spList = new StoredProcedure("spAttachementTypeList");
        AdminList = spList.List();
    }
    #endregion "Attachement Type List"

    #region "Attachement Type View"
    public void AttachementTypeView(int iRecordID)
    {
        StoredProcedure spList = new StoredProcedure("spAttachementTypeView");
        spList.AddIn("@iRID", iRecordID);
        AdminList = spList.List();
    }
    #endregion "Attachement Type View"

    #region "Attachement Type IUD"
    public void AttachementTypeIUD()
    {
        StoredProcedure spIUD = new StoredProcedure("spAttachementTypeIUD");
        spIUD.AddIn("@iRID", iRecordID);
        spIUD.AddIn("@sDML", DML);
        spIUD.AddIn("@sDesc", Desc);
        spIUD.AddOut("@sResult", "VarChar");
        spIUD.IUD();
        Result = (string)spIUD.GetOut("@sResult");
    }
    #endregion "Attachement Type View"
    #endregion "Public Functions"
}

Class Stored Procedure - used to list, insert, update and delete record(s)

class StoredProcedure
{
    public string CONNECTION_STRING =  "";
   
    private string sProcedureName;
    private DataSet dsList = new DataSet();
    private MySqlConnection sqlConn = new MySqlConnection();
    private MySqlCommand sqlCmd = new MySqlCommand();
    private MySqlDataAdapter sqlDAList = new MySqlDataAdapter();
   
    public string ProcedureName
    {
        get { return sProcedureName; }
        set { sProcedureName = value; }
    }
   
    public DataSet RecordList
    {
        get { return dsList; }
        set { dsList = value; }
    }

    public StoredProcedure(string sProcedureName)
    {
        sqlConn = new MySqlConnection(CONNECTION_STRING);
        ProcedureName = sProcedureName;
        sqlCmd.Connection = sqlConn;
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.CommandText = sProcedureName;
    }

    public void AddIn(string sParameter, Object oValue)
    {
        sqlCmd.Parameters.AddWithValue(sParameter, oValue);
        sqlCmd.Parameters[sParameter].Direction = ParameterDirection.Input;
    }

    public void AddInOut(string sParameter, Object oValue)
    {
        sqlCmd.Parameters.AddWithValue(sParameter, oValue);
        sqlCmd.Parameters[sParameter].Direction = ParameterDirection.InputOutput;
    }

    public void AddOut(string sParameter)
    {
        sqlCmd.Parameters.Add(new MySqlParameter(sParameter, MySqlDbType.VarChar, 1000));
        sqlCmd.Parameters[sParameter].Direction = ParameterDirection.Output;
    }

    public DataSet List()
    {
        try
        {
            sqlConn.Open();
            sqlDAList.SelectCommand = sqlCmd;
            sqlDAList.Fill(RecordList, "RecordList");
            sqlCmd.Dispose();
        }
        catch (Exception excep)
        {
            throw new Exception(excep.Message);
        }
        finally
        {
            sqlConn.Close();
        }
        return RecordList;
    }

    public void IUD()
    {
        try
        {
            sqlConn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCmd.Dispose();
        }
        catch (Exception excep)
        {
            throw new Exception(excep.Message);
        }
        finally
        {
            sqlConn.Close();
        }
    }

    public Object GetOut(string sParameter)
    {
        if (sqlCmd.Parameters[sParameter].Value != null)
            return sqlCmd.Parameters[sParameter].Value;
        return null;
    }
}

Class CXml - used to parse and display the xml content.

class CXml
{
    public static void DisplayList(DataSet dsList, string sXslName, Xml xmlObject)
    {
        string sDivObj = "adm_att";
        if (System.Web.HttpContext.Current.Request.QueryString["obj"] != null)
            sDivObj = System.Web.HttpContext.Current.Request.QueryString["obj"];

        XslTransform xslTrans = new XslTransform();
        XsltArgumentList xslArgs = new XsltArgumentList();
        xslArgs.AddParam("divobj", "", sDivObj);
        xslTrans.Load(System.Web.HttpContext.Current.Server.MapPath("xsl/" + sXslName + ".xsl"));
        XmlDataDocument xmlDoc = new XmlDataDocument(dsList);
        xmlObject.Document = xmlDoc;
        xmlObject.Transform = xslTrans;
        xmlObject.TransformArgumentList = xslArgs;
    }
}

Error

1.SELECT command denied to user 'daynight'@'localhost' for table 'proc'...

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Exception: SELECT command denied to user 'daynight'@'localhost' for table 'proc'

Solution - Rewrite the code like below
  sqlConn = new MySqlConnection(CONNECTION_STRING);
  sqlCmd.Connection = sqlConn;
  //sqlCmd.CommandType = CommandType.StoredProcedure;
  sqlCmd.CommandText = "CALL spAttachementTypeList";
  sqlConn.Open();
  //sqlCmd.ExecuteNonQuery();
  sqlDAList.SelectCommand = sqlCmd;
  sqlDAList.Fill(dsList, "AdminList");
  sqlCmd.Dispose();
OR
  Give Assiged Privileges to the user.

2.Access denied for user 'daynight'@'%' to database 'day_night'

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: Access denied for user 'daynight'@'%' to database 'day_night'

  Give Assiged Privileges to the user.

3. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'spAttachementTypeList()' at line 1

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'spAttachementTypeList()' at line 1

  USE CALL spAttachementTypeList();

4. SELECT command denied to user 'daynight'@'localhost' for table 'proc'
CREATE DEFINER=`day_night`@`localhost` PROCEDURE `spAttachementTypeIUD`(

  Give Assiged Privileges to the user.

Executing MYSQL Stored Procedure using C#.NET - Part 1