Передача параметра SQL в предложение IN() с использованием типизированных наборов данных в .NET.

Ответ 1

Вы не можете использовать один параметр для списка значений таким образом. Но для достижения желаемого вам может быть характерен специфический для базы данных способ. Например, с SQL Server 2005 или более поздней версией вы можете создать табличную функцию для разделения вашего параметра строки, например:

CREATE FUNCTION dbo.F_Split
(
@InputString VARCHAR(MAX)
,@Separator VARCHAR(MAX)
)
RETURNS @ValueTable TABLE (Value VARCHAR(MAX))
AS
BEGIN

    DECLARE @SeparatorIndex INT, @TotalLength INT, @StartIndex INT, @Value VARCHAR(MAX)
    SET @TotalLength=LEN(@InputString)
    SET @StartIndex = 1

    IF @Separator IS NULL RETURN

    WHILE @StartIndex <= @TotalLength
    BEGIN
        SET @SeparatorIndex = CHARINDEX(@Separator, @InputString, @StartIndex)
        IF @SeparatorIndex > 0
        BEGIN
            SET @Value = SUBSTRING(@InputString, @StartIndex, @[email protected])
            SET @StartIndex = @SeparatorIndex + 1
        END
        ELSE
        BEGIN
            Set @Value = SUBSTRING(@InputString, @StartIndex, @[email protected]+1)
            SET @StartIndex = @TotalLength+1
        END
        INSERT INTO @ValueTable
        (Value)
        VALUES
        (@Value)
    END

    RETURN
END

Затем вы будете использовать его следующим образом:

SELECT * from Table WHERE ID IN (SELECT CAST(Value AS INT) FROM F_Split(@IDs, ','))

Ответ 2

Я пробовал обходной путь для использования концепции "содержит" в SQL-методе:

В вашем случае измените SQL -

Оригинал:

SELECT * из таблицы WHERE ID IN (@IDs)

Стать:

SELECT * из таблицы WHERE CharIndex (',' + Cast (ID как Varchar (10)) + ',', @IDs) > 0

С кодом .net -

Оригинал:

TableAdapter.Fill(MyDataTable, "1,2,3" )

Стать:

TableAdapter.Fill(MyDataTable, ", 1,2,3," )

Ответ 3

SQL Server 2008 имеет функцию Табличные параметры

Итак, вам нужно

• укажите ваш запрос как SELECT * from Table WHERE ID IN (SELECT * FROM (@IDs))
• вернитесь в визуальный конструктор TableAdapter в Visual Studio и обновите параметр @IDS, чтобы изменить параметр @IDS как DbType = Object и ProviderType = Structured
• запустите эту партию SQL в базе данных, которую вы используете: CREATE TYPE MyIntArray AS TABLE ( Value INT );GO. Это создаст тип таблицы MyIntArray с одним столбцом типа INT.
• Теперь сложнее передать тип MyIntArray в TableAdapter со стороны ADO.NET.

К сожалению, конструктор Table Adapter не поддерживает аргумент SqlParameter.TypeName, поэтому нам нужно исправить его самостоятельно. Цель состоит в том, чтобы изменить свойство CommandCollection сгенерированного класса TableAdapter. К сожалению, это свойство защищено, поэтому вы должны получить TableAdapter или, например, использовать Reflection для его настройки. Вот пример с производным классом:

    public class MyTableAdapter2 : MyTableAdapter
    {
        public MyTableAdapter2()
        {
            SqlCommand[] cmds = base.CommandCollection;
            // here, the IDS parameter is index 0 of command 1
            // you'll have to be more clever, but you get the idea
            cmds[1].Parameters[0].TypeName = "MyIntArray";
        }
    }

И так вы можете вызвать этот метод:

        MyTableAdapter t = new MyTableAdapter2();

        // create the TVP parameter, with one column. the name is irrelevant.
        DataTable tvp = new DataTable();
        tvp.Columns.Add();

        // add one row for each value
        DataRow row = tvp.NewRow();
        row[0] = 1;
        tvp.Rows.Add(row);

        row = tvp.NewRow();
        row[0] = 2;
        tvp.Rows.Add(row);

        row = tvp.NewRow();
        row[0] = 3;
        tvp.Rows.Add(row);

        t.Fill(new MyDataTable(), tvp);

Ответ 4

Единственная база данных, которую я знаю об этом, может использовать параметры .NET в предложении IN, это PostgreSQL, потому что PostgreSQL имеет концепцию массивов, которые могут использоваться с IN, а Npgsql разрешает массив (или IEnumerable<T>) параметры.

В других базах данных вам нужно либо построить SQL, либо передать строку в процедуру базы данных, которая преобразует ее в параметры 0 или более, а затем действует на них.

