SQL Table Toolkit (STT)

• Download the DLL file - 395.8 KB
• Download the Demo project - 540.4 KB
• Download the Source Code - 1.8 MB

Introduction

SQL Table Toolkit is a Class Library offering a quite large amount of tools for fast and easy work with SQL Tables. Every table is defines as a class and its structure can be synchronized with the SQL Database. Every table class has its basic functionalities and methods that are expandable in the table class directly or elsewhere in an application. For the visual presentation of a table, STT uses always a DataGridView.

The table SQL statements are auto generated but it can also use custom SQL statements. More of that later. The SQL statements for table filtering are also auto generated and have methods for manipulation.

Every functionality of the tables are in a table context menu. With some code, they are implementable to form controls. All that functions can be restricted so that we can decide if a user can add or delete a row. The basic functionalities are:

• filter table with a filter form,
• remove the filter,
• table ordering,
• adding, deleting and copying rows,
• table refreshing and  refreshing of ComboBox sources in the table,
• change protocol and view of it,
• custom column order for every user and its maintaining,
• calculation of basic values like sum and max
• Report functionality that exports the table to a dynamic RDLC report. That report can then be exported to excel, pdf or printed out directly

The picture above shows a ContextMenu of a Table.

For the change protocol, STT has some integrated tables for user and permission management. With that user management is also a login module integrated that allows user verification on application startup.

Background

Working on large applications with a SQL Database can be a messy thing to do. If the application gets larger, the SQL Background gets harder to maintain. Oooo, and God beware if there is a change to make…if I add a column to a table. Would the application parts that work with that table use it? What if I add in one place a new row and the column is not in the SQL statement.

Maybe for large teams with a programing plan and guidelines it is something they have to deal with. Also, lots of tools, best practices etc… can help maintain all that. I googled my brain off and did not find something that fit my needs: easy and fast programming of SQL data manipulation. It is just that the work in my company with programming is a “one man show” and changes, new functionality, new tables and reports have to be done in minimum of time.

That is why most of the time work in with the production database. Yeas, I know. That is madness, are you crazy, what is wrong with this man…. but (there is not BUT! OK I get it.). I know the risks and sh..t happened. Still it is for me more efficient to work like that and repair errors with Database backups than testing all that on my own. Therefore, I use my end users as beta testers. The first testing I do on my own searching for critical errors and similar things and the small bugs are on the end users. However, to not leave the topic of STT we just continue on.

I am no programmer that finished school for this. I am not sure if I can define myself as a programmer. It all started with excel problem solving over Access databases with small functionality to SQL databases witch needed real code with a real programming language like C#. So I started reading books about OOP and find out that everything I need to make this little tool for me. Now after more than one year of usage and evaluation I decided to share it with the internet community. If someone can use it for his own applications, I would be happy. This tool is running two ERP systems in two middle size companies and I do not often hear that there are some kind of problems or errors.

If out there is someone who recognizes a terrible mistake in my approach or code please let me know. I would be glad for any kind of suggestion and critics.  

Basic parts of STT

Table basics

Of course, the main class of STT is Table.  Even if it is the main part of the Toolkit, it is a child class of DgvAdapter. DgvAdapter is holding all functionality for binding a table to a DataGridView. If a table is not binded to a DataGridView, the DgvAdapter is not used. More about the DgvAdapter, we will discuss on a later article part.

A table is a composition of columns. The Column class stores all properties of a table column. The Columns class is a list of all columns that a Table has. 

Filter basics

The main class for a table filter is Parameter. A parameter is a part of the table filter. Every table has a property Dictionary where all the table filter parameters are stored. For the filter, we use a Dictionary and not a List because a Dictionary is better for manipulation of its content. In a filter, we constantly need to add, manipulate and remove parameters. This would cause too much problems if using a List. Because we want to manipulate the table filter directly from the table the parameters Dictionary is a property of the table and not separated in an own class like Columns. It could be done but we would have no benefit from it. 

Connection basics

The table connection is stored in a Table Property called Connection with a getter and setter. By getting the Table Connection, it always returns an Open Connection and not a connection string. By setting the property, the table SQLConnection is saved to id.

The Class SQLConnectionAdapter is used to initialize a SQL Connection or to open it. The Open Connection method checks if the connection is Closed or Broken and opens it if it is. 

DgvAdapter

As mentioned, the DgvAdapter is the parent class of the Table class. Its main function is to do all the hard work for binding a Table to a DataGridView and everything that is coming with it. The DgvAdapter is like an interface from all the STT code to the user so that not only we trough the code can manipulate the table data but the user also. Later in a demo application, we will demonstrate how to bind a table to a DataGridView using STT.

On binding a Table to a DataGridView the DgvAdapter is catching the DataGridView events to make all functionality work like it should. That is allowing us to make also every custom change to the DataGridView that is not implemented in the STT.

Synchronisation

For the synchronization of the Tables to the SQL Database, we use just one class called Synchronization. For synchronizing a table, it is enough to send the assembly where it is stored and a method will do the rest. It recognizes every table in the in the assembly and tries to synchronize them with the database. It is important to say that the method does not delete rows, columns, tables or databases. This is because of security reasons during work on production databases. One other reason for that is that the applications using STT, can be updated asynchronously. That allows us that one user can work with one version and another with an older one even if they have different database structures. The deletion of columns and tables is left to the developer. There are methods that can be used for that but I personally would not risk it. 

Using the code

Demo application

Create a table

To not make long explanation how something works or is done we just make a small demo application showing some basic functionality of the STT. We will go step by step trough the creation of the project:

• Create a new Windows Form Application
• Set a reference to the STT dll file

After that, we are ready to work with the STT.

Let us now create a new class and call it Table1. We make the class public so we can access it from other projects. In that class we make a initialization method. In that method we can define the Table connection and user permissions. I prefer to store the table connection in the project default settings string value, that I always call Connection. I use a string value because it can be manipulated and SQLConnection setting cannot. Lets have a look at our first table class:

        public Table1()
        {
            Connection = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.Connection);
            AllowUserToAddRow = true;
            AllowUserToDeleteRow = true;
            AllowuserToCopyRow = true;
            AllowUserToFilter = true;
        }

Now that we have a Table we will now add Columns to it:

        public override void AddColumnsToTable()
        {
            Columns.AddDefaultColumns(this);
            Columns.AddTextBox(this, "TextColumn", "Text Col.");
            Columns.AddFloatBox(this, "FloatColumns", "Float Col.");
            Columns.AddDateBox(this, "DateColumn", "Date Col.");
        }

For that we override the Table method ADDColumnsToTable. The first line of code in the method is adding STT defualt Columns to the Table. Those Columns are ID and Deleted. 

ID is the Table identifier and Primary Key in the SQL Database table. Deleted is a Column storing boolean values that are by default false. They are used to mark deleted rows in the Table. That also means that by adding the default Columns a user can not realy delete rows in the SQL Database Table. They are just hidden by a Table default filter witch we are going to add right now to it:

         public override void SetDefaultParameters()
        {
            base.SetDefaultParameters();
            SetParameter("Deleted", false);
        }

The method SetDefaultParameters is always called when a table is filtered. That way the user can't remove the filter that only rows with Deleted=false will be shown to him. 

That is all we need to make a table. Let's have a look at the whole table code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using STT;

namespace STTDemoProject
{
    public class Table1: Table
    {

        public Table1()
        {
            Connection = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.Connection);
            AllowUserToAddRow = true;
            AllowUserToDeleteRow = true;
            AllowuserToCopyRow = true;
            AllowUserToFilter = true;
        }

        public override void AddColumnsToTable()
        {
            Columns.AddDefaultColumns(this);
            Columns.AddTextBox(this, "TextColumn", "Text Col.");
            Columns.AddFloatBox(this, "FloatColumns", "Float Col.");
            Columns.AddDateBox(this, "DateColumn", "Date Col.");
        }

        public override void SetDefaultParameters()
        {
            base.SetDefaultParameters();
            SetParameter("Deleted", false);
        }

    }
}

Add the table to a Form

To use the table in a Form so that the user can work with it we need to have a Form and a DataGridView on witch to bin the table. In our Demo project, we will use the default Form1. In that, we add a DataGridView and set its Dock Property to Fill so that it fills the Form. 

In the Form code we need just two lines of code to bind it to the DataGridView.

We need to initialize the table:

Table1 tblTable1 = new Table1();

And we need to Bind it to the DataGridView that we added previously to the Form:

tblTable1.BindTableToDataGridView(dataGridView1);

That is all we need to make the table work, as it should. Lets have a look at the whole Form1 code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace STTDemoProject
{
    public partial class Form1 : Form
    {
        Table1 tblTable1 = new Table1();

        public Form1()
        {
            InitializeComponent();

            tblTable1.BindTableToDataGridView(dataGridView1);
        }
    }
}

Initialize the table

To make the Demo application work we have to tell the table to synchronize on startup. Before that we also have to tell the table what connection should it use. This we will do in the Program class. To not hardcode the connection we can use a STT integrated module. It is a form that allows us to enter and test a SQL connection. In the below code we are initializing that form and using its result to proceed with the connection or to leave the application. If the application is alright we will save it to our settings value Connection and save the Default application Settings.

SQLConnectionForm frmConnection = new SQLConnectionForm(Properties.Settings.Default.Connection);
frmConnection.ShowDialog();

if (frmConnection.DialogResult != DialogResult.OK)
   return;

Properties.Settings.Default.Connection = frmConnection.ConnectionString;
Properties.Settings.Default.Save();

IMPORTAND: if the SQL connection is stored directly in the table or added manualy by code the MultipleActiveResults  must be set to true and the max pool size has to be set to a large number ( I use 300000).

After setting up the connection, we need to initialize the table synchronization. However, not only for the table we created but also for the STT Tables because we need them to work to even if we do not use them. The below code makes the initialization:

Initialization.Start(Assembly.Load("STT"), Properties.Settings.Default.Connection);
Initialization.Start(Assembly.Load("STTDemoProject"), Properties.Settings.Default.Connection);

UPDATE:  Now we can also change the language used by the STT (for now we have only EN and GE). The default one is EN. If we do not set a language it will be autoimaticaly EN. But for demonstration purpose we will set the EN anyway:

Language.SetLanguage(Languages.EN);

The whole code of the Program class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using STT;
using System.Reflection;

namespace STTDemoProject
{
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {

            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);

            SQLConnectionForm frmConnection = new SQLConnectionForm(Properties.Settings.Default.Connection);
            frmConnection.ShowDialog();

            if (frmConnection.DialogResult != DialogResult.OK)
                return;

            Properties.Settings.Default.Connection = frmConnection.ConnectionString;
            Properties.Settings.Default.Save();

            Initialization.Start(Assembly.Load("STT"), Properties.Settings.Default.Connection);
            Initialization.Start(Assembly.Load("STTDemoProject"), Properties.Settings.Default.Connection);

            Language.SetLanguage(Languages.EN);
            
            Application.Run(new Form1());
  
        }
    }
}

Now we are done with the demo project and it can be started.

Start the demo application

On startup of the application, we will see a Form where we can setup our SQL connection.

After entering, a valid connection and testing it press OK to continue. After that, we will see our table in the Form1 like on this picture:

By clicking on New we can add new rows or Delete delete rows. After adding some data to the table, we can try the report module by clicking on Report:

That will open us the dynamic report of the table:

By clicking on "Change protocol" or Cntrl+P we can see the change protocol of a specific row. Because we didn't use the Login module the user is not set:

 

And ofcourse the filtering of the table is integrated by clicking on Filtern it opens a Form in witch we can add and remove filter parameters of the table:

 

 

The Demo project can be downloaded. The download link is at the arcticle begining.

Other features

Beside the main table manipulation, change protocol and dynamic reports the STT Library has also:

• language of the STT forms can be changed
• user login and user manipulation. An article to the topic: Login module using STT
• TreeView menu with TabControl with user permissons and form opening in TabControl
• update module for custom update functions, settings and update checking
• user permissions
• table column settings
• Bind ComboBox and other controls to DataGridView using STT
• permission for a TreeView menu
• error handling with error logs and automatic email notification to the admin (with monitor screenshot)
• easy filtering of the table without using hardcodes SQL statements
• every change in the DataGridView is automaticaly saved to the server - no need for a save button

It is made for creating small and middle business size ERP applications. For that STT allows that:

• the applications can be splited in horisontal modules - every department or business part can have hes own module that can be developt seperately
• the applications can be splited in vertical modules - the tables can be seperated to a dll file allowing us to use the tables refering to a dll and not to a project (not circular references)
• the bussiness logic can be writen directly to the table - if we combine it with database dll files we don't have to rewrite logic in diferent user interfaces

Restrictions

• Table structure-As mentioned before the tables relay on that they have an ID as identifier and a Deleted column for defining deleted rows. The presentation of SQL table data doesn't depend on this two column but the manipulation does. By the benefits I earn using this tool I can live with that. 
• Filtering by the user - the filter of the user has only one dimension. The user can not make a filter like this WHERE (User=@UserID AND Deleted=1) OR (User=@UserID2) . The brekades make the diference.
• Filtering in the code - the filter of a table using code is restricted to 2 dimensions. That means that the abowe statement can be made using permission group ID-s (more of that in a other artikle) but more komplikated statements can not be made. For sutch there could be used custom code and custom table bidning source witch STT supports. 
• German language - some of the code/enums are still in the german language. I will translate them as soon possible. Because I have to implement the change to 2 ERP Systems i need more time for it. 

Knwon errors

• SQL Connection - if you make lots of table manipulations using STT (thousands in a second) the SQL connection pool can go full and it will crash the application. I "solwed" this by setting the connection max pool size to 30000. Also for the MultipleActiveResults has to be set to True!

Points of Interest

Because the STT has to mutch functionalities for one article and to make this first article short and clear, other functionalities I will discous in seperated articles where I can go more in detail.

History

12.03.2015 - First release.

17.03.2015 - Multiple languages added to the STT

19.03.2015 - Added reference article to the login module

25.03.2015 - Added reference to the articles for control binding and treeView menu