Подтвердить что ты не робот

Как передать массив в хранимую процедуру SQL Server

Как передать массив в хранимую процедуру SQL Server?

Например, у меня есть список сотрудников. Я хочу использовать этот список в виде таблицы и присоединить его к другой таблице. Но список сотрудников должен быть передан как параметр из С#.

4b9b3361

ОТВЕТЫ

Ответ 1

Поскольку у вас уже есть список, я думаю, что есть более простые способы, чем XML.

SQL Server 2008 (или новее)

Сначала в вашей базе данных создайте следующие два объекта:

CREATE TYPE dbo.EmployeeList
AS TABLE
(
  EmployeeID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.EmployeeList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID FROM @List; 
END
GO

Теперь в коде С#:

DataTable tvp = new DataTable();
// define / populate DataTable from your List here

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    // execute query, consume results, etc. here
}

SQL Server 2005

Если вы используете SQL Server 2005, я бы по-прежнему рекомендовал функцию разделения по XML. Сначала создайте функцию:

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );
GO

Теперь ваша хранимая процедура может быть только:

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 
END
GO

И в коде С# вам просто нужно передать список как '1,2,3,12'...

Я рекомендую вам сравнить ремонтопригодность и производительность этих опций с выбранным методом.

Ответ 2

Основываясь на моем опыте, создавая разграниченное выражение из идентификаторов employeeID, для этой проблемы есть сложное и приятное решение. Вы должны создать только строковое выражение типа ';123;434;365;', в котором 123, 434 и 365 - некоторые идентификаторы employeeID. Вызвав приведенную ниже процедуру и передав ей это выражение, вы можете получить нужные записи. Вы легко можете присоединиться к "другой таблице" в этот запрос. Это решение подходит для всех версий SQL-сервера. Кроме того, по сравнению с использованием табличной переменной или таблицы temp это очень быстрое и оптимизированное решение.

CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees  @List AS varchar(max)
AS
BEGIN
  SELECT EmployeeID 
  FROM EmployeesTable
  -- inner join AnotherTable on ...
  where @List like '%;'+cast(employeeID as varchar(20))+';%'
END
GO

Ответ 3

Используйте хранимый в таблице параметр для хранимой процедуры.

Когда вы передадите его с С#, вы добавите параметр с типом данных SqlDb.Structured.

Смотрите здесь: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Пример:

// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();
}

Ответ 4

Вам нужно передать его как параметр XML.

Изменить: быстрый код из моего проекта, чтобы дать вам представление:

CREATE PROCEDURE [dbo].[GetArrivalsReport]
    @DateTimeFrom AS DATETIME,
    @DateTimeTo AS DATETIME,
    @HostIds AS XML(xsdArrayOfULong)
AS
BEGIN
    DECLARE @hosts TABLE (HostId BIGINT)

    INSERT INTO @hosts
        SELECT arrayOfUlong.HostId.value('.','bigint') data
        FROM @HostIds.nodes('/arrayOfUlong/u') as arrayOfUlong(HostId)

Затем вы можете использовать таблицу temp для соединения с вашими таблицами. Мы определили arrayOfUlong как построенную в XML-схему для обеспечения целостности данных, но вам не нужно это делать. Я бы рекомендовал использовать его, чтобы здесь был быстрый код, чтобы убедиться, что вы всегда получаете XML с longs.

IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'xsdArrayOfULong')
BEGIN
    CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong]
    AS N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="arrayOfUlong">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded"
                            name="u"
                            type="xs:unsignedLong" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';
END
GO

Ответ 5

Контекст всегда важен, например размер и сложность массива. Для небольших и средних списков несколько ответов, размещенных здесь, просто прекрасны, хотя некоторые разъяснения должны быть сделаны:

  • Для разделения списка с разделителями самый сплиттер на основе SQLCLR является самым быстрым. Существует множество примеров, если вы хотите написать свой собственный, или вы можете просто загрузить бесплатную библиотеку SQL # функций CLR (которую я написал, но функция String_Split и многие другие полностью свободны).
  • Разделение массивов на основе XML может быть быстрым, но вам нужно использовать XML-атрибут на основе атрибутов, а не элементный XML (который является единственным типом, указанным в ответах здесь, хотя пример @AaronBertrand XML является лучшим, поскольку его код использует функцию text() XML. Для получения дополнительной информации (например, анализа производительности) при использовании XML для разбиения списков проверьте "Использование XML для передачи списков в качестве параметров в SQL Server" от Phil Factor.
  • Использование TVP отлично (при условии, что вы используете, по крайней мере, SQL Server 2008 или новее) по мере того, как данные передаются в proc и отображаются предварительно проанализированные и сильно типизированные как переменные таблицы. ОДНАКО, в большинстве случаев сохранение всех данных в DataTable означает дублирование данных в памяти при копировании из исходной коллекции. Следовательно, использование метода DataTable для передачи в TVP не работает хорошо для больших наборов данных (т.е. Плохо масштабируется).
  • XML, в отличие от простых разделительных списков Ints или Strings, может обрабатывать более чем одномерные массивы, подобно TVP. Но также, как и метод DataTable TVP, XML не масштабируется, так как он более чем удваивает объем данных в памяти, поскольку ему необходимо дополнительно учитывать накладные расходы XML-документа.

При всем том, что, если данные, которые вы используете, являются большими или не очень большими, но постоянно растут, то метод IEnumerable TVP - лучший выбор, поскольку он передает данные на SQL Server (например, t21 > ), но не требует дублирования коллекции в памяти (в отличие от любого другого метода). В этом ответе я привел пример кода SQL и С#: Pass Dictionary < string, int > к хранимой процедуре T-SQL.

Ответ 6

Нет поддержки массива в sql-сервере, но есть несколько способов, с помощью которых вы можете передать коллекцию в сохраненный процесс.

  • Используя datatable
  • Используя XML.Try, конвертируйте свою коллекцию в формате xml, а затем передайте ее как вход в хранимую процедуру.

Следующая ссылка может помочь вам

передача коллекции в хранимую процедуру

Ответ 7

Я просматривал все примеры и ответы о том, как передать любой массив на сервер sql без проблем с созданием нового типа таблицы, пока я не найду этот linK ниже, как я применил его к моему проекту:

- Следующий код собирает массив в качестве параметра и вставляет значения этого --array в другую таблицу

Create Procedure Proc1 


@UserId int, //just an Id param
@s nvarchar(max)  //this is the array your going to pass from C# code to your Sproc

AS

    declare @xml xml

    set @xml = N'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'

    Insert into UserRole (UserID,RoleID)
    select 
       @UserId [UserId], t.value('.','varchar(max)') as [RoleId]


    from @xml.nodes('//root/r') as a(t)
END

Надеюсь, вам понравится

Ответ 8

Это поможет вам.:) Следуйте следующим шагам,

  • Откройте конструктор запросов

  • Скопировать Вставьте следующий код так, как он есть, он создаст функцию, которая преобразует String в Int

    CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );
GO

  • Создайте следующую хранимую процедуру

     CREATE PROCEDURE dbo.sp_DeleteMultipleId
 @List VARCHAR(MAX)
 AS
 BEGIN
      SET NOCOUNT ON;
      DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, ',')); 
 END
 GO

  • Выполните этот SP с помощью exec sp_DeleteId '1,2,3,12', это строка идентификатора, которую вы хотите удалить,

  • Вы преобразуете свой массив в строку в С# и передаете его как параметр хранимой процедуры

    int[] intarray = { 1, 2, 3, 4, 5 };  
string[] result = intarray.Select(x=>x.ToString()).ToArray();


    SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "sp_DeleteMultipleId";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Id",SqlDbType.VARCHAR).Value=result ;

Это приведет к удалению нескольких строк, всего наилучшего

Ответ 9

Мне потребовалось много времени, чтобы понять это, поэтому на случай, если кому-то это понадобится...

Это основано на методе SQL 2005 в ответе Аарона и использовании его функции SplitInts (я просто удалил параметр delim, так как всегда буду использовать запятые). Я использую SQL 2008, но мне нужно что-то, что работает с типизированными наборами данных (XSD, TableAdapters), и я знаю, что строковые параметры работают с ними.

Я пытался заставить свою функцию работать в статье типа "где в (1,2,3)" и не повезло прямолинейно. Поэтому я сначала создал временную таблицу, а затем сделал внутреннее соединение вместо "где в". Вот мой пример использования, в моем случае я хотел получить список рецептов, которые не содержат определенных ингредиентов:

CREATE PROCEDURE dbo.SOExample1
    (
    @excludeIngredientsString varchar(MAX) = ''
    )
AS
    /* Convert string to table of ints */
    DECLARE @excludeIngredients TABLE (ID int)
    insert into @excludeIngredients
    select ID = Item from dbo.SplitInts(@excludeIngredientsString)

    /* Select recipies that don't contain any ingredients in our excluded table */
   SELECT        r.Name, r.Slug
FROM            Recipes AS r LEFT OUTER JOIN
                         RecipeIngredients as ri inner join
                         @excludeIngredients as ei on ri.IngredientID = ei.ID
                         ON r.ID = ri.RecipeID
WHERE        (ri.RecipeID IS NULL)