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.



About the Author

Jayant Kulkarni

Dear Friends, I'm from Pune and curently working with Symantec Corp. I'm having more than 7 years of exp in software field and have worked on areas like ASP.NET, C#, .NET remoting, web services, pocket pc applciations. I'm a brainbench certified software engineer in .NET framework, C#, ADO.NET and ASP.NET. If you like any of my articles or you want to suggest some changes and improve the way I code, write articles feel free to mail me at: jayantdotnet@gmail.com

Related Articles

Downloads

Comments

  • XML Format

    Posted by Vu on 08/28/2014 10:35am

    Is this code in VB? if so, can xml files be loaded into the data grid? if so, is there a code to parse it first before being loaded into data grid, thanks.

    Reply
  • Gpepn bEh NUrO

    Posted by FmCBibrWjm on 07/07/2013 11:14pm

    http://www.la-silhouettenyc.com/data/buyhydrocodoneonline/#23136 hydrocodone side effects rash - order hydrocodone online cod

    Reply
  • wheloltabotly PumeSonee Phobereurce 9222165

    Posted by TizefaTaNaday on 06/13/2013 07:03am

    fusGooffVob airjordan7retrofrenchblue.holidaygiving.org GypeSipsimide airjordan6retrocharitynavyred.holidaygiving.org exelePems

    Reply
  • QFQHWm yX QU BuL rvBj Kw

    Posted by vHjFbBJgSx on 03/31/2013 11:46am

    tramadol online tramadol 100mg high - tramadol snort get high

    Reply
  • Help convert this example to vb.net

    Posted by Edgar on 09/25/2012 08:34am

    Congratulations this code is very good, you can help me to convert to vb.net and sendme by email. thanks.

    Reply
  • Download link broken

    Posted by Henry on 07/18/2012 05:39am

    Please post dowload link that works...thank you!

    Reply
  • mr

    Posted by basil on 05/12/2012 01:23pm

    enjoy with programming

    Reply
  • Download link

    Posted by Carlos on 05/02/2012 03:31am

    Hello, Can you please set up a new download link? The current link is broken. Thanks, Carlos

    Reply
  • Nice article

    Posted by www.dotnetcodes.com on 05/12/2011 02:11am

    Nice article

    Reply
  • Images

    Posted by nbrahima on 04/05/2010 04:27pm

    I need the source code for bind the image in Gridview

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • Packaged application development teams frequently operate with limited testing environments due to time and labor constraints. By virtualizing the entire application stack, packaged application development teams can deliver business results faster, at higher quality, and with lower risk.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds