1. Always make sure your Stored Procedure returns result set from back end. i.e Database side. At least to have a
SELECT
Statement that returns the desired result set. And also check how to write and work with
Stored Procedure[
^].
2. Try to understand how ADO.NET objects such as
DataSet[
^],
DataAdapter [
^] and focus on how they work with different SQL Statements that returns result set.
3.
DO NOT EVEN ATTEMPT TO USE DataSet.Tables.Count != 0
at all in your application. See the explanation below.
If you are expecting result set(s) and couldn't able to get even a single table, The above code will prevent you not to get the actual problem(exception) by further hiding it.
Technically,
1. You have a SQL Statement/Stored Procedure that returns
result set(s).
2. And you used
DataAdaper
and
DataSet
to get the result set then you should
at least see a single table schema inside the DataSet
object.
Why is
DataSet.Tables.Count != 0
is BAD in this case.
Let me take
Richard Deeming[
^] suggested code down in the comment.
using (var connection = new SqlConnection("server=.;integrated security=SSPI;"))
using (var command = new SqlCommand("PRINT 'Hello'", connection))
{
var ds = new DataSet();
var da = new SqlDataAdapter(command);
da.Fill(ds);
Console.WriteLine(ds.Tables.Count);
}
This code works perfectly without issue. Happy ! Not so fast. Let see its hidden dangerous issues which leads to
Logic error[
^]
Issue 1. The input SQL Statement
PRINT 'Hello'
is
not SQL Statement that can return result set(s) like
SELECT
SQL Statement and hence you will never ever get result set(s). This is also true for
DELETE
,
UPDATE
and other SQL Statements which are
not capable of returning result set.
Issue 2. The
DataAdapter
object(
da
) was asked to fill the
DataSet
. i.e
da.Fill(ds);
Another mistake on top of
Issue 1, A
DataAdapter
which has a sql statement that doesn't return a result set and told to fill
DataSet
. This is another mistake
When to use the two objects,
DataAdapter
and
DataSet
in
What sql statement.
Issue 3,
This is the most dangerous one. To use
DataSet.Tables.Count
on top of the first two issues. This eluding the entire error, the
Why is not working
Why I'm not getting result set,
Why index of range issues by assuming
you don't have table
Generally the above code always works but give unexpected result at the end, and it will be even more hard to spot the actual error.
What is the
Ultimate solution then ?
1. Know the first two points suggested above. In short, Know
HOW to work with
Stored Procedures or SQL Statement
,
DataAdapter
and
DataSet
and related objects. Know
WHEN to use them in
WHAT SQL Statement such as
SELECT,
DELETE,
UPDATE etc.
2. If you are expecting result set(s) and couldn't see it in
DataSet
object then
DO NOT ATTEMPT to hide further by using
DataSet.Tables.Count == 0
or
DataSet.Tables.Count != 0
. See
Issue 1,2,3 explanation.
Face the error, figure out why it's not returning the result set, don't further hide it.