Need example of calling stored procedure sp_SSISCloneConfiguration from a c# program on SQL Server

Question

When I use this code below, I get a -1 returned from line

cmd.ExecuteNonQuery()

It may have something to do with InvalidCastException.

When we run this stored procedure manually in SSMS, it produces a SQL script in its output which we then copy and paste in a new window and run that to get what we want.

Any ideas of why it's not working from C#?

I knew the connection to the server is good.

using (SqlCommand cmd = new SqlCommand("dbo.sp_SSISCloneConfiguration", sqlConnection))
{
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@project", SqlDbType.VarChar).Value = projectName;
    cmd.Parameters.Add("@destinationProject", SqlDbType.VarChar).Value = projectName;

    cmd.ExecuteNonQuery();
}

Answer 1

Because ExecuteNonQuery() returns "The number of rows affected."

If you're expecting data as a result, you probably meant to use ExecuteReader() which returns "A SqlDataReader object", or perhaps ExecuteScalar() which returns "The first column of the first row in the result set, or a null reference if the result set is empty."

For example:

var result = cmd.ExecuteScalar();

Note that the type of result is object. So if "it produces a sql script in its output" then you would probably need to convert it to a string, for example:

var result = cmd.ExecuteScalar()?.ToString();

Note the ? operator being used, because ExecuteScalar() could return null.