Click here to Skip to main content
Click here to Skip to main content

Implementing CRUD operations in MySQL using Entity Framework

, 6 Jan 2013
Rate this:
Please Sign up or sign in to vote.
How we can implement CRUD operations using a MySQL database.

Introduction

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

Creation of table and stored procedures are left out as it is almost the same as we did in SQL Server.

Problem faced

Before doing this I would like to talk about a few problems that I faced.

While mapping stored procedures from the model I am unable to assign parameters as we did in SQL Server. Generally in SQL, while assigning parameters, we write as follows:

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

We use @ here as we assign parameters inside a Stored Procedure with @ symbol. In a similar way I tried the same by replacing @ with _ as we use _ symbol for assigning variables in MySQL stored procedures.

Then I called the stored procedure as follows:

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

After executing and trying to execute 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 per explained here Entity, we can insert data without mapping stored procedures by using ExecuteStoreCommand and others.

For 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 as follows:

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

Everything remaining is as per we did in our earlier blogs. The design and code are almost the same except the changes I mentioned.

I would like to thank Vulpes for helping me in resolving these issues.

http://3.bp.blogspot.com/-uJ_Tz-U3DkI/UNwOebe2jOI/AAAAAAAAFVA/KlbtSnYm8Lo/s1600/model.jpg

http://2.bp.blogspot.com/-KHsd-_fEZxE/UNwOgr8N0_I/AAAAAAAAFVQ/JSGHDbvaTKY/s1600/selectmysql.jpg

http://2.bp.blogspot.com/-ZH8GavtL40M/UNwOhnSIi4I/AAAAAAAAFVY/Ss0NdwoBz8M/s1600/spMapping.jpg

http://3.bp.blogspot.com/-zlwFGGUXXuo/UNwOdTGS2zI/AAAAAAAAFU0/m4RgmXug8Zc/s1600/connection.jpg

http://4.bp.blogspot.com/-5Q43TPssuDU/UNwOcZhlRNI/AAAAAAAAFUs/-dH0QlFwrMI/s1600/change.jpg

http://1.bp.blogspot.com/-GbC9mr_d9-o/UNwObEVCWRI/AAAAAAAAFUo/nzjn5aqXWqU/s1600/Server.jpg

http://1.bp.blogspot.com/-gON2Fv6GsWo/UNwOfmojgtI/AAAAAAAAFVE/HqTyt20uNQ0/s1600/noparameters.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 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;
                }
            }
        }
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

demouser743
Software Developer
India India
I am Dorababu, working as a Software engineer. Web development in Asp.Net with C#, WinForms and MS sql server are the experience tools that I have had for the past 3 years. Yet to work on WCF, WPF, Silverlight and other latest ones.
Follow on   Twitter   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 6 Jan 2013
Article Copyright 2013 by demouser743
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid