In the last two blogs 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.
creation of table and stored procedures are left as it is almost same as per we did in SQL.
Download
Problem faced :
Before doing this I would like to tell a few problems that I faced
- While mapping stored procedures from the model I am unable to assign the parameters as per 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 stored procedure with @ symbol. In 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 like we did in our earlier blogs. 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 the data with out 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 like 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 same except the changes as per I said.
<%@ 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;
}
}
}
}
}
Any comments welcome..
Feel free to ask for queries you have..
