CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
Matt Farrell
Matt Farrell

Reputation: 199

C# SQL Server stored procedure not returning expected value

I wrote up this function to return a dataset, I was expecting a smaller dataset as there's only one value I was expecting back, but I get a rather bloated object back which I cannot find the value I am looking for, this is causing problems as I intend to use this function heavily.

I was hoping someone could spot what I am doing wrong, I have included the code, a screenshot of the returned object and what I am expecting. Any help would be greatly appreciated.

If I have not phrased anything in this question correctly feel free to let me know, I struggle to express my thoughts well.

public DataSet getPartnerParameter(string parameter)
{
    using (var dbConnection = new SqlConnection(UnityHelper.IocContainer.Resolve<IConfigHelperService>().GetConnectionString("CASConnectionString")))
    {
        dbConnection.Open();

        using (var dbCommand = new SqlCommand("GETPARTNERPARAMETER"))
        {
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.Connection = dbConnection;

            SqlParameter lstrParameter = new SqlParameter("@Parameter", SqlDbType.VarChar);
            lstrParameter.Value = parameter;
            dbCommand.Parameters.Add(lstrParameter);

            var ldaDPS = new SqlDataAdapter(dbCommand);
            var ldstParameterValues = new DataSet();
            ldaDPS.Fill(ldstParameterValues);

            return ldstParameterValues;
        }
    }
}

data object contents

This is what I am expecting to find

Execute stored procedure

edit//

changed my code slightly but still not working.

public String[] getPartnerParameter(string parameter)
{
    using (var dbConnection = new SqlConnection(UnityHelper.IocContainer.Resolve<IConfigHelperService>().GetConnectionString("CASConnectionString")))
    {
        dbConnection.Open();

        SqlCommand dbCommand = new SqlCommand("GETPARTNERPARAMETER", dbConnection);
        dbCommand.CommandType = CommandType.StoredProcedure;

        SqlParameter lstrParameter = new SqlParameter("@Parameter", SqlDbType.VarChar);
        lstrParameter.Value = parameter;
        dbCommand.Parameters.Add(lstrParameter);

        SqlDataReader reader = dbCommand.ExecuteReader();
        string[] results = new string[2];
        while (reader.Read())
        {

            results[0] = reader[0].ToString();
            results[1] = reader[1].ToString();
    
        }
        if (results.Length < 1)
        {
            results[0] = "Cannot find Value";
            results[1] = "S";
            return results;
        }
        else
        {

            return results;
        }
    }

The error is this: {"Procedure or function 'GETPARTNERPARAMETER' expects parameter '@Parameter', which was not supplied."}

Upvotes: 0

Views: 1067

Answers (2)

JohnD
JohnD

Reputation: 14747

The values you are looking for are probably in the dataSet.Tables[0].Rows[0] row.

However, if you are expecting one row back, a DataSet object seems like overkill. I would recommend avoiding the SqlDataAdapter/DataSet and instead use a SqlDataReader.

Untested code, but should give you the gist of how to use it:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    SqlCommand dbCommand = new SqlCommand("GETPARTNERPARAMETER", connection);
    dbCommand.CommandType = CommandType.StoredProcedure;
    SqlParameter lstrParameter = new SqlParameter("@Parameter", SqlDbType.VarChar);
    lstrParameter.Value = "LexisNexisCreditConsentRequired";
    dbCommand.Parameters.Add(lstrParameter);

    SqlDataReader reader = dbCommand.ExecuteReader();
    while (reader.Read())
    {
        var yourValue = reader[0];
        var yourDataType = reader[1];
    }
}

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

A DataSet is an object which can contain many tables. It doesn't have to, but it can, and so it also has a number of fields, properties, and methods to support that role.

For this query, look at ldstParameterValues.Tables[0].Rows[0]. Within that row, you can also see the columns with another level of bracket-indexing:

DataRow row = ldstParameterValues.Tables[0].Rows[0];
var column0Value row[0];
var column1Value = row[1];

However, the type for these results is object. You'll need to either cast the values or use one of the GetX() methods on the datarow to get results with a meaningful type.

Upvotes: 0

Related Questions