Pass object parameter to stored procedure by SqlHelper

Question

I have a SQL table like this:

ID is Identity column.

And I have a class for this table in ASP.Net project like this:

public class Topic
    {
        public int ModuleID { get; set; }
        public string Name { get; set; }
        public string BodyHTML { get; set; }
    }

Also I have a stored procedure:

ALTER PROCEDURE [dbo].[addTopic] 
    @Topic dbo.Topic READONLY
AS
BEGIN
    insert into OnlineHelp_Topic
    select * from @Topic
END

I want to pass a Topic object from project and execute this stored procedure by SqlHelper.

I read many post like this and create a User-Defind Table in SQL Server and I test this code:

Topic Topic = new Topic()
            {
                BodyHTML = "test body",
                ModuleID = 1000,
                Name = "test"
            };
string constr = ConfigurationManager.ConnectionStrings["testConnection"].ConnectionString;
int rowsAffected = SqlHelper.ExecuteNonQuery(constr, "addTopic", new SqlParameter("@Topic", Topic));

But this code has error in SqlHelper.ExecuteNonQuery(...) like this;

It would be very helpful if someone could explain solution for this problem.

I'm not familiar with `SqlHelper` but I would think you'd need to pass the UDT data in a `DataTable` or at least some type of collection. Also there should be a way to tell it the query type is a stored procedure. — juharr, Dec 07 '16 at 15:29
take a look to this other question, it might give you some hints: http://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure ... basically, you'll need to create the list object so you can query it from the sp — Christian Melendez, Dec 07 '16 at 15:46

score 1 · Answer 1 · answered Dec 09 '16 at 11:24

Try to pass the topic parameters like follows:

List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@ModuleID ", topic.ModuleID ));
parameters.Add(new SqlParameter("@Name", topic.Name));
parameters.Add(new SqlParameter("@BodyHTML", topic.BodyHTML));
int rowsAffected = SqlHelper.ExecuteNonQuery(constr, "addTopic", parameters.ToArray());

You will also need to modify your stored procedure.

score 0 · Answer 2 · answered Jul 08 '18 at 05:03

0

You need to pass a DataTable as parameter example

SqlHelper.ExecuteNonQuery(constr, "addTopic", new SqlParameter("@Topic", TopicDatatable));

The DataTable must have the same columns: names and datatypes of the User Defined Table Type.

answered Jul 08 '18 at 05:03

Javier

1

Pass object parameter to stored procedure by SqlHelper

2 Answers2