Learning Path Details

Foundation in C#: Data Access from Tim Corey

Description:

Module 8 of the Complete Foundation in C# Course Series, we focus on data access (and data storage), from a user-interface-independent perspective. We learn both SQL types (SQL Server, SQLite, and MySQL) and NoSQL types (Mongo DB and CosmosDB), along with text file access, API consumption, using LINQ and lambda expressions, and an introduction to Entity Framework Core.

Key Takeaways:

By separating the data access layer from the user interface layer, the user interface does not need to change when the data access type does.

For each data access type, after Tim's demonstration, we are asked to build a different application (with a different schema) using the same access type, to demonstrate what we have learned.

Technologies Learned:

.NET;
.NET Core;
.NET Core 3.0;
.NET Core 3.1;
ASP.NET Core Web API;
ASP.NET Core Web Application (Razor Pages);
C#;
Class Library;
Console Application;
CosmosDB;
Dapper;
Entity Framework;
Lambda Expressions;
LINQ;
Mongo DB;
MySQL;
SQL;
SQL Server Database Project;
SQLite;
Text File Data Access;

This learning resource was completed on 12/21/2021.

Find the Resource here:

https://www.iamtimcorey.com/allcourses/

Practice Projects

Entity Framework Core

Description:

The homework from Lesson 14, Entity Framework Core, of Module 8, Data Access, of the Complete Foundation in C# Course Series from Tim Corey. We are to build a simple database using code first. Create models for People, Addresses, and Employers. Make sure it builds and that you can load and save data.

Technologies Used:

.NET;
.NET Core;
.NET Core 3.1;
C#;
Class Library;
Console Application;
Entity Framework;

Find the Project here:

https://github.com/Spartan-CSharp/DataAccess-EntityFrameworkCore

Code Snippet:

using System.Collections.Generic;
using System.Linq;

using DataAccessLibrary.Models;

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace DataAccessLibrary.EFDataAccess
{
	public class EFCrud : ICrud
	{
		private readonly IConfiguration _configuration;
		private readonly string _connectionStringName;

		public EFCrud(IConfiguration configuration, string connectionStringName)
		{
			_configuration = configuration;
			_connectionStringName = connectionStringName;
		}

		public void CreatePerson(Person person)
		{
			using ( EFContext db = new EFContext() )
			{
				_ = db.People.Add(person);
				_ = db.SaveChanges();
			}
		}

		public void CreateEmployer(Employer employer)
		{
			using ( EFContext db = new EFContext() )
			{
				_ = db.Employers.Add(employer);
				_ = db.SaveChanges();
			}
		}
		public void CreateAddress(Address address)
		{
			using ( EFContext db = new EFContext() )
			{
				_ = db.Addresses.Add(address);
				_ = db.SaveChanges();
			}
		}

		public List<Person> RetrieveAllPeople()
		{
			using ( EFContext db = new EFContext() )
			{
				List<Person> output = db.People.Include(e => e.Employer).Include(a => a.Addresses).ToList();
				return output;
			}
		}

		public Person RetrievePersonById(int id)
		{
			using ( EFContext db = new EFContext() )
			{
				Person output = db.People.Include(e => e.Employer).Include(a => a.Addresses).Where(p => p.Id == id).First();
				return output;
			}
		}

		public List<Person> RetrievePeopleByEmployerId(int employerId)
		{
			using ( EFContext db = new EFContext() )
			{
				List<Person> output = db.People.Include(e => e.Employer).Include(a => a.Addresses).Where(p => p.Employer.Id == employerId).ToList();
				return output;
			}
		}

		public List<Employer> RetrieveAllEmployers()
		{
			using ( EFContext db = new EFContext() )
			{
				List<Employer> output = db.Employers.Include(a => a.Addresses).ToList();
				return output;
			}
		}

		public Employer RetrieveEmployerById(int id)
		{
			using ( EFContext db = new EFContext() )
			{
				Employer output = db.Employers.Include(a => a.Addresses).Where(e => e.Id == id).First();
				return output;
			}
		}

		public List<Address> RetrieveAllAddresses()
		{
			using ( EFContext db = new EFContext() )
			{
				List<Address> output = db.Addresses.ToList();
				return output;
			}
		}

		public Address RetrieveAddressById(int id)
		{
			using ( EFContext db = new EFContext() )
			{
				Address output = db.Addresses.Where(a => a.Id == id).First();
				return output;
			}
		}

		public void UpdatePerson(Person person)
		{
			using ( EFContext db = new EFContext() )
			{
				Person record = db.People.Include(e => e.Employer).Include(a => a.Addresses).Where(p => p.Id == person.Id).First();

				record.FirstName = person.FirstName;
				record.LastName = person.LastName;
				record.IsActive = person.IsActive;

				record.Addresses.Clear();

				foreach ( Address item in person.Addresses )
				{
					record.Addresses.Add(item);
				}

				record.Employer = person.Employer;

				_ = db.SaveChanges();
			}
		}

		public void UpdateEmployer(Employer employer)
		{
			using ( EFContext db = new EFContext() )
			{
				Employer record = db.Employers.Include(a => a.Addresses).Where(e => e.Id == employer.Id).First();

				record.CompanyName = employer.CompanyName;

				record.Addresses.Clear();

				foreach ( Address item in employer.Addresses )
				{
					record.Addresses.Add(item);
				}

				_ = db.SaveChanges();
			}
		}

		public void UpdateAddress(Address address)
		{
			using ( EFContext db = new EFContext() )
			{
				Address record = db.Addresses.Where(a => a.Id == address.Id).First();

				record.StreetAddress = address.StreetAddress;
				record.City = address.City;
				record.State = address.State;
				record.ZipCode = address.ZipCode;

				_ = db.SaveChanges();
			}
		}

		public void DeletePerson(Person person)
		{
			using ( EFContext db = new EFContext() )
			{
				Person record = db.People.Include(e => e.Employer).Include(a => a.Addresses).Where(p => p.Id == person.Id).First();

				_ = db.People.Remove(record);

				_ = db.SaveChanges();
			}
		}

		public void DeleteEmployer(Employer employer)
		{
			using ( EFContext db = new EFContext() )
			{
				Employer record = db.Employers.Include(a => a.Addresses).Where(e => e.Id == employer.Id).First();

				_ = db.Employers.Remove(record);

				_ = db.SaveChanges();
			}
		}

		public void DeleteAddress(Address address)
		{
			using ( EFContext db = new EFContext() )
			{
				Address record = db.Addresses.Where(a => a.Id == address.Id).First();

				_ = db.Addresses.Remove(record);

				_ = db.SaveChanges();
			}
		}
	}
}

Linq/Lambdas

Description:

The homework from Lesson 13, Linq/Lambdas, of Module 8, Data Access, of the Complete Foundation in C# Course Series from Tim Corey. We are to create a list of PersonModel. Filter the list twice – once using the query syntax and once using the method syntax. Perform at least a filter and sort each time.

Technologies Used:

.NET;
.NET Core;
.NET Core 3.1;
C#;
Console Application;

Find the Project here:

https://github.com/Spartan-CSharp/DataAccess-LinqLambdas

Code Snippet:

using System;
using System.Collections.Generic;
using System.Linq;

using LinqLambdaConsoleAppUI.Models;

namespace LinqLambdaConsoleAppUI
{
	internal class Program
	{
		private static void Main()
		{
			Console.WriteLine("Query Syntax:");
			RunQueries();
			Console.WriteLine();
			Console.WriteLine("Method Syntax:");
			RunMethods();
			Console.WriteLine();
			Console.WriteLine("Done Processing!");
			_ = Console.ReadLine();
		}

		private static void RunQueries()
		{
			List<PersonModel> people = SampleData.GeneratePersonModels();
			List<EmployerModel> employers = SampleData.GenerateEmployerModels();
			List<AddressModel> addresses = SampleData.GenerateAddressModels();

			IOrderedEnumerable<PersonModel> results = from p in people where p.Addresses.Count() > 1 orderby p.LastName select p;
			foreach ( PersonModel item in results )
			{
				Console.WriteLine($"{item.FirstName} {item.LastName} - {item.Addresses.Count()}");
			}
		}

		private static void RunMethods()
		{
			List<PersonModel> people = SampleData.GeneratePersonModels();
			List<EmployerModel> employers = SampleData.GenerateEmployerModels();
			List<AddressModel> addresses = SampleData.GenerateAddressModels();

			IOrderedEnumerable<PersonModel> results = people.Where(x => x.Addresses.Count > 1).OrderBy(x => x.LastName).ThenBy(x => x.FirstName);
			foreach ( PersonModel item in results )
			{
				Console.WriteLine($"{item.FirstName} {item.LastName} - {item.Addresses.Count()}");
			}
		}
	}
}

APIs

Description:

The homework from Lesson 12, APIs, of Module 8, Data Access, of the Complete Foundation in C# Course Series from Tim Corey. We are to call the SWAPI (https://swapi.co) for a person and load models for the person and then load the models for each film they were in.

Technologies Used:

.NET;
.NET Core;
.NET Core 3.1;
ASP.NET Web Application (Razor Pages);
C#;

Find the Project here:

https://github.com/Spartan-CSharp/DataAccess-APIs

Code Snippet:

using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text.Json;
using System.Threading.Tasks;

using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Logging;

using SWAPIWebApplication.Models;

namespace SWAPIWebApplication.Pages
{
	public class SWAPIModel : PageModel
	{
		private readonly ILogger<SWAPIModel> _logger;
		private readonly HttpClient _httpClient;

		public PersonModel Person { get; set; }
		public List<FilmModel> Films { get; set; } = new List<FilmModel>();

		public SWAPIModel(ILogger<SWAPIModel> logger, IHttpClientFactory httpClientFactory)
		{
			_logger = logger;
			_httpClient = httpClientFactory.CreateClient();
			_httpClient.DefaultRequestHeaders.Clear();
			_httpClient.DefaultRequestHeaders.Accept.Clear();
			_httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
		}

		public async Task OnGetAsync()
		{
			_logger.LogInformation("OnGetAsync SWAPI Page");
			Person = await GetPersonAsync("https://swapi.dev/api/people/1");
			foreach ( string filmUrl in Person.Films )
			{
				FilmModel film = await GetFilmAsync(filmUrl);
				Films.Add(film);
			}
		}

		private async Task<PersonModel> GetPersonAsync(string personUrl)
		{
			using ( HttpResponseMessage response = await _httpClient.GetAsync(personUrl) )
			{
				if ( response.IsSuccessStatusCode )
				{
					JsonSerializerOptions options = new JsonSerializerOptions
					{
						PropertyNameCaseInsensitive = true
					};
					string responseText = await response.Content.ReadAsStringAsync();
					PersonModel person = JsonSerializer.Deserialize<PersonModel>(responseText, options);
					return person;
				}
				else
				{
					throw new ApplicationException(response.ReasonPhrase);
				}
			}
		}

		private async Task<FilmModel> GetFilmAsync(string filmUrl)
		{
			using ( HttpResponseMessage response = await _httpClient.GetAsync(filmUrl) )
			{
				if ( response.IsSuccessStatusCode )
				{
					JsonSerializerOptions options = new JsonSerializerOptions
					{
						PropertyNameCaseInsensitive = true
					};
					string responseText = await response.Content.ReadAsStringAsync();
					FilmModel film = JsonSerializer.Deserialize<FilmModel>(responseText, options);
					return film;
				}
				else
				{
					throw new ApplicationException(response.ReasonPhrase);
				}
			}
		}
	}
}

Text Files

Description:

The homework from Lesson 11, Text Files, of Module 8, Data Access, of the Complete Foundation in C# Course Series from Tim Corey. We are to build a CSV file that holds Person information. Load the file into models in C# and save the data from C# back into the CSV file.

Technologies Used:

.NET;
.NET Core;
.NET Core 3.1;
C#;
Class Library;
Console Application;

Find the Project here:

https://github.com/Spartan-CSharp/DataAccess-TextFiles

Code Snippet:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

using DataAccessLibrary.Models;

namespace DataAccessLibrary.TextFileDataAccess
{
	public class TextFileCRUD : ICrud
	{
		private readonly string _filePath;

		public TextFileCRUD(string fileName)
		{
			string filePath = Directory.GetCurrentDirectory();
			_filePath = filePath + "\\" + fileName;
		}

		public void CreatePerson(PersonModel person)
		{
			List<string> addresses = new List<string>();
			foreach ( AddressModel address in person.Addresses )
			{
				addresses.Add($"{address.StreetAddress}|{address.City}|{address.State}|{address.ZipCode}");
			}

			string addressString = string.Join(";", addresses);
			string employerString = string.Empty;
			if ( person.Employer != null )
			{
				employerString += person.Employer.CompanyName + "^";
				List<string> employerAddresses = new List<string>();
				foreach ( AddressModel address in person.Addresses )
				{
					employerAddresses.Add($"{address.StreetAddress}|{address.City}|{address.State}|{address.ZipCode}");
				}

				employerString += string.Join(";", employerAddresses);
			}

			string personString = $"{person.Id},{person.FirstName},{person.LastName},{person.IsActive},{employerString},{addressString}";

			List<string> existingLines = TextFileDataAccess.ReadAllCSVEntries(_filePath);

			existingLines.Add(personString);

			TextFileDataAccess.WriteAllCSVEntries(_filePath, existingLines);
		}

		public void DeletePerson(PersonModel person)
		{
			List<PersonModel> people = RetrieveAllPeople();

			people.RemoveAt(people.FindIndex(x => x.Id == person.Id));

			List<string> updateList = new List<string>();

			foreach ( PersonModel p in people )
			{
				List<string> addresses = new List<string>();
				foreach ( AddressModel address in p.Addresses )
				{
					addresses.Add($"{address.StreetAddress}|{address.City}|{address.State}|{address.ZipCode}");
				}

				string addressString = string.Join(";", addresses);
				string employerString = string.Empty;
				if ( p.Employer != null )
				{
					employerString += p.Employer.CompanyName + "^";
					List<string> employerAddresses = new List<string>();
					foreach ( AddressModel address in p.Addresses )
					{
						employerAddresses.Add($"{address.StreetAddress}|{address.City}|{address.State}|{address.ZipCode}");
					}

					employerString += string.Join(";", employerAddresses);
				}

				string personString = $"{p.Id},{p.FirstName},{p.LastName},{p.IsActive},{employerString},{addressString}";
				updateList.Add(personString);
			}

			TextFileDataAccess.WriteAllCSVEntries(_filePath, updateList);
		}

