Skip to content

Commit 1376bd0

Browse files
tcartwrighttcartwright
committed
Fixed a bug with using stored procedures that have output parameters with default values. When sending in null or DBNull.Value you get:
Exception "the Size property has an invalid size of 0."
1 parent 6d4105d commit 1376bd0

3 files changed

Lines changed: 47 additions & 43 deletions

File tree

‎Simple.Data.Ado/ProcedureExecutor.cs‎

Lines changed: 28 additions & 42 deletions
Original file line numberDiff line numberDiff line change
@@ -7,75 +7,60 @@
77
using Simple.Data.Ado.Schema;
88
using ResultSet = System.Collections.Generic.IEnumerable<System.Collections.Generic.IDictionary<string, object>>;
99

10-
namespace Simple.Data.Ado
11-
{
12-
public interface IProcedureExecutor
13-
{
10+
namespace Simple.Data.Ado {
11+
public interface IProcedureExecutor {
1412
IEnumerable<ResultSet> Execute(IDictionary<string, object> suppliedParameters);
1513
IEnumerable<ResultSet> ExecuteReader(IDbCommand command);
1614
}
1715

18-
public class ProcedureExecutor : IProcedureExecutor
19-
{
16+
public class ProcedureExecutor : IProcedureExecutor {
2017
private const string SimpleReturnParameterName = "@__Simple_ReturnValue";
2118

2219
private readonly AdoAdapter _adapter;
2320
private readonly ObjectName _procedureName;
2421
private Func<IDbCommand, IEnumerable<ResultSet>> _executeImpl;
2522

26-
public ProcedureExecutor(AdoAdapter adapter, ObjectName procedureName)
27-
{
23+
public ProcedureExecutor(AdoAdapter adapter, ObjectName procedureName) {
2824
_adapter = adapter;
2925
_procedureName = procedureName;
3026
_executeImpl = ExecuteReader;
3127
}
3228

33-
public IEnumerable<ResultSet> Execute(IDictionary<string, object> suppliedParameters)
34-
{
29+
public IEnumerable<ResultSet> Execute(IDictionary<string, object> suppliedParameters) {
3530
var procedure = _adapter.GetSchema().FindProcedure(_procedureName);
36-
if (procedure == null)
37-
{
31+
if (procedure == null) {
3832
throw new UnresolvableObjectException(_procedureName.ToString());
3933
}
4034

4135
using (var cn = _adapter.CreateConnection())
42-
using (var command = cn.CreateCommand())
43-
{
36+
using (var command = cn.CreateCommand()) {
4437
command.CommandText = procedure.QualifiedName;
4538
command.CommandType = CommandType.StoredProcedure;
4639
SetParameters(procedure, command, suppliedParameters);
47-
try
48-
{
40+
try {
4941
var result = _executeImpl(command);
5042
if (command.Parameters.Contains(SimpleReturnParameterName))
51-
suppliedParameters["__ReturnValue"] = command.Parameters.GetValue(SimpleReturnParameterName);
43+
suppliedParameters["__ReturnValue"] = command.Parameters.GetValue(SimpleReturnParameterName);
5244
RetrieveOutputParameterValues(procedure, command, suppliedParameters);
5345
return result;
54-
}
55-
catch (DbException ex)
56-
{
46+
} catch (DbException ex) {
5747
throw new AdoAdapterException(ex.Message, command);
5848
}
5949
}
6050
}
6151

62-
private static void RetrieveOutputParameterValues(Procedure procedure, IDbCommand command, IDictionary<string, object> suppliedParameters)
63-
{
64-
foreach (var outputParameter in procedure.Parameters.Where(p => p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output))
65-
{
52+
private static void RetrieveOutputParameterValues(Procedure procedure, IDbCommand command, IDictionary<string, object> suppliedParameters) {
53+
foreach (var outputParameter in procedure.Parameters.Where(p => p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)) {
6654
suppliedParameters[outputParameter.Name.Replace("@", "")] =
6755
command.Parameters.GetValue(outputParameter.Name);
6856
}
6957
}
7058

71-
public IEnumerable<ResultSet> ExecuteReader(IDbCommand command)
72-
{
59+
public IEnumerable<ResultSet> ExecuteReader(IDbCommand command) {
7360
command.WriteTrace();
7461
command.Connection.Open();
75-
using (var reader = command.ExecuteReader())
76-
{
77-
if (reader.FieldCount > 0)
78-
{
62+
using (var reader = command.ExecuteReader()) {
63+
if (reader.FieldCount > 0) {
7964
return reader.ToMultipleDictionaries();
8065
}
8166

@@ -85,40 +70,41 @@ public IEnumerable<ResultSet> ExecuteReader(IDbCommand command)
8570
}
8671
}
8772

88-
private static IEnumerable<ResultSet> ExecuteNonQuery(IDbCommand command)
89-
{
73+
private static IEnumerable<ResultSet> ExecuteNonQuery(IDbCommand command) {
9074
command.WriteTrace();
9175
Trace.TraceInformation("ExecuteNonQuery", "Simple.Data.SqlTest");
9276
command.Connection.Open();
9377
command.ExecuteNonQuery();
9478
return Enumerable.Empty<ResultSet>();
9579
}
9680

97-
private static void SetParameters(Procedure procedure, IDbCommand cmd, IDictionary<string, object> suppliedParameters)
98-
{
99-
if (procedure.Parameters.Any(p=>p.Direction == ParameterDirection.ReturnValue))
100-
AddReturnParameter(cmd);
81+
private static void SetParameters(Procedure procedure, IDbCommand cmd, IDictionary<string, object> suppliedParameters) {
82+
if (procedure.Parameters.Any(p => p.Direction == ParameterDirection.ReturnValue))
83+
AddReturnParameter(cmd);
10184

10285
int i = 0;
103-
foreach (var parameter in procedure.Parameters.Where(p=>p.Direction != ParameterDirection.ReturnValue))
104-
{
86+
foreach (var parameter in procedure.Parameters.Where(p => p.Direction != ParameterDirection.ReturnValue)) {
10587
object value;
106-
if (!suppliedParameters.TryGetValue(parameter.Name.Replace("@", ""), out value))
107-
{
88+
if (!suppliedParameters.TryGetValue(parameter.Name.Replace("@", ""), out value)) {
10889
suppliedParameters.TryGetValue("_" + i, out value);
10990
}
91+
11092
var cmdParameter = cmd.AddParameter(parameter.Name, value);
11193
cmdParameter.Direction = parameter.Direction;
94+
//Tim Cartwright: I added size and dbtype so inout/out params would function properly.
95+
//not setting the proper dbtype and size with out put parameters causes the exception: "Size property has an invalid size of 0"
96+
cmdParameter.DbType = parameter.Dbtype;
97+
cmdParameter.Size = parameter.Size;
11298
i++;
11399
}
114100
}
115101

116-
private static void AddReturnParameter(IDbCommand cmd)
117-
{
102+
private static void AddReturnParameter(IDbCommand cmd) {
118103
var returnParameter = cmd.CreateParameter();
119104
returnParameter.ParameterName = SimpleReturnParameterName;
120105
returnParameter.Direction = ParameterDirection.ReturnValue;
121106
cmd.Parameters.Add(returnParameter);
122107
}
108+
123109
}
124110
}

‎Simple.Data.Ado/Schema/Parameter.cs‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,8 @@ public class Parameter
1111
private readonly string _name;
1212
private readonly Type _type;
1313
private readonly ParameterDirection _direction;
14+
private int _size;
15+
private DbType _dbtype;
1416

1517
public Parameter(string name, Type type, ParameterDirection direction)
1618
{
@@ -19,6 +21,12 @@ public Parameter(string name, Type type, ParameterDirection direction)
1921
_type = type;
2022
}
2123

24+
public Parameter(string name, Type type, ParameterDirection direction, DbType dbtype, int size)
25+
: this(name, type, direction) {
26+
_dbtype = dbtype;
27+
_size = size;
28+
}
29+
2230
public ParameterDirection Direction
2331
{
2432
get { return _direction; }
@@ -33,5 +41,14 @@ public string Name
3341
{
3442
get { return _name; }
3543
}
44+
//Tim Cartwright: I added size and dbtype so inout/out params would function properly.
45+
public int Size {
46+
get { return _size; }
47+
}
48+
49+
public DbType Dbtype {
50+
get { return _dbtype; }
51+
}
52+
3653
}
3754
}

‎Simple.Data.SqlServer/SqlSchemaProvider.cs‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -81,8 +81,9 @@ public IEnumerable<Parameter> GetParameters(Procedure storedProcedure)
8181
connection.Open();
8282
SqlCommandBuilder.DeriveParameters(command);
8383

84+
//Tim Cartwright: I added size and dbtype so inout/out params would function properly.
8485
foreach (SqlParameter p in command.Parameters)
85-
yield return new Parameter(p.ParameterName, SqlTypeResolver.GetClrType(p.DbType.ToString()), p.Direction);
86+
yield return new Parameter(p.ParameterName, SqlTypeResolver.GetClrType(p.DbType.ToString()), p.Direction, p.DbType, p.Size);
8687
}
8788
}
8889
}

0 commit comments

Comments
 (0)