The following notes were taken during a lecture in COMP 241 .NET Web Applications at Camosun College by Rob Thorndyke.  These notes are here mainly to help me study, but if you can find some guidance through them then all the better!

Fundamental Objects

• DbConnection
• DBCommand
• DBDataReader
• DbDataAdapter

Connection Types

• SQL Server
• ODBC
• OLEDB (Old SQL Server)
• Access
• XML

• SqlDbConnection
• OleDbConnection
• OracleDbConnection
• ODBCDbConnection

Example Session

DbConnection connection = new SqlConnection(connectionString);
DbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM table_name";
connection.Open();
DbDataReader reader = cmd.ExecuteReader();
// Returns a table of data
// Do something with that data
// -------------
connection.Close();
// *NOTE: After closing the connection, the data
//        in reader will no longer be available!

Command Examples

cmd.CommandText = "UPDATE table1 SET value = 30 WHERE value = 10";
connection.Open();
int count = cmd.ExecuteNonQuery();
connection.Close();
 
// -----------------------------
 
cmd.CommandText = "SELECT count(*) FROM user";
connection.Open();
int num_users = (int) cmd.ExecuteScalar(); // Must be cast
connection.Close();

Data Reader

• Forward-only, read-only, cursor
• Highly efficient; use when you only need read-only data, that’s processed only once, in order

// Iterating through the reader
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {
    int value = (int) reader["value"];
    string name = (string) reader["name"];
} 
 
// Putting the data into a DataTable
DbDataReader reader = cmd.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader, LoadOption.Upsert);
connection.Close();
GridView1.DataSource = table; //Data Bound Control
GridView1.DataBind();

Load Options

