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.

linking application to data

so … we have an application, written in c#. we have a rockin’ sql database. how do we get it together? how do we get sql and c# to talk to each other? that’s what this post will discuss. we need an API – an application programming interface – that will facilitate our program’s access to the data to carry out specified functions. a number of alternatives exist.

1. ODBC ~ open database connectivity ~ a procedural API (common set of functions/methods) for using SQL queries to access data. limitations: unsuitable for accessing non-relational data such as spreadsheets, text files and xml files. An implementation of ODBC will contain one or more applications, a core ODBC library, and one or more “database drivers”. The core library, independent of the applications and DBMS, acts as an “interpreter” between the applications and the database drivers, whereas the database drivers contain the DBMS-specific details. Thus a programmer can write applications that use standard types and features without concern for the specifics of each DBMS that the applications may encounter. Likewise, database driver implementors need only know how to attach to the core library. This makes ODBC modular.

2. OLE DB ~ object linking and embedding, database ~ a procedural API developed by MS as a successor to ODBC, to extend its feature set to support a wide variety of non-relational databases, such as object databases, and spreadsheets. OLE DB separates the data store from the application that needs access to it through a set of abstractions that include the datasource, session, command and rowsets. This was done because different applications need access to different types and sources of data and do not necessarily want to know how to access functionality with technology-specific methods. OLE DB is conceptually divided into consumers and providers. The consumers are the applications that need access to the data, and the provider is the software component that implements the interface and therefore provides the data to the consumer.

3. ADO.NET ~ activeX data object .NET ~ an API that’s a base class in the MS .NET framework. ADO.NET consists of a set of objects in the Systems.Data namespace that communicate with the database via .NET providers. these objects allow connectivity to the database to retrieve, edit, delete and insert data in the database and manipulate the data within the program. ADO.NET consists of two parts – (i). the DataSet and (ii). the .NET provider.

(i). DataSet ~ does not provide or maintain connectivity to the database. provides a copy of the data source – in memory – with which to work. consists of a nested collection of objects that allow for manipulation of data within the dataset. see figure below.

(ii).NET data provider objects ~ consists of objects that faciliate connectivity to individual data sources. each provider resides in its own namespace within the System.Data namespace. @ present, 4 main components. see figure below.

to access an SQL Server database requires an OLE DB connection provider.

sql server and tuning with indexes

clustered indexes ~ only one per table; for tables with PK/ID SQL Server defaults to creating a clustered index on that column; designer can change that, however, by de-selecting ‘clustered index’ checkbox. its possible for a column that’s NOT the ID/PK to have a clustered index … and then the ID/FK column becomes a unique index. the structure of the index = the physical order of data in the database.

non-clustered ~ can have up to 249 non-clustered indexes on each table; when a table already has a clustered index, when creating a new index, SQL Server defaults to creating a non-clustered index. functions similarly to the index of a book.

unique ~ columns indexed on a unique index cannot have duplicate values. not really an index, but a property. so, clustered and non-clustered indexes can be unique. by default, any column with a PK/ID key has a unique clustered index.

where to build indexes ~
on columns used in these queries, and frequently accessed by WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses; only add indexes if you know that they will be used by the queries run against the table.

MS-SQL Server and database replication

so … to get what this topic’s all about, think of your public library. you’ve visited the library, right? it has multiple locations, right? ok … i’m gonna assume affirmative … assume you’re not a complete neanderthal. anyyyyywayyyy … think library. and searching for your favourite book or DVD or whatever. how’d they do it? how’d they keep it all sync’d? i mean … all those books … spread across town in all those different book depositories we call libraries. how’d they keep track of what books comes … what books go … where a book’s located?

database … and … data REPLICATION. that’s how it happens. it can take place in virtual real time or in static time, and updating of replicated data can occur immediately or in a queued process. so … let’s get to the official-type stuff. WTF is replication? essentially, its the process of distributing a database’s data and database objects to other database engines that exist across an organization or enterprise. clearly, the benefits seem obvious: (1) flexibility with respect to data manipulation, sharing and collaboration; (2) concurrent use of data~which likely leads to increased efficiency, scope, productivity of said enterprise or organization; (3) facilitating ease of mobile users.

how does replication work? to understand this … we must understand the paradigm used to discuss replication theory: the publisher~ distributor~ subscriber paradigm. publisher refers to the source server … subscriber refers to the receiving server … distributor refers to the system database, which can reside on its own, on the subscriber side or on the publisher side. one-to-one OR one-to-many can describe the relationship between the publisher and subscriber.

check out the three replication models. (1) snapshot – just like it sounds ~ a snapshot of data @ a particular moment in time; no updates; (2) transactional – real-time; transactions stored on distributor and send to subscriber; can replicate entire table or filter; (3) merge – most complex; offers most flexibility and autonomy; connection not required for update … just to merge update data (for syncing dbase). associated with conflicts.

so … how to choose which model? how to tackle replication? well, start by considering the following: does the database have a compound primary key? do we know the when~what~where of rec’d publications? what level of transactional consistency do we require? what about latency? i.e. how long does updated data wait to get replicated/received by subscribers? latency decreases network speed and increases congestion … so less is more. and, then, what about site autonomy? i.e. do sites cascade from one another? or do they exist reasonably independently of one another? and what of security? system/server performance? how will data get updated? is there a schedule? what of data source(s) availability? and the subscriber’s capacity to update data? does the replication or update require a 2PC process? if so … preparation and commitment of servers must be anticipated (to avoid data conflicts, transaction inconsistencies).