I was playing with entity framework
in recent days and I was searching something that
how we can execute TSQL statement
in entity framework. And I have found one great way
to do that with entity framework ‘ExecuteStoreQuery’ method. It’s executes a TSQL statement against data source given enity framework context and returns strongly typed result. You can find more information about ExcuteStoreQuery from following link. http://msdn.microsoft.com/en-us/library/dd487208.aspx So let’s examine
how it works. So Let’s first create a table against which we are going
to execute TSQL statement. So I have added a SQL Express database as following. Now once we are done with adding a database let’s add a table called Client like following. Here you can see above Client table is very simple. There are only two fields ClientId and ClientName where ClientId is primary key and ClientName is field where we are going
to store client name. Now it’s time
to add some data
to the table. So I have added some test data like following. Now it’s time
to add entity framework model class. So right click project->Add new item and select ADO.
NET entity model as following. After clicking on add button a wizard will start it will ask whether we need
to create model classes from database or not but we already have our client table ready so I have selected generate from database as following. Once you process further
in wizard it will be presented a screen where we can select the our table like following. Now once you click finish it will create model classes with for us. Now we need a gridview control where we need
to display those data. So
in Default.aspx page I have added a grid control like following. <%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeBehind="Default.aspx.cs" Inherits="EntityFramework._Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<h2>
Welcome
to ASP.NET!
</h2>
<p>
To learn more about
ASP.
NET visit <a href="http://www.
asp.net" title="ASP.
NET Website">www.
asp.net</a>.
</p>
<p>
You can also find <a href="http://go.microsoft.com/fwlink/?LinkID=152368&clcid=0x409"
title="MSDN
ASP.
NET Docs">documentation on
ASP.
NET at MSDN</a>.
<asp:GridView ID="grdClient" runat="server">
</asp:GridView>
</p>
</asp:Content>
Now once we are done with adding Gridview its time
to write code for server side. So I have written following code
in Page_load event of default.aspx page.
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
using (var context = new EntityFramework.TestEntities())
{
ObjectResult<Client> result = context.ExecuteStoreQuery<Client>("Select * from Client");
grdClient.DataSource = result;
grdClient.DataBind();
}
}
}
Here
in the above code you can see that I have written create a object of our entity model and then with the help of the ExecuteStoreQuery method I have execute a simple select TSQL statement which will return a object result. I have bind that object result with gridview
to display data.
So now we are done with coding.So let’s run application
in browser. Following is output as expected.
That’s it. Hope you like it. Stay tuned for more..Till then happy programming.
