Introduction


In this code sample/article developers will learn about misconceptions for connecting to SQL-Server, MS-Access and Oracle databases, how to connect to these databases better along with dealing with a strategy for handling run time exceptions in a manner which is easy to implement and use in any project.

What is important to understand as walking through this article is that all managed providers available under the .NET Framework all have connection classes where each has a ConnectionString. Knowing this, a developer should write connections as equals no matter the database. Developers reading this article will be presented with connection classes which each and everyone has a ConnectionString property which although are constructed according to the intended database to open they are similar to the connection string.

When in doubt how to create a connection string which varies from the ones presented use the following site which provides every single variation of how to connect to a specific database.

Although this article focuses on Windows Forms data operations there are parts of code presented that can be used in web solutions but a better idea would be to explore using an MVC pattern or repository pattern using Entity Framework.

Building the Sample projects

Before building the solution, right click on Solution Explorer and select "restore NuGet packages" followed by building the entire solution.

Description


Overview

Inherently when learning how to access database tables new to writing code will gravitate to writing this code within Windows Forms, not knowing better there are several issues. The first issue is that code written to perform data operations is now only available within that form then the second issue is what happens when (and this will happen to each and every developer at one time or another) a form is corrupted? Finally, not only is the code not accessible in other forms or classes the code can not be used in other projects.

In the following code, there are several issues:
  • Code is in the form to access data, at the very least a data class should be used which will be shown later.
  • Prefixing each variable with "my", there is nothing gained my prefixing variables with "my", you know it's your code.
  • The connection string is concatenated, in this case (which is not apparent here) before testing the connection string first which here fails from a missing semi-colon.
  • If a runtime exception occurs the MessageBox shows nothing to indicate what throw an exception.
  • Using globally scoped or privately scoped connections are costly and waste resources.
private void Form1_Load(object sender, EventArgs e)
{
 
    var myDataTable = new DataTable();
 
    var mySelectStatement = "SELECT CustomerIdentifier, CompanyName AS Company, ContactName AS Contact, ContactTitle AS Title, StreetAddress AS Street,City, PostalCode AS Postal, Country, JoinDate AS Joined FROM dbo.Customers WHERE ActiveStatus = @ActiveStatus";
 
 
    var myServer = "KARENS-PC;";
    var myDatabaseWrong = "WorkingWithDataTips_1";
 
    var myConnectionString = "Data Source=" +  myServer +
                             "Initial Catalog=" + myDatabaseWrong +
                             "Integrated Security=True";
 
    using (SqlConnection myConnection = new SqlConnection { ConnectionString = myConnectionString })
    {
        using (SqlCommand myCommand = new SqlCommand { Connection = myConnection })
        {
            myCommand.CommandText = mySelectStatement;
            myCommand.Parameters.AddWithValue("@ActiveStatus", true);
 
            try
            {
 
                myConnection.Open();
 
                myDataTable.Load(myCommand.ExecuteReader());
                myDataTable.Columns["CustomerIdentifier"].ColumnMapping = MappingType.Hidden;
 
                dataGridView1.DataSource = myDataTable;
 
            }
            catch (Exception ex)
            {
                MessageBox.Show("Something went really wrong!!!");
            }
 
        }
    }
}

In the revised code from above:
  • Code is still in the form, will be handled later.
  • Variables don't use "my" prefix.
  • Uses explicitly typed as in these cases implicit variables serve no real value.
  • The connection string looks the same but in this case, the connection string has been fully tested first.
  • Exception details are provided rather than a simple message as in the first example.

var dt = new DataTable();
  
var selectStatement =
    "SELECT CustomerIdentifier, CompanyName AS Company, ContactName AS Contact, " +
    "ContactTitle AS Title, StreetAddress AS Street,City, PostalCode AS Postal, " +
    "Country, JoinDate AS Joined " +
    "FROM dbo.Customers " +
    "WHERE ActiveStatus = @ActiveStatus";
  
  
var rightWay = "Data Source=KARENS-PC;"
               "Initial Catalog=WorkingWithDataTips_1;"
               "Integrated Security=True";
  
  
using (var cn = new SqlConnection { ConnectionString = rightWay })
{
    using (var cmd = new SqlCommand { Connection = cn })
    {
        cmd.CommandText = selectStatement;
        cmd.Parameters.AddWithValue("@ActiveStatus", true);
  
        try
        {
  
            cn.Open();
  
            dt.Load(cmd.ExecuteReader());
            dt.Columns["CustomerIdentifier"].ColumnMapping = MappingType.Hidden;
  
            dataGridView1.DataSource = dt;
  
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Something went really wrong!!!{Environment.NewLine}{ex.Message}");
        }
  
    }
}

Another bad practice is believing there should be one connection object for a project while this sounds like a good idea there are issues.

Example for using a singleton pattern for a connection object which is reusable (but not wise).

using System.Data;
using System.Data.SqlClient;
  
namespace WorkingWithDatabaseWithSingletonExample.Classes
{
    /// <summary>
    /// Central connection for entire project.
    /// Unwise to keep a connection open any longer than necessary.
    /// </summary>
    public class Connection
    {
  
        public static Connection ObjConnection;
        private static readonly object _lockobject = new object();
  
        public static Connection GetInstance() 
        {
            lock (_lockobject)
            {
                if (ObjConnection == null)
                {
                    ObjConnection = new Connection();
                }
  
            }
  
            return ObjConnection;
        }
  
        private Connection() {}
  
        public static SqlConnection Connect()
        {
             
            var connectionString = Properties.Settings.Default.MainConnection;
  
            var cn = new SqlConnection(connectionString);
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            else
            {
                cn.Close();
            }
  
            return cn;
  
        }
    }
}

Simple usage (simply is not always good)

SqlConnection cn = Connection.Connect();

The above is simple yet can have issues as per below.  


All the following may have many permutations.

  • When the connection is expected to be open but is closed and there is no proper check to know if the connection is open or closed.
  • Carrying such a connection object scoped as public (or public static) is extra overhead.
  • Working with a team where another teammate makes changes that go against the overly scoped connection causing issues.

Best practice


Each method that requires a connection will create a connection object in the method needing to access the database. Using a) a constant string for the connection string b) logic where the connection string is flexible to changes which do not require the solution to be compiled.  

To resolve these issues the first step is to create a class for data operations, each operation should reside in its own method. For instance, one method for reading all data from a database table or tables that are in a relationship e.g. join a Customer table with a Customers Contact table to form one row for each customer where the contact table has said the contact first and last name, usually more information and that may relate to another table e.g. contact’s contact information e.g. home phone, cell phone, home and work email addresses.


Using project classes


Rather than using one connection object and/or a singleton pattern for all connections the following will use a class to compose the connection string.  

Thinking ahead, suppose a developer will work with other databases? What is needed is a common property, in this case, ConnectionString for any database. This is done using an Interface.

public interface IConnection
{
    string ConnectionString { get; }
}

For SQL-Server connection

public abstract class SqlServerConnection : BaseExceptionProperties, IConnection
{
    /// <summary>
    /// This points to your database server
    /// </summary>
    protected string DatabaseServer = "";
    /// <summary>
    /// Name of database containing required tables
    /// </summary>
    protected string DefaultCatalog = "";
    public string ConnectionString
    {
        get
        {
            return $"Data Source={DatabaseServer};Initial Catalog={DefaultCatalog};Integrated Security=True";
        }
    }
}

Then for MS-Access (later an enhanced version will have a secure connection string)

public abstract class AccessConnection : BaseExceptionProperties, IConnection
{
    private readonly OleDbConnectionStringBuilder _builder = new OleDbConnectionStringBuilder { Provider = "Microsoft.ACE.OLEDB.12.0" };
    /// <summary>
    /// Database name and path
    /// </summary>
    protected string DefaultCatalog = "";
    public string ConnectionString
    {
        get
        {
  
            if (string.IsNullOrWhiteSpace(DefaultCatalog))
            {
                throw new Exception("Database name and path not provided.");
            }
  
            _builder.DataSource = DefaultCatalog;
  
            return _builder.ConnectionString;
  
        }
    }
}

The same goes for Oracle. Note there is more complexity to Oracle, this can happen with the latter classes dependent on what needs to be set past simple connections.

public abstract class OracleConnection : BaseExceptionProperties, IConnection
{
    public bool UseTNS { get; set; }
    public string DataServer { get; set; }
    public string Host { get; set; }
    private int _Port = 1521;
    public int Port
    {
        get => _Port;
        set => _Port = value;
    }
    public string ServiceName { get; set; }
  
    /// <summary>
    /// Defaults to true
    /// </summary>
    /// <returns></returns>
    private bool _PersistSecurityInfo = true;
    public bool PersistSecurityInfo
    {
        get => _PersistSecurityInfo;
        set => _PersistSecurityInfo = value;
    }
    /// <summary>
    /// Defaults to false
    /// </summary>
    /// <returns></returns>
    public bool Enlist { get; set; }
    /// <summary>
    /// Defaults to 10
    /// </summary>
    /// <returns></returns>
    private int _StatementCacheSize = 10;
    public int StatementCacheSize
    {
        get => _StatementCacheSize;
        set => _StatementCacheSize = value;
    }
    private bool _Pooling = true;
    public bool Pooling
    {
        get => _Pooling;
        set => _Pooling = value;
    }
  
    /// <summary>
    /// Defaults to 15
    /// </summary>
    /// <returns></returns>
    private int _ConnectionTimeout = 15;
    public int ConnectionTimeout
    {
        get => _ConnectionTimeout;
        set => _ConnectionTimeout = value;
    }
  
    public string UserId { get; set; }
    public string Password { get; set; }
    /// <summary>
    /// Compose connection string from properties above.
    /// </summary>
    /// <returns></returns>
    private string ComposeConnectionString()
    {
  
        var template = "";
  
        if (UseTNS)
        {
  
            template = $"Data Source={DataServer};Persist Security Info={PersistSecurityInfo};"
                       $"Enlist={Enlist};Pooling={Pooling};Statement Cache Size={StatementCacheSize};";
  
            if (string.IsNullOrWhiteSpace(UserId))
            {
                return template;
            }
  
            if (!(string.IsNullOrWhiteSpace(Password)))
            {
                template = template + $"User ID={UserId};Password={Password};";
            }
        }
        else
        {
            throw new NotImplementedException();
        }
  
        return template;
  
    }
  
    public string ConnectionString => 
        ComposeConnectionString();
}

The following project setup

There are classes for making a connection to a SQL-Server which is set up in a data class. First, inherit the connection class above.

public class DataOperations : SqlServerConnection

If only one server is being used in the data class create a new constructor and place the following code into the class. In this case, KARENS-PC is the server name.

public class DataOperations : SqlServerConnection
{
    public DataOperations()
    {
        DatabaseServer = "KARENS-PC";            
    }

In this case SQL-Server Express local server-based database.

public class DataOperations : SqlServerConnection
{
    public DataOperations()
    {
        DatabaseServer = ".\SQLEXPRESS";            
    }

If only one database will be used add the constructor will not set the database. 

public DataOperations()
{
    DatabaseServer = "KARENS-PC";
    DefaultCatalog = "NorthWind";
}

If the data class will work with various tables, each method can set the database using

public DataTable GetCustomersByStatus(bool pStatus = true)
{
    DefaultCatalog = "SomeOtherDatabase";

Or if the server and database are different set then in the method as follows.

public DataTable GetCustomersByStatus(bool pStatus = true)
{
    DatabaseServer = "SomeOtherServer";
    DefaultCatalog = "SomeOtherDatabase";

Exception handling starter


In each connection class above a class BaseExceptionProperties is used which is shown below. This class allows a developer to remember a) if there was an exception thrown and asserted by the caller along with the method not having to return if there was an exception.

public class BaseExceptionProperties
{
  
    protected bool mHasException;
    /// <summary>
    /// Indicate the last operation thrown an exception or not
    /// </summary>
    /// <returns></returns>
    public bool HasException
    {
        get
        {
            return mHasException;
        }
    }
    protected Exception mLastException;
    /// <summary>
    /// Provides access to the last exception thrown
    /// </summary>
    /// <returns></returns>
    public Exception LastException
    {
        get
        {
            return mLastException;
        }
    }
    /// <summary>
    /// If you don't need the entire exception as in LastException this 
    /// provides just the text of the exception
    /// </summary>
    /// <returns></returns>
    public string LastExceptionMessage
    {
        get
        {
            return mLastException.Message;
        }
    }
    /// <summary>
    /// Indicate for return of a function if there was an exception thrown or not.
    /// </summary>
    /// <returns></returns>
    public bool IsSuccessFul
    {
        get
        {
            return !mHasException;
        }
    }
}

Here is the full data class which uses a) the connection class coupled with the exception class.

The task is to open a database, read data from two joined tables. If there is an exception thrown this will be asserted in the caller.

The SQL SELECT statement was written in SSMS (SQL-Server Management Studio) to ensure it works as expected.

public class DataOperations : SqlServerConnection
{
    public DataOperations()
    {
        DatabaseServer = "KARENS-PC";
        DefaultCatalog = "NorthWindAzure";
    }
    /// <summary>
    /// Return active or inactive customers
    /// </summary>
    /// <param name="pStatus">Status of customers</param>
    /// <returns>DataTable</returns>
    public DataTable GetCustomersByStatus(bool pStatus = true)
    {
        var dt = new DataTable();
  
        mHasException = false;
  
        var selectStatement =
            "SELECT CustomerIdentifier, CompanyName AS Company, ContactName AS Contact, " +
            "ContactTitle AS Title, StreetAddress AS Street,City, PostalCode AS Postal, " +
            "Country, JoinDate AS Joined " +
            "FROM dbo.Customers " +
            "WHERE ActiveStatus = @ActiveStatus";
  
  
        using (var cn = new SqlConnection { ConnectionString = ConnectionString })
        {
  
            using (var cmd = new SqlCommand { Connection = cn })
            {
  
                cmd.CommandText = selectStatement;
                cmd.Parameters.AddWithValue("@ActiveStatus", pStatus);
  
                try
                {
  
                    cn.Open();
  
                    dt.Load(cmd.ExecuteReader());
                    dt.Columns["CustomerIdentifier"].ColumnMapping = MappingType.Hidden;
  
                }
                catch (Exception ex)
                {
                    mHasException = true;
                    mLastException = ex;
                }
  
            }
        }
  
        return dt;
    }
}

A form will call the method above to populate the returning DataTable to the DataSource property of a BindingSource component where the BindingSource component becomes the DataSource of a DataGridView. Note that is there was an exception thrown we still have a DataTable but without columns and rows of data. An assertion is performed using ops.IsSussessFul, this read-only property checks to see if an exception was thrown, if so returns false while no exception returns true.

If there was an exception ops.LastExceptionMessage can be presented to the user or used to send support an email or log the exception to a log file. For the full exception, this can be obtained via ops.LastException.

using System;
using System.Windows.Forms;
using BestPracticeSqlServer1.DataClasses;
  
namespace BestPracticeSqlServer1
{
    public partial class Form1 : Form
    {
        private BindingSource bsCustomers = new BindingSource();
        public Form1()
        {
            InitializeComponent();
  
            Shown += Form1_Shown;
        }
  
        private void Form1_Shown(object sender, EventArgs e)
        {
            {
                var ops = new DataOperations();
  
                bsCustomers.DataSource = ops.GetCustomersByStatus();
  
                if (ops.IsSuccessFul)
                {
                    dataGridView1.DataSource = bsCustomers;
                }
                else
                {
                    MessageBox.Show($"{ops.LastExceptionMessage}");
                      
                }
            }
        }
    }
}

Caveat
Using a BindingSource creates a little more coding as to obtain datacasting is required but well worth it. For example, to get the current row of the DataGridView above.

if (bsCustomers.Current != null)
{
    var currentRow = ((DataRowView) bsCustomers.Current).Row;
}

In the above code sample which loaded a DataTable the primary key is not a column in the DataGridView, using a BindingSource the following code provides access to the key on the current row.

if (bsCustomers.Current != null)
{
    var currentCustomerIdentifier = ((DataRowView) bsCustomers.Current)
        .Row.Field<int>("CustomerIdentifier");
}

This is the tip of functionality available, there are sorting, filtering and navigation methods too. 

Next level, extract all classes from the above project excluding the class DataOperations into a class project. This class project's sole responsibility is to create connectons and provide exception handling.


Remove the classes in the first project, add a reference to the newly created class project. Add a using statement for the class project. The code is exactly the same. This means each project can now use the class project for SQL-Server connections. Later the connection class will expand to include other databases e.g. MS-Access and Oracle.

using System;
using System.Windows.Forms;
using BestPracticeSqlServer2.DataClasses;
  
namespace BestPracticeSqlServer2
{
    public partial class Form1 : Form
    {
        private BindingSource bsCustomers = new BindingSource();
        public Form1()
        {
            InitializeComponent();
            Shown += Form1_Shown;
        }
  
        private void Form1_Shown(object sender, EventArgs e)
        {
            var ops = new DataOperations();
  
            bsCustomers.DataSource = ops.GetCustomersByStatus();
  
            if (ops.IsSuccessFul)
            {
                dataGridView1.DataSource = bsCustomers;
            }
            else
            {
                MessageBox.Show($"{ops.LastExceptionMessage}");
  
            }
  
        }
    }
}

Other databases


So far only SQL-Server has been used, let's examine connecting to Microsoft Access database. The following class inherits the base exception class used in examples above for SQL-Server.

Base exception class 

using System;
  
namespace DataConnectionsLibrary.GeneralClasses
{
    public class BaseExceptionProperties
    {
  
        protected bool mHasException;
        /// <summary>
        /// Indicate the last operation thrown an exception or not
        /// </summary>
        /// <returns></returns>
        public bool HasException
        {
            get
            {
                return mHasException;
            }
        }
        protected Exception mLastException;
        /// <summary>
        /// Provides access to the last exception thrown
        /// </summary>
        /// <returns></returns>
        public Exception LastException
        {
            get
            {
                return mLastException;
            }
        }
        /// <summary>
        /// If you don't need the entire exception as in LastException this 
        /// provides just the text of the exception
        /// </summary>
        /// <returns></returns>
        public string LastExceptionMessage
        {
            get
            {
                return mLastException.Message;
            }
        }
        /// <summary>
        /// Indicate for return of a function if there was an exception thrown or not.
        /// </summary>
        /// <returns></returns>
        public bool IsSuccessFul
        {
            get
            {
                return !mHasException;
            }
        }
    }
}

Access connection class

public abstract class AccessConnection : BaseExceptionProperties, IConnection
{
    private readonly OleDbConnectionStringBuilder _builder = 
        new OleDbConnectionStringBuilder { Provider = "Microsoft.ACE.OLEDB.12.0" };
  
    /// <summary>
    /// Database name and path
    /// </summary>
    protected string DefaultCatalog = "";
    public string ConnectionString
    {
        get
        {
  
            if (string.IsNullOrWhiteSpace(DefaultCatalog))
            {
                throw new Exception("Database name and path not provided.");
            }
  
            _builder.DataSource = DefaultCatalog;
  
            return _builder.ConnectionString;
  
        }
    }
    /// <summary>
    /// Connection string with user name and password generated using the protection class
    /// ProtectedDataConnections.ConnectionProtection
    /// </summary>
    public string ConnectionStringWithPassword { get; set; }
}


Just as in SQL-Server connection the database needs to be set, in the data class below which inherits AccessConnection class above the class constructor sets the database to a database in the same folder as the application executable. In the method, LoadCustomersPartialData ConnectionString is from AccessConnection class which used the Default catalog (meaning the database, not a table in the database).

The properties in the try/catch are inherited from the base exception class which the connection class inherited. These are available to the caller of DataOperations which can use assertion e.g. ops.IsSuccessFul to determine if an exception was thrown and if so like with SQL-Server can inspect the Exception object or simply use the Exception message for logging, email and also to display to users.


public class DataOperations : AccessConnection
{
    public DataOperations()
    {
        DefaultCatalog = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb");
    }
  
    /// <summary>
    /// Load data from joined tables
    /// </summary>
    /// <returns></returns>
    public DataTable LoadCustomersPartialData() 
    {
        using (var cn = new OleDbConnection(ConnectionString))
        {
            using (var cmd = new OleDbCommand { Connection = cn })
            {
                cmd.CommandText = 
                    "SELECT C.Identifier, C.CompanyName AS Company, C.ContactName AS Contact, CT.Title "
                    "FROM CustomersContactTitle AS CT "
                    "INNER JOIN Customers AS C ON CT.ContactTitleId = C.ContactTitleId "
                    "ORDER BY C.CompanyName;";
  
  
                var dt = new DataTable();
  
                try
                {
                    cn.Open();
                    dt.Load(cmd.ExecuteReader() ?? throw new InvalidOperationException());
                    dt.Columns["Identifier"].ColumnMapping = MappingType.Hidden;
  
                }
                catch (Exception ex)
                {
                    mHasException = true;
                    mLastException = ex;
                }
  
                return dt;
  
            }
        }
    }
}

In this example, the code above is called from the Shown event of a Windows form.

using System;
using System.Windows.Forms;
using WindowsFormsApp3.Classes;
using WindowsFormsApp3.Extensions;
  
namespace WindowsFormsApp3
{
    public partial class Form1 : Form
    {
        private readonly BindingSource _bsCustomerBindingSource = new BindingSource();
        public Form1()
        {
            InitializeComponent();
            Shown += Form1_Shown;
        }
  
        private void Form1_Shown(object sender, EventArgs e)
        {
            var ops = new DataOperations();
            _bsCustomerBindingSource.DataSource = ops.LoadCustomersPartialData();
  
            if (ops.IsSuccessFul)
            {
                dataGridView1.DataSource = _bsCustomerBindingSource;
                dataGridView1.ExpandColumns();
            }
            else
            {
                MessageBox.Show(
                    $"Failed to load data{Environment.NewLine}{ops.LastExceptionMessage}");
            }
        }
    }
}

Securing a Microsoft Access database


Securing a Microsoft Access database in the wild (used by internal or external customers) is important because at one time or another user will first poke at your data and then edit the data which without a proper understanding of data relationships can cause errors and missed placed data in your application. SQL-Server in an enterprise environment can be controlled for the most part outside of an application so here the focus is on Microsoft Access. 

Encryption of a Microsoft Access database
  • Open the selected database in exclusive mode.
  • Under the File menu select "Encrypt with Password"
  • Enter the password.
  • Close the database and test with a connection string with the password.
If the database opens correctly with the password then continue with encrypting the connection string while if the connection fails to open the database again.

