Returning multiple columns from SQL procedure with .NET

Question

I am using this vb.net code file to call a procedure to get dates (among other things). When I execute this procedure in SQL 2005 Server Management Studio, I get about 10 columns. However when I execute this code, the dataset seems to only have one index value, maybe I am misunderstanding something. When I change this

ds.Tables(0).Rows.Count.ToString

to

ds.Tables(1).Rows.Count.ToString  <---changing the index value to 1 
(or higher)

I get no information. So my question is;

Does the DataSet contain the other columns, only not indexed like an array? What I want to do is choose which of the columns to filter out and turn the rest into JSON formatted strings. Any suggestions on how to accomplish that would be greatly appreciated as well

Answer 1

edit

You can also try this:

 ds.Tables(0).Rows(0).Items.Count.ToString

and

 ds.Tables(0).Rows(0).Items(0).ToString

prior

You are checking the rows not the columns. What does

 ds.Tables(0).Columns.Count.ToString

return?

Answer 2

When you do ds.Tables(i).Rows.Count, you're going through the rows that were returned. What exactly are you trying to do?

Answer 3

You're talking about "columns" of data, but your code snippet is dealing with Tables of data (resultsets).

i.e. if your sproc does something like this:

SELECT Column1, Column2, Column3
FROM YourTable
WHERE ID = 123

then you'll have 1 DataTable in your dataset as 1 resultset is returned: ds.Tables(0). This will contain 3 columns: ds.Tables(0).Columns(n) where n is 0 to 2.

Answer 4

For the latter part of your question, JSON, have a look at

Microsoft JavascriptSerializer

Its then simply a case of Serialize(ds.Tables(0)) to get yourself a JSON representation