As Published In
Oracle Magazine
September/October 2008

DEVELOPER: ODP.NET


Customize the Experience

By Mark A. Williams Oracle ACE Director

Use the built-in capabilities of ODP.NET to provide globalization and personalization.

The user interface in an Oracle application is the portal through which users interact with the data in Oracle Database, and presenting this data in a format that is appropriate for each user’s locale, region, or country will enhance the user experience. Depending on the data access layer used, customizing applications for each user locale can be an arduous task, but this is definitely not the case when you use Oracle Data Provider for .NET (ODP.NET) as your data access layer.

This column demonstrates how to use ODP.NET to build an application that enables users to customize their experience by selecting the language and format of date and numeric data. The application queries Oracle Database, based on user input, and then presents the data formatted according to user-defined criteria. The user interface provides options for setting the language, territory, date format, and whether to perform a case-insensitive search. (For additional information on these settings as well as valid formats, see the Oracle Database Globalization Support Guide).

To build the application in this column, you will need the following:

 

  • Access to Oracle9i Database Release 9.2 or higher (any edition including Oracle Database Express Edition)

  • A tnsnames.ora file alias for your database (mine is named ORAMAG)

  • The HR sample schema installed (the application uses the EMPLOYEES table)

  • Microsoft Visual Studio 2005 or Visual Studio 2008 (non-Express Editions)

  • Oracle Developer Tools for Visual Studio .NET 11.1.0.6.20

  • Oracle Data Provider for .NET 11.1.0.6.20



To build the application, you create a Visual Studio solution, lay out the Web form items (including setting properties), configure datasources, and execute the application by using the built-in Web server. The sample download for this column contains all files needed (including comments in the source code) as well as a README file that outlines the use of the download.

Let’s begin by creating a new ASP .NET 2.0 Web site in Visual Studio.

Create the Solution

The following steps create the ASP.NET 2.0 solution inside Visual Studio 2005 or 2008:

1. From the Visual Studio main menu, select File -> New -> Web Site.

2. Select ASP.NET Web Site from the New Web Site dialog box, select File System from the Location list, select Visual C# from the Language list, and enter a path in the text box next to the Location list. (I entered E:\My Projects\WebSites\SepOct2008 as the location for the Web site on my system.) Click OK ; Visual Studio will create the solution and open the Default.aspx page in the editor.

3. To add a reference to the ODP.NET assembly, select Website -> Add Reference from the Visual Studio main menu, select Oracle.DataAccess under Component Name in the Add Reference dialog box, and click OK .

Lay Out the Web Form

Follow the steps in this section to lay out the user interface elements on the Web form. To keep the interface as simple and compact as possible, I use standard ASP.NET 2.0 components and have not segregated functionalities, such as presentation and business logic, into different layers.

1. Click the Source tab to switch to the source view in the editor.

2. Expand the HTML node within the toolbox, drag a Table object from the toolbox, and drop it between the <div> and </div> tags in the source view of the Web form. Next, edit the HTML source for the table as follows to create a table with five rows and two columns:

3. Now that you have created the HTML table that will serve as the container for the user interface input controls (such as the lists and text boxes), click the Design tab to switch to the design view.

4. The first column of cells in the table will hold the labels and a button control. In the first cell of the first row, type Language: ; in the first cell of the second row, type Territory: ; in the first cell of the third row, type Date Format: ; in the first cell of the fourth row, type Last Name: ; and drag a Button control from the Standard node in the toolbox and drop it into the first cell of the last row.

5. Click the Button control to select it, and then in the properties window, enter Lookup as the Text property and enter btnLookup as the (ID) property.

6. Drag a DropDownList control from the Standard node in the toolbox, and drop it into the second cell of the first row. In the properties window, enter ddlLanguage as the (ID) property.

7. Drag another DropDownList control, and drop it into the second cell of the second row. In the properties window, enter ddlTerritory as the (ID) property.

8. Drag a TextBox control (from the Standard node), and drop it into the second cell of the third row. In the properties window, enter txtDateFormat as the (ID) property.

9. Drag another TextBox control, and drop it into the second cell of the fourth row. In the properties window, enter txtLastName as the (ID) property.

10. Drag a CheckBox control (from the Standard node) into the second cell of the last row. In the properties window, enter chkCaseInsensitive as the (ID) property and enter Case Insensitive as the Text property.

11. Next, add the datasource objects to the Web form. Expand the Data node in the toolbox, drag a SqlDataSource control onto the designer surface below the HTML table, and enter dsLanguage as the (ID) property. Drag another SqlDataSource control onto the form under the dsLanguage control. Enter dsTerritory as the (ID) property for this datasource control.

12. The last step in laying out the form is to drag a GridView control onto the form below the datasource controls. Enter gvResults as the (ID) property, click the GridView Tasks arrow at the upper right corner of the GridView , select the Auto Format link from the available tasks, select Rainy Day from the available format schemes, and then click OK to apply the format.

At this point, the layout will look similar to the layout in Figure 1.

 

figure 1
Figure 1: The ASP.NET 2.0 Web form at design time


Configure the Datasources, and Connect the Controls

Now that you have successfully completed the layout of the various controls and HTML elements on the Web form, it is time to “wire” the pieces together. In this section, you create the necessary database connection string and SQL text and bind the controls together.

