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:gif

  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:

test1

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:

indexBefore

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.

insertingMultiple

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

afterInserting

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.

That is all from our side for now. Do tell us if you have got some more information regarding the same in the comment section. For more information, log on to codeproject.com.

Special thanks to Mr. Aditya Gupta
Aditya is an ASP.NET developer and web enthusiast, presently associated with Enuke Software Pvt. Ltd, an India based IT Outsourcing Company. Aditya believes that an effective web strategy is beyond the concepts of software and hardware implementations.

Hope you liked the whole cover-up. Stay tuned!


Comments

  • share
  • share
  • share
Loading Facebook Comments ...

Leave a Reply

Your email address will not be published. Required fields are marked *

         five + = 13

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Shares