|
How to: Insert Master-Detail Data by using the Transact-SQL OPENXML method with .NET
Erika Ehrli Cabral
March 2005
Introduction
Many applications have components that connect, manage, and consume data from a database. If you are designing/building an application that connects to a database to continously insert, update or delete data, you should keep in mind that each operation will need a round-trip to the database and will consume valuable resources, for example, network traffic, memory, and cpu. Microsoft SQL Server 2000 SQLXML allows (among other things) to manage batch operations in a database, which reduces significantly the need of more than one round-trip to a database. OpenXML
is a Transact-SQL statement that allows to represent data in XML format and can be used to insert, update, and delete more than one row (represented by an element) in a table or group of tables.
Note: The samples provided in this document use the PUBS database shipped with Microsoft SQL Server 2000.
Procedures
To create a stored procedure using SQLXML - OPENXML
|
- Open Microsoft SQL Server 2000 Query Analyzer.
- In the text panel, define the affected database.
[TSQL]
USE MYDATABASE
- Create the procedure and assign a owner and name.
CREATE PROCEDURE dbo.dspSample
- Receive an XML string.
@doc varchar(8000)
AS
- Declare an XML document handle.
DECLARE @hdoc int
- Generate the document in memory.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
- Create a new transaction.
BEGIN TRANSACTION
- If you need to insert rows to a table, use the following sample code and replace the table and field names with the ones you need:
INSERT INTO MYTABLE
SELECT *
FROM OPENXML(@hdoc, 'XPath query')
WITH MYTABLE
- If you need to update rows to a table, use the following sample code and replace the table and field names with the ones you need:
UPDATE MYTABLE
SET
MYTABLE.fieldX = XMLTABLE.fieldX,
MYTABLE.fieldY = XMLTABLE.fieldY
FROM OPENXML(@hDoc, 'XPath query')
WITH MYTABLE XMLTABLE
WHERE MYTABLE.fieldID = XMLTABLE.fieldID
- If you need to delete rows to a table, use the following sample code and replace the table and field names with the ones you need:
DELETE MYTABLE
FROM OPENXML(@hDoc, 'XPath query')
WITH MYTABLE XMLTABLE
WHERE MYTABLE.fieldID = XMLTABLE.fieldID
- Commit the transaction.
COMMIT
- Remove the XML document from memory.
EXEC sp_xml_removedocument @hdoc
Note Skipping this step avoids freeing memory and will result in poor performance.
- Finish and run the procedure.
RETURN
GO
The following sample code shows how to create a stored procedure to insert a publisher and its corresponding titles (master-detail relationship) to the PUBS database:
[TSQL]
CREATE PROCEDURE dbo.dspInsertPublisher_and_Titles
@doc varchar(8000)
AS
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
BEGIN TRANSACTION
INSERT INTO PUBLISHERS
SELECT * FROM OPENXML(@hdoc, '//publisher')
WITH PUBLISHERS
INSERT INTO TITLES
SELECT * FROM OPENXML(@hdoc, '//title')
WITH TITLES
COMMIT
EXEC sp_xml_removedocument @hdoc
RETURN
GO
|
To generate an XML document from your .NET application
|
- Create an XML document with an element for each row you need to insert and the corresponding attribute values. The following example shows how to create an XML document from a .NET application.
[C#]
XmlDocument xmldoc = new XmlDocument();
XmlElement doc = xmldoc.CreateElement("doc");
xmldoc.AppendChild(doc);
XmlElement publisher = xmldoc.CreateElement("publisher");
doc.AppendChild(publisher);
string pub_id = "9919";
publisher.SetAttribute("pub_id", pub_id);
publisher.SetAttribute("pub_name", "DotNetTreats Books");
publisher.SetAttribute("city", "Redmond");
publisher.SetAttribute("state", "WA");
publisher.SetAttribute("country", "USA");
for (int i = 1; i < 4; i++)
{
XmlElement title = xmldoc.CreateElement("title");
doc.AppendChild(title);
StringBuilder titleID = new StringBuilder("DT100");
StringBuilder titleName = new StringBuilder("OOP Concepts and .NET Part ");
title.SetAttribute("title_id", titleID.Append(i).ToString());
title.SetAttribute("title", titleName.Append(i).ToString());
title.SetAttribute("type", "Technical Article");
title.SetAttribute("pub_id", pub_id);
title.SetAttribute("price", "19.9900");
title.SetAttribute("advance", "9000.0000");
title.SetAttribute("royalty", "10");
title.SetAttribute("ytd_sales", "1000");
title.SetAttribute("notes", "Object-Oriented Programming concepts and samples.");
title.SetAttribute("pubdate", "2005-01-30");
}
[Visual Basic]
Dim xmldoc As XmlDocument = New XmlDocument
Dim doc As XmlElement = xmldoc.CreateElement("doc")
xmldoc.AppendChild(doc)
Dim publisher As XmlElement = xmldoc.CreateElement("publisher")
doc.AppendChild(publisher)
Dim pub_id As String = "9919"
publisher.SetAttribute("pub_id", pub_id)
publisher.SetAttribute("pub_name", "DotNetTreats Books")
publisher.SetAttribute("city", "Redmond")
publisher.SetAttribute("state", "WA")
publisher.SetAttribute("country", "USA")
Dim i As Integer = 1
Do While (i < 4)
Dim title As XmlElement = xmldoc.CreateElement("title")
doc.AppendChild(title)
Dim titleID As StringBuilder = New StringBuilder("DT100")
Dim titleName As StringBuilder = New StringBuilder("OOP Concepts and .NET Part ")
title.SetAttribute("title_id", titleID.Append(i).ToString)
title.SetAttribute("title", titleName.Append(i).ToString)
title.SetAttribute("type", "Technical Article")
title.SetAttribute("pub_id", pub_id)
title.SetAttribute("price", "19.9900")
title.SetAttribute("advance", "9000.0000")
title.SetAttribute("royalty", "10")
title.SetAttribute("ytd_sales", "1000")
title.SetAttribute("notes", "Object-Oriented Programming concepts and samples.")
title.SetAttribute("pubdate", "2005-01-30")
i = (i + 1)
Loop
|
To insert data to the corresponding database using ADO.NET
|
- Create a connection and a command that will call the stored procedure and send the XML document as a parameter.
[C#]
string connS = "data source=(local);database=pubs;integrated security=SSPI;persist security info=false";
SqlConnection sqlConn = new SqlConnection(connS);
sqlConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dspInsertPublisher_and_Titles";
cmd.Parameters.AddWithValue("@doc", xmldoc.OuterXml);
cmd.ExecuteNonQuery();
sqlConn.Close();
[Visual Basic]
Dim connS As String = "data source=(local);database=pubs;integrated security=SSPI;persist security info=false"
Dim sqlConn As SqlConnection = New SqlConnection(connS)
sqlConn.Open()
Dim cmd As SqlCommand = New SqlCommand
cmd.Connection = sqlConn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dspInsertPublisher_and_Titles"
cmd.Parameters.Add("@doc", xmldoc.OuterXml)
cmd.ExecuteNonQuery()
sqlConn.Close()
|
See Also
How to perform bulk updates and inserts by using the OpenXML method with .NET providers in Visual Basic .NET
Note: The sample source code*
for this document works only in Visual Studio 2005.
|