Looking for a JavaEE Architect or Potential Tech Co-Founder?
Please don't hesitate to contact me.

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);
}
}
Implement a stored procedure in Entity Framework Implement a stored procedure in Entity Framework Reviewed by Edward Legaspi on Thursday, July 15, 2010 Rating: 5

No comments:

Powered by Blogger.