returning result sets from a sub/function

[Heath P. Dillon]

Hi,

I do a lot of repeat calls to a database, and it would make sense for me
have a single function that I can pass just the query to, and it returns the
resultset which I can then work with.

I am sure this is easy and I am doing something basic wrong..

So I have declared my function :

Public Function ExecuteODBCCommand(ByVal Connection As String, ByVal
ODBCCommand As String) As Odbc.OdbcDataAdapter
do the connection using the connection string ... not an option in
my app to pass in a reference to an exisiting connection
execute the odbccommand....
ODBCDataAdapterQuery.Fill(dsData, "QueryTable")
Return (ODBCDataAdapterQuery)
End Function

The first problem is that I am using a global dataset dsdata, so each table
within the dataset needs to be unique. My thoughts on this are to create a
table in the calling module, and simply pass it as a paramater. Just get a
bit confused when I pass back the dataadapter how do I also pass back the
table ?

I don't seem to be able to get the calling module to work properly after the
function completes..

......

Also if the dsdata dataset is being passed back and forth, am I just passing
a reference to the dataset, or am i chewing up memory and actually moveing
the dataset about ?

Once the dataset is back in the calling module, I assume I can just to my
usual looping throught the dsdata.tables("querytable").rows..

 

[Bernie]

Heath,

I pass the table back from the function. Then I can directly copy the
functions return into my dataset. Something like this;

in my calling code;

myGlobalDataset.Tables(myTableToLoad) = ExecuteODBCCommand("SELECT * FROM
Persons")

Public Function ExecuteODBCCommand(ByVal Connection As String, ByVal
ODBCCommand As String) As DataTable
Dim myTable as DataTable
do the connection using the connection string ... not an option in
my app to pass in a reference to an exisiting connection
execute the odbccommand....
Code to fill the table ...
Return myTable
End Function

Note: I wrote this by hand so my syntax may be a bit off.

This example stays generic on the table type, but if I know the table
definition, then I can set the return type as that kind of table. The code
above will probably need some casting to get your table types the same.

Does this help any?

Bernie

 

[Cor Ligthert[MVP]]

Heath,

Have a look at the odbcdataadapter
http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdataadapter(VS.80).aspx

And mapping look for that to the sample in this page
http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx

Cor