(http://msdn.microsoft.com/en-us/library/system.data.loadoption.aspx)

Data Adapters

• Allows automatic writeback
• Has 3 “update” properties
  • InsertCommand
  • DeleteCommand
  • UpdateCommand
  • These are all or nothing! You must implement all 3
  • Formatting these things is not trivial

DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM board";
 
// Create our data adapter
DbDataAdapter adapter = new DataAdapter(cmd);
adapter.Fill(ds, "table"); // ds is a DataSet!

• Formatting:
  • Use a GUI wizard – Popped up when the DataAdapter is dropped on the form!
  • Use a Builder:

SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

The following notes were taken during a lecture in COMP 230 Systems Analysis & Design at Camosun College by Marla Weston.  These notes are here mainly to help me study, but if you can find some guidance through them then all the better!

Conceptual Data Modeling Process

In the design stage, the conceptual data model is translated into a physical design

Deliverables and Outcome

• Primary deliverable is an entity-relationship diagram (ER Diagram)
• Second deliverable is a set of entries about data obnects to be stored in repository or project dictionary
  • repository links data, process, and lopgic models of an information system
  • data elements included in the DFD must appear in the ERD

Conceptual Data Model

• The 30,000 foot view.
• Very high level
• Just the system and any external entities that interact with it.

Gathering Information for Conceptual Data Modeling

• Top Down approach for the data model is derived from an intimate understanding of the system
• Bottom Up approach which builds simple low-level objects into more complex higher-level objects.

Entity Relationship Model

• Entity type name should always be a singular noun
• Entity type definition should:
  • Include a statement of what the unique characteristics is/are for each instance
  • Make clear what entity instances are included and not included in the entity type
• Attributes: a named property characteristic of an entity that is of interest to the organization
  • Naming an attribute: eg Vehicle_ID
  • Place its name inside the rectangle for the associated entity in the E-R Diagram
  • Name is a noun and should be unique
  • States what the attribute is and possibly why it is important
  • Should indicate if a value is required or optional
• Relationships
  • A relationship name is a verb phrase and avoid vague names
  • A relationship definition should:
    • Explain any optiontion participation
    • Explain the extent of history that is kept in the relationship
• Associative Entity
  • An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances
  • Used mostly in many-many relationships
• Subtypes: A type together with a constraint which possibly restricts the allowed range of values
• Supertypes: A generic entity type that has a relationship with one or more subtypes
• Business Rules: specifications that preserve the integrity of the logical data model
  • Entity Integrity
  • Referential
  • Trigger/Triggering Operation – An assertion or rule that governs the validity of data manipulations such as insert update and delete

The following notes were taken during a lecture in COMP 241 .NET Web Applications at Camosun College by Rob Thorndyke.  These notes are here mainly to help me study, but if you can find some guidance through them then all the better!

Web Page
|
GridView (DataBound Control, DataSource Property)
|– DataTable (pure data – no UI)
|– Adapter Object — Database

DataTable & DataColumn

Example Code:

DataTable dataTable = new DataTable();
 
DataColumn idColumn = new DataColumn( "id" );
idColumn.DataType = typeof( string );
 
// DataColumn has all the properties you would expect to find if you were creating a database field
 
idColumn.MaxLength = 10; // Default: -1
idColumn.Unique = true; // Default: false
idColumn.AllowDBNull = false; // Default: true;
 
// Add the column to the dataTable
dataTable.Columns.Add( idColumn );
// OR
dataTable.Columns.Add( "id", typeof( string ) );
DataColumn column = dataTable.Columns[ "id" ];
// ---------------------------
DataColumn salaray = new DataColumn( "salary" );
salary.DataType = typeof( Decimal );
salary.DefaultValue = 0.00m; // m = decimal literal
dataTable.Columns.Add( salary );
 
// Decimal - Fixed precision float - good for money
// Hex: int t = 0xc4;

DataRow

Example Code:

DataRow row1 = dataTable.newRow();
row1["id"] = "x123";
row1["salary"] = 11043.00m;
dataTable.Rows.Add(row1);
// Alternatively
dataTable.Rows.Add("Z417", 32147.00m);
// Alternatively adding a row with an object array
dataTable.Rows.Add(new object[] { "P112", 7417.00m } );

DataRow State

DataRow State

• Detached – not in a Table
• Added – just added; never committed
• Unchanged – no changes since last commit
• Modified – some changes since last commit
• Deleted – just deleted

DataRow Version

• Current
• Default
• Original
• Proposed

Code:

row1.AcceptChanges(); // --> "commit"
row1.RejectChanges(); // --> "roll back"
label1.Text = "" + row1.RowState;

DataSet

A collection of DataTables and DataRelations

DataSet ds = new DataSet("db");
 
DataTable table1 = ds.Tables.Add("table1");
table1.Columns.Add("id", typeof(Guid)); // Guid: Global Unique Identifier
table1.Columns.Add("name", typeof(string));
table1.PrimaryKey = new DataColumn[] { table1.Columns["id"] };
 
DataTable table2 = ds.Tables.Add("table2");
table2.Columns.Add("id", typeof(Guid));
table2.Columns.Add("style", typeof(string));
table2.Columns.Add("foreign_key", typeof(Guid));
table2.PrimaryKey = new DataColumn[] { table2.Columns["id"] };
 
ds.Relations.Add("table1_table2_relation", table1.Columns["id"], table2.Columns["foreign_key"]);

Web Page
–|–GridView
——-|–DataSet
————|–DataAdapter
—————–|–database

The following notes were taken during a lecture in COMP 230 Systems Analysis & Design at Camosun College by Marla Weston.  These notes are here mainly to help me study, but if you can find some guidance through them then all the better!

Data Flow Diagrams (DFD)

• Technology independent
• show data flows, structure, and functional requirements of new system
• Useful for depicting purely logical information flows
• DFDs that detail physical systems differ from system flowcharts which depict details of physical computing equipment

Process: work or actions performed on data (inside the system)
Data store: data at rest (inside the system)
Source/sink: external entitty that is orgingin or destination of dara (ouotside the system)
Data flow: arrow depicting movement of data.
Context Diagram: is an overview of an organizational system that shows:

• the system boundaries
• external entities that interact with the system
• major information flows between the entities

Level-0 Diagram

DFD Rules

There are DFD guidelines that apply:

- the inputs to a process are different form the outputs of that provess
- no process can only have outputs or only inputs. processes must have both inputs and outputs.
- process labels should be verb phrases
- all flows to or from a data store must move through a process
- no data move directly between external entities without going through a process
- bidirectional flwo between process and data store is represented by two separate arrow (no bi-directional arrow)
- forked data flow must refer to exact same data item (not different data items) from a common location
- joined data flow must refer to exact same data item (not different data items) from multiple souces to a common location
- data flow caonn go diretly from a process to itself (no recursive data flows)
- data flow from  a process to a data store means update( insert, delete or change)
Functional decomposition is an iterative process of breaking a system description down into finer and finer detail.

• creates a set of charts in which one process on a given chart is explained in great detail on another chart.
• continues until no sub process can logically be broken down any further

Primitive DFD – the lowest level of a DFD.
level 1 diagram results from decomposition of level 0 diagram
BPR: Business Process Re-engineering

The following notes were taken during a lecture in COMP 241 .NET Web Applications at Camosun College by Rob Thorndyke.  These notes are here mainly to help me study, but if you can find some guidance through them then all the better!

Client Side Form Validation

• No post back
  • Faster
  • More responsive
  • Eases server load
  • .NET Validation Framework
• If it fails on client:
  • PostBack is cancelled
  • Predefined error message is shown
  • If desired, focus is put into the input control that failed

Server Side Form Validation

• More secure
• Access to DB
• If succeeds on client:
  • ServerValidation event is fired
    • Executes AFTER Load() event; before any control event
• If validation fails on the server:
  • IsValid property set to false
    • Code: if (! IsValid) return;

• Then the client responds:
  • Displays predetermined error message
  • If desired, sets focus to the failed input control

Validation Controls in ASP.NET

• Attached to an input control
• RequiredFieldValidator
  • Ensures the input text is not blank (whitespace)
  • Optionally ensure a default has been changed!
• CompareValidator
  • Tests <, <=, ==, >=, >, !=
  • Uses ValueToCompare or ControlToCompare and Operator
  • Eg. Password confirmation
  • Test against a fixed value, or the value in another control
  • Not just for numeric values: Set type property to Convert test values to any supported type first
• RangeValidator
  • Value falls between “MinimumValue” and “MaximumValue” (inclusive)
  • Again Type == string is assumed! So make sure to set the type if needed.
• RegularExpressionValidator
  • Set “ValidationExpression”
  • Eg. ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$
• CustomValidator
  • Client side: set ClientFunctionName property
    • Must be written in javascript
    • Function myValidatorFunction(source, arguments)
      • Source: validator control
      • Arguments: object with Value field and IsValid field
  • Server side: ServerValidate event handler
    • Public void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args) {
      • Args.Value, args.IsValid
• ValidationSummary control
  • Automatically accumulates the error messages from all failed validates
  • Displays them together: list, bullets, paragraph

Setting up .NET Validation Controls

1. Drag Validator to where you want to see the error message
2. Configure the Validator to catch whatever is relevant
3. Set ControlToValidate
4. Set ErrorMessage
5. Set Display => None, Static, Dynamic
6. Set Text to show something always. Eg “*”

The following notes were taken during a lecture in COMP 241 .NET Web Applications at Camosun College by Rob Thorndyke.  These notes are here mainly to help me study, but if you can find some guidance through them then all the better!

Client side State Management

• Scalable
  • As # users grows, server space doesn’t
• Easy to add web servers
• Ways to do it in .NET:
  • Hidden Fields
  • Cookies
  • Query Strings
  • ViewState -> fancy hidden field (hashed values/objects)
  • **Control State – just checking state of control.

Server side State Management

• Secure
• Bandwidth is lower
  • (eg Mobile devices)
• Variables in .NET:
  • Application – global to all users (use Lock() and Unlock())
  • Session – global to each user (uses Cookies to store SessionID())
    • Configure in Web.Config to use “cookieless sessions” -> ID is sent via Query String