Implementing CRUD Operation in MySql Using Entity Framework

Introduction

Before reading this article, please go through the following article.

Entity Framework CRUD Operations Using Stored Procedure

In the last article, I showed how to implement CRUD operations using Entity Framework and a SQL database. Now I want to show you how to implement the same using a MySQL database.

The creation of tables and Stored Procedures are left as it is almost the same as we did in SQL.

Problem faced

Before doing this, I would like to mention a few problems I encountered.

While mapping Stored Procedures from the model, I could not assign the parameters as we did in SQL Server.

Generally, in SQL, while assigning parameters, we use the following:

var ietsParameterEmpAddress = new MySqlParameter("@EmpAddress", txtAddress.Text); // We use @ here since we assign parameters inside the Stored Procedure with the @ symbol. Similarly, I tried the same by replacing the @ with _ as we use the _ symbol for assigning variables in MySQL Stored Procedures.

Then I called the Stored Procedure as follows as we did in our earlier article:

Entities.ExecuteStoreCommand("uspInsertUsers _UserName,_Password,_FirstName,_LastName)", userName,password,FirstName,LastName);

After executing and trying to execute the command, I got an 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 "uspInsertUsers "Dorababu," "sae", "Dorababu", "M" at the line.

For the first one as explained here Entity, we can insert the data without mapping Stored Procedures using ExecuteStoreCommand and others.

For the latter we have to replace _ with ?, so our parameters should be passed like this.

var ietsParameterEmpAddress = new MySqlParameter("?EmpAddress", txtAddress.Text);

Then ExecuteStoreCommand should be as follows.

entities.ExecuteStoreCommand("CALL uspInsertUsers(?UserName,?Password,?FirstName,?LastName)", userName,password,FirstName,LastName);

As we did in our earlier blogs, the design and code are almost the same, except the changes are as I said.

I want to thank Vulpes for helping me in resolving the issues.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="crudEF.WebForm1" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">

    <title>MYSQL Entity Framework</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <center>
            <h2>
                CRUD operations in MYSQL using Entity Framework
            </h2>
        </center>

        <center>
            <h3>
                Display data in gridview using Entity Framework with out Mapping stored procedure
                to Model
            </h3>
            <div style="width: 800px; margin: 0 auto; float: left;">
                <asp:GridView ID="grdEmployess" runat="server" BackColor="White" BorderColor="#999999"

                    DataKeyNames="EmpID" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">

                    <AlternatingRowStyle BackColor="#DCDCDC" />
                    <EmptyDataTemplate>
                        No record to show
                    </EmptyDataTemplate>
                    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#F1F1F1" />
                    <SortedAscendingHeaderStyle BackColor="#0000A9" />
                    <SortedDescendingCellStyle BackColor="#CAC9C9" />
                    <SortedDescendingHeaderStyle BackColor="#000065" />
                </asp:GridView>

            </div>
            <br />

            <div style="width: 800px; margin: 0 auto; float: left;">
                <h3>
                    Insert Data to table using Entity Framework with out Mapping stored procedures to Model</h3>
                <table>
                    <tr>
                        <td>
                            Employee ID :
                        </td>
                        <td>
                            <asp:TextBox ID="txtEmpID" ReadOnly="true" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Employee Name :
                        </td>
                        <td>
                            <asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
                            <asp:RequiredFieldValidator ID="rqrdEmployeeName" runat="server" ErrorMessage="*"
                                ControlToValidate="txtEmployeeName" ToolTip="Employee name required" ValidationGroup="g"
                                Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Employee Address :
                        </td>
                        <td>
                            <asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>

                            <asp:RequiredFieldValidator ID="rqrdAddress" runat="server" ErrorMessage="*" ControlToValidate="txtAddress"

                                ToolTip="Address required" ValidationGroup="g" Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator>

                        </td>
                    </tr>
                    <tr>
                        <td>
                        </td>
                    </tr>
                    <tr align="center">
                        <td colspan="3">
                            <asp:Button ID="btnInsert" runat="server" Text="Insert" ValidationGroup="g" OnClick="btnInsert_Click" />

                        </td>
                    </tr>
                </table>
            </div>
            <br />
            <div style="width: 800px; margin: 0 auto; float: left;">
                <h3>
                    Edit and Update data using storedprocedure With out mapping it to Model</h3>
                <table>
                    <tr>
                        <td>
                            Select Employee ID :
                        </td>
                        <td>

                            <asp:DropDownList ID="ddleditEmpID" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddleditEmpID_SelectedIndexChanged">

                            </asp:DropDownList>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Employee Name :
                        </td>
                        <td>

                            <asp:TextBox ID="txtedtEmployeeName" runat="server"></asp:TextBox>

                            <asp:RequiredFieldValidator ID="rqrdedtEmpName" runat="server" ErrorMessage="*" ControlToValidate="txtedtEmployeeName"

                                ToolTip="Employee name required" ValidationGroup="g1" Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator>

                        </td>
                    </tr>
                    <tr>
                        <td>
                            Employee Address :
                        </td>
                        <td>

                            <asp:TextBox ID="txtedtEmpAddress" runat="server"></asp:TextBox>

                            <asp:RequiredFieldValidator ID="rqrdedtEmpAddress" runat="server" ErrorMessage="*"
                                ControlToValidate="txtedtEmpAddress" ToolTip="Address required" ValidationGroup="g1"
                                Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator>

                        </td>
                    </tr>
                    <tr>
                        <td>
                        </td>
                    </tr>
                    <tr align="center">
                        <td colspan="4">

                            <asp:Button ID="btnUpdate" runat="server" ValidationGroup="g1" Text="Update" OnClick="btnUpdate_Click" />

                        </td>
                    </tr>
                </table>
            </div>
            <br />
            <div style="width: 800px; margin: 0 auto; float: left;">
                <h3>
                    Delete data using storedprocedure With out mapping it to Model</h3>
                <table>
                    <tr>
                        <td>
                            Select Employee ID to Delete :
                        </td>
                        <td>
                            <asp:DropDownList ID="ddlEmpID" runat="server">
                            </asp:DropDownList>
                        </td>
                    </tr>
                    <tr>
                        <td>
                        </td>
                    </tr>
                    <tr align="center">
                        <td colspan="2">
                            <asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" />
                        </td>
                    </tr>
                </table>
            </div>
        </center>
    </div>
    </form>
