Add, Edit, and Delete in DataGridView with Paging

• Download source files - 20.8 KB
• Download demo project - 18.2 KB

Introduction

The article, or rather code snippet, demonstrates a simple application to insert, update, and delete using a DataGridView. The application uses an asynchronous architecture for most of the calls to the database. This is to show that without a hanging UI, we can allow the user to continue with his tasks. The application has the following outline:

1. Get all SQL Server instances from the network.
2. Get all databases from the selected instance.

If the user provides an empty user name or password, or a wrong user name or password, the same list of SQL Server instances will be returned. The code is available here[^].

3. Get all tables from the selected database.
4. Get all records from selected table.
5. Add, edit, delete records from the DataGridView.
6. A paging feature with number of records per page is also provided.

Asynchronous architecture

The application uses an async calling mechanism to make database calls. As the SQLEnumerator does not support async calling, I have added delegates to call those methods asynchronously. In .NET 2.0, the SqlCommand object supports async calling to a database. I am using this feature to get all the tables from the selected database. Delegates are the best practices to design async architectures because most of the things are internally handled by the CLR, and we do not have to bother much about them.

Application structure

When we start reading the code from the beginning, we can see that there is an enum named CallFor. This is used to set a private variable called when making calls to a SQL Server list, databases, and tables. This is done because only one call back method handles all the callbacks from asyn calls. A switch case statement manages the behavior of different callbacks. I have designed this application using a SqlCommandBuilder as I have some queries regarding how to use SqlCommandBuilder. The builder will automatically generate the insert, update, and delete commands, provided that you select the primary key in your Select query. I have faced a very common problem of cross thread exceptions. But, in my previous project, we implemented the same architecture, and .NET 2.0 provides InvokeRequired and the Invoke() function to overcome this problem. We have to declare a delegate with a similar signature as the callback method and call the same method using the control's Invoke() method. This will push all the call stack to the parent thread from the executing thread, and put parent thread to work. You need to follow a sequence like:

1. Get all SQL Server instances.
2. Get all databases from the selected instance.
3. Get all tables from the selected database.
4. Load data from the selected table.
5. Use paging to navigate.
6. Add buttons like Add/Update, Commit, Delete to insert/update or delete. You can delete/update/insert multiple records.

Here are some code blocks I'll explain. This function sets the database objects required throughout the application. The sqlQuery is dynamically built.

private void SetDataObjects()
{
    connection = new SqlConnection(connectionString);
    command = new SqlCommand(sqlQuery, connection);
    adapter = new SqlDataAdapter(command);
    builder = new SqlCommandBuilder(adapter);
    ds = new DataSet("MainDataSet");
    tempDataSet = new DataSet("TempDataSet");
}

The two functions below load the data and bind it to a DataGridView. I was trying to bind a temporary DataTable object to the DataGridView and then update the main table. But, I was not able to do so. I used the adapter's Fill() method which takes a start record and the number of records as input parameters with the DataSet. I created a temporary DataSet to get the total records. I dispose it immediately. Instead of directly binding the data source to a DataGridView, I prefer to add columns manually and then let the rows to bind to them. This allows sorting, and in case we do not want to show any columns, we can do it here.

private void btnLoad_Click(object sender, EventArgs e)
{
    lblLoadedTable.Text = "Loading data from table " + cmbTables.Text.Trim();
    btnLoad.Enabled = false;
    this.Cursor = Cursors.WaitCursor;
    try
    {
        if (userTable != null)
        {
            userTable.Clear();
        }
        userDataGridView.DataSource = null;
        userDataGridView.Rows.Clear();
        userDataGridView.Refresh();
        sqlQuery = "SELECT * FROM [" + cmbTables.Text.Trim() + "]";
        SetDataObjects();
        connection.Open();
        ticker.Start();
        adapter.Fill(tempDataSet);
        totalRecords = tempDataSet.Tables[0].Rows.Count;
        tempDataSet.Clear();
        tempDataSet.Dispose();
        adapter.Fill(ds, 0, 5, cmbTables.Text.Trim());
        userTable = ds.Tables[cmbTables.Text.Trim()];
                        
        foreach (DataColumn dc in userTable.Columns)
        {
            DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn();
            column.DataPropertyName = dc.ColumnName;
            column.HeaderText = dc.ColumnName;
            column.Name = dc.ColumnName;
            column.SortMode = DataGridViewColumnSortMode.Automatic;
            column.ValueType = dc.DataType;
            userDataGridView.Columns.Add(column);
        }
        lblLoadedTable.Text = "Data loaded from table: " + userTable.TableName;
        lblTotRecords.Text = "Total records: " + totalRecords;
        CreateTempTable(0, int.Parse(cmbNoOfRecords.Text.Trim()));

        btnPrevious.Enabled = true;
        btnFirst.Enabled = true;
        btnPrevious.Enabled = true;
        btnNext.Enabled = true;
        btnLast.Enabled = true;
        btnAdd.Enabled = true;
        btnUpdate.Enabled = true;
        btnDelete.Enabled = true;
        cmbNoOfRecords.Enabled = true;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        connection.Close();
        btnLoad.Enabled = true;
        this.Cursor = Cursors.Default;
        prgProgress.Value = 0;
        prgProgress.Update();
        prgProgress.Refresh();
        ticker.Stop();
    }
}

This method actually binds the data to the DataGridView and is called for all paging functions. Depending upon the current index and all, the required number of records are fetched.

private void CreateTempTable(int startRecord, int noOfRecords)
{   
    if (startRecord == 0 || startRecord < 0)
    {
        btnPrevious.Enabled = false;
        startRecord = 0;
    }
    int endRecord = startRecord + noOfRecords;
    if (endRecord >= totalRecords)
    {
        btnNext.Enabled = false;
        isLastPage = true;
        endRecord = totalRecords;
    }
    currentPageStartRecord = startRecord;
    currentPageEndRecord = endRecord;
    lblPageNums.Text = "Records from " + startRecord + " to " 
        + endRecord+ " of " + totalRecords;
    currentIndex = endRecord;

    try
    {
        userTable.Rows.Clear();
        if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }
        adapter.Fill(ds, startRecord, noOfRecords, cmbTables.Text.Trim());
        userTable = ds.Tables[cmbTables.Text.Trim()];
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        connection.Close();
    }

    userDataGridView.DataSource = userTable.DefaultView;
    userDataGridView.AllowUserToResizeColumns = true;
}

Here is a sample method which gets SQL Server instances asynchronously. After BeginInvoke(), the user is free to perform any task. The callback function will catch the response. This is the way you can invoke a method asynchronously using a delegate.

private void btnLoadSqlServers_Click(object sender, EventArgs e)
{
    ticker.Start();
    btnLoadSqlServers.Enabled = false;
    this.Cursor = Cursors.WaitCursor;
    cmbSqlServers.Items.Clear();
    called = CallFor.SqlServerList;
    intlDelg.BeginInvoke(new AsyncCallback(CallBackMethod), intlDelg);
}

Here is the callback method that will handle all the callbacks from the different methods. When you use an async architecture, the callback is always on a new thread other than the parent one. This new thread will not able to access the controls on the parent thread. Hence, we need to shift the call stack to the parent thread, and this can be done using control.InvokeRequired and control.Invoke(). The following method shows how to do that.

private void CallBackMethod(IAsyncResult result)
{  
    if (this.InvokeRequired)
    {
        this.Invoke(new AsyncDelegate(CallBackMethod), result);
    }
    else
    {
        try
        {
            prgProgress.Value = prgProgress.Maximum;
            switch (called)
            {
                case CallFor.SqlServerList:
                    string[] sqlServers = intlDelg.EndInvoke(result);
                    cmbSqlServers.Items.AddRange(sqlServers);
                    if (cmbSqlServers.Items.Count > 0)
                    {
                        cmbSqlServers.Sorted = true;
                        cmbSqlServers.SelectedIndex = 0;
                    }
                    this.Cursor = Cursors.Default;
                    btnLoadSqlServers.Enabled = true;
                    txtUserName.Select();
                    txtUserName.Focus();
                    break;
                case CallFor.SqlDataBases:
                    string[] sqlDatabases = intlDelg.EndInvoke(result);
                    cmbAllDataBases.Items.AddRange(sqlDatabases);
                    if (cmbAllDataBases.Items.Count > 0)
                    {
                        cmbAllDataBases.Sorted = true;
                        cmbAllDataBases.SelectedIndex = 0;
                    }
                    this.Cursor = Cursors.Default;
                    btnGetAllDataBases.Enabled = true;
                    break;
                case CallFor.SqlTables:
                    reader = command.EndExecuteReader(result);
                    cmbTables.Items.Clear();
                    while (reader.Read())
                    {
                        cmbTables.Items.Add(reader[0].ToString());
                    }
                    if (cmbTables.Items.Count > 0)
                    {
                        cmbTables.Sorted = true;
                        cmbTables.SelectedIndex = 0;
                        grpDataManipulate.Enabled = true;
                    }
                    else
                    {
                        grpDataManipulate.Enabled = false;
                    }
                    break;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            if (called == CallFor.SqlTables)
            {
                btnGetAllTables.Enabled = true;
                this.Cursor = Cursors.Default;
            }
            prgProgress.Value = 0;
            prgProgress.Refresh();
            ticker.Stop();
        }
    }
}

Conclusion

This way, we can easily manipulate a DataGridView. The only this is we have to use the data source that is directly bound to the DataGridView. Async calling will be very efficient while loading huge data. Please let me know if you have any queries.