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 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 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