Tech-Today

Implement a stored procedure in Entity Framework

Implement a stored procedure in Entity Framework

1.) To do this we have to create a database (name according to your preference) and add the following tables:

CREATE TABLE [dbo].[Authors](
[AuthorID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NOT NULL,
CONSTRAINT [PK__Authors__70DAFC1403317E3D] PRIMARY KEY CLUSTERED 
(
[AuthorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Add insert procedure:

create procedure [dbo].[InsertAuthor]
(
@Name varchar(50)
) 
as
begin
insert into Authors(name) values (@name)
end

Get Authors:

create procedure [dbo].[GetAllAuthors] 
as
begin
select * from Authors
end

2.) Create an asp.net project
3.) Right click the project, add->new item->data->ado.net entity data model.
  a.) Select Generate from Database and point to the database we've created.
4.) Open the edmx file, and add tables, select Authors
5.) Open the Model Browser view then,
  a.) Select [database].edmx->[database]Model.Store
  b.) In the Stored Procedure folder you should see the InsertAuthor and GetAllAuthors
  c.) Right click each and select "Create Function Import"
  d.) For the InsertAuthor just click ok, but for the GetAllAuthors change the Return

Type->Entities to Authors
Then we can call the 2 stored procedures in our code.

How to call:
Insert Author:

public void InsertAuthor()
{
String connString = ConfigurationManager.ConnectionStrings["[database]Entities"].ConnectionString;
using (EntityConnection connection = new EntityConnection(connString))
{
connection.Open();
EntityCommand command = connection.CreateCommand();
command.CommandText = "AnimeEntities.InsertAuthor";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("Name", "Glaiza");
try
{
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}

Get All Authors:

void GetAllAuhors()
{
using (AnimeEntities context = new AnimeEntities())
{
Authors a = context.GetAllAuthors().ToArray()[0];
Response.Write("Name: " + a.Name);
}
}

- Csharp Development
Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.How to implement an ajaxcontrol toolkit cascading dropdown with databaseThe process account used to run ASP.NET must have read access to the IIS metabase (e.g. IIS://servername/W3SVC)Error...

- Passing An Array Of Objects To Mssql Stored Procedure
Oftentimes you need to pass an array of objects (could be ids, types, etc) in an mssql stored procedure that you either need to insert into a table or use as filter. The following codes will explain the latter: Pass an array of integers: ALTER PROCEDURE...

- How To Use Entityframework Codefirst To Update Your Database
This tutorial will attempt to explain how to update an already existing database using EntityFramework's CodeFirst approach. We need: 1.) VS2010 SP1 2.) NuGet 3.) EntityFramework 4.1 4.) EntityFramework.Migrations Steps: 1.) We need to make...

- Create A New Seam Web Project In Eclipse-helios
In this exercise we will be building seam web project in eclipse. It's long so stay focus :-D. What you need (The following should be installed correctly): Note: in parentheses is where I installed mine. 1.) Jboss seam 2.2.1 (C:\jboss-seam-2.2.1)...

- Setting Mssql Server 2005 Table 2000 Compatible
Recently I was developing a c# application that uses sqlserver2005 database (express edition) setup on my laptop which has win7 os. The problem is the public server where I will deploy the application is only running a mssql server 2000 database. My application...

Tech-Today