Creating and Linking LOBs in a Data Application for Microsoft .Net using DB2 Universal Database

Using a sample data-bound, forms-based application, learn how to manipulate LOBs. It's easy with the DB2 UDB Add-Ins for Visual Studio .NET.

Sonali Surange, Advisory Software Engineer, EMC

Sonali Surange is an Advisory Software Engineer working on Data Management Application Development Tools at the IBM San Francisco Lab.


developerWorks Contributing author
        level

24 April 2003

Note from the editor: The IBM DB2 Development Add-Ins for Visual Studio .NET are available as part of DB2 V8.1.2 and later.

Introduction

Using a sample application, this article shows you how IBM® tools provide rapid application development for developing DB2-based applications on Microsoft® Visual Studio .Net. In this article, I will first highlight the features provided by the IBM Explorer for DB2 development on Microsoft Visual Studio .Net. Then I will show you how to build a sample data-bound Windows® forms-based application to access DB2® Universal DatabaseTM (UDB) data sources. The sample consists of two parts:

  • The first part demonstrates retrieval, updates, inserts and deletes of data from a DB2 table using data binding.
  • The second part demonstrates retrieval and updates of BLOBs and CLOBs from a DB2 table using data binding.

I'll also describe how to test the sample.


Overview of IBM Explorer

IBM Explorer is part of the IBM solution for integrating DB2 UDB into Microsoft Visual Studio .Net. The IBM Explorer lets you create and manipulate data connections to local and remote DB2 UDB data sources and browse their contents. The IBM Explorer facilitates rapid application development for projects that access DB2 data sources by allowing creation of preconfigured DB2 data components. These components can be further used for application development.

IBM Explorer provides the following key features:

  • Adding DB2 connections to existing local or remote databases (on Windows 2000®, UNIX®, and OS/390®). Browse detailed properties for tables and views, stored procedures and user-defined functions (table and scalar). Column and parameter information is displayed in the standard Microsoft Visual Studio.Net properties window.
  • View table properties - Name, type, schema and other standard properties.
  • View column properties - Primary key, allow nulls, identity columns, data types, other standard properties as name and schema.
  • View stored procedure and functions properties - Specific name, language, package name, number of result sets and other standard properties such as name and schema.
  • View detailed parameter information for stored procedures and functions.
  • Retrieve data from tables and views asynchronously, and view the results in a grid format for easy readability.
  • Retrieve schema information from the database on an on demand basis or on startup, based on your choice.
  • Execute stored procedures and user-defined functions (UDFs) asynchronously. View results sets, input, output, in-out and return parameter values in a grid format.
  • Drop tables, views, stored procedures and UDFs from the database.
  • Maintain a client-side user-based cache of the schema information retrieved, with password encryption support.
  • Ability to apply filters to limit the schema information retrieval to the desired objects only.
  • Generate ADO.Net data components using drag and drop on Microsoft Visual Studio.Net windows forms projects for any Visual Studio .Net language such as C#, VB.NET or J#. These data sources are preconfigured for DB2 UDB and can be used to further develop applications that access DB2 UDB.
  • Monitor database activity in IBM DB2 Output Message Pane that is integrated with the Visual Studio.Net output window.

This article assumes that you have already installed DB2 with the Visual Studio .Net tools. This article also assumes that the DB2 SAMPLE database is installed on your DB2 installation. You can install the sample database by running the First Steps application.


Building a data-bound DB2 application with IBM Explorer

In this exercise we will use IBM Explorer to create a DB2 database connection to the SAMPLE database in the Visual Studio.Net environment. This connection will be further used to develop the sample application.

  1. Launch Microsoft Visual Studio.Net IDE.
  2. Click View -> IBM Explorer, if the IBM Explorer window is not already visible.
  3. Right click on Data Connections and select Add Connection from the context menu.
  4. A list of databases on your machine is available in the Database Alias combo box. Select SAMPLE and optionally add a user name and password. Click Test Connection.

    A message should pop up indicating success, as shown in Figure 1.

    Figure 1. Testing the connection
    Testing the connection
  5. Click OK on the Database Connection Properties dialog. Observe the messages in the IBM DB2 Message Pane indicating that schema information was successfully retrieved from the database. If the IBM DB2 Message pane is not visible, from Visual Studio.Net menu click View -> Other Windows -> Output to display the Output window (Figure 2).
    Figure 2. Output window
    Output window

By default, all schema information is retrieved for the connection and filters added. You can change this behavior to retrieve data on demand. This is useful if the schema contains large amounts of data. In either case, data retrieval is transparent, which means you can work in the IDE while the data retrieval request completes asynchronously.

Change the behavior to be 'on demand' by setting the PrefetchCache to "false" in the userOptions.xml file, as shown in this excerpt:

<DBAccess description="Database access settings" name="Database Settings"> 
  <MaxRows description="Maximum number of rows to retrieve for tables  
 and views." val="100" type="INT" />  
  <Timeout description="Database command execution timeout in seconds."  
 val="30" type="INT" /> 
  <PrefetchCache description="Prefetch filtered database catalogue data cache 
 on first retrieval." val="false" type="BOOL" />  
</DBAccess>

See IBM DB2 Development Add-Ins for Visual Studio.NET for more information on userOptions.xml.

Information on each folder will now be retrieved asynchronously only after you click on the folder. For example, clicking on the Tables folder retrieves the list of tables from the database schema asynchronously. The IBM DB2 Message Pane indicates the status of such requests (Figure 3).

Figure 3. Output message pane
Output message pane

We are now ready to create a form using data binding for a DB2 table.


Retrieving, updating, inserting, and deleting data

Now let's build and test a sample Employee application.

  1. Create a new C# Windows application, and call it db2lobs (See the attached sample code db2lobs.zip for the complete code). C# is used to develop the sample application in this exercise, but IBM Explorer can be used to create data-bound applications for any Visual Studio.Net language that is based on the Common Language Runtime.
  2. Drop a Data Grid control and a button control onto the form. Change the name property for the Data Grid control to dataGrid_employee. Change the text property for the button control to Apply changes to Database, and change the name property to applyChangesToDb.
  3. Drag and drop the EMPLOYEE table from IBM Explorer onto the form. This will create two preconfigured components:
    • db2Connection1 - With the connection string property set to SAMPLE database.
    • db2DataAdapter1 - With SELECT, INSERT, and UPDATE statements set for the EMPLOYEE table (Figure 4)
    Figure 4. The preconfigured components db2Connection1 and db2DataAdapter1
    db2Connection1 and db2DataAdapter1
  4. Click on the db2DataAdapter and click on Generate DataSet. Select New: DB2DataSet1 and click OK. This will create a dataset object and an instance of that object called db2DataSet11.
  5. Click on dataGrid_employee on the form and in the properties window make the following property selections, in order to data-bind the EMPLOYEE table to the data grid:
    • For DataSource - Select Db2DataSet11
    • For DataMember - Select EMPLOYEE
  6. Now we are ready to add some C# code in this form to retrieve and update data between the database and the data grid.
    1. Double click on the form. This will create the instances of the dropped objects into Form1.cs and then display the Form1.cs code.

      Add the following line of code (listed in bold) in Form1_Load event:

      private void Form1_Load(object sender, System.EventArgs e) 
      { 
      	this.db2DataAdapter1.Fill(this.db2DataSet11); 
      }
    2. In Solution Explorer, double click on Form1.cs to once again display Form1 in design mode. Double click on the button control to create a click event handler.

      Add the following line of code (listed in bold):

      private void applyChangesToDb_Click (object sender, System.EventArgs e) 
      { 
      	this.db2DataAdapter1.Update(this.db2DataSet11) 
      }

Testing the employee sample

  1. To test this sample, click Debug -> Start Without Debugging from the Visual Studio .Net menu. This will build and run the application.

    You will see data from the department table in the grid. We have successfully retrieved data from the DB2 database.

  2. Now add a row and change existing rows, as shown in Figure 5. Click the Apply Changes to Database button and reload the form. Notice that the changed values are displayed in the data grid from the database.
    Figure 5. Applying changes to a database
    Applying changes to a database
  3. Finally, select an entire row on the left-most column and click Delete on your keyboard. Click the Apply Changes to Database button. Exit and re-run the application and notice that the row was deleted.

How it works

In the above sample, we used design-time data binding of a database table to the data grid control that is provided by Visual Studio.Net.

We then changed the values in the data grid control manually. This changed the values in the underlying data adapter object.

When we clicked Apply Changes to Database, the data adapter update method was called (code added in Step 6-2). This performed database update, insert and delete operations on the database based on the changes made in the data grid.


Retrieving and updating BLOBs and CLOBs

Now we will add windows controls to display the photo and resume of the employee to the form that we've already built. We will use IBM Explorer to create a data binding to the EMP_PHOTO and EMP_RESUME tables. Finally, we will add some C# code that is required to display and update employee photo and resume. (See the attached sample db2lobs.zip for the complete code.)

Follow the steps listed below:

  1. Open the db2lobs project that was created.
  2. Drop a picture box control and a text box control onto the form. Change the name property for the picture box to photo and change the name property for the text box to resume. We will use these to display an employee's photo and resume, respectively. Add an additional text box to get a filename from the user to update the employee photo.
  3. Drag and drop the EMP_PHOTO and EMP_RESUME tables from IBM Explorer onto the form. This will create two pre-configured components (Figure 6):
    • db2DataAdapter2 - With SELECT, INSERT, UPDATE, and DELETE statements set for the EMP_PHOTO table.
    • db2DataAdapter3 - With SELECT,INSERT, UPDATE and DELETE statements set for the EMP_RESUME table.
    Figure 6. db2DataAdapter2 and db2DataAdapter3
    db2DataAdapter2 and db2DataAdapter3
  4. Click on db2DataAdapter2 and click Generate DataSet. Select New: Db2DataSet2 and click OK.

    Click on db2DataAdapter3 and click Generate DataSet. Select New: Db2DataSet3 and click OK.

  5. Now we are ready to add some C# code to this form to add data binding for the controls on the form. Double click on the form. This will create the instances of the dropped objects into Form1.cs and display Form1.cs code.
    1. Add the following references and private variables in Form1.cs class:
      using System.Drawing.Imaging; 
      using System.IO; 
       
      private int currRow = 0; 
      private Byte[] byteBLOBData;
    2. In the Form1_Load method, add the following code (listed in bold). This will create an event handler for the data grid's Click event. We will use this event to identify the current row selected in the data grid.

      The EMP_PHOTO and EMP_RESUME tables contain a subset of entries from the EMPLOYEES table. To display the subset, we will change the default generated select statement.

      private void Form1_Load(object sender, System.EventArgs e) 
      { 
           try{ 
      			 
      	  this.db2SelectCommand1.CommandText = "SELECT  
      	  \"EMPNO\", \"FIRSTNME\", \"MIDINIT\",  
      		\"LASTNAME\", \"WORKDEPT\", \"PHONENO\", 
      		\"HIREDATE\", \"JOB\", \"EDLEVEL\", \"SEX\", 
      		\"BIRTHDATE\", \"SALARY\", \"BONUS\",  
      		\"COMM\" FROM \"ADMIN" + 
      		"\".\"EMPLOYEE\" where \"EMPNO\" IN 
      	('000130','000140','000150','000190')"; 
       
      	this.db2DataAdapter1.Fill.(this.db2DataSet11); 
       
      	this.dataGrid_employee.Click += new
      	System.EventHandler(this.dataGrid_employee_Click); 
       
      	this.db2DataAdapter2.Fill(this.db2DataSet21); 
      	this.db2DataAdapter3.Fill(this.db2DataSet31); 
       
      	displayPict(); 
      	displayResume(); 
      } 
      catch 
      { 
      	// error handling 
      } 
          }

      Add the following code to create the click event handler method for the data Grid control. This will display the employee photo and resume.

      private void dataGrid_employee_Click
            (object sender, System.EventArgs e) 
      		{ 
      			currRow = dataGrid_employee.
      			CurrentCell.RowNumber; 
      			displayPict(); 
      			displayResume(); 
      		}
    3. Every employee entry in the EMPLOYEE table contains three corresponding entries in the EMP_PHOTO table. We will use the value current row*3 to display the first employee photo in the Windows picture box control for the employee that is selected in the data grid.

      Similarly, every employee entry in EMPLOYEE table contains two corresponding entries in the EMP_RESUME table. We will use the value current row*2 to display the first employee resume in the Windows edit box control for the employee that is selected in the data grid.

      Add the following private function to display the photo. Note that to save the photo as a file on the file system, uncomment the commented code.

      private void displayPict() 
      	{ 
      	     try 
      	     { 
      		byteBLOBData = 
      		Byte[])(this.db2DataSet21.Tables["EMP_PHOTO"]. 
      		Rows[currRow*3]["PICTURE"]); 
      		MemoryStream stmBLOBData = new
      		MemoryStream(byteBLOBData); 
      		photo.Image= Image.FromStream(stmBLOBData); 
       
      // Optionally to save this image to a file 
                            uncomment the following code 
      //FileStream fs = new FileStream(currRow.ToString()+".jpg",
                                       FileMode.CreateNew); 
      //stmBLOBData.WriteTo(fs);} 
      	      catch ( Exception exp) 
      	      { 
      			// error handling 
      	       } 
      	  }

      Add the following private function to display the resume.

      private void	displayResume() 
      	{ 
      		try 
      		{ 
      			this.resume.Text = string
      			this.db2DataSet31.Tables["EMP_RESUME"]. 
      				Rows[currRow*2]["RESUME"]; 
      		} 
      		catch ( Exception exp) 
      		{ 
      			// error handling 
      		} 
      	}
    4. Finally, we will add code to for special handling required for BLOB and CLOB updates. We will use the predefined DB2 command objects that are created from the IBM Explorer drag and drop operation. We will modify these objects for the special handling required for updates and retrievals of BLOBs and CLOBs. (See applyChangesToDb_Click in db2lobs.zip.)

