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