Click here to Skip to main content
13,002,628 members (69,494 online)
Click here to Skip to main content
Add your own
alternative version


20 bookmarked
Posted 9 Oct 2009

Table-Value Parameters in SQL Server 2008 - VB.NET

, 9 Oct 2009
Rate this:
Please Sign up or sign in to vote.
Using Table-Value Parameters to perform Inserts in SQL Server 2008 from VB.NET.



Have you ever come across the following scenario where you have to enter lists of data into tables in a SQL Server database? For example, Recipes, Employee Time Lists, or any repetitive data?

Have you had to struggle with arrays or lists and UDFs to manipulate the data in you bulk inserts? For example, in the image above, 7 Insert statements would have to be called from your front end in order to process all the rows in the table.

I was looking for a better solution, one that took the work off the client (and dispensed with multiple server connects) and transferred the grunt work to the server, where it rightly belongs.

While there are plenty of articles on the net about Table Value Parameters in SQL Server 2008, most concentrate on the server-side T-SQL script and forget about the application developer who has to use the database Stored Procedures. For those with less advanced skill sets, these kinds of examples - marrying server-side code with client-side code - can be a great learning tool and provide many a "light-bulb" moment.

Acknowledgements to Stephen Forté's Blog for the inspiration:



To create some background, and avoid having to type so much T_SQL, I have attached this simple database diagram that explains the underlying structure of the tables required. This is a fairly well known pattern. The script to create the sample database can be found at the top of the page. This work has been part of a much larger solution that involves half a dozen projects and dozens of classes, and it would really be too much to post all the code or even the complete solution as, um, there may be secrets in there and, well, you know what we would have to do to you then? I have, however, attached a small and dirty sample app to try and demonstrate the concepts.

Server Side

The server side consists of a small database consisting of a handful of tables, a few Stored Procedures, and the all important Table Value Parameters (User-Defined Types).

A Table Value User_Defined Type is first on our list:


Then, we create the tables that we need:

  • MaterialType
  • MaterialCategory
  • Material
  • FormulaBOM

and finally, a Stored Procedure:

  • usp_FormulaBOMInsert

Actually, a bunch of Stored Procedures, but this is the one that uses a Table-Valued Parameter that is a User-Defined Type.

So, first, we need to create a User-Defined Type to hold our table:

  Mat_ID Int,
  PPH Numeric(18,7)

To help illustrate better, we will throw in the table structure for the Material and FormulaBOM tables:

 ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,        -- Primary Key
 -- Foreign Key Reference: MaterialCategory
 Name VARCHAR(50) NOT NULL,       -- MaterialName
 Price Money NOT NULL Default(2.5)

  ID Int Identity(1,1) Not Null Primary Key,    -- Primary Key
  -- Foreign Key Reference (Material_ID: Formula)
  Formula_ID Int Not Null Foreign Key References Material(ID), 
  -- Foreign Key Reference (Material_ID: Raw Material)
  Material_ID Int Not Null Foreign Key References Material(ID), 
  PPH Decimal(18,7) NOT NULL Check(PPH > 0)    -- Parts Per Hundred

Notice how the structure of our User-Defined Type TVP_BOM matches the Mat_ID and PPH fields of our FormulaBOM table. This is what makes Inserts to the FormulaBOM table easier.

Also note that the FormulaBOM table has two fields that reference the Material table; Formula_ID references a Material of MaterialType formula, and Material_ID which references a Material of MaterialType Raw Material.

Now we will create a Stored Procedure to do the dirty work behind the scenes. In fact, the use of the TVP makes this particular query very simple, as you will see:

  @Form_ID Int,
    -- If a recipe exists then delete it
                FROM FormulaBOM bom
                  JOIN Material m
                    ON bom.Formula_ID = m.ID  
                WHERE  m.ID = @Form_ID)
      DELETE FormulaBOM
        WHERE ID = @Form_ID
    -- Add the new recipe to the Table
    -- The bit where we use the TVP (User_Defined Type)
      SELECT @Form_ID, Mat_ID, PPH
        FROM @BOM

    SELECT bom.ID, f.Name As Formula, m.Name As Material, PPH 
    FROM FormulaBOM bom
      JOIN Material f
        ON bom.Formula_ID = f.ID
      JOIN Material m
        ON bom.Material_ID = m.ID
    WHERE Formula_ID = @Form_ID


