Write SqlDataReader to immediate window c#

Question

I am trying to debug a SQL response which is throwing an error:

Conversion failed when converting the varchar value '0.01' to data type bit.

That does not make a lot of sense as object does not have any bools.

Code:

 using (var connection = _connectionProvider.GetDbConnection())
 {
    connection.Open();
    return connection.Query<Rate>(query, parameters);
 }

SQL that gets executed (I manually added parameters):

select * from (select top 1 BuildNumber, RateVersion, SampleId, Tariff, TariffStepName, Factor1, Result1 from dbo.Rates
where Tariff = 'Default' and TariffStepName = 'I_P' and (RateVersion <= 1) and Factor1 = 'false' and (SampleId is null)
order by RateVersion desc, sampleId desc) top1 

I placed breakpoint on where read happens (connection.Query<Rate>(query, parameters)), then enabled break on exceptions and when it failed jumped deeper into stack to TdsParser TryRun() (couple levels higher where exception is thrown)

System.Data.dll!System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior runBehavior, System.Data.SqlClient.SqlCommand cmdHandler, System.Data.SqlClient.SqlDataReader dataStream, System.Data.SqlClient.BulkCopySimpleResultSet bulkCopyHandler, System.Data.SqlClient.TdsParserStateObject stateObj, out bool dataReady) + 0x1ce1 bytes

At this point I have access to dataStream which is SqlDataReader

I am looking for a way to output 'raw' result right out of SqlDataReader, something like

System.Diagnostics.Debug.WriteLine((new System.IO.StreamReader(stream)).ReadToEnd());

but for SqlDataReader.

EDIT

as per request in comment

public class Rate
{
    public string Tariff { get; set; }
    public string TariffStepName { get; set; }
    public string Factor1 { get; set; }
    public string Factor2 { get; set; }
    public string Factor3 { get; set; }
    public string Factor4 { get; set; }
    public string Factor5 { get; set; }
    public string Factor6 { get; set; }
    public string Factor7 { get; set; }
    public string Factor8 { get; set; }
    public string Factor9 { get; set; }
    public string Factor10 { get; set; }
    public decimal Result1 { get; set; }
    public decimal Result2 { get; set; }
    public decimal Result3 { get; set; }
    public decimal Result4 { get; set; }
    public decimal Result5 { get; set; }
    public decimal Result6 { get; set; }
    public decimal Result7 { get; set; }
    public decimal Result8 { get; set; }
    public decimal Result9 { get; set; }
    public decimal Result10 { get; set; }
    public string TextResult1 { get; set; }
    public string TextResult2 { get; set; }
    public string TextResult3 { get; set; }
    public string TextResult4 { get; set; }
    public string TextResult5 { get; set; }
    public int? SampleId { get; set; }
    public int BuildNumber { get; set; }
    public decimal? RateVersion { get; set; }
}

SQL

CREATE TABLE dbo.[Rates](
    [BuildNumber] [int] NOT NULL,
    [Tariff] [varchar](30) NOT NULL,
    [TariffStepName] [varchar](60) NOT NULL,
    [Factor1] [varchar](50) NOT NULL,
    [Factor2] [varchar](50) NULL,
    [Factor3] [varchar](50) NULL,
    [Factor4] [varchar](50) NULL,
    [Factor5] [varchar](50) NULL,
    [Factor6] [varchar](50) NULL,
    [Factor7] [varchar](50) NULL,
    [Factor8] [varchar](50) NULL,
    [Factor9] [varchar](50) NULL,
    [Factor10] [varchar](50) NULL,
    [Result1] [varchar](50) NULL,
    [Result2] [decimal](19, 6) NULL,
    [Result3] [decimal](19, 6) NULL,
    [Result4] [decimal](19, 6) NULL,
    [Result5] [decimal](19, 6) NULL,
    [Result6] [decimal](19, 6) NULL,
    [Result7] [decimal](19, 6) NULL,
    [Result8] [decimal](19, 6) NULL,
    [Result9] [decimal](19, 6) NULL,
    [Result10] [decimal](19, 6) NULL,
    [RateVersion] [decimal](18, 2) NULL,
    [SampleId] [int] NULL,
    [TextResult1] [varchar](50) NULL,
    [TextResult2] [varchar](50) NULL,
    [TextResult3] [varchar](50) NULL,
    [TextResult4] [varchar](50) NULL,
    [TextResult5] [varchar](50) NULL
)

