C# & SQL Server stored procedure doesn't return results

Question

I'm trying to get a zip from a database and I've written a stored procedure like this

[dbo].[p_SearchZipLocal]
    @zip nvarchar(10) = NULL,
    @city nvarchar(100) = null
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN
        -- Verifica se o zip aparece na primeira coluna
        IF((SELECT COUNT(cttzip) 
            FROM seur 
            WHERE cttzip = @zip) > 0)
        BEGIN
            -- Verifica se o valor é NA, se não for devolve o zip e a localidade correcta
            IF((SELECT TOP(1) [SubCity1] as city 
                FROM seur 
                WHERE cttzip = @zip) = '#N/A')
            BEGIN
                -- Se for NA vamos ter de pesquisar na quinta coluna pela localidade
                IF((SELECT count(*) FROM seur WHERE subCity2 LIKE @city + '%' )>0)
                    -- Se encontrar-mos devolvemos o novo zip
                    SELECT TOP(1) 
                        '0' AS Error, subZip2 as zip, subCity2 AS city 
                    FROM seur 
                    WHERE subCity2 LIKE @city + '%'
                ELSE
                    SELECT 
                        '2' AS Error, 'Erro' AS zip, 'Erro' AS city
                END
            ELSE
                SELECT TOP(1) 
                    '0' AS Error, cttzip AS zip, [SubCity1] as city 
                FROM seur 
                WHERE cttzip = @zip
        END
    ELSE
        SELECT '1' AS Error, 'Erro' AS zip, 'Erro' AS city
    END
END

Returning the results when executed from SQL Server like this:

0   9900    AEROPORTO DA HORTA

But when I call it from C# code it doesn't return the results to the datatable.

using (SqlCommand cmd = new SqlCommand("p_SearchZipLocal", connectionSQL))
{
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = "9900";
    cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = "PRAIA DO ALMOXARIFE";

    connectionSQL.Open();

    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
}

This code gets the column names in the return but there is no value associated. Can anyone tell me where is my code wrong?

Little tip, in your `if` checks, instead of doing `> 0` or `TOP (1) ... = ` use `Exists(` it will result in faster queries. [Here is your posted code rewritten to use it](https://gist.github.com/anonymous/acf29262b062f9071965). Also your `SELECT TOP(1)` section I think is wrong, you are doing a top 1 without a order by which is likely a error. You probably wanted another exist there (which I did in the example I linked to) — Scott Chamberlain, Dec 10 '15 at 22:02
Your code worked well with some fine tunning. Thanks for the help — Lucky, Dec 14 '15 at 12:25

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

I would use .Fill with a DataAdapter

Please see this...

DataTable.Load(FbDataReader) does not load everything into DataTable

edited May 23 '17 at 11:52

Community

1
1

answered Dec 10 '15 at 19:37

FormulaChris

101
4

The problem persists. I'm thinking the data doesn't pass from the sql stored procedure -> C# – Lucky Dec 10 '15 at 19:45
Can you try something like this? // comment out dt.Load(cmd.ExecuteReader()); then add this: SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while(myReader.Read()) { Console.WriteLine(myReader["city"]); } myReader.Close(); – FormulaChris Dec 10 '15 at 20:39
Solved with a little of every tip. Thanks for the help – Lucky Dec 14 '15 at 12:29

score 1 · Answer 2 · answered Dec 10 '15 at 20:14

1

The DataTable.Load method expects a primary key column in the underlying data (i.e. from DataReader). Look like your procedure not have any primary key column, or if you have one please user order by in sql statement and try to keep it as first column so that DataTable will able to accept it as primary.

This is very old issue with DataTable.Load and not very well documented. In general SQLDataAdapter is good with DataTable.

answered Dec 10 '15 at 20:14

Avinash Jain

7,200
2
26
40

I'm not sure if the problem was directly correlated with not returning a primary key but it was one of the things I added to the final solution. Thanks for the help – Lucky Dec 14 '15 at 12:27

C# & SQL Server stored procedure doesn't return results

2 Answers2