Creating a bulk insert using a Stored Procedure and XML
One common task that I as developer have faced is to insert multiple records to a database. There are many different approach to solve a batch insertion but one that I prefer is to use a XML file containing all the records to be inserted. What I have done is to iterate over the records in the XML file and for each XML record execute an Insert operation to create a new record in the database.
I will create a sample to show you how I have created a bulk insertion to a database.
Creating the database tables used for the bulk insert
I created two database tables. A Developer table and an Articles table. In the Developers table I will store the name of the developer and in the Articles table I will store the articles that a developer has written.
1 CREATE TABLE [dbo].Developer
2 (
3 DeveloperId INT IDENTITY NOT NULL PRIMARY KEY,
4 FirstName VARCHAR(64) NOT NULL,
5 LastName VARCHAR(64) NOT NULL
6 )
7 CREATE TABLE [dbo].[Article]
8 (
9 ArticleId INT IDENTITY NOT NULL PRIMARY KEY,
10 DeveloperId INT NOT NULL REFERENCES Developer(DeveloperId),
11 Title VARCHAR(128) NOT NULL,
12 Url VARCHAR(256) NOT NULL
13 )
Creating a Store Procedure to execute the bulk insert
I created a Store Procedure to insert the bulk inserting in the Developer and Article database tables. The Stored Procedure expects a string containing XML content with the information about developers and their articles. What I do in the Store Procedure is to iterate over each record in the XML developer table and for each XML record insert a record into the Developer database table. Then I get all the records in the XML Article table related to the current developer and for each record in the XML Article table I create a record in the Article database table. At the beginning of the Store Procedure I create a transaction, if something goes wrong while executing the bulk inserting I rollback the transaction so ensure inserting all or none record in the bulk inserting. I won’t go into the implementation details, you can find more information in the SQL Server 2005 documentation.
1 ALTER PROCEDURE [dbo].[InsertDeveloperAndArticlesXML]
2 (
3 @xmlContent XML
4 )
5 AS
6 BEGIN
7 DECLARE @XmlDocumentHandle int
8
9 EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @xmlContent
10
11 DECLARE @errorCode INT
12 DECLARE @developerId int
13 DECLARE @firstName varchar(64)
14 DECLARE @lastName varchar(64)
15
16 BEGIN TRANSACTION
17
18 -- I create a cursor to iterate over the developer items in the XML file
19 DECLARE developerCursor CURSOR FOR
20 SELECT DeveloperId, FirstName, LastName
21 FROM OPENXML (@XmlDocumentHandle, '/DataAccessBlogDataSet/Developer',2)
22 WITH (DeveloperId int,
23 FirstName varchar(64),
24 LastName varchar(64))
25 OPEN developerCursor
26 FETCH NEXT FROM developerCursor INTO @developerId, @firstName, @lastName
27 WHILE @@FETCH_STATUS = 0
28 BEGIN
29 DECLARE @dbDeveloperId int
30
31 -- Insert the master record
32 INSERT INTO Developer (FirstName, LastName) VALUES (@firstName, @lastName)
33 SET @dbDeveloperId = @@IDENTITY
34 FETCH NEXT FROM developerCursor INTO @developerId, @firstName, @lastName
35 END
36
37 CLOSE_RESOURCES_DEVELOPER:
38 CLOSE developerCursor
39 DEALLOCATE developerCursor
40
41 -- Remove the internal representation.
42 EXEC sp_xml_removedocument @XmlDocumentHandle
43 END
Creating ADO.NET code to call the Stored Procedure that will execute the bulk insert
I created a typed dataset where I will store the information about the developers and their articles. I will create dummy data for the dataset in order to test the Stored Procedure.
1 //Articles written by Scott Guthrie
2 DataAccessBlogDataSet.DeveloperRow developerRow = this._dummyDataSet.Developer.AddDeveloperRow("Scott", "Guthrie");
3 this._dummyDataSet.Article.AddArticleRow(developerRow, "ASP.NET 2.0 Tips, Tricks, Recipes and Gotchas", "http://weblogs.asp.net/scot");
4 this._dummyDataSet.Article.AddArticleRow(developerRow, "ASP.NET Dynamic Data Preview Available", "http://weblogs.asp.net/scottgu/arc");
5 this._dummyDataSet.Article.AddArticleRow(developerRow, "July 4th Links: ASP.NET, ASP.NET AJAX, Visual Studio", "http://weblogs");
Now that the dataset has been created I only need to call the DataSet.GetXML() method to get the content of the dummy dataset as a XML string. Then I need to create ADO.NET code to call my Stored Procedure. I will use ADO.NET Accelerator to write less ADO.NET but you can use raw ADO.NET in your own implementation.
1 protected void Button1_Click(object sender, EventArgs e)
2 {
3 SqlNetFramework.CiOrderedDictionary values = new SqlNetFramework.CiOrderedDictionary();
4
5 values.Add("xmlContent", this.GetXml());
6
7 int affectedRows = DbManager.Instance.ExecuteNonQuery(1, "SqlServerConnection", values);
8
9 this.Label1.Visible = true;
10
11 if (affectedRows > 0)
12 this.Label1.Text = "Bulk insertion has been executed - " + affectedRows.ToString() + " rows affected.";
13 else
14 this.Label1.Text = "Bulk insertion has failed";
15 }
We have finished, when the user press the “Insert Multiple Records” button all the records in the dataset will be inserted in the database guarantee that the inserting will be an atomic transaction. It means that all or none record will be inserted.
You can download the source code of this sample from the
Data Access Code samples code.