Saving Master-Details form using XML

• Download source - 18.9 Kb

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.