Is there any way to reduce this ado.net code?

Question

I need reduce significantly this code, is there any way to create a sql parameter describing its direction? Here is the code:

    Dim Oparam1 As SqlParameter = New SqlParameter("@ROJO", SqlDbType.Int)
    Dim Oparam2 As SqlParameter = New SqlParameter("@AMBAR", SqlDbType.Int)
    Dim Oparam3 As SqlParameter = New SqlParameter("@AMARILLO", SqlDbType.Int)
    Dim Oparam4 As SqlParameter = New SqlParameter("@VERDE", SqlDbType.Int)
    Oparam1.Direction = ParameterDirection.Output
    Oparam2.Direction = ParameterDirection.Output
    Oparam3.Direction = ParameterDirection.Output
    Oparam4.Direction = ParameterDirection.Output
    command.Parameters.Add(Oparam1)
    command.Parameters.Add(Oparam2)
    command.Parameters.Add(Oparam3)
    command.Parameters.Add(Oparam4)

thanks in advance.

I see 3 sets of 4 nearly identical lines of code. Create a method and pass in parameters for what can vary. — Anthony Pegram
– Anthony Pegram, Commented Mar 26, 2013 at 18:49

Phil · Accepted Answer · 2013-03-26 18:56:35Z

2

For each parameter you could use

C#

command.Parameters.Add(new SqlParameter("@name", SqlDbType.Int)
    {Direction = ParameterDirection.Output});

VB.NET

command.Parameters.Add(New SqlParameter("@name", SqlDbType.Int) With { _
    .Direction = ParameterDirection.Output _
})

edited Mar 26, 2013 at 18:56

answered Mar 26, 2013 at 18:50

Phil

43.2k9 gold badges102 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guffa · Accepted Answer · 2013-03-26 19:22:40Z

There is an overload of the constructor that includes the direction, but then you have to specify a lot of other parameters, so the code won't be any shorter after all.

You can make an extension method:

Imports System.Runtime.CompilerServices

Module SqlExtensions

  <Extension()>
  Public Function SetOutput(parameter As SqlParameter) As SqlParameter
    parameter.Direction = ParameterDirection.Output
    Return parameter
  End Function

End Module

Now you can use that on the parameters:

command.Parameters.Add(New SqlParameter("@ROJO", SqlDbType.Int).SetOutput())
command.Parameters.Add(New SqlParameter("@AMBAR", SqlDbType.Int).SetOutput())
command.Parameters.Add(New SqlParameter("@AMARILLO", SqlDbType.Int).SetOutput())
command.Parameters.Add(New SqlParameter("@VERDE", SqlDbType.Int).SetOutput())

Jmyster · Accepted Answer · 2013-03-26 20:04:00Z

1

Try:

command.Parameters.Add(new SqlParameter("@ROJO", SqlDbType.Int) With {.Direction = ParameterDirection.Output});

edited Mar 26, 2013 at 20:04

answered Mar 26, 2013 at 18:52

Jmyster

1,0032 gold badges10 silver badges28 bronze badges

Collectives™ on Stack Overflow

Is there any way to reduce this ado.net code?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related