1 / 54

C#: Introduction for Developers

Neal Stublen nstublen@jccc.edu. C#: Introduction for Developers. Tonight’s Agenda. Database Errors Parameterized queries ToolStrip control Master-detail relationships Custom data objects IDisposable ADO.NET connections Q&A. Dataset Review. Review.

Télécharger la présentation

C#: Introduction for Developers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related