		public List<PersonModel> RetrieveAllPeople()
		{
			List<PersonModel> output = new List<PersonModel>();
			List<string> existingLines = TextFileDataAccess.ReadAllCSVEntries(_filePath);

			foreach ( string line in existingLines )
			{
				string[] cols = line.Split(',');
				if ( cols.Length != 6 )
				{
					throw new ApplicationException($"Bad Line in CSV File {_filePath}: {line}");
				}

				PersonModel person = new PersonModel
				{
					Id = new Guid(cols[0]),
					FirstName = cols[1],
					LastName = cols[2],
					IsActive = cols[3].ToLower() == "true"
				};

				if ( cols[4].Length > 0 )
				{
					// split out the employer data
					string[] employerCols = cols[4].Split('^');
					if ( employerCols.Length != 2 )
					{
						throw new ApplicationException($"Bad Employer in CSV File {_filePath} Line {line}: {cols[4]}");
					}

					person.Employer = new EmployerModel
					{
						CompanyName = employerCols[0]
					};

					string[] addresses = employerCols[1].Split(';');
					foreach ( string address in addresses )
					{
						string[] addressCols = address.Split('|');
						if ( addressCols.Length != 4 )
						{
							throw new ApplicationException($"Bad Employer Address in CSV File {_filePath} Line {line}, Employer {cols[4]}: {address}");
						}

						AddressModel addressModel = new AddressModel
						{
							StreetAddress = addressCols[0],
							City = addressCols[1],
							State = addressCols[2],
							ZipCode = addressCols[3]
						};

						person.Employer.Addresses.Add(addressModel);
					}
				}

				if ( cols[5].Length > 0 )
				{
					string[] addresses = cols[5].Split(';');
					foreach ( string address in addresses )
					{
						string[] addressCols = address.Split('|');
						if ( addressCols.Length != 4 )
						{
							throw new ApplicationException($"Bad Address in CSV File {_filePath} Line {line}: {address}");
						}

						AddressModel addressModel = new AddressModel
						{
							StreetAddress = addressCols[0],
							City = addressCols[1],
							State = addressCols[2],
							ZipCode = addressCols[3]
						};

						person.Addresses.Add(addressModel);
					}
				}

				output.Add(person);
			}

			return output;
		}

		public PersonModel RetrievePersonById(Guid id)
		{
			PersonModel output = RetrieveAllPeople().FirstOrDefault(x => x.Id == id);
			return output ?? throw new ApplicationException($"Person with Id of {id} not found in file {_filePath}");
		}

		public void UpdatePerson(PersonModel person)
		{
			List<PersonModel> people = RetrieveAllPeople();

			PersonModel personToUpdate = people.FirstOrDefault(x => x.Id == person.Id) ?? throw new ApplicationException($"Person with Id of {person.Id} not found in file {_filePath}");

			personToUpdate = person;

			List<string> updateList = new List<string>();

			foreach ( PersonModel p in people )
			{
				List<string> addresses = new List<string>();
				foreach ( AddressModel address in p.Addresses )
				{
					addresses.Add($"{address.StreetAddress}|{address.City}|{address.State}|{address.ZipCode}");
				}

				string addressString = string.Join(";", addresses);
				string employerString = string.Empty;
				if ( p.Employer != null )
				{
					employerString += p.Employer.CompanyName + "^";
					List<string> employerAddresses = new List<string>();
					foreach ( AddressModel address in p.Addresses )
					{
						employerAddresses.Add($"{address.StreetAddress}|{address.City}|{address.State}|{address.ZipCode}");
					}

					employerString += string.Join(";", employerAddresses);
				}

				string personString = $"{p.Id},{p.FirstName},{p.LastName},{p.IsActive},{employerString},{addressString}";
				updateList.Add(personString);
			}

			TextFileDataAccess.WriteAllCSVEntries(_filePath, updateList);
		}
	}
}

CosmosDB

Description:

The homework from the Lesson 10, CosmosDB, of Module 8, Data Access, of the Complete Foundation in C# Course Series from Tim Corey. We are to build a simple database in CosmosDB that holds People, Addresses, and Employers. Make sure it builds and that you can load and save data in C#.

Technologies Used:

.NET;
.NET Core;
.NET Core 3.1;
C#;
Class Library;
Console Application;
CosmosDB;

Find the Project here:

https://github.com/Spartan-CSharp/DataAccess-CosmosDB

Code Snippet:

using System;
using System.Collections.Generic;
using System.Threading.Tasks;

using DataAccessLibrary.Models;

using Microsoft.Azure.Cosmos;

namespace DataAccessLibrary.CosmosDBDataAccess
{
	public class CosmosDBCrud : ICrud
	{
		private readonly Database _database;

		public CosmosDBCrud(string endpointUri, string primaryKey, string databaseName)
		{
			CosmosClient cosmosClient = new CosmosClient(endpointUri, primaryKey);
			_database = cosmosClient.GetDatabase(databaseName);
		}

		public async Task CreateAddressAsync(AddressModel address)
		{
			await _database.CreateRecordAsync("Addresses", address);
		}

		public async Task CreateEmployerAsync(EmployerModel employer)
		{
			await _database.CreateRecordAsync("Employers", employer);
		}

		public async Task CreatePersonAsync(PersonModel person)
		{
			await _database.CreateRecordAsync("People", person);
		}

		public async Task DeleteAddressAsync(AddressModel address)
		{
			await _database.DeleteRecordAsync<AddressModel>("Addresses", address.Id.ToString(), new PartitionKey(address.ZipCode));
		}

		public async Task DeleteEmployerAsync(EmployerModel employer)
		{
			await _database.DeleteRecordAsync<AddressModel>("Employers", employer.Id.ToString(), new PartitionKey(employer.CompanyName));
		}

		public async Task DeletePersonAsync(PersonModel person)
		{
			await _database.DeleteRecordAsync<AddressModel>("People", person.Id.ToString(), new PartitionKey(person.LastName));
		}

		public async Task<AddressModel> RetrieveAddressByIdAsync(Guid id)
		{
			AddressModel output = await _database.RetrieveRecordByIdAsync<AddressModel>("Addresses", id.ToString());
			return output;
		}

		public async Task<List<AddressModel>> RetrieveAllAddressesAsync()
		{
			List<AddressModel> output = await _database.RetrieveRecordsAsync<AddressModel>("Addresses");
			return output;
		}

		public async Task<List<EmployerModel>> RetrieveAllEmployersAsync()
		{
			List<EmployerModel> output = await _database.RetrieveRecordsAsync<EmployerModel>("Employers");
			return output;
		}

