Write SqlDataReader to XML file

Question

I'm testing stored SQL procedures in C#. The procs return the datatype SqlDataReader and I want to write the whole thing to an XML file to compare later. Nothing I've read has provided a very simple solution. Is there a way to do this without looping through all the data in the stream? I don't know much about SQL, so I'm not sure exactly what I'm working with here.

Answer 1

The XML produced by DataSet, DataTable and its ilk leaves something to be desired from the point of view of humans reading it. I'd roll my own.

A SqlDataReader (and it doesn't matter whether its returning data from a stored procedure or a plain-text SQL query), returns 0 to many result sets. Each such result set has

• a schema that describes the columns being returned in each row, and
• the result set itself, consisting of zero or more rows.
• Each row, is essentially an array of 1 or more columns, with each cell containing the value for the column with that ordinal position in the row.
• each such column has certain properties, some from the schema, such as name, ordinal type, nullability, etc.
• Finally, the column value within a row, is an object of the type corresponding to the SQL Server data type of the column in the result...or DbNull.Value if the column is null.

The basic loop is pretty straightforward (lots of examples in MSDN on how to do it.) And while it might be a bit of work to write it in the first place, once written, it's usable across the board, so it's a one-time hit. I would suggest doing something like this:

1. Determine what you want the XML to look like. Assuming your intent is to be able to diff the results from time to time, I'd probably go with something that looks like this (since I like to keep things terse and avoid redundancy):

   <stored-procedure-results>
     <name> dbo.some-stored-procedure-name </name>
     <result-sets>
       <result-set>
         <column-schema column-count="N">
           <column ordinal="0...N-1" name="column-name-or-null-if-column-is-unnamed-or-not-unique" data-type=".net-data-type" nullable="true|false" />
           ...
         </schema>
         <rows>
           <row>
             <column ordinal="0..N-1" value="..." />
             ...
           <row/>
           ...
         </rows>
       </result-set>
       ...
     </result-sets>
   </stored-procedure-results>
   

2. Build POCO model classes to contain the data. Attribute them with XML serialization attributes to get the markup you want. From the above XML sample, these classes won't be all that complex. You'll probably want to represent column values as strings rather than native data types.

3. Build a mapper that will run the data reader and construct your model.

Then it's a couple of dozen lines of code to construct the XML serializer of choice and spit out nicely formatted XML.

Notes:

• For QA purposes, you might want to capture the parameters, if any, that were passed to the query, along with the query itself, possibly, the date/time of the run.

• There are a few oddball cases where the results set model I describe can get...wonky. For example, a select statement using compute by has to get handled somewhat differently. In my experience, it's pretty safe to ignore that sort of edge case, since you're unlikely to encounter queries like that in the wild.

• Think about how you represent null in the XML: null strings are not the same as empty strings.

Answer 2

Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"C:\temp\test.xml";
        static void Main(string[] args)
        {
            string connstr = "Enter your connection string here";
            string SQL = "Enter your SQL Here";

            SqlDataAdapter adapter = new SqlDataAdapter(SQL, connstr);
            SqlCommand cmd = adapter.SelectCommand;
            cmd.Parameters.Add("abc", SqlDbType.VarChar);


            adapter.SelectCommand.ExecuteNonQuery();

            DataSet ds = new DataSet();
            adapter.Fill(ds);

            ds.WriteXml(FILENAME, XmlWriteMode.WriteSchema);
        }
    }
}

Answer 3

I see the main issue is how to test complicated stored procedures before releases, not writing an XML from SQLDataAdapter which can be very simple. Row by row, column by column. You have a test database which does not contain static data and you store somehow different versions of the stored procedure. A simple setup would be to run the (let's say 5) versions of a stored procedure you have, run them against the same database content, store the xmls to a folder and compare them. I would use for example a different folder for each run and have a timestamp to distinguish between them for example. I would not spent too much on how the xmls are written and in order to detect if they are different you end up even using String.Compare(fileStream1.ReadToEnd(), fileStream2.ReadToEnd()). If the result is too large, then something more elaborated. If there are differences between 2 xmls, then you can look at them with a text compare tool. ...For more complicated stored procedures with multiple joins, the most common difference will likely be the size of the xmls\ the number of rows returned, not the value of a field.

In production, the content of the database is not static, so doing this type of test would not make sense.

Answer 4

When serializing SqlDataReader using the built-in methods WriteXml in DataTable or DataSet as described in the accepted answer, and the data contains geography data, the geography data are lost and can't be restored latter.

For more details read Datatable with SqlGeography column can't be serialized to xml correctly with loss of Lat,Long and other elements

There is a workaround solution to save to xml provided by @dbc without loss of data and save to xml using the same built-in methods WriteXml. Try it online