Transcript Arrays and Collections

Neal Stublen
[email protected]
Populating a Database
SQLExpress should be installed with
Visual Studio
 The book provides a .sql file for
populating the MMABooks database in
SQLExpress
 Double-click the .bat file on the S: drive
 We’ll need to repeat this process at the
start of each class session

Confirm Database Access

Using Visual Studio to locate the new
database as a Data Source
 View > Server Explorer
 Add Connection...
 Server name: .\SQLEXPRESS
 Database name: MMABooks
 Test Connection
Data Provider Errors
SqlException
 OracleException
 OdbcException
 OleDbException

Number
 Message
 Source
 Errors

Catch Provider Exception
private void Form1_Load(object sender,
EventArgs e)
{
try
{
this.customersTableAdapter.Fill(...);
}
catch (SqlException ex)
{
// report ex.Number, ex.Message
}
}
ADO.NET Errors
DBConcurrencyException
 DataException
 ConstraintException
 NoNullAllowedException


Message
Catch ADO.NET Exception
try
{
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
}
DataGridView Control Errors

Not an exception, but an event on the
control

DataError
Exception
 RowIndex
 ColumnIndex

Catch DataGridView Errors
private void gridView_DataError(...)
{
// report error in e.RowIndex and/or
// e.ColumnIndex
}
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

Bound TextBox Controls
Formatting and Advanced Binding
 Select TextBox
 Open Properties Window
 Expand DataBindings property
 Select Advanced option, click “…”

Select new format type
 Specify representation of null value

Bound ComboBox Controls
Populate a ComboBox with values from
a column of a database table
 SelectedItem is used to specify the
value in a column of another database
table

Code Practice

Select customer state using dropdown list
ComboBox instead of TextBox

Create StatesDataSet in Data Source window
Add DataSet control for StatesDataSet and set
DataSetName property
Add BindingSource control for DataSet and set
DataSource/DataMember properties
Set State field to use ComboBox
Set ComboBox to use data bound controls
Clear ComboBox data bindings for Text
property





Parameterized Queries
We can customize a DataSet by
providing parameters to modify the
query
 Parameters can be introduced using the
Query Builder

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 ‘%’
ToolStrip is added to provide the @Name parameter
Examine Fill button’s Click event
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 strip
 CancelEdit() on the customersBindingSource
Navigation Tool Strip
customersBindingSource.AddNew();
 customersBindingSource.EndEdit();
 customersBindingSource.CancelEdit();
 customersBindingSource.RemoveCurrent();


A binding source keeps all bound
controls in sync
DataViewGrid Control
Smart tag allows you to modify
commonly used properties
 Columns can be added, moved, or
removed

 Remove ID columns
 Columns 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 invoice entries






Create Customers-Invoices DataSet
Customers uses Detail View
Drag Customers onto Form
Drag Customers.Invoices onto Form
Examine DataSource/DataMember on grid
view and invoicesBindingSource
Why create our own?
Place data objects into a shared library
 We’re not using a form
 Separates database code from UI code

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 Commands
SqlCommand cmd = new SqlCommand();
cmd.CommandText =
"SELECT * FROM Customers";
cmd.CommandType = CommandType.Text;
cmd.Connection = cxn;
SqlReader 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 Parameters
SqlParameter stateParam = new SqlParameter();
stateParam.ParameterName = "@State";
stateParam.Value = some_local_variable;
cmd.Parameters.Add(stateParam);
cmd.Parameters.AddWithValue("@State", value);
SQL Injection

Don’t let this happen…
string cmd = "SELECT * FROM Customers
WHERE State=" + value;
Executing Commands
SqlDataReader r = cmd.ExecuteReader();
List<Customer> customers =
new List<Customer>();
while (r.Read())
{
Customer c = new Customer();
...
customers.Add(c);
}
r.Close();
cxn.Close();
Other Commands
object result = cmd.ExecuteScalar();
// Cast result to expected type
cmd.ExecuteNonQuery();
Examine Chapter 20 Code
MMABooksDB
 CustomerDB

 GetCustomer – ExecuteReader, exceptions
 AddCustomer – current ID
 UpdateCustomer – concurrency,
ExecuteNonQuery
StateDB
 frmAddModifyCustomer

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 Connections
using (SqlConnection cxn = ...)
{
cxn.Open();
using (SqlCommand cmd = ...)
{
cmd.Execute...
}
}
Using Equivalence
using (object obj = …)
{
}
object obj = …;
try
{
}
finally
{
obj.Dispose();
}