I'm using LinqPad to execute some dynamic sql and it is returning IEnumerable when I call .Dump(). I would like it to display the results of the anonymous type its returning. Any help on executing a dynamic sql statement in LinqPad and displaying the results would be appreciated.
我正在使用LinqPad执行一些动态sql,当我调用. dump()时,它将返回IEnumerable。我希望它显示匿名类型返回的结果。在LinqPad中执行动态sql语句并显示结果的任何帮助都是值得赞赏的。
Here is a code snippet of what I am trying to do:
下面是我要做的事情的代码片段:
// Any sql string for example.
var query = "SELECT DISTINCT [CustomerId] FROM Customers Where CustomerId = 2";
var dyn = this.ExecuteQuery<dynamic>(query);
LINQPad.Extensions.Dump(dyn);
3 个解决方案
#1
3
You're on the right track with IDataRecord. To make the output dynamic, use DynamicObject:
你和IDataRecord的关系很好。为了使输出动态,使用DynamicObject:
static class Extensions
{
public static IEnumerable<dynamic> ExecuteSQL (this DataContext dc, string sql)
{
var cx = new SqlConnection (dc.Connection.ConnectionString);
cx.Open();
return new SqlCommand (sql, cx).ExecuteReader (CommandBehavior.CloseConnection).Cast<IDataRecord>().Select (r => new DynamicDataRecord (r));
}
}
class DynamicDataRecord : System.Dynamic.DynamicObject
{
readonly IDataRecord _row;
public DynamicDataRecord (IDataRecord row) { _row = row; }
public override bool TryConvert (System.Dynamic.ConvertBinder binder, out object result)
{
if (binder.Type == typeof (IDataRecord))
{
result = _row;
return true;
}
return base.TryConvert (binder, out result);
}
public override bool TryInvokeMember (System.Dynamic.InvokeMemberBinder binder, object [] args, out object result)
{
if (binder.Name == "Dump")
{
if (args.Length == 0)
_row.Dump ();
else if (args.Length == 1 && args [0] is int)
_row.Dump ((int)args [0]);
else if (args.Length == 1 && args [0] is string)
_row.Dump ((string)args [0]);
else if (args.Length == 2)
_row.Dump (args [0] as string, args [1] as int?);
else
_row.Dump ();
result = _row;
return true;
}
return base.TryInvokeMember (binder, args, out result);
}
public override bool TryGetMember (System.Dynamic.GetMemberBinder binder, out object result)
{
result = _row [binder.Name];
if (result is DBNull) result = null;
return true;
}
public override bool TryGetIndex (System.Dynamic.GetIndexBinder binder, object [] indexes, out object result)
{
if (indexes.Length == 1)
{
result = _row [int.Parse (indexes [0].ToString ())];
return true;
}
return base.TryGetIndex (binder, indexes, out result);
}
public override IEnumerable<string> GetDynamicMemberNames ()
{
return Enumerable.Range (0, _row.FieldCount).Select (i => _row.GetName (i));
}
}
This will allow the following:
这将使下列各点成为可能:
this.ExecuteSQL ("select * from customer").GroupBy (c => c.Name).Dump();
EDIT: this functionality is now available within LINQPad as of v4.53.02. You can now go:
编辑:从v4.53.02开始,LINQPad中就可以使用这个功能了。您现在可以:
ExecuteQueryDynamic ("SELECT DISTINCT * FROM Customer WHERE ID = {0}", 2)
#2
1
So what I've done to get a result is this, but I think there must be a better way.
所以我所做的是得到这个结果,但是我认为一定有更好的方法。
using (SqlConnection connection = new SqlConnection(this.Connection.ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader();
reader.Cast<IDataRecord>().AsQueryable().Dump();
}
#3
1
In addition to Joe's answer:
除了乔的回答:
It is important that you're using a Linq-to-SQL connection, because
ExecuteQueryDynamic
is not available in the Entity Framework.使用linqto - sql连接非常重要,因为ExecuteQueryDynamic在实体框架中不可用。
Here's how you can handle different data types as parameters (based on a Northwind database) in LinqPad 5:
以下是如何在LinqPad 5中处理不同的数据类型作为参数(基于风数据库):
void Main()
{
// Boolean
ExecuteQueryDynamic(@"DECLARE @Discontinued bit={0};
SELECT DISTINCT * FROM Products
WHERE Discontinued = @Discontinued", true).Dump();
// Int
ExecuteQueryDynamic(@"DECLARE @OrderId Int={0};
SELECT DISTINCT OrderId, CustomerId, ShipName FROM Orders
WHERE OrderID = @OrderId", 10248).Dump();
// String
ExecuteQueryDynamic(@"DECLARE @CustomerId nvarchar(max)={0};
SELECT DISTINCT * FROM Customers
WHERE CustomerId = @CustomerId", "VINET").Dump();
}
I recommend that you're using a DECLARE
statement for your SQL variables. This way, you can try it out first in T-SQL (or LinqPad SQL mode) with assigned fixed values - where you will receive meaningful error messages if the data types are not matching, then you can insert it into ExecuteQueryDynamic and insert {0}, {1}, {2} ...
for the first, second, third, ... parameter as follows:
我建议您对SQL变量使用声明语句。通过这种方式,您可以先在T-SQL(或LinqPad SQL模式)中尝试使用指定的固定值——如果数据类型不匹配,您将收到有意义的错误消息,然后您可以将其插入到ExecuteQueryDynamic中,并插入{0}、{1}、{2}……对于第一,第二,第三,…参数如下:
ExecuteQueryDynamic(@"DECLARE @Discontinued bit={0}; DECLARE @ProductID Int={1};
DECLARE @CategoryID Int={2};
SELECT DISTINCT * FROM Products
WHERE Discontinued = @Discontinued AND ProductId = @ProductID
AND CategoryID = @CategoryID;
", true, 5, 2).Dump();
NOTE: ExecuteQueryDynamic does not support multiple resultsets. This means, only one SELECT statement is allowed, additional ones are being ignored.
注意:ExecuteQueryDynamic不支持多个结果集。这意味着,只允许一个SELECT语句,而忽略其他语句。
#1
3
You're on the right track with IDataRecord. To make the output dynamic, use DynamicObject:
你和IDataRecord的关系很好。为了使输出动态,使用DynamicObject:
static class Extensions
{
public static IEnumerable<dynamic> ExecuteSQL (this DataContext dc, string sql)
{
var cx = new SqlConnection (dc.Connection.ConnectionString);
cx.Open();
return new SqlCommand (sql, cx).ExecuteReader (CommandBehavior.CloseConnection).Cast<IDataRecord>().Select (r => new DynamicDataRecord (r));
}
}
class DynamicDataRecord : System.Dynamic.DynamicObject
{
readonly IDataRecord _row;
public DynamicDataRecord (IDataRecord row) { _row = row; }
public override bool TryConvert (System.Dynamic.ConvertBinder binder, out object result)
{
if (binder.Type == typeof (IDataRecord))
{
result = _row;
return true;
}
return base.TryConvert (binder, out result);
}
public override bool TryInvokeMember (System.Dynamic.InvokeMemberBinder binder, object [] args, out object result)
{
if (binder.Name == "Dump")
{
if (args.Length == 0)
_row.Dump ();
else if (args.Length == 1 && args [0] is int)
_row.Dump ((int)args [0]);
else if (args.Length == 1 && args [0] is string)
_row.Dump ((string)args [0]);
else if (args.Length == 2)
_row.Dump (args [0] as string, args [1] as int?);
else
_row.Dump ();
result = _row;
return true;
}
return base.TryInvokeMember (binder, args, out result);
}
public override bool TryGetMember (System.Dynamic.GetMemberBinder binder, out object result)
{
result = _row [binder.Name];
if (result is DBNull) result = null;
return true;
}
public override bool TryGetIndex (System.Dynamic.GetIndexBinder binder, object [] indexes, out object result)
{
if (indexes.Length == 1)
{
result = _row [int.Parse (indexes [0].ToString ())];
return true;
}
return base.TryGetIndex (binder, indexes, out result);
}
public override IEnumerable<string> GetDynamicMemberNames ()
{
return Enumerable.Range (0, _row.FieldCount).Select (i => _row.GetName (i));
}
}
This will allow the following:
这将使下列各点成为可能:
this.ExecuteSQL ("select * from customer").GroupBy (c => c.Name).Dump();
EDIT: this functionality is now available within LINQPad as of v4.53.02. You can now go:
编辑:从v4.53.02开始,LINQPad中就可以使用这个功能了。您现在可以:
ExecuteQueryDynamic ("SELECT DISTINCT * FROM Customer WHERE ID = {0}", 2)
#2
1
So what I've done to get a result is this, but I think there must be a better way.
所以我所做的是得到这个结果,但是我认为一定有更好的方法。
using (SqlConnection connection = new SqlConnection(this.Connection.ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader();
reader.Cast<IDataRecord>().AsQueryable().Dump();
}
#3
1
In addition to Joe's answer:
除了乔的回答:
It is important that you're using a Linq-to-SQL connection, because
ExecuteQueryDynamic
is not available in the Entity Framework.使用linqto - sql连接非常重要,因为ExecuteQueryDynamic在实体框架中不可用。
Here's how you can handle different data types as parameters (based on a Northwind database) in LinqPad 5:
以下是如何在LinqPad 5中处理不同的数据类型作为参数(基于风数据库):
void Main()
{
// Boolean
ExecuteQueryDynamic(@"DECLARE @Discontinued bit={0};
SELECT DISTINCT * FROM Products
WHERE Discontinued = @Discontinued", true).Dump();
// Int
ExecuteQueryDynamic(@"DECLARE @OrderId Int={0};
SELECT DISTINCT OrderId, CustomerId, ShipName FROM Orders
WHERE OrderID = @OrderId", 10248).Dump();
// String
ExecuteQueryDynamic(@"DECLARE @CustomerId nvarchar(max)={0};
SELECT DISTINCT * FROM Customers
WHERE CustomerId = @CustomerId", "VINET").Dump();
}
I recommend that you're using a DECLARE
statement for your SQL variables. This way, you can try it out first in T-SQL (or LinqPad SQL mode) with assigned fixed values - where you will receive meaningful error messages if the data types are not matching, then you can insert it into ExecuteQueryDynamic and insert {0}, {1}, {2} ...
for the first, second, third, ... parameter as follows:
我建议您对SQL变量使用声明语句。通过这种方式,您可以先在T-SQL(或LinqPad SQL模式)中尝试使用指定的固定值——如果数据类型不匹配,您将收到有意义的错误消息,然后您可以将其插入到ExecuteQueryDynamic中,并插入{0}、{1}、{2}……对于第一,第二,第三,…参数如下:
ExecuteQueryDynamic(@"DECLARE @Discontinued bit={0}; DECLARE @ProductID Int={1};
DECLARE @CategoryID Int={2};
SELECT DISTINCT * FROM Products
WHERE Discontinued = @Discontinued AND ProductId = @ProductID
AND CategoryID = @CategoryID;
", true, 5, 2).Dump();
NOTE: ExecuteQueryDynamic does not support multiple resultsets. This means, only one SELECT statement is allowed, additional ones are being ignored.
注意:ExecuteQueryDynamic不支持多个结果集。这意味着,只允许一个SELECT语句,而忽略其他语句。