Visual Studio and Data Access

 
Chapter 10 - Viewing .NET Data
bySimon Robinsonet al.
Wrox Press 2002
  

With the new version of Visual Studio come some new ways to bolt data access into your applications. This section will discuss some of the ways that Visual Studio .NET allows data to be integrated into the GUI, so that you can interact with data just as you would with regular controls.

The tools available allow you to create a database connection, using either the OleDbConnection or SqlConnection classes. The class you will use depends on which database you wish to connect to. Once you have defined a connection, you can then create a DataSet and populate it from within Visual Studio .NET. This generates an XSD file for the DataSet , just as we did manually in the previous chapter, and automatically generates the .cs code for you. This results in the creation of a type-safe DataSet .

In this section I'll show you how to create a connection, select some data, generate a DataSet , and use all of the generated objects to produce a simple application.

Creating a Connection

To begin this section, create a new Windows application. Once created, you'll most probably see a blank form. The first task is to create a new database connection. Bring up the Server Explorer either type Ctrl + Alt + S , or select Server Explorer item from the View menu. This will display a window similar to that shown overleaf:

click to expand

Within this window you can manage various aspects of data access. For this example, you need to create a connection to the Northwind database. Selecting the Add Connection option from the context menu available on the Data Connections item will bring up a wizard where you can select which OLE DB provider to use choose Microsoft OLE DB Provider for SQL Server as you will be connecting to the Northwind database installed as part of the Framework SDK samples. The second page of the Data Link dialog is shown here:

click to expand

Depending on how you have installed your Framework samples databases, you may have an instance of the Northwind database residing in SQL Server, one in a local MSDE (Microsoft Data Engine) database, or possibly both.

To connect to the MSDE database (if you have one), type (local)\NETSDK for the name of the server. To connect to a regular SQL Server instance, type (local) as shown above for the current machine, or the name of the desired server on the network.

Next you need to choose the login information you can either use integrated NT security, or specify a username and password. The choice you make again depends on how your database has been set up. For the local MSDE database, you can use a specific username and password, which are QSUser and QSPassword respectively.

Select the Northwind database from the drop-down list of databases, and to ensure you have everything set up correctly, click on the Test Connection button. This will attempt to connect to the database and should display a message box when complete. Of course, you'll have to set the server up appropriately for your machine's configuration, so the user name, password, and the server name may all be different.

To create a connection object, click and drag the newly added server onto the main application window. This will create a member variable of type System.Data.SqlClient.SqlConnection , or System.Data.OleDb.OleDbConnection if you chose a different provider, and add the following code into the InitializeComponent method of the main form:

   this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();     //     // sqlConnection1     //     this.sqlConnection1.ConnectionString = "data source=skinnerm\NETSDK;" +     "initial catalog=Northwind;" +     "user id=QSUser;password=QSPassword;" +     "persist security info=True;" +     "workstation id=SKINNERM;" +     "packet size=4096";   

As you can see, the connection string information is persisted directly in code.

When you have added this object to the project, you will notice that the sqlConnection1 object appears in the tray area at the bottom of the Visual Studio window:

click to expand

You can alter properties of this object by selecting it and showing the Properties dialog ( F4 ).

Selecting Data

When you have defined a data connection, you can then select a table (or view) from the available list, and drag that table into an active form from your project:

click to expand

For this example, I have chosen the Customers table. When you drag this object into your project (you can drop this on the form or the server controls palette), it will add an object to your form derived from SqlDataAdapter , or OleDbDataAdapter if you're not using SQL Server.

The data adapter generated contains commands for SELECT , INSERT , UPDATE , and DELETE . Needless to say, these may (and probably should), be tailored to call stored procedures rather than using straight SQL. The wizard-generated code will do for now, however. Visual Studio .NET adds the following code to your .cs file:

   private System.Data.SqlClient.SqlCommand sqlSelectCommand1;     private System.Data.SqlClient.SqlCommand sqlInsertCommand1;     private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;     private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;     private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;   

