SQL 저장 프로시저에 목록 전달 <>

SQL 저장 프로시저에 목록 전달 <>

데이터베이스의 특정 레코드에 여러 항목을 로드해야 하는 경우가 많았습니다.예를 들어, 웹 페이지에는 단일 보고서에 대해 포함할 항목이 표시됩니다. 모든 항목은 데이터베이스에 있는 레코드입니다(보고서는 보고서 테이블의 레코드이고 항목은 항목 테이블의 레코드입니다).사용자가 웹 앱을 통해 단일 보고서에 포함할 항목을 선택하고 있으며, 3개 항목을 선택하여 제출한다고 가정합니다.이 프로세스는 ReportItems(ReportId,ItemId)라는 테이블에 레코드를 추가하여 이 세 가지 항목을 이 보고서에 추가합니다.

현재 코드에서 다음과 같은 작업을 수행합니다.

public void AddItemsToReport(string connStr, int Id, List<int> itemList)
{
    Database db = DatabaseFactory.CreateDatabase(connStr);

    string sqlCommand = "AddItemsToReport"
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

    string items = "";
    foreach (int i in itemList)
        items += string.Format("{0}~", i);

    if (items.Length > 0)
        items = items.Substring(0, items.Length - 1);

    // Add parameters
    db.AddInParameter(dbCommand, "ReportId", DbType.Int32, Id);
    db.AddInParameter(dbCommand, "Items", DbType.String, perms);
    db.ExecuteNonQuery(dbCommand);
}

저장 프로시저의 내용은 다음과 같습니다.

INSERT INTO ReportItem (ReportId,ItemId)
SELECT  @ReportId,
          Id
FROM     fn_GetIntTableFromList(@Items,'~')

여기서 함수는 정수의 한 열 테이블을 반환합니다.

제 질문은 이것입니다: 이런 것을 다룰 수 있는 더 좋은 방법이 있을까요?데이터베이스 정규화 같은 것에 대해 묻는 것이 아닙니다. 제 질문은 특히 코드와 관련이 있습니다.

SQL Server 2008으로 이동하는 것이 선택사항이라면, 이 정확한 문제를 해결할 수 있는 "테이블 값 매개 변수"라는 새로운 기능이 있습니다.

여기와 여기에서 TVP에 대한 자세한 내용을 확인하거나 Google에 "SQL Server 2008 테이블 값 매개 변수"를 요청하면 많은 정보와 샘플을 찾을 수 있습니다.

적극 권장 - SQL Server 2008으로 이동할 수 있는 경우...

문자열 조인 논리는 다음과 같이 단순화할 수 있습니다.

string items = 
    string.Join("~", itemList.Select(item=>item.ToString()).ToArray());

그러면 에서 비싼 문자열 연결을 절약할 수 있습니다.그물.

저는 당신이 물건을 저장하는 방식에 아무 문제가 없다고 생각합니다.당신은 db로의 이동을 제한하고 있는데, 이것은 좋은 일입니다.만약 당신의 데이터 구조가 int 목록보다 더 복잡하다면, 나는 XML을 제안할 것입니다.

참고: 저는 댓글에서 이것이 우리에게 문자열 연결을 저장할 수 있는지에 대한 질문을 받았습니다(실제로 그렇습니다).저는 그것이 훌륭한 질문이라고 생각하고 그것에 대해 후속 조치를 취하고 싶습니다.

오픈 스트링을 벗기면.Reflector에 가입하면 Microsoft가 안전하지 않은 몇 가지를 사용하고 있음을 알 수 있습니다( 참조).문자 포인터 및 UnSafeCharBuffer라는 구조체를 사용하는 것을 포함한 순 단어 의미) 기술.그들이 하는 일은, 여러분이 정말로 그것을 졸이면, 빈 문자열을 가로질러 걸어가서 결합을 구축하기 위해 포인터를 사용하는 것입니다.에서 문자열 연결이 매우 비싼 주된 이유는 에 있습니다.Net은 문자열이 불변하기 때문에 모든 연결에 대해 힙에 새 문자열 개체가 배치되는 것입니다.이러한 메모리 작업은 비용이 많이 듭니다.끈.조인(...)은 기본적으로 메모리를 한 번 할당한 다음 포인터로 메모리에 대해 작업하는 것입니다.굉장히 빠릅니다.

기술의 한 가지 잠재적인 문제는 매우 큰 목록을 처리하지 않는다는 것입니다. 데이터베이스의 최대 문자열 길이를 초과할 수 있습니다.정수 값을 지정된 최대값보다 작은 문자열 열거형으로 연결하는 도우미 메서드를 사용합니다(다음 구현에서는 중복 ID도 선택적으로 확인하고 제거합니다).

public static IEnumerable<string> ConcatenateValues(IEnumerable<int> values, string separator, int maxLength, bool skipDuplicates)
{
    IDictionary<int, string> valueDictionary = null;
    StringBuilder sb = new StringBuilder();
    if (skipDuplicates)
    {
        valueDictionary = new Dictionary<int, string>();
    }
    foreach (int value in values)
    {
        if (skipDuplicates)
        {
            if (valueDictionary.ContainsKey(value)) continue;
            valueDictionary.Add(value, "");
        }
        string s = value.ToString(CultureInfo.InvariantCulture);
        if ((sb.Length + separator.Length + s.Length) > maxLength)
        {
            // Max length reached, yield the result and start again
            if (sb.Length > 0) yield return sb.ToString();
            sb.Length = 0;
        }
        if (sb.Length > 0) sb.Append(separator);
        sb.Append(s);
    }
    // Yield whatever's left over
    if (sb.Length > 0) yield return sb.ToString();
}

그런 다음 다음과 같은 방법으로 사용합니다.

using(SqlCommand command = ...)
{
    command.Connection = ...;
    command.Transaction = ...; // if in a transaction
    SqlParameter parameter = command.Parameters.Add("@Items", ...);
    foreach(string itemList in ConcatenateValues(values, "~", 8000, false))
    {
        parameter.Value = itemList;
        command.ExecuteNonQuery();
    }
}

이미 가지고 있는 것을 수행하여 구분된 문자열을 전달한 다음 테이블 값으로 구문 분석하거나 다른 선택지가 XML을 전달하는 것과 거의 유사합니다.

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

SQL 2008에 이러한 유형의 작업을 처리할 수 있는 새로운 기능이 추가되었는지 확인할 기회가 아직 없었습니다.

테이블 값 매개 변수를 사용하지 않는 이유는 무엇입니까?https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

이 문제와 사용할 수 있는 다양한 접근 방식에 대한 자세한 내용은 http://www.sommarskog.se/arrays-in-sql-2005.html 을 참조하십시오.

다음은 sqlteam.com 의 표 값 매개 변수에 대한 명확한 설명입니다.테이블 값 매개 변수

저장 프로시저의 여러 값에 대한 단일 필드 쿼리
http://www.norimek.com/blog/post/2008/04/Query-a-Single-Field-for-Multiple-Values-in-a-Stored-Procedure.aspx

언급URL : https://stackoverflow.com/questions/209686/passing-list-to-sql-stored-procedure