2

I am trying to pass a datatable as a parameter into a sql stored procedure.

I have a helper class that executes the code for me:

Helper class method I am using:

public int ExecNonQueryProc(string proc, params object[] args)
{
    using (SqlCommand cmd = CreateCommand(proc, CommandType.StoredProcedure, args))
    {
        return cmd.ExecuteNonQuery();
    }
}

public SqlCommand CreateCommand(string qry, CommandType type, params object[] args)
{
    SqlCommand cmd = new SqlCommand(qry, _conn);

    // Associate with current transaction, if any
    if (_trans != null)
        cmd.Transaction = _trans;

    // Set command type
    cmd.CommandType = type;

    // Construct SQL parameters
    for (int i = 0; i < args.Length; i++)
    {
        if (args[i] is string && i < (args.Length - 1))
        {
            SqlParameter parm = new SqlParameter();
            parm.ParameterName = (string)args[i];
            parm.Value = args[++i];
            cmd.Parameters.Add(parm);
        }
        else if (args[i] is SqlParameter)
        {
            cmd.Parameters.Add((SqlParameter)args[i]);
        }
        else throw new ArgumentException("Invalid number or type of arguments supplied");
    }
    return cmd;
}

My SQL Stored Procedure:

ALTER PROCEDURE [dbo].[sp_insert_input_portfolio_metrics]
@TYPE_INPUT_PORTFOLIO_METRICS TYPE_INPUT_PORTFOLIO_METRICS readonly
-- Add the parameters for the stored procedure here 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

delete from dbo.INPUT_PORTFOLIO_METRICS
where ID in (select ID from dbo.INPUT_PORTFOLIO_METRICS a 
inner join @TYPE_INPUT_PORTFOLIO_METRICS b
on a.Portfolio = b.portfolio and a.Portfolio_Val_date = b.portfolio_val_date)  
END

How I am executing:

private void simpleButton_UploadAll_Click(object sender, EventArgs e)
{

    AdoHelper adocommand = new AdoHelper();
    var dt = new DataTable();
    dt = wizard_tables.PerformanceDetail.Copy();


    adocommand.ExecNonQueryProc("sp_insert_input_portfolio_metrics",
                                "@TYPE_INPUT_PORTFOLIO_METRICS", dt);

}

The error that I receive:

No mapping exists from object type Risk_Performance_Platform.Datasets.DS_Wizard_Tables+PerformanceDetailDataTable to a known managed provider native type.

I was trying to follow the example by Navid Farhadi on How to insert a data table into SQL Server database table?

Community
  • 1
  • 1
DBansal
  • 21
  • 1
  • 2
  • BTW, I got the helper class from here: http://www.blackbeltcoder.com/Articles/ado/an-ado-net-sql-helper-class – DBansal Mar 07 '13 at 18:55
  • Nevermind. Got it to to work. adocommand.ExecNonQueryProc("sp_insert_input_portfolio_metrics", "@TYPE_INPUT_PORTFOLIO_METRICS", dt.DefaultView.ToTable()); Needed to add dt.DefaultView.ToTable() – DBansal Mar 07 '13 at 19:08
  • Add that as an answer - you can answer your own question - and then in two days you can accept it as the accepted answer. – Mike Perrenoud Mar 08 '13 at 17:42

0 Answers0