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

In this blog is going to explain how to get list of records, single record detail and insert/modify/delete records from MySQL stored procedure using C#.NET code. Blog contains information about MySQL table structure, stored procedure, C#.NET code and Error.

Project contains 3 layers of code
1)Database layer - Explained in this blog
2)Presentation layer - Explained in this blog
3)Business layer - Explained in Part 2 blog

Database Layer Part

MySQL Table - Attachement Type

TABLE tblattachementtype
(
  iattachementtypeid int(10),
  sattachementtypedesc varchar(100)
)

MySQL Stored Procedure - used to insert, modify and delete record. It contains 1 input_output, 2 input and 1 output parameter.

CREATE PROCEDURE `spAttachementTypeIUD`(
iRID INT,
sDML VARCHAR(1),
sDesc VARCHAR(100),
OUT sResult VARCHAR(100)
)
BEGIN
  SET sResult = 'Testing Return Result';
    IF sDML ="i" THEN
        INSERT INTO
            tblattachementtype(sattachementtypedesc)
        VALUES
            (sDesc);
        SET iRID = last_insert_id();
    ELSEIF sDML ="m" THEN
        UPDATE
            tblattachementtype
        SET
            sattachementtypedesc = sDesc
        WHERE
            iattachementtypeid = iRID;
        SET iRID = iRID;
    ELSEIF sDML = "d" THEN
        DELETE FROM tblattachementtype WHERE iattachementtypeid = iRID;
    SET iRID = iRID;
    END IF;
  SET sResult = iRID;

END $$

MySQL Stored Procedure - used to list the all records. It doesn't contain any parameter

CREATE PROCEDURE `spAttachementTypeList`()
BEGIN
  SELECT
    iattachementtypeid rid, sattachementtypedesc rdesc, 0 rtotal
  FROM
    tblattachementtype;
END $$

MySQL Stored Procedure - used to view single record. It contains only one input parameter.

CREATE PROCEDURE `spAttachementTypeView`(iRID INT)
BEGIN
  SELECT
        iattachementtypeid rid, sattachementtypedesc rdesc
    FROM
        tblattachementtype
    WHERE
        iattachementtypeid = iRID;
END $$

Presentation Layer

C#.NET Function - used to display all the record. It calls admin class function, get the resultset, parse and display the result in the client page using xslt program.

private void AttachementTypeList()
    {
        Admin attachementTypeList = new Admin();
        attachementTypeList.AttachementTypeList();
       
        CXml.DisplayList(attachementTypeList.AdminList, "attachementtypelist", xmlAttachementTypeList);
    }

C#.NET Function - used to insert, modify and delete the record.

private void IUD()
    {
        Admin attachementTypeIUD = new Admin();
        attachementTypeIUD.RecordID = iRecordID;
        attachementTypeIUD.DML = sIUD;
        attachementTypeIUD.Desc = txtDesc.Text;
        attachementTypeIUD.AttachementTypeIUD();
        AttachementTypeList();
    }

C#.NET Function - used to view the record.

private void LoadData()
    {
            Admin attachementTypeView = new Admin();
            attachementTypeView.AttachementTypeView(iRecordID);

            if (attachementTypeView.AdminList.Tables[0].Rows.Count > 0)
            {
                litID.Text = attachementTypeView.AdminList.Tables[0].Rows[0]["rid"].ToString();
                txtDesc.Text = attachementTypeView.AdminList.Tables[0].Rows[0]["rdesc"].ToString();
            }
    }

XSLT code - used to parse recordset and display it on the client page

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:param name="divobj" select="Value">
    <xsl:template match="NewDataSet">
        <xsl:for-each select="RecordList">
            <xsl:call-template name="DisplayRecords">
        </xsl:call-template>
    </xsl:for-each>
    <xsl:template name="DisplayRecords">
       <tr>
            <xsl:attribute name="class">
                <xsl:if test="position() mod 2 =1 ">even</xsl:if>
                <xsl:if test="position() mod 2 =0 ">odd</xsl:if>
            </xsl:attribute>
            <td>
                <xsl:value-of select="position()">.
                <xsl:text disable-output-escaping="yes">&nbsp;&nbsp;</xsl:text>
                <a class="lWOn" href="javascript:void(0)" rel="">
                    <xsl:attribute name="onclick">
                        AttachementTypeInfoView(<xsl:value-of select="rid">,"m", "<xsl:value-of select="rdesc">")
                    </xsl:value-of>
                    <img border="0" src="image/edit.gif" />
                </a>
                <xsl:text disable-output-escaping="yes">&nbsp;&nbsp;</xsl:text>
                <a class="lWOn" href="javascript:void(0)" rel="">
                    <xsl:attribute name="onclick">
                        AttachementTypeInfoView(<xsl:value-of select="rid">,"d", "<xsl:value-of select="rdesc">")
                    </xsl:value-of>
                    <img border="0" src="image/delete.png" />
                </a>
           </td>
            <td>
                <xsl:value-of select="rdesc">
            </td>
    </xsl:template>
</xsl:stylesheet>

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