  • Under File, options, client settings
  • Scroll to the bottom
  • Change default encryption to "use legacy encryption"
  • Press OK
Keeping with how an Access connection has been done above one difference will be introduced which is to store the connection string in the application configuration file, app.config.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
 <configSections>
 </configSections>
 <connectionStrings>
 <add name="SimpleLoad_OleDb_MS_Access2007_1.My.MySettings.CustomersConnection"
 connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
 Data Source=Database1.accdb;Jet OLEDB:Database Password=karen" />
 </connectionStrings>
 <startup>
 <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1"/>
 </startup>
</configuration>

Here is what a secured connection string appears in app.config using the code below.



Under project properties, setting tab, create a new setting of type "connection string" and place your connection string in as a value, suggested name is ConnectionString. In the source code provided with this article, add the class project ProtectedDataConnections as a reference to your project.

Create an instance (found in ProtectedDataConnections project) of ConnectionProtection in the data class. Setup encryption and decryption in the data class constructor as shown below. Note in this case the connection string setting is ConnectionString.

Note that there is a test connection method, this is not for the secure connection.

public class DataOperations : AccessConnection
{
    private ConnectionProtection connectionProtection = 
        new ConnectionProtection(Application.ExecutablePath);
  
    public DataOperations()
    {
        if (!connectionProtection.IsProtected())
        {
            connectionProtection.EncryptFile();
        }
  
        connectionProtection.DecryptFile();
        ConnectionStringWithPassword = Properties.Settings.Default.ConnectionString;
        connectionProtection.EncryptFile();
    }
    /// <summary>
    /// Provides a way for testing a data connection
    /// </summary>
    /// <returns></returns>
    public bool TestConnection()
    {
        mHasException = false;
  
        using (var cn = new OleDbConnection(ConnectionString))
        {
            try
            {
                cn.Open();
                return true;
            }
            catch (Exception ex)
            {
                mHasException = true;
                mLastException = ex;
            }
        }
  
        return IsSuccessFul;
  
    }
    /// <summary>
    /// Load data from joined tables
    /// </summary>
    /// <returns></returns>
    public DataTable LoadCustomersPartialData() 
    {
        using (var cn = new OleDbConnection(ConnectionStringWithPassword))
        {
            using (var cmd = new OleDbCommand { Connection = cn })
            {
                cmd.CommandText =
                    "SELECT Identifier, CompanyName, ContactName, ContactTitle FROM Customers;";
  
  
                var dt = new DataTable();
  
                try
                {
                    cn.Open();
                    dt.Load(cmd.ExecuteReader() ?? throw new InvalidOperationException());
                    dt.Columns["Identifier"].ColumnMapping = MappingType.Hidden;
  
                }
                catch (Exception ex)
                {
                    mHasException = true;
                    mLastException = ex;
                }
  
                return dt;
  
            }
        }
    }
}

Next steps 


Once all classes, connection, and exception classes have been fully tested the next evolution is to take these classes and place them in a Visual Studio solution, one class project. Any time you need to make a connection to a database add a reference to your project to the DLL files that comprise the connection and exception functionality.
 
This can be taken farther by creating a NuGet package. Creating a NuGet package is out of scope for this article/code sample but by following these instructions (quick-start) creating a package is fairly simple.

In the supplied source code all examples use a VB.NET version of the base exception and base connection classes found here, the mirror source code for C# is found here. The reasoning to use VB.NET for C# code samples is to get developers thinking about mixing programming languages. Suppose you are new to coding and can only find a solution to a problem in VB.NET when programming in C# or can only find a C# solution when coding in VB.NET and in both cases a language converter does not work, we need to consider using more than one language, at least until skills are honed to do the conversion yourself.

Here are both programming languages with the exact same functionality.



The published NuGet package used in the code samples can be found here on NuGet

Console installation

PM> Install-Package BaseConnectionLibrary -Version 1.0.1

Both base classes reside in the package which can be used together for connection or use the base exception class alone. 

Example of using the base exception class. In the following example, an attempt is made to read lines from a file, in this case, the file does not exist.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using BaseConnectionLibrary;
  
namespace FileHandlingExceptions.Classes
{
    public class FileOperations : BaseExceptionProperties
    {
        public List<string> ReadFileWithBaseExceptionProperties(string fileName)
        {
            var lines = new List<string>();
  
            try
            {
                lines = File.ReadAllLines(fileName).ToList();
            }
            catch (Exception e)
            {
                mHasException = true;
                mLastException = e;
            }
            return lines;
        }
    }
}

Invoking

private void readExistingBaseExceptionPropertiesFileButton_Click(object sender, EventArgs e)
{
    var ops = new FileOperations();
    var lineList = ops.ReadFileWithBaseExceptionProperties(_goodFileName);
  
    MessageBox.Show(ops.IsSuccessFul ? "Success" : ops.LastExceptionMessage);
}

If the file was present a message appears indicating success while in this case an exception method is displayed. For an actual application ops.IsSuccessFul would be in a conditional if statement, if true use lineList while false the list is empty.  

Without the base exception class.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using BaseConnectionLibrary;
  
namespace FileHandlingExceptions.Classes
{
    public class FileOperations : BaseExceptionProperties
    {
        public List<string> ReadFileWithOutBaseExceptionProperties(string fileName) => 
            File.ReadAllLines(fileName).ToList();
    }
}

The caller must use a try/catch statement as shown below.

Here is what a secured connection string appears in app.config using the code below.
private void readNonExistingConventionalFileButton_Click(object sender, EventArgs e)
{
    var ops = new FileOperations();
  
    try
    {
        var lineList = ops.ReadFileWithOutBaseExceptionProperties(_badFileName);
  
        MessageBox.Show("Success");
    }
    catch (Exception exception)
    {
        MessageBox.Show(exception.Message);
    }
}

The same holds true for other exceptions, explore the following project in the supplied source code.



Rules

Connection string rules


When/if you encountering issues creating connection strings different than what has been presented make sure to follow these rules [stackoverflow.com].

  • All blank characters, except those placed within a value or within quotation marks, are ignored
  • Blank characters will though affect connection pooling mechanism, pooled connections must have the exact same connection string
  • If a semicolon (;) is part of a value it must be delimited by quotation marks (")
  • Use a single-quote (') if the value begins with a double-quote (")
  • Conversely, use the double quote (") if the value begins with a single quote (')
  • No escape sequences are supported
  • Names are case iNsEnSiTiVe
  • If a keyword contains an equal sign (=), it must be preceded by an additional equal sign to indicate that it is part of the keyword.
  • If a value has preceding or trailing spaces it must be enclosed in single- or double quotes, ie Keyword=" value ", else the spaces are removed.

Exception rules


  • Use try/catch/finally blocks to recover from errors or release resources
  • Handle common conditions without throwing exceptions
  • Design classes so that exceptions can be avoided
  • Avoid catching exceptions that you’re unable to handle fully.
  • Avoid hiding (discarding) exceptions you don’t fully handle.
  • Consider terminating the process by calling System.Environment.FailFast() if the program encounters a scenario where it is unsafe for further execution.

Data connection rules


  • Coupled with basic exception rules, be proactive e.g.
  • Databases that are not server based, check if the database exists prior to opening the database.
  • Databases that rely on a service running, if a connection fails use assertion to determine if the service is running. See the following GitHub repository.
  • Ensure valid user credentials are set for server-based databases at the database or in active directory.

Summary

In this article, developers have learned how to connect to popular databases like SQL-Server, Microsoft Access and Oracle using base classes which reside in either class projects or NuGet packages rather than composing connection strings in a Windows form or directly in a data class. Inherited exception handle which may be used with composing connection strings, reading and writing data to using base exception class for other common operations. At the very core of this article what should be taken away is having a consistent method to connect to data and accessing data in popular databases.

See also

MS-Access best practices
.NET Defensive programming 
Move items up/down with Windows forms controls  
SQL-Server/C# Find duplicate records with identity
SQL-Server alternate method to remove records in C#  

Source code

GitHub repository for code samples non-NuGet packages and NuGet packages..
GitHub repository for Nuget package source.
NuGet package for base connection and base exceptions.