Using reader: SqlDataReader and object disposal

Elements Oxygene
1

hi there…

			        using command: SqlCommand := new SqlCommand('select data_type from information_schema.columns where table_name = '+chr(39)+tablename+chr(39)+' and column_name = '+chr(39)+ZFrom+chr(39), connection) do begin
				        using reader: SqlDataReader := command.ExecuteReader() do begin
                            if not reader.HasRows then
                                raise new Exception('Field '+ZFrom+' not found in table');

                            reader.Read();
                            var s:= reader.GetString(0);
                            if not contains(MSSQL_Numbers, s) then
                                raise new Exception('Field '+ZFrom+' is wrong type');
                            
                            reader.Close();
                        end;
                    end;

this block is an example, that is currently working.

However I needed to use that reader.Close() at the end, so that the next block (similar to this one) works !

If I do not call this reader.Close() the next query will return the values from the first query made. Only expliciting calling the Close on the reader I can effectivelly get data from a new query.

Looking up for this in C# I see 2 types of contructions, one where Using clause is used extensivelly, like I did, and supposedly when reader is disposed, close() is called automatically.The other construction is doing every step with the using clause for controlling context.

I am not sure this is an Oxigene thing, or I did not understand how reader and using works together.

RIght now, I have to add this reader.Close() everywhere in my code before the reader gets out of context.

Any clue?

2

using is for any objects that implement iDisposable, under the hood at the end of block its calling a Dispose method. Inside the dispose method it will call close. You would that for any objects that have physical objects underneath like database connections or file handles.

using connection := new SqlConnection...

using command := new SqlCommand
using reader
end
end

using command2 := new SqlCommand
using reader2
end
end

end  { end for connection)

You don’t need Close. This should be just fine as long as the using blocks are separate. I would of posted the code that doesn’t work because I cant see what might be wrong.

Also I would check out something like dapper https://github.com/StackExchange/dapper-dot-net
its way easier than working with sqlreader and sqlcommand.

Cheers,
John

3

thanks for the answer and the link

I am aware of each unsing should have its end block

I have created only one connection to be reused by the entire code, and then I have those command/reader blocks.

Then running it, I notted that the subsequent block does not work correctly, it remains based on the previous reader data. I dont undestand why, since everything is new here for me.

4

How about if you post the code that not working ?