In this article I will explain with an example, how to use Table Valued Parameters in Entity Framework in ASP.Net using C# and VB.Net.
First, the JSON string will be used to generate the DataTable which will be used to insert into Database.
 
 

JSON File URL

The following JSON file will be used in this article.
https://raw.githubusercontent.com/aspsnippets/test/master/Customers.json
[
  {
    "CustomerId": 1,
    "Name": "John Hammond",
    "Country": "United States"
  },
  {
    "CustomerId": 2,
    "Name": "Mudassar Khan",
    "Country": "India"
  },
  {
    "CustomerId": 3,
    "Name": "Suzanne Mathews",
    "Country": "France"
  },
  {
    "CustomerId": 4,
    "Name": "RobertSchidner",
    "Country": "Russia"
  }
]
 
 

Database

I have made use of the following table Customers with the schema as follows.
Using Table Valued Parameters in Entity Framework using C# and VB.Net
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 

User Defined Table Type

CREATE TYPE [dbo].[CustomerType] AS TABLE(
    [Id] [int]NULL,
    [Name] [varchar](100) NULL,
    [Country] [varchar](50) NULL
)
GO
 

Stored Procedure

CREATE PROCEDURE [dbo].[Insert_Customers]
     @tblCustomersCustomerType READONLY
AS
BEGIN
    SET NOCOUNT ON;
     
    INSERT INTO Customers ([CustomerId], [Name], [Country])
    SELECT [Id], [Name], [Country] 
    FROM @tblCustomers
END
 
 

HTML Markup

The HTML Markup consists of following controls:
Button – For inserting data.
The Button has been assigned with an OnClick event handler.
<asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="OnInsert" />
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.Net;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
 
VB.Net
Imports System.Net
Imports System.Data
Imports System.Data.SqlClient
Imports Newtonsoft.Json
 
 

Using Table Valued Parameter in Entity Framework with C# and VB.Net

Converting JSON string into DataTable

When the Insert Button is clicked, the JSON string is converted to DataTable using JSON.Net i.e. Newtonsoft library.
Note: For more details on how to convert JSON string into DataTable, please refer my article Convert JSON string to DataTable in ASP.Net using C# and VB.Net.
 

Inserting Data into Database using Table Valued Parameter

First, an object of SqlParameter is created which accepts the name of the user defined table Type as parameter and the data type as Structured.
Then, the type of the user defined table i.e. CustomerType and DataTable generated earlier are set to the TypeName and Value properties of the SqlParameter respectively.
After that, an object of AjaxSamplesEntities is created and the ExecuteSqlCommand method of DbContext is called which accepts EXECUTE query of the Stored Procedure along with the SqlParameter object which inserts the records in the database.
C#
protected void OnInsert(object sender, EventArgs e)
{
    ServicePointManager.Expect100Continue = true;
    ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;
    string json = new WebClient().DownloadString("https://raw.githubusercontent.com/aspsnippets/test/master/Customers.json");
    DataTable dt = JsonConvert.DeserializeObject<DataTable>(json);
 
    if (dt.Rows.Count > 0)
    {
        //Defining Table Valued Parameter
        SqlParameter sqlParameter = new SqlParameter("@tblCustomers", SqlDbType.Structured)
        {
             TypeName = "dbo.CustomerType",
             Value = dt
        };
 
        AjaxSamplesEntities entities = new AjaxSamplesEntities();
 
        //Executing Stored Procedure
        entities.Database.ExecuteSqlCommand("EXECInsert_Customers @tblCustomers", sqlParameter);
    }
}
 
VB.Net
Protected Sub OnInsert(ByVal sender As Object, ByVal e As EventArgs)
    ServicePointManager.Expect100Continue = True
    ServicePointManager.SecurityProtocol = CType(3072, SecurityProtocolType)
    Dim json As String = New WebClient().DownloadString("https://raw.githubusercontent.com/aspsnippets/test/master/Customers.json")
    Dim dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(json)
 
    If dt.Rows.Count > 0 Then
        'Defining Table Valued Parameter
        Dim sqlParameter As SqlParameter = New SqlParameter("@tblCustomers", SqlDbType.Structured) With {
             .TypeName = "dbo.CustomerType",
             .Value = dt
        }
        Dim entities As AjaxSamplesEntities = New AjaxSamplesEntities()
 
        'Executing Stored Procedure
        entities.Database.ExecuteSqlCommand("EXECInsert_Customers @tblCustomers", sqlParameter)
    End If
End Sub
 
 

Screenshots

Using Table Valued Parameters in Entity Framework using C# and VB.Net
 

Inserted Records

Using Table Valued Parameters in Entity Framework using C# and VB.Net
 
 

Downloads

Download Code