Table Value Parameters, SQL Server 2008, and ASP.NET

• Download source code

Introduction

This article will help you in understanding the working of table value parameters in SQL Server 2008 with ASP.NET. I have used ASP.NET MVC Framework 3.0, however you can use it with other frameworks of ASP.NET MVC or with classic ASP.NET.

Background 

In the developing world of Object Oriented Programming, there are various instances when we need to store an entire array of objects in our database. In such instances, programmers use one of the following two options:

1. Serialize the object array and its parameters with custom delimiters and deserialize them at the database end and perform all the database operations. However it makes computation quiet complex and time consuming apart from the fact that the complete array of objects is passed as a varchar parameter which has its own limitations.
2. Loop the entire array of objects and perform a database operation on each array element by separately creating a connection. However it is less complex in programming but still not a feasible option as we need to create a connection on every iteration.

Microsoft SQL Server 2008 has solved both the above problems by introducing the Table Value Parameter using which the user can create a custom parameter of type table which can be used in stored procedures easily. 

Using the code

Table value parameters can be created using the following syntax:

create type TVPType as table(
Name nvarchar(500),
Salary decimal(18,2),
Age int,
EndHeader bit
);

In the above code snippet, we need to mention the type for creating the custom parameter, its name, and type of custom parameter you want to create; in our case, it is table followed by the structure of the table parameter. Once you execute the code, you can find your custom parameter in:

Now we need to create the procedure for inserting the data received from the application. We will use the following stored procedure:

create proc AddDetails
@tvp TVPType readonly
as begin
insert into TVPTable(Name, Age, Salary) select t.Name,t.Age,t.Salary from @tvp t;
end

As we have completed our database side now, we will move towards the application side. Open a new MVC project or the one which you like to use. Instead of using Entity Framework, I have used typed datasets as Entity Framework does not support Structured Type parameters which are used for our table value parameter on the application side.

Following are the screenshots of application before and after inserting records to the database using the table value parameter.

Page display before inserting record:

Now when we are inserting our records I have allowed the user to insert as many records as he wants by using the following JavaScript code:

function addRow() {
    var table = document.getElementById('recordTable').lastChild;
    if (table) {
        var id = Number(table.children[table.children.length-1].id);
        if (!isNaN(id)) {
            id+=1;
            var child = document.createElement('tr');
            child.id = id;
            var html = '<input type="text" id="name_' + id + '" maxlength="50"/>';
            html += '<input type="text" id="age_' + id + '"/>';
            html += '<input type="text" id="salary_' + id + '"/>';
            child.innerHTML = html;
            table.appendChild(child);
        }
    }
}

The user can add a new row using the Add more record button and finally submit the records using the Submit button.

We can pass all the data using a JSON object to the controller by making an AJAX request. Following is the JavaScript code for achieving the above mentioned task:

function submitRecords() {
    var table = document.getElementById('recordTable').lastChild;
    if (table) {
        
        var childArr = table.children;
        if (childArr.length > 0) {
            var jsonArr={tvp:[]};
            var nameObj, ageObj, salaryObj, id;
            var counter = 0;
            for (var i = 1; i < childArr.length; i++) {
                id = childArr[i].id;
                nameObj = document.getElementById('name_' + id);
                ageObj = document.getElementById('age_' + id);
                salaryObj = document.getElementById('salary_' + id);
                if (nameObj != null && nameObj.value.trim() != '' && ageObj != null && 
                       ageObj.value.trim() != '' && salaryObj != null && salaryObj.value.trim() != '') {
                    jsonArr.tvp.push({
                        "name":nameObj.value.trim(),
                        "age":ageObj.value.trim(),
                        "salary":salaryObj.value.trim()
                    });                        
                }
            }
            if (jsonArr.tvp.length > 0) {
                $.ajax({
                    url: "../TVP/SubmitRecord",
                    data:{"data":JSON.stringify(jsonArr)},
                    type: "POST"
                });
            } else
                alert("Add data please");
        }
    }
}

At the controller side we have created a function SubmitRecord which receives our AJAX request and forwards the data received from the AJAX request to its corresponding model.

[HttpPost]
public ActionResult SubmitRecord(string data)
{
    TVPModel.StoreValues(data);
    return View();
}

In the above code, the HttpPost attribute specifies to the invoked function when the POST request is received. I have used the NewtonSoftJson library for parsing the JSON object at the model class which will parse the JSON data and store it in a DataTable at our code side.

public static void StoreValues(string data)
{
    JToken token = JObject.Parse(data);
    var tvp=token.SelectToken("tvp");
    DataTable recordTable = Params.GetDetailsTable();
    foreach (var value in tvp)
    {
        DataRow row = recordTable.NewRow();
        row["Name"] = value.SelectToken("name");
        row["Age"] = value.SelectToken("age");
        row["Salary"] = value.SelectToken("salary"); ;
        row["EndHeader"] = true;
        recordTable.Rows.Add(row);
    }
    TVPBL bl = new TVPBL();
    bl.InsertTVP(recordTable);
}

The DataTable created is passed to the database procedure as a parameter. This DataTable should have the same structure as mentioned in our type at the database end. I have created a BL class responsible for creating a table adapter for our typed dataset and then calling the specified procedure.

public void InsertTVP(DataTable tvpTable)
{
    TVPTableTableAdapter adapter = GetTvpTableAdapter();
    adapter.AddDetails(tvpTable);
}

After completing the above process, our data is stored in our database. Now when we navigate to our home page which was showing the message No details to show, it will not be visible, and our page looks as follows:

Points of Interest

There are a few points on which I want to put light in the end:

1. Entity Framework currently does not support Table Value Parameters.
2. Table Value Parameters cannot be edited but can be deleted and recreated only when any stored procedure in your database does not have any reference to that table value parameter.
3. Table Value Parameters are used in Readonly mode. 
4. Table Value Parameters cannot be used in user defined functions in the database.
5. Table Value Parameters and the structure of our DataTable should match, i.e., columns should be defined at the same position.