배경지식 - ORM

ORM

Object-Relational Mapping의 약자로 OOP에서 데이터베이스를 쉽게 사용하기 위한 도구

객체지향 프로그래밍 언어에서 사용되는 객체와 관계형 데이터베이스의 데이터를 자동으로 변환하고 매핑해줌

따라서 ORM을 사용하면 개발자가 SQL 쿼리를 직접 작성하지 않고도 데이터베이스와 상호작용할 수 있음

C#의 ORM으로는 Entity Framework, Dapper 등이 존재

 

---

SQLKata

SQL 쿼리 빌더를 제공하는 오픈 소스 라이브러리로, MySQL / PostgreSQL / Microsoft SQL Server 등 대부분의 RDBMS를 지원

라이브러리는 NuGet 패키지로 제공되기 때문에 C# 프로젝트에 쉽게 추가할 수 있음

• 쿼리 빌더 : C# 코드로 쿼리를 작성하고 실행 가능
• 조건절 빌더 : C# 코드로 조건절을 작성하고 쿼리에 동적으로 추가
• 테이블 조작 : 테이블의 생성, 수정, 삭제 등의 조작
• 결과 처리 : 쿼리의 결과를 C# 객체로 매핑하거나 컬렉션으로 처리
• 트랜잭션 관리 : 트랜잭션을 시작, 커밋, 롤백

SQLKata는 단순히 쿼리를 작성하는 SQL 쿼리 빌더이기 때문에 SQLKata가 베이스로 하는 Dapper를 ORM으로 활용하여 C# 객체와 데이터베이스 간의 매핑을 수행함

 

SQLKata를 사용한 예시

public class IDNameValue
    {
        public int ID { get; set; }
        public string? Name { get; set; }
        public int Value { get; set; }

        public override string ToString()
        {
            return $"ID={ID} Name={Name} Value={Value}";
        }
    }

ID, Name, Value를 가지는 IDNameValue 테이블에 대한 클래스를 생성

 

var config = new MySqlConnectionStringBuilder()
{
    Server = "localhost", // 호스트 이름
    Database = "testdb", // 데이터베이스 이름
    UserID = "root", // 사용자 이름
    Password = "password", // 비밀번호
    ConnectionTimeout = 60, // 연결 타임아웃 (초)
    Port = 3306, // 포트 번호
    Pooling = false // 풀링 사용 여부
};

MySQL 연결 문자열 빌더 생성

 

using (var conn = new MySqlConnection(config.ConnectionString))
{
    conn.Open();

MySQL 객체 생성 후 연결

 

    var compiler = new MySqlCompiler();
    var db = new QueryFactory(conn, compiler);

SQLKata에서 실제로 쿼리를 만드는 QueryFactory 클래스 생성

 

    db.Logger = complied =>
    {
        Console.WriteLine("발행된 SQL");
        Console.WriteLine("SQL:" + complied.Sql.ToString());
        Console.WriteLine("Parameters:" + string.Join(",", complied.Bindings));
    };

쿼리를 확인하기 위해 표준 출력에 작성된 쿼리 출력

 

    db.Statement("create table IDNameValue(ID integer primary key, Name text, Value integer);");

위와 같이 Statement()를 사용하여 SQL문을 직접 작성해 전달할 수 있음

단, 이 경우 SQL문의 유효성을 직접 확인해야하며 SQL Injection 공격 등의 보안 위험에 노출될 수 있음

 

    db.Query("IDNameValue").CreateTable(
        new Column("ID", SqlDbType.Int).Identity().PrimaryKey(),
        new Column("Name", SqlDbType.NVarChar, 50),
        new Column("Value", SqlDbType.Int)
    ).Execute();

 

따라서 위와 같이 Query() 메서드를 사용하는 것이 권장됨

이 경우 SQLKata가 SQL 쿼리를 추상화하고, 데이터베이스에 대한 호환성을 제공하며 SQL Injection과 같은 보안 위험을 방지함

또한 Column 클래스를 사용하여 각 열의 속성을 명확하게 지정할 수 있기 때문에 가독성과 유지보수성이 향상됨

CreateTable() 메서드는 데이터베이스의 스키마를 변경하는 작업을 수행하는 DDL 쿼리이기 때문에 명시적으로 Execute() 메서드를 호출하여 데이터베이스의 실행 권한을 확인하여야함

 

    db.Query("IDNameValue").Insert(new { ID = 1, Name = "Hoge1", Value = 101 });

    db.Query("IDNameValue").Insert(new[] { "ID", "Name", "Value" },
        new[]
        {
            new object[] {2, "Hoge2", 102 },
            new object[] {3, "Hoge3", 103 },
            new object[] {4, "Hoge4", 104 }
        });

테이블에 데이터를 삽입

데이터베이스의 스키마 자체를 변경하지 않는 DML 쿼리는 실행 권한에 제약을 받지 않기 때문에 Execute() 메서드를 호출하지 않아도 됨

 

    db.Query("IDNameValue").Where("ID", 4).Update(new { Value = 90423 });
    db.Query("IDNameValue").Where("ID", "<=", 2).Delete();

데이터의 업데이트 및 삭제

 

    foreach(var idnamevalue in db.Query("IDNameValue").Get<IDNameValue>())
    {
        Console.WriteLine(idnamevalue.ToString());
    }
}

db.Query("IDNameValue")로 IDNameValue 테이블에 대해 SELECT 문을 생성 (= SELECT * FROM IDNameValue)

Get<IDNameValue>()는 실행된 쿼리에 대한 결과를 IDNameValue 클래스로 매핑하여 반환

이 때 foreach() 문으로 IDNameValue 클래스의 객체들을 idnamevalue 변수에 하나씩 할당하여 순회하면서 Console.WriteLine() 메서드를 사용하여 출력

---

그 밖에 SQLKata에서 사용되는 문법들

SP(Stored Procedure) 사용시

var users = db.Select("exec sp_get_users_by_date @date", new {date = DateTime.UtcNow});

sp_get_users_by_date 저장 프로시저를 실행

 

 

추후 필요할 때마다 문법 추가 예정