Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE

An example of WPF Data Binding to ADO.NET DataSet and OpenESQL connected model EXEC ADO statements

An example of WPF Data Binding to ADO.NET DataSet and OpenESQL connected model EXEC ADO statements

This WPF application first loads data into a ADO.NET DataSet from a SQL Server database and binds the WPF DataGrid to the ADO.NET DataSet. Then you can do editing on the DataGrid. Finally you can click the Update button to update all the changes you've made on the DataGrid to the database. 

There are different ways to do this. This example illustrates the use of OpenESQL dataset cursor and the neccessary connected model EXEC ADO statements to load/update data from/to the datasource.

The complete project is attached and the code is below:

       class-id KBWPFBindingADO.Window1 is partial
                 inherits type System.Windows.Window.
      ******************************************************************
      *             WPFBINDINGADO Example                              *
      *This program demonstrates WPF Data Binding to ADO.NET DataSet   *
      *and the OpenESQL connected model EXEC ADO statements to         *
      *load/update ADO.NET DataSet from/to SQLServer database.         *
      *                                                                *
      *When the application is started, the DataGrid is populated with * 
      *data which is from the SQLServer database. You can do a         *
      *combination of insert, delete or update on the DataGrid. Then   *
      *press the update button and all the change will go back to the  *
      *database.                                                       *
      *                                                                *
      *To run this application, you need to define a 32 bit ADO.NET    *
      *connection pointing to the northwind database in SQLServer. You *
      *can check the topic "To catalog an ADO.NET data provider" in our* 
      *documentation on how to do it.                                  *
      ******************************************************************
       working-storage section.
           exec sql include sqlca end-exec.
       01 ws-ado-dsn pic x(10) value 'northwind'.
       01 ds type DataSet.  
       01 ws-sqlcode pic -9(9).
       method-id NEW.
       procedure division.
           invoke self::InitializeComponent()
           invoke self::createtable
           set ds to self::loadds
           set self::DataContext to ds           
           goback.
       end method.
       
      *Create the 'contractors' table in SQLServer northwind database
      *and insert it with data.
       method-id createtable.
       procedure division.
           exec sql whenever sqlerror perform errormessage end-exec
           exec sql connect to :ws-ado-dsn end-exec
           exec sql drop table contractors end-exec
           exec sql create table contractors
           ( ID     INT PRIMARY KEY,
             NAME   CHAR(10),
             RATE   DECIMAL(5,2))
           end-exec
           exec sql insert into contractors values
           (001, 'Evan', 11.11)
           end-exec
           exec sql insert into contractors values
           (002, 'John', 22.22)
           end-exec
           exec sql insert into contractors values
           (003, 'Liam', 33.33)
           end-exec
           exec sql insert into contractors values
           (004, 'Ethan', 44.44)           
           end-exec
           exec sql insert into contractors values
           (005, 'Lucas', 55.55)           
           end-exec
           exec sql insert into contractors values
           (006, 'Noah', 66.66)           
           end-exec
           exec sql insert into contractors values
           (007, 'Benjamin', 77.77)           
           end-exec
           exec sql insert into contractors values
           (008, 'Logan', 88.88)           
           end-exec
           exec sql insert into contractors values
           (009, 'James', 99.99)           
           end-exec
           exec sql insert into contractors values
           (010, 'Jacob', 100)           
           end-exec
           goback.
           
       errormessage.
           move sqlcode to ws-sqlcode
           invoke type MessageBox::Show(sqlerrmc , ws-sqlcode)
           stop run.
       end method.
       
       method-id loadds.
       local-storage section.
       01 tempdataset object.        
       procedure division returning myds as type DataSet.
           exec sql whenever sqlerror perform errormessage end-exec       
      *Declare OpenESQL dataset cursor.    
           exec sql declare dscsr dataset cursor for 
               select ID, NAME, RATE FROM contractors
           end-exec

      *Load cursor data into a ADO.NET DataSet.
           exec sql open dscsr end-exec
           exec ado get cursor dscsr into :tempdataset
               rename datatable as adodtcontractors
           end-exec
           exec sql close dscsr end-exec
           
           exec sql commit end-exec
           exec sql disconnect end-exec
           set myds to tempdataset as type DataSet::Copy
           goback.
           
       errormessage.
           move sqlcode to ws-sqlcode
           invoke type MessageBox::Show(sqlerrmc , ws-sqlcode)
           stop run.          
       end method.       

       method-id button1_click.
       local-storage section.
           exec ado declare adodscontractors untyped dataset end-exec.
       procedure division using by value sender as object e as type System.Windows.RoutedEventArgs.
           exec sql whenever sqlerror perform errormessage end-exec        
           exec sql connect to :ws-ado-dsn end-exec
      *Bind to the working-storage DataSet so EXEC ADO can use it.    
           exec ado using adodscontractors bind to :ds end-exec
           exec ado using adodscontractors prepare to update adodtcontractors end-exec
      *Specify the update, insert and delete SQL statements for the update to SQLServer database.
      *OpenESQL knows which rows are for update, insert and delete respectively.
           exec ado to update adodtcontractors 
               use update contractors set
                       ID = {ID},
                       NAME = {NAME},
                       RATE = {RATE}
                   where ID ={ID.Original}
           end-exec
           exec ado to insert into adodtcontractors 
               use insert into contractors values ({ID}, {NAME}, {RATE})
           end-exec
           exec ado to delete from adodtcontractors 
               use delete from contractors  
                       where ID = {ID}
           end-exec      
      *Update the SQLServer database.    
           exec ado update datasource end-exec
           if sqlcode = 0
               invoke type MessageBox::Show("Update is successful!" , "Success")
           end-if
           exec sql commit end-exec
           exec sql disconnect end-exec
           goback.
           
       errormessage.
           move sqlcode to ws-sqlcode
           invoke type MessageBox::Show(sqlerrmc , ws-sqlcode)
           stop run.   
       end method.
       
       end class.

5277.KBWPFBindingADO.zip

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2015-09-16 20:02
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.