Visual Studio 2010 - Ms Sql - Stored Procedures - ASP.NET - How to perform CRUD operations using Entity Framework 4.0 PART 1

Visual Studio 2010 - Ms Sql - Stored Procedures - ASP.NET - How to perform CRUD operations using Entity Framework 4.0

Watch whole example on YouTube
This is very simple example that illustrates how to perform simple Insert/Edit/Delete/Display -functions using Entity Framework with MS SQL.  I will be using stored procedures to load, insert, update and delete records.

First let's create simple database with one table and stored procedures.

Table name: Dpt
It has 2 Columns DepartmentId and DepartmentName.
DepartmentId is primary key is identity.

Now let's create 5 simple stored procedures
1.  this procedure will return all records from the table.
CREATE PROCEDURE [dbo].[DepartmentGetAll]

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * From Dpt;
END
2. This procedure will return only one record from the table
CREATE PROCEDURE [dbo].[DepartmentGetById]
    @DepartmentId int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * from Dpt where DepartmentID = @DepartmentId;
END
3. This will insert record, once record is inserted it will return id of that record (primary key)
CREATE PROCEDURE [dbo].[DepartmentInsert]
    @DepartmentName Varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Insert Into Dpt (DepartmentName) Values (@DepartmentName);
    Select Scope_Identity() As Id;
END
4. Update
CREATE PROCEDURE [dbo].[DepartmentUpdate]
    @DepartmentId int,
    @DepartmentName Varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Update Dpt Set DepartmentName = @DepartmentName
    Where DepartmentId = @DepartmentId;
END
5. and Delete
CREATE PROCEDURE [dbo].[DepartmentDelete]
    @DepartmentId int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Delete from Dpt where DepartmentId = @DepartmentId;
END

Now, let't create new project in Visual Studio - Empty Web Application - I named it Entity SP CRUD








Add new web form to it and make it and set it as start page

add 4 controls to this form
drop down list called departmentList - make sure auto postback is set to true
text box called departmentName
button called saveChanges with Text  set to Save
button called deleteRecord with Text set to Delete

This is very simple form, again, just to illustrate how to perform CRUD examples.  User will be able to select department name from the list and then update or delete it.  User will be able also to select "Create new department" option from the list - in this case after clicking Save button program will insert new record into database.

Now we will add ADO.NET Entity Data Model to the project.  Right click project, select add, then select new item

Select Data under installed templates and ADO.NET Entity Data Model.  My database is called School therefore I named it SchoolModel.


On next screen select Generate from Database and click Next

Enter your connection properties

Click - Yes, include the sensitive data in the connection string and click next

On next screen select tables and stored procedures (Dept and stored procedures created before)
Ensure that Pluralize or singularize generated object name is selected
Ensure that Include foreign key columns in the model also is selected ( in this particular example I have only one table, so it is not needed, but in real application most likely you will have more then one table)


Click Finish button.
SchoolModel.edmx file is created - right click Dpt table and select Stored Procedures mapping





You will be presented with 3 options:


Click <Select Insert Function> and select name of your Insert stored procedure - in my case it is called DepartmentInsert


Continue...
http://howtodomssqlcsharpexcelaccess.blogspot.ca/2012/10/visual-studio-2010-ms-sql-stored_26.html