Select Data With Table Valued Function (TVF) Via Data Entity Model - EDF Framework 5.0

Vijay Prativadi
Dec 16, 2012
6.3k
0
0
Article

Introduction

Today, in this article let's play around with one of the interesting and most useful concepts in Entity Data Model Framework 5.0.

Question: What is select data using TVF via EDM Framework 5.0?

In simple terms "It provides flexibility to pull off the data from a table valued function into an Entity Model using Entity Framework 5.0".

Step 1: Table Valued Function Script looks like this:

USE [Company]
GO/****** Object: UserDefinedFunction [dbo].[GetEmployeeData] Script Date: 12/15/2012 15:24:29 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCreate Function [dbo].[GetEmployeeData]
(@EmpId INT)
RETURNS TABLE
RETURNSelect [EmpId],[FirstName],[LastName],[Age],[Location]From [dbo].[Employee]Where EmpId = @EmpIdGO

Step 2: Create a new web application:

Step 3: Add a new ADO.NET entity data model:

Step 4: The complete code of WebForm1.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="TVFEFApp.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <style type="text/css">
        .grid
        {
            margin-top: 50px;
        }
    </style>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <center>
        <div>
            <table>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Label ID="Label1" runat="server" Text="Select Data with TVF via Entity Framework 5.0"
                            Font-Bold="true" Font-Size="Large" Font-Names="Verdana" ForeColor="Maroon"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:Label ID="Label2" runat="server" Text="Please Enter Employee Id" ForeColor="Brown"
                            Font-Bold="true" Font-Size="Medium" Font-Names="Verdana"></asp:Label>
                    </td>
                    <td>
                        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Button ID="Button1" runat="server" Text="Select Data" Font-Names="Verdana" Width="213px"
                            BackColor="Orange" Font-Bold="True" OnClick="Button1_Click" />
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:GridView ID="GridView1" runat="server" CssClass="grid" BackColor="LightGoldenrodYellow"
                            BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">
                            <AlternatingRowStyle BackColor="PaleGoldenrod" />
                            <FooterStyle BackColor="Tan" />
                            <HeaderStyle BackColor="Tan" Font-Bold="True" />
                            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
                            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
                          <SortedAscendingCellStyle BackColor="#FAFAE7" />
                            <SortedAscendingHeaderStyle BackColor="#DAC09E" />
                            <SortedDescendingCellStyle BackColor="#E1DB9C" />
                            <SortedDescendingHeaderStyle BackColor="#C2A47B" />
                        </asp:GridView>
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Label ID="Label3" runat="server" Font-Bold="true" Font-Size="Medium" Font-Names="Verdana"></asp:Label>
                    </td>
                </tr>
            </table>
        </div>
    </center>
    </form>
</body>
</html>

Step 5: The complete code of WebForm1.aspx.cs looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace TVFEFApp
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            TextBox1.Focus();
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(TextBox1.Text))
            {
                Label3.Text = "Please Enter Employee Id";
                Label3.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                var query = from r in objEntities.GetEmployeeData(int.Parse(TextBox1.Text)) select new
                { EmployeeId = r.EmpId, FirstName = r.FirstName, LastName = r.LastName, Age = r.Age };
                GridView1.DataSource = query;
                GridView1.DataBind();
                Label3.Text = "Data Retrived Successfully";
                Label3.ForeColor = System.Drawing.Color.Green;
                TextBox1.Text = string.Empty;
            }
        }
        #region Instance MembersCompanyEntities objEntities = new CompanyEntities();
        #endregion
    }
}

Step 6: The output of the application looks like this:

Step 7: The selected data output of the application looks like this:

I hope this article is useful for you. I look forward for your comments and feedback. Thanks Vijay Prativadi.