</body>
</html>

aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;

namespace crudEF
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        efdbEntities entities = new efdbEntities();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                checkMax();
                loadGrid();
                bindDDL();
            }
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            Page.Validate("g");

            if (Page.IsValid)
            {
                var ietsParameterID = new MySqlParameter("?ID", System.Data.SqlDbType.Int);
                ietsParameterID.Value = Convert.ToInt16(txtEmpID.Text);
                var ietsParameterEmpName = new MySqlParameter("?EmpName", txtEmployeeName.Text);
                var ietsParameterEmpAddress = new MySqlParameter("?EmpAddress", txtAddress.Text);

                entities.ExecuteStoreCommand("CALL insertEmployee(?ID,?EmpName,?EmpAddress)", ietsParameterID, ietsParameterEmpName, ietsParameterEmpAddress);

                loadGrid();
                checkMax();
                bindDDL();
                txtAddress.Text = string.Empty;
                txtEmployeeName.Text = string.Empty;
            }
        }

        public void checkMax()
        {
            int? maxEmpID = entities.tblemployees.Max(q => (int?)q.EmpID);

            if (maxEmpID != null)
            {
                maxEmpID = maxEmpID + 1;
                txtEmpID.Text = maxEmpID.ToString();
            }
            else
            {
                maxEmpID = 1000;
                txtEmpID.Text = maxEmpID.ToString();
            }
        }

        public void loadGrid()
        {
            var selectData = entities.ExecuteStoreQuery<tblemployee>("CALL SelectEmployee").ToList();

            grdEmployess.DataSource = selectData;
            grdEmployess.DataBind();
        }

        public void bindDDL()
        {
            var display = from e in entities.tblemployees select new { e.EmpID };

            ddlEmpID.DataSource = display.ToList();
            ddlEmpID.DataTextField = "EmpID";
            ddlEmpID.DataValueField = "EmpID";
            ddlEmpID.DataBind();
            ddlEmpID.Items.Insert(0, "--Select--");

            ddleditEmpID.DataSource = display.ToList();
            ddleditEmpID.DataTextField = "EmpID";
            ddleditEmpID.DataValueField = "EmpID";
            ddleditEmpID.DataBind();
            ddleditEmpID.Items.Insert(0, "--Select--");
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            if (ddlEmpID.SelectedItem.Text != "--Select--")
            {
                var ietsParameterID = new MySqlParameter("?ID", ddlEmpID.SelectedItem.Text);
                entities.ExecuteStoreCommand("CALL deleteEmp(?ID)", ietsParameterID);
                loadGrid();
                checkMax();
                bindDDL();
            }
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            Page.Validate("g1");
            if (Page.IsValid)
            {
                if (ddleditEmpID.SelectedItem.Text != "--Select--")
                {
                    var ietsParameterID = new MySqlParameter("?ID", System.Data.SqlDbType.Int);
                    ietsParameterID.Value = Convert.ToInt16(ddleditEmpID.SelectedItem.Text);
                    var ietsParameterEmpName = new MySqlParameter("?EmpName", txtedtEmployeeName.Text);

                    var ietsParameterEmpAddress = new MySqlParameter("?EmpAddress", txtedtEmpAddress.Text);

                    entities.ExecuteStoreCommand("CALL UpdateEmployee(?ID,?EmpName,?EmpAddress)", ietsParameterID, ietsParameterEmpName, ietsParameterEmpAddress);

                    loadGrid();
                    txtedtEmployeeName.Text = string.Empty;
                    txtedtEmpAddress.Text = string.Empty;
                }
            }
        }

        protected void ddleditEmpID_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddleditEmpID.SelectedItem.Text != "--Select--")
            {
                int id = Convert.ToInt16(ddleditEmpID.SelectedValue.ToString());
                var display = from e1 in entities.tblemployees

                              where e1.EmpID.Equals(id)
                              select new { e1.Emp_Name, e1.Emp_Address };

                foreach (var v in display)
                {
                    txtedtEmployeeName.Text = v.Emp_Name;
                    txtedtEmpAddress.Text = v.Emp_Address;
                }
            }
        }
    }
}

Conclusion

In the last article, I showed how we can implement CRUD operations using Entity Framework using SQL database; now, I want to show you how we can implement the same using MySql database.