ThinkingCog

Articles written by Parakh Singhal

Inserting Multiple Rows in SQL Server via Table Valued Parameters

Key take away:

In my last posts, I have described ways to insert multiple rows worth of data in a SQL Server database via a Comma Separated List and XML. In this post I will describe how to insert multiple rows from a .Net application using the most preferred way, a native feature in SQL Server, Table Valued Parameters.

Read on:

A short introduction to Table Valued Parameters:

Parameters are place holder for values that are passed from various constructs outside of a programming artifact. We declare parameters all the time, in various technologies, and in various forms - command line arguments, query string values in ASP.NET, sql parameters that carry information to database in a type safe way, etc. In SQL Server parameters are the way to pass and receive information that needs to be dealt with, and information can come from either within SQL Server or outside of SQL Server like from a .Net application. Parameters are generally made of a primal data type like int, char, varchar etc. When such parameters are modeled on some pre-defined table type, such parameters are called Table Valued Parameters. The important words to note here are “table type”. Not table, but table type, which means a user defined type based on some table structure.

Table Valued Parameters have been supported by SQL Server since version 2008.

To demonstrate the concept of TVPs, 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:

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.

Now consider the following code to make a table type. this type will be used to model a table valued parameter on the table type:

   1:  CREATE TYPE StudentCoursesTableType AS Table
   2:  ([StudentID] [int] NULL,
   3:   [CourseID] [int] NULL);

 

Making a table type makes the type available in the User-Defined Table Types sub-section of Types section.

clip_image002

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Execution of the following code should give you some result depending on what you have available in the StudentCourse table.

   1:  Declare @StudentCoursesTVP as StudentCoursesTableType;
   2:   
   3:  Insert into @StudentCoursesTVP(StudentID, CourseID)
   4:  Select [StudentID], [CourseID] from [dbo].[StudentCourse];
   5:   
   6:  Select * from @StudentCoursesTVP;
 
 

clip_image002[5]

 

 

 

 

 

 

 

 

 

 

 

So now that we are through with the basics of TVP, let’s see how we can use them in our main purpose of processing multiple rows of data. To demonstrate this, I will be using a simple web form application powered by the same data model discussed above. The application helps a student get enroll in the course(s) of his/her choice.

clip_image004

 

 

 

 

 

 

 

 

 

 

 

 

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 parses the incoming object data into a datatable.

2. The stored procedure responsible for receiving the data in the form of a table valued parameter and using it to feed data into the desired table.

Consider the following repository method in the StudentSQLRepository class:

   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:              foreach (Course course in courses)
   7:              {
   8:                  table.Rows.Add(new object[] { studentID, course.CourseID});
   9:              }
  10:              string sql = @"dbo.EnrollStudentInCourses";
  11:              int result = 0;
  12:              SqlParameter studentCoursesTVP = new SqlParameter("StudentCoursesTVP", table);
  13:              studentCoursesTVP.SqlDbType = SqlDbType.Structured;
  14:              using (SqlConnection connection = new SqlConnection(connectionString))
  15:              {
  16:                  using (SqlCommand command = new SqlCommand(sql, connection))
  17:                  {
  18:                      command.CommandType = CommandType.StoredProcedure;
  19:                      command.Parameters.Add(studentCoursesTVP);
  20:   
  21:                      connection.Open();
  22:                      result = command.ExecuteNonQuery();
  23:                      connection.Close();
  24:                  }
  25:              }
  26:              return result;
  27:          }

There are two noteworthy points in the code above:

1. We are passing the datatable to the SQL Server database to be processed. The datatable is modeled on the table type present in the SQL Server database, used by the table valued parameter.

2. The SqlDbtype of the SqlParameter is set to Structured type. This is strictly done to improve the readability of our code and convey meaning in an explicit way. ADO.NET will perfectly parse the datatable correctly had we not declared the SqlDbType to Structured explicitly.

Following is the SQL script of the dbo.EnrollStudentInCourses responsible for parsing the data in the @StudentCoursesTVP table valued parameter correctly.

   1:  CREATE PROCEDURE [dbo].[EnrollStudentInCourses]
   2:      
   3:      @StudentCoursesTVP StudentCoursesTableType readonly
   4:   
   5:  AS
   6:  BEGIN
   7:   
   8:      Insert into StudentCourse (StudentID, CourseID)
   9:      Select StudentID, CourseID from @StudentCoursesTVP    
  10:      
  11:  END
  12:   
  13:  GO

 

If you go and read my two other posts on inserting multiple rows using a comma separated values list and XML, you surely will come to the conclusion that using table valued parameters is the cleanest approach, that doesn’t include any code acrobatics either in .Net code or in SQL code. Not only that, table valued parameters code is highly performant as long as there’s reasonable data involved.

The sample code for this post can be downloaded from the following location:

References:

1. Table Valued Parameters at TechNet

Inserting Multiple Rows in SQL Server via a XML

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: