Using Table Valued Function for Data Selection - EDF Framework 5.0

Vijay Prativadi
Apr 18, 2024

9.1k
0
3
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

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.

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
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetEmployeeData] (@EmpId INT)
RETURNS TABLE
AS
RETURN
    SELECT [EmpId], [FirstName], [LastName], [Age], [Location]
    FROM [dbo].[Employee]
    WHERE EmpId = @EmpId
GO

Step 2. Create a new web application.

New web application

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

ADO.NET entity

Entity data model

Wizard model

Database

Employee

Model browser

Edit function import

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 Members
        CompanyEntities objEntities = new CompanyEntities();
        #endregion
    }
}

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

Output of the application