C#: INTRODUCTION

FOR DEVELOPERS

Neal Stublen

nstublen@jccc.edu

Tonight’s Agenda

Database Errors Parameterized queries ToolStrip control Master-detail relationships Custom data objects IDisposable ADO.NET connections Q&A

DATASET REVIEW

Review

How would you display a single customer record on a form?

CHAPTER 18, PART 3

HANDLING DATA ERRORS

Database Errors

Errors can be reported by the data provider, ADO.NET, or a bound control

Possible causesInvalid dataNetwork failureServer shutdown

Data Provider Errors

Data providers will throw an exception when an error occursSqlExceptionOracleExceptionOdbcExceptionOleDbException

Error Properties

Number Message Source Errors

Contains multiple errors that may have occurred when executing a single command

Number and Message refer to the first error in the collection

Catch Provider Exceptionprivate void Form1_Load(object sender, EventArgs e){ try { this.customersTableAdapter.Fill(...); } catch (SqlException ex) { // report ex.Number, ex.Message }}

ADO.NET Errors

Errors may occur when updating a DatasetDBConcurrencyExceptionDataException (general exception)ConstraintExceptionNoNullAllowedException

Message property describes the exception

Catch ADO.NET Exceptiontry{ this.customerBindingSource.EndEdit(); this.customersTableAdapterManager.UpdateAll(...);}catch (DBConcurrencyException ex){ // from UpdateAll() exception // report concurrency error this.customerTableAdapter.Fill(...);}catch (DataException ex){ // from EndEdit() exception // report ex.Message customerBindingsSource.CancelEdit();}catch (SqlException ex){ // report ex.Number, ex.Message}

Concurrency Errors

Exception handling of concurrency errors may be tested by running multiple instances of an application and editing the same record from both applications

DataGridView Control Errors

Not an exception, but an event on the control

DataError eventExceptionRowIndexColumnIndex

Catch DataGridView Errorsprivate void gridView_DataError(...){ // report error in e.RowIndex and/or // e.ColumnIndex}

CHAPTER 19

MORE WITH DATA SOURCES AND DATA SETS

Dataset Designer

Command property on Fill, GetData Opens Query Builder Visually build SQL command Preview Data to see query results

Designer.cs Queries

SQL queries are updated in the schema’s Designer.cs file

DeleteCommand, InsertCommand, UpdateCommand

SCOPE_IDENTITY() = ID generated from INSERT command

@ = query parameter UPDATE only updates a record

matching original column values

Formatting Bound Text

Advanced formatting optionsNumeric valuesDates

Display for null values Format and Parse events of the Binding

object

Why a BindingSource?

Using a BindingSource will keep all bound controls synchronized

Changing the position within a data source will update all the bound controls

Changes made to any controls will update the data source when changing position

Using a BindingSource

AddNewAdd a new blank row to the data source

EndEditSave changes to a new or existing row

CancelEditAbort and changes to a new or existing row

RemoveCurrentDelete the current row

Using a BindingSource

Position/CountDetermine current position with the data

source MoveFirst MoveLast MoveNext MovePrevious

Change the current position within the data source

CHAPTER 19, PART 2

PARAMETERIZED QUERIES

Parameterized Queries

We can customize a DataSet by providing parameters to modify the query

Add Query from smart tag menu of a bound control

Parameters can be introduced by modifying the WHERE clause

Parameter values are prefixed with @

Code Practice Create a customer search form Populate a DataGridView based on the entry within a

TextBox

Create CustomersDataSet as a Data Source Open CustomersDataSet.xsd and modify Fill

CommandText using Query Builder Change Name Filter to “LIKE @Name” Drag Customers table onto a form Update Fill to append ‘%’ (wildcard) ToolStrip is added to provide the

@Name parameter Examine Fill button’s Click event

CHAPTER 19, PART 3

TOOLSTRIPS

What was that ToolStrip?

A tool strip can be docked around the main window

It contains other controls Controls can be added through the Items

collection Items have events just like other controls We can add a “Cancel” button to the

navigation tool stripCancelEdit() on the customersBindingSource

Navigation Tool Strip

A ToolStrip can be used to add and update rows in the data sourcecustomersBindingSource.AddNew();customersBindingSource.EndEdit();customersBindingSource.CancelEdit();customersBindingSource.RemoveCurrent();

CHAPTER 19, PART 4

MASTER-DETAIL RELATIONSHIPS

DataViewGrid Control

Smart tag allows you to modify commonly used properties

Columns can be added, moved, or removedRemove ID columnsColumns still exist in the DataSet

Column content can be formatted using DefaultCellStyle

Master-Detail Relationships One-to-many relationship between

tables One customer has many invoices

Code Practice View customer invoices based on the selection of a

customer record Populate DataGridView with customer entries Populate DataGridView with invoice entries

Create CustomerInvoice DataSet Customers uses Detail View Drag Customers onto Form Drag Customers.Invoices onto Form Examine DataSource/DataMember

on grid view and

invoicesBindingSource

CHAPTER 20

CREATING DATA ACCESS OBJECTS

Why create our own?

Place data objects into a shared library We’re not using a form Separates database code from UI code

Start with an empty console

application

Using Our Own Connections

SqlConnection cxn = new SqlConnection();cxn.ConnectionString = "...";cxn.Open();...cxn.Close();

Sample Connection String:

Data Source=localhost\SqlExpress;Initial Catalog=MMABooks;Integrated Security=False;User ID=Be Careful;Password=Be Very, Very Careful;

Using Our Own CommandsSqlCommand cmd = new SqlCommand();cmd.CommandText =

"SELECT * FROM Customers";cmd.CommandType = CommandType.Text;cmd.Connection = cxn;SqlDataReader r = cmd.ExecuteReader();

Parameters in Commands Add parameters to SQL statements

SELECT * FROM Customers WHERE STATE = 'VA'

SELECT * FROM Customers WHERE STATE = @State

@State is a SQL variable representing the state

Create the ParametersSqlParameter stateParam = new SqlParameter();stateParam.ParameterName = "@State";stateParam.Value = some_local_variable;

cmd.Parameters.Add(stateParam);

cmd.Parameters.AddWithValue("@State", value);

SQL Injection

Don’t do this…

string cmd = "SELECT * FROM Customers WHERE Name=" + value;

Especially if value is user-entered data, such as:“John; DROP TABLE Customers;”

Executing CommandsSqlDataReader r = cmd.ExecuteReader();

List<Customer> customers =new List<Customer>();

while (r.Read()){ Customer c = new Customer(); ... customers.Add(c);}

r.Close();cxn.Close();

Other Commandsobject result = cmd.ExecuteScalar();// Cast result to expected type

cmd.ExecuteNonQuery();

Chapter 20-1 Exercise

MMABooksDB CustomerDB

GetCustomer – ExecuteReader, exceptionsAddCustomer – current IDUpdateCustomer – concurrency,

ExecuteNonQuery StateDB

Notice exception handling frmAddModifyCustomer

Notice DataSource for states

BONUS CONTENT

Disposable Objects

IDisposable interface Single method: Dispose()

Releases unmanaged resources that may be held by an object

Such as a database connection!!

Using…

using keyword can be used to confine objects to a particular scope

using also ensures that Dispose() is called if the object implements IDisposable

using also calls Dispose if an exception is thrown

Disposable Connectionsusing (SqlConnection cxn = ...){ cxn.Open(); using (SqlCommand cmd = ...) { cmd.Execute... }}

Using Equivalenceusing (object obj = …){}

{ object obj = …; try { } finally { obj.Dispose(); }}

Open/Close Connections

ADO.NET uses “connection pooling” to optimize opening and closing connections to the database

cxn.Open() and cxn.Close() are using connections from the connection pool that share the same connection string

ADO.NET manages the actual connection to the database

http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

Think of it like this…class SqlConnectionPool{ public SqlConnection Open(string cxnStr) { if (mPool.Contains(cxnString)) { return mPool[cxnString]; } // Create a new connection ... }}

And…class SqlConnectionPool{ public void CheckIdle() { foreach (cxn in mPool) { if (cxn.IsIdle()) { cxn.ReallyClose(); mPool.Remove(cxn); } } }}

DataSets in Class Libraries Create a DataSet in a class library Specify DataSet Modifier property

public or internal? Add the library as a reference from another

project Select “Referenced DataSets” when adding

a DataSet control to a form Add a BindingSource Add form controls and bind

them to the BindingSource

Slow Start Times?

http://blogs.msdn.com/b/dotnet/archive/2012/10/18/an-easy-solution-for-improving-app-launch-performance.aspx

http://msdn.microsoft.com/en-us/magazine/cc163655.aspx

http://msdn.microsoft.com/en-us/library/cc656914(v=vs.110).aspx

CHAPTER 24

MENUS, TOOL BARS, AND STATUS BARS

Designer Walkthrough

Summary

MenuStrip w/ defaults ToolStrip w/ defaults StatusStrip View Menu Toggles PerformClick() ToolStripContainer w/ docking ContextMenuStrip SplitContainer ErrorProvider