blocks|key|1361326|text|如果要设置列的类型，请检查此页：https://msdn.microsoft.com/en-us/library/hfx3s9wd(v=vs.110).aspx|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1361327|您可能想看看SQLBulkCopy+(https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx+)。它接受每种默认数据。|1361328|当您使用可以轻松转换的对象时，有一个扩展可用，请参阅https://www.nuget.org/packages/FastMember/。|1361329|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/hfx3s9wd(v=vs.110).aspx|1|https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110%2529.aspx)|2|https://www.nuget.org/packages/FastMember/^0|G|1S|0|0|J|2H|1|0|Q|16|2|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@$A|Y|B|Z|1|10]]|C|$]]|$1|F|3|G|5|6|7|11|8|@]|9|@$A|12|B|13|1|14]]|C|$]]|$1|H|3|-4|5|6|7|15|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]|R|$5|K|L|M|C|$N|S]]]]

If you want to set the type of a column check this page: <a href="https://msdn.microsoft.com/en-us/library/hfx3s9wd(v=vs.110).aspx" rel="nofollow">https://msdn.microsoft.com/en-us/library/hfx3s9wd(v=vs.110).aspx</a>

You might want to take a look at SQLBulkCopy (<a href="https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx" rel="nofollow">https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx</a>). It accepts per default datatables. 

<hr>

There is a extension available when you work with objects that it can be easily converted, for that see <a href="https://www.nuget.org/packages/FastMember/" rel="nofollow">https://www.nuget.org/packages/FastMember/</a>.

blocks|key|1363371|text|如果需要将多个字符串值放在一起，则可以使用逗号分隔的字符串，然后在存储过程中，可以将这个逗号分隔的字符串拆分为表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1363372|例如，将aaa,bbb,ccc传递给存储过程，然后将其拆分到表中。|offset|length|style|CODE|1363373|entityMap^0|0|4|B|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

if you need multiple string values together, you can, for example, use a comma separated string, and then in your stored procedure, you can split this comma separated string to a table

for example, you pass <code>aaa,bbb,ccc</code> to you stored procedure, and then split it to a table

blocks|key|1361371|text|在Server中创建用户定义的表类型：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1361372|CREATE+TYPE+LocationTableType+AS+TABLE+
(+LocationName+VARCHAR(50));
GO|code-block|syntax|javascript|1361373|并将此表值参数传递给存储过程：|1361374|CREATE+PROCEDURE+[Sade].[SPLocationInsert]
+@...+INT
,@...+VARCHAR(64)
,@...+VARCHAR(MAX)
,@...+INT
,@...+INT
,@...+INT
,@...+INT
,@...+INT
,@PLocationDataList+LocationTableType+READONLY
AS+
++++SET+NOCOUNT+ON
++++--+insert+your+code+here
GO|1361375|参考文献：|1361376|用户定义的表类型：https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx|offset|length|1361377|表值参数：https://technet.microsoft.com/en-us/library/bb510489(v=sql.105).aspx|1361378|entityMap|0|LINK|mutability|MUTABLE|url|https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx|1|https://technet.microsoft.com/en-us/library/bb510489(v=sql.105).aspx^0|0|0|0|0|0|9|1W|0|0|5|1W|1|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|17|8|@]|9|@$O|18|P|19|1|1A]]|A|$]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@$O|1C|P|1D|1|1E]]|A|$]]|$1|S|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]|10|$5|V|W|X|A|$Y|11]]]]

Create a user defined table type in SQL Server:

<pre><code>CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50));
GO
</code></pre>

And pass this table valued parameter to the stored procedure :

<pre><code>CREATE PROCEDURE [Sade].[SPLocationInsert]
 @... INT
,@... VARCHAR(64)
,@... VARCHAR(MAX)
,@... INT
,@... INT
,@... INT
,@... INT
,@... INT
,@PLocationDataList LocationTableType READONLY
AS 
 SET NOCOUNT ON
 -- insert your code here
GO
</code></pre>

References:

User defined table types : <a href="https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx" rel="nofollow">https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx</a>

Table valued parameters: <a href="https://technet.microsoft.com/en-us/library/bb510489(v=sql.105).aspx" rel="nofollow">https://technet.microsoft.com/en-us/library/bb510489(v=sql.105).aspx</a>

blocks|key|1917231|text|1.首先将datatable转换为XMl，如下所示|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1917232|DataSet+ds+=+new+DataSet();
ds.Tables.Add(dt1);+//+dataTable+
string+dsXml=+ds.GetXml();|code-block|syntax|javascript|1917233|2.然后将这个XML字符串作为一个参数传递给存储过程。|1917234|3.在SQL中，MYSQL+XML数据类型是要定义的。通过使用它，您可以执行存储过程。|1917235|有关参考资料，请参阅此链接|offset|length|1917236|entityMap|0|LINK|mutability|MUTABLE|url|http://www.itworld.com/article/2960645/development/tsql-how-to-use-xml-parameters-in-stored-procedures.html^0|0|0|0|0|B|2|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

1.First convert datatable into XMl like below

<pre><code>DataSet ds = new DataSet();
ds.Tables.Add(dt1); // dataTable 
string dsXml= ds.GetXml();
</code></pre>

2.Then pass this XML string as a one parameter to Stored procedure.

3.In SQL , MYSQL XML datatype is ther to define . By using that you can execute stored procedure. 

For reference see this <a href="http://www.itworld.com/article/2960645/development/tsql-how-to-use-xml-parameters-in-stored-procedures.html" rel="nofollow">link</a>

I have a <code>DataTable</code> in C#, and I'm trying to pass it to SQL Server.

What I have is:

<pre><code>DataTable asd = new DataTable();

for (int i = 0; i &lt; LocationDataList.Count; i++)
{
 asd.Columns.Add(LocationDataList[i], typeof(string));
}

command.Parameters.Add("@PLocationDataList", SqlDbType.NVarChar, -1).Value = asd;

command.ExecuteNonQuery();
</code></pre>

Here is the procedure T-SQL;

<pre><code>ALTER PROCEDURE [Sade].[SPLocationInsert]
 @... INT
 ,@... VARCHAR(64)
 ,@... VARCHAR(MAX)
 ,@... INT
 ,@... INT
 ,@... INT
 ,@... INT
 ,@... INT
 ,@PLocationDataList NVARCHAR(MAX)
AS
</code></pre>

I have no idea how to pass the <code>DataTable</code> and which data type I should use.

Thanks in advance

C# DataTable into SQL Server stored procedure

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在C#中有一个C#，我正在尝试将它传递给Server。我拥有的是：DataTable asd = new DataTable();for (int i = 0; i < LocationDataList.Count; i++){ asd.Columns.Add(LocationDataList[i], type...

问将C# DataTable导入Server存储过程
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将C# DataTable导入Server存储过程EN