Viewing .NET Data

1. Viewing .NET Data Asad Usman Khan Syed Amier Haider Rehan Ahmad

2. Outline… • The DataGrid Control • Displaying Tabular Data • Data Sources • Displaying Data from a Array • Data Table • Displaying Data from a DataView • Displaying Data from a DataSet • IListSource and IList Interfaces • DataGrid Class Hierarchy • DataGridTableStyle and DataGridColumnStyle

3. …Outline • Data Binding • Simple Binding • Data Binding Objects • Visual Studio and Data Access • Creating a Connection • Selecting Data • Generating a Dataset • Updating the Data Source • Building a Schema • Adding an Element • Other Common Requirments • Manufactured Tables and Rows • Using an Attribute • Dispatching Methods • Getting the Selected Row

4. The DataGrid Control

5. The DataGrid Control • One of the best features of the new DataGrid control is its flexibility • The data source can be an Array, DataTable, DataView, DataSetclass, or a component that implements either the IListSource orIList interface. • The DataGrid control gives you a variety of views of the same data • Data can be displayed (as in a DataSet class) by calling the SetDataBinding() method.

6. Displaying Tabular Data • The data can also be displayed through

7. To Create main window and instance variables using System; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; public class DisplayTabularData : System.Windows.Forms.Form { private System.Windows.Forms.ButtonretrieveButton; private System.Windows.Forms.DataGriddataGrid; public DisplayTabularData() { this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(464, 253); this.Text = “01_DisplayTabularData”;

8. Creation of data grid Control this.dataGrid = new System.Windows.Forms.DataGrid(); dataGrid.BeginInit(); dataGrid.Location = new System.Drawing.Point(8, 8); dataGrid.Size = new System.Drawing.Size(448, 208); dataGrid.TabIndex = 0; dataGrid.Anchor = AnchorStyles.Bottom | AnchorStyles.Top |AnchorStyles.Left | AnchorStyles.Right; this.Controls.Add(this.dataGrid); dataGrid.EndInit();

9. Creating a Button this.retrieveButton = new System.Windows.Forms.Button(); retrieveButton.Location = new System.Drawing.Point(384, 224); retrieveButton.Size = new System.Drawing.Size(75, 23); retrieveButton.TabIndex = 1; retrieveButton.Anchor = AnchorStyles.Bottom | AnchorStyles.Right; retrieveButton.Text = “Retrieve”; retrieveButton.Click += new System.EventHandler (this.retrieveButton_Click); this.Controls.Add(this.retrieveButton); }

10. Associating Event protected void retrieveButton_Click(object sender, System.EventArgs e) { retrieveButton.Enabled = false; string source = “server=(local)\\NetSDK;” + “uid=QSUser;pwd=QSPassword;” + “database=Northwind”; string select = “SELECT * FROM Customers” ; SqlConnectionconn = new SqlConnection(source); SqlDataAdapterda = new SqlDataAdapter( select , conn); DataSetds = new DataSet(); da.Fill(ds , “Customers”); dataGrid.SetDataBinding(ds , “Customers”); } static void Main() { Application.Run(new DisplayTabularData()); } }

11. Data Sources… • The DataGrid control provides a flexible way to display data; in addition to calling SetDataBinding() • with a DataSet and the name of the table to display, this method can be called with any of the following data sources:

12. …Data Sources • An array (the grid can bind to any one dimensional array) • DataTable • DataView • DataSet or DataViewManager • Components that implement the IListSource Interface • Components that implement the IList interface

13. Displaying data from an array string[] stuff = new string[] {“One”, “Two”, “Three”}; dataGrid.SetDataBinding(stuff, null); protected class Item { public Item(string text) { _text = text; } public string Text { get{return _text;} } private string _text; }

14. DataTable • There are two ways to display a DataTable within a DataGrid control: • If your DataTable is standalone, call SetDataBinding(DataTable, null) • If your DataTable is contained within a DataSet, call SetDataBinding(DataSet,“<Table Name>”)

