Implementing CRUD Operation in MySql Using Entity Framework

Dorababu Meka
11y
13.4k
0
1

Article

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 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.

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

<title>MYSQL Entity Framework</title>

</head>

<body>

<div>

<h2>

CRUD operations in MYSQL using Entity Framework

</h2>

</center>

<h3>

Display data in gridview using Entity Framework with out Mapping stored procedure

to Model

</h3>

<asp:GridView ID="grdEmployess" runat="server" BackColor="White" BorderColor="#999999"

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

No record to show

</EmptyDataTemplate>

</asp:GridView>

</div>

<br />

<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>

<asp:Button ID="btnInsert" runat="server" Text="Insert" ValidationGroup="g" OnClick="btnInsert_Click" />

</td>

</tr>

</table>

</div>

<br />

<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>

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

</td>

</tr>

</table>

</div>

<br />

<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>

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

}

Up Next

Ebook Download

View all

SQL Queries For Beginners

Read by 5.2k people

Download Now!

Learn

View all

Implementing CRUD Operation in MySql Using Entity Framework

Entity Framework CRUD Operations Using Stored Procedure