Post
Ask Question

CRUD Operations In ASP.NET Core 1.0 MVC Application - Part Seven

Let's discuss how to connect to databases.
 
We have already implemented basic CRUD operations with mockup data. Now, we are going to extend our application from last discussion Crud Operations in Asp.net Core 1.0 Mvc Application - Part Six to connect with SQL Server Database. And, we will be using DataAccess Project from .NET Core 1.0 Connecting SQL Server Database.

A .NET Core application can connect to a database through Database Provider. We are going to use an SQL Server Database ContactDB, therefore, we are going to use Microsoft SQL Server Database Provider.

Create Data Access Project
  • Open existing Solution in Visual Studio 2015.

  • Add new WebApplicationCore.NetCore.DataAccess and add reference to Microsoft.EntityFrameworkCore.SqlServer and BaseDataAccess class following steps in .NET Core 1.0 Connecting SQL Server Database.

  • We are going to use IConfigurationRoot to get web application configurations. So, add Reference to Microsoft.Extensions.Configuration.Abstractions as following.

    • Open NuGet Manager through WebApplicationCore.NetCore.DataAccess Reference ContextMenu >> References >> Manage NuGet Packages. In Browse tab, search for "Microsoft.Extensions.Configuration.Abstractions" and install.

  • Add new Constructor with parameter of IConfigurationRoot type to get configurations.
  1. public BaseDataAccess(IConfigurationRoot configuration) {  
  2.     this.ConnectionString = configuration["ConnectionStrings:DefaultConnection"];  
  3. }  


