如何将datatable参数传递给存储过程sql server？ [英] how to pass datatable parameter to stored procedure sql server?

问题描述

我需要将数据表传递到以下存储过程

i need to pass data table to the following stored procedure

 create procedure insert_data
(@a int ,
@b DataTable)
as 
Begin 
......
end

我使用C＃

推荐答案

你需要做几件事得到这一点，因为你想传递一个表作为一个参数，你需要创建一个（1）表类型和（2）使你的商店程序接受该类型的参数。

You will need to do a couple of things to get this going, since you want to pass a table as a parameter you need to create a (1) Table Type and (2) make your store procedure accept a parameter of that type.

以下是创建TABLE TYPE所需的步骤。

Following are the steps required to create a TABLE TYPE .

表类型

CREATE TYPE dbo.DataTable AS TABLE 
 (
    -- define table structure here
  )
 GO

过程

现在让你程序接受该表类型的参数。

Now make you procedure accept a parameter of that table type.

create procedure insert_data
@a int ,
@b dbo.DataTable READONLY    --<-- Note this is read only param
as 
Begin 
......
end

这个传递的param将是只读参数，所以如果你需要操作在这个参数中传递的值，你将需要在你的程序中的表变量或临时表中获取这些值之前您可以对其进行任何更新或插入操作。

This passed param will be read-only param, so if you need to manipulate the values passed in this param you will need to get these values in a table variable or temp table inside you procedure before you can do any update or insert operations on them.

使用C＃

您可以使用DataTable类来创建一个与sql server中的表类型匹配的新实例。这样的东西..

You can make use of DataTable class to create a new instance of a that type which matches the you table type in sql server. something like this..

DataTable dt = new DataTable("DataTable"); 

// Add columns to this object same as the type in sql server

dt.Columns.Add("Column1", typeof(string)); 
dt.Columns.Add("Column2", typeof(Int32)); 

//Populate the dt object 

dt.Rows.Add("Value1", 1); 
dt.Rows.Add("Value2", 2); 
dt.Rows.Add("Value2", 3); 

这篇关于如何将datatable参数传递给存储过程sql server？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！