[2021.03] SqLite + Dapper(ORM) 사용방법

환경: Visual Studio 2017, C#, 닷넷 4.6.1 

 

검색능력이 떨어진 건지 제대로 된 게시물이 없는 건지 국/내외 게시물을 다 뒤져도 안나와서 짜집기 + 자작으로 작성해보았다. Node.js에서 Squelize로 작년까지 개발하다가 C#으로 넘어오니 ORM 패키지가 별로 없었다. Sonic도 있다고 하던데 이건 차차 알아보기로 하고 오늘은 Dapper를 사용해보자. 

SQLite란?

SQLite는 원래 모바일 특히 안드로이드에서 사용하는 데이터베이스이다. 작지만 강력하고 크로스플랫폼에서 사용할 수 있는 무료인 파일DB로 보면 된다. 

 

Dapper란?

Dapper는 요즘 깡 쿼리보다는 모델링된 객체를 통해 CRUD를 하는 방식의 ORM(Object Relational Mapping) 방식의 패키지이다. 이걸 설명할 것이 아니기 때문에 자세히 알려면 인터넷에서 찾아보자. 많이 나온다. 

 

ORM의 장점은 패키지가 지원해주는 모든 DBMS를 연결할 수 있다. 깡쿼리를 하게 되면 표준 SQL이 아닌 DB마다 다른 함수를 사용하게 될 경우 소스가 난잡해지는 상황을 겪었을 것인데, 이런 부분이 말끔히 해소가 된다. 

 

구현방법은 테이블 스키마를 모델링 객체로 만들고 CRUD에 대한 인터페이스를 구현해서 사용하는 방식으로 보면 된다. 

 

개발환경 설치

Nuget 패키지 관리자에서 "Dapper" 검색하면 바로 나온다. 설치하자.

위에 설명글을 보면 SQL Server(MS-SQL), MySQL, Sqlite, SqlCE, firebird 등을 지원한다고 나와있다. 

 

SqLite는 Nuget 패키지 관리자에서 보면 뭔가 많다. 그래서 필자는 콘솔에서 설치하였다.

Install-Package System.Data.SQLite

32, 64bit 모두 설치가 된다고 한다. 홈페이지에 있는 설치파일을 설치(내가 32비트로 설치했나?)하고 설치된 경로의 DLL을 직접 참조해도 되지만 빌드오류가 발생했다. 위와 같이 설치하니 잘되니 필자처럼 삽질하지 말자. 

 

코딩

이제 코딩해보자.  출처를 기반으로 설명해보겠다. 출처 소스가 한군데 잘못된 부분이 있다. 

 

콘솔이나 윈폼으로 프로젝트를 만들고, Models폴더를 생성해 "Cars.cs" 클래스 파일을 생성한다.

using System;

namespace DBTest.Models
{
    public class Car
    {
        public long Id { get; set; }
        public string modelName { get; set; }
        public int years { get; set; }
        public string createAt { get; set; }
    }
}

 테이블의 구조라고 생각하자. 

 

Interfaces 폴더를 생성하고 "ICarsRepository.cs 인터페이스 파일을 생성한다.

using DBTest.Models;

namespace DBTest.Interfaces
{
    public interface ICarsRepository
    {
        Car GetCar(long id);
        void SaveCar(Car car);
        void DelCar(long id);
        void updateCar(Car car);
    }
}

GetCar는 단일 레코드를 위에서 생성한 Car 객체(구조체)에 넣어주는게 이번 글에 핵심이다. 이 역할을 해주는 것이 Dapper가 된다. 나머지는 Sqlite 기본 코드로 구현된다. 

일반 Grid나 Devexpress 같은 특정 객체로 넘겨받아야 한다면 여기서 인터페이스를 구성해주자.

(이건 다음 고도화 강좌에서 만나볼까?)

 

Services 폴더를 생성하고 "SqLiteBaseRepository.cs" 클래스 파일을 생성한다.

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;

namespace DBTest.Services
{
    public class SqLiteBaseRepository
    {
        public static string DbFile
        {
            get { return Environment.CurrentDirectory + "\\Simpledb.sqlite"; }
        }

        public static SQLiteConnection simpleDbConnection()
        {
            return new SQLiteConnection("Data Source=" + DbFile + ";Version=3;");
        }

        public static void CreateDatabase(List<string> sqlCmds)
        {
            if (!File.Exists(DbFile))
            {
                SQLiteConnection.CreateFile(DbFile);
            }

            using (var cnn = simpleDbConnection()) 
            {
                cnn.Open();
                
                foreach (string sqlCmd in sqlCmds)
                {
                    SQLiteCommand command = new SQLiteCommand(sqlCmd, cnn);
                    int result = command.ExecuteNonQuery();

                    if (result != 0)
                    {
                        // 에러처리, 에러나면 DB파일삭제 할까?

                    }
                }
                cnn.Close();
            }
        }

        public static SQLiteDataReader GetQuery(string sqlCmd)
        {
            if (!File.Exists(DbFile)) return null;

            using (var cnn = simpleDbConnection())
            {
                cnn.Open();
                SQLiteCommand command = new SQLiteCommand(sqlCmd, cnn);
                SQLiteDataReader rdr = command.ExecuteReader();
                return rdr;
            }
        }

        public static int ExecuteQuery(string sqlCmd)
        {
            if (!File.Exists(DbFile)) return -1;

            using (var cnn = simpleDbConnection())
            {
                cnn.Open();
                SQLiteCommand command = new SQLiteCommand(sqlCmd, cnn);
                int result = command.ExecuteNonQuery();
                return result;
            }
        }
    }
}

 

출처의 소스와 다른데 그때는 그렇게 했어도 됐는지 몰라도 지금은 위와 같이 처리해야 한다. 

SQLiteCommand 를 사용해서 처리하는 부분이 다르다. 

 

이 클래스는 기본적인 데이터베이스 생성, 연결, Select 쿼리와 실행쿼리(Insert, update, Delete)를 실행하는 기본 클래스가 된다. 

 

Controls폴더를 생성하고 "SqLiteCarRepository.cs" 파일을 생성한다.

using DBTest.Models;
using DBTest.Services;
using DBTest.Interfaces;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using Dapper;
using System;

namespace DBTest.Controls
{
    public class SqLiteCarRepository : SqLiteBaseRepository, ICarsRepository
    {
        public void DelCar(long id)
        {
            if (!File.Exists(DbFile)) return;

            int result = ExecuteQuery(@"delete from cars where Id = " + id);
            // 에러처리
        }

        public Car GetCar(long id)
        {
            if (!File.Exists(DbFile)) return null;

            using (var cnn = simpleDbConnection())
            {
                cnn.Open();
                string strCmd = String.Format("SELECT Id, modelName, years, createAt FROM cars WHERE Id = {0}", id);
                Car result = SqlMapper.Query<Car>(cnn, strCmd).FirstOrDefault();
                return result;
            }
        }

        public void SaveCar(Car car)
        {
            if (!File.Exists(DbFile))
            {
                CreateDB();
            }
            
            int result = ExecuteQuery(String.Format("INSERT INTO cars(modelName, years, createAt) VALUES('{0}', {1}, '{2}')", car.modelName, car.years, car.createAt));
            // 에러처리
        }

        public void updateCar(Car car)
        {
            if (!File.Exists(DbFile)) return;

            int result = ExecuteQuery(@"UPDATE cars SET modelName = '" + car.modelName + "', years = " + car.years + ", createAt = '" + car.createAt + "' WHERE Id = " + car.Id);
            // 에러 처리
        }

        public void CreateDB()
        {
            List<string> strCmds = new List<string>();
            strCmds.Add(@"create table cars(Id integer primary key AUTOINCREMENT, modelName varchar(100) not null, years integer not null, createAt datetime not null)");

            CreateDatabase(strCmds);
        }
    }
}

 

Car 모델링을 기반으로 CRUD를 제어하는 함수를 구현한 클래스다. Dapper 초보자라서 여기서는 Select 만 구현했다. 

(고도화 강좌에서 Insert, Update, Delete도 Dapper를 이용한 내용을 추가하도록 하겠다.)

 

출처소스에서 Select부분 구문이 잘못되어 있다. Dapper강좌소스에 Dapper는 선언해놓고 사용하지 않는 아이러니....

 

쿼리 구문을 String.format()로 감싸는 것이 중요하다. 아래와 같은 오류가 발생할 수 있다. (근데 Update는 왜 됐지??)

stackoverflow.com/questions/55029183/sqlite-c-sharp-error-system-data-sqlite-sqliteexception-sql-logic-error-unrec

SQLite C# Error: System.Data.SQLite.SQLiteException: 'SQL logic error unrecognized token: "'Income""'

 

폼에 다음과 같이 그리고 코딩을 해보자.

 

using System;
using System.Windows.Forms;
using DBTest.Controls;
using DBTest.Models;

namespace DBTest
{
    public partial class frmMain : Form
    {
        SqLiteCarRepository CarRepo = new SqLiteCarRepository();

        public frmMain()
        {
            InitializeComponent();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            Car data = new Car();
            data.modelName = "쏘렌토";
            data.years = 2018;
            data.createAt = "2018-01-01";

            CarRepo.SaveCar(data);
        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            Car Data = CarRepo.GetCar(1);
            string strMsg = "모델명: " + Data.modelName + "\r\n년식: " + Data.years + "\r\n등록일: " + Data.createAt;

            MessageBox.Show(strMsg);
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            Car data = new Car();
            data.modelName = "쏘렌토";
            data.years = 2018;
            data.createAt = "2018-01-01";
            data.Id = 1;

            CarRepo.updateCar(data);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            CarRepo.DelCar(1);
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}