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

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

---

내 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>저장된 절차 에 대한 전달 목록을 전달하는 방법이 있습니까 ? 아니면 더 좋은 방법이 있습니까?

감사합니다, 브렛

---

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 측에서 분할하고 필요한 모든 작업을 수행하십시오.

---

CREATE TYPE [dbo].[StringList1] AS TABLE(
[Item] [NVARCHAR](MAX) NULL,
[counts][nvarchar](20) NULL);

TYPE을 테이블로 만들고 이름을 "StringList1"로 지정합니다.

create PROCEDURE [dbo].[sp_UseStringList1]
@list StringList1 READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.item,l.counts FROM @list l;
    SELECT l.item,l.counts into tempTable FROM @list l;
 End

위와 같이 프로 시저를 만들고 이름을 "UserStringList1"로 지정합니다.

String strConnection = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString.ToString();
            SqlConnection con = new SqlConnection(strConnection);
            con.Open();
            var table = new DataTable();

            table.Columns.Add("Item", typeof(string));
            table.Columns.Add("count", typeof(string));

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

            }
                SqlCommand cmd = new SqlCommand("exec sp_UseStringList1 @list", con);


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

                    cmd.Parameters.Add(pList);
                    string result = string.Empty;
                    string counts = string.Empty;
                    var dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        result += dr["Item"].ToString();
                        counts += dr["counts"].ToString();
                    }

C #에서 이것을 시도하십시오

참고 URL : https://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure