Empty result when calling SQL Server stored procedure

Question

I am building a WebAPI using ASP.NET to expose some stored procedures I have implemented in a database. I am using ADO.NET to call the stored procedures.

I have a controller:

public class AuthController : ApiController
{
    [HttpGet]
    [ActionName("AuthSearch")]
    public AuthorizationResult GetAuthSearch(Guid? id, [FromUri]AuthorizationQuery Query)
    {
        SqlConnection con = new SqlConnection("Connection String");
        SqlDataReader reader = null;

        try
        {
            con.Open();

            SqlCommand cmd = new SqlCommand("SP_AUTH_SEARCH", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@GUID", id);
            cmd.Parameters.AddWithValue("@AUTHNO", Query.AuthNo);
            cmd.Parameters.AddWithValue("@STATUS", Query.Status);
            cmd.Parameters.AddWithValue("@REQDATE_BEGIN", Query.ReqDateBegin);
            cmd.Parameters.AddWithValue("@REQDATE_END", Query.ReqDateEnd);
            cmd.Parameters.AddWithValue("@REQDATE_OP", Query.ReqDateOp);
            cmd.Parameters.AddWithValue("@AUTDATE_BEGIN", Query.AuthDateBegin);
            cmd.Parameters.AddWithValue("@AUTDATE_END", Query.AuthDateEnd);
            cmd.Parameters.AddWithValue("@AUTDATE_OP", Query.AuthDateOp);
            cmd.Parameters.AddWithValue("@EXPDATE_BEGIN", Query.ExpDateBegin);
            cmd.Parameters.AddWithValue("@EXPDATE_END", Query.ExpDateEnd);
            cmd.Parameters.AddWithValue("@EXPDATE_OP", Query.ExpDateOp);
            cmd.Parameters.AddWithValue("@REFPROV", Query.RefProv);
            cmd.Parameters.AddWithValue("@REQPROV_FIRST", Query.ReqProvFirst);
            cmd.Parameters.AddWithValue("@REQPROV_LAST", Query.ReqProvLast);
            cmd.Parameters.AddWithValue("@MEMB_FIRST", Query.MembFirst);
            cmd.Parameters.AddWithValue("@MEMB_LAST", Query.MembLast);
            cmd.Parameters.AddWithValue("@MEMB_ID", Query.MembId);
            cmd.Parameters.AddWithValue("@SORT_COLUMN", 1);
            reader = cmd.ExecuteReader();
            AuthSearchResult auth = null;
            if (reader.Read())
            {
                auth = new AuthSearchResult();
                auth.AuthNo = reader[0].ToString();

            }

            return auth;
        }
        catch (Exception e)
        {
            AuthorizationResult auth = null;
            auth = new AuthorizationResult();
            auth.Descr = e.ToString();
            return auth;
        }
        finally
        {
            con.Close();
        }
    }
}

And some model classes:

public class AuthorizationResult
{
    public string AuthNo { get; set; }
    public string Descr { get; set; }
    public string MembId { get; set; }
    public string MembName { get; set; }
    public string MembSex { get; set; }
    public string MembDob { get; set; }
    public string MembHp { get; set; }
    public string ProvName { get; set; }
    public string AuthDate { get; set; }
    public string ReqDate { get; set; }

    public AuthorizationResult()
    {
        AuthNo = " ";
        Descr = " ";
        MembId = " ";
        MembName = " ";
        MembSex = " ";
        MembDob = " ";
        MembHp = " ";
        ProvName = " ";
        AuthDate = " ";
        ReqDate = " ";
    }
}

public class AuthorizationQuery
{
    public string AuthNo { get; set; }

    public string Status { get; set; }
    public string ReqDateBegin { get; set; }
    public string ReqDateEnd { get; set; }
    public string ReqDateOp { get; set; }
    public string AuthDateBegin { get; set; }
    public string AuthDateEnd { get; set; }
    public string AuthDateOp { get; set; }
    public string ExpDateBegin { get; set; }
    public string ExpDateEnd { get; set; }
    public string ExpDateOp { get; set; }
    public string RefProv { get; set; }
    public string ReqProvFirst { get; set; }
    public string ReqProvLast { get; set; }
    public string MembFirst { get; set; }
    public string MembLast { get; set; }
    public string MembId { get; set; }

    public AuthorizationQuery()
    {
        AuthNo = "";
        Status = "";
        ReqDateBegin = "";
        ReqDateEnd = "";
        ReqDateOp = "";
        AuthDateBegin = "";
        AuthDateEnd = "";
        AuthDateOp = "";
        ExpDateBegin = "";
        ExpDateEnd = "";
        ExpDateOp = "";
        RefProv = "";
        ReqProvFirst = "";
        ReqProvLast = "";
        MembFirst = "";
        MembLast = "";
        MembId = "";
    }
}

However when I run my project and navigate to

localhost/api/Auth/AuthSearch?id=GUID

I get an empty result set. If I run the same stored procedure in SQL Server using the same GUID value, I get a non empty result set.

I've used this same SP in a VB.NET web forms app without any issues.

I've ensured that the connection string is correct and I've double checked that the GUID I'm using is correct. What else am I missing?

When I run the same SP on SQL Server

Answer 1

you can try this

cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier).Value = id;

if your @GUID is of type UniqueIdentifier if its a varchar or nvarchar then try this

cmd.Parameters.AddWithValue("@GUID", id.ToString().ToUpper());

to convert the type GUID to String

Edit

i noticed in your sp the guid is upper case and in code guid converts string to lower case maybe this was causing problem so try adding this .ToUpper() to make the string uppercase

Answer 2

Perhaps...

I'd of course change my strategy is to avoid using " ".

(The 'else' condition in place if you are forced to supply all parameters.)

if (!String.IsNullOrEmpty(Query.AuthNo))
 cmd.Parameters.AddWithValue("@AUTHNO", Query.AuthNo);
else
 cmd.Parameters.AddWithValue("@AUTHNO", DBNull.Value);

By the way, strongly recommend you implement USING() since it takes care of DISPOSE and CLOSE, etc.

using (var ds = new DataSet())
{
  using (var con = new SqlConnection("Connection String"))
  {
    using (var cmd = new SqlCommand("SP_AUTH_SEARCH", con))
    {
      using (var adapter = new SqlDataAdapter(cmd))
      {
        con.Open();
        adapter.Fill(ds);
      }
    }
  }
  AuthorizationResult auth = null;
  auth = new AuthorizationResult();

  foreach (DataRow dr in ds.Tables[0].Rows)
  {
    auth.AuthNo = dr[0].ToString();
  }

  return auth;
}