您缺少a.TypeName =“ dbo.TableTypeInitial”; 将此语句放在“ a.sqlDbType = sqlDbType.Structured;”之前
使用
cmd.CommandText = "EXEC FinishRegisterChoiceUserInitial @UserId, @TableTypeInitial ";
代替
cmd.CommandText = "EXEC FinishRegisterChoiceUserInitial @UserId, @CurrentTableInitial ";
CREATE TABLE [Target]
(
[ID] [int] NOT NULL PRIMARY KEY IDENTITY,
[FirstName] [varchar](100)NOT NULL,
[LastName] [varchar](100)NOT NULL,
[Email] [varchar](200) NOT NULL
)
CREATE TYPE [TargetUDT] AS TABLE
(
[FirstName] [varchar](100)NOT NULL,
[LastName] [varchar](100)NOT NULL,
[Email] [varchar](200) NOT NULL
)
CREATE PROCEDURE AddToTarget(@TargetUDT TargetUDT READONLY)
AS
BEGIN
INSERT INTO [Target]
SELECT * FROM @TargetUDT
END
public static void StartProcess()
{
//Create a local data table to hold customer records
DataTable dtCustomers = new DataTable("Customers");
DataColumn dcFirstName = new DataColumn("FirstName", typeof(string));
DataColumn dcLastName = new DataColumn("LastName", typeof(string));
DataColumn dcEmail = new DataColumn("Email", typeof(string));
dtCustomers.Columns.Add(dcFirstName);
dtCustomers.Columns.Add(dcLastName);
dtCustomers.Columns.Add(dcEmail);
//Add customer 1
DaTarow drCustomer = dtCustomers.NewRow();
drCustomer["FirstName"] = "AAA";
drCustomer["LastName"] = "XYZ";
drCustomer["Email"] = "aaa@test.com";
dtCustomers.Rows.Add(drCustomer);
//Add customer 2
drCustomer = dtCustomers.NewRow();
drCustomer["FirstName"] = "BBB";
drCustomer["LastName"] = "XYZ";
drCustomer["Email"] = "bbb@test.com";
dtCustomers.Rows.Add(drCustomer);
//Add customer 3
drCustomer = dtCustomers.NewRow();
drCustomer["FirstName"] = "CCC";
drCustomer["LastName"] = "XYZ";
drCustomer["Email"] = "ccc@test.com";
dtCustomers.Rows.Add(drCustomer);
//Create Connection object to connect to server/database
sqlConnection conn = new sqlConnection(ConStr);
conn.open();
//Create a command object that calls the stored procedure
sqlCommand cmdCustomer = new sqlCommand("AddToTarget", conn);
cmdCustomer.CommandType = CommandType.StoredProcedure;
//Create a parameter using the new sql DB type viz. Structured to pass as table value parameter
sqlParameter paramCustomer = cmdCustomer.Parameters.Add("@TargetUDT", sqlDbType.Structured);
paramCustomer.Value = dtCustomers;
//Execute the query
cmdCustomer.ExecuteNonQuery();
}
602392714
清零编程群