Click here to Skip to main content
15,868,349 members
Articles / Database Development / SQL Server / SQL Server 2008

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

Rate me:
Please Sign up or sign in to vote.
4.31/5 (8 votes)
9 Oct 2009CPOL6 min read 67K   1.5K   21   8
Using Table-Value Parameters to perform Inserts in SQL Server 2008 from VB.NET.

IndRecipeTest_DB

Introduction

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: www.stephenforte.net.

Background

IndRecipeTest_DB

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:

  • TVP_BOM

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:

SQL
CREATE TYPE TVP_BOM AS TABLE(
  Mat_ID Int,
  PPH Numeric(18,7)
)
GO 

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

SQL
CREATE TABLE Material(
 ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,        -- Primary Key
 -- Foreign Key Reference: MaterialCategory
 MatCat_ID INT NOT NULL FOREIGN KEY REFERENCES MaterialCategory(ID), 
 Name VARCHAR(50) NOT NULL,       -- MaterialName
 Price Money NOT NULL Default(2.5)
)
GO

CREATE TABLE FormulaBOM(
  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
)
GO

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:

SQL
CREATE PROCEDURE usp_FormulaBOMInsert(
  @Form_ID Int,
  @BOM AS TVP_BOM READONLY    -- MUST BE READONLY AND NOT for OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON
  
  BEGIN TRAN
  
    -- If a recipe exists then delete it
    IF EXISTS(SELECT TOP 1 (m.ID)
                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)
    INSERT INTO FormulaBOM
      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
    
  COMMIT

  RETURN
  
END
GO

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...

VB
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()
      pc.Add(par)

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

      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"))), _
                           dr("PPH"))

         ' Add the BOMItem object to the Collection
          Me.Add(bom)
          
        Next

      End If

    End If

  End Sub

  ...
  ' Other Important methods and functions
  ...

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

VB
...
' 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), _
                           dr.Cells("PPH"))

    Me.Add(obj)

  Next
  
End With

curFormula.Recipe.AddNew(curFormula.Id)

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:

VB
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
        SetBOM()

        Try
            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

            cmd.ExecuteNonQuery()

            conn.Close()
            cmd.Dispose()

            GetMaterialsByCategory(cboMatCat.Text)

        Catch ex As Exception
            MsgBox("Ooops!  - " & ex.ToString, _
                   MsgBoxStyle.OkOnly, "ERROR")
        Finally
            conn.Close()
            cmd.Dispose()
        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.

ConnectionString.JPG

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.

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

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

ScreenShotDataEntry.JPG

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.

History

  • Version 1.

License

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


Written By
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.

Comments and Discussions

 
QuestionNice article. One question. Pin
Middle Manager13-Oct-09 3:00
Middle Manager13-Oct-09 3:00 
AnswerRe: Nice article. One question. Pin
Andy_L_J22-Oct-09 1:35
Andy_L_J22-Oct-09 1:35 
GeneralVery nice Pin
Goran Bacvarovski9-Oct-09 4:10
Goran Bacvarovski9-Oct-09 4:10 
GeneralRe: Very nice Pin
Andy_L_J9-Oct-09 8:31
Andy_L_J9-Oct-09 8:31 
GeneralGood article Pin
Mycroft Holmes9-Oct-09 0:27
professionalMycroft Holmes9-Oct-09 0:27 
GeneralRe: Good article Pin
Andy_L_J9-Oct-09 0:44
Andy_L_J9-Oct-09 0:44 
Thanks Mycroft.

I am not a full-time dev. I wear so many hats I dont know what colour my hair is anymore! It has been nerve wracking pressing the publish button, but if someone can learn from me, and I can get some feedback, then all is good?

I don't speak Idiot - please talk slowly and clearly

I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury

Driven to the arms of Heineken by the wife

GeneralRe: Good article Pin
Mycroft Holmes9-Oct-09 0:51
professionalMycroft Holmes9-Oct-09 0:51 
GeneralRe: Good article Pin
Andy_L_J9-Oct-09 1:12
Andy_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.