StrataFrame Forum

A good Forum for VB Help with data adapters and datasets?

http://forum.strataframe.net/Topic21688.aspx

By Terry Bottorff - 1/27/2009

This is really not a Strataframe issue. I am trying to save values from an array to a database table. I am not sure if I can use a BO or not but I was trying to write it on my own. I can get it to save one value from the array but not more? If anyone knows where I might post my code and let the Pro's have at it, I would apprecaite it. TIA.

Buffie

By Dustin Taylor - 1/27/2009

We've written a couple of routines in .NET to help with this situation in SQL for our medical appliation. We sometimes need to pass over a dynamically sized list of PKs for use within a SQL sproc. To do that we first convert the list into a binary stream within .NET and pass that stream to the SQL sproc as a VarBinary(MAX) paramater. Once in the sproc, we have a a custom CLR udf that turns the binary stream into a single-column table containing all of the PKs from the list. 

So, within the sproc, you just join in the results of the UDF to a select statement to limit the results down to the contents of the list:

SELECT
   CST.*
FROM Customers AS CST
INNER JOIN dbo.Udf_BytesToPkList(@CustomerPKs) AS CPK ON CST.cst_pk = CPK.pk

Does that sound like something that would handle your need here? The custom UDF is really the only part of the process that has any complexity at all, and I can go into greater detail there if it sounds like something that can help you out Smile.

By Terry Bottorff - 1/27/2009

I'm truly a newbie so I believe that is over kill for what I want. I want to iterate thru and array and write to a table. 

By using the command builder I am now able to write the data and the PK to the table. Learned a great deal doing that but now I can not seem to delete the old data just before I write the new data. Should I be able to use the command builder to do that?

TIA

By Dustin Taylor - 1/27/2009

So you are bulding the command in .NET via SqlCommandBuilder?  What data are you wanting to delete first? The entire existing table in SQL? Any records in the table matching entries in your array? Something else?

Forgive me, I'm a bit slow at times Smile

By Trent L. Taylor - 1/28/2009

Also, when you say that you are trying to save an array to a database, are you wanting to create a new row for each array element or store an array within a single row of a record?  This could alter our response as well.
By Terry Bottorff - 1/28/2009

Yes to both of your questions. 1 row for each array element and delete All data in the table before I start saving the new array data.

TIA.

By Trent L. Taylor - 1/29/2009

Well, you can just add a new row for each array element by enumerating the array.  So you may want to create a method on the BO that accepts an array and creates the record:

Public Sub CreateRecordsByArray(ByVal e As Integer())
    '-- Place any deletion logic here to remove any undesired records from within the BO or back at the server. 

    '-- Cycle through all of the array rows and add a record for each row
    For Each i As Integer in e
         Me.NewRow()
         Me.MyField = i
    Next
End Sub
By Terry Bottorff - 1/29/2009

That's the kind of thing I was looking for. Thank you.