the project, part 2

ok. so i figured it out. my previous post set me on the right track. here’s what i arrived at, finally. just using the adapter that i created on the form and adding the database tables i need. and then just coding the on-click event of each button to perform the function – i.e. add/update a row. no need to create the connection to the database manually for each and every button. simple. not sure why the ’sample’ had such complicated coding.

as for loading the data … i simply attached the loading of the dataset to each tab page. only one page has data actually bound to the text/list box controls. the other forms are form entering data to add to the database. and there’s the report viewer. that’s it. simple.
then i figured out the audit thing. even handler, indeed. i changed the system to record  payment voids – the amount, date, ID. but i see from testing that information can be changed on my updates form. and so i need a second audits table to record changes made to information made on that form.

i question having an add lease form and a separate update lease form. once again, i followed the lead of the sample. but … now i wonder what the point of that separate form is, really. i suppose, if i’m still convinced its superfluous by monday a.m. i can just delete the controls and make it a welcome/instruction tab page.

project: design a program~c-sharp front-end; sql server back-end

ok. so i have a windows form with 4 tab-controlled pages. each tab page related to a different function of the program. tab 1 ~ add a lease ~ 2 buttons and several text boxes. tab 2 ~ crystal report viewer. tab 3 ~ update lease ~ 3 buttons and several text boxes. tab 4 ~ make payment ~ 1 button, 1 check box and several text boxes. at the back end, a SQL Server 2000 database. no class libraries for this project. just windows controls and event handlers and ADO.NET controls.

ok. so. i am looking at the source code of a ’sample’ … something given to me to inspire my own creation. its deficient. lacking in some of the basic, stated requirements. and coded weirdly. like … each windows control – i.e. button – click event has the ADO.NET objects coded within it. i wonder, why not just attach the connection to the tab-click of the tab page to which it pertains? why repeat the same code for so many buttons? i don’t get it.

and what of an event handler that considers the whole audit thing? the audit thing being that, an audit table must get updated in the event of any changes made to the lease table can i attach an audit-related event handler to ‘add lease’ (submit) and ‘update lease’ (update) buttons? is that what i do to implement the auditing system?

and what about the whole void payments thing? the ‘make payment’ tab page has a check box for users to indicate whether they are voiding the payment. does void now become a new field in my payments table? this void field would take on a boolean/binary type – 0 for false or not voided and 1 for true or voided, right? that seems to make sense.

ADO.NET ~ classes and objects

ADO.NET ~ the name for a set of classes used with C# and the .NET framework to access data in a relational, table-oriented format. this includes relational databases such as Acess, SQL as well as other databases and even non-relational data sources. located within the System.Data.dll assembly. developed for the purpose of providing a set of easy-to-use classes with which to access data … extensibility: supports more data sources that predecessors … support for multi-tiered internet applications.

classes and objects detailed in a previous post.

DataReader ~ five steps to retrieving data from a dataTable in SQL~ 1.connect to the data source … 2. open the connection … 3. issue an SQL query … 4. read and display the data with the data reader … 5. close the data reader and connection.

DataSet ~ 1. create connection … 2. create a dataAdapter object … 3. create a dataSet … 4. fill dataSet with data … 5. use a foreach statement to write data from the dataSet to the console … no need to open or close connection – data adapter manages that.

*data updated through the dataSet object and its objects, i.e. dataRow.

SqlDataAdpater.Update () ~ this method goes thru the rows in a DataTable to check for data marked for changes. each DataRow object in the Rows collection has a property, RowState, tracking whether the current row marked for deletion, addition, modification, or whether unchanged. any changes made get reflected in the database.

Find () ~ a method in the DataTable Rows collection that ~ goes through rows in a DataTable finds data in rows by using the PK.

Delete () ~ a method in the DataRow object that deletes the current row.

*use Remove () to remove rows from dataSet, while leaving underlying database unchanged.

**reproduce graphics p. 619 … p. 634 … **

DataRelation Object ~ used to describe the relation betweeen multiple DataTable objects. Each DataSet has a Relations collection of DataRelations that enables you to find and manipulate related tables. ~objects to represent relationships between fields, data~ creating objects that represent relationships enable navigation among the data of related tables.

Add () method of Relations accepts a string name for the relationship and two DataColumns: the parent column, followed by the child column.

GetChildRows () method resides in DataRow object ~ loops thru each DataRow in the Rows collection and returns just the related rows. (GetParentRows() works analogously)

to access data with multiple relations, initialize a connection to the data source, create a DataSet and then a data adapter for each table required …. then build a data relations object for each of the relationships between the tables … then use foreach loops to process data using child-parent hierarchy.

DataSet has several methods that process XML ~ WriteXML() … ReadXML()

DataAdapter also has properties to access SQL commands used in data update more directly than with command builder.

Stored Procedures require passage to the comman objects constructor of the stored procedure’s name and the connection used. items to remember:

1. set the CommandType of the command object to StoredProcedure

2. with parameters collection its possible to create and add input, output and return parameters for use with stored procedures

3. stored procedure also make it possible to retrieve information about the DataTable, DataRow, and DataColumns using the Get and Set methods

4. it’s possible to derive parameters from the parameters collection using the CommandBuilder’s DeriveParameters method.