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

Return value will be Id, remember that DepartmentInsert procedure returns Scope_Identity that is named Id

Part 1 of this tutorial - http://howtodomssqlcsharpexcelaccess.blogspot.ca/2012/10/visual-studio-2010-ms-sql-stored.html

Watch this tutorial on YouTube:



Assign Functions for Update and Delete - they are not returning any values.

Now right click on .edmx file and select Model Browser

In Model Browser expend Stored Procedures folder - this folder lists all stored procedures
Right click DepartmentGetAll and select Add Function Import
On new screen click Get Column Information - it will populate all columns that this stored proc is returning.  Then click Create New Complex Type.  It will create DepartmentGetAll_Result1 type - I will use it as a return type.


Right click DepartmentGetById and select Add Function Import
Click Complex radio button and select DepartmentGetAll_Result

Right click DepartmentDelete and select Add Function Import
This function will return nothing, so select None radio button.

 Repeat it for DepartmentInsert and DepartmentUpdate - both are returning nothing.

Now we can start coding.
Double click somewhere on WebForm1.aspx file to create on page load function.
This function will check if page is loaded for the first time.  If yes, it will call LoadDepartments method, that will populate drop down list with department names.  It will also add option "Create new department"
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                loadDepartments();
            }
        }

        private void loadDepartments()
        {
            using (SchoolEntities schoolContext = new SchoolEntities())
            {
                var dept = from d in schoolContext.DepartmentGetAll()
                           orderby d.DepartmentName
                           select new
                           {
                               Name = d.DepartmentName,
                               Id = d.DepartmentId
                           };

                departmentList.DataTextField = "Name";
                departmentList.DataValueField = "Id";
                departmentList.DataSource = dept;
                departmentList.DataBind();

                departmentList.Items.Insert(0, new ListItem("Create a new department", ""));
            }
        }

Run this program - department list should be populated with department names.
Now double click departmentList to create select index changed method.  It will populate departmentName textbox with... department name (again, this is very simple and basic example, just to illustrate how EntityFramework works)
If CreateNewDepartment is selected - textbox will be set to empty string
        protected void deleteRecord_Click(object sender, EventArgs e)
        {
            using (SchoolEntities schoolContext = new SchoolEntities())
            {
                Dpt department = new Dpt();
                department.DepartmentId = Convert.ToInt32(departmentList.SelectedValue);
                schoolContext.Dpts.Attach(department);
                schoolContext.ObjectStateManager.ChangeObjectState(department, System.Data.EntityState.Deleted);
                schoolContext.SaveChanges();
                loadDepartments();
                departmentName.Text = "";
            }
        }
Now les't save or update department.  Double click save button.  Program will check
- if create new department option is selected then insert new record into database
- if existing department is selected - update record in database
        protected void deleteRecord_Click(object sender, EventArgs e)
        {
            using (SchoolEntities schoolContext = new SchoolEntities())
            {
                Dpt department = new Dpt();
                department.DepartmentId = Convert.ToInt32(departmentList.SelectedValue);
                schoolContext.Dpts.Attach(department);
                schoolContext.ObjectStateManager.ChangeObjectState(department, System.Data.EntityState.Deleted);
                schoolContext.SaveChanges();
                loadDepartments();
                departmentName.Text = "";
            }
        }
Finally - double click delete button -
        protected void deleteRecord_Click(object sender, EventArgs e)
        {
            using (SchoolEntities schoolContext = new SchoolEntities())
            {
                Dpt department = new Dpt();
                department.DepartmentId = Convert.ToInt32(departmentList.SelectedValue);
                schoolContext.Dpts.Attach(department);
                schoolContext.ObjectStateManager.ChangeObjectState(department, System.Data.EntityState.Deleted);
                schoolContext.SaveChanges();
                loadDepartments();
                departmentName.Text = "";
            }
        }

Now we have web page where we can insert, update or delete records - all using entity framework.