There is an object defined for each of the SQL commands, and a SqlDataAdapter . Further down the file, in the InitializeComponent() method, the wizard has generated code to create each one of these commands and the data adapter too. The code is fairly verbose, so I have only included excerpts here.

There are two aspects of the code generated by Visual Studio .NET that are worth looking at the UpdateCommand and InsertCommand properties. Here is an abridged version showing the pertinent information:

   //     // sqlInsertCommand1     //     this.sqlInsertCommand1.CommandText = @"INSERT INTO dbo.Customers     (CustomerID, CompanyName, ContactName,     ContactTitle, Address, City, Region,     PostalCode, Country, Phone, Fax)     VALUES(@CustomerID, @CompanyName, @ContactName, @ContactTitle,     @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);     SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address,     City, Region, PostalCode, Country, Phone, Fax     FROM dbo.Customers WHERE (CustomerID = @Select2_CustomerID)";     this.sqlInsertCommand1.Connection = this.sqlConnection1;     //     // sqlUpdateCommand1     //     this.sqlUpdateCommand1.CommandText = @"UPDATE dbo.Customers     SET CustomerID = @CustomerID, CompanyName = @CompanyName,     ContactName = @ContactName, ContactTitle = @ContactTitle,     Address = @Address, City = @City, Region = @Region,     PostalCode = @PostalCode, Country = @Country,     Phone = @Phone, Fax = @Fax     WHERE (CustomerID = @Original_CustomerID)     AND (Address = @Original_Address) AND (City = @Original_City)     AND (CompanyName = @Original_CompanyName)     AND (ContactName = @Original_ContactName)     AND (ContactTitle = @Original_ContactTitle)     AND (Country = @Original_Country)     AND (Fax = @Original_Fax)     AND (Phone = @Original_Phone)     AND (PostalCode = @Original_PostalCode)     AND (Region = @Original_Region);     SELECT CustomerID, CompanyName, ContactName, ContactTitle,     Address, City, Region, PostalCode, Country, Phone, Fax     FROM dbo.Customers     WHERE (CustomerID = @Select2_CustomerID)";     this.sqlUpdateCommand1.Connection = this.sqlConnection1;   

The main area of interest in these commands is the SQL that has been generated. For both the INSERT and UPDATE commands there are actually two SQL statements: one to do the INSERT or UPDATE , and the other to reselect the row from the database.

These seemingly redundant clauses are used as a way to re-synchronize the data on the client machine with that on the server. There may be defaults applied to columns when inserted, or database triggers that fire to update some of the columns in the inserted/updated record, so re-syncing the data has some benefit. The @Select2_CustomerID parameter used to reselect the data is the same value passed into the INSERT / UPDATE statement for the primary key; the name is just something auto-generated by the wizard.

For tables that include an IDENTITY column, the SQL generated utilizes the @@IDENTITY value after the INSERT statement. As described in the previous chapter, relying on @@IDENTITY to produce primary keys can lead to some interesting bugs , so that's one area of the SQL you may want to change. Similarly, if you have no calculated columns it seems a little wasteful to re-select all columns from the original table just in case something has been updated.

The wizard-generated code works, but is less than optimal. For a production system, you would probably want to replace some, if not all of these SQL clauses with calls to stored procedures. If your INSERT or UPDATE clauses didn't need to re-synchronize the data then the removal of the redundant SQL clause could speed up the application a little.

Generating a DataSet

Now you have defined the data adapter, you can use it to create a DataSet . To generate the DataSet , click on the data adapter and display the properties for the object ( F4 ). Towards the bottom of the property sheet you'll notice the following three options:

click to expand

Clicking on Generate DataSet will permit you to choose a name for the new DataSet object, and to choose which tables to add into that DataSet . If you have dragged several tables from the Server Explorer onto the form, you can link them together from within the dialog box, into a single DataSet .

What is actually created is an XSD schema, defining the DataSet and each table that you have included within the DataSet . This is similar to the hand-crafted example in the previous chapter, but in this instance the XSD file has been created for you:

