I'm struggling to understand how to implement principles of
encapsulation,
inheritance and
polymorphism to break this code into logical parts. My background is in VBS/Classic ASP and database development, not OOP.
I'm not asking anyone to write the code, but rather to point me in the right direction. I have this class, which I suppose is something I've heard referred to as a God Object.
My code works, but it's unmanageable and will only get worse.
This class:
1. Receives information about a data source, like the path to an Access database or the name of a db server.
2. Determines which engine can process the file... Access, MSSQL, MySQL...
3. Select a Provider to connect to the DBMS
4. Set db specific settings, like whether to use single or double quotes around object names, etc.
5. Generate a connection string to connect to the db.
Is it Ok to have all this stuff in a single class?
I was thinking it would be easier to understand later, if I separated it into a few different classes:
1. A class to figure out what kind of data source it will connect to
2. A class to set db specific settings and generate a connection string
Does that sound right? If so, would you recommend maybe pulling all of these enums and their accompanying methods into a separate static class so that they don't need to be instantiated?
using System;
using System.Diagnostics;
using System.IO;
namespace RickRoll
{
public class Db
{
public string DataSource { get; }
public string Database { get; }
public string Username { get; }
public string Password { get; }
public string ConnectionString { get; private set; }
public string ExtendedProperties { get; }
private WrapColumn wrapcol;
private WrapObject wrapobj;
public DataProvider Provider { get; set; }
public DataFileType FileType { get; private set; }
public DataEngine Engine { get; private set; }
public WrapColumn WrapCol { get => wrapcol; private set => wrapcol = value; }
public WrapObject WrapObj { get => wrapobj; private set => wrapobj = value; }
public Db()
{
}
public Db(string filepath)
{
FileType = GetDbFileType (filepath);
Engine = GetDbEngine (FileType);
Provider = GetDbProvider (FileType);
DataSource = filepath;
Database = Path.GetFileName (filepath);
Username = null;
Password = null;
ConnectionString = GetConnectionString (Provider);
GetWrapCol (Engine);
GetWrapObj (Engine);
}
public Db(string filepath, string password)
{
FileType = GetDbFileType (filepath);
Engine = GetDbEngine (FileType);
Provider = GetDbProvider (FileType);
DataSource = filepath;
Database = Path.GetFileName (filepath);
Username = null;
Password = password;
ConnectionString = GetConnectionString (Provider);
GetWrapCol (Engine);
GetWrapObj (Engine);
}
public Db(string path, string server , string database)
{
FileType = GetDbFileType (path);
Engine = GetDbEngine (FileType);
Provider = GetDbProvider (FileType);
DataSource = GetDataSource (Engine , path , server);
Database = database;
Username = null;
Password = null;
ConnectionString = GetConnectionString (Provider);
GetWrapCol (Engine);
GetWrapObj (Engine);
Debug.WriteLine (ConnectionString);
Debug.WriteLine (Environment.UserName);
}
public Db(string path, string server, string database , string username , string password)
{
FileType = GetDbFileType (path);
Engine = GetDbEngine (FileType);
Provider = GetDbProvider (FileType);
DataSource = GetDataSource (Engine, path, server);
Database = database;
Username = username;
Password = password;
ConnectionString = GetConnectionString (Provider);
GetWrapCol (Engine);
GetWrapObj (Engine);
}
public Db(int filetype_value, string server , string database)
{
FileType = GetDbFileType (filetype_value);
Engine = GetDbEngine (FileType);
Provider = GetDbProvider (FileType);
DataSource = server;
Database = database;
Username = null;
Password = null;
ConnectionString = GetConnectionString (Provider);
GetWrapCol (Engine);
GetWrapObj (Engine);
Debug.WriteLine (ConnectionString);
Debug.WriteLine (Environment.UserName);
}
public Db(int filetype_value , string server , string database , string username , string password)
{
FileType = GetDbFileType (filetype_value);
Engine = GetDbEngine (FileType);
Provider = GetDbProvider (FileType);
DataSource = server;
Database = database;
Username = username;
Password = password;
ConnectionString = GetConnectionString (Provider);
GetWrapCol (Engine);
GetWrapObj (Engine);
}
public void ReSetConnectionString(DataProvider p)
{
Provider = p;
ConnectionString = GetConnectionString ( Provider );
}
private DataFileType GetDbFileType(string path)
{
DataFileType ft;
string ext;
if (path.Contains ("."))
{
string fpath = path.Replace ("|DataDirectory|" , AppDomain.CurrentDomain.BaseDirectory);
ext = Path.GetExtension (fpath).Replace ("." , "").ToUpper ( );
if (Enum.TryParse<DataFileType> (ext , out ft) == true) return ft;
}
else
{
ft = (DataFileType) Enum.Parse (typeof (DataFileType) , path.ToUpper()); }
return ft;
}
private string GetDataSource(DataEngine e, string path, string server)
{
if (DataSourceIsFile (e)) return path;
return server;
}
private DataFileType GetDbFileType(int value)
{
DataFileType ft = (DataFileType) Enum.Parse (typeof (DataFileType) , value.ToString ( ));
return ft;
}
public enum DataEngine
{
None,
ACCESS = 1,
MSSQL,
EXCEL,
ORACLE,
MYSQL,
TEXT
}
public enum DataFileType
{
None,
MDB = 1,
ACCDB,
MDF,
NDF,
XLS,
XLSX,
XLSXM,
XLTM,
XLW,
CSV,
TAB,
TSV,
TXT
}
public enum DataProvider
{
None,
Microsoft_ACE_OLEDB_12_0 = 1,
Microsoft_ACE_OLEDB,
Microsoft_Jet_OLEDB_4_0,
Microsoft_Jet_OLEDB,
SQLNCLI11,
SQLNCLI,
SQLOLEDB_1,
SQLOLEDB,
SQL__Server__Native__Client__11_0,
SQL__Server__Native__Client,
MSDASQL_1,
MSDASQL
}
private DataEngine GetDbEngine(int enumvalue)
{
DataEngine result = (DataEngine) Enum.Parse (typeof (DataEngine) , enumvalue.ToString ( ));
return result;
}
private DataEngine GetDbEngine(DataFileType ft)
{
switch (ft)
{
case DataFileType.MDB:
return DataEngine.ACCESS;
case DataFileType.ACCDB:
return DataEngine.ACCESS;
case DataFileType.MDF:
return DataEngine.MSSQL;
case DataFileType.NDF:
return DataEngine.MSSQL;
case DataFileType.XLS:
return DataEngine.EXCEL;
case DataFileType.XLSX:
return DataEngine.EXCEL;
case DataFileType.CSV:
return DataEngine.TEXT;
case DataFileType.TAB:
return DataEngine.TEXT;
case DataFileType.TSV:
return DataEngine.TEXT;
case DataFileType.TXT:
return DataEngine.TEXT;
default:
throw new ArgumentException ($"* * * DataFileType is not a supported data file format. Database DataEngine could not be determined.");
}
}
private DataProvider GetDbProvider(DataFileType ft)
{
switch (ft)
{
case DataFileType.MDB:
case DataFileType.ACCDB:
return DataProvider.Microsoft_ACE_OLEDB_12_0;
case DataFileType.MDF:
return DataProvider.SQLNCLI11;
case DataFileType.NDF:
return DataProvider.SQLNCLI11;
case DataFileType.XLS:
case DataFileType.XLSX:
case DataFileType.CSV:
case DataFileType.TAB:
case DataFileType.TSV:
case DataFileType.TXT:
return DataProvider.Microsoft_ACE_OLEDB_12_0;
default:
throw new ArgumentException ($"* * * DataFileType is not a supported data file format. Database DataProvider could not be determined.");
}
}
private void GetWrapCol(DataEngine e)
{
switch (e)
{
case DataEngine.ACCESS:
WrapCol = new WrapColumn { left = "`" , middle = "`,`" , right = "`" };
break;
case DataEngine.MSSQL:
WrapCol = new WrapColumn { left = "[" , middle = "],[" , right = "]" };
break;
case DataEngine.EXCEL:
WrapCol = new WrapColumn { left = "`" , middle = "`,`" , right = "`" };
break;
case DataEngine.ORACLE:
WrapCol = new WrapColumn { left = @"""" , middle = @""",""" , right = @"""" };
break;
case DataEngine.MYSQL:
WrapCol = new WrapColumn { left = "`" , middle = "`,`" , right = "`" };
break;
case DataEngine.TEXT:
WrapCol = new WrapColumn { left = @"""" , middle = @""",""" , right = @"""" };
break;
default:
throw new ArgumentException ($"* * * DataEngine was not valid. Could not determine a COLUMN escape character.");
}
}
private void GetWrapObj(DataEngine e)
{
switch (e)
{
case DataEngine.ACCESS:
WrapObj = new WrapObject { left = "`" , middle = "`,`" , right = "`" };
break;
case DataEngine.MSSQL:
WrapObj = new WrapObject { left = "[" , middle = "],[" , right = "]" };
break;
case DataEngine.EXCEL:
WrapObj = new WrapObject { left = "`" , middle = "`,`" , right = "`" };
break;
case DataEngine.ORACLE:
WrapObj = new WrapObject { left = @"""" , middle = @""",""" , right = @"""" };
break;
case DataEngine.MYSQL:
WrapObj = new WrapObject { left = "[" , middle = "],[" , right = "]" };
break;
case DataEngine.TEXT:
WrapObj = new WrapObject { left = @"""" , middle = @""",""" , right = @"""" };
break;
default:
throw new ArgumentException ($"* * * DataEngine was not valid. Could not determine a OBJECT escape character.");
}
}
private bool DataSourceIsFile(DataEngine e)
{
switch (e)
{
case DataEngine.ACCESS:
return true;
case DataEngine.MSSQL:
return false;
case DataEngine.EXCEL:
return true;
case DataEngine.ORACLE:
return false;
case DataEngine.MYSQL:
return false;
case DataEngine.TEXT:
return true;
default:
throw new ArgumentException ($"* * * DataEngine was not valid. Could not determine if this is a file or server DataSource.");
}
}
private string GetConnectionString(DataProvider Provider)
{
string ProgId = Provider.ToString ( ).Replace ("_" , ".");
string result = "";
switch (Provider)
{
case DataProvider.Microsoft_ACE_OLEDB_12_0:
case DataProvider.Microsoft_ACE_OLEDB:
return $@"Provider={ProgId};Data Source={DataSource};Persist Security Info=False;Jet OLEDB:Database Password={Password.EmptyIfNull ( )};";
case DataProvider.Microsoft_Jet_OLEDB_4_0:
case DataProvider.Microsoft_Jet_OLEDB:
if (Engine == DataEngine.ACCESS && Password.NullIfEmpty() == null)
return $@"Provider={ProgId};Data Source={DataSource};User ID=Admin;Password=;";
else
return $@"Provider={ProgId};Data Source={DataSource};Persist Security Info=False;Jet OLEDB:Database Password={Password.EmptyIfNull ( )};";
case DataProvider.SQLNCLI11:
case DataProvider.SQLNCLI:
result = $@"Provider={ProgId};Server={DataSource};Database={Database};";
if (Password != null) result += $"Uid={Username.EmptyIfNull ( )};Pwd={Password.EmptyIfNull ( )};";
if (Password == null) result += "Integrated Security=SSPI;";
return result;
case DataProvider.SQLOLEDB_1:
case DataProvider.SQLOLEDB:
result = $@"Provider={ProgId};Data Source={DataSource};Initial Catalog={Database};";
if (Password != null) result += $"User Id={Username.EmptyIfNull()};Password={Password.EmptyIfNull ( )};";
if (Password == null) result += "Integrated Security=SSPI;";
return result;
case DataProvider.MSDASQL_1:
case DataProvider.MSDASQL:
if (Engine == DataEngine.ACCESS)
return $@"Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};DbQ={DataSource}";
if (Engine == DataEngine.EXCEL)
return $@"Driver={{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}};DbQ={DataSource}";
if (Engine == DataEngine.MSSQL)
if (string.IsNullOrEmpty (Username))
return $@"[DataProvider = MSDASQL;] DRIVER={{SQL Server}}; SERVER={DataSource}; DATABASE={Database};UID=;Integrated Security=SSPI";
else
return $@"[DataProvider = MSDASQL;] DRIVER={{SQL Server}}; SERVER={DataSource}; DATABASE={Database};UID={Username};PWD={Password}";
else
throw new ArgumentException ($" * * * The MSDASQL Provider has only been set up for MS Access or Excel or MSSQL connections. Could not create Connection String");
case DataProvider.SQL__Server__Native__Client__11_0:
if (Engine == DataEngine.MSSQL && (Username.NullIfEmpty ( ) != null))
return $@"Driver={{{ProgId}}};Server={DataSource};Database={Database};Uid={Username};Pwd={Password};";
else
return $@"Driver={{{ProgId}}};Server={DataSource};Database={Database};Trusted_Connection=yes;";
default:
throw new ArgumentException ($" * * * DataProvider is not valid. Could not create Connection String");
}
}
public string Columns(string value)
{
string[ ] c = value.Split (',');
return $"{WrapCol.left}{string.Join(WrapCol.middle , c)}{WrapCol.right}";
}
}
public static class StringExt
{
public static string NullIfEmpty(this string value)
{
return string.IsNullOrEmpty (value) ? null : value;
}
public static string EmptyIfNull(this string value)
{
if (string.IsNullOrEmpty (value))
return string.Empty;
else
return value;
}
}
}
What I have tried:
My class is working as designed, but the entire program is wrapped up in this and two other classes. I feel like I could possibly write a base class and then have some derived classes, but I've never done that before and I am not familiar enough with the mechanism by which the classes talk to eachother to spec that out.