LinqPad dynamic sql and displaying results
I am using LinqPad to do some dynamic sql and it returns IEnumerable when I call .Dump (). I would like it to display the anonymous type results that it returns. Any help executing dynamic sql statement in LinqPad and displaying the results would be appreciated.
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);
source to share
You are on the right track with IDataRecord. To do dynamic output, use 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 do the following:
this.ExecuteSQL ("select * from customer").GroupBy (c => c.Name).Dump();
EDIT: This functionality is now available in LINQPad as of v4.53.02. Now you can go:
ExecuteQueryDynamic ("SELECT DISTINCT * FROM Customer WHERE ID = {0}", 2)
source to share
So what I did to get the 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();
}
source to share
In addition to Joe's answer :
It is important that you use a Linq-to-SQL connection because it is
ExecuteQueryDynamic
not available in the Entity Framework.
Here's how you can handle different data types as parameters (based on Northwind database ) in 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 use a statement DECLARE
for your SQL variables. So you can try first in T-SQL (or LinqPad SQL mode) with fixed values assigned - where you get meaningful error messages if the datatypes don't match, then you can insert them in ExecuteQueryDynamic and insert {0}, {1}, {2} ...
for the first, second. third, ... parameter as follows:
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 result sets. This means that only one SELECT statement is allowed, and additional ones are ignored.
source to share