Working with Image Objects in InfoPath 2003 and SQL Server 2000

Summary: Learn how to use features in Microsoft Office InfoPath 2003 Service Pack (SP) 1 to work more efficiently with image objects and other data. Learn how to use BLOB data types to work with data stored in a Microsoft SQL Server 2000 database from an InfoPath form. InfoPath 2003 provides a robust and rich interface to store and access data in SQL Server databases. Learn about best practices for integrating SQL Server databases and InfoPath form data, with an illustrative example. (7 printed pages)

Anup Kafle, 3Sharp

September 2004

Applies to: Microsoft Office InfoPath 2003, Microsoft SQL Server 2000

Contents

Introduction to Image Objects

Microsoft Office InfoPath 2003 Service Pack (SP) 1 provides a robust and rich interface to store and access data in Microsoft SQL Server 2000 databases. Just as SQL Server is capable of storing many data types, InfoPath forms can handle a similar range of data types, including binary objects. In SQL Server, these objects are often referred to as binary large objects (BLOB). The BLOB data type includes image, text, and ntext data types. With the information and examples in this article, you can learn about best practices for integrating SQL Server databases and InfoPath form data, including image objects, using BLOB data types.

Imagine a simple scenario in which you are setting up a product catalog in a SQL Server database and using an InfoPath form for data entry. You want to include a product image and a long text description in the catalog. The main tasks you want to do are:

  1. Setting up the SQL Server database.

  2. Setting up the Web service.

  3. Creating the InfoPath form and connecting it to the Web service.

Setting Up the SQL Server Database

You must first create a database table to accommodate the required product information. To include product images in the database, you must add an image field. Because you also want to store detailed product descriptions, you add a text field that can store long strings of text instead of the more limited variable-length character (varchar) field that does not accept data over 8 kilobytes (KB) in size.

Working with BLOB Data Types

BLOB data types permit the storage and retrieval of up to 2 gigabytes (GB) of data in SQL Server. Other character-based data types, such as varchar, can only store up to 8,000 bytes of data. Hence, BLOB data is essential for storing long strings of text, as well as any kind of binary objects like pictures and media files, through InfoPath. The size of such data, however, can severely hamper network performance when large queries are run. Also, only a limited number of SELECT and UPDATE query operations can be performed on BLOB data. Despite these limitations, BLOB data is used to include picture data in the database table in the example, because it is still the most practical solution.

Alternatively, you could store the pictures in an external file system and include a pointer to the file in the database table. Storing the pictures separately introduces other problems, however, such as having to manage two different destinations (database table and file system) for a single row of data. You may also encounter situations in which the user has access to the database only and not to the file system, or vice versa. These problems can result in inconsistent data, because no native relationship exists between the SQL Server database table and the external file system.

Another alternative is to store images within the InfoPath form, because InfoPath allows picture items (as base64Binary) to be embedded with the form. This solution is impractical, though, because in the example, you are using the InfoPath form merely as a data entry tool. A third alternative is to save the entire form to a file system location where it is processed later by a custom program that extracts the picture and text data. This alternative, while possible, is beyond the scope of this article.

Setting Up the Database Table

The first step in setting up the SQL Server database is to set up the database table to contain the BLOB data. The structure of the table and the data types of its columns dictate the design of both the Web service and the InfoPath form. In the example, the table contains the product catalog information.

To set up the database table

  1. In the SQL Server Enterprise Manager, expand the database tree view to reveal all tables in the destination database.

  2. On the Actions menu, click New Table.

  3. Fill in the new table information as follows:

    Column Name Data Type Length Allow Nulls

    ItemId

    int

    4

    No

    Notes

    text

    4

    Yes

    ItemImage

    image

    16

    Yes

  4. Set the Identity property of the ItemId column to Yes.

  5. Save the table with the name, myBlobTable.

Setting Up the Web Service

InfoPath provides multiple ways of communicating with the database: through a direct database connection, a Web service, or a custom InfoPath form script, or by rules that are bound to specific form controls (Figure 1). The direct database connection and Web service methods are submit adapters, whereas custom script and rules are submit mechanisms. For more information on adapters and mechanisms, see Submitting Forms in InfoPath 2003.

The use of a Web service is preferred because this option keeps the InfoPath form separate from the middle-tier Web service application that may include business rules and security checks. A Web service also provides the option to use sophisticated ADO.NET technologies, such as datasets and data adapters, that form a seamless integration between SQL Server and InfoPath. For example, converting a complex data object such as a picture from InfoPath (Base64 data type) to SQL Server (image data type) can be facilitated by the use of an ADO.NET dataset.

Figure 1. Multiple ways to submit data from InfoPath to SQL Server


Multiple ways to submit data to SQL Server

In the example, a custom Web service acts as a bridge between the form and the database, controlling both data submittal and data retrieval operations. The Web service can also verify data for integrity and consistency before inserting it into the database. At a minimum, the Web service should have two methods that are accessible from the InfoPath form: one to handle the submit process and another to handle the data retrieval process. The Web service accesses the database through ADO.NET, which manages database connections and queries, including SELECT and UPDATE operations. The data handled by ADO.NET is in the form of a DataSet object that InfoPath understands natively.

Creating the Web Service

To create the Web service using Microsoft Visual Studio .NET 2003, start a new ASP.NET Web Service project and add two key ADO.NET objects to the project: a SQLDataAdapter and a DataSet. For a step-by-step guide, see Lab 9: ADO.NET DataSets in InfoPath 2003. You can keep the default names of the ADO.NET objects. Assume that the Web service is published to the following location:

