问题描述
我正在尝试调试抛出错误的SQL响应:
这没有什么意义，因为对象没有任何废话.
代码:
using (var connection = _connectionProvider.GetDbConnection())
{
connection.Open();
return connection.Query<Rate>(query, parameters);
}
要执行的SQL(我手动添加了参数):
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
我在发生读取的位置(connection.Query<Rate>(query, parameters))上设置了断点，然后对异常启用了中断，当失败失败时，它跳入了TdsParser TryRun()(在抛出异常的地方，耦合级别更高)中更深的堆栈
这时我可以访问dataStream，它是 SqlDataReader
我正在寻找一种方法来输出SqlDataReader之类的原始结果，例如
System.Diagnostics.Debug.WriteLine((new System.IO.StreamReader(stream)).ReadToEnd());
，但SqlDataReader.
编辑
根据评论中的请求
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 :对于那些想知道是什么原因的人
声明实际上是通过其他机制转换为
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
当没有行时，如果不按预期选择top 1，则此错误将失败，并且随后的行将不会转换为位
问题仍然存在:即时调试时如何编写SqlDataReader?
即时调试时如何编写SqlDataReader?
SqlDataReader实现接口 IDataReader .以下技巧适用于任何实现此接口的读者.与(new System.IO.StreamReader(stream)).ReadToEnd()一样，这些技术将消耗数据读取器的内容，因此将不再可用.
完全是在实时转储结果.
如果您没有时间准备并需要立即查看阅读器的内容，则可以将数据阅读器加载到在即时窗口中定义的DataTable中，它们会打印出该表的XML./p>
首先，通过键入以下内容在立即窗口中定义三个运行时全局变量:
object [] _objs = null;
DataTable _table = null;
DataSet _set = null;
每个会话一次.
接下来，如果代码已经开始读取表列，则可以通过键入以下内容获取当前行的值:
_objs = new object[dataStream.FieldCount];
dataStream.GetValues(_objs);
_objs
现在将显示当前值.
然后，要读入并显示其余行，请执行以下操作:
_table = new DataTable();
_table.Load(dataStream);
_set = new DataSet();
_set.Tables.Add(_table);
_set.GetXml();
Debug.WriteLine(_set.GetXml());
您将在即时窗口中看到以XML字符串形式打印的_set的内容.请注意，如果部分读取了表，则 DataTable.Load(IDataReader) 将跳过当前行，因此请先转储当前值.
这对于与单个表相对应的读取器非常有效，但不适用于与构成一个表的多个表相对应的读取器.
使用小型调试库转储结果.
如果您有少量时间准备或需要调试多表读取器，则可以执行以下操作.
首先，使用以下实用程序创建一个小的调试DLL项目. 您不需要将此链接链接到您实际调试的项目中.
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();
}
}
}
(注意-获取表名的代码尚未经过完全测试.)
请注意，我正在使用json.net 以序列化结果值并格式化总体结果.如果愿意，可以使用其他序列化器.
以调试模式构建项目，然后将其复制到方便的位置，例如C:\Temp\DataReaderDebugUtilities.dll.
接下来，当您需要将值转储到数据读取器中时，请在立即窗口中键入:
Assembly.LoadFile(@"C:\Temp\DataReaderDebugUtilities.dll");
现在，即使它没有链接到您的项目中，您也可以在立即窗口中从此DLL调用方法.因此输入:
DataReaderDebugUtilities.DataReaderExtensions.CurrentNamesAndValues(dataStream)
将显示当前行的名称和值(如果有).
然后键入
string _s = DataReaderDebugUtilities.DataReaderExtensions.ToStringAsDataSet(dataStream);
或
string _s = DataReaderDebugUtilities.DataReaderExtensions.ToStringAsDataTable(dataStream);
会将读取器的其余内容作为数据表或数据集转储到JSON字符串中，以进行手动检查.
I am trying to debug a SQL response which is throwing an error:
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)
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.
这篇关于将SqlDataReader写入立即窗口C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！