Created On:  10 September 2012

Problem:

Visual COBOL for Visual Studio Windows Form application is using a dataGridView control to display a table in a SQL database.  How can I bind this table to the dataGridView control so that any changes made to the data in the dataGridView will be written back to the original database on disk?

Resolution:

You can bind a SQL table to a dataGridView control using a SqlDataAdapter object and this will allow you to invoke the Update method of the SqlDataAdapter in order to save the changed data back to the original database.

See the sample "bindadapter.zip" file attached to this article.

Code is shown below:
*--------------------------------------------------------------------------------*
*                           BINDADAPTER Example                                  *
*                                                                                *
* This example program demonstrates how a SqlDataAdapter can be used to bind a   *
* data table to a dataGridView control so that the data can be edited directly   *
* in the dataGridview and then the updated contents of the table will be written *
* to the datasource in the SQL Server database using the Update command.         *
*                                                                                *
* Change the value of the connect string to one that points to a valid SQL Server*
* installation on your computer.                                                 *
* This example allows for updates and insertion of new rows but not row deletions*
* as the Customers table is also dependant on Orders and Order Details tables.   *
* After making changes, click on Submit button to update data in database.       *
*--------------------------------------------------------------------------------*
$set ilusing"System.Data"
$set ilusing"System.Data.SqlClient"
class-id bindadapter.Form1 is partial
   inherits type System.Windows.Forms.Form.
working-storage section.
01 dataAdapter type SqlDataAdapter.
method-id NEW.
procedure division.
   invoke self::InitializeComponent
   goback.
end method.

method-id
reloadButton_Click final private.
procedure division using by value sender as object e as type System.EventArgs.
*> Reload the data from the database.
   invoke self::GetData(dataAdapter::SelectCommand::CommandText)
   goback
   end method.

method-id
submitButton_Click final private.
procedure division using by value sender as object e as type System.EventArgs.
*> Update the database with the user's changes.
   invoke dataAdapter::Update(bindingSource1::DataSource as type DataTable)
   goback
end method.

method-id
Form1_Load final private.
procedure division using by value sender as object e as type System.EventArgs.
*> Bind the DataGridView to the BindingSource
*> and load the data from the database.
   set dataGridView1::DataSource to bindingSource1
   invoke self::GetData("select * from Customers")
   goback
end method.

method-id
GetData public.
local-storage section.
*> Modify connectString below to point to your own Data Source for SQL Server.
01 connectString string
value "Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=MYCOMPUTER".
01 commandBuilder type SqlCommandBuilder.
01 mytable type DataTable.
procedure division using selectCommand as string.
   try
*> Create a new data adapter based on the specified query.
      set dataAdapter to new SqlDataAdapter(selectCommand, connectString)
*> Create a command builder to generate SQL update, insert, and
*> delete commands based on selectCommand. These are used to
*> update the database.
      set commandBuilder to new SqlCommandBuilder(dataAdapter)
*> Populate a new data table and bind it to the BindingSource.
      set mytable to new DataTable
      set mytable::Locale to type System.Globalization.CultureInfo::InvariantCulture
      invoke dataAdapter::Fill(mytable)
      set bindingSource1::DataSource to mytable
*> Resize the DataGridView columns to fit the newly loaded content.
      invoke dataGridView1::AutoResizeColumns(type DataGridViewAutoSizeColumnsMode::AllCellsExceptHeader)
   catch ex as type SqlException
      invoke type MessageBox::Show("To run this example, replace the value of the " &
         "connectionString variable with a connection string that is " &
         "valid for your system.")
   end-try
   goback.
end method.
end class.