Implementing CRUD Operation in MySql Using Entity Framework

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

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

  1. 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 would like 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 that I encountered.

While mapping Stored Procedures from the model I was unable to 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. In a similar way I tried the same by replacing the @ with _ as we use _ symbol for assigning variables in MySQL Stored Procedures.

Then I called the Stored Procedure as follows like 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 as 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 line

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

For the later we have to replace _ with ? so our parameters should be passed like this:

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

Then ExecuteStoreCommand should be such as follows:

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

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

I would like to thanks Vulpes for helping me in resolving the issues.

Image1.jpg

Image2.jpg

Image3.jpg

Image4.jpg

Image5.jpg

Image6.jpg

Image7.jpg

<%@ 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;

                }

            }

        }

    }

}