0
SqlConnection connection = Connect();

SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = sprocName;
command.Connection = connection;
//loop through the dictionary
foreach (string key in paramList.Keys)
{
        command.Parameters.AddWithValue(key, paramList[key]);
}
SqlDataReader dataReader = command.ExecuteReader();

I am trying read data from database, when I am passing same parameters and execute the SP then I am getting proper result.But here SqlDataReader dataReader = command.ExecuteReader(); when I am checking (dataReader.HasRows) it returns false!

Mitali Patil
  • 67
  • 1
  • 9
  • That's not possible ... check if you are running against same DB/Table – Rahul Feb 12 '19 at 06:48
  • 1
    have you checked that your query actually returns value from the DB? – styx Feb 12 '19 at 06:48
  • You may need to call [NextResult](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.nextresult?view=netframework-4.7.2). – Nick Feb 12 '19 at 06:48
  • @styx yes I have checked query actually returns value from db. – Mitali Patil Feb 12 '19 at 07:02
  • Can you please add the SP too if possible? – sujith karivelil Feb 12 '19 at 07:05
  • Might you please [edit] your question to show the code that includes the call to `dataReader.HasRows`? An [mcve] would be ideal. But possibly useful for debugging purposes: [Write SqlDataReader to immediate window c#](https://stackoverflow.com/q/38953677/3744182). – dbc Feb 12 '19 at 07:18
  • please include `Connect();` method , `paramList` and `sprocName` – styx Feb 12 '19 at 07:24
  • Show your paramList and stored procedure definition, it sounds like a typing issue, if there were a problem with the Connect() method it would throw and exception that the connection is not open.. – Daniel Brughera Feb 12 '19 at 07:57
  • there is no issue with SP , – Mitali Patil Feb 12 '19 at 08:26
  • Exec up_GetAtt @pageNumber=1, @pagesize=10, @PID=1000, @DeID=NULL, @DeName =NULL, @SortByColumn='ReleaseDate', @SortOrder='DESC' this are the parameters I am passing – Mitali Patil Feb 12 '19 at 08:28
  • That's the problem, there is not an implicit conversion from NULL to DBNull values, check my answer – Daniel Brughera Feb 12 '19 at 11:41
  • What is the data type of the parameters? The AddWithValue function can cause issues with implicit conversions. https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ – userfl89 Feb 12 '19 at 15:20
  • I have tried executing same query in sql with same dataset which command.parameter contains. – Mitali Patil Feb 13 '19 at 05:04
  • If you don't show how are you filling the paramList, what is in the sprocName string and the parameters definition of your stored procedure, we cannot do nothing but try to guess what is happening, please edit your question and add that info. You may think that you are sending the same parameters and values, but a wrong typing can change everything – Daniel Brughera Feb 13 '19 at 07:31

2 Answers2

0

Does you key contains '@', for example if key parameter name EmployeeId then needs to add parameter as below

commnad.Parameters.AddWithValue('@EmployeeId', 1);
Mohit Raiyani
  • 21
  • 3
0

Exec up_GetAtt @pageNumber=1, @pagesize=10, @PID=1000, @DeID=NULL, @DeName =NULL, @SortByColumn='ReleaseDate', @SortOrder='DESC' this are the parameters I am passing

You need to convert the C# null values into DBNull

SqlConnection connection = Connect();

SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = sprocName;
command.Connection = connection;
//loop through the dictionary
foreach (string key in paramList.Keys)
{
        command.Parameters.AddWithValue(key, paramList[key] == NULL? DBNull.Value : paramList[key]);
}
SqlDataReader dataReader = command.ExecuteReader();

Given the lack of information to help you better, check also for common mistakes

  • Typos in proc and parameter names
  • Sending a query with CommandType.StoredProcedure: sprocName value must be just "up_GetAtt", is a common mistake to include the EXEC word or parameters.
  • Check Value Types, your paramList should be a dictionary, and the value type of the items that you put in that list should match with the sql equivalent type, not all types have implicit conversion.
  • Check nullability of paramList items, use nullable int and datetime
  • Check that the parameter names start with @
  • Omitting a parameter does not mean that it is null, unless your store procedure definition default it as null
  • Omitting a parameter with no default value in procedure definition
  • Unnecessary quotation of values, you are sending parameter values, it is not a concat
Daniel Brughera
  • 1,641
  • 1
  • 7
  • 14