ADO.NET 3.5 Cookbook, 2nd Edition

6.13. 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

Use a routine that mimics the functionality of the ADO Recordset GetString() method.

The solution creates a DataTable containing data from the Person.Contact table in AdventureWorks. The custom 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 output to the console.

The GetString() 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 in Program.cs in the project ExportQueryResultsString is shown in Example 6-13.

Example 6-13. File: Program.cs for ExportQueryResultsString solution

using System; using System.Data; using System.Data.SqlClient; using System.Text; namespace ExportQueryResultsString { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT ContactID, Title, FirstName, LastName " + "FROM Person.Contact"; // Create a data adapter SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); // Fill ...