Solved

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

Posted on 2009-05-12
3
348 Views
Last Modified: 2013-12-25
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

0
Comment
Question by:jmarbutt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
jmarbutt earned 0 total points
ID: 24365731
I found the answer via the SQLCommandBuilder

See this article:
http://www.davidhayden.com/blog/dave/archive/2006/11/01/SqlCommandBuilderDeriveParameters.aspx
0
 
LVL 3

Expert Comment

by:GregTSmith
ID: 24365947
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

0
 
LVL 3

Expert Comment

by:GregTSmith
ID: 24365969
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

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question