dynamic creation of datasets from stored sql server procs

[Brian Henry]

How would I create a dataset dynamicly on the fly.. I looked at the example
in the MSDN article on datasets but it wouldn't work (the northwind example)

here's what i want to do...
I want to execute the stored procedure "SP_GETNAMES" on SQL server and have
it return the table, which could change over time as more return columns get
added of course, so instead of makeing a fixed data set as you would when
you generate one with the data adapter, i want to create one dynamicly...
any examples on how i would do that with that stored procedure name? thanks!

 

[Bernie Yaeger]

Hi Brian,

I'm not sure I'm following what you're after, but there is a fairly simple
way to create an sp dynamically, and thus you can run anything dynamically.
Here's how (I explain at the bottom):
ocmd = New SqlCommand("exec sp_dropsp_copyintomagt", oconnx)

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

creationstring = "CREATE PROCEDURE sp_copyintomagt AS " _

& "if exists (select * from information_schema.tables where table_name = " _

& Chr(39) & magtfilename & Chr(39) & ")" & vbCrLf & "drop table " _

& magtfilename & vbCrLf _

& "select * into imc_extra.." & magtfilename & " from imc..hvimport" _

& vbCrLf & "go"

Dim sqladaptx As New SqlDataAdapter

' create a non-table designating sqldataadapter for these non-queries

sqladaptx.SelectCommand = New SqlCommand(creationstring, oconnx)

Try

sqladaptx.SelectCommand.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

ocmd = New SqlCommand("exec sp_copyintomagt", oconnx)

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

First I delete the sp. Then I create the sp using executenonquery. Finally
I execute the new sp, which was created dynamically.

Let me know if you have any questions about this approach.

HTH,

Bernie Yaeger