Storing Ink in a Database

  • Article

 

Peter Gruenbaum
Microsoft Corporation

July 2004

Applies to:
   Microsoft® Tablet PC Platform SDK
   Ink
   Microsoft SQL Server™

Summary: Describes how to store ink in a Microsoft SQL Server database and retrieve it. An application that runs on Microsoft Windows® XP Tablet PC Edition can store ink in a database and later retrieve ink that satisfies some search criteria. These descriptions and examples—in C# and Microsoft Visual Basic .NET—use the Microsoft Tablet PC Platform SDK version 1.7 API, currently in Beta, although nearly all the content applies to version 1.5 as well. Readers should be familiar with the Microsoft Tablet PC Platform SDK, SQL, and managed code. For information about joining the Tablet PC Beta partners, send an e-mail to tabbeta@microsoft.com. (15 printed pages)

Download the example code

Contents

Introduction
How to Use the Example
Database Design Details
Using the Database
Adding Ink to the Database
Searching for Ink
Displaying the Ink
Conclusions

Introduction

If you are collecting ink that you would like to retrieve at a later time, you may want to save it in a database. Databases provide a way to quickly retrieve data that matches certain criteria. With a database, you can save both the ink as well as data about the ink that is easily searchable. You use the Ink.Save method to turn the ink into an array of bytes, and you use the Ink.Load method to recreate ink from the data. The most efficient format in which to store the ink is Ink Serialized Format (ISF), but if you store the ink in fortified GIF persistence format then you can use the data to show ink in applications that are not ink-enabled. By using ADO.NET (SqlCommand objects in particular) you can perform the search on the server side, and thereby reduce the amount of ink data transferred from the database to the client.

This article makes heavy use of an example project that shows how you could collect ink on an InkPicture object, store it in a database, and then retrieve only the ink that has strokes in a certain region of the image.

This article discusses the following:

  • Creating a SQL Server database to store ink and searchable data.
  • Storing ink in fortified GIF persistence format.
  • Using ADO.NET to add ink and to search for ink.
  • Retrieving and displaying the ink.

This article does not discuss the following:

  • The various ADO.NET methods that you can use to interact with your database.
  • Details about how to set up a search based on recognition alternates.
  • Basic use of database technology.

How to Use the Example

Note   The example requires the Microsoft Tablet PC Platform SDK version 1.7 and either Microsoft SQL Server 2000 or Microsoft SQL Server 2000 Desktop Engine (MSDE 2000). Example projects are provided in C# and Visual Basic® .NET.

The purpose of the example is to show how you could create a database of ink that corresponds to sketches of damage marks on an automobile. For simplicity, it is a Windows Forms application, although you could easily apply the same techniques to Web applications. You can draw on an image of the car and then add those marks to the database, and you can also search the database for particular marks. Searching with no ink on the car will fill a list box with all the ink in the database. Circling a part of the car and searching will fill the list box with ink marks near the search area. Once the ink is retrieved, you have the option of having it drawn onto the image of the car or displayed as a GIF file by itself.

Note   The example demonstrates how to search for ink on the server side. This means that only ink that matches the search criterion is actually loaded from the database.

