C# SQL Server stored procedure not returning expected value

Question

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;
        }
    }
}

This is what I am expecting to find

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."}

Stored procedures don't return objects. Your code doesn't execute the stored procedure directly, it used an Adapter to fill a Dataset from it. What object are you talking about? What did you expect to get? Why load a Dataset anyway? Did you copy the code from some old example perhaps? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 2, 2020 at 15:14
If the stored procedure returns a single value you can use SqlCommand.ExecuteScalar to return it directly. To return multiple fields and rows you need ExecuteReader so you can read the rows one by one. That's what the adapter does to load the DataSet's tables. You can get rid of it though, and fill a DataTable directly with DataTable.Load(IDbDataReader). — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 2, 2020 at 15:16
i copied the method from how others have done this within the same codebase as i wanted to try keep an element of consistency but as the other segments have been specialised i had to change this else i would of just made into a function to remove repeated code.# — Matt Farrell
– Matt Farrell, Commented Jul 2, 2020 at 15:17
Perhaps an even better idea is to use a micro-ORM like Dapper and get the results automatically mapped to objects, eg connection.Query<MyRecord>(sprocName); will execute the stored procedure and map the results to MyRecord instances based on property names — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 2, 2020 at 15:17
That's not a common way to load data. That's very old code - think pre-2005, that gets blindly copied around. Nobody wants to create DataSet instances without reason — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 2, 2020 at 15:18

JohnD · Accepted Answer · 2020-07-07 18:29:14Z

2

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];
    }
}

edited Jul 7, 2020 at 18:29

answered Jul 2, 2020 at 15:18

JohnD

14.9k4 gold badges42 silver badges54 bronze badges

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

11 Comments

JohnD Over a year ago

@DStanley true, if he wants a single value back. From the question it looks like he is expecting a row back.

D Stanley Over a year ago

Ahh I missed that and read it as a single value. Thanks.

Matt Farrell Over a year ago

I get this error, i updated my question to show the code i am using taken from what you have said. {"Procedure or function 'GETPARTNERPARAMETER' expects parameter '@Parameter', which was not supplied."}

JohnD Over a year ago

Hard to say, it looks right from the C# side and you are providing the @Parameter param. I would double check to make sure you are running your code correctly (has it been rebuilt?) and that the value is not NULL. Also might want to post the stored procedure declaration & argument list.

JohnD Over a year ago

Actually you might need to add "dbCommand.CommandType = CommandType.StoredProcedure;" when building the command

|

Joel Coehoorn · Accepted Answer · 2020-07-02 16:16:14Z

0

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.

edited Jul 2, 2020 at 16:16

answered Jul 2, 2020 at 15:42

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Collectives™ on Stack Overflow

C# SQL Server stored procedure not returning expected value

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related