gpt4 book ai didi

c# - 将对象列表传递给 SQL Server 存储过程并使用 asp.net core 插入记录

转载 作者:行者123 更新时间:2023-12-04 01:48:30 25 4
gpt4 key购买 nike

我正在开发 asp.net 应用程序。我正在使用 ado.net 访问 SQL Server 数据库并执行 CRUD。我想一次性插入多条记录。为此,我在数据库中添加了一个自定义类型和一个存储过程,如下所示:

CREATE TYPE [dbo].[UserTableType] AS TABLE
(
[FirstName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[UserName] [nvarchar](100) NULL,
[Password] [nvarchar](100) NULL,
[IsActive] [bit] NULL,
[IsDeleted] [bit] NULL,
[CreatedBy] [int] NULL,
[CreatedAt] [datetime] NULL,
[UpdatedBy] [int] NULL,
[UpdatedAt] [datetime] NULL,
[Email] [nvarchar](100) NULL
)
GO

存储过程是这样的:

CREATE PROCEDURE [dbo].[sp_InsertUser] 
@UserTableType UserTableType READONLY
AS
BEGIN
INSERT INTO tblUser
SELECT * FROM UserTableType
END

在 C# 中,我使用的是这段代码:

public void CreateMultipleUser()
{
var cs = "Server=.;Database=Clicon;Trusted_Connection=True;";

using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("exec sp_InsertUser @UserTableType", con);

var pList = new SqlParameter("@UserTableType", SqlDbType.Structured);
pList.TypeName = "dbo.UserTableType";
pList.Value = GetAddUserList();
cmd.Parameters.Add(pList);

con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}

此函数用于创建 SqlDataRecord 列表并传递给存储过程:

private List<SqlDataRecord> GetAddUserList()
{
List<SqlDataRecord> datatable = new List<SqlDataRecord>();
SqlMetaData[] sqlMetaData = new SqlMetaData[11];
sqlMetaData[0] = new SqlMetaData("FirstName", SqlDbType.VarChar, 100);
sqlMetaData[1] = new SqlMetaData("LastName", SqlDbType.VarChar, 100);
sqlMetaData[2] = new SqlMetaData("UserName", SqlDbType.VarChar, 100);
sqlMetaData[3] = new SqlMetaData("Password", SqlDbType.VarChar, 100);
sqlMetaData[4] = new SqlMetaData("isActive", SqlDbType.Bit);
sqlMetaData[5] = new SqlMetaData("isDeleted", SqlDbType.Bit);
sqlMetaData[6] = new SqlMetaData("CreatedBy", SqlDbType.Int);
sqlMetaData[7] = new SqlMetaData("CreatedAt", SqlDbType.DateTime);
sqlMetaData[8] = new SqlMetaData("UpdatedBy", SqlDbType.Int);
sqlMetaData[9] = new SqlMetaData("UpdatedAt", SqlDbType.DateTime);
sqlMetaData[10] = new SqlMetaData("Email", SqlDbType.VarChar, 100);
SqlDataRecord row = new SqlDataRecord(sqlMetaData);

row = new SqlDataRecord(sqlMetaData);
row.SetValues(new object[] { "Asif", "hameed", "asif371", "123", true,false,1,DateTime.Now,1,DateTime.Now, "asif1@gmail.com" });
datatable.Add(row);
row = new SqlDataRecord(sqlMetaData);
row.SetValues(new object[] { "Asif1", "hameed1", "asif372", "123", true, false, 1, DateTime.Now, 1, DateTime.Now, "asif2@gmail.com" });
datatable.Add(row);
row = new SqlDataRecord(sqlMetaData);
row.SetValues(new object[] { "Asif2", "hameed2", "asif373", "123", true, false, 1, DateTime.Now, 1, DateTime.Now, "asif3@gmail.com" });
datatable.Add(row);
row = new SqlDataRecord(sqlMetaData);
row.SetValues(new object[] { "Asif4", "hameed4", "asif375", "123", true, false, 1, DateTime.Now, 1, DateTime.Now, "asif5@gmail.com" });

datatable.Add(row);
row = new SqlDataRecord(sqlMetaData);
row.SetValues(new object[] { "Asif5", "hameed4", "asif376", "123", true, false, 1, DateTime.Now, 1, DateTime.Now, "asif6@gmail.com" });

datatable.Add(row);

return datatable;
}

但我遇到了这个异常:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.SqlClient.dll but was not handled in user code

Additional information: Invalid object name 'UserTableType'

请提出解决方案。

最佳答案

您是从类型定义中进行选择,而不是从参数中进行选择。试试这个:

CREATE PROCEDURE [dbo].[sp_InsertUser] 
-- Add the parameters for the stored procedure here
@UserTableType UserTableType READONLY

AS
BEGIN
INSERT INTO tblUser
SELECT * FROM @UserTableType
END

关于c# - 将对象列表传递给 SQL Server 存储过程并使用 asp.net core 插入记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42097281/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com