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
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:
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:
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:
CREATE TABLE Material(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
MatCat_ID INT NOT NULL FOREIGN KEY REFERENCES MaterialCategory(ID),
Name VARCHAR(50) NOT NULL,
Price Money NOT NULL Default(2.5)
)
GO
CREATE TABLE FormulaBOM(
ID Int Identity(1,1) Not Null Primary Key,
Formula_ID Int Not Null Foreign Key References Material(ID),
Material_ID Int Not Null Foreign Key References Material(ID),
PPH Decimal(18,7) NOT NULL Check(PPH > 0)
)
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:
CREATE PROCEDURE usp_FormulaBOMInsert(
@Form_ID Int,
@BOM AS TVP_BOM READONLY
)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON
BEGIN TRAN
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
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...
Public Class Recipe
Inherits Generic.List(Of BOMItem)
...
Public Sub AddNew(ByVal formID As Integer, dt As DataTable)
If formID <> 0
Dim par As New DbParameter("@FormulaID", SqlDbType.nVarChar, formID)
Dim pc As New ParCollection()
pc.Add(par)
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
Dim bom As New BOMItem(dr("Formula_ID", _
New Material(True, Convert.ToInt32(dr("Material_ID"))), _
dr("PPH"))
Me.Add(bom)
Next
End If
End If
End Sub
...
...
It is now very simple to perform a FormulaBOM Insert from your code:
...
Dim curFormula = New Formula
curFormula.Name = "1/57 White"
curFormula.MatCategory = "Rigid PVC"
curFormula.AddNew()
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:
Private Sub InsertFormulaBOM(ByVal formID As Integer)
If dgvRecipe.DataSource IsNot Nothing Then
SetBOM()
Try
conn.Open()
cmd = New SqlCommand("usp_FormulaBOMInsert", conn)
cmd.CommandType = CommandType.StoredProcedure
With cmd
.Parameters.AddWithValue("@Form_ID", formID)
.Parameters(0).SqlDbType = SqlDbType.VarChar
.Parameters.AddWithValue("@BOM", ds.Tables("BOM"))
.Parameters(1).SqlDbType = SqlDbType.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.
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.
History
