Skip to main content

Execute TSQL statement with ExecuteStoreQuery in entity framework 4.0

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.

Execute TSQL Staement with entity framework

Now once we are done with adding a database let’s add a table called Client like following.

Execute SQL staement with enity framework

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.

Extcute StoredProcedure with entityframework

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.

Entity Framework with TSQL Statement

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.

Execute TSQL statement with ExecuteStoreQuery in entity framework 4.0

Once you process further in wizard it will be presented a screen where we can select the our table like following.

Entity Model with TSQL Stement

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&amp;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.

Enity framework example with TSQL Statement

That’s it. Hope you like it. Stay tuned for more..Till then happy programming.

Shout it

kick it on DotNetKicks.com

Comments

Popular posts from this blog

How to create Rest API(Web API) with ASP.NET Core 1.0

Recently Microsoft has released ASP.NET Core 1.0 RC2, I am getting lots of request from readers that about creating Web API. So I thought it will be a good idea to write a blog post about how to create Rest API(Web API) with ASP.NET Core 1.0.

So let’s get started via creating an ASP.NET Core 1.0 Web Application like following.



Once you click on ASP.NET Web Application, It will ask whether you need to create Web Application or Web API application. We are going to Web API so I am going to select Web API Application like following. Please note that in ASP.NET Core 1.0 there is no separate libraries or DLLs required for creating web APIs. This is just a project template.



Now once you click on OK It will create a Web API application with default values controller and program.cs.  As you know Program.cs is now starting point for the ASP.NET Core 1.0 application so It contains all the required configuration and startup items. Following is a code for that.
using System.IO; using Microsoft.Asp…

How to use migration with Entity Framework Core

Entity framework core is  the lightweight, extensible and cross-platform version of Entity Framework. Before some time, Microsoft has Released a new version of Entity Framework RC2. I have written a couple of blog post about Entity framework code first migration earlier for Entity framework 6.0. So there was a couple of request coming for writing a blog post about Entity Framework Core RC2 migration. So I thought it will be a good idea to give an overview how database migration works in Entity Framework Core RC2. This post will cover a basic scenario where we are going to create the database with existing ASP.NET Identity migration and then we are going to create a new model and have that migration applied in the database.

How to use Entity Framework Migrations: Let’s get started, To demonstrate entity framework core migrations, I am going to create a sample asp.net core web application like following.



Once we select asp.net core application it will appear the following dialog.



Now w…

Solution: gulp is not recognized as internal or external command- Visual Studio

Recently, After Upgrading to the newer version of Gulp, I was getting this error.  After doing lots of  finding online and in my system I was able to figure out that Modules like Gulp does not installed to the path. So when you try to run it from the command line it was showing below error.
gulp is not recognized as internal or external command How to solve : gulp is not recognized as internal or external command To solve this error we just need to create a new environment variable and set the NPM modules path there.

To create an environment variable,  Right click My Computer/ This Pc and click on properties.  It will open following windows.



Go to Advance System Settings and it will open system properties windows like below.



Click on Environment Variables it will open an environment and system variables windows like below.



Click on new System variable and put NODE_PATH in system variable name and %AppData%\npm\node_modules into the variable value like below.



Click on Ok. That’s it. N…