Unable to pass table type as parameter inside Canvas app

SQL Server Stored Procedures support System.Data.DataTable as a parameter. We can pass the DataTable to the Stored Procedure using ADO.Net in the same way as we provided using the System.Data.SqlParameter class, but needs a few changes in the datatype. Normally we provide DbType of SqlParameter for a normal parameter like varchar, nvarchar, int and so on as in the following code.

SqlParameter sqlParam= new SqlParameter();  
sqlParam.ParameterName = "@StudentName";  
sqlParam.DbType = DbType.String;  
sqlParam.Value = StudentName;

Example

SqlParameter Parameter = new SqlParameter;  
Parameter.ParameterName = "@PhoneBook";  
Parameter.SqlDbType = SqlDbType.Structured;  
Parameter.Value = PhoneTable;

The following example receives a list of phone books and stores them in a database using ADO.Net. The example retrieves the phone book details from the list and stores them into the DataTable and passes this table to the Stored Procedure named NewPhoneBook as a parameter.

//Phone book list    
List<PhoneBook> PhoneBooks    
 //CReating Table    
 DataTable PhoneTable = new DataTable();    

 // Adding Columns    
 DataColumn COLUMN=new DataColumn();    
 COLUMN.ColumnName="ID";    
 COLUMN.DataType= typeof(int);    
 PhoneTable.Columns.Add(COLUMN);    

 COLUMN = new DataColumn();    
 COLUMN.ColumnName = "ContactNumber";    
 COLUMN.DataType = typeof(string);    
 PhoneTable.Columns.Add(COLUMN);    

 COLUMN = new DataColumn();    
 COLUMN.ColumnName = "ContactName";    
 COLUMN.DataType = typeof(string);    
 PhoneTable.Columns.Add(COLUMN);    

 // INSERTING DATA    
 foreach (UserPhoneBook UPB in PhoneBooks)    
 {    
    DataRow DR = PhoneTable.NewRow();    
    DR[0] = UPB.UserName;    
    DR[1] = UPB.ContactNumber;    
    DR[2] = UPB.ContactName;    
    PhoneTable.Rows.Add(DR);    
 }    
 //Parameter declaration    
 SqlParameter[] Parameter = new SqlParameter[2];    
 Parameter[0].ParameterName = "@PhoneBook";    
 Parameter[0].SqlDbType = SqlDbType.Structured;    
 Parameter[0].Value = PhoneTable;    

 Parameter[1].ParameterName = "@Return_Value";    
 Parameter[1].Direction = ParameterDirection.ReturnValue;    
 //Executing Procedure  
 SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.StoredProcedure, "[NewPhoneBook]", Parameter);

Converting a DataTable to JSON with .NET Core 2.x,

using System;
using System.Data;
using Newtonsoft.Json;

class Program
{
  static void Main()
  {
    // Get the DataTable.
    DataTable table = GetTable();
    // ... Use the DataTable here with SQL.
    string json = JsonConvert.SerializeObject(table, Formatting.Indented);
    Console.WriteLine(json);

  }

  static DataTable GetTable()
  {
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable();
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));

    // Here we add five DataRows.
    table.Rows.Add(25, "Indocin", "David", DateTime.Now);
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);

    return table;
  }
}

You'd still have to connect to the db and drop the json into an INSERT statement, but that should be easily done.

cmd.CommandText = "SELECT * FROM myfunc(@json)";
cmd.Parameters.AddWithValue("json", json);

First, create a table that we want to populate. Execute the following script:

CREATE DATABASE ShowRoom


USE ShowRoom
Create Table Cars
(
    Id int primary key,
    Name nvarchar(50),
    company nvarchar(50)
)
Go

As described earlier, the first step is to create a user-defined table type that corresponds to the table that you want to populate. Execute the following script to do so:

CREATE TYPE CarTableType AS TABLE
(
     Id int primary key,
    Name nvarchar(50),
    company nvarchar(50)
)
Go

Now let’s create a stored procedure that accepts the CarTableType variable as a parameter. Inside the stored procedure, we will SELECT all the records from this variable and insert them into the Cars table. Execute the following script to create such stored procedure:

CREATE PROCEDURE spInsertCars
@CarType CarTableType READONLY
AS
BEGIN
    INSERT INTO Cars
    SELECT * FROM @CarType
END

The final step is to create a variable of the CarTableType variable, populate it with dummy data and pass it to the spInsertCars stored procedure. Take a look at the following script:

DECLARE @CarTableType CarTableType

INSERT INTO @CarTableType VALUES (1, 'Corrolla', 'Toyota')
INSERT INTO @CarTableType VALUES (2, 'Civic', 'Honda')
INSERT INTO @CarTableType VALUES (3, '6', 'Audi')
INSERT INTO @CarTableType VALUES (4, 'c100', 'Mercedez')
INSERT INTO @CarTableType VALUES (5, 'Mustang', 'Ford')

EXECUTE spInsertCars @CarTableType

Inside the stored procedure, five records from the @CarTableType variable are selected and inserted into Cars table. Now if you select all the records from the Cars table, you should see the newly inserted records. Execute the following script to do so:

SELECT * FROM Cars