1. Click the dsLanguage SqlDataSource control to select it, and then click the quick tasks arrow in the upper right corner of the control to open the SqlDataSource Tasks window. Click the Configure Data Source option, and then click New Connection and ensure that Oracle Database (Oracle ODP.NET) is listed for Data source . If it is not, click Change and select Oracle Database for Data source and Oracle Data Provider for .NET for Data provider and click OK . In the Add Connection window, select Data source name from the list for the datasource you want to use ( ORAMAG in my case). Enter HR for User name , provide the password for the HR user in your system, and click the Save password check box.

Next, click Advanced and set the following properties in the Advanced Properties window, by selecting the appropriate values from the corresponding lists: Metadata Pooling = false, Enlist = false , and Statement Cache Purge = true . Click OK to close the Advanced Properties window, and then click OK to close the Add Connection window.

2. Now that the connection has been configured, click Next ; click the Yes, save this connection as: check box; change the connection string name to SepOct2008 ; click Next ; click the Specify a custom SQL statement or stored procedure option; click Next ; enter the following text as the SQL statement :

 

select value from v$nls_valid_values 
where parameter='LANGUAGE' order by 1


click Next ; and, finally, click the Finish button.

3. Click the dsTerritory SqlDataSource control to select it, and click the quick tasks arrow in the upper right corner of the control to open the SqlDataSource Tasks window as before. Click the Configure Data Source option, select the previously defined connection from the list, click Next , click the Specify a custom SQL statement or stored procedure option, click Next , enter the following text as the SQL text :

 

select value from v$nls_valid_values where parameter='TERRITORY' order by 1 


click Next , and click Finish .

4. Click the ddlLanguage list to select it, click the quick tasks arrow, select Choose Data Source , select dsLanguage from the Select a data source list, and click OK .

5. Click the ddlTerritory list to select it, click the quick tasks arrow, select Choose Data Source , select dsTerritory from the Select a data source list, and click OK .

6. Double-click the btnLookup button to open the source code editor for the Default.aspx.cs file; add the following to the list of using directives:

 

using Oracle.DataAccess.Client; 


and for the automatically created btnLookup_Click handler, enter the code in Listing 1.

Code Listing 1: The button click event handler code

 

protected void btnLookup_Click(object sender, EventArgs e) {
        string strLastName = string.Empty;

        string constr = ConfigurationManager.ConnectionStrings["SepOct2008"].ConnectionString;

        OracleConnection con = new OracleConnection(constr);
        con.Open();

        OracleGlobalization glb = OracleGlobalization.GetThreadInfo();

        glb.Language = ddlLanguage.Text;
        glb.Territory = ddlTerritory.Text;

        if (txtDateFormat.Text.Length > 0) {
                glb.DateFormat = txtDateFormat.Text;
        }

        if (txtLastName.Text.Length > 0) {
                strLastName = txtLastName.Text;
        }
        else {
                strLastName = "%";
        }

        if (chkCaseInsensitive.Checked) {
                glb.Sort = "BINARY_CI";
                glb.Comparison = "LINGUISTIC";
        }

        con.SetSessionInfo(glb);

        OracleCommand cmd = con.CreateCommand();
        cmd.CommandText = "select first_name, " +
                                                         "last_name, " +
"to_char(hire_date) hire_date, " +
"to_char(nvl(commission_pct, 0), 'fm990D00') commission_pct " +
"from employees " +
"where last_name like (:1) " +
"order by last_name, first_name";
OracleParameter pLastName = new OracleParameter(); pLastName.Value = strLastName; cmd.Parameters.Add(pLastName); OracleDataReader dr = cmd.ExecuteReader(); gvResults.DataSource = dr; gvResults.DataBind(); glb.Dispose(); dr.Dispose(); pLastName.Dispose(); cmd.Dispose(); con.Dispose(); }


Run the Sample 

Next Steps



READ more about
ODP.NET
Oracle Data Provider for .NET Developer’s Guide
Oracle Database Globalization Support Guide

 DOWNLOAD the sample code for this column

 VISIT the .NET Developer Center

You can now execute this ASP.NET 2.0 Web site application by pressing the F5 key from within Visual Studio and clicking OK to enable debug information to be created in the configuration file. To set the display for the French language and France as the country, do the following when the browser displays the Web form: select FRENCH from the Language list and FRANCE from the Territory list; enter DD-fmMonth-YYYY for Date Format ; enter g% for Last Name ; click the Case Insensitive check box; and, finally, click the Lookup button. Note that the month names are displayed in French, the Commission Percent decimal separator is a comma (appropriate for the FRANCE territory choice), and the values for Last Name are correctly retrieved in a case-insensitive manner. (Note that if your browser does not support the display of the selected language, you may see incorrect characters in the output.) I encourage you to experiment with different combinations of language, territory, date format, and case-insensitive searching.


Conclusion

The steps in this column have demonstrated how to quickly and easily build an ASP.NET 2.0 application with ODP.NET that can conform to a user’s preference for language and formatting of date and numeric data stored in Oracle Database. Using ODP.NET to provide this custom user experience increases developer productivity (because there is less code to write) and makes it easier to manage code, because your application will use a single set of code that works for all locales.

 



Mark A. Williams (mawilliams@cheshamdbs.com) is an Oracle ACE director, an Oracle Certified Professional DBA, the author of Pro .NET Oracle Programming (Apress, 2004), and a contributor to the Oracle Data Provider for .NET forum on Oracle Technology Network.

Send us your comments

false ,,,,,,,,,,,,,,,,