Reader Level:
Article

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

By Vijay Prativadi on Dec 15, 2012
Today, in this article let’s play around with one of the interesting and most useful concepts in Entity Data Model Framework 5.0.

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:

web-application.png

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

web-application1.png

Output3.png

Output4.png

Output5.png

Output6.png

Output7.png

Output8.png

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:

Output9.png

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

Output10.png

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

Vijay Prativadi

Hey Everyone, It's Vijay. I am SDE mostly up on Microsoft Technologies. If you are thinking to know what I do mostly when I'm at work. I get a chance to work upon few Microsoft technologies. All the way along my core tec... Read more

COMMENT USING