ASP.NET/VB.NET problem solving help!

Question

Hi folks,

Got a problem I need help with. Basically I'm gonna develop a form (part of a bigger web app) that lists a load of clients and there business contact, tech contact 1, and tech contact 2.

The idea is rapid data entry. So one form shows each client with their contacts in dropdowns and I we can change each one then click a save button to do a mass save.

the database looks like this:

tblClient

ClientID

ClientName

BusinessContact

Tech1

Tech2

My idea was to use a repeater to format the data like this:

Client

Business Contact

Tech1

Tech2

Client2

Business Contact

Tech1

Tech2

What I'm stuck on is how to do the mass update? Can I do something like for each item in Repeater1 then do an update SQL statement? -- Jonesy

Answer 1

If you use the GUI tools in VS to build a datasource with DataAdaptors and hook these into a gridview, all the code is written for you, other than calling update on your save button.

Answer 2

A manual approach would be to format the data into an XML block and pass it to a stored procedure, where you can then create an UPDATE statement that JOINS to the XML to perform the update in one shot.

The XML you would want to create would be similar to:

<ClientData>
    <Client ClientID="1" BusinessContact="..." Tech1="..." Tech2="..." />
    <Client ClientID="2" ... />
    ...
</ClientData>

One way to create this by using an XmlWriter and looping over your repeater to harvest the data. In VB.NET:

Dim stream As New System.IO.MemoryStream
Dim settings As New System.Xml.XmlWriterSettings

settings.Encoding = Encoding.GetEncoding("iso-8859-1") 'This encoding handles special characters pasted in from MS Word

Dim writer As System.Xml.XmlWriter = System.Xml.XmlWriter.Create(stream, settings)

With writer
    .WriteStartElement("ClientData")
    For Each item As RepeaterItem In myRepeater.Items
        .WriteStartElement("Client")
        .WriteAttributeString("ClientId", CType(item.FindControl("ClientIdHidden"), HtmlInputHidden).Value)
        .WriteAttributeString("BusinessContact", CType(item.FindControl("BusinessContact"), TextBox).Text)
        ...
        .WriteEndElement()
    Next
    .WriteEndElement()
    .Flush()
End With

Dim xmlString As String = writer.Settings.Encoding.GetString(stream.ToArray())

Then create a stored procedure that takes a parameter where you can pass in the XML:

CREATE PROCEDURE [dbo].[BulkUpdateClients]
(
    @xmlInput AS text
)
AS

DECLARE @xmlHandle int

EXEC sp_xml_preparedocument @xmlHandle output, @xmlInput

UPDATE c
SET
    BusinessContact = x.BusinessContact,
    Tech1 = x.Tech1,
    Tech2 = x.Tech2
FROM tblClient c
    JOIN 
    (
        SELECT
            ClientId,
            BusinessContact,
            Tech1,
            Tech2
        FROM
           OPENXML (@xmlHandle, '/ClientData/Client', 1)
           WITH
           (
               ClientId int,
               BusinessContact varchar(50),
               Tech1 varchar(50),
               Tech2 varchar(50)
           )
   ) x ON c.ClientId = x.ClientId

The code above hasn't been tested, but I think the general pattern is there. I've used this technique for bulk inserts on a number of occasions. I like it because it gets the job done in a single database operation.