Thursday, December 27, 2012

Implementing CRUD operation in MySql using Entity Framework

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


Wednesday, December 26, 2012

Continued Entity framework CRUD operations using Stored Procedure

In the last blog I posted how can directly call Stored Procedures with out mapping them to a Model. Check here Entity Framework

Download code

Now I will show how you can map the stored procedures and do.

First Step :

Right click Employee and select Stored Procedure Mapping


Step 2 :

You can now see Mapping Details for Employee as follows


Step 3 : You can see the different functions here for Insert, Update and Delete. Now we will map each and every function with the required Stored procedure as follows


I will show for Insert Function the same way you can do for Update and Delete functions

Steep 4 : Mapping Insert Functions with properties


Step 5 : Mapping each property to the Parameters


Step 6 : Assign each and every parameter the you can have your Insert Function as follows


Like this do for Update and Delete Functions too, and save it.

Now we will look how to create functions for the mapped Stored Procedures

Inside the Model window right click select -> Add-> Function Import

Step A :



After this you can see the list of available Stored Procedures that you have include for the Model.

Step B :


Select the required Stored Procedure Name and give a Function Import Name, I will show for InsertEmployee in the same way you can do for Update and Delete. For Select I will show how to do

Step C :


Repeat (from Step A - Step C) the same for Update and Delete too..

When coming for Select follow as per Insert with the following changes, as it will return data from table we have to make it as Complex.



Get Column Information



Create Complex Type



Save the Model and now we will check whether this functions are imported or not through code.

Create a new web page copy Paste the following design


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="crud1.aspx.cs" Inherits="CRUDentity.crud1" %>

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <center>
            <h3>
                Display data in gridview using Entity Framework with 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 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 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 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>

Now you code is as follows

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

namespace CRUDentity
{
    public partial class crud1 : System.Web.UI.Page
    {
        EntitySampleEntities entities = new EntitySampleEntities();
        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)
            {
                entities.InsertData(Convert.ToInt16(txtEmpID.Text), txtEmployeeName.Text, txtAddress.Text);

                loadGrid();
                checkMax();
                bindDDL();
                txtAddress.Text = string.Empty;
                txtEmployeeName.Text = string.Empty;
            }
        }

        public void checkMax()
        {
            int? maxEmpID = entities.Employees.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 select = entities.selectData().ToList();
            grdEmployess.DataSource = select;
            grdEmployess.DataBind();
        }

        public void bindDDL()
        {
            var display = from e in entities.Employees 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--")
            {
                entities.deleteEmp(Convert.ToInt16(ddleditEmpID.SelectedValue.ToString()));
                loadGrid();
                checkMax();
                bindDDL();
            }
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            Page.Validate("g1");
            if (Page.IsValid)
            {
                if (ddleditEmpID.SelectedItem.Text != "--Select--")
                {
                    entities.updateEmp(Convert.ToInt16(ddleditEmpID.SelectedValue.ToString()), txtedtEmployeeName.Text, txtedtEmpAddress.Text);
                    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.Employees
                              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..
Please don't hesitate to ask for queries.

Happy Coding

Entity framework CRUD operations using Stored Procedure

In this post I would like to share some thing regarding Entity Framework, how we can implement CRUD operations using stored procedures in Entity Framework.

In this explain in two ways of implementing CRUD operations


  1. By calling Stored Procedures using ExecuteStoreCommand and ExecuteStoreQuery, with out mapping to Model
  2. By mapping Stored Procedures in to the Model.
OK first we will see how we can map the Stored Procedures in to the Model and how we can implement CRUD operations.

Create an Empty web application from your VS2010


First let us create a sample table, as I am not having SQL Server installed in my machine, I am adding SQL Server Database as follows



Here you can name Database1.mdf as per your naming convention. Now in the server explorer you can see your database, we will add a table and some Stored Procedures here as follows


Add the required columns and save the table with a desired name, the most important aspect before you start working on Entity Framework is to have a Primary key in your table.

Now my table looks as follows on which we are going to perform CRUD operations.


Ok now let's create stored procedures for Insert, Update, Delete and Select operations.

Insert Stored Procedure

CreatePROCEDURE dbo.InsertEmployee
(
  @ID int,
 @EmpName varchar(50),
 @EmpAddress varchar(50)
)
AS
Begin
insert into Employee(EmpID,Emp_Name,Emp_Address)values(@ID,@EmpName,@EmpAddress)
END


Delete Stored Procedure

Create PROCEDURE dbo.deleteEmp
(
 @ID int
)
As
Begin
delete from Employee where EmpID=@ID
End

Select

Create PROCEDURE dbo.SelectEmployee
As
Begin
select * from Employee
End

Update

Create PROCEDURE dbo.UpdateEmployee
(@ID int,
@EmpName varchar(50),
@EmpAddress varchar(50))
As
Begin
update Employee set Emp_Name=@EmpName,Emp_Address=@EmpAddress where EmpID=@ID
End


We are finished up with our database, now let us create a sample page and add Entity Model in our application.

Adding an Entity Model to your application


After adding an Model you will immediately have this Entity Data Model Wizard where you have to select Generate from database and click on Next


Select New Connection from the Choose your data




Here on Data source you will have some different sources which you can see by clicking on Change, as I have created by database in my application I will go with Microsoft SQL Server Database File (SqlClient), if any one using SQL Server you can change that to SQL Server from the options available.

As I am going with Microsoft SQL Server Database File (SqlClient) I will browse for my Database file and click on OK.




Here you can see my Database file and also the connection settings in Web.Config will be saved with the name EntitySampleEntities. Click Next where you will find all your tables and stored procedures that you have created select the required, as I create only one table and 4 stored procedures I will select them

Initial Window




Click on Finish after you are finished, the you can see your model with the tables you added and if there are any relations it will also map them. As per now I just created on table that will be shown as follows


Now we are finished up with creating database and adding it to an Entity Model. Now we will see how can perform CRUD operations with out mapping the stored procedures in to the model.

I also included some LINQ queries where ever needed, for example to auto-generate Employee ID and binding the drop-down list.

Create a web page and add the following design to that page.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="crud.aspx.cs" Inherits="CRUDentity.crud" %>

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <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" 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" ValidationGroup="g1" Text="Delete" OnClick="btnDelete_Click" />
                        </td>
                    </tr>
                </table>
            </div>
        </center>
    </div>
    </form>
</body>
</html>

Yourpage.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

namespace CRUDentity
{
    public partial class crud : System.Web.UI.Page
    {
        EntitySampleEntities entities = new EntitySampleEntities();
        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 SqlParameter("@ID", System.Data.SqlDbType.Int);
                ietsParameterID.Value = Convert.ToInt16(txtEmpID.Text);
                var ietsParameterEmpName = new SqlParameter("@EmpName", txtEmployeeName.Text);
                var ietsParameterEmpAddress = new SqlParameter("@EmpAddress", txtAddress.Text);

                entities.ExecuteStoreCommand("InsertEmployee @ID,@EmpName,@EmpAddress", ietsParameterID, ietsParameterEmpName, ietsParameterEmpAddress);
                loadGrid();
                checkMax();
                bindDDL();
                txtAddress.Text = string.Empty;
                txtEmployeeName.Text = string.Empty;
            }
        }

        public void checkMax()
        {
            int? maxEmpID = entities.Employees.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<Employee>("SelectEmployee").ToList();
            grdEmployess.DataSource = selectData;
            grdEmployess.DataBind();
        }

        public void bindDDL()
        {
            var display = from e in entities.Employees 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 SqlParameter("@ID", ddlEmpID.SelectedItem.Text);
                entities.ExecuteStoreCommand("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 SqlParameter("@ID", System.Data.SqlDbType.Int);
                    ietsParameterID.Value = Convert.ToInt16(ddleditEmpID.SelectedItem.Text);
                    var ietsParameterEmpName = new SqlParameter("@EmpName", txtedtEmployeeName.Text);
                    var ietsParameterEmpAddress = new SqlParameter("@EmpAddress", txtedtEmpAddress.Text);

                    entities.ExecuteStoreCommand("UpdateEmployee @ID,@EmpName,@EmpAddress", ietsParameterID, ietsParameterEmpName, ietsParameterEmpAddress);
                    loadGrid();
                }
            }
        }

        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.Employees
                              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;
                }
            }
        }
    }
}

Sample screen shots :

When you first run the application



You can see as there are no records in the table grid view is showing empty. Also you can see the Employee ID is read only, to avoid duplicated I make this one if you wan't you can remove that and do as per required.

Now we will see what happens after submitting data


Now we will edit the record see here I will change the Employee Address initially it is Hyderabad I will change it to some other. To do that select the Employee ID that you need to edit and update, as per here I am having only one Employee so I will do for that.

Before editing Employee Address



Let's do deleting for this I will add another employee to the table as per shown and will delete.

Before delete


After Delete

That's it, this is how we can do the basic CRUD operation using Entity Framework with out mapping Stored Procedures to the Model.

Wait for the next one how we can implement CRUD operations using Entity Framework with mapping stored procedures to the Model.


Happy Coding


Popular Posts