Add ContactDataAccess to WebApplicationCore.NetCore.DataAccess
  • Add reference to “WebApplicationCore.NetCore.Model” because we are going to use Contact Model Class in project.

    • Open WebApplicationCore.NetCore. BusinessLogic References >> Add References >> Reference Manager Screen >> Projects >> Solution >> Select WebApplicationCore.NetCore.Model.
    • Click OK.
  • Add new Interface.

    • Open Add New Item Screen through Solution Context Menu >> Add >> New Item >> Installed >> .NET Core >> Code >> Interface.
    • Name it IContactDataAccess.cs.
    • Click OK Button.
  • It will add a new interface in project. 
  • Define CRUD operations in interface.
  • Add new Class.

    • Open Add New Item Screen through Solution Context Menu >> Add >> Class >> Installed >> .NET Core >> Code >> Class.
    • Name it ContactDataAccess.cs.
    • Click OK Button.
  • It will add a new class in project. 
  • Make ContactDataAccess child of BaseDataAccess and implement IContactDataAccess.
  • Implement code to perform CRUD operation from database.
  1. public interface IContactDataAccess {  
  2.     Contact GetContact(int contactId);  
  3.     List < Contact > GetContacts();  
  4.     Contact CreateContact(Contact contact);  
  5.     bool UpdateContact(Contact contact);  
  6.     bool DeleteContact(int contactId);  
  7. }  
  1. public class ContactDataAccess: BaseDataAccess, IContactDataAccess {  
  2.     public ContactDataAccess(IConfigurationRoot configuration): base(configuration) {}  
  3.     public Contact GetContact(int contactId) {  
  4.         Contact contactItem = null;  
  5.   
  6.         List < DbParameter > parameterList = new List < DbParameter > ();  
  7.   
  8.         parameterList.Add(base.GetParameter("ContactId", contactId));  
  9.   
  10.         using(DbDataReader dataReader = base.ExecuteReader("Contact_Get", parameterList, CommandType.StoredProcedure)) {  
  11.             if (dataReader != null && dataReader.HasRows) {  
  12.                 if (dataReader.Read()) {  
  13.                     contactItem = new Contact();  
  14.                     contactItem.ContactId = (int) dataReader["ContactId"];  
  15.                     contactItem.Name = (string) dataReader["Name"];  
  16.                     contactItem.Address1 = (string) dataReader["Address1"];  
  17.                     contactItem.Address2 = (string) dataReader["Address2"];  
  18.                     contactItem.City = (string) dataReader["City"];  
  19.                     contactItem.ProvinceState = (string) dataReader["ProvinceState"];  
  20.                     contactItem.ZipPostalCode = (string) dataReader["ZipPostalCode"];  
  21.                     contactItem.Country = (string) dataReader["Country"];  
  22.                     contactItem.ContactNumber = (string) dataReader["ContactNumber"];  
  23.                     contactItem.Email = (string) dataReader["Email"];  
  24.                     contactItem.WebSite = (string) dataReader["WebSite"];  
  25.                 }  
  26.             }  
  27.         }  
  28.         return contactItem;  
  29.     }  
  30.   
  31.     public List < Contact > GetContacts() {  
  32.         List < Contact > contacts = new List < Contact > ();  
  33.         Contact contactItem = null;  
  34.   
  35.         List < DbParameter > parameterList = new List < DbParameter > ();  
  36.   
  37.         using(DbDataReader dataReader = base.ExecuteReader("Contact_GetAll", parameterList, CommandType.StoredProcedure)) {  
  38.             if (dataReader != null) {  
  39.                 while (dataReader.Read()) {  
  40.                     contactItem = new Contact();  
  41.                     contactItem.ContactId = (int) dataReader["ContactId"];  
  42.                     contactItem.Name = (string) dataReader["Name"];  
  43.                     contactItem.Email = (string) dataReader["Email"];  
  44.                     contactItem.WebSite = (string) dataReader["WebSite"];  
  45.   
  46.                     contacts.Add(contactItem);  
  47.                 }  
  48.             }  
  49.         }  
  50.         return contacts;  
  51.     }  
  52.   
  53.     public Contact CreateContact(Contact contact) {  
  54.         List < DbParameter > parameterList = new List < DbParameter > ();  
  55.   
  56.         DbParameter contactIdParamter = base.GetParameterOut("ContactId", SqlDbType.Int, contact.ContactId);  
  57.         parameterList.Add(contactIdParamter);  
  58.         parameterList.Add(base.GetParameter("Name", contact.Name));  
  59.         parameterList.Add(base.GetParameter("Address1", contact.Address1));  
  60.         parameterList.Add(base.GetParameter("Address2", contact.Address2));  
  61.         parameterList.Add(base.GetParameter("City", contact.City));  
  62.         parameterList.Add(base.GetParameter("ProvinceState", contact.ProvinceState));  
  63.         parameterList.Add(base.GetParameter("ZipPostalCode", contact.ZipPostalCode));  
  64.         parameterList.Add(base.GetParameter("Country", contact.Country));  
  65.         parameterList.Add(base.GetParameter("ContactNumber", contact.ContactNumber));  
  66.         parameterList.Add(base.GetParameter("Email", contact.Email));  
  67.         parameterList.Add(base.GetParameter("WebSite", contact.WebSite));  
  68.         base.ExecuteNonQuery("Contact_Create", parameterList, CommandType.StoredProcedure);  
  69.         contact.ContactId = (int) contactIdParamter.Value;  
  70.         return contact;  
  71.     }  
  72.     public bool UpdateContact(Contact contact) {  
  73.         bool updated = true;  
  74.         List < DbParameter > parameterList = new List < DbParameter > ();  
  75.   
  76.         parameterList.Add(base.GetParameter("ContactId", contact.ContactId));  
  77.         parameterList.Add(base.GetParameter("Name", contact.Name));  
  78.         parameterList.Add(base.GetParameter("Address1", contact.Address1));  
  79.         parameterList.Add(base.GetParameter("Address2", contact.Address2));  
  80.         parameterList.Add(base.GetParameter("City", contact.City));  
  81.         parameterList.Add(base.GetParameter("ProvinceState", contact.ProvinceState));  
  82.         parameterList.Add(base.GetParameter("ZipPostalCode", contact.ZipPostalCode));  
  83.         parameterList.Add(base.GetParameter("Country", contact.Country));  
  84.         parameterList.Add(base.GetParameter("ContactNumber", contact.ContactNumber));  
  85.         parameterList.Add(base.GetParameter("Email", contact.Email));  
  86.         parameterList.Add(base.GetParameter("WebSite", contact.WebSite));  
  87.   
  88.         int returnValue = base.ExecuteNonQuery("Contact_Update", parameterList, CommandType.StoredProcedure);  
  89.   
  90.         updated = returnValue > 0;  
  91.   
  92.         return updated;  
  93.     }  
  94.   
  95.     public bool DeleteContact(int contactId) {  
  96.         bool deleted = false;  
  97.   
  98.         List < DbParameter > parameterList = new List < DbParameter > ();  
  99.   
  100.         parameterList.Add(base.GetParameter("ContactId", contactId));  
  101.   
  102.         int returnValue = base.ExecuteNonQuery("Contact_Delete", parameterList, CommandType.StoredProcedure);  
  103.   
  104.         deleted = returnValue > 0;  
  105.   
  106.         return deleted;  
  107.     }  
  108. }  


Change WebApplicationCore.NetCore.BusinessLogic
  • Add reference to “WebApplicationCore.NetCore.DataAccess” in WebApplicationCore.NetCore.BusinessLogic because we are going to use ContactDataAccess in this project.

    • Open WebApplicationCore.NetCore. BusinessLogic References >> Add References >> Reference Manager Screen >> Projects >> Solution >> Select WebApplicationCore.NetCore.DataAccess.

    • Click OK Button.
  • Although we are not going to use Configuration in BusinessLogic, yet we may need it in future. To use IConfigurationRoot to get web application configurations, add Reference to Microsoft.Extensions.Configuration.Abstractions as following,

    • Open NuGet Manager through WebApplicationCore.NetCore.BusinessLogic Reference context menu >> References >> Manage NuGet Packages. in Browse tab search for "Microsoft.Extensions.Configuration.Abstractions" and install.
  • Add new Interface.

    • Open Add New Item Screen through Solution Context Menu >> Add >> New Item >> Installed >> .NET Core >> Code >> Interface.

    • Name it IContactBusinessLogic.cs.

    • Click OK Button.
  • It will add a new interface in project. 
  • Define CRUD operations in interface.
  • Change ContactBusinessLogic to use ContactDataAccess instead of mockup data.
  1. public interface IContactBusinessLogic {  
  2.     Contact GetContact(int contactId);  
  3.     List < Contact > GetContacts();  
  4.     Contact CreateContact(Contact contact);  
  5.     bool UpdateContact(Contact contact);  
  6.     bool DeleteContact(int contactId);  
  7. }  
  1. public class ContactBusinessLogic: IContactBusinessLogic {  
  2.     private IConfigurationRoot Configuration;  
  3.     private IContactDataAccess ContactDataAccess;  
  4.   
  5.     public ContactBusinessLogic(IContactDataAccess contactDataAccess, IConfigurationRoot configuration) {  
  6.         this.ContactDataAccess = contactDataAccess;  
  7.         this.Configuration = configuration;  
  8.     }  
  9.   
  10.     public Contact GetContact(int contactId) {  
  11.         return this.ContactDataAccess.GetContact(contactId);  
  12.     }  
  13.   
  14.     public List < Contact > GetContacts() {  
  15.         return this.ContactDataAccess.GetContacts();  
  16.     }  
  17.   
  18.     public Contact CreateContact(Contact contact) {  
  19.         return this.ContactDataAccess.CreateContact(contact);  
  20.     }  
  21.   
  22.     public bool UpdateContact(Contact contact) {  
  23.         return this.ContactDataAccess.UpdateContact(contact);  
  24.     }  
  25.   
  26.     public bool DeleteContact(int contactId) {  
  27.         return this.ContactDataAccess.DeleteContact(contactId);  
  28.     }  
  29. }  


Changes in WebApplicationCore.NetCore.Startup
  • Open Startup.cs in WebApplicationCore.NetCore project.

  • open WebApplicationCore.NetCore.Startup and add service dependencies in ConfigureServices method for Configuration, ContactDataAccess and ContactBusinessLogic.

  • Update ContractController class to use ContactBusinessLogic object available from constructor through dependency injection. 
  1. public void ConfigureServices(IServiceCollection services) {  
  2.     // Add framework services.  
  3.     services.AddMvc();  
  4.     services.AddSingleton < IConfigurationRoot > (sp => {  
  5.         return this.Configuration;  
  6.     });  
  7.     services.AddScoped < IContactDataAccess, ContactDataAccess > ();  
  8.     services.AddScoped < IContactBusinessLogic, ContactBusinessLogic > ();  
  9. }  
  10.   
  11.   
  12.   
  13.   
  14. public class ContactController: Controller {  
  15.     private IContactBusinessLogic ContactBusinessLogic;  
  16.     private IConfigurationRoot Configuration;  
  17.     private IContactDataAccess ContactDataAccess;  
  18.   
  19.     public ContactController(IContactBusinessLogic contactBusinessLogic, IContactDataAccess contactDataAccess, IConfigurationRoot configuration) {  
  20.         this.ContactBusinessLogic = contactBusinessLogic;  
  21.         this.ContactDataAccess = contactDataAccess;  
  22.         this.Configuration = configuration;  
  23.     }  
  24.   
  25.     // GET: /<controller>/  
  26.     public IActionResult Index() {  
  27.         List < Contact > contacts = this.ContactBusinessLogic.GetContacts();  
  28.         List < ContactListVM > contactVMs = new List < ContactListVM > ();  
  29.         ContactListVM contactVM;  
  30.   
  31.         foreach(Contact contact in contacts) {  
  32.             contactVM = new ContactListVM {  
  33.                 ContactId = contact.ContactId,  
  34.                     ContactNumber = contact.ContactNumber,  
  35.                     Email = contact.Email,  
  36.                     Name = contact.Name,  
  37.                     WebSite = contact.WebSite  
  38.             };  
  39.             contactVMs.Add(contactVM);  
  40.         }  
  41.   
  42.         return View(contactVMs);  
  43.     }  
  44.   
  45.     public IActionResult GetContact(int id) {  
  46.         Contact contact = this.ContactBusinessLogic.GetContact(id);  
  47.   
  48.         ContactVM contactVM = new ContactVM {  
  49.             Address1 = contact.Address1,  
  50.                 Address2 = contact.Address2,  
  51.                 City = contact.City,  
  52.                 ContactId = contact.ContactId,  
  53.                 ContactNumber = contact.ContactNumber,  
  54.                 Country = contact.Country,  
  55.                 Email = contact.Email,  
  56.                 Name = contact.Name,  
  57.                 ProvinceState = contact.ProvinceState,  
  58.                 WebSite = contact.WebSite,  
  59.                 ZipPostalCode = contact.ZipPostalCode  
  60.         };  
  61.   
  62.         return View(contactVM);  
  63.     }  
  64.   
  65.     [HttpGet]  
  66.     public IActionResult CreateContact() {  
  67.         ContactVM contactVM = new ContactVM();  
  68.   
  69.         return View(contactVM);  
  70.     }  
  71.   
  72.     [HttpPost]  
  73.     public IActionResult CreateContact(ContactVM contactVM) {  
  74.         if (this.ModelState.IsValid) {  
  75.             Contact contact = new Contact {  
  76.                 Address1 = contactVM.Address1,  
  77.                     Address2 = contactVM.Address2,  
  78.                     City = contactVM.City,  
  79.                     ContactId = contactVM.ContactId,  
  80.                     ContactNumber = contactVM.ContactNumber,  
  81.                     Country = contactVM.Country,  
  82.                     Email = contactVM.Email,  
  83.                     Name = contactVM.Name,  
  84.                     ProvinceState = contactVM.ProvinceState,  
  85.                     WebSite = contactVM.WebSite,  
  86.                     ZipPostalCode = contactVM.ZipPostalCode  
  87.             };  
  88.   
  89.             contact = this.ContactBusinessLogic.CreateContact(contact);  
  90.   
  91.             if (contact.ContactId > 0) {  
  92.                 return RedirectToAction("Index");  
  93.             }  
  94.         }  
  95.   
  96.         return View(contactVM);  
  97.     }  
  98.   
  99.     [HttpGet]  
  100.     public IActionResult UpdateContact(int id) {  
  101.         Contact contact = this.ContactBusinessLogic.GetContact(id);  
  102.   
  103.         ContactVM contactVM = new ContactVM {  
  104.             Address1 = contact.Address1,  
  105.                 Address2 = contact.Address2,  
  106.                 City = contact.City,  
  107.                 ContactId = contact.ContactId,  
  108.                 ContactNumber = contact.ContactNumber,  
  109.                 Country = contact.Country,  
  110.                 Email = contact.Email,  
  111.                 Name = contact.Name,  
  112.                 ProvinceState = contact.ProvinceState,  
  113.                 WebSite = contact.WebSite,  
  114.                 ZipPostalCode = contact.ZipPostalCode  
  115.         };  
  116.   
  117.         return View(contactVM);  
  118.     }  
  119.   
  120.     [HttpPost]  
  121.     public IActionResult UpdateContact(ContactVM contactVM) {  
  122.         if (this.ModelState.IsValid) {  
  123.             Contact contact = new Contact {  
  124.                 Address1 = contactVM.Address1,  
  125.                     Address2 = contactVM.Address2,  
  126.                     City = contactVM.City,  
  127.                     ContactId = contactVM.ContactId,  
  128.                     ContactNumber = contactVM.ContactNumber,  
  129.                     Country = contactVM.Country,  
  130.                     Email = contactVM.Email,  
  131.                     Name = contactVM.Name,  
  132.                     ProvinceState = contactVM.ProvinceState,  
  133.                     WebSite = contactVM.WebSite,  
  134.                     ZipPostalCode = contactVM.ZipPostalCode  
  135.             };  
  136.   
  137.             if (this.ContactBusinessLogic.UpdateContact(contact)) {  
  138.                 return RedirectToAction("Index");  
  139.             }  
  140.         }  
  141.   
  142.         return View(contactVM);  
  143.     }  
  144.   
  145.     [HttpGet]  
  146.     public IActionResult DeleteContact(int id) {  
  147.         ContactVM contactVM = new ContactVM {  
  148.             ContactId = id  
  149.         };  
  150.   
  151.         return View(contactVM);  
  152.     }  
  153.   
  154.     [HttpPost]  
  155.     public IActionResult DeleteContact(ContactVM contactVM) {  
  156.         this.ContactBusinessLogic.DeleteContact(contactVM.ContactId);  
  157.   
  158.         return RedirectToAction("Index");  
  159.     }  
  160. }  


Run Application in Debug Mode
  • Press F5 or Debug Menu >> Start Debugging or Start IIS Express Button on Toolbar to start application in debugging mode.

  • It will show Home Page in browser.

  • Click Contact List Menu Open to open the Contact List Page, so we have performed Get All operation.

  • Now, perform all of following operations,

    • Create New Contact.
    • Open Details of a Contact.
    • Edit and Update a Contact.
    • Delete a Contact.
    • We can observe that changes are persistent as now values have been stored in database.
Sample Contact Database

We are using a test database named ContactDB having single table Contact and Stored Procedures Contact_GetAll, Contact_Get, Contact_Create, Contact_Update and Contact_Delete for GetAll, Get, Create, Update and Delete operations respectively. We have Scripts available to create this database in Solution as "ContractDB.sql", and to insert test data as "ContactTestData.sql". We are using SQL Server 2014 Express, you can download it from MSDN. We are not going to discuss SQL Server Database in this article.



Sample Source Code

I have placed the sample code for this session in "CRUD operations in ASP.NET Core 1.0 MVC Application Part 7_Code.zip" in CodePlex repository.


Similar Articles