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