Loading a DataSet - Newby

[Fred Nelson]

I have an applicatioin that I'm writing that uses a "case" file that
contains over 350 columns and more may be added in the future. I would like
to create a dataset with all the column names and only one case record that
is delivered by a stored procedure. (I have a stored procedure that works
so my question is only on loading the DataSet.)

The DataSet will only be used for printing form letters and then will be
zapped. I would like to do it this way rather than maintain a stored
procedure that returns 350 variables if possible so that I don't have to add
new code each time a column is added.

For example:

dim myDataSet as DataSet = New Dataset("MyCaseData")
dim myCaseTable as DataTable = New DataTable
dim my

dim caselib as new data.caselib
mycasetable = caselib.getonerec(case#) // returns an sql reader with one
record

So: I guess I'm asking how to define a data set and load data columns and
data from an SQL Data Reader

Then I would access the info by column name.

At the end of the process I would set the myDataSet = nothing

Any help or pointers to where to look to do this would be GREATLY
appreciated!

Thanks,

Fred

 

[CJ Taylor]

To get the original 350 fields (instead of typing them in) create a strongly
typed dataset, then in your server explorer, go to your sql connection for
you rdatabase, find the stored procedure, and drag that onto your newly
created dataset. This will populate your first 350 fields, then every field
after that, you can just easily add to the bottom of the list.

HTH,
CJ

 

[Cor Ligthert]

Hi Fred,

I do not know what to think from your question, or you are teasing us or you
are really a newby in dotNet. I was in doubt if I would write an answer,
however it intrigue me.

Making a datatable with 350 rows and 1 column cost no time at all and is
endless extensible.

Setting the dataset to nothing is a useless operation and should be avoided.

Using the datareader and fill a dataset is not done, because for that is the
dataadapter fill special made.

And maybe your problem is even better to do when you have really a table
with only one row simple using only the datareader (or maybe when you want
only one value from that table using the executenonscalar).

However it is "posible" in the way as CJ told you .

Cor

 

[Fred Nelson]

Cor:

Thanks for your reply! Let me assure that this is not "teasing"! And I'm
VERY new to this!

I have an application that tracks medical records for hospital patients and
there is an ever increasing amount of information that is required on a
routine basis. (I've been supporting this application for almost 11 years
and I am now converting it to use SQL 2000 and VB.Net). The main case
database really does have over 350 columns!

The design goal is to reduce the impact of adding the new fields -
especially in the part of the system that generates standard form letters.
In the existing system users are able to add references to new fields and
then the information is interpreted and obtained from a FoxPro database.
Any of the 350 fields may be needed in a letter or report.

In the new system I would like to be able to use a stored procedure that is
simple, for example:

SELECT * from CASEMAST where cs_ident = @p_searchid

This would of course return only one record. Then I would like to create,
define, and load a dataset with one record (0) that I could reference by
colum name(0).

When I said that I was going to set the dataset to nothing I meant that
after the letters were generated I would not need the data any longer - no
updates would be sent back to the database.

It appears that this is not going to be possible so I will need to create a
stored procedure with 350 parameters and add the new fields as needed.

Fred

 

[Cor Ligthert]

Hi Fred

I think that it is very easy in dotnet to do this

Make a connection string
www.connectionstrings.com

Than you can make a connection
dim conn as new sqlclient.connection(connectionstring)

A command (you can also use the selectcommand in the adapter however this
shows nicer)
Dim cmd As New SqlCommand("EXECUTE MYDATABASE.dbo.SelectMySelect", conn)
cmd.Parameters.Add(New SqlParameter("@p_Searchid", SqlDbType.NVarChar,
200)).Value = Whatever

Than the dataset
dim ds as new dataset

Than the dataadapter
dim da as new sqlclient.sqldataadapter(cmd)

fill the dataset
da.fill(ds)

Use the 50th field of your dataset to show in a messagebox
messagebox.show(ds.tables(0).rows(0)(49).toString) 'instead of the 49 you
can use the columname between "" remember that is case sensetive.

In your situation you do not have to open or close the connection because
that does the dataadapter for you, however you can set the dataadapter in a
try and catch block and than that fill is.

try
da.fill(ds)
catch ex as exception
messagebox.show(ex)
end try

I hope this helps, everything is of course typed in above so watch typos see
it as pseudo code.

Cor