dapperlib / dapper Goto Github PK
View Code? Open in Web Editor NEWDapper - a simple object mapper for .Net
License: Other
Dapper - a simple object mapper for .Net
License: Other
Dapper.Contrib.Insert generates a query wrong.
Example:
{insert into Markers (Name, Address,) values (@ Name, @ Address,}
This error occurs when the key (Id) is the last in the list allProperties.
var allProperties = TypePropertiesCache(type);
The current code only works correctly when the key (Id) is the first in the list (allProperties).
I modified the code to work regardless of the order of the properties, I hope will be welcome:)
public static long Insert(this IDbConnection connection, T entityToInsert, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
{
var type = typeof(T);
var name = GetTableName(type);
var sb = new StringBuilder(null);
sb.AppendFormat("insert into {0} (", name);
var allProperties = TypePropertiesCache(type);
var keyProperties = KeyPropertiesCache(type);
var allPropertiesExceptKey = allProperties.Except(keyProperties);
for (var i = 0; i < allPropertiesExceptKey.Count(); i++)
{
var property = allPropertiesExceptKey.ElementAt(i);
sb.Append(property.Name);
if (i < allPropertiesExceptKey.Count() - 1)
sb.Append(", ");
}
sb.Append(") values (");
for (var i = 0; i < allPropertiesExceptKey.Count(); i++)
{
var property = allPropertiesExceptKey.ElementAt(i);
sb.AppendFormat("@{0}", property.Name);
if (i < allPropertiesExceptKey.Count() - 1)
sb.Append(", ");
}
sb.Append(") ");
connection.Execute(sb.ToString(), entityToInsert, transaction: transaction, commandTimeout: commandTimeout);
//NOTE: would prefer to use IDENT_CURRENT('tablename') or IDENT_SCOPE but these are not available on SQLCE
var r = connection.Query("select @@IDENTITY id", transaction: transaction, commandTimeout: commandTimeout);
return (int)r.First().id;
}
The Page on the URL http://code.google.com/p/dapper-dot-net/ point to this Project at the URL https://github.com/StackExchange/dapper-dot-net (Dapper has moved to github. See you there). Maybe the page on the latter URL should not have a prominent link that points back to the former URL. Thanks
I'm emitting a class at runtime whose purpose is to represent a data object out of a database.
For simplicity, it's emitted as a class with a bunch of public fields.
If I pass an instance of this class to Execute() as the "param" argument, it does not recognize the public fields and map them to my sql variables.
I am having to re-emit them as public properties as a workaround.
When getting SQL Server Types (SqlGeography, SqlGeometry, SqlHierarchyId) from a query result, I was getting the following exception:
Microsoft.CSharp.RuntimeBinder.RuntimeBinderInternalCompilerException was unhandled by user code
HResult=-2146233088
Message=An unexpected exception occurred while binding a dynamic operation
Source=Microsoft.CSharp
StackTrace:
at Microsoft.CSharp.RuntimeBinder.RuntimeBinder.Bind(DynamicMetaObjectBinder payload, IEnumerable`1 parameters, DynamicMetaObject[] args, DynamicMetaObject& deferredBinding)
at Microsoft.CSharp.RuntimeBinder.BinderHelper.Bind(DynamicMetaObjectBinder action, RuntimeBinder binder, IEnumerable`1 args, IEnumerable`1 arginfos, DynamicMetaObject onBindingError)
at Microsoft.CSharp.RuntimeBinder.CSharpConvertBinder.FallbackConvert(DynamicMetaObject target, DynamicMetaObject errorSuggestion)
at System.Dynamic.DynamicMetaObject.BindConvert(ConvertBinder binder)
at System.Dynamic.ConvertBinder.Bind(DynamicMetaObject target, DynamicMetaObject[] args)
at System.Dynamic.DynamicMetaObjectBinder.Bind(Object[] args, ReadOnlyCollection`1 parameters, LabelTarget returnLabel)
at System.Runtime.CompilerServices.CallSiteBinder.BindCore[T](CallSite`1 site, Object[] args)
at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
at DATailor.Examples.Dapper.SqlClient.Test.AllTypesDAOTest.TestAllTypesDynamic()
Long story short, the following underlying exception was actually occurring and required that I add a bindingRedirect.
System.InvalidCastException was unhandled
HResult=-2147467262
Message=[A]Microsoft.SqlServer.Types.SqlGeometry cannot be cast to [B]Microsoft.SqlServer.Types.SqlGeometry. Type A originates from 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll'. Type B originates from 'Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll'.
Source=DynamicGeometryIssue
StackTrace:
at DynamicGeometryIssue.TestDao.TestGeometry() in c:\Users\rich\Documents\Visual Studio 2013\Projects\DynamicGeometryIssue\DynamicGeometryIssue\TestDao.cs:line 27
at DynamicGeometryIssue.Program.Main(String[] args) in c:\Users\rich\Documents\Visual Studio 2013\Projects\DynamicGeometryIssue\DynamicGeometryIssue\Program.cs:line 15
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
I'm not sure anything can be done about the exception being swallowed due to the use of dynamic, but if at all possible, it would save some people some headaches. I spent a good 4 hours trying to figure out what was going on here.
The issue is documented with more detail and formatted better on Stackoverflow http://stackoverflow.com/questions/25600043/runtimebinderinternalcompilerexception-when-using-microsoft-sqlserver-types-with
To elegantly handle Oracle SYS_REFCURSOR out parameters, I am implementing a custom SqlMapper.IDynamicParameters class. In the AddParameters(IDbCommand command, SqlMapper.Identity identity) method I would like to use any registered ITypeHandler instances. The easiest way to acquire a reference to a registered instance would be via a GetTypeHandler(Type type) method on SqlMapper.
It would be cool if you could specify an instance and a member expression for output parameters in the DynamicParameters bag. This would be useful for populating object properties after operations.
How it is:
var sql = @"
INSERT INTO dbo.posts VALUES @AuthorId, @Title, @Body
SET @PostId = SCOPE_IDENTITY()
SET @Author = (SELECT Name FROM dbo.users WHERE Id = @AuthorId)
";
var param = new DynamicParameters(post);
param.Add("@PostId", dbType: DbType.Int32, direction: ParameterDirection.Output);
param.Add("@Author", dbType: DbType.String, direction: ParameterDirection.Output);
GetConnection().Execute(sql, param);
post.PostId = param.Get<int>("@PostId");
post.Author = param.Get<string>("@Author");
How it could be:
var sql = @"
INSERT INTO dbo.posts VALUES @AuthorId, @Title, @Body
SET @PostId = SCOPE_IDENTITY()
SET @Author = (SELECT Name FROM dbo.users WHERE Id = @AuthorId)
";
var param = new DynamicParameters(post)
.Output(post, p => p.PostId)
.Output(post, p => p.Author);
GetConnection().Execute(sql, param);
Hello,
I just get the last sources, and I'am not able to build the solution:
The error is (Dapper NET45, SqlMapperAsync.cs):
"Cannot await in the body of a finally clause", line 575.
Because it's apparently not possible to call the following code from the finally block:
if (index == gridIndex)
{
await NextResultAsync().ConfigureAwait(false);
}
Could you please fix this issue?
Thanks,
Thierry M.
Setting the typemap to map string to AnsiString works with a single parameter. When passing in an IEnumerable it automatically uses String instead of looking up the DBType in the typemap.
The following code works with SqlConnection but fails with AseConnection. Both implement the IDbConnection interface so I assumed AseConnection would work. AseConnection works fine with queries that don't have parameters.
var data = connection.Query("select * from dbo.ORDER_HDR where ORD_NUM = @ORD_NUM", new { ORD_NUM = id });
The actual error is: AseException: COUNT field incorrect
I am trying to use the Insert method from Dapper.Contrib and it keeps throwing an error.
using (SqlConnection conn = new SqlConnection(Program.ConnectionString))
{
Author a = new Author
{
Username = "[email protected]",
FullName = "James Hughes",
CreatedDate = DateTime.Now
};
conn.Open();
conn.Insert(a);
}
This code results in the following error
System.InvalidOperationException was unhandled
Message=ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.
Source=System.Data
StackTrace:
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, IDbTransaction tranaction, String sql, Action`2 paramReader, Object obj, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\Projects\microorm-comparison\CodeSlice.Data\CodeSlice.Data\Dapper\SqlMapper.cs:line 836
at CallSite.Target(Closure , CallSite , Type , IDbConnection , IDbTransaction , String , Object , Object , Nullable`1 , Nullable`1 )
at System.Dynamic.UpdateDelegates.UpdateAndExecute8[T0,T1,T2,T3,T4,T5,T6,T7,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4, T5 arg5, T6 arg6, T7 arg7)
at CallSite.Target(Closure , CallSite , Type , IDbConnection , IDbTransaction , String , Object , Object , Nullable`1 , Nullable`1 )
at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\Projects\microorm-comparison\CodeSlice.Data\CodeSlice.Data\Dapper\SqlMapper.cs:line 176
at Dapper.Contrib.Extensions.SqlMapperExtensions.Insert[T](IDbConnection connection, T entityToInsert) in C:\Projects\microorm-comparison\CodeSlice.Data\CodeSlice.Data\Dapper\Extensions\SqlMapperExtensions.cs:line 175
at CodeSlice.Data.Profilers.ProfileInsert.<Run>b__2() in C:\Projects\microorm-comparison\CodeSlice.Data\CodeSlice.Data\Profilers\ProfileInsert.cs:line 51
at CodeSlice.Data.Profilers.ActionProfiler.Profile(String description, Action actionToProfile) in C:\Projects\microorm-comparison\CodeSlice.Data\CodeSlice.Data\Profilers\ActionProfiler.cs:line 16
at CodeSlice.Data.Profilers.ProfileInsert.Run() in C:\Projects\microorm-comparison\CodeSlice.Data\CodeSlice.Data\Profilers\ProfileInsert.cs:line 39
at CodeSlice.Data.Profilers.ProfileInsert.Exec() in C:\Projects\microorm-comparison\CodeSlice.Data\CodeSlice.Data\Profilers\ProfileInsert.cs:line 18
at CodeSlice.Data.Program.Main(String[] args) in C:\Projects\microorm-comparison\CodeSlice.Data\CodeSlice.Data\Program.cs:line 24
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
Trying to solve an issue (losing milliseconds on DateTime in SQLite database) using a TypeHandler that do conversion between db string (in format "o") and DateTime doesn't pass test because typeMap[DateTime] is processed before custom type handlers.
Probably the solution is to invert the lookup sequence or add the chance for remove a default type map with a brother of
public static void AddTypeMap(Type type, DbType dbType)
as
public static void RemoveTypeMap(Type type)
This is not an issue, but since I have no clue how to submit an enhancement, here I go ...
I'd like to submit for your consideration the following Extension to Dapper:
/// <summary>
/// This extension converts an enumerable set to a Dapper TVP
/// </summary>
/// <typeparam name="T">type of enumerbale</typeparam>
/// <param name="enumerable">list of values</param>
/// <param name="typeName">database type name</param>
/// <param name="orderedColumnNames">if more than one column in a TVP, columns order must mtach order of columns in TVP</param>
/// <returns>a custom query parameter</returns>
public static SqlMapper.ICustomQueryParameter AsTableValuedParameter<T>(this IEnumerable<T> enumerable,
string typeName, IEnumerable<string> orderedColumnNames = null)
{
var dataTable = new DataTable();
if (typeof(T).IsValueType)
{
dataTable.Columns.Add("NONAME", typeof(T));
foreach (T obj in enumerable)
{
dataTable.Rows.Add(obj);
}
}
else
{
PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
PropertyInfo[] readableProperties = properties.Where(w => w.CanRead).ToArray();
if (readableProperties.Length > 1 && orderedColumnNames == null)
throw new ArgumentException("Ordered list of column names must be provided when TVP contains more than one column");
var columnNames = (orderedColumnNames ?? readableProperties.Select(s => s.Name)).ToArray();
foreach (string name in columnNames)
{
dataTable.Columns.Add(name, readableProperties.Single(s => s.Name.Equals(name)).PropertyType);
}
foreach (T obj in enumerable)
{
dataTable.Rows.Add(
columnNames.Select(s => readableProperties.Single(s2 => s2.Name.Equals(s)).GetValue(obj))
.ToArray());
}
}
return dataTable.AsTableValuedParameter(typeName);
}
Sample uses:
var l = new List<AUDITRECORD>
{
new AUDITRECORD {AUDIT_PK = 2, MESSAGE = "HELO", SUCCESS = true},
new AUDITRECORD {AUDIT_PK = 3, MESSAGE = "EHLO", SUCCESS = false}
};
using (var conn = new SqlConnection(connString))
{
conn.Open();
conn.Execute("TestOne",
new { TVP = l.AsTableValuedParameter("AuMine.AUDITRECORD", new[] { "AUDIT_PK", "MESSAGE", "SUCCESS" }) },
commandType: CommandType.StoredProcedure);
}
var customerKeys = new List<long>{3,5,7};
var productKeys = new List<long>{11,33,55};
using (var conn = new SqlConnection("Data Source=.;Initial Catalog=Scratch;Integrated Security=true"))
{
conn.Open();
conn.Execute("TestTwo",
new
{
customerKeys = customerKeys.AsTableValuedParameter("dbo.TVPBIGINT"),
productKeys = productKeys.AsTableValuedParameter("dbo.TVPBIGINT")
},
commandType: CommandType.StoredProcedure);
}
While using the custom type handler ITypeHandler, the DbType of the parameter is defaulted to DbType.Object (SqlMapper.cs, L716).
This value overrides any changes made in the custom TypeHandler (SqlMapper.cs, L4026).
It would be great to be able to set the DbType in ITypeHandler.SetValue.
In my case, I would like to be able to save a specific object as a varchar (DbType.String) in the database (c.f. the example below).
The issue has been tested using MS SQLServer 2008.
Below is a sample of the code that produces the error.
Error message: System.Data.SqlClient.SqlException: Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.
public class Demo
{
public static void Main(string[] args)
{
// Define the dynamic parameter.
DynamicParameters dynamicParameters = new DynamicParameters();
dynamicParameters.Add("@Bar", new Foo("Goo"));
// Add the custom mapper.
SqlMapper.AddTypeHandler<Foo>(new FooTypeHandler());
// Execute the insert statement.
SqlConnection conn = new SqlConnection(args[0] /* Connection string */);
conn.Execute("INSERT INTO Foo (Bar) VALUES (@Bar)", dynamicParameters);
}
/// <summary>
/// Empty class with one string property.
/// </summary>
class Foo
{
public string Bar { get; set; }
public Foo(string value)
{
this.Bar = value;
}
}
/// <summary>
/// Very basic TypeHandler.
/// </summary>
class FooTypeHandler : SqlMapper.TypeHandler<Foo>
{
public override Foo Parse(object value)
{
return new Foo(value.ToString());
}
public override void SetValue(IDbDataParameter parameter, Foo value)
{
parameter.Value = value.Bar;
// This value is overridden later on.
parameter.DbType = DbType.String;
}
}
}
This may be by design, but I am finding that if a returned value from the database is null, the property setter on the object is not being invoked. For my use case (ActiveRecord-style objects) this is causing some issues when I want to have something like the following:
public class Ticket
{
public int? AssignedTo
{
get
{
return _currentValue;
}
set
{
if (!_init)
{
_initialValue= value;
_init = true;
}
_currentValue = value;
}
}
}
If I run a query like so...
var sql = @"
SELECT t.*, a.UserId AS AssignedTo
FROM dbo.Tickets t
LEFT JOIN dbo.vw_MostRecentAssignees a
ON a.TicketId = t.TicketId
WHERE t.TicketId = @ticketId
";
var ticket = conn.Query<Ticket>(sql, new { ticketId }).SingleOrDefault();
...and that particular ticket has no current assignee, then I can't track changes to that property.
I can do something like this:
var sql = @"
SELECT t.*, 1 AS splitHere, a.UserId AS AssignedTo
FROM dbo.Tickets t
LEFT JOIN dbo.vw_MostRecentAssignees a
ON a.TicketId = t.TicketId
WHERE t.TicketId = @ticketId
";
var ticket = conn.Query<Ticket, dynamic, Ticket>(sql, (t, d) =>
{
t.AssignedTo = d.AssignedTo;
return t;
}, new { ticketId }, splitOn: "splitHere").SingleOrDefault();
...but that's not very pretty. ;)
Hi,
Issue could be related to #130 and affects recent version of Dapper.
To reproduce:
// Entity has dynamic Id
public class Dyno
{
public dynamic Id { get; set; }
public string Name { get; set; }
}
// Test case
[Fact]
public void Insert_EntityWithDynamicGuidId_Ok()
{
using (var conn = GetConnection())
{
conn.Open();
try
{
conn.Execute("drop table [Dyno];");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
conn.Execute("create table [Dyno] ([Id] uniqueidentifier primary key, [Name] nvarchar(50) not null);");
var result = conn.Execute("insert into [Dyno] ([Id], [Name]) values (@Id, @Name);", new Dyno { Name = "T Rex", Id = Guid.NewGuid() }, commandType: CommandType.Text);
Assert.Equal(1, result);
conn.Close();
conn.Dispose();
}
}
Interestingly, this works if I pass in an anonymous type parameter, ie:
var result = conn.Execute("insert into [Dyno] ([Id], [Name]) values (@Id, @Name);", new { Name = "T Rex", Id = Guid.NewGuid() }, commandType: CommandType.Text);
With this new release I am getting An exception when quering sql server database. The issue appears to be in IEnumerable QueryImpl(...) output conversion process. To my 1st look the parser block smells. The 1st and the last case do the same yet they are in different blocks. Perhaps the the 1st case was supposed to simply return val?
Current:
if (effectiveType == typeof(object))
{
yield return (T)Convert.ChangeType(val, effectiveType);
} else if (val == null || val is T) {
yield return (T)val;
} else {
yield return (T)Convert.ChangeType(val, effectiveType);
}
New:
if (effectiveType == typeof(object))
{
yield return val;
} else if (val == null || val is T) {
yield return (T)val;
} else {
yield return (T)Convert.ChangeType(val, effectiveType);
}
Error:
An exception of type 'System.InvalidCastException' occurred in mscorlib.dll but was not handled in user code
Additional information: Object must implement IConvertible.
This code succeeds in raw ADO.net Executereader(), but fails in dapper Executereader().
IDataReader.Read() throws a System.InvalidOperationException exception with the message:
Invalid attempt of read when the reader is closed.
using System;
using System.Data;
using Dapper;
using FirebirdSql.Data.FirebirdClient;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
var cs = @"initial catalog=localhost:database;user id=SYSDBA;password=masterkey";
var sql = @"select count(*) from user";
var con = new FbConnection(cs);
con.Open();
// raw ADO.net
var sqlCmd = new FbCommand(sql, con);
IDataReader reader1 = sqlCmd.ExecuteReader();
while (reader1.Read())
{
Console.WriteLine(reader1[0].ToString()); // output is 10
}
// dapper
var reader2 = con.ExecuteReader(sql);
while (reader2.Read()) // System.InvalidOperationException. "Invalid attempt of read when the reader is closed."
{
Console.WriteLine(reader2[0].ToString());
}
Console.Read();
}
}
}
Stack trace:
System.InvalidOperationException was unhandled by user code.
HResult=-2146233079
Message=Invalid attempt of read when the reader is closed.
Source=FirebirdSql.Data.FirebirdClient
StackTrace:
at FirebirdSql.Data.FirebirdClient.FbDataReader.CheckState()
at FirebirdSql.Data.FirebirdClient.FbDataReader.Read()
at ConsoleApplication2.Program.Main(String[] args) at c:\Users\aoki\Desktop\ConsoleApplication2\ConsoleApplication2\Program.cs:行 33
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
I'm currently trying to decide whether or not to fork and modify Dapper to accomplish something that isn't really possible now and that is to be able to modify the command text of a query.
The situation: we have a fairly large existing code base that uses Dapper and we need to have the ability to modify our queries to be context dependent. That is, if a query is currently select * from Products where ID = @id
we want to be able to have that (depending on the current context) get transformed into select * from Products_en where ID = @id
when the current user's language is set to English. First of, no that can't be accomplished by adding a language filter in the where-clause since the context I'm using here is greatly simplified, they really are physically different tables :) I think we really need a dynamic query in our case.
It's inevitable that we'll have to modify each and every query that currently touches the Products table; we'll have to replace every instance of from Products
with something like from [[PRODUCTS]]
. However, while we could do the actual context dependent replace inside something like connection.Query<int>(QueryProcessor.Process("select count(*) from [[PRODUCTS]]"))
I prefer not having to do this for each query as it's something that can be easily forgotten when writing a query and it would be a maintenance nightmare if we ever to decide to change the method signature, having to modify 300 references to that method.
So that's where modifying Dapper comes in: would it be a good idea to add a SqlMapper.AddCommandTextProcessor(ICommandTextProcessor processor)
? With ICommandTextProcessor
being defined as:
public interface ICommandTextProcessor
{
string Process(string commandText);
}
If one has been defined, it would then be used inside the CommandDefinition type's constructor to do something with the query. In our case, it would be replacing the placeholders with a proper implementation.
Of course, if I make this modification it would be preferable if it was one that could be merged back to the mainline :) So I'm open to any suggestions here.
Hi~
I've encountered a crash when trying to read parameter value from a struct object.
A test case would be like:
struct Car { /* from Tests\Tests.cs */ }
public void TestStructAsParam()
{
var car1 = new Car() { Name = "Ford", Age = 21, Trap = Car.TrapEnum.B };
var car2 = connection.Query<Car>("select @Name Name, @Age Age, @Trap Trap", car1).First();
car2.Name.IsEqualTo(car1.Name);
car2.Age.IsEqualTo(car1.Age);
car2.Trap.IsEqualTo(car1.Trap);
}
The generated ParamReader will throw a System.InvalidProgramException
while setting up command.
System.InvalidProgramException: Common Language Runtime detected an invalid program.
StackTrace:
ParamInfo47fc3058-e009-477d-8e7e-64bc77e90be6(IDbCommand , Object )
Dapper.CommandDefinition.SetupCommand(IDbConnection cnn, Action`2 paramReader)
Dapper.SqlMapper.<QueryImpl>d__11`1.MoveNext()
I am using Dapper. We are not trying to use any of the MultiMapping features . I am trying to figure out why this one proc would return that error? What should I check? What should I look for?
Error: When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id
Parameter name: splitOn
Code:
SELECT * FROM [User] WHERE ID=96569
If I add UPPER keyword around a column and query with Dapper that column comes back null, without it isn't null.
select id, UPPER(column) from T - Column is null on result set.
select id, column from T - Column is not null on result set.
When a dapper sql command fails, I want to log the command along with the dynamic parameters. Something like
private static void LogCommand(string cmd, DynamicParameters dp)
{
var dpl = dp as SqlMapper.IParameterLookup;
Debug.WriteLine(cmd);
Debug.WriteLine("Parameters: " +
String.Join(", ",
from pn in dp.ParameterNames
select string.Format("@{0}={1}", pn, dpl[pn])));
}
Is this a good approach? Any alternative, maybe built in logging support?
What steps will reproduce the problem?
What is the expected output? The corresponding database row should be updated.
What do you see instead? An error message saying that the property created in step 2, cannot be updated.
What version of the product are you using? - latest one from May 2014.
On what operating system? Windows 7.
Please provide any additional information below.
Please fix the line 243 of SqlMapperExtensions.cs:
from
var nonIdProps = allProperties.Where(a => !keyProperties.Contains(a));
to
var computedProperties = ComputedPropertiesCache(type);
var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties));
The ability to turn off arrays support has been removed on version 1.31
FeatureSupport.Get(cn).Arrays = false;
This doesn't work anymore since the property can only be set privately. I wonder the reason behind this.
There are some queries in PostGreSQL in which it is convenient to turn array support off.
Thanks.
Diego
If you register a custom type handler for a value type but do not register a type handler for the nullable version then a NullReferenceException can be thrown.
The NullReferenceException is thrown when the nullable custom type is used as a parameter value, and the parameter is not null.
The NullReferenceException is thrown from TypeHandlerCache<T>.SetValue
because no handler has been registered for the nullable version.
A type handler registered for a value type should support nulls automatically.
The application has a value type LocalDate
that has been registered using the following.
SqlMapper.AddTypeHandler(typeof(LocalDate), LocalDateHandler.Default)
See https://gist.github.com/chilversc/994509807c177259e77b for a complete example.
CreateParamInfoGenerator line 2580
DbType dbType = LookupDbType(prop.PropertyType, prop.Name, out handler);
Assume that the property type is Nullable<LocalDate>
and the value of the property is not null.
if (typeHandlers.TryGetValue(type, out handler))
The parameter type
has been changed from Nullable<LocalDate>
to LocalDate
.
This finds the handler for LocalDate
that was registered.
CreateParamInfoGenerator line 2683
if (handler != null)
{
il.Emit(OpCodes.Call, typeof(TypeHandlerCache<>).MakeGenericType(prop.PropertyType).GetMethod("SetValue")); // stack is now [parameters] [[parameters]] [parameter]
}
The test passes and IL is generated to call TypeHandlerCache<Nullable<LocalDate>>.SetValue
Later, when issuing a query that has a non-null, Nullable<LocalDate>
the application will throw a NullReferenceException
from TypeHandlerCache.SetValue line 667 because no handler has been registered for Nullable<LocalDate>
.
The handler that was found was for TypeHandlerCache<LocalDate>.SetValue
There is a disparity between the SetValue
and Parse
methods of ITypeHandler
. Parse
is never called with a null value while SetValue
is. Ideally I think these methods should have parity, and that SetValue
should not be called if the value is null. Instead there should be a null check before calling SetValue
.
This will break existing code that is performing custom logic if the value is null, instead of just setting the parameter to DBNull.
When calling AddTypeHandler
, if the type is a value type automatically register the nullable version of the type.
A breaking change is that if a client manually registers a different nullable custom type handler before registering a non-nullable handler the nullable handler will be replaced.
There is no issue with a custom handler's SetValue
being unexpectedly called with a value of DBNull because the application would have crashed with a NullReferenceException
requiring the developer to either use a non-nullable parameter or register a nullable custom type handler.
There is a small edge case if a client calls AddTypeHandler(typeof(LocalDate), null)
. SqlMapper will remove both the nullable and non-nullable handlers, even if the nullable handler was separately registered. I think this is a rare case and unlikely to break existing clients.
Keep the behaviour as is, but detect if handler ais null and the type is nullable. When this condition occurs throw a more detailed error message including a hint to register the nullable handler.
This has the least impact on clients by not changing the behaviour but makes it much easier for the developer to understand what has gone wrong and fix it.
I think automatically registering a nullable type handler is the best solution. It has minimal impact on existing clients while matching the expected behaviour.
The issue with overwriting an existing nullable handler is small. Having different nullable and non-nullable handlers for the same type isn't really supported as there are several places where the non-nullable version will be used even if a nullable version is registered, such as DynamicParameters.AddParameters line 4043.
Register the custom handler twice, once for the non-nullable version, and once for the non-nullable, e.g.
SqlMapper.AddTypeHandler(typeof(LocalDate), LocalDateHandler.Default);
SqlMapper.AddTypeHandler(typeof(LocalDate?), LocalDateHandler.Default);
Could you please increase count of input parameters up to 16, like it implemented by Microsoft BCL (for example Action<T1, ,T16> delegate)
Just cosmetics, but File Version and Product Version for all dll's are still 1.25.
Dapper generates SQL for Oracle when passing empty IEnumerables to an IN() clause:
var param = new List<string>();
param.Add("PS"); // Add one or more entries
var bar = Database.Query<mytable>(ConnectInfo,
@"SELECT * FROM mytable WHERE myvarchar2 IN :param", new {param});
...executes without issue, but:
var param = new List<string>();
// param.Add("PS");
var bar = Database.Query<mytable>(ConnectInfo,
@"SELECT * FROM mytable WHERE myvarchar2 IN :param", new {param});
Fails with: ORA-00923: FROM keyword not found where expected
See my SO question for more details:
http://stackoverflow.com/questions/26877297/dapper-sql-generation-bug-oracle-and-the-sqls-in-clause
One of the projects I have been maintaining occasionally ends up using a large lists of IDs which then get turned into large amount of parameters. As such, I've been doing some initial poking around to see if it was worth switching over to TVPs instead of just using the 'IN' support.
What I found is that after a ~75 parameters or so TVPs become faster. As the numbers grow, it becomes a massive difference; To the point where once you get to 1000 you being talking about orders of magnitude.
What I also found is that in terms of execution speed there isn't very much difference. DataTables might be very slightly slower, but it's within the margin of error. However, the IEnumerable<int
> into a DataTable takes much longer than it does to an List<SqlDataRecord
>. With a test of 1000 ints, it takes twice as long to make the DataTable as it does the List. (Ignoring some 100% reproducible weirdness, where generating the DataTable takes 50x as long in one step of the benchmark. However, if I copy paste that step, then the copy pasted section runs without speed again. This 50x delay makes it almost as long as the 'IN parameter' dapper code. I thought it might be GC, but I am forcing it do collection between steps, and it doesn't seem to help.)
This generation speed seems to hold true for the current Dapper TVP support. If I create class inheriting IDynamicParameters, and use it to generate and pass a List<SqlDataRecord
> to Dapper, it runs faster than just passing the DataTable as a parameter. The gap in speed aligns pretty well with the difference in generation methods I measured in the hand coded tests. However, the method I am using to do this restricts me to only sending the one parameter, so it's obviously not ideal.
So the question is, before I start trying to schedule time in the future to dig into the core of Dapper. Is there technical reason that makes supporting List<SqlDataRecord
> difficult?
The current version of Dapper.Contrib published to NuGet (1.0) requires a transaction which doesn't match the documentation. When used normally, you will get the following error on insert:
System.InvalidOperationExceptionExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.
Please update NuGet with the latest version which fixes this issue.
string sql = @"UPDATE mytalbe SET Name=:name WHERE ID=:_id";
var r = conn.Execute(sql, new { _id=123,name="test"}
run this code,throw exception:ORA-00911:invalid character
I found ,change _id to id,it is work fine.
BTW:I useOracle Data Provider for .NET (ODP.NET) Managed Driver 121.1.0
The Insert method does not emit the entity property marked as [Key] during inserts. This makes it unsuitable for cases where the Id column is generated by the application.
Upgraded Dapper from 1.26 to 1.34. Am getting this error wherever I pass Table Valued parameters to a stored procedure (using SQL Azure)
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 7 : Data type 0x62 (sql_variant) has an invalid type for type-specific metadata.
What have I missed out on??
From a clean build, I have to add a reference to System.Xml to Dapper NET35, 40 and 45 for the solution to build.
To run DapperTests NET40 I also needed FSharp.Core 4.0.0 (I have 4.3, apparently) so I had to add a reference to the 4.0.0 nuget package.
Is this just me, or would this be useful to submit a PR for?
If I use dapper to execute query like below:
var result = con.Query("SELECT * FROM table WHERE col = @col", new { col = "123" });
I get ora-00936
exception but if I do like below it works fine:
var result = con.Query(String.Format("SELECT * FROM table WHERE col = '{0}'", "123"));
Any ideas to why?
Please make all methods and classes public and all methods marked with virtual so I can extend them easily. There are tools you can use to do this automatically for you at compile time I believe. This is an API and I don't want to compile my own version so I can get to the definition of Dapper.SqlMapper.DapperRow for instance. There isn't any reason to make things private since all the source code is open source.
what difference between standard parameter query and Literal replacement ?
string sql = "select count(1) from user where lgtd={=lgtd}";
string sql = "select count(1) from user where lgtd=@lgtd";
The bug is detailed in s StackOverflow question I posted:
http://stackoverflow.com/questions/26561451/dapper-extensions-object-of-type-system-int32-cannot-be-converted-to-type-s/26563263#26563263
Hi All,
In our most recent project, we would like to use the CRUD helpers from Dapper.Contrib in an async fashion. Therefore we have put together an async version of SqlMapperExtensions.cs.
https://gist.github.com/AdamAtDotNet/4547584d35d038cda13a
Is this something you planned, or would be interested in having incorporated in the project?
Kind regards,
Adam
See comment in 3691fe1.
Hi!
I'm getting an exception of type "System.InvalidOperationException" with message "Invalid attempt to call FieldCount when reader is closed."
Additional information:
Source: "System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.get_FieldCount()
at Dapper.SqlMapper.GetColumnHash(IDataReader reader) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 468
at Dapper.SqlMapper.d__148.MoveNext() in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1719 at System.Collections.Generic.List
1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable
1 source)
at Implementation.UserRepository.d__1b.MoveNext() in UserRepository.cs:line 217
It's reproducing only on async queries with buffered parameter set to false. If I set true, every thing is fine.
When I was trying to insert a large size of string into oracle clob type, I will see exception : Specified argument was out of the range of valid values.
Hi guys,
I found an error for listed parameters:
First create a table like this:
CREATE TABLE DapperTest (
Field INT PRIMARY KEY IDENTITY,
Field_1 INT
);
Now use the following Query:
IEnumerable<dynamic> result = con.Query(
"SELECT * FROM dbo.DapperTest WHERE Field IN @Field AND Field_1 IN @Field_1",
new { Field = new[] { 1, 2 }, Field_1 = new[] { 2, 3 } });
You get the following exception:
{"Incorrect syntax near '_1'."}
Grabbed from SQL Profiler:
exec sp_executesql N'SELECT * FROM dbo.DapperTest WHERE Field IN (@Field1,@Field2) AND Field_1 IN (@Field1,@Field2)_1',N'@Field1 int,@Field2 int,@Field_11 int,@Field_12 int',@Field1=1,@Field2=2,@Field_11=2,@Field_12=3
As you can see Dapper renames all "@field" occurences regardless if the string ends there.
I had the same issue in my own SqlBuilder library and I replaced the exact string replacement with a replacement of the position and length given by System.Text.RegularExpressions.Match
class. Maybe that helps.
Please add support for Nodatime
Hi
I am developing one application using dapper dot net, need to get collection of objects where columns are configurable, when i tried ienumerable dynamic list, i am getting list of Key Value Pair of records
But i need list of records with columns (as we get in strong type ienumerable collection)
I tried below code:
IEnumerable tickets = null;
using (var dbcon = new DBContext())
{
tickets = dbcon.Connection.Query(" Select top 100 Ticket_ID,Ticket_No,Status,Reg_Time from IM_TicketMaster where sup_Function=@instance", new { Instance = new DbString { Value = "IT" } });
}
the result is IEnumerable collection of DapperRow, when i convert this collection to json, it gives me a collection of key value pair, how do i convert this dynamic list to strong type collection.
Is there any proper way to get List of dynamic json objects instead of key value collection?
I've been using Dapper for about a month or so without any issues, but I ran into an issue today with the following code:
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConnection"].ConnectionString))
{
connection.Open();
const string sql = @"select TOP(1) * from Order
order by TimeOfOrderUtc desc";
var order = connection.Query<Order>(sql).FirstOrDefault();
return order;
}
This is returning the following:
System.Data.DataExceptionError parsing column 41 (TableNumber=Invalid attempt to read from column ordinal '41'. With CommandBehavior.SequentialAccess, you may only read from column ordinal '42' or greater.)
I use ExpandoObject for initialize Execute() method parameters. If take a look at SqlMapper.Execute() method, multiExec enabled for IEnumeable param, but it's unexpected behavior with ExpandoObject. Please, refine conditions like this:
if (multiExec != null && !(multiExec is string) &&
// additional conditions:
!(multiExec is IDynamicMetaObjectProvider) &&
!(multiExec is IEnumerable<KeyValuePair<string, object>>))
{
// execute as multiExec
}
I have a stored procedure that takes in a number of parameters and returns two separate recordsets. It is called using this code
var queryObject = Connection.QueryMultiple("storedprocname", parameterObject,
commandType: CommandType.StoredProcedure);
var resultObject1 = queryObject.Read<resultType1>();
var resultObject2 = queryObject.Read<resultType2>();
If both recordsets contain 0 rows, the following exception occurs when attempting the first Read call above.
Message "When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id\r\nParameter name: splitOn"
Source "Dapper"
StackTrace " at Dapper.SqlMapper.GetTypeDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 3264\r\n at Dapper.SqlMapper.GetDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1945\r\n at Dapper.SqlMapper.GridReader.ReadImpl[T](Type type, Boolean buffered) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 3835\r\n at Dapper.SqlMapper.GridReader.Read[T](Boolean buffered) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 3808\r\n . ....
TargetSite {System.Func`2[System.Data.IDataReader,System.Object] GetTypeDeserializer(System.Type, System.Data.IDataReader, Int32, Int32, Boolean)} System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}
Running version 1.34 of Dapper, dated 11/09.2014 12:14.
.NET Framework v4.5
Provider = System.Data.SqlClient
SQL Server 11.0.5058
public class Person { public string Id { get; set; } /* etc... */ }
var result = cnn.Query(@"select top 1 * from Persons").ToList().First();
if Id is defined in the DB as uniqueidentifier, then the error message reads:
Error parsing column 0 (Owner=Invalid attempt to read from column ordinal '0'. With CommandBehavior.SequentialAccess, you may only read from column ordinal '1' or greater.)
IMO this should just work and populate the string Id (this would be consistent with how it works when saving - you can write a string into an uniqueidentifier column)
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.