Intelligent Combobox for MySQL/SQL Server Database Applications using VB.NET/C#

• Download source code - 14.6 KB

Introduction

Combobox is one of the main graphical user interfaces (GUI) used in any type of computer-based software application, either internet or desktop. Since data can be displayed using its drop-down feature, it is a commonly used GUI and sometimes, it is called drop-down list as well. Anyway, I am not going to discuss about its basic nature as everybody is well acquainted with it, rather would like to focus on its applicability. A Combobox control allows searching for items that need to be selected. Therefore, this control is being used several hundred times in an application and the snippets are being scripted accordingly. Even if the codes are written optimally, though some of the codes need to be written in the corresponding events of the different Comboboxes. Therefore, while developing a desktop database application using .NET platform, the Combobox control, ShahCombo21 was customized with some custom properties containing dynamic features. It is named as ShahCombo21. The Combobox control was customized and tested in the .NET platform of ‘Visual Studio 2019’ using databases MySQL under XAMPP version 8.0.0 and Microsoft (MS) SQL Server Management Studio v18.8. All these tools are free version. This article will describe briefly about the customized features for database handling and integration. It is suggested to visit the Youtube video presentations (Advanced Combobox Part 1 and Advanced Combobox Part 2) to have a look at how the Combobox works in a real project of .NET platform.

Features

Capabilities of the Combobox have been enhanced/fortified by incorporating FOURTEEN (14) advanced custom properties as can be seen in the figure below under the category of Shah Advanced/Dynamic Properties of the design-time property layout.

The key trait of this advanced Combobox is that very minimal coding/snippets is required to enjoy its complete functionalities. Actually, NO code is required to implement the data selection and display functions. Only a few lines, depending on the number of parameters, of codes are required to activate its data manipulation capabilities such as Save, Update & Delete of data, connecting to the database server which is unavoidable as data input is required from the front-end users’. This control is called dynamic as value of the many interactive customized properties’ get changed based on the value selected by the other property during design-time and run-time. All the properties are prefixed with Shah to identify the customized one separately and conveniently.

At first, it is worthy to be mentioned that the DLL file can be used for the development of database applications using VB.NET/C#.NET to handle data of MySQL/MS SQL Server databases. There are TWO (2) custom properties related to decoration of the control as can be seen, in the figure above, at the bottom of the property pallet. The property ShahComboBorderColor and ShahComboDropButtonColor are used to select the ‘Border Color’ and ‘Drop Button Color’ of the control, respectively, as it is evident in the figure above as well. The other TWELVE (12) properties were customized to handle data of the above-mentioned databases and the details are being extended in the following paragraphs.

The data related TWELVE (12) custom properties are arranged in the control property pallet according to the order of their functionalities and the functions are briefly described in the following steps.

• The first one in this kind is a list type property named ShahDatabseSystemName where the name of the database system, either MySQL or SQLServer, can be selected.
• It can be understood from the name ShahConnectionString that it deals/establishes the connectivity between database and the control during even design-time.
• Therefore, based on the input of the first and second property of this group, the name of the databases will be listed automatically under property of ShahDatabaseName during design-time of the application development, too, as it was claimed that many of the properties are interactive/dynamic during design-time also. So, list of the ShahDatabaseName will be changed automatically while the first and second properties are changed.
• ShahDataSourceOption property is equipped with the option of database objects to be selected from the list of Procedure/Table/View/SQL. Based on the selected option of this property, objects will be filtered from database server and listed under the property of ShahDataSourceName. It is also interactive and dynamic during design-time.
• If Procedure/Table/View option of ShahDataSourceOption is selected, a list will be generated under ShahDataSourceName. But if SQL is selected, instead, a SQL statement is required to write as a value of the ShahDataSourceName to get it activated during design-time and run-time.
• The list of parameters will be found automatically as a collection under the property of ShahProcParameters if ShahDataSourceOption is ‘Procedure’ and the selected procedure of the ShahDataSourceName contain some parameters as it can be seen in the following figure:

  

• Each of the parameters contains THREE(3) collection properties named ProcParamName, ProcParamType and ProcParamValue. Value of the first and second properties will be generated automatically from the connected database, and value for the third one can be assigned during design-time or run-time, based upon the necessity of the developing application.
• Column number of a table belonging to the linked database will be assigned as value for the ShahColDisplay property to display the data during run-time of the developed application and the column number of the corresponding primary value from database table will be assigned as value for ShahColKey property. And this primary key value will be returned as value to the ShahReturnValue property during run-time for further use.
• The object controls such as ‘Textbox’, ‘label’, ‘Combobox’, etc., can be kept connected with the table column number during design-time, if it is required to show/display the corresponding data of the selected Combobox listed data to the other controls during run-time. Therefore, the ShahWantToDisplayDataOnControl is a boolean type of property offered to decide whether the ShahListOfControlsToDisplay property is to be activated/functioning. It is presented in the figure below:

  

• The last interactive/dynamic collection type property is ShahListOfControlsToDisplay and each of the members of this collection having THREE (3) properties named ControlName, ControlType and ColumnNumber.
  • ControlName is a dynamic type as a list of control names according to the control name order will be generated automatically based on the available controls on the current container during design-time, as can be seen in the following figure:

  

• Following the selection of the ControlName, ControlType value will be assigned automatically and the ColumnNumber value can be assigned during design-time or run-time based on the requirements.
• ColumnNumber is the table column number of the connected database.
• The last database related property is ShahReLoad which will be found to be useful during different events of data manipulation such as INSERT/UPDATE/DELETE, of this customized Combobox control, ShahCombo21, at run-time of the developed database application.

At last, it would be reminded that reading and audio/video presentation together make anything more clear, meaningful and comprehensive, always. Therefore, at the end, it is repeated once again that all these functionalities can be experienced through visual presentation in a real project following the Youtube links (Advanced Combobox Part 1 and Advanced Combobox Part 2) for further and better understanding.

Technical Aspects of the Code Snippets

In the previous “Features” section, it was discussed about the customized properties of the Combobox control “ShahCombo21”. Here, it is going to be discussed about the technical aspects of the programming codes following the source codes attached herewith. The control was customized using Visual Basic.NET programming language. The codes are not included here in this article again to keep its size minimized since they are included as attachment already and it is suggested to download the source codes while reading this section, based on your convenience. If all the 15 files are attached in a DLL project of VB.NET, then it is possible to produce a compiled DLL file of “ShahCombo21” customized Combobox control to include in a VB.NET/C#.NET development project. The code snippets and the included files are being discussed briefly so that anyone can update/extend the control further, comfortably, considering the attached files as base.

Among the attached 15 files, “ShahCombo21.vb” is the main/base file and other relevant information are Assembly name: ShahCombo21, Root namespace: ShahCombo21, Target framework: .NET Framework 4.7.2 and Application type: Class Library. All the base properties are declared in this file, “ShahCombo21.vb”. Other files are named based on their understandable functions. Now based on the custom properties of the control “ShahCombo21”, the code snippets are discussed briefly.

The code snippets for the TWO decorative properties, “ShahComboBorderColor” and “ShahComboDropButtonColor” can be found below the property window, are included at the beginning of the main/base class and finishes before the constructor. WndProc method was implemented to materialize these properties.

Enum is an expression of enumeration data type and the data inside an Enum are fixed. It is a very important coding tool specially to create a fixed list type property of an object. Therefore, two Enum namely ‘dataQryOption’ and ‘enumDatabaseSystemName’ were used here. The properties “ShahDatabseSystemName” and “ShahDataSourceOption” were declared as ‘enumDatabaseSystemName’ and ‘dataQryOption’ respectively, and organize the properties accordingly.

There are FOUR (4) interactive/dynamic properties composed of this custom control and TWO (2) are dynamic list type. “ShahDatabaseName” is list type property but Enum cannot be used in this case as this property is dynamic. Therefore, ‘MfuncDbObjectList.vb’ module needs to be used together with the ‘DropDownConverter’ class and ‘DropDownConverterDataAttribute’ class. The ‘MfuncDbObjectList.vb’ module generates the dynamic array from database and ‘DropDownConverter’ class together with the ‘DropDownConverterDataAttribute’ class converts the array to list, and sets to the property. This is the typical way of creating dynamic list type property. It is also worthy to be noticed at line 155 & 228 inside the main ‘Public Class ShahCombo21’ how the ‘DropDownConverter’ was implemented. “ShahDataSourceName” is also the dynamic list type property and also generated from database in the same fashion using ‘MfuncDbList’ module together with the ‘DropDownConverter’ class and ‘DropDownConverterDataAttribute’ class.

The other TWO (2) interactive/dynamic properties are “ShahProcParameters” and “ShahListOfControlsToDisplay”. These are collection type properties. Designing a collection type property, one collection base class and one collection member class is required. Therefore, in the source code files, it can be found that ‘clsShahProcParamCollection’ class was used as collection base and ‘clsShahProcParam’ class was used as a member class for implementation of “ShahProcParameters” property. On the other hand, ‘clsShahListOfControlsToDisplayCollection’ class and ‘clsShahListOfControlsToDisplay’ class were used for “ShahListOfControlsToDisplay”, accordingly. In both of the cases, dynamic array was employed. Dynamic array for “ShahProcParameters” property was generated from database using SQL but a function, ‘funcGetControlListToDisplay’, was brought into work to generate the array for the property “ShahListOfControlsToDisplay”.

At last, it is concluded with the hope that the brief and sequential illustration of the code snippets would be helpful for the readers to understand the mechanism of designing and implementation of the custom properties. However, it is requested to the interested readers to contact us for further clarification.

Using the Code

It might be necessary to write few lines of codes for the main two cases such as:

1. Return corresponding values for the selected Combobox data, and
2. Save/Update/Delete operations

Both of the cases are going to be described including sample codes as follows.

In the first case, it is possible to retrieve any number of corresponding column data of the selected Combobox data using the following code assuming ‘cmbCity’ is the name of the custom control.

Dim xx As ShahCombo21.ccClsDbData = CType(cmbCity.SelectedItem, ShahCombo21.ccClsDbData)

        If xx IsNot Nothing Then

            MsgBox(xx.cKey.ToString())

            MsgBox("11th Column Data: " & Chr(9) & 
                    xx.cDataIntoArray(11).ToString & vbCrLf & "13th Column Data: " & 
                    Chr(9) & xx.cDataIntoArray(13).ToString)

        End If

ShahCombo21.ccClsDbData xx = (ShahCombo21.ccClsDbData) cmbCity.SelectedItem;

            MessageBox.Show(xx.cKey);

            MessageBox.Show("Column 11 Data: " +"\t"+xx.cDataIntoArray[11].ToString() + 
                   "\r\n" + "Column 13 Data: " + "\t" + xx.cDataIntoArray[13].ToString());

In the second case, the snippets below need to be used if the control is employed to send data from user interface to the connected database during run-time for data manipulation such as Save/Update/Delete.

If MsgBox("Do you want to Save/Update/Delete???", vbYesNo, "Shah") = vbYes Then
            'ADD VALUE TO THE COLLECTION PARAMETERS ACCORDING TO THE FOLLOWING WAY-
            cmbCity.ShahProcParameters.Item(0).ProcParamValue = Val(cmbCity.ShahReturnValue)
            cmbCity.ShahProcParameters.Item(1).ProcParamValue = Trim(cityShort.Text)
            cmbCity.ShahProcParameters.Item(2).ProcParamValue = Trim(cityName.Text)
            cmbCity.ShahProcParameters.Item(3).ProcParamValue = 1
            cmbCity.ShahReLoad = True
            msgbox ("Saved")
            Else
            MsgBox("Thanks.")
End If

if (MessageBox.Show("Do you want to Save/Update/Delete???", 
                    "ShahCombo21",MessageBoxButtons.YesNo)==DialogResult.Yes)
            {
                cmbCity.ShahProcParameters.get_Item(0).ProcParamValue = 
                                                       cmbCity.ShahReturnValue;
                cmbCity.ShahProcParameters.get_Item(1).ProcParamValue = Trim(cityShort.Text);
                cmbCity.ShahProcParameters.get_Item(2).ProcParamValue = Trim(cityName.Text);
                cmbCity.ShahProcParameters.get_Item(3).ProcParamValue = "1";
                cmbCity.ShahReLoad = true;
                MessageBox.Show ("saved");
            }
            else
            {
                MessageBox.Show("No Thanks");
            }

Using the Control

The customized control needs to be compiled as a DLL file and can be found as it is seen in the following figure below after the DLL was selected through ‘.NET Framework Components’ tab of the ‘Choose Toolbox Items’ custom control selection box. If anybody is interested, please feel free to contact us by leaving a comment below. It will be distributed by us free of cost to users. The DLL file can also be created using the source code files attached here with this article.

Conclusion

The customized Combobox control, ShahCombo21, was designed to incorporate few dynamic features to its existing inherited property to enhance its capability for wide range of database applications. Therefore, this customized control can be used for VB.NET/C# platform and MySQL/SQL Server databases without any changes using minimal code. Its implementation in a database application can reduce development time and management to some extent. It can be concluded that this type of control customization can drastically minimize the application development duration.

History

• 8^th May, 2021: Initial version