Q: send dataset as a parameter

[Guest]

Hello,
Can I send a dataset as a parameter into stored procedure and import data to
a table in the stored procedure?
Thanks,
Jim.

 

[Scott M.]

I don't think a stored proc. would accept a DataSet because the SP is
running on your DB and the DB doesn't know what a DataSet is.

 

[Wilco Bauwer]

If you insist, you could have a text parameter in your stored
procedure. You could pass the xml as text, and in the SP use OPENXML to
intepret the text as xml. At that point, you can do xpath queries, etc.

 

[Scott M.]

You can absolutely pass text (in whatever form you like: plain text, html,
xml, etc.). But if you pass XML as a parameter to a SP, then you find
yourself in the same boat as if you passed a DataSet in terms of dealing
with the XML. Will the DB know what XPath is?

Can you do XPath queries on a DB?

 

[D.W. Warlock]

You can absolutely pass text (in whatever form you like: plain text, html,
xml, etc.). But if you pass XML as a parameter to a SP, then you find
yourself in the same boat as if you passed a DataSet in terms of dealing
with the XML. Will the DB know what XPath is?

Yes, SQL Server 2000/2005 (also MSDE) can utilize both XPath and XML. I
believe that Oracle 9i and up can also handle XPath/XML. Other RDBMSs
that can: FireBird (1.3x and above), MySql(limited support in the 4.x.x
versions).

 

[Wilco Bauwer]

Lookup OPENXML in the documentation of SQL server.

 

[Guest]

Thanks guys. I will try XML as last resort. I am wondering if I have a
dataset, which is basically a DataTable, couldn't I create a SQL table and
dump all the data from DataTable to this SQL table programmatically in
asp.net. I have some data in my dataset, I am trying to dump it to a table in
SQL Server.

 

[Scott M.]

Well, first understand that a DataSet is much more than just a DataTable.
Technically, a DataSet does not hold any data at all. It is just a
container to hold DataTables.

But the point of your question is, can you programmatically move the data
from a DataTable into a SQL table. Absolutely! There are several way to
accomplish this:

1. Use a .NET DataAdapter to populated the DataTable in the first place (or
to map the original table's structure to a .NET DataTable) and just call the
DataAdapter's Update method, which will move the DataTable data to the
source table.

2. Write a custom .NET Command (either SQL or SP) and call the
ExecuteNonQuery method of the Command to update the original table.

3. Serialize the data from the DataSet (via the GetXML method of the
DataSet) and either store the XML or parse the XML on the DB.

 

[Guest]

Hello Scott,
First, thank you very mush for your great help. I had the following question
in the past, nobady replied. I have a proprietary database keeping table:
TableSrc: fname,lname,age In my asp.net application I am retrieving data
from TableSrc into a dataset: DataSet1 though an adapter. Now I created a SQL
Server table TableDest: Fname,Lname,Age and I want to append DataSet1 into
TableDest. How should I do this? Remember, DataSet1 is linked to TableSrc,
not TableDest. Are you suggesting me to create another adapter for TableDest
and use adapter.update(DataSet1), if yes, do you have any example for this, I
am quite new in asp.net.
Thanks,
Jim.

 

[Scott M.]

Hi Jim,

Since you populated the dataset using a dataadapter that is linked to the
proprietary db and you want to take that data and append it to a different
db, you can't (as you probably know) use the same dataadapter that got you
the data in the first place.

So yes, you must create a different dataadapter that is linked to the SQL
Server table and pass the dataset data to this new dataadapter. It would go
something like this (VB.NET pseudo-code):

'Create the connection and dataadapter objects needed for SQL Server
Dim connectionString As String = your connection string here or get from
Web.Config
Dim strSQL As String = "SELECT * FROM TableDest"
Dim sqlCon as New SqlClientConnection(connectionString)
Dim sqlDA as New SqlClientDataAdapter(strSQL, sqlCon)

'Configure the dataadapter's UPDATE command to suit your needs
sqlDA.UpdateCommand.CommandText = your update statement here

sqlDA.Update(DataSet1)

Remember this is pseudo-code off the top of my head, so I might have missed
something, but this is the process you'll need to go through.

Good luck!

 

[Sudhanshu Mishra]

Can I send a dataset as a parameter in an stored procedure.


Regards,
Sudhanshu

 

[Guest]

Scoot thanks again for the reply. You are at the heart of my problem, how
should I write my update command, I could not just say "insert into TableDest
(...) Values(...)", you know the values are in dataset, not in a table.

 

[Scott M.]

Actually, you'd need to write the CommandText for the DataAdapter's
InsertCommand, not the UpdateCommand (sorry about that). Yes, your Insert
statement would look something like what you've written (INSERT INTO
TableDest () VALUES ()).

