Inserting Multiple Rows in SQL Server via a XML

15. February 2014 12:33 by Parakh in SQL Server, XML  //  Tags: , , ,   //   Comments
This post discusses how to post multiple rows worth of data to a SQL Server database using XML to minimize calls made to the database to store data.

Key take away:

My last two posts touched on the two methods available in SQL Server to flatten hierarchical XML data into flat relational form. In this post I will build upon those concepts and will cover how to leverage them to insert multiple rows worth of data into a SQL Server database in a single call.

Read on:

Hierarchical XML data can be flattened at SQL Server database level using one of the two ways:

1. OPENXML method

2. Nodes method

One of the reason why we would want to convert XML data into relational data at database level is to push in multiple rows worth of data to be inserted into a single or multiple tables in a single database call. Opening and closing a connection to a database for doing operation can be a costly affair for a website storing a sizeable amount of data at every call. The cost can be minimized by sending all the related data in a single call and parsing it out into distinct rowsets and storing them in the desired table(s). This scenario is especially true when you provide editing capabilities in a tabular or gridview kind of an environment and allow user to check in all the changes in one button click.

The technique of storing multiple rows with the help of XML works on the following strategy:

1. Convert the information into XML hierarchy.

2. Pass the XML to SQL Server.

3. Parse the hierarchy via one of the methods – OPENXML or nodes method and convert it into relational form.

4. Parse the relational form and store it via normal insert query.

To demonstrate I will be using a very simple data model consisting of three tables: Student, Course and an association table supporting many-to-many relationship between Student and Course, StudentCourse.

The Entity-Relationship diagram will clarify the relationship between the tables:

ERD diagram

 

 

 

 

 

 

 

According the ER diagram, a student can take many courses and a course can be taken by many students. The insertion of courses that a student is interested in the association table is an ideal application of this technique.

The following ASP.NET webform that is the web front end that we will use to form a complete example to demonstrate this approach:

image

 

 

 

 

 

 

 

 

 

 

 

 

The coding for the webpage is very simple. The pseudo-code is as follows:

1. Select a student from the drop down.

2. Select from the available courses that the student needs enrollment for.

3. Click on the submit button.

I am going to leave it up to the reader to understand the programming in the web application. It is straight forward and the domain model powering the application is a reflection of the data model depicted above.

The main work is being done at two places:

1. Web application’s repository method which does the work of making a hierarchical XML data from the objects.

2. The stored procedure that converts incoming XML data into relational data and stores it into table.

Consider the following repository method:

   1:  public int GetEnrolled(List<Course> courses, int studentID)
   2:          {
   3:              DataTable table = new DataTable("data");
   4:              table.Columns.Add("StudentID");
   5:              table.Columns.Add("CourseID");
   6:   
   7:              foreach (Course course in courses)
   8:              {
   9:                  table.Rows.Add(new object[] { studentID, course.CourseID});
  10:              }
  11:   
  12:              string data;
  13:              using (StringWriter sw = new StringWriter())
  14:              {
  15:                  table.WriteXml(sw);
  16:                  data = sw.ToString();
  17:              }
  18:   
  19:   
  20:              string sql = @"dbo.EnrollStudentInCourses";
  21:   
  22:              int result = 0;
  23:   
  24:              SqlParameter xml = new SqlParameter("XML", data);
  25:   
  26:              using (SqlConnection connection = new SqlConnection(connectionString))
  27:              {
  28:                  using (SqlCommand command = new SqlCommand(sql, connection))
  29:                  {
  30:                      command.CommandType = CommandType.StoredProcedure;
  31:                      command.Parameters.Add(xml);
  32:   
  33:                      connection.Open();
  34:                      result = command.ExecuteNonQuery();
  35:                      connection.Close();
  36:                  }
  37:              }
  38:   
  39:              return result;
  40:          }

 

The point of interest in the code mentioned above are the lines that push the object data into a datatable and the code that converts the datatable into an XML hierarchy. Please note that the hierarchy will include the name of the datatable that gets set in the .Net code. So please name it appropriately. The resulting XML hierarchy looks something as shown below:

   1:  <DocumentElement>
   2:      <data>
   3:          <StudentID>1</StudentID>
   4:          <CourseID>4</CourseID>
   5:      </data>
   6:      <data>
   7:          <StudentID>1</StudentID>
   8:          <CourseID>5</CourseID>
   9:      </data>
  10:      <data>
  11:          <StudentID>1</StudentID>
  12:          <CourseID>6</CourseID>
  13:      </data>
  14:  </DocumentElement>

It is this XML that gets passed to the SQL Server and is de-serialized into relational form using the nodes method. I have discussed the fundamentals of the nodes method in my last post. The de-serialization can also be carried out by using the OPENXML method.

The core of the dbo.EnrollStudentInCourses stored procedure, responsible for recording the course enrollment data for a student is made of the following code:

   1:  Insert into StudentCourse (StudentID, CourseID)
   2:  SELECT
   3:  data.value('(StudentID/text())[1]','int') as StudentID,
   4:  data.value('(CourseID/text())[1]','int') as CourseID
   5:  FROM @XML.nodes('/DocumentElement/data')
   6:  as StudentCourses(data)
 

NOTE: The SQL code is made keeping in mind the name of the datatable used to capture the data in the .Net code.

The sample code for this post consists of the web application and the powering database. Download it from:

blog comments powered by Disqus

Month List

ParakhSinghal@Twitter.com

Note: For Customization and Configuration, CheckOut Recent Tweets Documentation