如何将集合值传递给sql server 2008存储过程？ [英] How to pass collection values to sql server 2008 Stored Procedures ?

问题描述

我有一个集合，我想将这些集合值传递给存储过程。



但是集合包含多行，我如何将它传递给存储过程。



我的下面的C＃代码用于单行，我想为多行修改它。



以下是我的单行代码 -

  public   bool  PlaSh（Collection< salesp> spCollection）
 
 { int  recordsAffected =  0 ; 
 
  string  connectionString =  数据源= ...... ..; 
 
 使用（SqlConnection conn =  new  SqlConnection（connectionString））
 
 {
 
 conn.Open（）; 
 
 
 SqlParameter [] parms =  new  SqlParameter [ 6 ] ; 
 
 parms [ 0 ] = newSqlParameter（  @ RCI，spCollection.ReportingCustomerId.Trim（））; 
 
 parms [ 1 ] = newSqlParameter（  @ PID，spCollection.PlanXrefId）; 
 
 parms [ 2 ] = newSqlParameter（  @ PSD，spCollection.EffectiveDate）; 
 
 parms [ 3 ] = newSqlParameter（  @ OC，Convert.ToInt32（spCollection .OptionCode））; parms [ 5 ] = newSqlParameter（  @ VA，spCollection.VolumeAmount）; 
 
 recordsAffected = SqlHelper.ExecuteNonQuery（conn，CommandType.StoredProcedure，  usp_ShDetail，parms）; 
 
} 
 
} 
 
 
  return  recordsAffected >   0 ？  true ： false ; 
 
 
} 

这里spCollection包含集合行，我想处理所有行并传递给Stored程序，我是如何实现这一点的!!



谢谢。

解决方案

好吧，你不能直接这样做。集合是框架级实体，而您的存储过程是在t-sql中，并且在它们之间有ado.net驱动程序，因此您的集合不能遍历这些边界。

但是集合就像一个集合表，您可以为存储过程提供表值参数，请参见此处： http://msdn.microsoft .com / zh-CN / library / bb510489.aspx [ ^ ]。您也可以通过c＃代码将表值参数传递给存储过程： http：// msdn。 microsoft.com/en-us/library/bb675163.aspx [ ^ ]。

当然，您需要将您的收藏转换为ado.net对象，即DataTable。



[更新]

另一种方法很难。您可以将集合打包到xml中，并将其传递给存储过程。由于您在sql server端有工具来遍历它，您可以轻松处理它。您可以从以下文章开始： http：// www .aspdotnet-suresh.com / 2012/12 / aspnet-send-xml-file-as-parameter-to.html [ ^ ]， http://www.dotnetpools.com/Article/ ArticleDetiail /？articleId = 220& title =如何传递-XML-as-Parameter-to-SQL-Stored-Procedure-Using-C [ ^ ]。

Hi,

I have a collection and i want to pass that collection values to Stored procedures.

but collection holds multiple rows, how i pass this to stored procedures.

My below C# code is for single row, i want to modfiy it for multiple rows.

Below are my single row code-

public bool PlaSh(Collection<salesp> spCollection)

        {                       int recordsAffected = 0;

                string connectionString = "Data Source = ……..";

                using (SqlConnection conn = new SqlConnection(connectionString))

                {

                    conn.Open();


                    SqlParameter[] parms = new SqlParameter[6];

                    parms[0] = newSqlParameter("@RCI", spCollection.ReportingCustomerId.Trim());

                    parms[1] = newSqlParameter("@PID", spCollection.PlanXrefId);

                    parms[2] = newSqlParameter("@PSD", spCollection.EffectiveDate);

                    parms[3] = newSqlParameter("@OC", Convert.ToInt32(spCollection .OptionCode));                                       parms[5] = newSqlParameter("@VA", spCollection.VolumeAmount);

                    recordsAffected = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "usp_ShDetail", parms);

                }

            }


            return recordsAffected > 0 ? true : false;


        }

Here spCollection holds collection rows , i want to process all rows and pass to Stored procedues, how i achieve this!!

thanks.

解决方案

Well, you can not do this directly. Collections are framework level entities, while your stored procedure is in t-sql and in between there is the ado.net driver, thus your collection can not traverse these boundaries.
But a collection is just like a table, and you can have table valued parameters for a stored procedure, see here: http://msdn.microsoft.com/en-us/library/bb510489.aspx[^]. And you can pass table-valued parameters to the stored procedure from your c# code too: http://msdn.microsoft.com/en-us/library/bb675163.aspx[^].
Of course, you will need to "convert" your collection to an ado.net object, a DataTable.

[Update]
There is an other method tough. You can pack your collection in an xml, and pass it to the stored procedure. As you have tools on sql server side to traverse it, you can easily handle it. You could start from following articles: http://www.aspdotnet-suresh.com/2012/12/aspnet-send-xml-file-as-parameter-to.html[^], http://www.dotnetpools.com/Article/ArticleDetiail/?articleId=220&title=How-To-Pass-XML-as-Parameter-to-SQL-Stored-Procedure-Using-C[^].

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