SQL 쿼리 중에 ID를 얻고 다른 Insert Statement에 사용하기

.net c# dapper sql-server transactions

문제

그래서 나는 dapper를 사용하고 사용자가 데이터베이스를 조작 할 수있는 라이브러리를 만들고 있습니다.

다음을 달성하는 최선의 방법을 찾는 데 도움이 필요합니다.

"주문"테이블이 있고 "거래"테이블과 "주문 _ 라인"테이블이 있다고 가정 해 보겠습니다. 나는 "테이블"의 Increment Id를 "트랜잭션"과 "order_line"테이블의 컬럼에 저장하기 위해 삽입하고 사용할 때 이것을 사용하고 싶습니다. 그리고이 모든 것이 SQL 트랜잭션으로 끝나기를 원합니다. 문제가있는 경우.

이제는 내 라이브러리가 모든 유형 및 작업에 역동적이므로 이와 같은 방식으로 접근하는 방법에 대해서는 확신 할 수 없습니다.

삽입 방법에 대한 코드는 다음과 같습니다. 2 개의 전역 변수가 있습니다.

  private string connectionString { get; set; }

        public void newConnection(string connection)
        {
            if (string.IsNullOrWhiteSpace(connectionString))
            {
                connectionString = connection;
            }
        }

        private List<KeyValuePair<string, object>> transactions = new List<KeyValuePair<string, object>>();

다음은 클래스를 데이터베이스에 저장하도록 호출하는 방법입니다.

public void Add(object item)
{
    string propertyNames = "";
    string propertyParamaters = "";
    Type itemType = item.GetType();

    System.Reflection.PropertyInfo[] properties = itemType.GetProperties();

    for (int I = 0; I < properties.Count(); I++)
    {
        if (properties[I].Name.Equals("Id", StringComparison.CurrentCultureIgnoreCase) || properties[I].Name.Equals("AutoId", StringComparison.CurrentCultureIgnoreCase))
        {
            continue;
        }

        if (I == properties.Count() - 1)
        {
            propertyNames += "[" + properties[I].Name + "]";
            propertyParamaters += "@" + properties[I].Name;
        }
        else
        {
            propertyNames += "[" + properties[I].Name + "],";
            propertyParamaters += "@" + properties[I].Name + ",";
        }
    }

    string itemName = itemType.Name;
    KeyValuePair<string, object> command = new KeyValuePair<string, object>($"Insert Into[{ itemName}] ({ propertyNames}) Values({ propertyParamaters})", item);
    transactions.Add(command);
}

더 많은 방법이 있으며 편집, 제거, 목록 편집, 목록 제거 등이 있지만이 경우에는 관련이 없습니다.

호출하는 데이터베이스에 변경 사항을 커밋하고자 할 때 :

public void SaveChanges()
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        foreach (KeyValuePair<string, object> command in transactions)
                        {
                            sqlConnection.Execute(command.Key, command.Value, sqlTransaction);
                        }

                        sqlTransaction.Commit();
                    }
                    catch
                    {
                        sqlTransaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        sqlConnection.Close();
                        transactions.Clear();
                    }
                }
                sqlConnection.Close();
            }

            transactions.Clear();
        }

내 도서관은 github.com에서 찾을 수 있습니다.
https://github.com/pietercdevries/Bamboo.Net

수락 된 답변

그것은 할 수 ... 네 ... 우리는 스스로 이것을하려고 노력해야한다 ... 나는하지 않을 것이다. 그러나 그것을 어떤 방식 으로든 시도 할 수있다.

이 코드를 더 간단하게 만들 수있는 몇 가지 아이디어는 다음과 같습니다.

  • 헬퍼 인터페이스를 정의하고 데이터 클래스가이를 구현하도록하거나 속성 선언을 사용하여 ID 필드와 외래 키 참조를 지정합니다.
  • 인젝션 또는 코드 생성 기술을 조사하여 런타임이 아닌 컴파일 타임에 실행되는 이러한 '동적 인'코딩 및 조회를 얻을 수 있습니다.

Dapper와 SqlConnection을 사용하지 않습니다 .Execute ()는 익숙하지 않은 확장 메서드이지만 전달 된 DbParameters를 개체에서 생성한다고 가정하고 실행될 때 SqlCommand에 적용한다고 가정합니다. 아마 dapper는 매개 변수를 추출하는 몇 가지 함수를 가지고 있기 때문에이 코드 예제에서 사용할 수 있습니다. 또는 아마도 이러한 개념 중 일부를 사용하여 더러운 코드에 적용 할 수 있습니다. 그저 앞 부분을 인정하고 명령을 실행할 때 객체를 매개 변수화하는 코드 예제를 생략했습니다.

다음 스 니펫이 내려갈 여정입니다.

  1. 생성 된 SQL을 준비하여 Id 필드 캡처
  2. 변경 사항을 저장할 때 Id 값 출력
  3. 배열의 나머지 모든 객체를 반복하고 외래 키 값을 설정합니다.

참고 : 이러한 코드 변경 사항은 프로덕션을 위해 테스트되거나 예외 처리되지 않으며, 개념을 증명하고 동료 코더를 돕기 위해이 "모범 사례"를 호출하지 않습니다.

  1. 이미 Id 필드 추적을위한 컨벤션을 고안해 냈습니다. 출력 매개 변수의 값을 설정하기 위해 SQL 문을 준비하여 아이디어를 확장 할 수 있습니다.

    참고 : MS SQL에서는 SCOPE_IDENTITY ()를 @@ Identity보다 우선 사용하십시오.
    Scope_Identity (), Identity (), @@ Identity 및 Ident_Current의 차이점은 무엇입니까?

    참고 : 생성 된 문은 매개 변수를 사용하고 있으며 매개 변수 값을 아직 읽지 않으므로 나중에 다른 개체 ... phew에 삽입 할 ID 값을 찾은 후에 저장된 SQL 문을 다시 생성 할 필요가 없습니다.

    public void Add(object item)
    {
        List<string> propertyNames = new List<string>();
        Type itemType = item.GetType();
    
        System.Reflection.PropertyInfo[] properties = itemType.GetProperties();
    
        for (int I = 0; I < properties.Count(); I++)
        {
            if (properties[I].Name.Equals("Id", StringComparison.CurrentCultureIgnoreCase) || properties[I].Name.Equals("AutoId", StringComparison.CurrentCultureIgnoreCase))
            {
                continue;
            }
            propertyNames.Add(properties[I].Name);
        }
    
        string itemName = itemType.Name;
        KeyValuePair<string, object> command = new KeyValuePair<string, object>
            ($"Insert Into[{itemName}] ({String.Join(",", propertyNames.Select(p => $"[{p}]"))}) Values({String.Join(",", propertyNames.Select(p => $"@{p}"))}); SET @OutId = SCOPE_IDENTITY();", item);
        transactions.Add(command);
        // Simply append your statement with a set command on an @id parameter we will add in SaveChanges()
    }
    
  2. 변경 내용 저장에서 출력 매개 변수를 구현하여 생성 된 ID를 캡처하고 ID가 캡처 된 경우 명령이 연결된 개체에 다시 저장합니다.

    참고 :이 코드 스 니펫은 항목 3의 솔루션에 대한 참조를 보여줍니다. foreach가 for로 대체되었으므로 현재 색인에서 순방향 반복을 수행 할 수 있습니다

    public void SaveChanges()
    {
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            sqlConnection.Open();
            using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
            {
                try
                {
                    for (int i = 0; i < transactions.Count; i++)
                    {
                        KeyValuePair<string, object> command = transactions[i];
                        // 1. Execute the command, but use an output parameter to capture the generated id
                        var cmd = sqlConnection.CreateCommand();
                        cmd.Transaction = sqlTransaction;
                        cmd.CommandText = command.Key;
                        SqlParameter p = new SqlParameter()
                        {
                            ParameterName = "@OutId",
                            Size = 4,
                            Direction = ParameterDirection.Output
                        };
                        cmd.Parameters.Add(p);
                        cmd.ExecuteNonQuery();
    
                        // Check if the value was set, non insert operations wil not set this parameter
                        // Could optimise by not preparing for the parameter at all if this is not an 
                        // insert operation.
                        if (p.Value != DBNull.Value)
                        {
                            int idOut = (int)p.Value;
    
                            // 2. Stuff the value of Id back into the Id field.
                            string foreignKeyName = null;
                            SetIdValue(command.Value, idOut, out foreignKeyName);
                            // 3. Update foreign keys, but only in commands that we haven't execcuted yet
                            UpdateForeignKeys(foreignKeyName, idOut, transactions.Skip(i + 1));
                        }
                    }
    
                    sqlTransaction.Commit();
                }
                catch
                {
                    sqlTransaction.Rollback();
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                    transactions.Clear();
                }
            }
            sqlConnection.Close();
        }
    
        transactions.Clear();
    }
    
    
    /// <summary>
    /// Update the Id field of the specified object with the provided value
    /// </summary>
    /// <param name="item">Object that we want to set the Id for</param>
    /// <param name="idValue">Value of the Id that we want to push into the item</param>
    /// <param name="foreignKeyName">Name of the expected foreign key fields</param>
    private void SetIdValue(object item, int idValue, out string foreignKeyName)
    {
        // NOTE: There are better ways of doing this, including using interfaces to define the key field expectations.
        // This logic is consistant with existing code so that you are familiar with the concepts
        Type itemType = item.GetType();
        foreignKeyName = null;
    
        System.Reflection.PropertyInfo[] properties = itemType.GetProperties();
    
        for (int I = 0; I < properties.Count(); I++)
        {
            if (properties[I].Name.Equals("Id", StringComparison.CurrentCultureIgnoreCase) || properties[I].Name.Equals("AutoId", StringComparison.CurrentCultureIgnoreCase))
            {
                properties[I].SetValue(item, idValue);
                foreignKeyName = $"{item.GetType().Name}_{properties[I].Name}";
                break;
            }
        }
    }
    
  3. 이제 개체에 ID가 삽입되어 업데이트됩니다. 이제 재미있는 부분을 위해 ... ID를 업데이트 한 후에는 다른 객체를 반복하고 외래 키 필드를 업데이트해야합니다.

