3

I'm trying to run a test case, and this won'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 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'm not getting any errors. Just 0 rows affected.

This works in SQL Server, though:

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

What am I supposed to be doing here?

5
  • Can you profile your server to see what SQL is being executed? Commented Jun 2, 2015 at 15:36
  • 2
    Though I would suggest removing EXEC from the command and setting command.CommandType = CommandType.StoredProcedure. Commented Jun 2, 2015 at 15:39
  • @DavidG the other solution would be to make the command text EXEC Playground.InsertDate @dt. Thanks for the clue! Commented Jun 2, 2015 at 15:40
  • I prefer being explicit about my SqlCommand properties, but that might work too. Let me know... Commented Jun 2, 2015 at 15:44
  • @DavidG, yep. Declaring CommandType.StoredProcedure just means that it will get the definition before executing, and use the parameter names in the definition, meaning it essentially resolves the command text to EXEC Playground.InsertDate @dt. Adding parameters just makes them available to your command text. Commented Jun 2, 2015 at 15:52

1 Answer 1

2

You are not setting your SqlCommand object to be a stored procedure. You should do a couple of things:

  1. Remove the EXEC prefix from the string ~(it's not needed)
  2. Set command to be a stored procedure:

    command.CommandType = CommandType.StoredProcedure;
    
  3. Not sure how the square braces around the DataTable column names will affect this either, but I suspect it's better with them removed.
Sign up to request clarification or add additional context in comments.

3 Comments

Square braces are just there from autogenerated code, it just helps to prevent escaping issues like names starting with numbers, spaces in the name, or a name that is the same as a reserved word like "FROM". The square bracket forces "Everything between the start and the end of these brackets is the object name". It is easier for the auto generated code to have them there all the time instead of detecting when they are needed and only adding them then.
@ScottChamberlain I know that (at least in SQL Server), I'm just not sure they are needed in the context of the DataTable column name.
AHH, you where talking about table.Columns.Add("[time]", typeof(DateTime));, I did not see that. I thought you where talking about the ([time] DATETIME);

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.