Click here to Skip to main content
Click here to Skip to main content

Saving Master-Details form using XML

, 14 Apr 2004
Rate this:
Please Sign up or sign in to vote.
This article shows how to save a master-details form using XML.

Introduction

One of the most common types of form in database applications is the "Master-Details" form. One of the ways to save this kind of form is to loop through the "Detail" sections and insert the values in the table. A better way would be to send all the values to SQL server in one shot and then insert all of them in the table. This is exactly what my code does.

Using the code

As an example, I have used two tables Purchase and PurchaseDetails; the schema of the two tables is as follows:

CREATE TABLE Purchase (
    InvoiceID numeric(18, 0) NOT NULL IDENTITY (1, 1),
    InvoiceDate datetime NOT NULL,
    SupplierName varchar(50) NULL
)
GO 
CREATE TABLE PurchaseDetails (
    InvoiceID int NOT NULL ,
    [Description] varchar (75) NOT NULL ,
    Quantity int NOT NULL ,
    Rate numeric(18, 2) NOT NULL , 
    Amount numeric(18, 2) NOT NULL
)

Next is the stored procedure. The stored procedure has to accept the XML string which can be any string data type. These are the steps to be followed in the stored proc:

  1. Insert the values in the Master table, in this case Purchase.
  2. exec sp_xml_preparedocument to create the XML document of the XML that you have passed.
  3. Using OPENXML, insert the values in the Details table, in this case PurchaseDetails.
  4. Last but not the least, clean up the XML document using sp_xml_removedocument.

All of the functions are very well documented in the MSDN, so will not explain then here again. The code in the stored procedure is as follows:

CREATE PROC InsertPurchaseOrder (@InvoiceDate DATETIME, 
          @SupplierName VARCHAR(50), @PurchaseDetails NTEXT)
AS
BEGIN
     DECLARE @InvoiceID INT
     DECLARE @idoc INT

    INSERT INTO Purchase( InvoiceDate, SupplierName)
     VALUES (@InvoiceDate, @SupplierName)

     SET @InvoiceID = @@IDENTITY

    -- Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @PurchaseDetails
    -- Insert the values in the details table
    INSERT PurchaseDetails (InvoiceID, [Description], Quantity, Rate, Amount)
    SELECT @InvoiceID, [Description], Quantity, Rate, Amount
    FROM OPENXML (@idoc, '/PurchaseOrder/Details', 2)
    WITH PurchaseDetails
    -- Destory the internal representation of the XML document.
    EXEC sp_xml_removedocument @idoc
END

In the form, I have created a DataTable to hold the details of the purchase order. Add this DataTable to a DataSet and convert it into XML form.

 private void CreateDT () {
    // Name is important this will be used in the stored proc 
    dtProd = new DataTable("Details"); 

    // Create the table structure 
    dtProd.Columns.Add("Description", 
       Type.GetType("System.String")).DefaultValue = ""; 
    dtProd.Columns.Add("Quantity", 
       Type.GetType("System.Int32")).DefaultValue = 0; 
    dtProd.Columns.Add("Rate", 
       Type.GetType("System.Decimal")).DefaultValue = 0.0; 
    dtProd.Columns.Add("Amount", Type.GetType("System.Decimal"), 
                                  "Rate * Quantity").ReadOnly = true;
}

private void btnSave_Click(object sender, System.EventArgs e) {
.
.
    ds.Tables.Add(dtProd); 
    DetailsXML = ds.GetXml();
.
.
}

Points to remember

  1. Run the SQL script that is included with this article.
  2. The column names of the DataTable should be the same as in the database.
  3. DataSet name and the DataTable name has to match with names used in the stored proc.

Additional Information

I have used Microsoft's Data Access Application Block for executing the stored proc. You can find the code and documentation here.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Muffadal Jaorawala
Web Developer
United Arab Emirates United Arab Emirates
No Biography provided

Comments and Discussions

 
GeneralADO.NET/OPENXML PinmemberDaniel Stephen Rule15-Apr-04 8:46 
GeneralRe: ADO.NET/OPENXML PinmemberMuffadal Jaorawala15-Apr-04 11:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411028.1 | Last Updated 15 Apr 2004
Article Copyright 2004 by Muffadal Jaorawala
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid