In this article you will learn Select, Insert, Update and Delete operation with SQL Stored procedure in entity framework with Ado.Net data Model.
C-----Create New Record in Data table
R-----Read Record from Data table
U-----Update Record in Data table
D-----Delete Record from Data table
First you should create a database with a table name of student. Then you should be able to create a table field and add some sample records shown in below image,
Next you should create a stored procedure for insert, update/edit, delete operations on table records as shown in the below image,
Here ID is a primary key for student table.
Now go to visual studio to create an entity framework application.
You can see my pervious article for Entity framework with Ado.Net data Model connection and how to create a new entity framework project and sql database connection maintained with created entity framework project. Also you can see how to create a class in entity framework based on data table. Click on given below link for details,
Now create ADO.NET Entity data Model. In this ADO.NET data Entity Model create entity project connection with our sql database. When we create model for an entity framework project open a database object and set a dialog box. In this dialog box you should check the box for created data table and stored procedure import in the entity model as shown in below image,
After clicking finish button you can see SudentModel.edmx surface diagram as shown in the below image,
But you cannot see here the created import stored procedure from database; you can see only import table and field. Don’t worry, just right click on surface area of created model as shown in the below image,
Now click on Model Browser Tab and open a model browser window in your project as shown in the below image,
When you click Model Browser then open StudentModel.Store and inside StudentModel.Store you can see created stored procedure with supplied parameters. Also you can check in Function Import tab.
Now add a web from in the project and add EntityDataSource control, Detailview control for applying sql operation on table records and also add a gridview control for displaying student information as shown in the below image,
Now select EntityDataSource side arrow and open EntityDataSource Tasks for configuring data source as shown in the below image,
Now open one more configure ObjectContext dialog box as shown in the below image,
Here select Named connection and DefaultContainerName. Then press Next button and open configure data selection dialog box as shown in the below image,
In this opened dialog box select an EntitySetName and properties. Also you should select checkbox for automatic inserts, updates and deletes and click finish button.
After that configure process, run project, and open in browser as shown in the below image,
Here you can see just student information display. It's not displaying any button for insert, update and delete.
Don’t worry, it’s very easy go to web from design in gridview control for adding Edit and delete buttons as shown in the below image,
The above image goes to gridview task and enables Editing and Deleting checkbox.
Now goes to Detailview control tasks and choose data source and enable inserting as shown in the below image,
You can see here detailview control is available as read only so you should change detailview DefaultMode properties as shown in the below image,
Next map a stored procedure for insert, update, delete operation which goes to our created Model and select and right click on that and it goes to properties as shown in the below image,
Now click on stored Procedure Mapping then open Student Mapping Detail dialog box as shown in the below image,
Here you should map created stored procedure as shown in the below image,
Here you can see all stored procedure parameter fields mapped with property fields as shown in the above image. Here you can see Student id is not mapped because it’s an auto increment primary id for all students so we cannot insert from here. So you should go to Detailview design code and primary ID datafield property make InsertVisible = “false” as shown in the below image,
When you make ID property InsertVisible = “false” then you cannot see id in detail view as shown in the below image,
Now you can see here three fields available as per our requirement. Now add one more command ItemInserting property of detailview for inserted record display on gridview as shown in the below image,
Also you should be add a single line code on Student.aspx.cs page as shown in the below image,
Now we're ready for our code add to insert, update and delete operation so you can now open web form on browser as shown in the below image,
In this above image you can see here add a new record in a table and after that try to update and delete record as shown in the below image,
Now you can check in database student table if the record is changed or not.
I hope you understood how SQL stored procedure performs with entity framework using ADO.NET Entity Data Model.
Good Day...Happy Coding