Why some stored procedures do not return data in ASP.Net C#

Question

What I tried to do is generating a text file on ASP.Net using C#. The data is retrived from a database by executing a stored procedure. I ensured that the stored procedures returned values by executing it in SQL server Mangement studio. I was actually able to do just that.

I first gether all related tables into a View then using stored procedure to get data from that View. Then generate that data into text file in ASP.Net. using the following code:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Name"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("Stored Procedure Name", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);        
DataTable dt = new DataTable();
myAdapter.Fill(dt);           
System.Text.StringBuilder strResult = new System.Text.StringBuilder("");
string createtext = (Server.MapPath("./Feeds/") + "feed.txt");
StreamWriter w = File.CreateText(createtext);
w.Flush();
w.Close();

I ensured that every View returned values as well (in SQL manament studio).

But the problem is that not all stored procedures returned values on the ASP.Net I only get blank text file eventhough all stored procedures returned some values in SQL server Mangement studio.

I only use a simple select statement in the storeprocedures like

select Somthing form View_Name

This query returns some value in the Management Studio, but not ASP.Net (for some stored procedures).

what would be the issue for this problem. Please help

Answer 1

I don't see where you have taken the data in your DataTable and put it into the text file.

EDIT:

Since you have shown your code in the comments:

It appears you are swallowing your exceptions. If you were just leaving the code out for brevity great but if not then you could be hiding an error on the catch statement since you aren't doing anything with the exception.

For example you could be having a permissions issue on an SP or View and don't know it.

If that isn't the case then: Debug / add a breakpoint for this code to see if the datatable has data.

You could also use SQL Profiler to see what is getting to SQL Server from your code.

Answer 2

In addition to the obvious fact that you are not using the data you retrieved from the database, your code can leak resources if exceptions are thrown. It should be written as follows to avoid that:

using (SqlConnection conn = new SqlConnection(
       ConfigurationManager.ConnectionStrings["Name"].ConnectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("Stored Procedure Name", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        using (SqlDataAdapter myAdapter = new SqlDataAdapter(cmd))
        {
            using (DataTable dt = new DataTable())
            {
                myAdapter.Fill(dt);
            }
        }
    }
}

System.Text.StringBuilder strResult = new System.Text.StringBuilder("");
string createtext = (Server.MapPath("./Feeds/") + "feed.txt");
using (StreamWriter w = File.CreateText(createtext))
{
    // Do something with w
    w.Flush();
}

Answer 3

Have you debugged / added a breakpoint for this code to see if the datatable has data? Are you swallowing any exceptions (kind of looks like it from your code snippet)? For example, could you have a permissions issue on a few of those SP's and/or views. You could also use SQL Profiler to see what is getting to SQL Server from your code

Thank you guys. I went back and really looked at the Views I was using. As I mentioned earlier, some stroed procedures would return data on the text files, some would not.

In my case, It seemed that the stored procedures that retrived data from views that involved User Defined Function would return blank file.(The problem I'm facing) And for that particular stored procedure; the datatable was empty after this code : myAdapter.Fill(dt); Which explained why I got the blank text.

I also looked into SQL Profiler, it did execute the stored procedure once the button "Generate Text" on ASP.Net was clicked. But there was no data returned.

Is it possible that the UDF caused my problem. Is the problem somehow related to the UDF permission. I'm thinking the permission might be the cause of the problem. How can I grant permission to ASP.Net to be able to get data from the Stored Procedure that contains UDF.

Note: The UDF is created by other developers.

Thank you. Aein

Answer 4

i had the same problem, i try to replace all OleDbXXXX por SqlXXXX (OleDBConection to SqlConection) and it work as i spected i think is a kind of Bug?...or we have to do something else to get that working