从C＃传递数据表到SQL Server 2008 [英] Passing datatable from C# to SQL Server 2008
本文介绍了从C＃传递数据表到SQL Server 2008的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我如何能够通过从C＃的DataTable到SQL Server 2008
例外：
表类型参数@CurrentTableInitial必须有一个有效的类型
名称。
表结构为：
CREATE TABLE [DBO] [RegisterChoiceUserInitial（$ b。 $ b [RegisterChoiceUserInitialID] [INT] IDENTITY（1,1）NOT NULL，
[用户ID] [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，
约束[PK_RegisterChoiceUserInitial] PRIMARY KEY CLUSTERED
（
[RegisterChoiceUserInitialID] ASC
）
用户定义类型：
CREATE TYPE [DBO]。[TableTypeInitial]如表（
[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唯一标识符，
@TableVariable TableTypeInitial只读）
AS
BEGIN
INSERT INTO [选举] [DBO]。 [RegisterChoiceUserInitial]
（[用户ID]
，[RegisterChoiceUserInitialJob]
，[RegisterChoiceUserInitialOrganization]
，[RegisterChoiceUserInitialUnit]
，[RegisterChoiceUserInitialMembershipType]
，[RegisterChoiceUserInitialStart ]
，[RegisterChoiceUserInitialEnd]）
选择
@UserId AS用户ID
，InitialJob
，InitialOrganization
，InitialUnit
，InitialMembershipType
，InitialMembershipStart
，InitialMembershipEnd
起价
@TableVariable
端
数据表：
DataTableInitial.Columns.Add（新的DataColumn（ID，的typeof（INT）））;
DataTableInitial.Columns.Add（新的DataColumn（InitialJob，typeof运算（字符串）））;
DataTableInitial.Columns.Add（新的DataColumn（InitialOrganization，typeof运算（字符串）））;
DataTableInitial.Columns.Add（新的DataColumn（InitialUnit，typeof运算（字符串）））;
DataTableInitial.Columns.Add（新的DataColumn（InitialMembershipType，typeof运算（字符串）））;
DataTableInitial.Columns.Add（新的DataColumn（InitialMembershipStart，typeof运算（字符串）））;
DataTableInitial.Columns.Add（新的DataColumn（InitialMembershipEnd，typeof运算（字符串）））;
cmd.CommandText =EXEC FinishRegisterChoiceUserInitial @UserId，@CurrentTableInitial
cmd.Parameters.AddWithValue（@用户ID，user.ProviderUserKey）;
的DataTable TableInitial =（数据表）的ViewState [CurrentTableInitial];
的SqlParameter一个= cmd.Parameters.AddWithValue（@ CurrentTableInitial，TableInitial）;
a.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery（）;
解决方案您失踪a.TypeName =dbo.TableTypeInitial
将这个语句之前a.SqlDbType = SqlDbType.Structured;
使用
cmd.CommandText =EXEC FinishRegisterChoiceUserInitial @UserId，@TableTypeInitial
而不是
cmd.CommandText =EXEC FinishRegisterChoiceUserInitial @UserId，@CurrentTableInitial
SQL Server脚本：
CREATE TABLE [目标]
（
[ID] [INT] NOT NULL PRIMARY KEY的身份，
[名字] [VARCHAR] （100）NOT NULL，
[姓氏] [VARCHAR（100）NOT NULL，
[邮件] [VARCHAR（200）NOT NULL
）
CREATE TYPE [TargetUDT ]如表
（
[名字] [VARCHAR（100）NOT NULL，
[姓氏] [VARCHAR（100）NOT NULL，
[邮件] [VARCHAR] （200）NOT NULL
）
CREATE PROCEDURE AddToTarget（@TargetUDT TargetUDT只读）
AS
BEGIN
INSERT INTO [目标]
SELECT * FROM @ TargetUDT
端
示例代码：
公共静态无效StartProcess（）
{
//创建一个本地数据表来保存客户记录
数据表dtCustomers =新的DataTable（客户）;
的DataColumn dcFirstName =新的DataColumn（名字，typeof运算（字符串））;
的DataColumn dcLastName =新的DataColumn（姓氏，typeof运算（字符串））;
的DataColumn dcEmail =新的DataColumn（电子邮件，typeof运算（字符串））;
dtCustomers.Columns.Add（dcFirstName）;
dtCustomers.Columns.Add（dcLastName）;
dtCustomers.Columns.Add（dcEmail）;
//添加客户1
的DataRow drCustomer = dtCustomers.NewRow（）;
drCustomer [名字] =AAA;
drCustomer [姓氏] =XYZ;
drCustomer [电子邮件] =aaa@test.com;
dtCustomers.Rows.Add（drCustomer）;
//添加客户2
drCustomer = dtCustomers.NewRow（）;
drCustomer [名字] =BBB;
drCustomer [姓氏] =XYZ;
drCustomer [电子邮件] =bbb@test.com;
dtCustomers.Rows.Add（drCustomer）;
//添加客户3
drCustomer = dtCustomers.NewRow（）;
drCustomer [名字] =CCC;
drCustomer [姓氏] =XYZ;
drCustomer [电子邮件] =ccc@test.com;
dtCustomers.Rows.Add（drCustomer）;
//创建连接对象连接到服务器/数据库
SqlConnection的康恩=新的SqlConnection（构造）;
conn.Open（）;
//创建一个调用存储过程
的SqlCommand cmdCustomer =新的SqlCommand（AddToTarget，conn）的命令对象;
cmdCustomer.CommandType = CommandType.StoredProcedure;
//使用新的SQL数据库类型即一个参数。结构传递的表值参数
的SqlParameter paramCustomer = cmdCustomer.Parameters.Add（@ TargetUDT，SqlDbType.Structured）;
paramCustomer.Value = dtCustomers;
//执行查询
cmdCustomer.ExecuteNonQuery（）;
}
How can I pass a DataTable from C# to SQL Server 2008?
Exception:
The table type parameter '@CurrentTableInitial' must have a valid type name.
Table structure:
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 )User-defined type:
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 )Stored procedure:
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 ENDDataTable:
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();
解决方案You were missing a.TypeName = "dbo.TableTypeInitial"; Put this statement before "a.SqlDbType = SqlDbType.Structured;"
Use
cmd.CommandText = "EXEC FinishRegisterChoiceUserInitial @UserId, @TableTypeInitial ";instead of
cmd.CommandText = "EXEC FinishRegisterChoiceUserInitial @UserId, @CurrentTableInitial ";Sql Server Scripts :
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 ENDSample Code :
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屋！
查看全文
