{"action":"closed","issue":{"url":"https://api.github.com/repos/Paymentsense/Dapper.SimpleSave/issues/53","labels_url":"https://api.github.com/repos/Paymentsense/Dapper.SimpleSave/issues/53/labels{/name}","comments_url":"https://api.github.com/repos/Paymentsense/Dapper.SimpleSave/issues/53/comments","events_url":"https://api.github.com/repos/Paymentsense/Dapper.SimpleSave/issues/53/events","html_url":"https://github.com/Paymentsense/Dapper.SimpleSave/issues/53","id":113567844,"number":53,"title":"\"System.DBNull cannot be used as a parameter value\" error sometimes when updating","user":{"login":"bartread","id":4139358,"avatar_url":"https://avatars.githubusercontent.com/u/4139358?v=3","gravatar_id":"","url":"https://api.github.com/users/bartread","html_url":"https://github.com/bartread","followers_url":"https://api.github.com/users/bartread/followers","following_url":"https://api.github.com/users/bartread/following{/other_user}","gists_url":"https://api.github.com/users/bartread/gists{/gist_id}","starred_url":"https://api.github.com/users/bartread/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bartread/subscriptions","organizations_url":"https://api.github.com/users/bartread/orgs","repos_url":"https://api.github.com/users/bartread/repos","events_url":"https://api.github.com/users/bartread/events{/privacy}","received_events_url":"https://api.github.com/users/bartread/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/Paymentsense/Dapper.SimpleSave/labels/bug","name":"bug","color":"fc2929"}],"state":"closed","locked":false,"assignee":{"login":"bartread","id":4139358,"avatar_url":"https://avatars.githubusercontent.com/u/4139358?v=3","gravatar_id":"","url":"https://api.github.com/users/bartread","html_url":"https://github.com/bartread","followers_url":"https://api.github.com/users/bartread/followers","following_url":"https://api.github.com/users/bartread/following{/other_user}","gists_url":"https://api.github.com/users/bartread/gists{/gist_id}","starred_url":"https://api.github.com/users/bartread/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bartread/subscriptions","organizations_url":"https://api.github.com/users/bartread/orgs","repos_url":"https://api.github.com/users/bartread/repos","events_url":"https://api.github.com/users/bartread/events{/privacy}","received_events_url":"https://api.github.com/users/bartread/received_events","type":"User","site_admin":false},"milestone":null,"comments":1,"created_at":"2015-10-27T12:04:52Z","updated_at":"2015-12-11T17:17:41Z","closed_at":"2015-12-11T17:17:41Z","body":"Migrated from SWT-347 in PaymentSense JIRA:\r\n\r\nSuggested solution in this StackOverflow post:\r\n\r\nhttp://stackoverflow.com/questions/11106825/how-to-pass-a-null-parameter-with-dapper\r\n\r\nProblem:\r\n\r\n2015-10-22 13:40:59,058 [32] INFO Dapper.SimpleSave.Impl.BasicSimpleSaveLogger [(null)] - { message = Executing script, sql = UPDATE [user].USER_MST\r\nSET [OfficeNumberGuid] = @p0\r\nWHERE [UserKey] = @p1;\r\n, parameters =\r\n{\"p0\":null,\"p1\":47}\r\n} \r\n2015-10-22 13:40:59,127 [32] ERROR Dapper.SimpleSave.Impl.BasicSimpleSaveLogger [(null)] -\r\n{ message = Error executing external transaction, exception = System.NotSupportedException: The member p0 of type System.DBNull cannot be used as a parameter value at Dapper.SqlMapper.LookupDbType(Type type, String name, Boolean demand, ITypeHandler& handler) in D:\\Dev\\dapper-dot-net\\Dapper NET40\\SqlMapper.cs:line 937 at Dapper.DynamicParameters.AddParameters(IDbCommand command, Identity identity) in D:\\Dev\\dapper-dot-net\\Dapper NET40\\SqlMapper.cs:line 4767 at Dapper.CommandDefinition.SetupCommand(IDbConnection cnn, Action`2 paramReader) in D:\\Dev\\dapper-dot-net\\Dapper NET40\\SqlMapper.cs:line 193 at Dapper.SqlMapper.ExecuteScalarImpl[T](IDbConnection cnn, CommandDefinition& command) in D:\\Dev\\dapper-dot-net\\Dapper NET40\\SqlMapper.cs:line 3423 at Dapper.SqlMapper.ExecuteScalar(IDbConnection cnn, CommandDefinition command) in D:\\Dev\\dapper-dot-net\\Dapper NET40\\SqlMapper.cs:line 1271 at Dapper.SimpleSave.SimpleSaveExtensions.ExecuteCommandForScript[T](IDbConnection connection, T oldRootObject, T newRootObject, Boolean softDelete, IDbTransaction transaction, Script script) at Dapper.SimpleSave.SimpleSaveExtensions.ExecuteScripts[T](IDbConnection connection, IList`1 scripts, T oldRootObject, T newRootObject, Boolean softDelete, IDbTransaction transaction) at Dapper.SimpleSave.SimpleSaveExtensions.ExecuteScriptsForTuples[T](IDbConnection connection, IEnumerable`1 oldAndNewObjects, IDictionary`2 scripts, Boolean softDelete, IDbTransaction transaction) at Dapper.SimpleSave.SimpleSaveExtensions.UpdateInternal[T](IDbConnection connection, IEnumerable`1 oldAndNewObjects, Boolean softDelete, IDbTransaction transaction), scripts = System.Collections.Generic.Dictionary`2[System.Tuple`2[PS.Mothership.Core.Common.Dto.User.UserDto,PS.Mothership.Core.Common.Dto.User.UserDto],System.Collections.Generic.IList`1[Dapper.SimpleSave.Impl.Script]] }\r\n\r\n2015-10-22 13:41:29,172 [36] INFO PS.Mothership.Api.Hubs.MothershipHub [(null)] - OnDisconnected ConnectionId: 1cc7498e-4676-4218-91a3-0ea6b23ce590, StopCalled: False \r\n2015-10-22 13:41:29,180 [107] INFO PS.Mothership.Api.Hubs.MothershipHub [(null)] - OnReconnected user , ConnectionId: 1cc7498e-4676-4218-91a3-0ea6b23ce590\r\n\r\nOccurs because Dapper itself cannot infer the type of the value. I'm unsure why it would need to but, whatever.\r\n\r\nThe only solution I can see is, rather than using ExpandoObject for the parameters, use Reflection.Emit to spew out a type on the fly with properties of the correct type and use that as the parameters.\r\n\r\nThis is a bit of nightmare because we'll need to keep a cache of types we create, otherwise performance is going to take a hit. It also side-steps any potential memory leak, although .NET 4 and later do support GCable dynamic assemblies so in theory that shouldn't be an issue anyway."}}