How can I make my console application more reusable across different databases?

Question

Take for example the pubs database and the employee table. I have the following contrived example that reads batches of employees and just updates the last name column using Entity Framework 6.0.

Let's say I want to use this to update employee information in the Northwind database. Is there a good way to do this without creating 2 different programs because what if another similar employee table in a different database would require a third console application.

Assume that the fields are the same across the different databases but could have different names for fields.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;

namespace EFTransactionConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            using (pubsEntities dbContext = new pubsEntities())
            {
                using (DbContextTransaction transaction = dbContext.Database.BeginTransaction())
                {
                    IEnumerable<employee> employees = null;
                    int currentPage = 1;
                    int totalPages = dbContext.employees.Count();
                    string lastEmpId = string.Empty;

                    try
                    {
                        do
                        {
                            employees = (from x in dbContext.employees
                                         where string.Compare(x.emp_id, lastEmpId) > 0
                                         orderby x.emp_id
                                         select x).Take(5).ToList();

                            if (employees.Count() > 0)
                            {
                                lastEmpId = employees.Last().emp_id;

                                //var fifthEmp = dbContext.employees.Where(x => x.emp_id == lastEmpId).FirstOrDefault();
                                //fifthEmp.lname += "test";


                                dbContext.Database.ExecuteSqlCommand(@"
                                UPDATE employee SET lname = " + "'test 123'" + " WHERE emp_id = '" + lastEmpId + "'"
                                    );

                                //foreach (var item in employees)
                                //{
                                //    Console.WriteLine("{0}-{1}", item.emp_id, item.fname);
                                //}
                                //dbContext.SaveChanges();
                            }

                            if (currentPage == 6)
                                throw new Exception("Error occurred");

                            currentPage++;

                        } while (currentPage <= totalPages);

                        transaction.Commit();
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                    }
                }
            }
        }
    }
}

If the table names and structures are the same, all that should need to be changed is the `connection string`. If the field names are different, you'll need to have a different method with the correct fields being used. — Jaskier, Mar 12 '19 at 18:29
If you are asking about how to point the same application to a different database server still using the same data provider, Symon's comment covers it - just change the connection string. If you are asking more generally how to use any database (or even any persistent storage) that is a much broader question. I think the broad answer is [program to an interface](https://stackoverflow.com/questions/383947/what-does-it-mean-to-program-to-an-interface) and use [dependency injection](https://martinfowler.com/articles/injection.html). — chadnt, Mar 12 '19 at 18:57

score 0 · Answer 1 · answered Mar 12 '19 at 18:58

You could consider the Repository Pattern. See: https://www.infoworld.com/article/3107186/how-to-implement-the-repository-design-pattern-in-c.html as a sample article.

In general, the repository pattern's goal is to separate data persistence from business logic. If you have different databases underneath, you would still need at least 2 sets of repository code, but the business logic that sits on top of it would, in theory, not care.

How can I make my console application more reusable across different databases?

1 Answers1