Visual Studio .NET and Data Access C# Help

This section discusses some of the ways that Visual Studio allows data to be integrated into the GUl, including how to create a connection, select some data, generate a DataSet, and use all of the generated objects to produce a simple application. The available tools enable you to create a database connection with the OleDbConnection or SqlConnection classes, The class you use depends on the type of database you are using, After a connection has been defined, you can create a DataSet and populate it from within Visual Studio .NET. This generates an XSD file for the DataSet and the. cs code. The result is a type-safe DataSet.

Creating a Connection

First, create a new Windows application, and then create a new database connection. Using the Server Explorer (see Figure 32·19), you can manage various aspects of data access.

Figure 32-19

Figure 32-19

For this example, create a connection to the Northwind database. Select the Add Connection option from the context menu available on the Data Connections item to launch a wizard that enables you to choose a database provider, Select the .NET Framework Provider for SQL Server. Figure 32-20 shows the Add Connection dialog box.

Figure 32-20

Figure 32-20

Depending on your NET Framework installation, the sample databases might be located in SQL Server, M3DE (Microsoft SQL Server Data Engine), or both.

To connect to the local MSDE database, if it exists, type (local) \ \sql express for the name of the server, To connect to a regular SQL Server instance, type (local) or ‘: to select a database on the current machine, or the name of the desired server on the network. You may need to enter a user name and password to access the database.

Select the Northwind database from the drop-down list of databases, and to ensure that everything is set up correctly, click the Test Connection button. If everything is set up properly, you should see a message box with a confirmation message.

Visual Studio 2005 had numerous changes when accessing data, and these are available from several places in the user interface. The Data menu is a good choice because it permits you to view any data sources already added to the project, add a new data source, and preview data from the underlying database (or other data source).

The following example uses the Northwind database connection to generate a user interface for selecting data from the Employees table. The first step is to choose Add New Data Source from the Data menu, which begins a wizard that walks you through the process. The dialog shown in Figure 32-21 shows part of the Data Source Configuration Wizard, in this case where you can select appropriate tables for the data source.

As you progress through the wizard, you can choose the data source, which can be a database, local database file (such as an mdb file), a Web service, or an object. You will then be prompted for further information based on the type of data source you choose. For a database connection, this includes the name of the connection (which is subsequently stored in the application configuration file shown in the following code), and you can then select the table, view, or stored procedure that supplies the data, Ultimately, this generates a strongly typed DataSet within your application.

Figure 32-21

Figure 32-21

This includes the name of the connection, the connection string itself, and a provider name, which isused when generating the connection object. You can manually edit this information as necessary, To display a user interface for the employee data, you can simply drag the chosen data from the Data Sources window onto your form. This will generate one of two styles of user interface for you – a gridstyle UI that utilizes the DataGridView control described earlier or a details view that presents just the data for a single record at a time. Figure 32-22shows the details view.

Figure 32-22

Figure 32-22

Dragging the data source onto the form generates a number of objects, both visual and nonvisual, The nonvisual objects are created within the tray area of the form and comprise a DataConnector, a strongly typed DataSet, and a TableAdapter, which contains the SQLused to select/update the data, The visual objects created depend on whether you have chosen the DataGridView or the details view, Both include a DataNavigator control that can be usedto page through the data. Figure 32-23 shows the user interface generated using the DataGridView control- one of the goals of Visual Studio 2005 was to simplify data access to the point where you could generate functional forms without writing a single line of code.

Figure 32-23

Figure 32-23

When the data source is created, it adds a number of files to your solution, To view these, click the Show Ali Files button in the Solution Explorer.

You will then be able to expand the data set node and view the extra files added, The main one of interest Is the Designer cs file, which includes the C# source code used to populate the data set. You will find several classes defined within the Designer cs file, The classes represent the strongly typed data set, which acts in a similar way to the standard DataAdapter class. This class internally uses the Data  adapter to fill the DataSet.

Selecting Data