현실에서 어떻게 진행되는지는 업데이트중인 데이터에 대해 어떤 종류의 가정 / 관례를 시행 할 지에 따라 달라집니다. 간단히하기 위해 업데이트해야하는 모든 외래 키는 {ParentClassName} _ {Id} 규칙에 따라 이름이 지정됩니다.

즉, 예제에서 방금 새 '위젯'을 삽입하면이 트랜잭션 범위에서 'Widget_Id'(또는 'Widget_AutoId') 필드가있는 다른 모든 개체를 강제로 업데이트 할 수 있습니다.

    private void UpdateForeignKeys(string foreignKeyName, int idValue, IEnumerable<KeyValuePair<string, object>> commands)
    {
        foreach(var command in commands)
        {
            Type itemType = command.Value.GetType();
            var keyProp = itemType.GetProperty(foreignKeyName);
            if(keyProp != null)
            {
                keyProp.SetValue(command.Value, idValue);
            }
        }
    }

이것은 OPs 데이터 지속성 라이브러리에서 외부 (또는 참조) 키를 업데이트하는 방법에 대한 매우 단순한 예입니다. 관계형 키 필드는 규칙을 사용하여 거의 일관되게 명명되지 않지만 규칙을 따를 경우에도 동일한 유형의 부모 (예 : 하나의 매니페스트)에 대한 참조가 여러 개인 테이블을 지원하지 않습니다. 내 고객의 앱에 3 개의 링크가 사용자 테이블에 다시 있습니다.

public class Manifest
{
    ...
    Driver_UserId { get; set; }
    Sender_UserId { get; set; }
    Receiver_UserId { get; set; }
    ...
}

모든 가능한 연계 조합을 다루려면 꽤 발전된 논리를 발전시켜야합니다.

일부 ORM은 값을 음수로 설정하고 각 유형의 숫자를 감소시켜 새로운 유형이 명령 모음에 추가됩니다. 그런 다음 삽입 후에 위조 된 숫자를 업데이트 된 번호로 유지 한 키 필드 만 업데이트하면됩니다. 어떤 필드가 핵심 필드인지 알아야하지만 적어도 각 관계의 끝을 구성하는 정확한 필드를 추적 할 필요는 없으며 값을 추적 할 수 있습니다.

Entity Framework가 어떻게 작동하는지 알고 싶습니다. 속성에 대한 속성을 사용하여 필드에 대한이 링크 정보를 주입 해보십시오. 직접 작성해야 할 수도 있지만, SQL 모델을 생성하는 것뿐만 아니라 나중에 모든 종류의 논리를 활용할 수있는 방식으로 데이터 모델 클래스에서 이러한 관계를 설명하도록하는 명확한 선언적 개념입니다.

Dapper에 대해 너무 비판적이지는 않겠지 만 일단이 경로를 따르거나 수동으로 참조 무결성을 관리하기 시작하면 Entity Framework 나 nHibernate와 같은 엔터프라이즈 급 ORM을 고려해야합니다. 물론 그들은 수하물을 가지고 있지만 그 ORM은 실제로 커뮤니티에 의해 최적화 된 성숙한 제품으로 발전했습니다. 지금은 RDBMS와의 모든 상호 작용을 사용자 정의하기위한 수동 작성 또는 스크립트 코드가 거의 없으므로 테스트 또는 유지 보수 비용이 훨씬 적습니다. (= 적은 버그)


인기 답변

그것은 당신이 사용하는 데이터베이스를 말하지 않습니다. MSSQL이라면 할 수있다.

var id =  connection.Query<int?>("SELECT @@IDENTITY").SingleOrDefault();

삽입을 실행 한 후 마지막 삽입물의 ID를 알려줍니다.



아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.