asked on

Store Procedure won't return multple rows to C# Application

I have a stored procedure which returns three results:

ie.
select 1 as FirstValue from Table1
select 2 as SecondValue from Table2
Select 3 as ThirdValue from Table3

I then call the Stored Proc and am trying to get back the 3 results.

It works fine in a query window, but in c# it returns no rows.

kaufmed

Soooooo... Do we get to see the C# code that doesn't work?

Howard Bash

ASKER

        static int GetLibrary(SiteProperties siteProps)
        {
            int retVal = -1;

            try
            {
                string connectionString = ConfigurationManager.ConnectionStrings["TDM"].ToString();

              
                using (SqlCommand cmd = new SqlCommand("USP_GetInfo"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@Id", siteProps.Id));
                    cmd.Parameters.Add(new SqlParameter("@juris", siteProps.Juris));

                    SqlConnection con = new SqlConnection(connectionString);
                    con.Open();
                    cmd.Connection = con;

                    SqlDataReader sqlReader = cmd.ExecuteReader();
                    sqlReader.NextResult();

                    while (sqlReader.Read())
                    {
                        object a = sqlReader.GetValue(0);
                    }

                    while (sqlReader.Read())
                    {
                        object a = sqlReader.GetValue(0);
                    }
                    sqlReader.NextResult();

                    while (sqlReader.Read())
                    {
                        object a = sqlReader.GetValue(0);
                    }

                   
                    con.Close();

                }
            }
            catch (Exception ex)
            {
                Logger.InfoTrace(ex.Message.ToString());
                Logger.LogFailErrorMessage(ex.Message.ToString(), ex);
            }
            return retVal;
        }
h

Open in new window

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

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

Start Free Trial

Howard Bash

ASKER

Yes. That is true. I have been juggling the code and with three result sets, at least one of them should have shown the data. As it happens, the issue turned out to be when I wanted to pass a null as a parameter, I tried null which failed and using DBNull.Value works. So that was the real issue. When I passed empty string I wasn't getting any values back.

Thanks.