loading...
Cover image for An Elegant Data Access Tutorial (YouTube)

An Elegant Data Access Tutorial (YouTube)

kironroy profile image Kiron Roy ・5 min read

In page navigation, jump to a topic
The bigger picture
Code sample
SQL data access

Repository is available here

The tutorial

Tim Corey's Data Access in C# is a great tutorial on the following:

  • Create A MVC web application (User Interface)
  • Create a User Interface class library
  • Create a Business Logic / Data Access class library
  • Create a database within Visual Studio

The bigger picture

This tutorial is not a full application, but is a great practice piece.

  1. This Model Control View (MVC) will have a form that will collect data
  2. The form will capture the data and put the data into a database
  3. The data can then be brought back from the database with another form

The Solution will have three projects. A solution is simply a container used by Visual Studio to organize one or more related projects Source.

It is common for solutions to contain more than one project, and often these projects reference each other. Some projects in a solution might be class libraries, some executable applications, and some might be unit test projects or websites Source.

These three projects work together to create the application:

  • DataLibrary: This is where Business Logic and Data Access code go
  • MVCApp: This is where the user interface markup and code go (in this case, a website)
  • MVCDemoDB: This the database that stores the information

Here is the page where a user can view the employees

Videdata

Sample Code

This model is for the user interface. The bracket annotations: [Display(Name = "Employee ID")] are for client-side validation. These validations access the jQuery (JavaScript) library.

using System.ComponentModel.DataAnnotations;

namespace MVCApp.Models
{
    // this is a UI Model
    public class EmployeeModel
    {
        [Display(Name = "Employee ID")]
        [Range(100000, 999999, ErrorMessage ="You need to enter a valid Employee Id")]
        public int EmployeeId { get; set; }

        [Display(Name = "First Name")]
        [Required(ErrorMessage ="You need to give us your first name.")]
        public string FirstName { get; set; }

        [Display(Name = "Last Name")]
        [Required(ErrorMessage = "You need to give us your last name.")]
        public string LastName { get; set; }

        [DataType(DataType.EmailAddress)]
        [Display(Name = "Emaill Address")]
        [Required(ErrorMessage = "You need to give us your email address.")]
        public string EmailAddress { get; set; }

        [Display(Name = "Confirm Emaill")]
        [Compare("EmailAddress", ErrorMessage = "The Email and Confirm Email must match.")]
        public string ConfirmEmail { get; set; }

        [Display(Name = "Password")]
        [Required(ErrorMessage = "You must have a password.")]
        [DataType(DataType.Password)]
        [StringLength(100, MinimumLength = 10, ErrorMessage ="You need to provide a long enough password")]
        public string Password { get; set; }

        [Display(Name = "Confirm Password")]
        [DataType(DataType.Password)]
        [Compare("Password", ErrorMessage = "Your password and confirm password do not match")]
        public string ConfirmPassword { get; set; }

    }
}

This model is for data access. It only has essential properties that the database cares about

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataLibrary.Models
{
    // this is data access model
    public class EmployeeModel
    {
        public int Id { get; set; }
        public int EmployeeId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string EmailAddress { get; set; }

    }
}

Home Controller (top code omitted) [ValidateAntiForgeryToken] This is an C# annotation for validation:


using MVCApp.Models;
using System.Collections.Generic;
using System.Web.Mvc;
using static DataLibrary.BusinessLogic.EmployeeProcessor;

...

public ActionResult ViewEmployees()
        {
            ViewBag.Message = "Employees List";

            var data = LoadEmployees();
            List<EmployeeModel> employees = new List<EmployeeModel>();
            foreach (var row in data)
            {
                employees.Add(new EmployeeModel
                {
                    EmployeeId = row.EmployeeId,
                    FirstName = row.FirstName,
                    LastName = row.LastName,
                    EmailAddress = row.EmailAddress,
                    ConfirmEmail = row.EmailAddress
                });
            }

            return View(employees);
        }

       // load data from user
        public ActionResult SignUp()
        {
            ViewBag.Message = "Employee Sign Up";

            return View();
        }

        // data sent to this method
        // this method has a second layer of validation
         // id user turns Js off

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult SignUp(EmployeeModel model)
        {
            if (ModelState.IsValid)
            {
                // Class from the DataLibrary project 
                // - BusinessLogic Folder 
                // - using statement line 3
                // - CreateEmployee() method
               int recordsCreated = CreateEmployee(model.EmployeeId
                    , model.FirstName
                    , model.LastName
                    , model.EmailAddress);
                return RedirectToAction("Index");
            }

            return View();
        }
    }
}

SQL Access (1) Setting up the connection (2) SQL queries

using System.Collections.Generic;
using System.Linq;
using Dapper;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DataLibrary.DataAccess
{
    // this class is where the actual data access to the datbabse occurs 
    // this class is static because it doesn't store data
    public static class SqlDataAccess
    {
        public static string GetConnectionString(string connectionName = "MVCDemoDB")
        {
            return ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
        }

       // 1. load data
       // 2. here is the model to load into (T)
       // 3. return back a list of that model (T)
       // 4. connect to this sql, give me this sql
       // 5. execute that sql and load that query into type T
       // 6. query returns INumerable
       // 7. convert INumerable to List
       // 8. _done_

        public static List<T> LoadData<T>(string sql) 
        {
            using (IDbConnection cnn = new SqlConnection(GetConnectionString()))
            {
                return cnn.Query<T>(sql).ToList();
            }
        }

        public static int SaveData<T>(string sql, T data)
        {
            using (IDbConnection cnn = new SqlConnection(GetConnectionString()))
            {
                return cnn.Execute(sql, data);
            }
        }
    }
}

SQL queries are stored in the appropriately named file: EmployeeProcessor.cs

This is where we process a Employee and read the Employees from the User Interface (The website).

using DataLibrary.DataAccess;
using DataLibrary.Models;
using System.Collections.Generic;

namespace DataLibrary.BusinessLogic
{
    // this class is static, not storing data
    public static class EmployeeProcessor
    {
        public static int CreateEmployee(int employeeId, string firstName, 
            string lastName, string emailAddress)
        {
            // mapped one model to another model
            EmployeeModel data = new EmployeeModel
            {
                EmployeeId = employeeId,
                FirstName = firstName,
                LastName = lastName,
                EmailAddress = emailAddress
            };

            // insert to database
             // the sql statement
            string sql = @"Insert into dbo.Employee (EmployeeID, FirstName, LastName, EmailAddress)
                           values(@EmployeeId, @FirstName, @LastName, @EmailAddress);";

            return SqlDataAccess.SaveData(sql, data);
        }

        // this is the Business logic data access Employee Model
        public static List<EmployeeModel> LoadEmployees()
        {
            // this statement just returns everything
            string sql = @"select Id, EmployeeId, FirstName, LastName, EmailAddress
                         from dbo.Employee;";

            // load all our employees back
            return SqlDataAccess.LoadData<EmployeeModel>(sql);
        }
    }
}

Posted on by:

Discussion

markdown guide