Add, Edit, and Delete in DataGridView

The article, or rather a code snippet, demonstrates a simple application that inserts, updates, and deletes by using DataGridView. The application uses asynchronous architecture for most calls to database. This shows that, without hanging the UI, you can allow a user to continue with his tasks.

Add, Edit and Delete in DataGridView
Figure 1

The application has the following outline:

  1. Gets all SQL Server instances from the network.
  2. Gets all databases from the selected instance. If the user provides an empty user name or password or wrong user name or password, the same list of SQL Server instances will be returned. The code is available.
  3. Gets all tables from the selected database.
  4. Gets all records from the selected table.
  5. Add, edit, delete records from the DataGridView.
  6. Pages with the number of records per page is also provided.

Asynchronous Architecture

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

Application Structure

When you start reading the code from the beginning, there is an enum named CallFor. This enum is used to set a private variable called when making calls to the SQL server list, databases, and tables. This is done because only one call back method handles all call backs from async calls. A switch case statement manages the behavior of different call backs.

I have designed this application with SqlCommandBuilder because I have some questions about using SqlCommandBuilder. Builder will automatically generate insert, update, and delete command, provided that you select a primary key in your select query.

I have faced a very common problem of cross thread exception. In my previous project, I implemented the same architecture and .NET 2.0 provides the .InvokeRequired and Invoke() functions to overcome this problem. You have to declare a delegate with a similar signature of call back method and call the same method by using the control’s Invoke() method. This is push all call stack to the parent thread from the executing thread and puts the parent thread into working status.

You need to follow the sequence, like this:

  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 navigation.
  6. Use buttons such as Add/Update, Commit, and 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. 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 following two functions load the data and bind it to a DataGridView. I tried to bind a temporary datatable object to DataGridView and then update the main table, but I was not able to do so. I used the adapter’s Fill() method, which takes the starting record and number of records as input parameters with dataset. I created one temporary dataset to get the total records. I dispose of it immediately. Instead of directly binding the datasource to DataGridView, I added columns manually and then let the rows get bound to them. This let me allow sorting and, in case you do not want to show any columns, you 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 DataGridView and calls for all paging functions. Depending upon the current index and all, those 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 one of the sample methods that gets SQL Server instances asynchronously. After BeginInvoke(), the user is free to perform any task. The call back function will catch the response. This is how you can invoke a method asynchronously by 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 call back method that will handle all call backs from different methods. When you use an async architecture, the call back is always on a new thread other than the parent one. This new thread will not be able to access the controls on the parent thread. Therefore, you need to shift the call stack to the parent thread; this can be done by using the 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, you easily can manipulate the DataGridView. You have to use the datasource that is directly bound to DataGridView. Async calling will be very efficient when loading huge amounts of data. Please let me know if you have any queries.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read