How do I use a MySQL stored procedure from c# which sends a table as input to the store procedure? I have T-SQL working

Question

In Transact-SQL I have the input parameter to the store procedure

@DataTable InputTabel READONLY,

And the "InputTabel" is defined as:

CREATE TYPE InputTabel AS TABLE
    (
        ID INT NULL,
        RESULT_INDEX INT NULL
    )

And on the C# side I use this code to define the parameter:

SqlParameter parameter = new SqlParameter( );
parameter.ParameterName = "@DataTable ";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.TypeName = "InputTabel";
parameter.Value = theInputTable;

Where theInputTable is defined as:

DataTable theInputTable = new DataTable("TheInputTableName");
theInputTable.Columns.Add( "ID", typeof( Int32 ) );
theInputTable.Columns.Add( "RESULT_INDEX", typeof( string ) );

I now have to transfer this approach to MySQL and wounder how do I do that? Both on C# side and on the server side.

score 7 · Accepted Answer · edited May 23 '17 at 12:22

Unfortunately MySql didn't implement table-valued parameters, but alternatives do exist:

Using a temporary table which drops immediately after the connection closes but is visible to all queries coming via this connection. The only con is having to execute an additional query (or more) before the intended query, just to insert data into the temp table.
Using a delimited string ("str1,str2,str3") which significantly downgrades performance, especially when the string is very long.

For more info about both methods: Create table variable in MySQL

How do I use a MySQL stored procedure from c# which sends a table as input to the store procedure? I have T-SQL working

1 Answers1

Linked