Procedure Returning Blank Data Table from ASP.NET

Question

I'm having issues with a stored procedure I'm attempting to run from an ASP.NET console application.

This is how I'm attempting to get the results in the code:

public static DataTable PerformStoredProcedure(int PracticeID, string CommandName)
    {

        DataTable dt = new DataTable();

        try
        {
            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["DbConnString"].ToString()))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = CommandName;
                    cmd.Parameters.AddWithValue("@practiceID", PracticeID);

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }
                }

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Rows[i].ItemArray.Length; j++)
                    {
                        Console.Write(dt.Rows[i].ItemArray[j] + ",");
                    }
                    Console.WriteLine();
                }
            }
        }

After the procedure is run I get returned with a blank data set with no errors. However, when I run the procedure in SQL Server Management Studio I works just fine.

You may notice I don't use the new SqlCommand() constructor when initiating the SqlCommand cmd. That is because the query is enormous and takes a while to run so I figured it was a timeout issue.

Following the suggestion here, I made the following class in order to adjust the execution timeout limit.

class CommandFactory
{
            public static int CommandTimeout
            {
                get
                {
                    int commandTimeout = 0;
                    var configValue = ConfigurationManager.AppSettings["commandTimeout"];

                    if (int.TryParse(configValue, out commandTimeout))
                        return commandTimeout;

                    return 120;
                }
            }

Figured I'd include that just to avoid confusion about whether the SqlCommand was being initialized correctly. After that adjustment, I began getting the issue where the command returns a blank data table. I assumed it could have to do with the minimum capacity of the data table dt, so I adjusted that as you can see in the original code.

I've done additional research and made the suggested fixes that are suggested to no avail:

1. Set nocount on

2. Use data adapter instead of data reader

3. This is the most detailed answer. There are several suggestions made with in the forum but the eventual resolution has to do with views being used by the database as a default and blocking access to desired data. I have already confirmed with our DBA that we do not do this.

Like I mentioned before, the query encompassed in the stored procedure is enormous and I haven't included it here because of its sheer size and because this post is long enough as it is. Again, I did confirm that the procedure is working in SSMS.

I also haven't included an example result set because the data contains restricted information.

I appreciate any help you can give.

UPDATE:

I added another method that calls a stored procedure to see if the problem was particular to the particular procedure being called. That second stored procedure also returned a blank data table so the problem is not specific to either procedure. (I also confirmed the second procedure was working and returning rows in SSMS)

That led me to believe there was something wrong with the app.config file for that project. I then added a third method that called a stored procedure on another project in the solution. However, even that returned a blank data table.

Figured I'd provide clues as I happen upon them.

Answer 1

You're quite close. Here are a few pointers:

1. The SqlConnection is never opened. You need to call .Open() in order for the connection to be established.
2. If you set a breakpoint on the .Fill() line and hover over the DataTable object, it will appear as if the DataTable is empty (as you've seen), but .Fill() actually fills the Rows of the DataTable for you automatically (see MSDN). I bet that if you loop through the Rows, you'll find that your query has actually been returning data all along.
3. You could use using statements which automatically call .Dispose() for you as well as close the SqlConnection.

Here's an updated version of your method:

public static DataTable getAllModifiedChargesToday(int practiceID)
{
    DataTable dt = new DataTable();

    try
    {
        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["DbConnString"].ToString()))
        {
            conn.Open();

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "ch_getAllModifiedChargesToday";
                cmd.Parameters.AddWithValue("@practiceID", practiceID);

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                }
            }
        }
    }
    catch (Exception ex)
    {
        LogError(ex);
    }

    return dt;
}

And here's a test you can run to view the data returned:

for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int j = 0; j < dt.Rows[i].ItemArray.Length; j++)
    {
        Console.Write(dt.Rows[i].ItemArray[j] + ",");
    }
    Console.WriteLine();
}

Also, you mentioned setting a timeout... I usually set my in my connection string, like so:

"Server=SERVER;Database=DATABASE;User ID=USER;Password=PASSWORD;Trusted_Connection=False;Asynchronous Processing=true;Timeout=60;"

You don't need that extra CommandFactory class at all, and you don't need to set a MinimumCapacity (MSDN) unless you are really trying to optimize performance and have a guesstimate of how many records will be returned. This property isn't used very often.