DataGridView with integrated filtering, sorting, and updating changes to SQL

• Download source - 3.15 MB

Introduction

This little contribution is a DataGridView which auto-saves inserts and updates to an SQL database, extended with filtering and sorting on multiple columns, and as a bonus, it contains the code for a multicolumn combo box.

Background

As I work a lot with the DataGridView, I found it time consuming to write the code over and over for handling update and insert events. Furthermore, I would like to have the DataGridView extended with filtering and sorting on multiple columns. Therefore, I needed a DataGridView, based on SQL-data, which can be easily put on a form, and handles the data updates without the need for extra coding. I wrote down the tasks I frequently repeated, and started extending the DataGridView to my needs.

So, for starters, it would be nice if all the header text were caps, by default. Then, it would be nice if all double values are right outlined by default, with the correct format. Same for dates and integers. Also, nice, and added to the functionality, all columns have a minimum column width so that the header text is always visible.

I was also annoyed by the fact that the DataGridView, by default, adds text on the column names, so I got rid of that by renaming all DataGridView columns by default to the data property name. This can easily be done, although one must consider, when creating queries, the column names contain no underscores or non-standard characters.

Then, when column names were equal the data property names, I wrote some code which gets information on the data types for the columns, so the update and insert SQL statements could be generated and added to the functionality of the extended DataGridView. However, one must consider that the base table column names may not be altered to custom names in the query, otherwise the generated statements won't work. I myself always make sure that the columns for the table which needs to be updated have the exact column names in the query I put together for displaying the data. If someone rather uses different column names in the query, the class can be extended by adding a dictionary containing column mapping, for which the info can be passed through the form, and with a little adjustment of the generation of the SQL statements, this should work.

Then, I included filtering and sort capabilities in the header columns, which both support multiple columns.

I recently put together a DataGridView multiple column combo box, which I added to the project because I also wrote some code to handle possible data errors.

Using the code

We need a single line of code for loading the data, and a sub for handling the data updates and insert events, as shown below :

Private Sub Form1_Load(ByVal sender As Object, _
            ByVal e As System.EventArgs) Handles Me.Load
    RemoveHandler Me.NorthwindDataSet.Orders_Qry.RowChanged, _
                  New DataRowChangeEventHandler(AddressOf Rijgewijzigd)
    Me.Orders_QryTableAdapter.Fill(Me.NorthwindDataSet.Orders_Qry)
    Dim dv As New DataView(Me.NorthwindDataSet.Orders_Qry, "", _
                           "", DataViewRowState.OriginalRows)
    Me.filteredSource.DataSource = dv
    Me.SqlDatagridview1.Bind_to_Bindingsource(Me.filteredSource)
    AddHandler Me.NorthwindDataSet.Orders_Qry.RowChanged, _
               New DataRowChangeEventHandler(AddressOf Rijgewijzigd)
End Sub
Private Sub Row_Changed(ByVal sender As Object, _
            ByVal e As System.Data.DataRowChangeEventArgs)
    SqlDatagridview1.SaveDatatoDatabase("Server=.\SQLExpress;AttachDbFilename=" & _
                     Application.StartupPath & "\northwnd.mdf;Database=Northwind;" & _ 
                     "Trusted_Connection=Yes;", e, "orders")
End Sub

By using this minimal code, you have a DataGridView control supporting multiple column filtering and sorting, which automatically updates changes and new records to the SQL database. If you want to use this DataGridView with another data source, such as OLEDB for example, a little re-coding should do the trick.

I added the Northwind database as an example; however, the auto-generation of SQL inserts and updates works with any SQL-database; the only thing to do is to make sure the columns of the table which needs to be updated have the same column names in the query as they do in the actual SQL table.

Hope this can be of use for someone.