Re: Parameter Collection - C# Discussion Boards

11-Oct-07 22:15

how should i use the parametercollection class , i mean i have the following information like
Stored procedure Parameter Name
Stored procedure Parameter direction (input/output)
stored procedure Parameter value
stored procedure Parameter Type (decimal , string)
Now how should i use the parametercollection class , in order to insert this information i the add function

Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1

Sonia Gupta11-Oct-07 22:25

11-Oct-07 22:25

i tried the following code
class abc
{
ParameterCollection p;
public ParameterCollection ab
{
get
{
return p;
}

}
}
public class waste
{
public waste()
{
abc a;
a = new abc();
a.ab.Add("ParameterName", TypeCode.Decimal, "ParameterValue");
a.ab[0].Direction = ParameterDirection.Output;
a.ab[0].Size = 100;
}
}

can i improve the code?

Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1

Pete O'Hanlon11-Oct-07 23:16

11-Oct-07 23:16

I generally use a set of helper methods. Roughly, they do the following:

public static DbParameter Parameter<T>(string parameterName, DbType parameterType, T value, int size)
{
  DbParameter param = Parameter(parameterName, parameterType, value, ParameterDirection.Input);
  param.Size = size;
  return param;
}

public static DbParameter Parameter<T>(string parameterName, DbType parameterType, T value)
{
  return Parameter(parameterName, parameterType, value, ParameterDirection.Input);
}

public static DbParameter Parameter<T>(string parameterName, DbType parameterType, T value, ParameterDirection direction, int size)
{
  DbParameter param = Parameter(parameterName, parameterType, value, direction);
  param.Size = size;
  return param;
}

public static DbParameter Parameter<T>(string parameterName, DbType parameterType, T value, ParameterDirection direction)
{
  DbParameter param = new DbParameter();
  param.Direction = direction;
  param.Name = parameterName;
  param.DbType = parameterType;
  param.Value = value;
  return param;
}

Then, all you need to do is

cmd.Parameters.Add(Parameter("@ID", DbType.Int, 100));

Deja View - the feeling that you've seen this post before.

Brady Kelly11-Oct-07 23:43

Brady Kelly

11-Oct-07 23:43

Pete, that seems a lot of work for what I usually do:

<br />
int value1 = 10;<br />
cmd.Parameters.Add(new SqlParameter("@param1", value1));<br />

Or am I missing something?

I do not believe they are right who say that the defects of famous men should be ignored. I think it is better that we should know them. Then, though we are conscious of having faults as glaring as theirs, we can believe that that is no hindrance to our achieving also something of their virtues. - W. Somerset Maugham

My New Blog

Pete O'Hanlon11-Oct-07 23:59

11-Oct-07 23:59

Brady Kelly wrote:
Or am I missing something?

Sort of. The full version that I have takes care of a lot more than this, and is standardised so that more complex types of parameter can easily be created.

There's been a lot of work put into the library to ensure that it copes with a wide variety of cases - including things like automatically putting the right parameter modifier (e.g. @) onto a parameter name, based on the database type. It's sort of a cut down version of the Enterprise Library, and it means that we can target multiple databases with the same codebase, just by changing an entry in the config file.

Deja View - the feeling that you've seen this post before.

Brady Kelly12-Oct-07 0:15

Brady Kelly

12-Oct-07 0:15

Aah, thanks. Would you mind clearing something up for me please? The excerpt generates a compiler error for trying the call the protected constructor for DbParamter. Were you just generalising for the example? If so, I'm curious to know how you implement concrete helpers.

Pete O'Hanlon12-Oct-07 0:32

12-Oct-07 0:32

This was a very generalised example, mainly to demonstrate the fact that you can use a DbParameter. Our routines actually accept the DbCommand and use the CreateParameter method on this. One hint as to why we don't use the Enterprise Library - our routines can work with delegates (anonymous methods), attributes and default values. They've grown over the years, as our experience/needs with our clients has grown.

One day, I might write an article on them and detail how they've grown. It's an interesting way to document how infrastructure evolves with client demands.

Deja View - the feeling that you've seen this post before.

Sonia Gupta12-Oct-07 1:10

12-Oct-07 1:10

what is DbParameter in this context?

Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1

Pete O'Hanlon12-Oct-07 1:27

Re: Parameter Collection [modified]

12-Oct-07 1:27

If you just target SQL Server, just replace this with SqlParameter. DbParameter is the base class that SqlParameter derives from, and (as noted in the other posts in this thread), requires slightly different behaviour.

Deja View - the feeling that you've seen this post before.

Sonia Gupta12-Oct-07 1:47

12-Oct-07 1:47

Hi Pete....
can u tell me something about <T> in the following context.
<code>public static SqlParameter Parameter<T>(string parameterName, DbType parameterType, T value, ParameterDirection direction)</code>

-- modified at 7:52 Friday 12th October, 2007

Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1

-- modified at 7:52 Friday 12th October, 2007

Pete O'Hanlon12-Oct-07 2:18