2

I have a stored procedure that I need to run in C# and set the result set returning from the SP in a HTML table. Please note that the SP is working well in SSMS and returning results.

The c# code I am using is (it is in an ASP 4.5 project):

SQLDatabase sqldb = new SQLDatabase();
using (SqlConnection sqlcn = new SqlConnection(sqldb.GetConnectionString().ToString()))
{
   if (sqlcn.State == ConnectionState.Closed)
   {
      sqlcn.Open();
   }
   SqlCommand cmd = new SqlCommand("[MyStoredProcedure]", sqlcn);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFrom.Value.ToString()));
   cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtTo.Value.ToString()));
   using (SqlDataAdapter a = new SqlDataAdapter(cmd))
   {
       DataSet ds = new DataSet();
       a.Fill(ds);
       dtExtra = ds.Tables[0];
   }
}

This code above is returning 0 rows, even though the SP is working in SSMS. While debugging, the connectionstring and the parameters are coming all true, no issue. The connectionstring is:

<add name="DefaultDB" connectionString="Data Source=TestEnv;Initial Catalog=TestTable;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>

I don't understand what may cause this. I found the topic below, but I am using Integrated Security=SSPI in my connection string already, so it did not help me. Any advice would be appreciated.

ASP.NET stored proc call bringing back no rows, but does in Management Studio!

EDIT: SOLVED! Thanks @NineBerry. It turned into a between/and usage problem in SP. Changing txtTo.Value as: DateTime.Today.AddDays(1).ToString("yyyy-MM-dd"); in the code fixed the issue (It was DateTime.Today.ToString("yyyy-MM-dd") before, I should have included it in the code part, didn't think it is related to that, sorry). Better solution would be updating the SP using >= and <= instead of between/and keywords tho.

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
  • 3
    It looks like `txtFrom.Value` and `txtTo.Value` are **already** DateTime values. Calling `.ToString()` and then `Convert.ToDateTime()` is just slowing things down and potentially introducing localization issues. – Joel Coehoorn Nov 08 '18 at 17:21
  • I'm also curious about using integrated security here, as the web server by default runs under it's own special user account, which is unlikely to have access to your DB Server. – Joel Coehoorn Nov 08 '18 at 17:23
  • 2
    Add logging to the stored procedure to see what parameters are actually used and what it returns. – NineBerry Nov 08 '18 at 17:32
  • Are you sure that you're getting to the end of this without an exception? If so, I'd work on the DB side to profile or record that you are calling the SP with the parameters you expect. (you could log the parameters in the SP, for example.) – Jamie F Nov 08 '18 at 17:33
  • did u check yr query by SQL Profiler? – Daniel B Nov 08 '18 at 18:10

1 Answers1

3

I would modify your code to simply be:

using(var dbConnection = new SqlConnection("..."))
using(var command = new SqlCommand(query, dbConnection))
{
     dbConnection.Open();
     ...
}

Handling the connection pooling in the using block is always a good idea per Microsoft guideline:

To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.

You are checking if the connection is closed, what if the connection is idle? By using the using syntax you implement dispose. So it will correctly close your connection, so you should not need to check if the connection is closed unless you are using a singleton for the connection.

After reading your question, you may have more than just the one issue I pointed out. I would recommend a service account with access the specific data you are seeking, that the application can access rather than integrated security.

Greg
  • 11,302
  • 2
  • 48
  • 79