Testing the employee sample

  1. To build and run the application, click Debug -> Start Without Debugging from the Visual Studio .Net menu. Notice that the grid displays values from the EMPLOPYEE table, and the picture and text box display the employee's photo and resume (See Figure 7).
    Figure 7. Employee table
    Employee table
  2. We will now update the resume and picture. Simply change the resume text in the text box and type a filename for the new photo. Click Apply Changes to Database. Exit and re-run your application and observe the updated results from the database (Figure 8).
    Figure 8. The database is now updated
    The database is now updated

How it works

DB2Command objects created from the drag and drop operations are modified to perform BLOB and CLOB updates.

To display the picture in the picture box, a MemoryStream object is created from the "picture" field of the dataset representing the EMP_PHOTO table. The picture box's image property is set to this memory stream. This displays the photo in the picture box. This was accomplished by displayPict() code added in Step 5-3.

DB2UpdateCommand2 is the pre-configured update command generated for the EMP_PHOTO table. DB2UpdateCommand_photo is a copy of this command with the original photo parameter removed. This achieves optimal update of the BLOB table, because the BLOB is not part of the primary key.

BLOB data to be updated is read from a file specified and converted to a byte array. This value is set to the "picture" parameter of the DB2UpdateCommand_photo object. The executeNonQuery method on the DB2Command object updates the database with the new value. (See code in applyChangesToDb_Click in db2lobs.zip.)

To display resume in a multi-line text box, the text box Text property is set to the "resume" field of the dataset representing the EMP_RESUME table. This displays the resume in the text box. This was accomplished by code added in Step 5-3.

DB2UpdateCommand3 is the pre-configured update command generated for the EMP_RESUME table. Similar to the blob update command, DB2UpdateCommand_resume is a copy of this command with the original resume parameter removed. This achieves optimal update of the CLOB table, because the CLOB is not part of the primary key.

CLOB data to be updated is read from the resume text box. This value is set to the "resume" parameter of the DB2UpdateCommand_resume object. The executeNonQuery method of the DB2Command object updates the database with the new value. (See code in applyChangesToDb_Click in db2lobs.zip)


Wrapping up

We used IBM Explorer to add DB2 connections and browse the database contents. We took an introductory look how IBM Explorer retrieves schema information asynchronously and displays status messages in the IBM DB2 Message pane. We looked at how you can change the options so that data is retrieved from the data source only when you click on the folder.

Using IBM Explorer, we created ADO.Net data-bound applications. We performed database operations on a simple table in and then extended the application to successfully retrieve and update BLOBs and CLOBs.


Notices

IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not give you any license to these patents. You can send license inquiries, in writing, to:

IBM Director of Licensing
IBM Corporation
North Castle Drive Armonk, NY 10504-1785
U.S.A.

All statements regarding IBM's future direction or intent are subject to change or withdrawal without notice, and represent goals and objectives only.


Download

DescriptionNameSize
Code sampledb2lobs.zip92 KB

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13787
ArticleTitle=Creating and Linking LOBs in a Data Application for Microsoft .Net using DB2 Universal Database
publish-date=04242003