How to process an integer list row by row sent to

问题:

I have an ArrayList (in C#) that contains some int numbers (those are IDs in a table), I want to select some data for each number(s) in this ArrayList and return a table variable or a #temporary table :)

I found a solution for passing this ArrayList as an user-defined table type to my stored procedure:

CREATE TYPE [dbo].[integer_list_tbltype] AS TABLE(
    [n] [int] NOT NULL,
    PRIMARY KEY CLUSTERED ([n] ASC)
        WITH (IGNORE_DUP_KEY = OFF)
)
GO

CREATE PROCEDURE [dbo].[Sp_apr_get_apraisors]
    (@listNumbers INTEGER_LIST_TBLTYPE readonly)
AS
   ....

but I didn't find an efficient way to read this array as easily as in C# :(

Is there any way to write a loop for each of these numbers and save data in a temp table and finally return it to C#??

回答1:

SQL is set based, so your best option is to write a single select statement that would join your input table to the tables containing the data you would like to look up. The select statement would be the result set to be sent back to your application. Then if you want to use straight ADO.Net, you can use the SqlDataReader class to read back into C#, or you could use an ORM like Linq2Sql, Entity Framework, or NHibernate. By the way, if you must do a loop in Sql, please avoid cursors. They are slow and unnecessarily complicated both to manage and to develop. Use a while loop instead.

回答2:

I would suggest you change the procedure parameter to varchar(n) and then send in those values as comma-delimited string.

DECLARE @IDs VARCHAR(MAX)
SELECT @IDs = '1,2,3'

DECLARE @ID INT

WHILE LEN(@IDs) > 0
BEGIN

    SELECT @ID = CONVERT(INT, LEFT(@IDs, CHARINDEX(',', @IDs + ',') -1)
    -- Do something with the ID here...

    SELECT @IDs = STUFF(@IDs, 1, CHARINDEX(',', @IDs + ','), '')
END

回答3:

mmmmm :), after 24h (!) search aorund the www , i found my problem Answer, @Toni's answer helped me on this :) Tanx @Toni :*

1) first define stored procedure

CREATE PROCEDURE [spName]( @list_entry  VARCHAR(max)=NULL)
AS
  BEGIN
      SELECT [Column1,column2,...]
      FROM   [TABLE(s)]
      WHERE ( @list_entry IS NULL
                    OR Column1 IN (SELECT value FROM   Fn_split(@list_person, ',')) )     
  END 

2) write a function to split items (comma delimited)

CREATE  FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END
GO

3) pass my array as a comma-delimited string from .NET

//defin sample array list or your data
ArrayList array = new  ArrayList();

//fill array with some data
for (int i = 1000; i<1010;i++)
    array.Add(i);

//define connection and command
using(SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString))
{
    connection.Open();
    SqlCommand cmd = new SqlCommand("",connection);
    cmd.Parameters.AddWithValue("@list_entry", SqlDbType.varchar,8000,Get_comma_delimited_string(array));
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "yourSpName";
    cmd.ExecuteNonQuery();
}

/// <summary>
/// Resturns a comma delimited string (sepearte each item in list with ',' )
/// </summary>            
public string Get_comma_delimited_string(ArrayList arrayList)
{
    string result = string.Empty;
    foreach (object item in arrayList)
        result += item.ToString() + ",";

    return result.Remove(result.Length - 1);
}