EDIT2: For those who are wondering what was the cause

statement was actually being converted to this by additional mechanism

exec sp_executesql N'select * from (select top 1 BuildNumber, RateVersion, SampleId, Tariff, TariffStepName, Factor1, Result1 from dbo.Rates
where Tariff = @Tariff and TariffStepName = @TariffStepName and (RateVersion <= @RV) and Factor1 = @Factor1 and (SampleId is null)
order by RateVersion desc, sampleId desc) top1 
',N'@Tariff varchar(50),@TariffStepName varchar(50),@RV decimal(3,2),@Factor1 bit',@Tariff='Default',@TariffStepName='I_P',@RV=1.00,@Factor1=0
go

this then would fail with error when there was no row by selecting not top 1 like it was intended but row after that then wouldn't cast to bit

Question still stands: How do I write SqlDataReader when debugging on the fly to immediate window?

Cupreous

Are you using some extensions ? Where does the method Query<T> come from ? – Elena

@user3185569 Hi, I am using dapper, but it's using underlying Sql server mechanism. – Cupreous

Can you show the class Rate along with the CREATE statement of your table in SQL. There seems to be a mismatch in the data types. – Elena

One last thing i guess. What does query hold ? Can you show your raw SQL Query ? Maybe the error is in the query itself. – Elena

@user3185569 query returns results ok when executed directly (via SQL management studio). – Cupreous

This is very confusing. One hint i can give you is to start to remove the conditions in the where one by one, to detect which clause is causing the error. – Elena

Your query is perfectly fine. But the error you get i guess is because of actual data in the table. In any of your column some wrong data is entered. Please check your Rate table's records if its not too big table. My doubt is on Factor1 and Result1 column. But better you check all column – Guiana

pretty clear you had new { ..., Factor1 = false, ... } instead of "false" in the parameters. – Jimerson

Answer 1

How do I write SqlDataReader when debugging on the fly to immediate window?

SqlDataReader implements the interface IDataReader. The following tricks apply to any reader implementing this interface. As with (new System.IO.StreamReader(stream)).ReadToEnd(), these techniques will consume the contents of the data reader, so it will no longer be usable.

Dumping results purely on the fly.

If you don't have time to prepare and need to look at your reader's contents right away, you can load your data reader into a DataTable defined in the immediate window, them print out the XML for that table.

First, define three run-time global variables in the immediate window by typing:

object [] _objs = null;
DataTable _table = null;
DataSet _set = null;

Do this once per session.

Next, if the code has already begun to read through the table columns, you can get the values of the current row by typing:

_objs = new object[dataStream.FieldCount];
dataStream.GetValues(_objs);
_objs

The current values will now be displayed.

Then, to read in and display the remainder of the rows, do the following:

_table = new DataTable();
_table.Load(dataStream);
_set = new DataSet();
_set.Tables.Add(_table);
_set.GetXml();
Debug.WriteLine(_set.GetXml());

You will see the contents of _set printed out in the Immediate Window as an XML string. Note that if the table is partially read, DataTable.Load(IDataReader) will skip the current row, so dump the current values first.

This works well for readers corresponding to a single table, but not for readers corresponding to multiple tables that form a set.

Dumping results using a small debugging library.

If you have a little time to prepare or need to debug a multi-table reader, you can do the following.

First, create a small debugging DLL project with utilities such as the following. You do not need to link this into the project you are actually debugging.

namespace DataReaderDebugUtilities
{
    public static class DataReaderExtensions
    {
        public static object[] CurrentValues(this IDataReader reader)
        {
            if (reader == null)
                throw new ArgumentNullException();
            var objs = new object[reader.FieldCount];
            reader.GetValues(objs);
            return objs;
        }

        public static KeyValuePair<string, object> [] CurrentNamesAndValues(this IDataReader reader)
        {
            if (reader == null)
                throw new ArgumentNullException();
            var query = Enumerable.Range(0, reader.FieldCount).Select(i => new KeyValuePair<string, object>(reader.GetName(i), reader.GetValue(i)));
            return query.ToArray();
        }