The table adapter generated contains com! ands for SELECT, INSERT, UPD.1\TE,and DELETE Needless to say, these can (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 the . Designer file:

An object is defined for each of the SQL commands, with the exception of the Select command, and also a SqlDatal’.dapter Further down the me, in the intializeComponent () method, the wizard has generated code to create each one of these commands as well as the data adapter.

In previous versions of  visual Studio ‘ET the commands generated for Insert and Update also included a select clause – this was used as a way to resynchronize the data with that on the server, just in case any fields within the database were calculated (such as Identity columns and/or computed fields), The wizard generated code works but is less than optimal. For a production system, all the generated SQL
should probably be replaced with calls to stored procedures, If the INSERT or UPDATE clauses didn’t have to resynchronize the data, the re-noval of the redundant SQL clause would speed up the application a little.

Updating the Data Source

So far, the applications have selected data IT m the database. This section discusses how to persist changes to the database, If you followed the steps in the previous section, you should have an application that contains everything needed for a rudimentary application, The one change necessary is to enable the Save button on the generated toolbar and write an event handler that will update the database.

From the IDE, select the Save button from the data navigator control, and change the Enabled property to true. Then, double-click the button to generate an event handler. Within this handler, save the changes made onscreen to the database:

private void dataNavigatorSaveItem_Click(object sender, EventArgs e)
employeesTableAdapter.Update (employeesDataset. Employees) ;

Because Visual Studio has done the hard work for you, all that’s needed is to use the Update method of the table adapter class that was generated, Six Update methods are available on the table adapter – this example uses the override that takes a DataTable as the parameter.

Other Common Requirements

A common requirement when displaying data is to provide a pup-up menu for given row, You (an this in numerous ways, The example in this section focuses on one approach that can simply the code required, especially if the display context Is a DataGr id, where a DataSet with some relations is displayed, The problem here is that the context menu depends on the row thai is selected, and I at row could be part of any source DataTable in the DataSet.

Because the context menu functionality is likely to be general-purpose in nature, the implementation here uses a base class (ContextDataRow) that supports the menu-building code, and each data row class that supports a pop-up menu derives from this base class.

When the user right-clicks any part of a row in the DataGrid, the row is looked up to check if It derives from ContextDataRow, and If so, Popupmenuj () can be called. ThIS could be implemented using an interface; however, in this instance, a base class provides a simpler solution, This example demonstrates how to generate DataRow and DataTable classes that can be used to provide type-safe access to data in much the same way as the previous XSD sample, However, this time you write the code yourself to show how to use custom attributes and reflection in this context.
Figure 32-24 illustrates the class hierarchy for this example.

Figure 32-24

Figure 32-24

Here is the code for this example:

The ContextDataRow class is derived horn DataRow and contains just two member functions: PopupMenu and Filter ( ), 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:

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

  1. The member must be a method.
  2. The member must be a public instance method.
  3. The member must return void.
  4. member The member must accept zero parameters.
  5. The member must include the ContextMenuAttribute.

The last of these criteria refers to a custom attribute, written specifically for this example. (It’s discussed after discussing the PopupMenu method.)

A context menu instance is created, and a pop-up menu item is added for each method that matches the preceding criteria. The menu is subsequently displayed as shown in Figure 32-25.

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

Figure 32-25

Figure 32-25

Each method that should he disnla yed on the context menu is attributed with the ContextMenuAttribute, This defines a user-friendly name for the menu option because 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 IS created and added to the menu items collection of the pop-up menu.

This sample code also shows the use of a simplified Command class (a common design pattern), The MenuConunand class used in this instance is triggered by 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 eplained in the following sections.

Manufactured Tables and Rows

The XSD example earlier in the chapter showed the code produced when the Visual Studio .NET editorm is used to generate a set of data access classes, The following class shows the required methods for a DataTable, which are fairly minimal (and they all have been generated manually):

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, 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:

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

Each method that is to be 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 is to be able to supply a free text name for.a given menu option. The following example also adds a Defaul t flag, which is used to indicate the default menu choice, The entire attribute class is presented here:

The Attribute Usage attribute on the class marks ContextMenuAt tribute as being usable on only a method, and it also defines that there can only be 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 on by a subclass.

A number of other members could be added to this attribute, including the following:

  1. A hotkey for the menu option
  2. An image to be displayed
  3. Some text to be displayed in the toolbar as the mouse pointer rolls over the menu option
  4. A help context ID

Dispatching Methods

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

  1. Implement a method with the same signature as the System. EventHandler. This is defined as shown in this snippet:

public delegate void EventHandler(object sender, EventArgs e);

  1. Define a proxy class, which implements the preceding delegate and forwards calls to the received class, This is known as the Command pattern and is what has been chosen for this example.

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

The class simply provides an EventHandler delegate (the Execute method), which invokes the desired method on the receiver object, This example handles two different types of 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, Figure 32-25 showed the operations available for a Customer row, whereas Figure 32-26 shows the options available for an Order row.

Figure 32-26

Figure 32-26

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 selected. You might think that it must be a property on the DataGrid. However, this control is not available in this context. The hit test information obtained from within the MouseUp () event handler might also be a likely candidate to look at, but that only helps if the data displayed is from a single DataTable.

Remember how the grid is filled:

dataGrid.SetDataBinding(ds, ·Customers·);

This method adds a new CurrencyManager to the BindingContext, which represents the current DataTable and the DataSet. Now, the DataGrid has two properties, DataSource and DataMember, which are set when the SetDataBinding () is called. DataSource in this instance refers to a DataSet and the DataMember property refers to Customers.

Given the data source, a data member, And the binding context of the form, the current row can be located with the following code:

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

BindingManagerBase bmb = this. BindingContext[ dataGrid.DataSource,

This uses the DataGrid’sbataSource and DataMember to name the object to be returned. All that is left now is to find the row the user clicked and display the context menu, With a right-click on a row, the current row indicator doesn’t normally move, but that’s not good enough, The rowjndicator should be moved and then the pop-up menu should be displayed, The HitTestInfo object mcludes the row number, so the BindinqManagerBase object’s current position can be changed as follows:

bmb.Position – hti.Row:

This changes the cell indicator and at the same time means that when a call is made into the class to get the Row,the current row is returned, not the previous one selected:

Because 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 previous code, If this succeeds, the actual row that the DataRowView wraps can be retrieved by performing another cast to check if it is indeed a ContextDataRow, and finally pop up a menu.

In this example, you will notice that two data tables, CUstomers and Orders, have been created, and a relationship has been defined between these tables, so that when users click CustomerOrders they see a filtered list of orders, When the user clicks, the DataGrid changes the DataMember from Customers to Customers. CustomerOrders, which just so happens to be the correct object that the Binding-Context’ indexer uses to retrieve the data being shown.

Posted on November 2, 2015 in Data Binding

Share the Story

Back to Top