Recipe 5.11 Exporting the Results of a Query as a String

Problem

You need to export the results of a query to a string in a manner similar to the GetString( ) method of the ADO Recordset.

Solution

Write a routine to mimic the functionality of the ADO Recordset's GetString( ) method.

The sample code contains an event handler and a method:

Go Button.Click

Sets up the sample by creating a DataTable containing the Orders table from Northwind. The GetString( ) method in this solution is called to convert the DataTable into a string similar to one that is generated by the GetString( ) method of the ADO Recordset. The string is displayed in a text box on the form.

GetString( )

This method mimics the functionality of the GetString( ) method of the ADO Recordset. The method iterates over the collection of rows and columns in the table appending the field values to a string. Null values are replaced as specified and column and row delimiters are applied.

The C# code is shown in Example 5-13.

Example 5-13. File: AdoGetStringForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;

// Table name constants
private const String ORDERS_TABLE = "Orders";

//  . . . 

private void goButton_Click(object sender, System.EventArgs e)
{
    // Fill the Order table.
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    DataTable dt = new DataTable(ORDERS_TABLE);
    da.Fill(dt);

    // Call method to convert the DataTable to a string.
    resultTextBox.Text = GetString(dt, -1, null, null, null);
}

private String GetString(DataTable dt, int numRows,
    String columnDelimiter, String rowDelimiter, String nullExpr)
{
    if(numRows < 0)
        // Negative number of rows returns all rows
        numRows = dt.Rows.Count;
    else
        // Set number of rows to the lesser of the user entered
        // number of rows and the number of rows in the table.
        numRows = Math.Max(numRows, dt.Rows.Count);

    // Column delimiter defaults to TAB
    if(columnDelimiter == null)
        columnDelimiter = "\t";

    // Row delimiter defaults to CARRIAGE RETURN
    if(rowDelimiter == null)
        rowDelimiter = "\r";

    // Null expression defaults to empty string
    if(nullExpr == null)
        nullExpr = "";

    StringBuilder sb = new StringBuilder( );

    // Iterate over the collection of rows.
    for(int i = 0; i < numRows; i++)
    {
        // Iterate over the collection of columns.
        foreach (object col in dt.Rows[i].ItemArray)
        {    
            // Replace null values as they occur.
            String colString = (col == System.DBNull.Value) ?
                nullExpr : col.ToString( );

            // Add the column value to the string.
            sb.Append(colString + columnDelimiter);
        }
        // Remove the column delimiter on last field.
        sb.Remove(sb.Length - columnDelimiter.Length,
            columnDelimiter.Length);
        // Append row delimiter.
        sb.Append(rowDelimiter);
    }

    return sb.ToString( );
}

Discussion

ADO.NET does not contain a method that is equivalent to the GetString( ) method of the ADO Recordset or a method that converts the Recordset to a string.

This solution presents an ADO.NET method, which is also called GetString( ), that duplicates the functionality of the ADO GetString( ) method. The prototype for the ADO.NET method is:

String tableString = GetString(DataTable dt, Integer numRows, String columnDelimiter, 
    String rowDelimiter, String nullExpr);

Parameters

tableString

Returns a string corresponding to the rows selected from the table.

dt

The DataTable to convert to a string.

numRows

The number of rows in the table to convert. If this number is negative, all rows in the table are converted. If a number larger than the number of records in the table is specified, all records are converted without error.

columnDelimiter

The character or characters that separate columns. The default value is the TAB character.

rowDelimiter

The character or characters that separate rows. The default value is the CARRIAGE RETURN character.

nullExpr

A string that is substituted for null column values in the table. The default value is an empty string.