        public static string ToStringAsDataTable(this IDataReader reader)
        {
            if (reader == null)
                throw new ArgumentNullException();
            var sb = new StringBuilder();
            using (var textWriter = new StringWriter(sb))
            using (var jsonWriter = new JsonTextWriter(textWriter) { Formatting = Formatting.Indented })
            {
                var serializer = JsonSerializer.CreateDefault();
                jsonWriter.WriteDataTable(reader, serializer);
            }
            return sb.ToString();
        }

        public static string ToStringAsDataSet(this IDataReader reader)
        {
            if (reader == null)
                throw new ArgumentNullException();
            var sb = new StringBuilder();
            using (var textWriter = new StringWriter(sb))
            using (var jsonWriter = new JsonTextWriter(textWriter) { Formatting = Formatting.Indented })
            {
                var serializer = JsonSerializer.CreateDefault();
                jsonWriter.WriteDataSet(reader, serializer);
            }
            return sb.ToString();
        }
    }

    public static class JsonExtensions
    {
        public static void WriteDataTable(this JsonWriter writer, IDataReader reader, JsonSerializer serializer)
        {
            if (writer == null || reader == null || serializer == null)
                throw new ArgumentNullException();
            writer.WriteStartArray();
            while (reader.Read())
            {
                writer.WriteStartObject();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    writer.WritePropertyName(reader.GetName(i));
                    serializer.Serialize(writer, reader[i]);
                }
                writer.WriteEndObject();
            }
            writer.WriteEndArray();
        }

        public static void WriteDataSet(this JsonWriter writer, IDataReader reader, JsonSerializer serializer)
        {
            if (writer == null || reader == null || serializer == null)
                throw new ArgumentNullException();
            writer.WriteStartObject();

            do
            {
                var tableName = string.Empty;
                var schemaTable = reader.GetSchemaTable();
                if (schemaTable != null)
                    tableName = schemaTable.Rows.Cast<DataRow>()
                        .Select(r => r[schemaTable.Columns[System.Data.Common.SchemaTableColumn.BaseTableName]].ToString())
                        .FirstOrDefault();
                writer.WritePropertyName(tableName ?? string.Empty);
                writer.WriteDataTable(reader, serializer);
            }
            while (reader.NextResult());

            writer.WriteEndObject();
        }
    }
}

(Note - the code to get the table name is not fully tested.)

Note I am using json.net to serialize result values and format the overall results. You could use a different serializer if you prefer.

Build the project in debug mode and copy it to a convenient location, say C:\Temp\DataReaderDebugUtilities.dll.

Next, when you need to dump the values inside a data reader, in the immediate window, type:

Assembly.LoadFile(@"C:\Temp\DataReaderDebugUtilities.dll");

Now you can call methods from this DLL in the immediate window even though it is not linked into your project. Thus typing:

DataReaderDebugUtilities.DataReaderExtensions.CurrentNamesAndValues(dataStream)

will show you the names and values of the current row, if any.

Then typing

string _s = DataReaderDebugUtilities.DataReaderExtensions.ToStringAsDataSet(dataStream);

or

string _s = DataReaderDebugUtilities.DataReaderExtensions.ToStringAsDataTable(dataStream);

will dump the remaining contents of the reader as a data table or data set into a JSON string for manual inspection.

Turves

Answer 2

• You could set a breakpoint inside Dapper code - is Open Source.
• Result1 is defined as varchar(50), but your C# class says decimal.

Demisemiquaver

Nice spot, this means there has to be mechanism for conversion somewhere, as when I changed Result1 to string in C# it gave 100s of errors, even better when I checked select SELECT distinct cast([Result1] as decimal(18,10)) FROM dbo.[Rates] it didn't give me any errors. At this point I will go to speak to team on who wrote it like this (and why) and what is going on with this.... – Cupreous

Answer 3

Conversion failed when converting the varchar value '0.01' to data type bit.
i think the message is thrown by SQL server. so there should be error at SQL level. Try checking actual query in SQL profiler and run in SSMS.

Jadwiga

do you mean if you copy paste query from profiler to SSMS and run then it runs fine? I suspect issue in query, because this message comes at SQL level (for ex: select convert(bit,'0.01')) – Jadwiga