Introduction to Database Population and Handling in Visual Studio with SQLExpress

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

2. Database Check

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

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

5. Handling Data Errors

6. Data Provider Errors • SqlException • OracleException • OdbcException • OleDbException • Number • Message • Source • Errors

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

8. ADO.NET Errors • DBConcurrencyException • DataException • ConstraintException • NoNullAllowedException • Message

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

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

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

12. More With Data Sources and Data Sets

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

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

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

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

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

18. Parameterized Queries • We can customize a DataSet by providing parameters to modify the query • Parameters can be introduced using the Query Builder

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

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

21. Navigation Tool Strip • customersBindingSource.AddNew(); • customersBindingSource.EndEdit(); • customersBindingSource.CancelEdit(); • customersBindingSource.RemoveCurrent(); • A binding source keeps all bound controls in sync

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

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

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

25. Creating Data Access Objects

26. Why create our own? • Place data objects into a shared library • We’re not using a form • Separates database code from UI code

27. 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;

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

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

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

31. SQL Injection • Don’t let this happen… string cmd = "SELECT * FROM Customers WHERE State=" + value;

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

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

34. Examine Chapter 20 Code • MMABooksDB • CustomerDB • GetCustomer – ExecuteReader, exceptions • AddCustomer – current ID • UpdateCustomer – concurrency, ExecuteNonQuery • StateDB • frmAddModifyCustomer

35. Bonus Content

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

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

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

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