Created On:  12 September 2012

Problem:

I am using embedded EXEC SQL statements in my Windows Forms application and I wish to bind the results of a cursor open directly to a dataGridView control without the need to fetch each row individually and add it to the controls items collection.

Is this possible?

Resolution:

Yes, it is possible to use data binding to bind the results of a cursor open directly to a dataGridView control using the controls DataSource property and the EXEC ADO GET CURSOR embedded statement.

The EXEC ADO GET CURSOR statement will return either a DataReader object or a DataSet object depending on the setting of the SQL(BEHAVIOR) directive.

If SQL(BEHAVIOR=UNOPTIMIZED) then a DataSet will be returned otherwise a DataReader will be returned.
The two are handled a little bit differently.

The examples below both use an ADO provider connection which points to a SQL Server database with the Northwind sample database installed.

Here is an example of returning a DataSet object and binding it to the dataGridView:

The class form1.

method-id

Form1_Load final private.
procedure division using by value sender as object e as type System.EventArgs.
   declare mysqldata as type testgridbind7.SQLData = new testgridbind7.SQLData
   set dataGridView1::DataSource to mysqldata::getdataset
   set dataGridView1::DataMember to "Customers"
end method.

The class SQLData.

$set sql(dbman=ado behavior=unoptimized)
class-id testgridbind7.SQLData.
working-storage section.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE Customers END-EXEC.
01 myobject object.
method-id getdataset.
local-storage section.
procedure division returning mydataset as type DataSet.
   EXEC SQL
      CONNECT TO 'SQLADO32'
   END-EXEC
   EXEC SQL
      DECLARE mycursor CURSOR FOR SELECT
         A.ContactName
        ,A.CompanyName
        ,A.Phone
      FROM Customers A
   END-EXEC
   EXEC SQL
      OPEN mycursor
   END-EXEC 
   exec ado get cursor mycursor into :myobject
      rename datatable as "Customers"
   end-exec
   set mydataset to myobject as type DataSet
   goback.
end method.
end class.

Here is an example of returning a DataReader object and binding it to the dataGridView:

The class form1 method Form1_Load:

method-id Form1_Load final private.
procedure division using by value sender as object e as type System.EventArgs.
   declare mySQLObject as type testgridbind.SQLData = new testgridbind.SQLData
   set dataGridView1::DataSource to mySQLObject::gettable
end method.

The class SQLData:

$set sql(dbman=ado behavior=mainframe)
$set ilusing"System.Data"
class-id testgridbind.SQLData.
working-storage section.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE Customers END-EXEC.

method-id
gettable.
local-storage section.
01 myobject object.
procedure division returning myTable as type DataTable.
   EXEC SQL
      CONNECT TO 'SQLADO32'
   END-EXEC 
   EXEC SQL
      DECLARE mycursor CURSOR FOR SELECT
         A.ContactName
        ,A.CompanyName
        ,A.Phone
      FROM Customers A
   END-EXEC
   EXEC SQL
      OPEN mycursor
   END-EXEC
   exec ado get cursor mycursor into :myobject end-exec
   set myTable to new type DataTable
   invoke myTable::Load(myobject as type SqlDataReader)
   goback.
end method.
end class.