Create SQL Stored Procedures

I need a stored procedure to retrieve the data, one to Update the data, and one to Insert the data. To get the data, I create a simple stored proc called Sales.usp_SalesCustomersAll which is nothing more than a select statement SELECT * from Person.Contact. I need a Stored Procedure to Insert the data (usp_InsContacts) and one to Update the data (usp_UpdContacts).

Create a ViewModel

The View Model needs fields that will be called from the Stored Proc.

public class JobViewModel
{
public int id { get; set; }
public string firstName { get; set; }
public string lastName { get; set; }
public string email { get; set; }
}

Create a Data Layer Repository

I will called the Stored Proc from the Data Layer in a method called getAllJobs() which will return a collection of the View Model called JobViewModel.

.

Create an MVC Controller and View to Display Data

I add code to my Home Controller uner the ActionResult Index section to call my method to push all of the Person.Contact records into my model which will be of acollection of the strongly typed ViewModel, JobViewModel.

Notice the call at the top for the model to be of type IEnumerable to get the collection of the Type JobViewModel. I have made the id field a link to Edit the record in the foreach loop that builds a table from the collection. There is also a button at that top that I will use to create new records to add to Person.Contact.

I have added a snippet of jQuery code to get the btnNew event and redirect it to my controller called Data and go to the Action Create.

A quick test to see that everything runs before I create the Controller and Views for Edit and Create.

Adding Controllers and Views to Insert and Update Data

The Update SQL Stored Procedure to retrieve data by Id for editing is accessed in the Data Layer.

A Business Layer that returns data for editing or a placeholder in the FirstName textbox is created and a static method is created to return data to the model.

I create a Controller called Data with 2 ActionResult sections: one for Edit, the other for Create.

I then create a View for each. The Create View and Edit View are identical forms except that the Edit View will have data to update and the Create View will be inserting data.

In the Index.cshtml, I call separate Html.RenderActions depending on which Controller has been called - one for Edit, one for Create.

Addoing Stored Procedures & Methods to Insert and Update the Records

In the Data Layer, I add a static method to update the Contact table by passing the ViewModel as well as a 2nd static method to insert a record into the Contact table.

Note how the Insert stored procedure call does not require a parameter for the ID since it is already the Primary Key defined with the Identity(1,1) to automatically increment it upon Insert.

I add a couple of static methods to the Business Layer for the Update and Insert of the data using the ViewModel

I add an ActionResult for the Post method of the form for both Edit and Create and call my static methods in the Business Layer to either Update or Insert a record.

Testing the Edit Controller and View

I run the application and click on a link on the View page provided by the ViewModel in the Home Controller and successfully get to my record and update his name and click on Save Changes. This will cause my Data Controller to execute the code under [HttpPost] for the ActionResult Edit to update the data in the table.

I verify in the list back in the Home Controller that he has been updated with his new first name.

Testing the Insert Controller and View

I click on the Create New button from the Index page in the Home Controller and successfully get to the Create View form..

I then change the First Name, Last Name and email fields and click on Save Changes which will cause the code in the Data Controller Action Result under [HttpPost] for Create to execute and insert the new record into the Person.Contact table..

Back on the Home Index page, I see I have successfully added a new record to Person.Contact.