SQL-Server: Alternate methods to remove records using C#
Introduction
This article provides an alternative to physically removing records in a SQL-Server database tables along with timestamping records when they are added, modified or marked as inactive. In many solutions developers will provide methods to physically remove records from a database which can only be recovered from the last backup of the database. This means if the last backup was the night prior to removing one or more records they are gone for good. If there are no backups then the records physically removed are gone for good.
History/retention of removed records can be good for many reasons, a record was removed accidentally, a disgruntled employee is told they are to be left off at weeks end. In retaliation this employee deletes all records in a table that are critical to the business.
Backing up a database is a must but as stated above this will not protect your data completely, neither will an alternate method for marking records as inactive but this is much better than doing only one of these operations or neither of the operations.
Step 1: Backup database
Backup your database using the backup functionality of SSMS (SQL-Server Management Studio).
Step 2: Define DateTime column in table
When creating a new table using T-SQL, define a column as date time, for this article ModifiedDate, set the property of this column "Default Value or Binding" to GETDATE(). Define another column named InUse as bit, set the property of this column "Default Value or Binding" to 1 (equates to true).
In script
ModifiedDate DATETIME NULL DEFAULT GETDATE(),
InUse BIT NULL DEFAULT 1
If the table exists, you can open the table definition in SSMS by right clicking on the table and select "Design", add the two columns and set "Default Value or Binding" as above, GETDATE() and 1.
Step 3: Modify the Delete statement in SQL
Modify your DELETE SQL statement to update the column InUse rather than physically removing records along with setting the ModifiedDate which here is a hard-coded value of the current date/time using GETDATE SQL function. Modify your UPDATE statements if columns are updated but the record is marked inactive. For new records, ModifiedDate will auto-populate with the current date/time and InUse will be set to 1/true which means the record is automatically set to active.
Instrumenting into data classes
When retrieving data from a table implement as shown below which has an optional parameter set to true which means return all active records for a Customer table. If there is a need to return all inactive records pass the value for pInUse as false.
Implementing the above is dependent on how data is being read in your current solution. Presented above can be done with a managed provider as shown using a connection and command object or with data adapters which return a DataSet or DataTable. With a little effort this can be done with Table Adapters too within the designer added to the form reading data from a database.
Download
Find the code on GitHub.
/// <summary>
/// Read Customers table by InUse, true for active Customers, false for
/// inactive Customers
/// </summary>
/// <param name="pInUse">True for active, false for inactive</param>
/// <returns></returns>
public DataTable Customers(bool pInUse = true)
{
var dt = new DataTable();
CustomerStatements cs = new CustomerStatements();
var selectStatement = cs.SelectStandard();
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText = selectStatement;
try
{
cmd.Parameters.AddWithValue("@InUse", pInUse);
cn.Open();
dt.Load(cmd.ExecuteReader());
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return dt;
}
Updates can remain as they are since default values have been set for the two new columns or you can set the column values within the appropriate UPDATE statements as shown below.
/// <summary>
/// Activate a specific Customer record by primary key
/// </summary>
/// <param name="pIdentifier">Primary key</param>
/// <returns></returns>
public bool ActivateCustomer(int pIdentifier)
{
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText = "UPDATE dbo.Customers SET InUse = 1, ModifiedDate = GETDATE() " +
"WHERE CustomerIdentifier = @Id";
try
{
cmd.Parameters.AddWithValue("@Id", pIdentifier);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
Using a un-modified UPDATE will set InUse to 1 (considering the prior value was 0/false), ModifiedDate to the current date/time. It would not be common place to perform this operation, rename a customer and make them active yet it is possible.
/// <summary>
/// Activate a specific Customer record by primary key
/// </summary>
/// <param name="pIdentifier">Primary key</param>
/// <param name="pCustomerName">Customer name</param>
/// <returns></returns>
public bool ActivateCustomer(int pIdentifier, string pCustomerName)
{
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText = "UPDATE dbo.Customers SET CustomerName = @CustomerName " +
"WHERE CustomerIdentifier = @Id";
try
{
cmd.Parameters.AddWithValue("@Id", pIdentifier);
cmd.Parameters.AddWithValue("@CustomerName", pCustomerName);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
The following method would be better done with SSMS or creating a .sql file in your project and executing the SQL but some are more comfortable with this way, use code. Execute this code to make all customer records active. This is purely for development only as its doubtful to set all inactive records to a state of active.
/// <summary>
/// Activate a specific Customer record by primary key
/// </summary>
/// <returns></returns>
public bool ActivateAllCustomer()
{
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText = "UPDATE dbo.Customers SET InUse = 1, ModifiedDate = GETDATE()";
try
{
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
Step 4: Create a unit test project
Create a unit test project to cover major operations. For those who have not done many unit test you could simply test these operations directly in your application. In the code samples (test project) provided there are several unit test which cover the basics then in the user interface project the methods tested are visually inspected as follows; Press the delete button in the BindingNavigator, press the "Inactive" button to verified the record was marked inactive with the primary key and company name. A second check would be to visually confirm this in SSMS via a SELECT WHERE statement.
These are the unit test (on Github). At this point consider these a base, more unit test may be added dependent on your comfort level.
using System.Collections.Generic;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace BackendUnitTest
{
[TestClass]
public class UnitTest1 : TestBase
{
[TestInitialize]
public void Init()
{
ResetCategoryInUseField();
ResetContactsInUseField();
ResetCustomersInUseField();
}
/// <summary>
/// Test marking several records as not in use
/// </summary>
[TestMethod]
public void CategoryInUse()
{
var tableName = "Categories";
var primaryKey = "CategoryID";
var identifierList = new List<int>() {2,6,7};
SetInUse(tableName, primaryKey, identifierList,false);
Assert.IsTrue(GetInUse(tableName, primaryKey, identifierList, false),
$"Expected {identifierList.Count} records to be marked as not in use");
}
[TestMethod]
public void ContactsInUse()
{
var tableName = "Contact";
var primaryKey = "ContactIdentifier";
var identifierList = new List<int>() { 27, 44, 60, 99 };
SetInUse(tableName, primaryKey, identifierList, false);
Assert.IsTrue(GetInUse(tableName, primaryKey, identifierList, false),
$"Expected {identifierList.Count} records to be marked as not in use");
}
[TestMethod]
public void CustomersInUse()
{
var tableName = "Customers";
var primaryKey = "CustomerIdentifier";
var identifierList = new List<int>() { 2, 5, 11, 34, 42, 90, 87};
SetInUse(tableName, primaryKey, identifierList, false);
Assert.IsTrue(GetInUse(tableName, primaryKey, identifierList, false),
$"Expected {identifierList.Count} records to be marked as not in use");
}
[TestMethod]
public void ProductsInUse()
{
var tableName = "Products";
var primaryKey = "ProductID";
var identifierList = new List<int>() { 10, 13, 34, 43};
SetInUse(tableName, primaryKey, identifierList, false);
Assert.IsTrue(GetInUse(tableName, primaryKey, identifierList, false),
$"Expected {identifierList.Count} records to be marked as not in use");
}
/// <summary>
/// This test has a primary key that does not exists
/// which validates the prior test are valid.
/// </summary>
[TestMethod]
public void ProductsInUseExpectFailure()
{
var tableName = "Products";
var primaryKey = "ProductID";
var identifierList = new List<int>() { 10, 13, 34, 43, 177 };
SetInUse(tableName, primaryKey, identifierList, false);
Assert.IsFalse(GetInUse(tableName, primaryKey, identifierList, false),
$"Expected {identifierList.Count} records to be marked as not in use");
}
}
}
Step 5: Provide a user interface for activating inactive records
This is done by selecting records where InUse is set to 0/false. Provide the primary key and columns that the person running the interface can clearly identify what needs to be reactivated. In some cases this child relations may be needed to completely confirm the correct record is to be reactivated. For instance, with customers, there may be two customers with the same name but their contact information is a clear indicator of which record to restored.
Reactivate demonstration
In the following screen, the red X was depressed to remove the current customer record. Rather than outright removing the customer record they are presented with a prompt which defaults to the No button. By setting No as the default button it's an intentional act to remove the current record. The author has heard from customers who simply pressed ENTER with the default button set standard to Yet and said it was a mistake, this way it's an intentional and deliberate action.
Note the two disabled buttons, they are here for demonstration purposes, when there is at least one deactivated customer record both are enabled. Press "Get" button to get a list of deactivated customer records by customer name. If there are many deactivated records open the ComboBox, start typing as this is an auto complete ComboBox. Select a customer, press "Activate" button to reactivate this customer record. This would be one way to perform a reactivation of a record (and if child reference tables if needed reactivate these records too).
The following screen shot is after deactivating several records, note the count in the BindingNavigator against the prior count of records in the screenshot above. Pressing "Inactive" button presents the deactivated records with primary key and company name. Also note the "Get" and "Activate" buttons are not active.
The following screenshot is after pressing the "Get" button which populates the ComboBox with deactivated customer records. Start typing for the auto complete feature to assist in finding a customer record. Press "Activate" button which prompts for confirmation to activate the selected customer.
Issues
When setting up a column to determine if records should be shown in a application you must also consider the following.
Relational data, for example, customers have contact information which with a good database schema the contact information will be broken out to one or more tables. A customer may have many contacts over a timespan where there work, retired, move on and others fill their position. This means the active column and it's relation to the now is critical as you don't want to view old contacts. This means when joining tables you need to consider this.
Other applications using your database or users that may have direct access to your database or BI (Business Intelligence). This means they need to be educated. For some another option may be to create read-only views of data that takes care of InUse column.
Source code notes
All code presented for this article is in C# except for one class project which is responsible for retrieving a SELECT statement broken down over several lines for easy of maintaining and reading.
Test code notes
Prior to any test running the tables to test against have InUse and ModifiedDate set to current date time and active for all records. This means that afterwards running the front end project if there were inactive records before they are now all active.
There are two methods in the test class that are found in a base class TestBase which are setup generically so they work against any table with the columns InUse and ModifiedDate.
protected bool SetInUse(string pTableName, string pIdentifier, List<int> pIdentifiers, bool pInUse = true)
{
var inConditions = $"({string.Join(",", pIdentifiers.ToArray())})";
var updateStatement = $"UPDATE dbo.{pTableName} SET InUse = @InUse, ModifiedDate = GETDATE() " +
$"WHERE {pIdentifier} IN {inConditions}";
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText = updateStatement;
try
{
cmd.Parameters.AddWithValue("@InUse", pInUse);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
protected bool GetInUse(string pTableName, string pIdentifier, List<int> pIdentifiers, bool pInUse = true)
{
var inConditions = $"({string.Join(",", pIdentifiers.ToArray())})";
var updateStatement = $"SELECT COUNT(InUse) FROM dbo.{pTableName} WHERE {pIdentifier} IN {inConditions} " +
$"AND InUse = @InUse";
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText = updateStatement;
try
{
cmd.Parameters.AddWithValue("@InUse", pInUse);
cn.Open();
var temp = (int) cmd.ExecuteScalar();
return temp == pIdentifiers.Count;
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
Several other methods in TestBase class are not generic in the event special test need to be executed other than the InUse and ModifiedDate test.
protected bool ResetCategoryInUseField(bool pInUse = true)
{
var updateStatement = "UPDATE dbo.Categories SET InUse = @InUse";
var selectStatementInUse = "SELECT COUNT(CategoryID) FROM dbo.Categories WHERE InUse = @InUse";
var selectStatementTotal = "SELECT COUNT(CategoryID) FROM dbo.Categories";
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText = updateStatement;
try
{
cmd.Parameters.AddWithValue("@InUse", pInUse);
cn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = selectStatementTotal;
var totalRows = (int) cmd.ExecuteScalar();
cmd.CommandText = selectStatementInUse;
return totalRows == (int) cmd.ExecuteScalar();
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
protected bool ResetCustomersInUseField(bool pInUse = true)
{
var updateStatement = "UPDATE dbo.Customers SET InUse = @InUse";
var selectStatementInUse = "SELECT COUNT(CustomerIdentifier) FROM dbo.Customers WHERE InUse = @InUse";
var selectStatementTotal = "SELECT COUNT(CustomerIdentifier) FROM dbo.Customers";
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText = updateStatement;
try
{
cmd.Parameters.AddWithValue("@InUse", pInUse);
cn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = selectStatementTotal;
var totalRows = (int)cmd.ExecuteScalar();
cmd.CommandText = selectStatementInUse;
var temp = (int) cmd.ExecuteScalar();
return totalRows == temp;
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
protected bool ResetContactsInUseField(bool pInUse = true)
{
var updateStatement = "UPDATE dbo.Contact SET InUse = @InUse";
var selectStatementInUse = "SELECT COUNT(ContactIdentifier) FROM dbo.Contact WHERE InUse = @InUse";
var selectStatementTotal = "SELECT COUNT(ContactIdentifier) FROM dbo.Contact";
using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
{
using (SqlCommand cmd = new SqlCommand { Connection = cn })
{
cmd.CommandText = updateStatement;
try
{
cmd.Parameters.AddWithValue("@InUse", pInUse);
cn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = selectStatementTotal;
var totalRows = (int)cmd.ExecuteScalar();
cmd.CommandText = selectStatementInUse;
return totalRows == (int)cmd.ExecuteScalar();
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
DataGridView bonus
In the class project there is a method ConfigureDataGridViewColumns which sets up text to display for each DataGridViewColumn, should the column be visible and if so it's display index. A list is used in the form shown event to configure the DataGridView for position, header text and visible or not visible. In a future article this will be expanded upon using a utility that will permit configuring an entire database with these properties rather than hard coded in a single project.
Column configuration utilizing VB.NET (could have been coded in C#)
''' <summary>
''' Define how columns are setup for a DataGridView
''' </summary>
''' <returns></returns>
''' <remarks>
''' A future TechNet article from Karen Payne will introduce how to do this
''' in the database which will take care of everything rather than a developer
''' having to perform this in their database.
''' </remarks>
Public Function ConfigureDataGridViewColumns() As List(Of DataGridViewColumnDefinition)
Return New List(Of DataGridViewColumnDefinition) From
{
New DataGridViewColumnDefinition() With {
.Name = "CustomerIdentifier",
.DisplayText = "Id", .Position = 0,
.Visible = False},
New DataGridViewColumnDefinition() With {
.Name = "CompanyName",
.DisplayText = "Name",
.Position = 1,
.Visible = True},
New DataGridViewColumnDefinition() With {
.Name = "ContactTitle",
.DisplayText = "Title",
.Position = 2,
.Visible = True},
New DataGridViewColumnDefinition() With {
.Name = "ContactNameFullName",
.DisplayText = "Contact",
.Position = 3,
.Visible = True},
New DataGridViewColumnDefinition() With {
.Name = "Street",
.DisplayText = "Street",
.Position = 8,
.Visible = True},
New DataGridViewColumnDefinition() With {
.Name = "City",
.DisplayText = "City",
.Position = 5,
.Visible = True},
New DataGridViewColumnDefinition() With {
.Name = "PostalCode",
.DisplayText = "Postal",
.Position = 6,
.Visible = True},
New DataGridViewColumnDefinition() With {
.Name = "CountryIdentfier",
.DisplayText = "Country Identfier",
.Position = 7,
.Visible = False},
New DataGridViewColumnDefinition() With {
.Name = "Phone",
.DisplayText = "Telephone",
.Position = 4,
.Visible = True},
New DataGridViewColumnDefinition() With {
.Name = "ModifiedDate",
.DisplayText = "Modified",
.Position = 9,
.Visible = False},
New DataGridViewColumnDefinition() With {
.Name = "InUse",
.DisplayText = "Active",
.Position = 10,
.Visible = False},
New DataGridViewColumnDefinition() With {
.Name = "ContactInUse",
.DisplayText = "Active",
.Position = 11,
.Visible = False},
New DataGridViewColumnDefinition() With {
.Name = "CountryName",
.DisplayText = "Country",
.Position = 12,
.Visible = False},
New DataGridViewColumnDefinition() With {
.Name = "ContactIdentifier",
.DisplayText = "Contact Id",
.Position = 13,
.Visible = False},
New DataGridViewColumnDefinition() With {
.Name = "CountryIdentifier",
.DisplayText = "Country Id",
.Position = 14,
.Visible = False}
}
End Function
Main form
Implemented in the main form (github download here) shown event in C#.
var configItems = ops.CustomerConfigurationItems();
foreach (DataGridViewColumnDefinition item in configItems)
{
if (!dataGridView1.Columns.Contains(item.Name)) continue;
dataGridView1.Columns[item.Name].DisplayIndex = item.Position;
dataGridView1.Columns[item.Name].Visible = item.Visible;
dataGridView1.Columns[item.Name].HeaderText = item.DisplayText;
}
Long SQL statements
Although C# can easily handle a larger SQL statement, VB.NET handles these elegantly using XML literals as shown below.
''' <summary>
''' Select customers standard statement
''' </summary>
''' <returns></returns>
Public Function SelectStandard() As String
Return <SQL>
SELECT C.CustomerIdentifier,
C.CompanyName ,
ContactType.ContactTitle ,
CT.FirstName + ' ' + CT.LastName AS ContactNameFullName,
C.Street ,
C.City ,
C.PostalCode ,
C.CountryIdentfier ,
C.Phone ,
C.ModifiedDate ,
C.InUse ,
CT.InUse AS ContactInUse ,
Countries.CountryName ,
C.ContactIdentifier ,
Countries.id AS CountryIdentifier
FROM Customers AS C
INNER JOIN Contact AS CT ON C.ContactIdentifier = CT.ContactIdentifier
INNER JOIN ContactType ON C.ContactTypeIdentifier = ContactType.ContactTypeIdentifier
INNER JOIN Countries ON C.CountryIdentfier = Countries.id
WHERE ( C.InUse = @InUse AND CT.InUse = @InUse );
</SQL>.Value
End Function
Setup before running any code
Change script.sql found in the class project BackEndData as follows. Validate the path to where SQL-Server on your machine store database is the same as in the script.
In the class project BaseLibrary change the property DatabaseServer from KARENS-PC to your server name or if using SQL-EXPRESS change to .\SQLEXPRESS.
Conclusion
In this article you have learn an alternate method to remove records by marking them as inactivate rather than physically remove these records permanently. Also methods within the source code show generic test method helpers.
Source code
Full source code on GitHub.