Alternate to sql server table values parameter

Existing table valued parameter has fields like ID, product name, account holder id and product price. I also need to validate the values that are passed.

I am working on it. I will get back to you soon.

But why are you not able to use Stored Procedure? What issue you are facing? Please explain.

Hi tsakumar81,

What issue you are facing while using Table Valued parameter in edmx file?

• Table Valued Parameters are only supported in raw/inline SQL in Entity Framework because we just pass any SqlParameter through to the underlying ADO.NET provider.

Here i have create a sample using the Table Valued parameter in Stored Procedure.

For configuring Stored Procedure in Entity Framework refer following article.

Call and execute a Stored Procedure in Entity Framework in ASP.Net using C# and VB.Net

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Table Valued function

CREATE TYPE CustomerType AS TABLE
(  
    CustomerID NCHAR(5),  
    CompanyName NVARCHAR(40),  
	ContactName NVARCHAR(30),  
    City NVARCHAR(15),
    Country NVARCHAR(15)
) 

Stored Procedure

CREATE PROCEDURE Customers_InsertCustomer
	@Customer CustomerType READONLY  
AS  
BEGIN  
	INSERT INTO Customers 
		  (CustomerID
		  ,CompanyName
		  ,ContactName
		  ,City
		  ,Country)
	SELECT CustomerID
		  ,CompanyName
		  ,ContactName
		  ,City
		  ,Country
	FROM @Customer  
END

Namespaces

using System.Data;
using System.Data.SqlClient;

Code

DataTable dtCustomers = new DataTable();
dtCustomers.Columns.Add("CustomerID", typeof(string));
dtCustomers.Columns.Add("CompanyName", typeof(string));
dtCustomers.Columns.Add("ContactName", typeof(string));
dtCustomers.Columns.Add("City", typeof(string));
dtCustomers.Columns.Add("Country", typeof(string));
dtCustomers.Rows.Add("DHARM", "ASPSnippets", "Dharmendra Das", "Mumbai", "India");

SqlParameter sqlParameter = new SqlParameter("@Customer", SqlDbType.Structured);
sqlParameter.Value = dtCustomers;
sqlParameter.TypeName = "dbo.CustomerType";

NorthwindEntities entities = new NorthwindEntities();
entities.ExecuteStoreCommand("exec dbo.Customers_InsertCustomer @Customer", sqlParameter);

Screenshot

If you do not want to use Table Valued parameter pass the Serialized Json string as parameter.

Here is an example.

DECLARE @json NVARCHAR(MAX) = N'[{ "CustomerID":"DHARM", "CompanyName":"ASPSnippets", "ContactName":"Dharmendra Das", "City":"Mumbai", "Country":"India"}]';

SELECT CustomerID
	  ,CompanyName
	  ,ContactName
	  ,City
	  ,Country
FROM OPENJSON(@json) 
WITH (CustomerID NCHAR(5),  
     CompanyName NVARCHAR(40),  
     ContactName NVARCHAR(30),  
     City NVARCHAR(15),
     Country NVARCHAR(15))

Output

For more details refer OPENJSON.

I have created an ASP.Net project where i have implemented Bulk Insert operation.

While working with Entity Framework in ASP.Net WebForms, MVC and Windows Forms, you can use SqlParameter class to make Table accepted as table-valued parameter (TVP) by defining the Name of parameter and DB Type of parameter.

And its object is passed as parameter to ExecuteSqlCommand method of Entity Framework called using DBML (Database Markup Language) class.

For more information on DBML, please refer below reply https://www.aspsnippets.com/questions/520746/Question520746/replies/2

Refer below code:

Database

You can download the database table SQL by clicking the download link below. Download SQL file

Then, I have created a User Defined Table Type in SQL Server using the following query.

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

Stored Procedure is created which will accept the DataTable as parameter and then will insert all records into the table.

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

XML File URL

The following XML file I used for populating GridView.

https://raw.githubusercontent.com/aspsnippets/test/master/Customers.xml

HTML Markup

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox ID="CheckBox1" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="Id" HeaderText="Customer Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<br />
<asp:Button ID="btnInsert" runat="server" Text="Bulk Insert" OnClick="OnInsert" />

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("~/Customers.xml"));
        gvCustomers.DataSource = ds.Tables[0];
        gvCustomers.DataBind();
    }
}

protected void OnInsert(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                                            new DataColumn("Name", typeof(string)),
                                            new DataColumn("Country",typeof(string)) });
    foreach (GridViewRow row in gvCustomers.Rows)
    {
        if ((row.FindControl("CheckBox1") as CheckBox).Checked)
        {
            int id = int.Parse(row.Cells[1].Text);
            string name = row.Cells[2].Text;
            string country = row.Cells[3].Text;
            dt.Rows.Add(id, name, country);
        }
    }
    if (dt.Rows.Count > 0)
    {
        //Defining Table Valued Parameter
        SqlParameter sqlParameter = new SqlParameter("@tblCustomers", SqlDbType.Structured)
        {
            TypeName = "dbo.CustomerType",
            Value = dt
        };

        //Creating DBML Class
        SampleEntities entities = new SampleEntities();

        //Executing Stored Procedure
        entities.Database.ExecuteSqlCommand("EXEC Insert_Customers @tblCustomers", sqlParameter);
    }
}

Screenshot