Monday, October 2, 2017

Call Stored Procedures from Entity Framework 6 in C# (Part 1)

Hello,
Here's a tutorial on how to call stored procedures from Entity Framework 6.0 through the context object using the stored procedure name. In the second part of the series, I'll demonstrate how to call the stored procedures using methods like ExecuteSqlCommand() and SqlQuery() from context.Database class. To start with here are the steps to complete this example.
Step 1
Add a Customers table in your database with fields.
=> CustomerID (int and identity set to true)
=> CompanyName(nvarchar)
=> ContactName(nvarchar)
=> Address(nvarchar)
=> Country(nvarchar)
=> Phone(nvarchar)
Step 2
Create stored procedures that will perform insert, update, delete and get all records operations.
Insert
ALTER Procedure [dbo].[InsertCustomer](@CompanyName nvarchar(40), 
 @ContactName nvarchar(30), 
 @Address nvarchar(60),
 @Country nvarchar(15),
 @Phone nvarchar(24))
As
Begin
 Insert Into dbo.Customers (CompanyName, ContactName, [Address], Country, Phone) 
  values (@CompanyName, @ContactName, @Address, @Country, @Phone)
End
Update
ALTER Procedure [dbo].[UpdateCustomer]
   (@CustomerID int,
    @CompanyName nvarchar(40), 
 @ContactName nvarchar(30), 
 @Address nvarchar(60),
 @Country nvarchar(15),
 @Phone nvarchar(24))
As
Begin
 Update dbo.Customers
   Set CompanyName = @CompanyName,
    ContactName  = @ContactName,
    [Address] = @Address,
    Country = @Country,
    Phone = @Phone
 where CustomerID = @CustomerID 
End
Delete
ALTER Procedure [dbo].[DeleteCustomer]
   (@CustomerID int)
As
Begin
 Delete from dbo.Customers where CustomerID = @CustomerID 
End
Get All Customers
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[GetAllCustomers]
As
Begin
 Select * from dbo.Customers 
End
Step 3
Create a console application and then add an ADO.NET Entity Model. Set the Entity Framework version to 6.0. Include the Customers table and the stored procedures which will be called by code. Step 4
In your console application project, create a context object and then add methods that will call the stored procedures. In the example code below, the stored procedures are executed directly using the context object. The parameters are also supplied with dummy records for testing purposes.
private static CustomerEntities ce = new CustomerEntities();

static void Main(string[] args)
{
 GetAllCustomers();
 //InsertCustomer();
 //UpdateCustomer();
 //DeleteCustomer();
 Console.ReadLine();
}
  
private static void InsertCustomer()
{
 try
 {
  int result = ce.InsertCustomer("BCD", "Mike", "Manila", "Philippines", "999-0001");
  if (result == 1)
  {
   Console.WriteLine("Insert Record Successful!");
  }
 }
 catch (Exception e)
 {
  //do something here
 }
}

private static void UpdateCustomer()
{
 try
 {
  int result = ce.UpdateCustomer(4, "Jerang", "Lara", "Kuala Lumpur", "Malaysia", "333-3333");
  if (result == 1)
  {
   Console.WriteLine("Update Record Successful!");
  }
 }
 catch (Exception e)
 {
  //do something here
 }
}

private static void DeleteCustomer()
{
 try
 {
  int result = ce.DeleteCustomer(4);
  if (result == 1)
  {
   Console.WriteLine("Delete Record Successful!");
  }
 }
 catch (Exception e)
 {
  //do something here
 }
}
  
private static void GetAllCustomers()
{
 var results = ce.GetAllCustomers().ToList();
 foreach (var item in results)
 {
  Console.WriteLine("ID:{0}, Company Name:{1}, Contact:{2}, Address: {3}, Country: {4}, Phone:{5}",
   item.CustomerID, item.CompanyName, item.ContactName, item.Address, item.Country, item.Phone);
  Console.Write("\n\n");
 }
}

Cheers! :-)

0 comments:

Post a Comment