click to expand

In addition to the XSD file there is a (hidden) .cs file that defines a number of type safe classes. To view this generated file, click on the Show All Files toolbar button as shown above, and then expand the XSD file. You'll notice 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 you chose

  • A class derived from DataRow , defining the columns accessible within the DataTable

  • A class derived from EventArgs , used when a row changes

You may have guessed what tool is used to generate this file and these classes it's XSD.EXE , discussed in the last chapter.

You can naturally choose to update the XSD file once the wizards have done their thing, but don't be tempted to edit the .cs file to tweak it in some way, as it will be regenerated when you recompile the project, and all those changes will be lost.

Updating the Data Source

Now that we have created an application that can select data from the database, I'll show you how trivial it is to persist the changes back to the database. If you have followed along with the last few steps, you should have an application that contains connection, data adapter and DataSet objects. All that is left to do is hook the DataSet up to a DataGrid , add on some logic to retrieve data from the database and display it, then simply persist any changes back to the database.

We'll set up a form as shown below, and then I'll go through the code behind the scenes, which is available in the \10_UpdatingData subdirectory.

click to expand

The form consists of a DataGrid control and two buttons . When the user clicks the Retrieve button, the following code is executed.

   private void retrieveButton_Click(object sender, System.EventArgs e)     {     sqlDataAdapter1.Fill (customerDataSet , "Customer") ;     dataGrid1.SetDataBinding (customerDataSet , "Customer") ;     }   

This code utilizes the data adapter created earlier (by dragging a database table from the Server Explorer) to fill a DataSet . We fill the Customer data table with all records from the database. The call to SetDataBinding() will then display these records on screen.

After navigating through the data and making some changes, you can then click on the Update button. The code behind this is shown next.

   private void updateButton_Click(object sender, System.EventArgs e)     {     sqlDataAdapter1.Update(customerDataSet , "Customer") ;     }   

Again this code is trivially simple, as the data adapter is doing most of the work. The Update() method loops through the data in the selected table of the DataSet , and for each change made will execute the appropriate SQL statement against the database. Note that this method returns an int , which is the number of rows modified by the update.

The use of the data adapter was discussed in detail in the previous chapter, but to recap, it represents SQL statements for SELECT , INSERT , UPDATE , and DELETE operations. When the Update() method is called, this executes the appropriate statement for each modified row. This will cause all modified rows to execute an UPDATE statement, all deleted rows to issue a DELETE statement, and so on. If you have hand crafted a set of SQL commands, you have the option of defining calls to stored procedures instead of simply issuing many different SQL statements, as in general, the use of stored procedures produces much better performing code.

If you want all the benefits of using stored procedures, but don't have the time or knowledge to write your own, there's an easy way to do it in Visual Studio .NET. Display the context menu for the data adapter and choose the Configure Data Adapter menu option. This will display a wizard where it is possible to choose the source of data for the adapter:

click to expand

After selecting Create new stored procedures , clicking Next will walk through the process of automatically generating new stored procedures for SELECT , INSERT , UPDATE, and DELETE statements, and ultimately modifies the code generated within the project to add calls to these stored procedures instead of the calls to straight SQL statements.

In addition to generating new stored procedures, you can also select existing stored procedures to populate the four SQL commands on the adapter. This would be useful when hand-crafted stored procedures are already available, or when some other function is performed by a procedure such as auditing changes or updating linked records.

Building a Schema

I spent a few pages in the XSD schema by hand, which isn't the only way to do it. Visual Studio includes an editor for creating XSD schemas from the Project menu, choose Add New Item , then select the XML Schema item from the Data category, and call it TestSchema.xsd :

click to expand

This will add two new files to your project the .xsd file and a corresponding .xsx file (which is just used by the designer to store layout information for the schema elements that are designed). To create a corresponding set of code for the schema, choose the Generate Dataset option from the Schema menu as shown below.

