Parameter class for Sql data access

[Guest]

Hello all,

I'm writing a Parameter class (following Fowler's "Introduce Parameter Object") that accepts a stored procedure and key-value pairs to be passed to a data access class that will parse it to do a generalized query to a database.

In particular, I want to retrieve the stored procedure using an accessor, and be able to iterate through the SqlParameters (stored as an internal StringDictionary)using a foreach loop.

Here's the method I have so far (with extra testing stuff for Snippet Compiler):

using System;
using System.Collections;
using System.Collections.Specialized;

public class Parameters
{
private string _storedProcedure;
private StringDictionary _storedProcedureParameters;

internal Parameters()
{
_storedProcedure = null;
StringDictionary _storedProcedureParameters = new StringDictionary();

}

internal string StoredProcedure
{
get
{
return _storedProcedure;
}
set
{
_storedProcedure = value;
}
}


internal ICollection Keys
{
get
{
return _storedProcedureParameters.Keys;
}
}

internal ICollection Values
{
get
{
return _storedProcedureParameters.Values;
}
}

internal void Add(string key, string value)
{
_storedProcedureParameters.Add(key, value);
}

internal void Remove(string key)
{
_storedProcedureParameters.Remove(key);
}

internal void Clear()
{
_storedProcedure = null;
_storedProcedureParameters.Clear();
}


public static void Main()
{
// Create and initialize new Parameters object
Parameters parameters = new Parameters();
parameters.StoredProcedure = "usp_LookupUserByDept";
parameters.Add("@dept", "030000");
parameters.Add("@UserName", "adam");
PrintKeysAndValues(parameters);
}

private static void WL(string text, params object[] args)
{
Console.WriteLine(text, args);
}

private static void RL()
{
Console.ReadLine();
}

private static void Break()
{
System.Diagnostics.Debugger.Break();
}

public static void PrintKeysAndValues( Parameters passedParameters )
{
WL(passedParameters.StoredProcedure);
}

}

Compiling produces the following:

An unhandled exception of type 'System.NullReferenceException' occurred in output.exe

Additional information: Object reference not set to an instance of an object.

I'm clearly missing something conceptually.

 

[Nicholas Paldino [.NET/C# MVP]]

Adam,

The error arises because you are not setting _storedProcedureParameters
to a new instance of the StringDictionary when the class is created. You
have this line:
StringDictionary _storedProcedureParameters = new StringDictionary();

in the constructor, but that sets a local variable, not the class level.
Change it to this:

_storedProcedureParameters = new StringDictionary();

and it will work.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hello all,

I'm writing a Parameter class (following Fowler's "Introduce Parameter

Object") that accepts a stored procedure and key-value pairs to be passed to
a data access class that will parse it to do a generalized query to a
database.

In particular, I want to retrieve the stored procedure using an accessor,

and be able to iterate through the SqlParameters (stored as an internal
StringDictionary)using a foreach loop.

Here's the method I have so far (with extra testing stuff for Snippet Compiler):

using System;
using System.Collections;
using System.Collections.Specialized;

public class Parameters
{
private string _storedProcedure;
private StringDictionary _storedProcedureParameters;

internal Parameters()
{
_storedProcedure = null;
StringDictionary _storedProcedureParameters = new StringDictionary();

}

internal string StoredProcedure
{
get
{
return _storedProcedure;
}
set
{
_storedProcedure = value;
}
}


internal ICollection Keys
{
get
{
return _storedProcedureParameters.Keys;
}
}

internal ICollection Values
{
get
{
return _storedProcedureParameters.Values;
}
}

internal void Add(string key, string value)
{
_storedProcedureParameters.Add(key, value);
}

internal void Remove(string key)
{
_storedProcedureParameters.Remove(key);
}

internal void Clear()
{
_storedProcedure = null;
_storedProcedureParameters.Clear();
}


public static void Main()
{
// Create and initialize new Parameters object
Parameters parameters = new Parameters();
parameters.StoredProcedure = "usp_LookupUserByDept";
parameters.Add("@dept", "030000");
parameters.Add("@UserName", "adam");
PrintKeysAndValues(parameters);
}

private static void WL(string text, params object[] args)
{
Console.WriteLine(text, args);
}

private static void RL()
{
Console.ReadLine();
}

private static void Break()
{
System.Diagnostics.Debugger.Break();
}

public static void PrintKeysAndValues( Parameters passedParameters )
{
WL(passedParameters.StoredProcedure);
}

}

Compiling produces the following:

An unhandled exception of type 'System.NullReferenceException' occurred in output.exe

Additional information: Object reference not set to an instance of an object.

I'm clearly missing something conceptually.