C#: Introduction for Developers

1. Neal Stublen nstublen@jccc.edu C#: Introduction for Developers

2. Tonight’s Agenda • Database Errors • Parameterized queries • ToolStrip control • Master-detail relationships • Custom data objects • IDisposable • ADO.NET connections • Q&A

3. Dataset Review

4. Review • How would you display a single customer record on a form?

5. Chapter 18, Part 3Handling Data Errors

6. Database Errors • Errors can be reported by the data provider, ADO.NET, or a bound control • Possible causes • Invalid data • Network failure • Server shutdown

7. Data Provider Errors • Data providers will throw an exception when an error occurs • SqlException • OracleException • OdbcException • OleDbException

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

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

10. ADO.NET Errors • Errors may occur when updating a Dataset • DBConcurrencyException • DataException (general exception) • ConstraintException • NoNullAllowedException • Message property describes the exception

11. 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 }

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

13. DataGridView Control Errors • Not an exception, but an event on the control • DataErrorevent • Exception • RowIndex • ColumnIndex

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

15. Chapter 19More With Data Sources and Data Sets

16. Dataset Designer • Command property on Fill, GetData • Opens Query Builder • Visually build SQL command • Preview Data to see query results

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

18. Formatting Bound Text • Advanced formatting options • Numeric values • Dates • Display for null values • Format and Parse events of the Binding object

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

20. Using a BindingSource • AddNew • Add a new blank row to the data source • EndEdit • Save changes to a new or existing row • CancelEdit • Abort and changes to a new or existing row • RemoveCurrent • Delete the current row

21. Using a BindingSource • Position/Count • Determine current position with the data source • MoveFirst • MoveLast • MoveNext • MovePrevious • Change the current position within the data source

22. Chapter 19, Part 2Parameterized Queries

23. 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 @

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

25. Chapter 19, Part 3ToolStrips

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

27. Navigation Tool Strip • A ToolStrip can be used to add and update rows in the data source • customersBindingSource.AddNew(); • customersBindingSource.EndEdit(); • customersBindingSource.CancelEdit(); • customersBindingSource.RemoveCurrent();

28. Chapter 19, Part 4Master-Detail Relationships

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

30. Master-Detail Relationships • One-to-many relationship between tables • One customer has many invoices

31. Code Practice • View customer invoices based on the selection of a customer record • Populate DataGridView with customer entries • Populate DataGridView with invoice entries • Create CustomerInvoiceDataSet • Customers uses Detail View • Drag Customers onto Form • Drag Customers.Invoices onto Form • Examine DataSource/DataMember on grid view and invoicesBindingSource

32. Chapter 20Creating Data Access Objects

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

34. Using Our Own Connections SqlConnectioncxn = 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;

35. Using Our Own Commands SqlCommandcmd = new SqlCommand(); cmd.CommandText = "SELECT * FROM Customers"; cmd.CommandType = CommandType.Text; cmd.Connection = cxn; SqlDataReader r = cmd.ExecuteReader();

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

37. Create the Parameters SqlParameterstateParam = new SqlParameter(); stateParam.ParameterName = "@State"; stateParam.Value = some_local_variable; cmd.Parameters.Add(stateParam); cmd.Parameters.AddWithValue("@State", value);

38. 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;”

39. 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();

40. Other Commands object result = cmd.ExecuteScalar(); // Cast result to expected type cmd.ExecuteNonQuery();

41. Chapter 20-1 Exercise • MMABooksDB • CustomerDB • GetCustomer – ExecuteReader, exceptions • AddCustomer – current ID • UpdateCustomer – concurrency, ExecuteNonQuery • StateDB • Notice exception handling • frmAddModifyCustomer • Notice DataSource for states

42. Bonus Content

43. Disposable Objects • IDisposable interface • Single method: Dispose() • Releases unmanaged resources that may be held by an object • Such as a database connection!!

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

45. Disposable Connections using (SqlConnectioncxn = ...) { cxn.Open(); using (SqlCommandcmd = ...) { cmd.Execute... } }

46. Using Equivalence using (object obj = …) { } { object obj = …; try { } finally { obj.Dispose(); } }

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

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

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

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