Stored Procedures With Entity Framework

A Stored Procedure is a group of Transact SQL statements. If you write the same query many times then we can write that query as a Stored Procedure and call it by its name.
Here we will learn about Stored Procedures in Entity Framework with an example.

Step 1
 
First create a table with the name “StudentStoredProcedure” and provide a column name for this table and insert some value into this.



Step 2
 
Now create a Stored Procedure for insert, update and delete.
  1. Create procedure InsertStudentInfo  
  2. @Name nvarchar(50),  
  3. @Gender nvarchar(50),  
  4. @Branch nvarchar(50)  
  5. as  
  6. Begin  
  7. Insert into StudentStoredProcedure values (@Name, @Gender,@Branch)  
  8. End  
  9. Go  
  10. Create procedure UpdateStudentInfo  
  11. @ID int,  
  12. @Name nvarchar(50),  
  13. @Gender nvarchar(50),  
  14. @Branch nvarchar(50)  
  15. as  
  16. Begin  
  17. Update StudentStoredProcedure Set Name = @Name, Gender =@Gender,  
  18. Branch = @Branch  
  19. where ID = @ID  
  20. End  
  21. Go  
  22. Create procedure DeleteStudentInfo  
  23. @ID int  
  24. as  
  25. Begin  
  26. Delete from StudentStoredProcedure where ID = @ID  
  27. End  
  28. Go 


After writing this Stored Procedure script execute this and go to Programmability and expand the Stored Procedure. There you will see 3 Stored Procedures with the names “InsertStudentInfo”, “UpdateStudentInfo” and “DeleteStudentInfo”.


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Step 3
 
Now create a new project and right-click on Solution Explorer and click on “add new item” and select “ADO.Net Entity Data Model” and select "EF Designer from database". Then click on "Next". 
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Step 4
 
Here provide the connection and select your table and specify the app.config name as “SP_EntityFramework” and click on "Next".


















 
 
 
 
 
 
 
 
Step 5
 
Select the table name as StudentStoredProcedure. First check that the Import selected Stored Procedures and functions into the entity model checkbox is selected or not and then click Finish.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Step 6
 
After clicking on Finish, your Entity Data Model and Stored Procedure are created, but here you will not be able to see the Stored Procedure.
View Stored Procedure
Go to the Entity Model Designer surface and click on "Model Browser".

Now expand the Stored Procedures folder and you will see your Stored Procedures.




 


 
 
 
 
 
 
 
 
Step 7
 
Go to the Mapping Details, here you will see <Select Insert Function>, <Select Update Function>, <Select Delete Function>. Select one Stored Procedure for each one.


 


 
 
 
 
 
 
 
 
 
 
 
Step 8
 
Select for InsertStudent info and like that you can set for all the functions.
Step 9
 
Now we need to validate it before executing to ensure it will give an error or not. Right-click on the designer surfer and click on Validate.

Step 10
 
Add a form and drag and drop a DataGridView from the toolbox.
Go to the smart tag of the DataGridView and add columns to it. When you click on the “add column” link you will see the following screen.






















 
 


 
After adding the columns click OK. Then the DataGridView will be like the following:



 
 
 
 
 
 
 
 
 
 
Step 11
 
Again go to the smart tag of the DataGridView and select the DataSource as “BindingdataSource1”. In this screen you will see a link as “Add project dataSource” and click on this:


 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Click on Add project to Data Source, then Database and select Dataset. Now go to give a connection and provide a connection string name.
After clicking on Next you will see your tables. Select the table and Stored Procedure when you click on the OK button. When you run the application you will see data in the DataGridView.
And the following code will be generated automatically:
  1. private void Form1_Load(object sender, EventArgs e)  
  2. {  
  3.    // TODO: This line of code loads data into the 'for_entityDataSet.StudentStoredProcedure' table. You can use, or remove it, as needed.  
  4.    this.studentStoredProcedureTableAdapter.Fill(this.for_entityDataSet.StudentStoredProcedure);  

Double-click on the DataGridView and write code for “Delele” and “insert”.
And provide the connection also.
  1. builder.DataSource = "MUNESH ";  
  2. builder.InitialCatalog = "ForEntity";  
  3. builder.IntegratedSecurity = true;
  1. private void Form1_Load(object sender, EventArgs e)   
  2. {  
  3.     // TODO: This line of code loads data into the 'for_entityDataSet.StudentStoredProcedure' table. this.studentStoredProcedureTableAdapter.Fill(this.for_entityDataSet.StudentStoredProcedure);  
  4. }  
  5. private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)   
  6. {  
  7.     //delete the row form database on gridview buttion click  
  8.     if (e.ColumnIndex == 1)   
  9.     {  
  10.         using(SqlConnection conn = new SqlConnection(builder.ToString()))   
  11.         {  
  12.             using(SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn))   
  13.             {  
  14.                 try   
  15.                 {  
  16.                     conn.Open();  
  17.                     cmd.CommandType = CommandType.StoredProcedure;  
  18.                     // _customQuery = new CustomQuery();  
  19.                     // SqlCommand sqlcmd= new SqlCommand();  
  20.                     SqlParameter parm = null;  
  21.                     cmd.CommandText = "DeleteStudentInfo";  
  22.                     cmd.Parameters.AddWithValue("@ID", Convert.ToInt64(dataGridView1.Rows[e.RowIndex].Cells[2].Value));  
  23.                     cmd.ExecuteNonQuery();  
  24.                     conn.Close();  
  25.                     Form1_Load(sender, e);  
  26.                 } catch {}  
  27.             }  
  28.         }  
  29.     }  
  30.     //INSERT into the row form database on gridview buttion click  
  31.     if (e.ColumnIndex == 0)   
  32.     {  
  33.         using(SqlConnection conn = new SqlConnection(builder.ToString()))   
  34.         {  
  35.             using(SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn))   
  36.             {  
  37.                 try   
  38.                 {  
  39.                     conn.Open();  
  40.                     cmd.CommandType = CommandType.StoredProcedure;  
  41.                     // _customQuery = new CustomQuery();  
  42.                     // SqlCommand sqlcmd= new SqlCommand();  
  43.                     SqlParameter parm = null;  
  44.                     cmd.CommandText = "InsertStudentInfo";  
  45.                     cmd.Parameters.AddWithValue("@Name""MUNESH");  
  46.                     cmd.Parameters.AddWithValue("@GENDER""MALE");  
  47.                     cmd.Parameters.AddWithValue("@BRANCH""IT")  
  48.                     cmd.ExecuteNonQuery();  
  49.                     conn.Close();  
  50.                     Form1_Load(sender, e);  
  51.                 } catch {}  
  52.             }  
  53.         }  
  54.     }  

Step 12
 
Now run your application, you will get the following output.

 
 
 
 
 
 
 
 
 
 
 
 
You can visit  my blog.