Choosing this option will add an extra C# file to the project, which again will show up beneath the XSD file in the Solution Explorer. This file is automatically generated whenever changes are made to the XSD Schema, and so should not be edited manually; it is generated, as in the last chapter, with the XSD.EXE tool.

If you click from Schema view to XML view, you will see the raw schema template:

   <?xml version="1.0" encoding="utf-8" ?>     <xs:schema id="TestSchema"     targetNamespace="http://tempuri.org/TestSchema.xsd"     elementFormDefault="qualified"     xmlns="http://tempuri.org/TestSchema.xsd"     xmlns:mstns="http://tempuri.org/TestSchema.xsd"     xmlns:xs="http://www.w3.org/2001/XMLSchema">     </xs:schema>   

This XSD script generates the following C# in the file TestSchema.cs . In the following code I have omitted the bodies of the methods and formatted for easier reading you can inspect the code generated when you work through the example yourself:

   using System;     using System.Data;     using System.Xml;     using System.Runtime.Serialization;     [Serializable()]     [System.ComponentModel.DesignerCategoryAttribute("code")]     [System.Diagnostics.DebuggerStepThrough()]     [System.ComponentModel.ToolboxItem(true)]     public class TestSchema : DataSet     {     public TestSchema() { ... }     protected TestSchema(SerializationInfo info, StreamingContext context)     { ... }     public override DataSet Clone() { ... }     protected override bool ShouldSerializeTables() { ... }     protected override bool ShouldSerializeRelations() { ... }     protected override void ReadXmlSerializable(XmlReader reader) { ... }     protected override System.Xml.Schema.XmlSchema GetSchemaSerializable()     { ... }     internal void InitVars() { ... }     private void InitClass() { ... }     private void SchemaChanged(object sender,     System.ComponentModel.CollectionChangeEventArgs e)     { ... }     }   

I'll use this as the starting point for this section, so that you can see what code changes are made as items are added into the XSD schema. The two main things to note are that an XSD schema is mapped to a DataSet , and that this DataSet is serializable note the protected constructor that can be used by an ISerializable implementation. We'll see more about serialization in Chapter 11.

Adding an Element

The first thing to do is add a new top-level element. Right-click on the workspace and choose Add New Element :

click to expand

This will create a new, unnamed element on screen. You should type in a name for the element; in this example we'll use Product . I've also added some attributes to the element:

When you save the XSD file, the C# file will be modified and a number of new classes generated. We'll discuss the most pertinent aspects of the code generated in this file, TestSchema.cs :

   public class TestSchema : DataSet     {     private ProductDataTable tableProduct;     [System.ComponentModel.DesignerSerializationVisibilityAttribute     (System.ComponentModel.DesignerSerializationVisibility.Content)]     public ProductDataTable Product     {     get     {     return this.tableProduct;     }     }     }   

A new member variable of the class ProductDataTable (described in a moment) is created. This object is returned by the Product property, and is constructed within the updated InitClass() method. From this small section of code, it's evident that the user of these classes can now construct a DataSet from the class in this file, and use DataSet.Products to return the products DataTable .

Generated DataTable

The code below is generated for the DataTable ( Product ) that was added to the schema template:

   public delegate void ProductRowChangeEventHandler     (object sender, ProductRowChangeEvent e);     public class ProductDataTable : DataTable, System.Collections.IEnumerable     {     internal ProductDataTable() : base("Product")     {     this.InitClass();     }     [System.ComponentModel.Browsable(false)]     public int Count     {     get { return this.Rows.Count;}     }     public ProductRow this[int index]     {     get { return ((ProductRow)(this.Rows[index]));}     }     public event ProductRowChangeEventHandler ProductRowChanged;     public event ProductRowChangeEventHandler ProductRowChanging;     public event ProductRowChangeEventHandler ProductRowDeleted;     public event ProductRowChangeEventHandler ProductRowDeleting;   

The generated ProductDataTable class is derived from DataTable , and includes an implementation of the IEnumerable interface. Four events are defined that use the delegate defined above the class when raised. This delegate is passed an instance of the ProductRowChangeEvent class, again defined by Visual Studio .NET.

The generated code includes a class derived from DataRow , which permits type-safe access to columns within the table. You can create a new row in one of two ways:

  • Call the NewRow() (or generated NewProductRow() ) method to return a new instance of the row class. Pass this new row to the Rows.Add () method (or the type-safe AddProductRow() ).

  • Call the Rows.Add() (or generated AddProductRow() ) method, and pass an array of objects, one for each column in the table.

The AddProductRow() methods are shown below:

   public void AddProductRow(ProductRow row)     {     this.Rows.Add(row);     }     public ProductRow AddProductRow (...)     {     ProductRow rowProductRow = ((ProductRow)(this.NewRow()));     rowProductRow.ItemArray = new Object[0];     this.Rows.Add(rowProductRow);     return rowProductRow;     }   

As can be seen from the code, the second method not only creates a new row, it then inserts that row into the Rows collection of the DataTable , and then returns this object to the caller. The bulk of the other methods on the DataTable are for raising events, which I won't discuss here.

Generated DataRow

The ProductRow class generated is shown below:

   public class ProductRow : DataRow     {     private ProductDataTable tableProduct;     internal ProductRow(DataRowBuilder rb) : base(rb)     {     this.tableProduct = ((ProductDataTable)(this.Table));     }     public string Name { ... }     public bool IsNameNull { ... }     public void SetNameNull { ... }     // Other accessors/mutators omitted for clarity     }   

When attributes are added to an element, a property is added to the generated DataRow class as shown above. The property has the same name as the attribute, so in the example above for the Product row, there would be properties for Name , SKU , Description, and Price .

For each attribute added, several changes are made to the .cs file. In the following example, suppose we have added an attribute called ProductId , of type int .

At first the ProductDataTable class (derived from DataTable ) has a private member added, which is the new DataColumn :

   private DataColumn columnProductId;   

This is joined by a property named ProductIDColumn as shown below. This property is defined as internal :

   internal DataColumn ProductIdColumn     {     get { return this.columnProductId; }     }   

The AddProductRow() method shown above is also modified; it now takes an integer ProductID , and stores the value entered in the newly created column:

   public ProductRow AddProductRow (... , int ProductId)     {     ProductRow rowProductRow = ((ProductRow)(this.NewRow()));     rowProductRow.ItemArray = new Object[] { ... , ProductId};     this.Rows.Add(rowProductRow);     return rowProductRow;     }   

Finally, in the ProductDataTable , there is a modification to the InitClass() method:

   private void InitClass()     {     ...     this.columnProductID = new DataColumn("ProductID", typeof(int), null,     System.Data.MappingType.Attribute);     this.Columns.Add(this.columnProductID);     this.columnProductID.Namespace = "";     }   

This creates the new DataColumn and adds it to the Columns collection of the DataTable . The final parameter to the DataColumn constructor defines how this column is mapped back into XML; this is of use when the DataSet is saved to an XML file, for example.

The ProductRow class is updated to add an accessor for this column:

   public int ProductId     {     get { return ((int)(this[this.tableProduct.ProductIdColumn])); }     set { this[this.tableProduct.ProductIdColumn] = value; }     }   
Generated EventArgs

The final class added into the sourcecode is a derivation of EventArgs , which provides methods for directly accessing the row that has changed (or is changing), and the action being applied to that row. This code has been omitted for brevity.

Other Common Requirements

A common requirement when displaying data is to provide a pop-up menu for a given row. There are numerous ways of doing this, but I'll concentrate on one that can simplify the code required, especially if the display context is a DataGrid , where a DataSet with some relations is displayed. The problem here is that the context menu depends on the row being selected, and that row could come from any source DataTable within the DataSet .

As the context menu functionality is likely to be fairly general purpose, the implementation here utilizes a base class ( ContextDataRow ) which supports the menu building code, and each data row class wishing to support a pop-up menu derives from this base class.

When the user right-clicks on any part of a row in the DataGrid , we'll look up the row and check if it derives from ContextDataRow , and if so, PopupMenu() can be called. You could implement this by using an interface; however, in this instance a base class is probably simpler.

This example will show how to generate DataRow and DataTable classes, which can be used to provide type-safe access to data, in much the same way as the previous XSD sample. However, this time the code will be hand crafted, and it also shows one use for custom attributes and reflection.

The following illustration shows the class hierarchy for this example:

click to expand

The full code for this example is available in the \11_Miscellaneous directory:

   using System;     using System.Windows.Forms;     using System.Data;     using System.Data.SqlClient;     using System.Reflection;     public class ContextDataRow : DataRow     {     public ContextDataRow(DataRowBuilder builder) : base(builder)     {     }     public void PopupMenu(System.Windows.Forms.Control parent, int x, int y)     {     // Use reflection to get the list of popup menu commands     MemberInfo[] members = this.GetType().FindMembers (MemberTypes.Method,     BindingFlags.Public  BindingFlags.Instance ,     new System.Reflection.MemberFilter(Filter),     null);     if (members.Length > 0)     {     // Create a context menu     ContextMenu menu = new ContextMenu();     // Now loop through those members and generate the popup menu     // Note the cast to MethodInfo in the foreach     foreach (MethodInfo meth in members)     {     // Get the caption for the operation from the     // ContextMenuAttribute     ContextMenuAttribute[] ctx = (ContextMenuAttribute[])     meth.GetCustomAttributes(typeof(ContextMenuAttribute), true);     MenuCommand callback = new MenuCommand(this, meth);     MenuItem item = new MenuItem(ctx[0].Caption, new     EventHandler(callback.Execute));     item.DefaultItem = ctx[0].Default;     menu.MenuItems.Add(item);     }     System.Drawing.Point pt = new System.Drawing.Point(x,y);     menu.Show(parent, pt);     }     }     private bool Filter(MemberInfo member, object criteria)     {     bool bInclude = false;     // Cast MemberInfo to MethodInfo     MethodInfo meth = member as MethodInfo;     if (meth != null)     if (meth.ReturnType == typeof(void))     {     ParameterInfo[] parms = meth.GetParameters();     if (parms.Length == 0)     {     // Lastly check if there is a ContextMenuAttribute on the     // method...     object[] atts = meth.GetCustomAttributes     (typeof(ContextMenuAttribute), true);     bInclude = (atts.Length == 1);     }     }     return bInclude;     }     }   

The context data row class is derived from DataRow , and contains just two member functions. The first, PopupMenu , uses reflection to look for methods that correspond to a particular signature, and it displays a pop-up menu of these options to the user. Filter() is used as a delegate by PopupMenu when enumerating methods. It simply returns true if the member function does correspond to the appropriate calling convention:

   MemberInfo[] members = this.GetType().FindMembers(MemberTypes.Method,     BindingFlags.Public  BindingFlags.Instance,     new System.Reflection.MemberFilter(Filter),     null);   

This single statement is used to filter all methods on the current object, and return only those that match the following criteria:

  • The member must be a method

  • The member must be a public instance method

  • The member must return void

  • The member must accept zero parameters

  • The member must include the ContextMenuAttribute

The last of these is a custom attribute, written specifically for this example. I'll discuss this after completing the dissection of the PopupMenu method:

   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);   

A context menu instance is created, and we loop through each method that matches the above criteria, and add the item to the menu. The menu is subsequently displayed as shown in the following screenshot:

The main area of difficulty within this example is the following section of code, repeated once for each member function that is to be displayed on the pop-up menu:

   System.Type ctxtype = typeof(ContextMenuAttribute);     ContextMenuAttribute[] ctx = (ContextMenuAttribute[])     meth.GetCustomAttributes(ctxtype);     MenuCommand callback = new MenuCommand(this, meth);     MenuItem item = new MenuItem(ctx[0].Caption,     new EventHandler(callback.Execute));     item.DefaultItem = ctx[0].Default;     menu.MenuItems.Add(item);   

Each method that should show up on the context menu is attributed with the ContextMenuAttribute . This defines a user-friendly name for the menu option, as a C# method name cannot include spaces, and it's wise to use real English on pop-up menus rather than some internal code. The attribute is retrieved from the method, and a new menu item created and added to the menu items collection of the pop-up menu.

This example code also shows the use of a simplified Command class (a common design pattern). The MenuCommand class used in this instance is triggered from the user choosing an item on the context menu, and it forwards the call to the receiver of the method in this case the object and method that was attributed. This also helps keep the code in the receiver object more isolated from the user interface code. This code is explained in the following sections.

Manufactured Tables and Rows

The XSD example earlier in the chapter showed the code produced when the Visual Studio editor was used to generate a set of data access classes, and you may be wondering what the minimal set of code for these classes looks like. The following class shows the required methods for a DataTable , which are fairly minimal:

   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.Type GetRowType()     {     return typeof(CustomerRow);     }     protected override DataRow NewRowFromBuilder(DataRowBuilder builder)     {     return(DataRow) new CustomerRow(builder);     }     }   

The first prerequisite of a DataTable is that you override the GetRowType() method. This is used by the .NET internals when generating new rows for the table. You should return the type of the class used to represent each row.

The next prerequisite is that you implement NewRowFromBuilder() , again called by the runtime when creating new rows for the table. That's enough for a minimal implementation. Our implementation includes adding columns to the DataTable . Since we know beforehand what the columns are in this example, we can add them accordingly . The corresponding CustomerRow class is fairly simple. It implements properties for each of the columns within the row, and then implements the methods that ultimately are displayed on the context menu:

   public class CustomerRow : ContextDataRow     {     public CustomerRow(DataRowBuilder builder) : base(builder)     {     }     public string CustomerID     {     get { return (string)this["CustomerID"];}     set { this["CustomerID"] = value;}     }     // Other properties omitted for clarity     [ContextMenu("Blacklist Customer")]     public void Blacklist()     {     // Do something     }     [ContextMenu("Get Contact",Default=true)]     public void GetContact()     {     // Do something else     }     }   

The class simply derives from ContextDataRow , including the appropriate getter/setter methods on properties named the same as each field, and then a set of methods may be added that are used when reflecting on the class:

   [ContextMenu("Blacklist Customer")]     public void Blacklist()     {     // Do something     }   

Each method that you wish to have displayed on the context menu has the same signature, and includes the custom ContextMenu attribute.

Using an Attribute

The idea behind writing the ContextMenu attribute was to be able to supply a free text name for a given menu option. I have also implemented a Default flag, which is used to indicate the default menu choice. The entire attribute class is presented here:

   [AttributeUsage(AttributeTargets.Method,AllowMultiple=false,Inherited=true)]     public class ContextMenuAttribute : System.Attribute     {     public ContextMenuAttribute(string caption)     {     Caption = caption;     Default = false;     }     public readonly string Caption;     public bool Default;     }   

Here, the AttributeUsage attribute on the class marks the ContextMenuAttribute as only being usable on a method, and it also defines that there may be only one instance of this object on any given method. The Inherited=true clause defines whether the attribute can be placed on a superclass method, and still reflected upon by a subclass.

You can probably think of a number of other members to add to this attribute. Some examples are:

  • 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

Dispatching Methods

When a menu is displayed within .NET, each menu option is linked to the processing code for that option by means of a delegate. In implementing the mechanism for hooking menu choices to code, you basically have two choices:

  • Implement a method with the same signature as the System.EventHandler . This is defined as shown below:

       public delegate void EventHandler(object sender, EventArgs e);   
  • Define a proxy class, which implements the above delegate, and forwards calls to the received class. This is known as the Command pattern, and is what I have chosen for this example.

The Command pattern separates the sender and the receiver of the call by means of a simple intermediate class. You may think this is overkill for such an example, but it makes the methods on each DataRow simpler (as they don't need the parameters passed to the delegate), and it is more extensible:

   public class MenuCommand     {     public MenuCommand(object receiver, MethodInfo method)     {     Receiver = receiver;     Method = method;     }     public void Execute(object sender, EventArgs e)     {     Method.Invoke(Receiver, new object[] {});     }     public readonly object Receiver;     public readonly MethodInfo Method;     }   

The class simply provides an EventHandler delegate (the Execute method), which invokes the desired method on the receiver object. Our example handles two different types of row: rows from the Customers table, and rows from the Orders table. Naturally, the processing options for each of these types of data are likely to differ . The previous image showed the operations available for a Customer row. The image below shows the options for an Order row:

click to expand

Getting the Selected Row

The last piece of the puzzle for this example is how to work out which row within the DataSet the user has clicked upon. Your first thought might be "it must be a property on the DataGrid ", but try as you like you won't find it there. You might look at the hit test information that you can obtain from within the MouseUp() event handler, but that only helps if you are displaying data from a single DataTable .

Going back to how the grid is filled for a moment, the line of code is:

   dataGrid.SetDataBinding(ds,"Customers");   

Remember the section on DataBinding ? This method adds a new CurrencyManager into the BindingContext , which represents the current DataTable and the DataSet . Now, the DataGrid has two properties, DataSource and DataMember , which are set when you call SetDataBinding() . DataSource in this instance will be a DataSet , and DataMember will be Customers .

We have a data source, a data member, and know that this information is stored within the BindingContext of the form. All we need to do is look up the information:

   protected void dataGrid_MouseUp(object sender, MouseEventArgs e)     {     // Perform a hit test     if(e.Button == MouseButtons.Right)     {     // Find which row the user clicked on, if any     DataGrid.HitTestInfo hti = dataGrid.HitTest(e.X, e.Y);     // Check if the user hit a cell     if(hti.Type == DataGrid.HitTestType.Cell)     {     // Find the DataRow that corresponds to the cell     //the user has clicked upon   

After calling dataGrid.HitTest() to calculate where the user has clicked the mouse, we then retrieve the BindingManagerBase instance for the data grid:

   BindingManagerBase bmb = this.BindingContext[ dataGrid.DataSource,     dataGrid.DataMember];   

This uses the DataGrid 's DataSource and DataMember to name the object we want to be returned. All we want to do now is find the row the user clicked on, and display the context menu. With a right mouse click on a row, the current row indicator doesn't normally move, but that's not good enough for us. We want to move the row indicator and then pop up the menu. From the HitTestInfo object we have the row number, so all I need to do is move the BindingManagerBase object's current position:

   bmb.Position = hti.Row;   

This changes the cell indicator, and at the same time means that when I call into the class to get the Row , I end up with the current row and not the last one selected:

   DataRowView drv = bmb.Current as DataRowView;     if(drv != null)     {     ContextDataRow ctx = drv.Row as ContextDataRow;     if(ctx != null) ctx.PopupMenu(dataGrid,e.X,e.Y);     }     }     }     }   

As the DataGrid is displaying items from a DataSet , the Current object within the BindingManagerBase collection is a DataRowView , which is tested by an explicit cast in the code above. If this succeeds, I can then retrieve the actual row that the DataRowView wraps by performing another cast to check if it is indeed a ContextDataRow , and finally pop up a menu.

In the example, you'll notice that I have created two data tables, Customers and Orders , and defined a relationship between these tables, so that when you click on CustomerOrders you see a filtered list of orders. When you do this, the DataGrid changes the DataMember from Customers to Customers.CustomerOrders , which just so happens to be the correct thing that the BindingContext indexer uses to retrieve the data being shown.

  


Professional C#. 2nd Edition
Performance Consulting: A Practical Guide for HR and Learning Professionals
ISBN: 1576754359
EAN: 2147483647
Year: 2002
Pages: 244

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net