[c#] C # SQL Server-저장 프로 시저에 목록 전달

Program / 글쓴이

내 C # 코드에서 SQL Server 저장 프로 시저를 호출하고 있습니다.

using (SqlConnection conn = new SqlConnection(connstring))
{
   conn.Open();
   using (SqlCommand cmd = new SqlCommand("InsertQuerySPROC", conn))
   {
      cmd.CommandType = CommandType.StoredProcedure;

      var STableParameter = cmd.Parameters.AddWithValue("@QueryTable", QueryTable);
      var NDistanceParameter = cmd.Parameters.AddWithValue("@NDistanceThreshold", NDistanceThreshold);
      var RDistanceParameter = cmd.Parameters.AddWithValue(@"RDistanceThreshold", RDistanceThreshold);

      STableParameter .SqlDbType = SqlDbType.Structured;
      NDistanceParameter.SqlDbType = SqlDbType.Int;
      RDistanceParameter.SqlDbType = SqlDbType.Int;

      // Execute the query
      SqlDataReader QueryReader = cmd.ExecuteReader();

내 저장 프로시 저는 상당히 표준 적이지만 조인을 수행합니다 QueryTable(따라서 저장 프로 시저를 사용해야 함).

이제 List<string>매개 변수 세트 에 문자열 목록을 추가하고 싶습니다 . 예를 들어, 저장된 proc 쿼리는 다음과 같습니다.

SELECT feature 
FROM table1 t1 
INNER JOIN @QueryTable t2 ON t1.fid = t2.fid 
WHERE title IN <LIST_OF_STRINGS_GOES_HERE>

그러나 문자열 목록은 동적이며 수백 개의 길이입니다.

List<string>저장된 proc 에 문자열 목록을 전달하는 방법이 있습니까 ??? 아니면 더 좋은 방법이 있습니까?

감사합니다, 브렛

답변

SQL Server 2008을 사용하는 경우 사용자 정의 테이블 유형이라는 새로운 기능이 있습니다. 다음은 사용 방법의 예입니다.

사용자 정의 테이블 유형을 만듭니다.

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

다음으로 저장 프로 시저에서 올바르게 사용해야합니다.

CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

마지막으로 C #에서 사용하는 몇 가지 SQL이 있습니다. 

using (var con = new SqlConnection(connstring))
{
    con.Open();

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
    {
        using (var table = new DataTable()) {
          table.Columns.Add("Item", typeof(string));

          for (int i = 0; i < 10; i++)
            table.Rows.Add("Item " + i.ToString());

          var pList = new SqlParameter("@list", SqlDbType.Structured);
          pList.TypeName = "dbo.StringList";
          pList.Value = table;

          cmd.Parameters.Add(pList);

          using (var dr = cmd.ExecuteReader())
          {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
          }
         }
    }
}

SSMS에서 실행하려면

DECLARE @list AS StringList

INSERT INTO @list VALUES ('Apple')
INSERT INTO @list VALUES ('Banana')
INSERT INTO @list VALUES ('Orange')

-- Alternatively, you can populate @list with an INSERT-SELECT
INSERT INTO @list
   SELECT Name FROM Fruits

EXEC sp_UseStringList @list

답변

이 상황의 일반적인 패턴은 쉼표로 구분 된 목록의 요소를 전달한 다음 SQL에서 사용할 수있는 테이블로 분할하는 것입니다. 대부분의 사람들은 일반적으로 다음과 같은 작업을 위해 지정된 함수를 만듭니다.

 INSERT INTO <SomeTempTable>
 SELECT item FROM dbo.SplitCommaString(@myParameter)

그런 다음 다른 쿼리에서 사용할 수 있습니다.

답변

아니요, 배열 / 목록은 SQL Server에 직접 전달할 수 없습니다.

다음 옵션을 사용할 수 있습니다.

  1. 쉼표로 구분 된 목록을 전달한 다음 SQL에서 함수를 사용하면 목록이 분할됩니다. 쉼표로 구분 된 목록은 대부분 Nvarchar ()로 전달됩니다.
  2. xml을 전달하고 SQL Server의 함수가 목록의 각 값에 대해 XML을 구문 분석합니다.
  3. 새로 정의 된 사용자 정의 테이블 유형 사용 (SQL 2008)
  4. 동적으로 SQL을 빌드하고 원시 목록을 “1,2,3,4”로 전달하고 SQL 문을 빌드합니다. 이것은 SQL 인젝션 공격에 취약하지만 작동합니다.

답변

예, Stored proc 매개 변수를 VARCHAR(...)
다음 과 같이 만든 다음 쉼표로 구분 된 값을 저장 프로 시저에 전달합니다.

Sql Server 2008을 사용하는 경우 TVP ( 테이블 값 매개 변수 )를 활용할 수 있습니다 . QueryTable의 구조가 문자열 배열보다 더 복잡한 경우 SQL 2008 TVP 및 LINQ 그렇지 않으면 SQl Server 내에서 테이블 형식을 만들어야하므로 과잉이 될 수 있습니다.

답변

List 대신 하나의 열로 데이터 테이블을 만들고 테이블에 문자열을 추가합니다. 이 데이터 테이블을 구조화 된 유형으로 전달하고 테이블의 제목 필드와 다른 조인을 수행 할 수 있습니다.

답변

SQL에서 CSV 목록을 분할하는 것을 선호하는 경우 CTE ( 공통 테이블 식)를 사용하는 다른 방법이 있습니다 . CTE를 사용하여 문자열을 분할하는 효율적인 방법을 참조하십시오 .

답변

내가 아는 유일한 방법은 CSV 목록을 작성한 다음 문자열로 전달하는 것입니다. 그런 다음 SP 측에서 분할하고 필요한 모든 작업을 수행하십시오.