		public async Task<List<PersonModel>> RetrieveAllPeopleAsync()
		{
			List<PersonModel> output = await _database.RetrieveRecordsAsync<PersonModel>("People");
			return output;
		}

		public async Task<EmployerModel> RetrieveEmployerByIdAsync(Guid id)
		{
			EmployerModel output = await _database.RetrieveRecordByIdAsync<EmployerModel>("Employers", id.ToString());
			return output;
		}

		public async Task<List<PersonModel>> RetrievePeopleByEmployerIdAsync(Guid employerId)
		{
			List<PersonModel> output = (await RetrieveAllPeopleAsync()).FindAll(x => x.Employer?.Id == employerId);
			return output;
		}

		public async Task<PersonModel> RetrievePersonByIdAsync(Guid id)
		{
			PersonModel output = await _database.RetrieveRecordByIdAsync<PersonModel>("People", id.ToString());
			return output;
		}

		public async Task UpdateAddressAsync(AddressModel address)
		{
			await _database.UpdateRecordAsync("Addresses", address);
		}

		public async Task UpdateEmployerAsync(EmployerModel employer)
		{
			await _database.UpdateRecordAsync("Employers", employer);
		}

		public async Task UpdatePersonAsync(PersonModel person)
		{
			await _database.UpdateRecordAsync("People", person);
		}
	}
}

MongoDB

Description:

The homework from the Lesson 9, MongoDB, of Module 8, Data Access, of the Complete Foundation in C# Course Series from Tim Corey. We are to build a simple database in MongoDB that holds People, Addresses, and Employers. Make sure it builds and that you can load and save data in C#.

Technologies Used:

.NET;
.NET Core;
.NET Core 3.1;
C#;
Class Library;
Console Application;
MongoDB;

Find the Project here:

https://github.com/Spartan-CSharp/DataAccess-MongoDB

Code Snippet:

using System;
using System.Collections.Generic;

using DataAccessLibrary.Models;

using MongoDB.Driver;

namespace DataAccessLibrary.MongoDBDataAccess
{
	public class MongoDBCrud : ICrud
	{
		private readonly IMongoDatabase _mongoDatabase;

		public MongoDBCrud(string databaseName, string connectionString)
		{
			MongoClient client = new MongoClient(connectionString);
			_mongoDatabase = client.GetDatabase(databaseName);
		}

		public void CreateAddress(AddressModel address)
		{
			_mongoDatabase.CreateRecord("Addresses", address);
		}

		public void CreateEmployer(EmployerModel employer)
		{
			_mongoDatabase.CreateRecord("Employers", employer);
		}

		public void CreatePerson(PersonModel person)
		{
			_mongoDatabase.CreateRecord("People", person);
		}

		public void DeleteAddress(AddressModel address)
		{
			_mongoDatabase.DeleteRecord<AddressModel>("Addresses", address.Id);
		}

		public void DeleteEmployer(EmployerModel employer)
		{
			_mongoDatabase.DeleteRecord<EmployerModel>("Employers", employer.Id);
		}

		public void DeletePerson(PersonModel person)
		{
			_mongoDatabase.DeleteRecord<PersonModel>("People", person.Id);
		}

		public AddressModel RetrieveAddressById(Guid id)
		{
			AddressModel output = _mongoDatabase.RetrieveRecordById<AddressModel>("Addresses", id);
			return output;
		}

		public List<AddressModel> RetrieveAllAddresses()
		{
			List<AddressModel> output = _mongoDatabase.RetrieveRecords<AddressModel>("Addresses");
			return output;
		}

		public List<EmployerModel> RetrieveAllEmployers()
		{
			List<EmployerModel> output = _mongoDatabase.RetrieveRecords<EmployerModel>("Employers");
			return output;
		}

		public List<PersonModel> RetrieveAllPeople()
		{
			List<PersonModel> output = _mongoDatabase.RetrieveRecords<PersonModel>("People");
			return output;
		}

		public EmployerModel RetrieveEmployerById(Guid id)
		{
			EmployerModel output = _mongoDatabase.RetrieveRecordById<EmployerModel>("Employers", id);
			return output;
		}

		public List<PersonModel> RetrievePeopleByEmployerId(Guid employerId)
		{
			List<PersonModel> output = RetrieveAllPeople().FindAll(x => x.Employer?.Id == employerId);
			return output;
		}

		public PersonModel RetrievePersonById(Guid id)
		{
			PersonModel output = _mongoDatabase.RetrieveRecordById<PersonModel>("People", id);
			return output;
		}

		public void UpdateAddress(AddressModel address)
		{
			_mongoDatabase.UpdateRecord("Addresses", address.Id, address);
		}

		public void UpdateEmployer(EmployerModel employer)
		{
			_mongoDatabase.UpdateRecord("Employers", employer.Id, employer);
		}

		public void UpdatePerson(PersonModel person)
		{
			_mongoDatabase.UpdateRecord("People", person.Id, person);
		}
	}
}

MySQL

Description:

The homework from the Lesson 7, MySQL, of Module 8, Data Access, of the Complete Foundation in C# Course Series from Tim Corey. We are to build a simple database in MySQL that holds People, Addresses, and Employers. Make sure it builds and that you can load and save data in C#.

Technologies Used:

.NET;
.NET Core;
.NET Core 3.1;
C#;
Class Library;
Console Application;
MySQL;

Find the Project here:

https://github.com/Spartan-CSharp/DataAccess-MySQL

Code Snippet:

using System.Collections.Generic;
using System.Linq;

using Dapper;

using DataAccessLibrary.Models;

namespace DataAccessLibrary.MySQLDataAccess
{
	public class MySQLCrud : ICrud
	{
		private readonly string _connectionString;

		public MySQLCrud(string connectionString)
		{
			_connectionString = connectionString;
		}

		public void CreatePerson(PersonBaseModel person)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@FirstName", person.FirstName);
			parameters.Add("@LastName", person.LastName);
			parameters.Add("@IsActive", person.IsActive);
			parameters.Add("@EmployerId", person.EmployerId);
			string sqlstatement = "INSERT INTO People (FirstName, LastName, IsActive, EmployerId) VALUES (@FirstName, @LastName, @IsActive, @EmployerId);";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT Id FROM People WHERE FirstName = @FirstName AND LastName = @LastName AND IsActive = @IsActive AND EmployerId = @EmployerId;";
			person.Id = MySQLDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreateEmployer(EmployerBaseModel employer)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@CompanyName", employer.CompanyName);
			string sqlstatement = "INSERT INTO Employers (CompanyName) VALUES (@CompanyName);";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT Id FROM Employers WHERE CompanyName = @CompanyName;";
			employer.Id = MySQLDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreateAddress(AddressModel address)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@StreetAddress", address.StreetAddress);
			parameters.Add("@City", address.City);
			parameters.Add("@State", address.State);
			parameters.Add("@ZipCode", address.ZipCode);
			string sqlstatement = "INSERT INTO Addresses (StreetAddress, City, State, ZipCode) VALUES (@StreetAddress, @City, @State, @ZipCode);";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT Id FROM Addresses WHERE StreetAddress = @StreetAddress AND City = @City AND State = @State AND ZipCode = @ZipCode;";
			address.Id = MySQLDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreatePersonAddress(PersonAddressBaseModel personAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@PersonId", personAddress.PersonId);
			parameters.Add("@AddressId", personAddress.AddressId);
			string sqlstatement = "INSERT INTO PersonAddresses (PersonId, AddressId) VALUES (@PersonId, @AddressId);";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT Id FROM PersonAddresses WHERE PersonId = @PersonId AND AddressId = @AddressId;";
			personAddress.Id = MySQLDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreateEmployerAddress(EmployerAddressBaseModel employerAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@EmployerId", employerAddress.EmployerId);
			parameters.Add("@AddressId", employerAddress.AddressId);
			string sqlstatement = "INSERT INTO EmployerAddresses (EmployerId, AddressId) VALUES (@EmployerId, @AddressId);";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT Id FROM EmployerAddresses WHERE EmployerId = @EmployerId AND AddressId = @AddressId;";
			employerAddress.Id = MySQLDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public List<PersonBaseModel> RetrieveAllPeople()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT Id, FirstName, LastName, IsActive, EmployerId FROM People;";
			List<PersonBaseModel> output = MySQLDataAccess.ReadData<PersonBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public PersonBaseModel RetrievePersonById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT Id, FirstName, LastName, IsActive, EmployerId FROM People WHERE Id = @Id;";
			PersonBaseModel output = MySQLDataAccess.ReadData<PersonBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<PersonBaseModel> RetrievePeopleByEmployerId(int employerId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@EmployerId", employerId);
			string sqlstatement = "SELECT Id, FirstName, LastName, IsActive, EmployerId FROM People WHERE EmployerId = @EmployerId;";
			List<PersonBaseModel> output = MySQLDataAccess.ReadData<PersonBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<EmployerBaseModel> RetrieveAllEmployers()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT Id, CompanyName FROM Employers;";
			List<EmployerBaseModel> output = MySQLDataAccess.ReadData<EmployerBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public EmployerBaseModel RetrieveEmployerById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT Id, CompanyName FROM Employers WHERE Id = @Id;";
			EmployerBaseModel output = MySQLDataAccess.ReadData<EmployerBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<AddressModel> RetrieveAllAddresses()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT Id, StreetAddress, City, State, ZipCode FROM Addresses;";
			List<AddressModel> output = MySQLDataAccess.ReadData<AddressModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public AddressModel RetrieveAddressById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT Id, StreetAddress, City, State, ZipCode FROM Addresses WHERE Id = @Id;";
			AddressModel output = MySQLDataAccess.ReadData<AddressModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<PersonAddressBaseModel> RetrieveAllPersonAddresses()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT Id, PersonId, AddressId FROM PersonAddresses;";
			List<PersonAddressBaseModel> output = MySQLDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public PersonAddressBaseModel RetrievePersonAddressById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT Id, PersonId, AddressId FROM PersonAddresses WHERE Id = @Id;";
			PersonAddressBaseModel output = MySQLDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<PersonAddressBaseModel> RetrievePersonAddressByPersonId(int personId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@PersonId", personId);
			string sqlstatement = "SELECT Id, PersonId, AddressId FROM PersonAddresses WHERE PersonId = @PersonId;";
			List<PersonAddressBaseModel> output = MySQLDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<PersonAddressBaseModel> RetrievePersonAddressByAddressId(int addressId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@AddressId", addressId);
			string sqlstatement = "SELECT Id, PersonId, AddressId FROM PersonAddresses WHERE AddressId = @AddressId;";
			List<PersonAddressBaseModel> output = MySQLDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<EmployerAddressBaseModel> RetrieveAllEmployerAddresses()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT Id, EmployerId, AddressId FROM EmployerAddresses;";
			List<EmployerAddressBaseModel> output = MySQLDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public EmployerAddressBaseModel RetrieveEmployerAddressById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT Id, EmployerId, AddressId FROM EmployerAddresses WHERE Id = @Id;";
			EmployerAddressBaseModel output = MySQLDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<EmployerAddressBaseModel> RetrieveEmployerAddressByEmployerId(int employerId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@EmployerId", employerId);
			string sqlstatement = "SELECT Id, EmployerId, AddressId FROM EmployerAddresses WHERE EmployerId = @EmployerId;";
			List<EmployerAddressBaseModel> output = MySQLDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<EmployerAddressBaseModel> RetrieveEmployerAddressByAddressId(int addressId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@AddressId", addressId);
			string sqlstatement = "SELECT Id, EmployerId, AddressId FROM EmployerAddresses WHERE AddressId = @AddressId;";
			List<EmployerAddressBaseModel> output = MySQLDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public void UpdatePerson(PersonBaseModel person)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", person.Id);
			parameters.Add("@FirstName", person.FirstName);
			parameters.Add("@LastName", person.LastName);
			parameters.Add("@IsActive", person.IsActive);
			parameters.Add("@EmployerId", person.EmployerId);
			string sqlstatement = "UPDATE People SET FirstName = @FirstName, LastName = @LastName, IsActive = @IsActive, EmployerId = @EmployerId WHERE Id = @Id;";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdateEmployer(EmployerBaseModel employer)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employer.Id);
			parameters.Add("@CompanyName", employer.CompanyName);
			string sqlstatement = "UPDATE Employers SET CompanyName = @CompanyName WHERE Id = @Id;";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdateAddress(AddressModel address)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", address.Id);
			parameters.Add("@StreetAddress", address.StreetAddress);
			parameters.Add("@City", address.City);
			parameters.Add("@State", address.State);
			parameters.Add("@ZipCode", address.ZipCode);
			string sqlstatement = "UPDATE Addresses SET StreetAddress = @StreetAddress, City = @City, State = @State, ZipCode = @ZipCode WHERE Id = @Id;";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdatePersonAddress(PersonAddressBaseModel personAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", personAddress.Id);
			parameters.Add("@PersonId", personAddress.PersonId);
			parameters.Add("@AddressId", personAddress.AddressId);
			string sqlstatement = "UPDATE PersonAddresses SET PersonId = @PersonId, AddresId = @AddressId WHERE Id = @Id;";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdateEmployerAddress(EmployerAddressBaseModel employerAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employerAddress.Id);
			parameters.Add("@EmployerId", employerAddress.EmployerId);
			parameters.Add("@AddressId", employerAddress.AddressId);
			string sqlstatement = "UPDATE EmployerAddresses SET EmployerId = @EmployerId, AddresId = @AddressId WHERE Id = @Id;";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeletePerson(PersonBaseModel person)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", person.Id);
			string sqlstatement = "DELETE FROM People WHERE Id = @Id;";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeleteEmployer(EmployerBaseModel employer)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employer.Id);
			string sqlstatement = "DELETE FROM Employers WHERE Id = @Id;";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeleteAddress(AddressModel address)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", address.Id);
			string sqlstatement = "DELETE FROM Addresses WHERE Id = @Id;";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeletePersonAddress(PersonAddressBaseModel personAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", personAddress.Id);
			string sqlstatement = "DELETE FROM PersonAddresses WHERE Id = @Id;";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeleteEmployerAddress(EmployerAddressBaseModel employerAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employerAddress.Id);
			string sqlstatement = "DELETE FROM EmployerAddresses WHERE Id = @Id;";
			MySQLDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}
	}
}

SQLite

Description:

The homework from the Lesson 6, SQLite, of Module 8, Data Access, of the Complete Foundation in C# Course Series from Tim Corey. We are to build a simple database in SQLite that holds People, Addresses, and Employers. Make sure it builds and that you can load and save data in C#.

Technologies Used:

.NET;
.NET Core;
.NET Core 3.1;
C#;
Class Library;
Console Application;
SQLite;

Find the Project here:

https://github.com/Spartan-CSharp/DataAccess-SQLite

Code Snippet:

using System.Collections.Generic;
using System.Linq;

using Dapper;

using DataAccessLibrary.Models;

namespace DataAccessLibrary.SQLiteDataAccess
{
	public class SQLiteCrud : ICrud
	{
		private readonly string _connectionString;

		public SQLiteCrud(string connectionString)
		{
			_connectionString = connectionString;
		}

		public void CreatePerson(PersonBaseModel person)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@FirstName", person.FirstName);
			parameters.Add("@LastName", person.LastName);
			parameters.Add("@IsActive", person.IsActive);
			parameters.Add("@EmployerId", person.EmployerId);
			string sqlstatement = "INSERT INTO People (FirstName, LastName, IsActive, EmployerId) VALUES (@FirstName, @LastName, @IsActive, @EmployerId);";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT Id FROM People WHERE FirstName = @FirstName AND LastName = @LastName AND IsActive = @IsActive AND EmployerId = @EmployerId;";
			person.Id = SQLiteDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreateEmployer(EmployerBaseModel employer)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@CompanyName", employer.CompanyName);
			string sqlstatement = "INSERT INTO Employers (CompanyName) VALUES (@CompanyName);";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT Id FROM Employers WHERE CompanyName = @CompanyName;";
			employer.Id = SQLiteDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreateAddress(AddressModel address)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@StreetAddress", address.StreetAddress);
			parameters.Add("@City", address.City);
			parameters.Add("@State", address.State);
			parameters.Add("@ZipCode", address.ZipCode);
			string sqlstatement = "INSERT INTO Addresses (StreetAddress, City, State, ZipCode) VALUES (@StreetAddress, @City, @State, @ZipCode);";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT Id FROM Addresses WHERE StreetAddress = @StreetAddress AND City = @City AND State = @State AND ZipCode = @ZipCode;";
			address.Id = SQLiteDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreatePersonAddress(PersonAddressBaseModel personAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@PersonId", personAddress.PersonId);
			parameters.Add("@AddressId", personAddress.AddressId);
			string sqlstatement = "INSERT INTO PersonAddresses (PersonId, AddressId) VALUES (@PersonId, @AddressId);";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT Id FROM PersonAddresses WHERE PersonId = @PersonId AND AddressId = @AddressId;";
			personAddress.Id = SQLiteDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreateEmployerAddress(EmployerAddressBaseModel employerAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@EmployerId", employerAddress.EmployerId);
			parameters.Add("@AddressId", employerAddress.AddressId);
			string sqlstatement = "INSERT INTO EmployerAddresses (EmployerId, AddressId) VALUES (@EmployerId, @AddressId);";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT Id FROM EmployerAddresses WHERE EmployerId = @EmployerId AND AddressId = @AddressId;";
			employerAddress.Id = SQLiteDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public List<PersonBaseModel> RetrieveAllPeople()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT Id, FirstName, LastName, IsActive, EmployerId FROM People;";
			List<PersonBaseModel> output = SQLiteDataAccess.ReadData<PersonBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public PersonBaseModel RetrievePersonById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT Id, FirstName, LastName, IsActive, EmployerId FROM People WHERE Id = @Id;";
			PersonBaseModel output = SQLiteDataAccess.ReadData<PersonBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<PersonBaseModel> RetrievePeopleByEmployerId(int employerId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@EmployerId", employerId);
			string sqlstatement = "SELECT Id, FirstName, LastName, IsActive, EmployerId FROM People WHERE EmployerId = @EmployerId;";
			List<PersonBaseModel> output = SQLiteDataAccess.ReadData<PersonBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<EmployerBaseModel> RetrieveAllEmployers()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT Id, CompanyName FROM Employers;";
			List<EmployerBaseModel> output = SQLiteDataAccess.ReadData<EmployerBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public EmployerBaseModel RetrieveEmployerById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT Id, CompanyName FROM Employers WHERE Id = @Id;";
			EmployerBaseModel output = SQLiteDataAccess.ReadData<EmployerBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<AddressModel> RetrieveAllAddresses()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT Id, StreetAddress, City, State, ZipCode FROM Addresses;";
			List<AddressModel> output = SQLiteDataAccess.ReadData<AddressModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public AddressModel RetrieveAddressById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT Id, StreetAddress, City, State, ZipCode FROM Addresses WHERE Id = @Id;";
			AddressModel output = SQLiteDataAccess.ReadData<AddressModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<PersonAddressBaseModel> RetrieveAllPersonAddresses()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT Id, PersonId, AddressId FROM PersonAddresses;";
			List<PersonAddressBaseModel> output = SQLiteDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public PersonAddressBaseModel RetrievePersonAddressById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT Id, PersonId, AddressId FROM PersonAddresses WHERE Id = @Id;";
			PersonAddressBaseModel output = SQLiteDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<PersonAddressBaseModel> RetrievePersonAddressByPersonId(int personId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@PersonId", personId);
			string sqlstatement = "SELECT Id, PersonId, AddressId FROM PersonAddresses WHERE PersonId = @PersonId;";
			List<PersonAddressBaseModel> output = SQLiteDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<PersonAddressBaseModel> RetrievePersonAddressByAddressId(int addressId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@AddressId", addressId);
			string sqlstatement = "SELECT Id, PersonId, AddressId FROM PersonAddresses WHERE AddressId = @AddressId;";
			List<PersonAddressBaseModel> output = SQLiteDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<EmployerAddressBaseModel> RetrieveAllEmployerAddresses()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT Id, EmployerId, AddressId FROM EmployerAddresses;";
			List<EmployerAddressBaseModel> output = SQLiteDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public EmployerAddressBaseModel RetrieveEmployerAddressById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT Id, EmployerId, AddressId FROM EmployerAddresses WHERE Id = @Id;";
			EmployerAddressBaseModel output = SQLiteDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<EmployerAddressBaseModel> RetrieveEmployerAddressByEmployerId(int employerId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@EmployerId", employerId);
			string sqlstatement = "SELECT Id, EmployerId, AddressId FROM EmployerAddresses WHERE EmployerId = @EmployerId;";
			List<EmployerAddressBaseModel> output = SQLiteDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<EmployerAddressBaseModel> RetrieveEmployerAddressByAddressId(int addressId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@AddressId", addressId);
			string sqlstatement = "SELECT Id, EmployerId, AddressId FROM EmployerAddresses WHERE AddressId = @AddressId;";
			List<EmployerAddressBaseModel> output = SQLiteDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public void UpdatePerson(PersonBaseModel person)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", person.Id);
			parameters.Add("@FirstName", person.FirstName);
			parameters.Add("@LastName", person.LastName);
			parameters.Add("@IsActive", person.IsActive);
			parameters.Add("@EmployerId", person.EmployerId);
			string sqlstatement = "UPDATE People SET FirstName = @FirstName, LastName = @LastName, IsActive = @IsActive, EmployerId = @EmployerId WHERE Id = @Id;";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdateEmployer(EmployerBaseModel employer)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employer.Id);
			parameters.Add("@CompanyName", employer.CompanyName);
			string sqlstatement = "UPDATE Employers SET CompanyName = @CompanyName WHERE Id = @Id;";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdateAddress(AddressModel address)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", address.Id);
			parameters.Add("@StreetAddress", address.StreetAddress);
			parameters.Add("@City", address.City);
			parameters.Add("@State", address.State);
			parameters.Add("@ZipCode", address.ZipCode);
			string sqlstatement = "UPDATE Addresses SET StreetAddress = @StreetAddress, City = @City, State = @State, ZipCode = @ZipCode WHERE Id = @Id;";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdatePersonAddress(PersonAddressBaseModel personAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", personAddress.Id);
			parameters.Add("@PersonId", personAddress.PersonId);
			parameters.Add("@AddressId", personAddress.AddressId);
			string sqlstatement = "UPDATE PersonAddresses SET PersonId = @PersonId, AddresId = @AddressId WHERE Id = @Id;";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdateEmployerAddress(EmployerAddressBaseModel employerAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employerAddress.Id);
			parameters.Add("@EmployerId", employerAddress.EmployerId);
			parameters.Add("@AddressId", employerAddress.AddressId);
			string sqlstatement = "UPDATE EmployerAddresses SET EmployerId = @EmployerId, AddresId = @AddressId WHERE Id = @Id;";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeletePerson(PersonBaseModel person)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", person.Id);
			string sqlstatement = "DELETE FROM People WHERE Id = @Id;";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeleteEmployer(EmployerBaseModel employer)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employer.Id);
			string sqlstatement = "DELETE FROM Employers WHERE Id = @Id;";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeleteAddress(AddressModel address)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", address.Id);
			string sqlstatement = "DELETE FROM Addresses WHERE Id = @Id;";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeletePersonAddress(PersonAddressBaseModel personAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", personAddress.Id);
			string sqlstatement = "DELETE FROM PersonAddresses WHERE Id = @Id;";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeleteEmployerAddress(EmployerAddressBaseModel employerAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employerAddress.Id);
			string sqlstatement = "DELETE FROM EmployerAddresses WHERE Id = @Id;";
			SQLiteDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}
	}
}

SQL Server

Description:

The homework from the Lesson 5, SQL Server, of Module 8, Data Access, of the Complete Foundation in C# Course Series from Tim Corey. We are to build a simple database in SQL that holds People, Addresses, and Employers. Make sure it builds and that you can load and save data in C#.

Technologies Used:

.NET;
.NET Core;
.NET Core 3.1;
C#;
Class Library;
Console Application;
SQL Server Database Project;

Find the Project here:

https://github.com/Spartan-CSharp/DataAccess-SQLServer

Code Snippet:

using System.Collections.Generic;
using System.Linq;

using Dapper;

using DataAccessLibrary.Models;

namespace DataAccessLibrary.SQLDataAccess
{
	public class SqlCrud : ICrud
	{
		private readonly string _connectionString;

		public SqlCrud(string connectionString)
		{
			_connectionString = connectionString;
		}

		public void CreatePerson(PersonBaseModel person)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@FirstName", person.FirstName);
			parameters.Add("@LastName", person.LastName);
			parameters.Add("@IsActive", person.IsActive);
			parameters.Add("@EmployerId", person.EmployerId);
			string sqlstatement = "INSERT INTO [dbo].[People] ([FirstName], [LastName], [IsActive], [EmployerId]) VALUES (@FirstName, @LastName, @IsActive, @EmployerId);";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT [Id] FROM [dbo].[People] WHERE [FirstName] = @FirstName AND [LastName] = @LastName AND [IsActive] = @IsActive AND [EmployerId] = @EmployerId;";
			person.Id = SqlDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreateEmployer(EmployerBaseModel employer)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@CompanyName", employer.CompanyName);
			string sqlstatement = "INSERT INTO [dbo].[Employers] ([CompanyName]) VALUES (@CompanyName);";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT [Id] FROM [dbo].[Employers] WHERE [CompanyName] = @CompanyName;";
			employer.Id = SqlDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreateAddress(AddressModel address)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@StreetAddress", address.StreetAddress);
			parameters.Add("@City", address.City);
			parameters.Add("@State", address.State);
			parameters.Add("@ZipCode", address.ZipCode);
			string sqlstatement = "INSERT INTO [dbo].[Addresses] ([StreetAddress], [City], [State], [ZipCode]) VALUES (@StreetAddress, @City, @State, @ZipCode);";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT [Id] FROM [dbo].[Addresses] WHERE [StreetAddress] = @StreetAddress AND [City] = @City AND [State] = @State AND [ZipCode] = @ZipCode;";
			address.Id = SqlDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreatePersonAddress(PersonAddressBaseModel personAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@PersonId", personAddress.PersonId);
			parameters.Add("@AddressId", personAddress.AddressId);
			string sqlstatement = "INSERT INTO [dbo].[PersonAddresses] ([PersonId], [AddressId]) VALUES (@PersonId, @AddressId);";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT [Id] FROM [dbo].[PersonAddresses] WHERE [PersonId] = @PersonId AND [AddressId] = @AddressId;";
			personAddress.Id = SqlDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public void CreateEmployerAddress(EmployerAddressBaseModel employerAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@EmployerId", employerAddress.EmployerId);
			parameters.Add("@AddressId", employerAddress.AddressId);
			string sqlstatement = "INSERT INTO [dbo].[EmployerAddresses] ([EmployerId], [AddressId]) VALUES (@EmployerId, @AddressId);";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
			sqlstatement = "SELECT [Id] FROM [dbo].[EmployerAddresses] WHERE [EmployerId] = @EmployerId AND [AddressId] = @AddressId;";
			employerAddress.Id = SqlDataAccess.ReadData<int, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
		}

		public List<PersonBaseModel> RetrieveAllPeople()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT [Id], [FirstName], [LastName], [IsActive], [EmployerId] FROM [dbo].[People];";
			List<PersonBaseModel> output = SqlDataAccess.ReadData<PersonBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public PersonBaseModel RetrievePersonById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT [Id], [FirstName], [LastName], [IsActive], [EmployerId] FROM [dbo].[People] WHERE Id = @Id;";
			PersonBaseModel output = SqlDataAccess.ReadData<PersonBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<PersonBaseModel> RetrievePeopleByEmployerId(int employerId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@EmployerId", employerId);
			string sqlstatement = "SELECT [Id], [FirstName], [LastName], [IsActive], [EmployerId] FROM [dbo].[People] WHERE EmployerId = @EmployerId;";
			List<PersonBaseModel> output = SqlDataAccess.ReadData<PersonBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<EmployerBaseModel> RetrieveAllEmployers()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT [Id], [CompanyName] FROM [dbo].[Employers];";
			List<EmployerBaseModel> output = SqlDataAccess.ReadData<EmployerBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public EmployerBaseModel RetrieveEmployerById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT [Id], [CompanyName] FROM [dbo].[Employers] WHERE Id = @Id;";
			EmployerBaseModel output = SqlDataAccess.ReadData<EmployerBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<AddressModel> RetrieveAllAddresses()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT [Id], [StreetAddress], [City], [State], [ZipCode] FROM [dbo].[Addresses];";
			List<AddressModel> output = SqlDataAccess.ReadData<AddressModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public AddressModel RetrieveAddressById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT [Id], [StreetAddress], [City], [State], [ZipCode] FROM [dbo].[Addresses] WHERE Id = @Id;";
			AddressModel output = SqlDataAccess.ReadData<AddressModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<PersonAddressBaseModel> RetrieveAllPersonAddresses()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT [Id], [PersonId], [AddressId] FROM [dbo].[PersonAddresses];";
			List<PersonAddressBaseModel> output = SqlDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public PersonAddressBaseModel RetrievePersonAddressById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT [Id], [PersonId], [AddressId] FROM [dbo].[PersonAddresses] WHERE Id = @Id;";
			PersonAddressBaseModel output = SqlDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<PersonAddressBaseModel> RetrievePersonAddressByPersonId(int personId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@PersonId", personId);
			string sqlstatement = "SELECT [Id], [PersonId], [AddressId] FROM [dbo].[PersonAddresses] WHERE PersonId = @PersonId;";
			List<PersonAddressBaseModel> output = SqlDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<PersonAddressBaseModel> RetrievePersonAddressByAddressId(int addressId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@AddressId", addressId);
			string sqlstatement = "SELECT [Id], [PersonId], [AddressId] FROM [dbo].[PersonAddresses] WHERE AddressId = @AddressId;";
			List<PersonAddressBaseModel> output = SqlDataAccess.ReadData<PersonAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<EmployerAddressBaseModel> RetrieveAllEmployerAddresses()
		{
			DynamicParameters parameters = new DynamicParameters();
			string sqlstatement = "SELECT [Id], [EmployerId], [AddressId] FROM [dbo].[EmployerAddresses];";
			List<EmployerAddressBaseModel> output = SqlDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public EmployerAddressBaseModel RetrieveEmployerAddressById(int id)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", id);
			string sqlstatement = "SELECT [Id], [EmployerId], [AddressId] FROM [dbo].[EmployerAddresses] WHERE Id = @Id;";
			EmployerAddressBaseModel output = SqlDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString).First();
			return output;
		}

		public List<EmployerAddressBaseModel> RetrieveEmployerAddressByEmployerId(int employerId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@EmployerId", employerId);
			string sqlstatement = "SELECT [Id], [EmployerId], [AddressId] FROM [dbo].[EmployerAddresses] WHERE EmployerId = @EmployerId;";
			List<EmployerAddressBaseModel> output = SqlDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public List<EmployerAddressBaseModel> RetrieveEmployerAddressByAddressId(int addressId)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@AddressId", addressId);
			string sqlstatement = "SELECT [Id], [EmployerId], [AddressId] FROM [dbo].[EmployerAddresses] WHERE AddressId = @AddressId;";
			List<EmployerAddressBaseModel> output = SqlDataAccess.ReadData<EmployerAddressBaseModel, DynamicParameters>(sqlstatement, parameters, _connectionString);
			return output;
		}

		public void UpdatePerson(PersonBaseModel person)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", person.Id);
			parameters.Add("@FirstName", person.FirstName);
			parameters.Add("@LastName", person.LastName);
			parameters.Add("@IsActive", person.IsActive);
			parameters.Add("@EmployerId", person.EmployerId);
			string sqlstatement = "UPDATE [dbo].[People] SET [FirstName] = @FirstName, [LastName] = @LastName, [IsActive] = @IsActive, [EmployerId] = @EmployerId WHERE [Id] = @Id;";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdateEmployer(EmployerBaseModel employer)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employer.Id);
			parameters.Add("@CompanyName", employer.CompanyName);
			string sqlstatement = "UPDATE [dbo].[Employers] SET [CompanyName] = @CompanyName WHERE [Id] = @Id;";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdateAddress(AddressModel address)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", address.Id);
			parameters.Add("@StreetAddress", address.StreetAddress);
			parameters.Add("@City", address.City);
			parameters.Add("@State", address.State);
			parameters.Add("@ZipCode", address.ZipCode);
			string sqlstatement = "UPDATE [dbo].[Addresses] SET [StreetAddress] = @StreetAddress, [City] = @City, [State] = @State, [ZipCode] = @ZipCode WHERE [Id] = @Id;";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdatePersonAddress(PersonAddressBaseModel personAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", personAddress.Id);
			parameters.Add("@PersonId", personAddress.PersonId);
			parameters.Add("@AddressId", personAddress.AddressId);
			string sqlstatement = "UPDATE [dbo].[PersonAddresses] SET [PersonId] = @PersonId, [AddresId] = @AddressId WHERE [Id] = @Id;";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void UpdateEmployerAddress(EmployerAddressBaseModel employerAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employerAddress.Id);
			parameters.Add("@EmployerId", employerAddress.EmployerId);
			parameters.Add("@AddressId", employerAddress.AddressId);
			string sqlstatement = "UPDATE [dbo].[EmployerAddresses] SET [EmployerId] = @EmployerId, [AddresId] = @AddressId WHERE [Id] = @Id;";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeletePerson(PersonBaseModel person)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", person.Id);
			string sqlstatement = "DELETE FROM [dbo].[People] WHERE [Id] = @Id;";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeleteEmployer(EmployerBaseModel employer)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employer.Id);
			string sqlstatement = "DELETE FROM [dbo].[Employers] WHERE [Id] = @Id;";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeleteAddress(AddressModel address)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", address.Id);
			string sqlstatement = "DELETE FROM [dbo].[Addresses] WHERE [Id] = @Id;";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeletePersonAddress(PersonAddressBaseModel personAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", personAddress.Id);
			string sqlstatement = "DELETE FROM [dbo].[PersonAddresses] WHERE [Id] = @Id;";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}

		public void DeleteEmployerAddress(EmployerAddressBaseModel employerAddress)
		{
			DynamicParameters parameters = new DynamicParameters();
			parameters.Add("@Id", employerAddress.Id);
			string sqlstatement = "DELETE FROM [dbo].[EmployerAddresses] WHERE [Id] = @Id;";
			SqlDataAccess.WriteData(sqlstatement, parameters, _connectionString);
		}
	}
}

Back to List