To use the example, follow these steps:

  1. Run the script called InkInDb.sql to generate the database called InkDb, containing the tables and the stored procedure. The script can be run using the SQL Query Analyzer. (The script assumes that your SQL Server data is kept in C:\Program Files\Microsoft SQL Server\MSSQL\data\. If it's not, you will have to edit the file.)
  2. Open the Visual Studio project, build the project, and then run the project.
  3. Draw a mark on the windshield of the car and click Add.
  4. Draw another mark near the rear tire and click Add.
  5. Draw a mark on the windshield and another mark near the rear tire and click Add again. You have now added three records to the database.
  6. Click Search. This should show you all three records.
  7. Now circle the windshield and click Search again. This should show you any ink marks on the windshield. You can do the same for the rear tire area.
  8. Check the Show strokes only checkbox and click Search to see the strokes as they are stored as an image.
  9. Click Delete all if you choose to clear the database and start over.

Database Design Details

If your application is going to have ink in a database that can be searched on the server side, then you will need to store both the ink and some searchable data about the ink. In this example, the database has two tables: one stores the ink, and the other stores the bounding boxes of each stroke in the ink. Two database tables allow for multiple stroke bounding boxes for each ink object. If you are searching on data where there is only one value for each ink object, then you may choose to have that data in the same table as the ink. The table with the ink contains a unique identifier, the image data for the ink, and the length of the data's byte array. The bounding box table contains a unique integer, the identifier of the ink to which the stroke belongs, and the left, top, right, and bottom values for the bounding box. The example stores the data as a fortified GIF, so use an image type for the ink data, unique identifier types for the ink identifier, and integer types for length, stroke identifier, and the bounding box data.

You can use a stored procedure to perform the search and return the data for the ink. In the example, the stored procedure is called ink_in_bounds, and the SQL query is shown in the code sample that follows. The comparison between the parameters for the bounding box to search over (leftSearch, topSearch, rightSearch, and bottomSearch), and the database columns for the bounding box of each stroke (leftBound, topBound, rightBound, and bottomBound) result in selecting all rows where the two bounding boxes intersect. The logic of the comparison works as follows: if the database bounding box is either completely to the left, right, top, or bottom of the bounding box being passed in, then an intersection does not occur. It assumes that the bounding rectangles are not inverted, which means that the left is always a lower value than the right and the top is always a lower value than the bottom.

SQL

CREATE PROCEDURE ink_in_bounds 
  @leftSearch int,
  @topSearch int,
  @rightSearch int,
  @bottomSearch int
 AS
SELECT Ink, Length FROM ink WHERE ID IN 
  (SELECT InkID FROM strokeBounds WHERE
    NOT (rightBound < @leftSearch OR
         leftBound > @rightSearch OR 
         bottomBound < @topSearch OR 
         topBound > @bottomSearch))
GO

Using the Database

Visual Studio.NET contains a graphical way to create ADO.NET classes for a SQL Server database. To do so, open the Server Explorer and navigate to your tables. Drag them onto your form in Design mode. This will create a SqlConnection object for the database and a SqlDataAdapter object for each table. It will also create some SqlCommand objects, although they are not visible in Design mode.

You can use ADO.NET in several ways to interact with your database. This example primarily uses SqlConnection objects to add records, delete records, and call a stored procedure. For the most part, create your own SqlConnection objects, but to add records, it is convenient to use the SqlConnection objects that are automatically created in Design mode.

Note   If you create the ADO.NET classes in Design mode and then you make changes to your database table designs, you will need to remove and re-create the classes.

Adding Ink to the Database

To add an Ink object to the database, you first need to convert it into an array of bytes with the Ink.Save method. The Save method can take a PersistenceFormat parameter, which indicates in what format the data will be saved. The options are InkSerializedFormat, Gif, Base64InkSerializedFormat, and Base64Gif. Ink Serialized Format (ISF) is the most compact representation of the ink. Gif is an image format (Graphics Interchange Format) that contains ISF data embedded as metadata, called fortified GIF. This format allows applications that are not ink-enabled to use the ink, but also allows you to obtain the ink if necessary. The Base64 formats are provided so ink can be encoded directly in an Extensible Markup Language (XML) or HTML file. The example saves the data in Gif format and shows how you can use the data to create an Image object or to create an Ink object.

Once ink is stored as a binary array, it is impossible to get any information about it without first turning it back into ink. Therefore, if you want to search through the ink, you should store useful metadata in the database that you can use for the search. The example stores the bounding box of each stroke so that you can then search the ink by location. In addition to the bounding box, the example stores a Guid object that allows you to find the original ink that contains the stroke. If you were storing handwriting, then you might want to perform recognition on the strokes before storing them, and then store recognition alternates in the database for searching purposes.

The following code sample illustrates what happens in the example when you click Add. The automatically-generated SqlCommand objects for insertion are filled with the appropriate data, and then the SqlCommand.ExecuteNonQuery method is called.

C#

sqlConnectionInk.Open();
try
{
    // Take the ink in the ink picture and put it in the database.
    byte[] inkData = this.inkPictVehicle.Ink.Save(PersistenceFormat.Gif);
    Guid inkId = Guid.NewGuid();
    this.sqlInsertCommand1.Parameters["@ID"].Value = inkId;
    this.sqlInsertCommand1.Parameters["@Ink"].Value = inkData;
    this.sqlInsertCommand1.Parameters["@Length"].Value = inkData.Length;
    this.sqlInsertCommand1.ExecuteNonQuery();

    // Add the bounding box data of each stroke to the database.
    foreach (Stroke stroke in this.inkPictVehicle.Ink.Strokes)
    {
         Rectangle bounds = stroke.GetBoundingBox();
         this.sqlInsertCommand2.Parameters["@InkID"].Value = inkId;
         this.sqlInsertCommand2.Parameters["@LeftBound"].Value = 
             bounds.Left;
         this.sqlInsertCommand2.Parameters["@TopBound"].Value = 
             bounds.Top;
         this.sqlInsertCommand2.Parameters["@RightBound"].Value = 
             bounds.Right;
         this.sqlInsertCommand2.Parameters["@BottomBound"].Value = 
             bounds.Bottom;
         this.sqlInsertCommand2.ExecuteNonQuery();
     }
}
catch (Exception ex)
{
    // For this example, simply show the exception.
    MessageBox.Show(ex.ToString());
}
finally 
{
    // Always call Close when done reading.
    sqlConnectionInk.Close();
}

Visual Basic .NET

SqlConnectionInk.Open()
Try
    ' Take the ink in the ink picture and put it in the database.
    Dim inkData As Byte() = Me.InkPictVehicle.Ink.Save(PersistenceFormat.Gif)
    Dim inkId As Guid = Guid.NewGuid()
    Me.SqlInsertCommand1.Parameters("@ID").Value = inkId
    Me.SqlInsertCommand1.Parameters("@Ink").Value = inkData
    Me.SqlInsertCommand1.Parameters("@Length").Value = inkData.Length
    Me.SqlInsertCommand1.ExecuteNonQuery()

    ' Add the bounding box data of each stroke to the database.
    Dim stroke As Stroke
    For Each stroke In Me.InkPictVehicle.Ink.Strokes
        Dim bounds As Rectangle = stroke.GetBoundingBox()
        Me.SqlInsertCommand2.Parameters("@InkID").Value = inkId
        Me.SqlInsertCommand2.Parameters("@LeftBound").Value = bounds.Left
        Me.SqlInsertCommand2.Parameters("@TopBound").Value = bounds.Top
        Me.SqlInsertCommand2.Parameters("@RightBound").Value = bounds.Right
        Me.SqlInsertCommand2.Parameters("@BottomBound").Value = bounds.Bottom
        Me.SqlInsertCommand2.ExecuteNonQuery()
    Next stroke
Catch ex As Exception
    ' For this example, simply show the exception.
    MessageBox.Show(ex.ToString())
Finally
' Always call Close when done reading.
    SqlConnectionInk.Close()
End Try

Searching for Ink

To retrieve the ink data from the database, you can use the stored procedure described in the Using the Database section. The following code sample shows how to set up a SqlCommand object to do this in the form's constructor.

C#

this.sqlSearchInkCommand = 
    new SqlCommand("EXECUTE ink_in_bounds @Left, @Top, @Right, @Bottom", 
    this.sqlConnectionInk);
this.sqlSearchInkCommand.Parameters.Add(
    new System.Data.SqlClient.SqlParameter("@Left", 
    System.Data.SqlDbType.Int, 4, "leftSearch"));
this.sqlSearchInkCommand.Parameters.Add(
    new System.Data.SqlClient.SqlParameter("@Top", 
    System.Data.SqlDbType.Int, 4, "topSearch"));
this.sqlSearchInkCommand.Parameters.Add(
    new System.Data.SqlClient.SqlParameter("@Right", 
    System.Data.SqlDbType.Int, 4, "rightSearch"));
this.sqlSearchInkCommand.Parameters.Add(
    new System.Data.SqlClient.SqlParameter("@Bottom", 
    System.Data.SqlDbType.Int, 4, "BottomSearch"));

Visual Basic .NET

Me.SqlSearchInkCommand = New SqlCommand("EXECUTE ink_in_bounds @Left, _
    @Top, @Right, @Bottom", Me.SqlConnectionInk)
Me.SqlSearchInkCommand.Parameters.Add( _
    New System.Data.SqlClient.SqlParameter("@Left", _
    System.Data.SqlDbType.Int, 4, "leftSearch"))
Me.SqlSearchInkCommand.Parameters.Add( _
    New System.Data.SqlClient.SqlParameter("@Top", _
    System.Data.SqlDbType.Int, 4, "topSearch"))
Me.SqlSearchInkCommand.Parameters.Add( _
    New System.Data.SqlClient.SqlParameter("@Right", _
    System.Data.SqlDbType.Int, 4, "rightSearch"))
Me.SqlSearchInkCommand.Parameters.Add( _
    New System.Data.SqlClient.SqlParameter("@Bottom", _
    System.Data.SqlDbType.Int, 4, "BottomSearch"))

If there are no strokes in the current ink, you can just use a query that returns all the data from the ink table. Otherwise, set the parameters with the bounding box of the current ink in the InkPicture object. Using the SqlCommand.ExecuteReader method, you can first read the length of the byte array, and then use the SqlDataReader.GetBytes method to obtain the array of bytes. The following code sample shows how to do this.

C#

// Get the bounding box of the ink for searching.
Rectangle searchBounds = this.inkPictVehicle.Ink.GetBoundingBox();

// Get the ink data from the database.
SqlCommand inkCommand;
if (this.inkPictVehicle.Ink.Strokes.Count == 0)
{
    // If no strokes for searching, then get all ink from the database.
    inkCommand = new SqlCommand("SELECT Ink, Length FROM ink", 
        this.sqlConnectionInk);
}
else
{
    // Use the stored procedure for getting ink from bounds.
    inkCommand = this.sqlSearchInkCommand;
    inkCommand.Parameters["@Left"].Value = searchBounds.Left;
    inkCommand.Parameters["@Top"].Value = searchBounds.Top;
    inkCommand.Parameters["@Right"].Value = searchBounds.Right;
    inkCommand.Parameters["@Bottom"].Value = searchBounds.Bottom;
}

// Gather the ink data.
ArrayList inkDataList = new ArrayList();
sqlConnectionInk.Open();
try
{
    using (SqlDataReader reader = inkCommand.ExecuteReader())
    {
        while (reader.Read())
        {
            // First get the length of the data.
            int dataLength = reader.GetInt32(1);
            // Next, get the data itself.
            byte[] inkData = new byte[dataLength];
            long obtainedLength = reader.GetBytes(0, 0, inkData,
                0, dataLength);
            if (obtainedLength != dataLength)
            {
                 MessageBox.Show("There may be a problem with the ink data.");
            }
             inkDataList.Add(inkData);
        }
    }
}
catch (Exception ex)
{
    // For this example, simply show the exception.
    MessageBox.Show(ex.ToString());
}
finally 
{
    // Always call Close when done reading.
    sqlConnectionInk.Close();
}

Visual Basic.NET

' Get the bounding box of the ink for searching.
Dim searchBounds As Rectangle = Me.InkPictVehicle.Ink.GetBoundingBox()

' Get the ink data from the database.
Dim inkCommand As SqlCommand
If Me.InkPictVehicle.Ink.Strokes.Count = 0 Then
    ' If no strokes for searching, then get all ink from the database.
    inkCommand = New SqlCommand("SELECT Ink, Length FROM ink", Me.SqlConnectionInk)
Else
    ' Use the stored procedure for getting ink from bounds.
    inkCommand = Me.SqlSearchInkCommand
    inkCommand.Parameters("@Left").Value = searchBounds.Left
    inkCommand.Parameters("@Top").Value = searchBounds.Top
    inkCommand.Parameters("@Right").Value = searchBounds.Right
    inkCommand.Parameters("@Bottom").Value = searchBounds.Bottom
End If

' Gather the ink data.
Dim inkDataList As New ArrayList
Me.SqlConnectionInk.Open()
Try
    Dim reader As SqlDataReader = inkCommand.ExecuteReader()
    Try
    While reader.Read()
        ' First get the length of the data.
        Dim dataLength As Integer = reader.GetInt32(1)
        ' Next, get the data itself.
        Dim inkData(dataLength) As Byte
        Dim obtainedLength As Long = reader.GetBytes(0, 0, inkData, 0, dataLength)
        If obtainedLength <> dataLength Then
            MessageBox.Show("There may be a problem with the ink data.")
        End If
        inkDataList.Add(inkData)
    End While
Finally
    reader.Close()
End Try
Catch ex As Exception
    ' For this example, simply show the exception.
MessageBox.Show(ex.ToString())
Finally
    ' Always call Close when done reading.
    Me.SqlConnectionInk.Close()
End Try

To view the data as a fortified GIF, you can put the data into a MemoryStream object and then use the Image.FromStream method to create an Image object. To convert the data into an Ink object, use the Ink.Load method, which will work for any PersistenceFormat parameter that you chose when calling the Ink.Save method.

Note   In order to use the Ink.Load method, you need to start with a new Ink object with no strokes. Otherwise, an exception is thrown.

The following code sample shows how to create the Image objects, and load them into a ListView object. The CreateImageWithInk method is described in the next section.

C#

// Turn the data into images and add them to the image list.
for (int i = 0; i < inkDataList.Count; i++)
{
    byte[] inkData = (byte[])inkDataList[i];
    if (this.checkBoxInkOnly.Checked)
    {
        // Convert the data into an image.
        using (MemoryStream stream = new MemoryStream(inkData))
        {
            Image inkImage = Image.FromStream(stream);
            inkList.Images.Add(inkImage);
            this.listViewSearchResults.Items.Add("", i);
        }
    }
    else
    {
        Ink tempInk = new Ink();
        tempInk.Load(inkData);
        inkList.Images.Add(this.CreateImageWithInk(tempInk));
        this.listViewSearchResults.Items.Add("", i);
    }
}

// Set list view's image list.
this.listViewSearchResults.LargeImageList = inkList;
this.listViewSearchResults.View = View.LargeIcon;

Visual Basic .NET

' Turn the data into images and add them to the image list.
Dim i As Integer
For i = 0 To inkDataList.Count - 1
    Dim inkData As Byte() = CType(inkDataList(i), Byte())
    If Me.CheckBoxInkOnly.Checked Then
        ' Convert the data into an image.
        Dim stream As New MemoryStream(inkData)
        Try
            Dim inkImage As Image = Image.FromStream(stream)
            inkList.Images.Add(inkImage)
            Me.ListViewSearchResults.Items.Add("", i)
        Finally
            stream.Close()
        End Try
    Else
        Dim tempInk As New Ink
        tempInk.Load(inkData)
        inkList.Images.Add(Me.CreateImageWithInk(tempInk))
        Me.ListViewSearchResults.Items.Add("", i)
    End If
Next i

' Set the list view's image list.
Me.ListViewSearchResults.LargeImageList = inkList
Me.ListViewSearchResults.View = View.LargeIcon

Displaying the Ink

If you have stored the ink as a fortified GIF, you can simply create an Image object and display it. If you recreate an Ink object instead, you can use the Renderer.Draw method to draw the ink. If you want the user to be able to modify the ink, you can set the Ink property on an InkOverlay object or InkPicture object.

The following sample code demonstrates a method called CreateImageWithInk that draws the ink over the InkPicture object's Image object and creates a new Image object.

C#

private Image CreateImageWithInk(Ink inkToDraw)
{
    // Create an image the size of the InkPicture.
    Image imageWithInk = 
        new Bitmap(this.inkPictVehicle.Image, this.inkPictVehicle.Size);

    // Draw the ink onto it.
    Graphics graphicsImage = Graphics.FromImage(imageWithInk);
    Renderer renderer = new Renderer();
    renderer.Draw(graphicsImage, inkToDraw.Strokes);
    graphicsImage.Dispose();

    return imageWithInk;
}

Visual Basic .NET

Private Function CreateImageWithInk(ByVal inkToDraw As Ink) As Image
    ' Create an image the size of the InkPicture.
    Dim imageWithInk = New Bitmap(Me.InkPictVehicle.Image, _
        Me.InkPictVehicle.Size)

    ' Draw the ink onto it.
    Dim graphicsImage As Graphics = Graphics.FromImage(imageWithInk)
    Dim renderer As New Renderer
    renderer.Draw(graphicsImage, inkToDraw.Strokes)
    graphicsImage.Dispose()

    Return imageWithInk
End Function

Conclusions

This article described how to store ink in a database and then retrieve it. A summary of the points of the article follows.

  • You can store the data in an array of bytes using the Ink.Save method, and load it back using the Ink.Load method. The data can be saved as Ink Serialized Format (ISF) or as a GIF file with ISF in its metadata (that is, a fortified GIF).
  • By creating tables for ink and searchable data, you can perform searches on the server side and thereby limit the amount of ink data retrieved from the database.
  • You can use a stored procedure to perform the search and retrieve the ink data.
  • To display the ink, you can either show it as a GIF file (if you stored it that way), or by turn it back into an Ink object and display it with the Renderer.Draw method.