asked on

How do you query Stored Procedure Parameters via ADO.net?

I am wondering how to figure out if a store procedure has a certain parameter without a try catch. Basically we have a standard in our database where all of our stored procedures are Delete_<TableName> and a set number of parameters. Well we want to add a @ReturnValue to some but not all right away. Since it is a return value I have to add it to the stored proc because you can't make it optional. So I was wondering if there was a way to check the stored proc to see if @ReturnValue has been defined.

Is that possible?

Dim nCmd As New SqlClient.SqlCommand("Delete_" & TableName)
nCmd.CommandType = CommandType.StoredProcedure
nCmd.Parameters.AddWithValue("@Param1", val1)
nCmd.Parameters.AddWithValue("@Param2", val2)
nCmd.Parameters.AddWithValue("@Param3", val3)
 
'Need logic to check to see if @ReturnValue is on Delete_<TableName>
nCmd.Parameters.Add("@ReturnValue", SqlDbType.VarChar)
nCmd.Parameters("@ReturnValue").Direction = ParameterDirection.Output
nCmd.Parameters("@ReturnValue").Size = 250

Open in new window

ASKER CERTIFIED SOLUTION

jmarbutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GregTSmith

All stored procedures have an implicit return value of zero if you do not set it explicitly.

I'll attach some of the code I was playing with because you might find it useful.

using System;
using System.Data;
using System.Data.SqlClient;
 
public class Database
{
  protected virtual string ConnectionString { get; set; }
 
  public Database(string connectionString)
  {
    this.ConnectionString = connectionString;
  }
 
  public virtual int DeleteTable(string tableName, params object[] parameters)
  {
    int returnValue = 0;
 
    this.UseCommand(
      (command) =>
      {
        command.CommandText = "Delete_" + tableName;
        command.CommandType = CommandType.StoredProcedure;
 
        SqlCommandBuilder.DeriveParameters(command);
 
        for (int i = 0; i < parameters.Length; i++)
          command.Parameters[i].Value = parameters[i];
 
        command.ExecuteNonQuery();
 
        returnValue = (int)command.Parameters[0].Value;
      }
    );
 
    return returnValue;
  }
 
  protected virtual void UseCommand(Action<SqlCommand> lambda)
  {
    this.UseConnection(
      (connection) =>
      {
        using (SqlCommand command = new SqlCommand())
        {
          command.Connection = connection;
          lambda(command);
        }
      }
    );
  }
 
  protected virtual void UseConnection(Action<SqlConnection> lambda)
  {
    using (SqlConnection connection = new SqlConnection(this.ConnectionString))
    {
      connection.Open();
      lambda(connection);
      connection.Close();
    }
  }
}

Open in new window

GregTSmith

Oops... that code won't quite work... since all procedures have a return value, you'd have to offset the parameter value you're setting by 1... ignore the last code posting and look at this one instead.

using System;
using System.Data;
using System.Data.SqlClient;
 
public class Database
{
  protected virtual string ConnectionString { get; set; }
 
  public Database(string connectionString)
  {
    this.ConnectionString = connectionString;
  }
 
  public virtual int DeleteTable(string tableName, params object[] parameters)
  {
    int returnValue = 0;
 
    this.UseCommand(
      (command) =>
      {
        command.CommandText = "Delete_" + tableName;
        command.CommandType = CommandType.StoredProcedure;
 
        SqlCommandBuilder.DeriveParameters(command);
 
        for (int i = 0; i < parameters.Length; i++)
          command.Parameters[i + 1].Value = parameters[i];
 
        command.ExecuteNonQuery();
 
        returnValue = (int)command.Parameters[0].Value;
      }
    );
 
    return returnValue;
  }
 
  protected virtual void UseCommand(Action<SqlCommand> lambda)
  {
    this.UseConnection(
      (connection) =>
      {
        using (SqlCommand command = new SqlCommand())
        {
          command.Connection = connection;
          lambda(command);
        }
      }
    );
  }
 
  protected virtual void UseConnection(Action<SqlConnection> lambda)
  {
    using (SqlConnection connection = new SqlConnection(this.ConnectionString))
    {
      connection.Open();
      lambda(connection);
      connection.Close();
    }
  }
}

Open in new window