15. Displaying data from a DataView • A DataView provides a means to filter and sort data within a DataTable. • A DataView does not permit filtering of columns only rows. DataView dv = new DataView(dataTable);

16. Setting AllowEdit = false disables all column edit functionality for rows • Setting AllowNew = false disables the new row functionality • Setting AllowDelete = false disables the delete row capability • Setting the RowStateFilter displays only rows of a given state • Setting the RowFilter enables you to filter rows • Sorting the rows by certain columns

17. Filtering rows by data

18. Filtering rows on state

19. Sorting rows dataView.Sort = “ProductName”; dataView.Sort = “ProductName ASC, ProductID DESC”;

20. Displaying data from a DataSet class SqlDataAdapter da = new SqlDataAdapter(orders, conn); DataSet ds = new DataSet(); da.Fill(ds, “Orders”); da = new SqlDataAdapter(customers , conn); da.Fill(ds, “Customers”); ds.Relations.Add(“CustomerOrders”, ds.Tables[“Customers”].Columns[“CustomerID”], ds.Tables[“Orders”].Columns[“CustomerID”]); Once created, the data in the DataSet is bound to the DataGrid simply by calling SetDataBinding(): dataGrid1.SetDataBinding(ds, “Customers”);

21. Displaying data in a data view manager • The display of data in a DataViewManager is the same as that for the DataSet. • However, when a DataViewManager is created for a DataSet, an individual DataView is created for each DataTable, which then permits the code to alter the displayed rows, based on a filter or the row state.

22. Displaying data in a data view manager DataViewManager dvm = new DataViewManager(ds); dvm.DataViewSettings[“Customers”].RowFilter = “Country=’UK’”; dataGrid.SetDataBinding(dvm, “Customers”);

23. IListSource and IList interfaces • The DataGrid also supports any object that exposes one of the interfaces IListSource or IList. • IListSource only has one method, GetList(), which returns an IList interface. IList on the other hand is somewhat more interesting, and is implemented by a large number of classes in the runtime. • Some of the classes that implement this interface are Array, ArrayList, and StringCollection. • When using IList, the same caveat for the object within the collection holds true as for the Array implementation shown earlier—if a StringCollection is used as the data source for the DataGrid, the length of the strings is displayed within the grid, not the text of the item as expected.

24. DataGrid Class Hierarchy

25. DataGridTableStyle • A DataGridTableStyle contains the visual representation of a DataTable. • The DataGrid contains a collection of these styles, accessible by the TableStyles property. • The DataGridTableStyle permits the definition of the visual parameters for the DataGrid, such as the background and foreground color, the font used in the column header, and various other properties.

26. DataGridColumnStyle • The DataGridColumnStyle can be used to refine the display options on a column-by-column basis, such as setting the alignment for the data in the column, the text that is displayed for a null value, and the width of the column on screen.

27. Data Binding • The process of linking a control to a data source is called data binding. • The facilities available within .NET for binding data to controls is substantially easier to use and also more capable. For example, in .NET you can bind data to most properties of a control, not just the text property.

28. Simple Binding • A control that supports single binding typically displays only a single value at once, such as a text box or radio button. The following example shows how to bind a column from a DataTable to a TextBox: DataSet ds = CreateDataSet(); textBox1.DataBindings.Add(“Text”, ds , “Products.ProductName”);

29. Data-Binding Objects…

30. …Data-Binding Objects

31. Visual Studio.NET and Data Access

32. Visual Studio.NET and Data Access • Creating a Connection • Selecting Data • Generating a Dataset • Updating the Data Source • Building a Schema • Adding an Element • Other Common Requirments • Manufactured Tables and Rows • Using an Attribute • Dispatching Methods • Getting the Selected Row

33. Creating a Connection… • Step 1 • Step 2

34. Step 3 this.sqlConnection = new System.Data.SqlClient.SqlConnection(); // // sqlConnection // this.sqlConnection.ConnectionString = “data source=(local)\\NETSDK;” + “initial catalog=Northwind;” + “user id=QSUser;password=QSPassword;” + “persist security info=True;” + “workstation id=BILBO;” + “packet size=4096”;

