将数据表从 C# 传递到 SQL Server 2008 [英] Passing datatable from C# to SQL Server 2008
本文介绍了将数据表从 C# 传递到 SQL Server 2008的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何将数据表从 C# 传递到 SQL Server 2008?
How can I pass a DataTable from C# to SQL Server 2008?
例外:
表类型参数 '@CurrentTableInitial' 必须有一个有效的类型名字.
The table type parameter '@CurrentTableInitial' must have a valid type name.
表结构:
CREATE TABLE [dbo].[RegisterChoiceUserInitial](
[RegisterChoiceUserInitialID] [int] IDENTITY(1,1) NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[RegisterChoiceUserInitialJob] [nvarchar](50) NULL,
[RegisterChoiceUserInitialOrganization] [nvarchar](50) NULL,
[RegisterChoiceUserInitialUnit] [nvarchar](50) NULL,
[RegisterChoiceUserInitialMembershipType] [nvarchar](50) NULL,
[RegisterChoiceUserInitialStart] [nvarchar](10) NULL,
[RegisterChoiceUserInitialEnd] [nvarchar](10) NULL,
CONSTRAINT [PK_RegisterChoiceUserInitial] PRIMARY KEY CLUSTERED
(
[RegisterChoiceUserInitialID] ASC
)
用户自定义类型:
CREATE TYPE [dbo].[TableTypeInitial] AS TABLE(
[ID] [int] NULL,
[InitialJob] [nvarchar](50) NULL,
[InitialOrganization] [nvarchar](50) NULL,
[InitialUnit] [nvarchar](50) NULL,
[InitialMembershipType] [nvarchar](50) NULL,
[InitialMembershipStart] [nvarchar](10) NULL,
[InitialMembershipEnd] [nvarchar](10) NULL
)
存储过程:
create PROCEDURE [dbo].[FinishRegisterChoiceUserInitial]
( @UserId uniqueidentifier,
@TableVariable TableTypeInitial READONLY)
AS
BEGIN
INSERT INTO [Election].[dbo].[RegisterChoiceUserInitial]
([UserId]
,[RegisterChoiceUserInitialJob]
,[RegisterChoiceUserInitialOrganization]
,[RegisterChoiceUserInitialUnit]
,[RegisterChoiceUserInitialMembershipType]
,[RegisterChoiceUserInitialStart]
,[RegisterChoiceUserInitialEnd])
SELECT
@UserId AS UserId
,InitialJob
,InitialOrganization
,InitialUnit
,InitialMembershipType
,InitialMembershipStart
,InitialMembershipEnd
FROM
@TableVariable
END
数据表:
DataTableInitial.Columns.Add(new DataColumn("ID", typeof(int)));
DataTableInitial.Columns.Add(new DataColumn("InitialJob", typeof(String)));
DataTableInitial.Columns.Add(new DataColumn("InitialOrganization", typeof(String)));
DataTableInitial.Columns.Add(new DataColumn("InitialUnit", typeof(String)));
DataTableInitial.Columns.Add(new DataColumn("InitialMembershipType", typeof(String)));
DataTableInitial.Columns.Add(new DataColumn("InitialMembershipStart", typeof(String)));
DataTableInitial.Columns.Add(new DataColumn("InitialMembershipEnd", typeof(String)));
cmd.CommandText = "EXEC FinishRegisterChoiceUserInitial @UserId, @CurrentTableInitial ";
cmd.Parameters.AddWithValue("@UserId",user.ProviderUserKey);
DataTable TableInitial=(DataTable)ViewState["CurrentTableInitial"];
SqlParameter a = cmd.Parameters.AddWithValue("@CurrentTableInitial", TableInitial);
a.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
推荐答案
您缺少 a.TypeName = "dbo.TableTypeInitial";将此语句放在a.SqlDbType = SqlDbType.Structured;"之前
You were missing a.TypeName = "dbo.TableTypeInitial"; Put this statement before "a.SqlDbType = SqlDbType.Structured;"
使用
cmd.CommandText = "EXEC FinishRegisterChoiceUserInitial @UserId, @TableTypeInitial ";
代替
cmd.CommandText = "EXEC FinishRegisterChoiceUserInitial @UserId, @CurrentTableInitial ";
Sql Server 脚本:
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();
}
这篇关于将数据表从 C# 传递到 SQL Server 2008的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文