2

Something stopped working in my application. I tested my stored procedure and it does return 3 rows (too big to repost here). At the output in Visual Studio it says afterwards:

(1 row(s) affected)
(3 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[GetCaseDocumentFile].

Why can't I put it into the datatable called dtResult? The command sqlDataAdapter.Fill(dtResult) should do it. Instead I get {} when looking at it in debug mode.

string connectionString = @"Data Source=34.56.55.251;Initial Catalog=DBXXX;User ID=xx;Password=XXXXXXXX;";
string queryString = "GetCaseDocumentFile";

using (SqlConnection connection = new SqlConnection(connectionString))
            {
    SqlCommand command = new SqlCommand(queryString, connection);
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@key", 200012);

    connection.Open();

    // Works! Three lines are returned.
    SqlDataReader reader = command.ExecuteReader();
    try
    {
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}",
                reader[0], reader[1]));
        }
    }
    finally
    {
        reader.Close();
    }

    // Does not work! dtResult remains empty. {}
    DataTable dtResult = new DataTable();
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command);
    sqlDataAdapter.Fill(dtResult);

    DataTable result = dtResult;
}

Update:

Nevermind. This actually works. This is my test code and what I thought my real code does. But I guess that I overlooked something in the real code.

Kasper Hansen
  • 6,307
  • 21
  • 70
  • 106
  • 1
    SqlConnection isnt the only thing that should really have a using statement, SqlCommand, SqlDataReader, Datatable and SqlDataAdapter all have dispose methods and should probably be wrapped in usings (unless you are disposing them manually) – Manatherin Apr 01 '11 at 09:36

1 Answers1

2

Have You set no count on in your Procedure??
Set NoCount On;

Alaa
  • 545
  • 1
  • 5
  • 11