C # parameter passing table to SqlCommand value not working

I'm trying to run a test case and it doesn't even work ... What am I doing wrong here?

Here's the SQL:

CREATE TABLE 
    Playground.Test (saved DateTime)
GO
CREATE TYPE
    Playground.DateTimeTable AS TABLE
    ([time] DATETIME);
GO
CREATE PROCEDURE
    Playground.InsertDate
    @dt Playground.DateTimeTable READONLY
AS
    BEGIN
        INSERT INTO Playground.Test (saved) 
        SELECT [time] 
        FROM @dt
    END
GO

      

And the code to connect and execute the procedure:

const String connString = 
    "server = SERVER; database = DB; UID = myUserID; pwd = myPassword;";
static void Main(string[] args)
{
    SqlCommand command =
        new SqlCommand(
            "EXEC Playground.InsertDate",
            new SqlConnection(connString));

    DataTable table = new DataTable("DateTimeTable");
    table.Columns.Add("[time]", typeof(DateTime));
    table.Rows.Add(DateTime.Parse("10/27/2004"));

    SqlParameter tvp = command.Parameters.AddWithValue("@dt", table);
    tvp.SqlDbType = SqlDbType.Structured;
    tvp.TypeName = "Playground.DateTimeTable";

    command.Connection.Open();
    int affected = command.ExecuteNonQuery();
    command.Connection.Close();

    Console.WriteLine(affected);
    Console.ReadKey();
}

      

I have no errors. Only 0 lines are affected.

This works in SQL Server:

DECLARE @dt Playground.DateTimeTable
INSERT INTO @dt VALUES ('2004-10-27')
EXEC Playground.InsertDate @dt

      

What am I supposed to do here?

+3


source to share


1 answer


You are not setting the object SqlCommand

as a stored procedure. You have to do a couple of things:



  • Remove the prefix EXEC

    from the string ~ (not needed)
  • Install command

    as a stored procedure:

    command.CommandType = CommandType.StoredProcedure;
    
          

  • Not sure how the square brackets around the column names DataTable

    will also affect this, but I suspect it's better to remove them.
+1


source







All Articles