Application Delivery Management
Application Modernization & Connectivity
CyberRes
IT Operations Management
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.