You could just use a CommandBuilder object to generate this for you:

Dim cb as New SqlClientCommandBuilder(sqlDA)

This line will cause the sqlDA's InsertCommand to be automatically generated
for you based on the SELECT command used in the instantiation of the sqlDA
in the first place.

 

[Scott M.]

This is the same as the original question in the thread. Please go back and
read this thread for your answer.

 

[Guest]

Scott,
You helped me on this and in the past a lot. I really appreciate it. I am
completely lost from now on. The following code does not fail but does not
update the table either. SqlClientCommandBuilder is not recognized in my
environment.

I copy-paste the code I have up to now. What it does for the moment, once
user clicked Button1, it goes to this propriatery database and fills dataset
Ds and displays it on the screen and then calls ELiveConnSQL to import Ds
into TableDest which has the same structure as sqlStr select string. Can you
complete ELiveConnSQL function?



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim sqlStr As String
sqlStr = "SELECT pate_id as PtID,date as ADate, time as ATime, name
as PtName FROM TableSrc WHERE date ='20050222' AND Loc='0A1'"

ELiveConn(sqlStr)
End Sub

Private Sub ELiveConn(ByVal sqlStr As String)

Dim rowNum As Integer
Dim colNum As Integer
Dim retValue As String

Dim strConn As String = "Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=\\TServer\Test;Exclusive=No" ' working,

Dim Conn As OdbcConnection = New OdbcConnection(strConn)
Try
Conn.Open()
Catch e As System.Exception
Response.Write("Connection has failed: " & strConn)
Exit Sub
End Try

Dim Da As New OdbcDataAdapter
Dim Ds As New DataSet
Dim cmdSelect As New OdbcCommand
Dim i As Integer
Dim Dr As DataRow
Dim Dt As DataTable

' populate dataAdapter and dataset
cmdSelect = Conn.CreateCommand
cmdSelect.CommandText = sqlStr
Da.SelectCommand = cmdSelect
Da.Fill(Ds, "ReturnedRows")
rowNum = Ds.Tables(0).Rows.Count()
colNum = Ds.Tables(0).Columns.Count()
Response.Write("Row Count: " & rowNum & " Column Count: " & colNum)


' populate datagrid
DataGrid1.DataSource = Ds
DataGrid1.DataMember = "ReturnedRows"
DataGrid1.DataBind()

retValue = ELiveConnSQL(Me.SqlConnection1.ConnectionString, sqlStr,
Ds)

' close connection
Da.Dispose()
Conn.Close()
End Sub

Private Function ELiveConnSQL(ByVal myConnStr As String, ByVal mySQLStr
As String, ByVal myDs As DataSet) As String
Dim myConn As SqlConnection
Dim myDA As SqlDataAdapter
Dim retValue As String

myConn = New SqlConnection(myConnStr)
myDA = New SqlDataAdapter(mySQLStr, myConn)
'myDA.InsertCommand.CommandText = "insert ??"

'Dim cb As New SqlClientCommandBuilder(myDA)

myDA.Update(myDs) 'Fill the DataSet with the rows returned.
retValue = "SUCCESS"

myDA.Dispose() 'Dispose of the DataAdapter.
myConn.Close() 'Close the connection.
Return retValue
End Function

Thanks you very much.
Jim.

 

[Scott M.]

Hi Jim. Make that a SQLCommandBuilder, rather than a SQLClientCommandBuilder
and see if that helps.