OK. Now we have our tables, a User_Defined Type, and a Stored Procedure wired up in the backend, ready to weave its magic. An important point to note is that the Table Valued Parameter must be declared READONLY, and you cannot use it as an OUTPUT parameter. (You will have to flesh out the DB structure by adding a MaterialType table, and a MaterialCategory table, and insert some relevant data, then you're good to go.)

Client Side

Now we come to the client-side code. I won't go into the details about wiring up an n-tier architecture etc... but the concept is very straightforward.

Assume we have a class library containing various classes mapping business objects. For our example, we will assume a Recipe class that is related to Materials and their categories etc...We will create a function to handle adding a FormulaBOM (Recipe) to the database. In the case of the image at the top of the page, what looks like a pseudo-industrial chemical formulation...

Public Class Recipe
  Inherits Generic.List(Of BOMItem)

  Public Sub AddNew(ByVal formID As Integer, dt As DataTable)
    If formID <> 0

      ' Set the required parameters, par and pc are helper
      ' classes that pass data to the datalayer 
      Dim par As New DbParameter("@FormulaID", SqlDbType.nVarChar, formID)
      Dim pc As New ParCollection()

      ' Note: SqlDbType.Structured: Me.ToBOMTable returns System.DataTable
      ' Note: SqlDBType.Structured
      par = New DbParameter("@BOM", SqlDbType.Structured, Me.ToBOMTable)

      Dim dtRet As DataTable = DAL.GetDataTable("usp_FormulaBOMInsert", pc, "Recipe")
      If dtRet.Rows.Count > 0
        For Each dr As DataRow In dtRet.Rows
         ' Create a New BOMItem object (helper class) to hold the extracted data
          Dim bom As New BOMItem(dr("Formula_ID", _
              New Material(True, Convert.ToInt32(dr("Material_ID"))), _

         ' Add the BOMItem object to the Collection

      End If

    End If

  End Sub

  ' Other Important methods and functions

It is now very simple to perform a FormulaBOM Insert from your code:

' Create a Formula Object (Remember how you created this class
' when you were writing the Business Layer? :) )

Dim curFormula = New Formula
curFormula.Name = "1/57 White"
curFormula.MatCategory = "Rigid PVC"
curFormula.AddNew()            ' Load Data into the Object

With curForm.Recipe

  For Each dr As DataGridViewRow In dgvRecipe.Rows
    Dim bom As New BOMItem(curForm.Id, New Material(True, dr.Cells("Chemical").ToString), _


End With


Very Very Simple Code

OK, before you ask for it, here is the real simple way to access this functionality (from the sample project). You still need the database stuff, but the table insert code can be as simple as this:

Private Sub InsertFormulaBOM(ByVal formID As Integer)

    If dgvRecipe.DataSource IsNot Nothing Then

        ' Set up a BOM Table to pass to the database
        ' BOM Table must match TVP_BOM UDT

            conn.Open()     ' Open connection
            cmd = New SqlCommand("usp_FormulaBOMInsert", conn)  ' Set Command
            cmd.CommandType = CommandType.StoredProcedure

            ' Set parameters
            With cmd
                ' Formula ID parameter
                .Parameters.AddWithValue("@Form_ID", formID)
                .Parameters(0).SqlDbType = SqlDbType.VarChar
                ' BOM Table parameter
                .Parameters.AddWithValue("@BOM", ds.Tables("BOM"))
                .Parameters(1).SqlDbType = SqlDbType.Structured     ' Note Structured

            End With




        Catch ex As Exception
            MsgBox("Ooops!  - " & ex.ToString, _
                   MsgBoxStyle.OkOnly, "ERROR")
        End Try
    End If
End Sub

While this was simple, you still need a way to provide the Formula_ID parameter, and get the DataTable data. This is where the elegance of OOP concepts really come to the fore as these tasks can be handled anonymously within your objects... The included sample project is a very simple example of this functionality. It could be done using LINQ or databinding, but there are some basic manual data access, and DataGridView handling techniques involved that may be of interest to some. I have left it unfinished so you can extend and play around with the concept.

How to Use the Included Ffiles

Download the IndRecipeTest script and open it in SQL Server Management Studio. Run the script to create a sample database named IndRecipeTest.mdf.

Next, open the sample VB project and set up your connection string to the database that you have just created.

To get your connection string the easy way:

  • Click View
  • Select Server Explorer
  • Right-click Data Connections
  • Select Add Connection

A dialog will appear:

  • Provide the Server Name
  • Select: Attach a database file
  • Browse to file location, select, and click OK
  • Test the connection

If everything worked out OK, a dialog indicating a successful connection will appear. The database will be added to the Server Explorer tree under DataConnections.

Click on the IndRecipeTest database, and from the Properties window, copy the ConnectionString property to the clipboard.


Open the Project Properties page from the Program menu. Select Settings and paste the connection string into the value field.

Select (ConnectionString) from the type drop down, and give the string a name (connStr comes to mind). When you save these properties, a new app.config XML file will be added to your project. Data held here can now be accessed using the My.Settings construct, and you can reference this anywhere in your code.

Dim conn As New SqlConnection(My.Settings.connStr)

Press F5 to run the program. The following window will appear:


To Create a New Material:

  • Select "Raw Material" from the Material Type combo.
  • Select a Material category.
  • Enter or select a Material/Formula from the Material combo box. If a Formula is selected, the Recipe can be displayed by clicking on the Show Rec. button.

If there is no recipe for a selected formula, you are able to build a new recipe by selecting rows in the Chemical list and clicking the Add button. Provide a PPH (Parts Per Hundred) value and click OK. Note, PPH must be numeric. Data validation is omitted in the sample.

When you have finished creating the recipe, click New to save the data to the database. This is where the SQL Stored Procedure is called.

Check your entry by selecting the formula from the combo box and clicking Show Rec. (Notice that the DataGridView has nicely formatted and sorted data.)

Points of Interest

For more information, you can see Stephen Forté's website (link at the top of the page), or this very useful article on CP by Robin_Roy: Table_Valued Parameters.


  • Version 1.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Australia Australia
Born in New Zealand and transported to Australia for crimes against good taste in the eighties.

Employed by large Manufacturing Co to Coordinate Production Processes, Integrate new technologies, Implement new, in-house MRP/ERP.

Out of my depth, challenged and love it.

You may also be interested in...

Comments and Discussions

QuestionNice article. One question. Pin
Ed Bouras13-Oct-09 3:00
memberEd Bouras13-Oct-09 3:00 
AnswerRe: Nice article. One question. Pin
Andy_L_J22-Oct-09 1:35
memberAndy_L_J22-Oct-09 1:35 
GeneralVery nice Pin
maxxnostra9-Oct-09 4:10
membermaxxnostra9-Oct-09 4:10 
GeneralRe: Very nice Pin
Andy_L_J9-Oct-09 8:31
memberAndy_L_J9-Oct-09 8:31 
GeneralGood article Pin
Mycroft Holmes9-Oct-09 0:27
memberMycroft Holmes9-Oct-09 0:27 
GeneralRe: Good article Pin
Andy_L_J9-Oct-09 0:44
memberAndy_L_J9-Oct-09 0:44 
GeneralRe: Good article Pin
Mycroft Holmes9-Oct-09 0:51
memberMycroft Holmes9-Oct-09 0:51 
GeneralRe: Good article Pin
Andy_L_J9-Oct-09 1:12
memberAndy_L_J9-Oct-09 1:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170626.1 | Last Updated 9 Oct 2009
Article Copyright 2009 by Andy_L_J
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid