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 to share