35. Selecting Data • Simply Drag and drop the table on the form. • The code is generated automatically but is very redundant.

36. Generating a DataSet… • To generate the DataSet, select the data adapter and display its properties • Towards the bottom of the property sheet there are three options: Configure Data Adapter, Generate Dataset, and Preview Data.

37. …Generating a DataSet… • Select Generate DataSet. You will be prompted to provide a name for the new DataSet object before you can choose the tables that you want to add to the data set. If you have dragged multiple tables from Server Explorer and dropped them onto the form, you can link them from inside the dialog box to a single DataSet. • What is actually created is an XSD schema, defining the DataSet and each table that was included in the DataSet.

38. …Generating a DataSet… • In addition to the XSD file there is a (hidden) .cs file that defines various type-safe classes. To view this file, click the Show All Files toolbar button and then expand the XSD file.

39. …Generating a DataSet • Visual Studio .NET creates a .cs file with the same name as the XSD file. The classes defined are as follows: • A class derived from DataSet • A class derived from DataTable for the data adapter chosen • A class derived from DataRow, defining the columns accessible within the DataTable • A class derived from EventArgs, used when a row changes

40. Updating the Data Source… private void retrieveButton_Click(object sender, System.EventArgs e) { // Fill the data adapter from the database supplierDataAdapter.Fill ( supplierDataSet , “Supplier” ) ; // And display the data in the data grid... dataGrid1.SetDataBinding ( supplierDataSet , “Supplier” ) ; // And disable the retrieve button... retrieveButton.Enabled = false ; }

41. …Updating the Data Source private void updateButton_Click(object sender, System.EventArgs e) { // Update the database int modified = supplierDataAdapter.Update ( supplierDataSet , “Supplier” ) ; if ( modified > 0 ) MessageBox.Show ( string.Format ( “Modified {0} rows” , modified ) ) ; }

42. Building a Schema… • Add New Item from the Proejct menu • Select the XML Schema item from the Data category • Name your schema

43. …Building a Schema

44. …Building a Schema

45. Adding an element • To add a new top-level element, right-click inside your workspace and • Choose Add➪New Element from the context menu.

46. Other Common Requirements • A common requirement when displaying data is to provide a pop-up menu for a given row. • When the user right-clicks on any part of a row in the DataGrid, the row is looked up to check if it derives from ContextDataRow, and if so, PopupMenu()can be called. This could be implemented using an interface; however, in this instance a base class provides a simpler solution.

47. Other Common Requirements… ContextMenu menu = new ContextMenu(); foreach (MethodInfo meth in members) { // ... Add the menu item } System.Drawing.Point pt = new System.Drawing.Point(x,y); menu.Show(parent, pt);

48. Manufactured tables and rows… public class CustomerTable : DataTable { public CustomerTable() : base(“Customers”) { this.Columns.Add(“CustomerID”, typeof(string)); this.Columns.Add(“CompanyName”, typeof(string)); this.Columns.Add(“ContactName”, typeof(string)); } protected override System.TypeGetRowType() { return typeof(CustomerRow); } protected override DataRowNewRowFromBuilder(DataRowBuilder builder) { return(DataRow) new CustomerRow(builder); } }

49. …Manufactured tables and rows • The first prerequisite of a DataTable is to override the GetRowType() method. This is used by the .NET internals when generating new rows for the table. The type used to represent each row should be returned from this method. • The next prerequisite is to implement NewRowFromBuilder(), which is called by the runtime when creating new rows for the table. That’s enough for a minimal implementation. This implementation includes adding columns to the DataTable.

50. Using an attribute • The idea behind writing the ContextMenu attribute is to be able to supply a free text name for a given menu option. • There are a number of other members that could be added to this attribute, including: • A hotkey for the menu option • An image to be displayed • Some text to be displayed in the toolbar as the mouse pointer rolls over the menu option • A help context ID