Ответ 5

@Joe прав.

Или вы можете использовать цикл foreach для этого.

Что-то вроде:

   int[] arr = new int[3]; 
    arr[0] = "1";        
    arr[1] = "2";             
    arr[2] = "3";             

foreach(vat data in arr)
{

//Do your Code here

// 
var MyDatatable = obj.GetDatabyID(data);

TableAdapter.Fill(MyDataTable);

}

Привет

Ответ 6

Вы также можете создать список параметров идентификаторов поэтому вместо использования @IDs вы будете использовать @ID1, @ID2, @ID3 и т.д.

var sql = "SELECT * from Table WHERE ID IN (" + getKeys(values.Count) + ")";

И getKeys (count) делает что-то вроде этого:

var result = string.Empty;
            for (int i = 0; i < count; i++)
            {
                result += ", @ID" + i;
            }
            return string.IsNullOrEmpty(result) ? string.Empty : result.Substring(1);

и, наконец, добавьте параметры:

foreach (int i = 0; i < values.Count; i++)
            {
                cmd.Parameters.Add(new SqlParameter("@ID" + i, SqlDbType.VarChar) { Value = values[i]});
            }

Ответ 7

Вы также можете использовать XML для перехода в список параметров в хранимую процедуру:

1) В Visual Studio:

Создайте новую таблицу и создайте типизированный набор данных, чтобы получить одну запись:

SELECT * FROM myTable WHERE (ID = @ID)

2) В диспетчере SQL Server:

Создайте хранимую процедуру с теми же полями ввода, что и ваш типизированный набор данных:

CREATE PROCEDURE [dbo].[usrsp_GetIds]
    @paramList xml = NULL
AS
    SET NOCOUNT ON;

/*
Create a temp table to hold paramaters list.
Parse XML string and insert each value into table.
Param list contains: List of ID's
*/
DECLARE @tblParams AS TABLE (ID INT)
INSERT INTO @tblParams(ID) 
    SELECT 
        XmlValues.ID.value('.', 'INT')
    FROM 
        @paramList.nodes('/params/value') AS XmlValues(ID)

/* 
Select records that match ID in param list:
*/
SELECT * FROM myTable 
WHERE 
    ID IN (
        SELECT ID FROM @tblParams
    )

3) В Visual Studio:

Добавьте новый запрос в свою таблицу, выберите хранимую процедуру, созданную выше usrsp_GetIds, и назовите ее FillBy_Ids. Это создает команду:

TableAdapter.FillBy_Ids(@paramList)

4) В Visual Studio:

В вашем .net-коде создайте функцию утилиты для преобразования массива строк в XML:

    ''' <summary>
    ''' Converts an array of strings to XML values.
    ''' </summary>
    ''' <remarks>Used to pass parameter values to the data store.</remarks>
    Public Shared Function ConvertToXML(xmlRootName As String, values() As String) As String
        'Note: XML values must be HTML encoded.
        Dim sb As New StringBuilder
        sb.AppendFormat("<{0}>", HttpUtility.HtmlEncode(xmlRootName))
        For Each value As String In values
            sb.AppendLine()
            sb.Append(vbTab)
            sb.AppendFormat("<value>{0}</value>", HttpUtility.HtmlEncode(value))
        Next
        sb.AppendLine()
        sb.AppendFormat("</{0}>", xmlRootName)
        Return sb.ToString
    End Function

Пример использования:

Заполните таблицу данных с помощью строго типизированных функций, передав список строк в качестве параметра:

'Create a list of record IDs to retrieve:
Dim ids as New List(of String)
ids.Add(1)
ids.Add(2)
ids.Add(3)

'Convert the list of IDs to an XML string:
Dim paramsXml As String = ConvertToXML("params", ids.ToArray)

'Get the records using standard DataTable & TableAdapter methods:
Using myDT As New MyDataTable
    Using myTA As New MyTableAdapter

        myTA.FillBy_Ids(myDT, paramsXml)

        For Each row In myDT
            'do stuff:
        Next

    End Using
End Using

Ответ 8

Я смог решить эту проблему, установив для свойства ClearBeforeFill значение false и заполнив TableAdapter в цикле foreach.

List<string> aList = new List<string>();
aList.Add("1");
aList.Add("2");
aList.Add("3");

yourTableAdapter.ClearBeforeFill = true;
yourTableAdapter.Fill(yourDataSet.yourTableName, ""); //clears table

foreach (string a in aList)
{
    yourTableAdapter.ClearBeforeFill = false;
    yourTableAdapter.Fill(yourDataSet.yourTableName, a);
}
yourTableAdapter.Dispose();