http://localhost/blobdata/service1.asmx.

With the ADO.NET objects in place and a connection to the SQL Server database established, the next step is to add the two Web service methods for retrieving and submitting data. Note that the data is handled as an ADO.NET dataset in both instances. The ADO.NET connection wizard creates the XML schema for the dataset automatically (Figure 2). To view the schema, in the Solution Explorer pane, double-click DataSet1.xsd. Note that the Notes and ItemImage columns that were text and image data types in SQL Server are now mapped as string and base64Binary data types, respectively.

Figure 2. Dataset XML schema created by ADO.NET connection wizard


Dataset XML schema

The following code example shows a Web service method that exposes the dataset for retrieval by the InfoPath form. The SqlDataAdapter1 and DataSet11 variables in this example represent SQLDataAdapter and DataSet objects, respectively.

<WebMethod()> _
Public Function ReceiveDataSet() As DataSet
    'Get the data from database and put it in the DataSet
    SqlDataAdapter1.Fill(DataSet11)
    'Provide the DataSet object to the client
    Return DataSet11
End Function

Similarly, the following code example is the Web service method that consumes the dataset when InfoPath submits form data.

<WebMethod()> _
Public Function SubmitDataSet(ByVal myDataSet As DataSet)
    'Pass the dataset to database via ADO.NET
    SqlDataAdapter1.Update(myDataSet)
End Function

Once you create the Web service methods, you can build the solution. The Web service is available in the URL specified previously with two methods: ReceiveDataSet and SubmitDataSet.

Setting Up the InfoPath Form

InfoPath provides a number of controls that you can use for data input. Two such controls, picture control and text box control, are used in the example. The picture control allows an easy point-and-click operation to insert images in the form. The control shows a preview of the attached image after it is inserted in the form.

Because you already set up the database and Web service, you can use the InfoPath Data Connection Wizard to design the form. This way, you create the appropriate form controls automatically to match the dataset managed by the Web service.

Connecting to the Web Service

The following steps show you how to set up a new InfoPath form and connect to the Web service you created previously for data retrieval and submittal.

To create a form and connect to the Web service

  1. Open InfoPath, and in the task pane, click Design a Form.

  2. Click New from Data Connection.

  3. In the Data Connection Wizard, for the type of data connection, select Web service, and click Next.

  4. To specify how the form works with the Web service, select Receive and submit data, and click Next.

  5. Enter the location of the Web service created earlier (for example, "http://localhost/blobdata/service1.asmx?WSDL"), and then click Next.

  6. Keep the default name for the data connection used to receive data, and click Next.

  7. From the list of operations available from the Web service, select GetRecords, and then click Next.

  8. The submit data location is filled in for you automatically. It is the same address used in Step 5. Keep this default value, and click Next.

  9. From the list of operations, select SubmitData, and click Next.

  10. In the Parameters list page of the wizard, double-click the name of the dataset listed (for example, s0:myDataSet). This represents the dataset parameter that the Web service expects to receive.

  11. In the Select a Field or Group dialog box, expand the tree view under dataFields until you see the item ns1:DataSet1.

  12. Select ns1:DataSet1 and click OK.

  13. In the Data Connection Wizard window, click Next.

  14. Keep the default name for the data connection for submitting data, and click Finish.

You now have a new InfoPath form template as shown in Figure 3. The form looks mostly empty, but it already has all the necessary connections to the back-end services (Web service and database). It also has placeholders for data input and query fields, which you add in the next step.

Figure 3. Blank InfoPath form template with Web service data connection


Blank form template with Web service connection

Designing the Form

In this step, you set up the form user interface to display fields and controls by dragging and dropping from the task pane to the form.

To display fields and controls in the form

  1. Switch the InfoPath task pane view to Data Source.

  2. Expand the dataFields group tree view to reveal all levels.

  3. Drag the group named myBlobTable to the form area labeled Drag data fields here.

  4. Select Repeating Section with Controls in the list of options.

This procedure adds three fields to the form, each corresponding to a column in the dataset (which in turn corresponds to each column in the SQL Server database table). When a user submits form data, values from these fields are sent to the Web service collectively as a dataset.

The box labeled Drag query fields here is for adding parameters to the retrieve data method. Because the ReceiveDataSet Web service method does not accept any parameters, you can discard or delete this area of the form. You can rearrange or resize other elements in the form to suit your needs.

Using the Form

The form design is now complete. To preview the form for testing, on the standard toolbar, click Preview Form. To review products and images already in the database, click Run Query. This action invokes a SELECT query on the database through the Web service and returns all matching product records to the form. You can add a new record to the database by using the following procedure.

To add a new record to the database

  1. In the Notes field, type in a text description of the new product.

  2. Click the control labeled Click here to insert a picture.

  3. Point to a picture file in the file system and click Insert.

  4. In the standard toolbar, click Submit.

Conclusion

The information and example presented here showed you how to use Microsoft SQL Server 2000 to store picture and text data fields from an InfoPath form. You saw how to work with BLOB data to manage large binary files. You can store any types of data fields that incorporate binary data, such as media files, in SQL Server by using this process. Furthermore, you can expand the Web service methods to handle more data operations, such as updating and deleting records, by following the example given here.

Additional Resources

Show: