Microsoft MVP성태의 닷넷 이야기
.NET Framework: 885. C# - 닷넷 응용 프로그램에서 Sqlite 사용 [링크 복사], [링크+제목 복사]
조회: 465
글쓴 사람
홈페이지
첨부 파일

C# - 닷넷 응용 프로그램에서 Sqlite 사용

요즘엔, 웬만한 건 모두 NuGet에 다 들어 있으니... 검색을 해보면 가장 상단에 sqlite가 나옵니다.

sqlite 3.13.0
; https://www.nuget.org/packages/sqlite/

아쉽게도 저건 C/C++ 사용자를 위한 native DLL을 담고 있을 뿐 닷넷 개발자에게는 Microsoft.Data.Sqlite가 의미 있습니다.

[닷넷 프레임워크]
Install-Package Microsoft.Data.SQLite -Version 3.1.1
; https://www.nuget.org/packages/Microsoft.Data.Sqlite/

[닷넷 코어]
Install-Package Microsoft.Data.Sqlite.Core -Version 3.1.1
; https://www.nuget.org/packages/Microsoft.Data.Sqlite.Core/

현재(2020-02-09) 3.1.1 기준으로 .NET Standard 2.0을 요구하기 때문에 .NET Framework의 경우 4.6.1 이상의 프로젝트에서 참조 추가할 수 있습니다. 사용법은, System.Data의 명세를 따른 만큼 일전에 소개한 다른 DB들과,

무료 데이터베이스 서버 성능 비교(SQL Server Express, IBM DB2 Express, MySQL, Sybase, PostgreSQL, Oracle XE)
; https://www.sysnet.pe.kr/2/0/1411

코드 상으로 거의 유사합니다.

using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string currentPath = Path.GetDirectoryName(typeof(Program).Assembly.Location);
            string sqlFilePath = Path.Combine(currentPath, "test.sqlite");

            string connectionString = $"Data Source={sqlFilePath};";

            using (SqliteConnection connection = new SqliteConnection(connectionString))
            {
                connection.Open();

                CreateTableIfNotExists(connection);

                // Create
                SqliteCommand insertCommand = new SqliteCommand();
                insertCommand.Connection = connection;
                insertCommand.CommandText = "INSERT INTO mytable(id, NAME, age, DESCRIPTION) VALUES (@id, @NAME, @age, @DESCRIPTION)";

                insertCommand.Parameters.Add("@id", SqliteType.Integer);
                insertCommand.Parameters.Add("@NAME", SqliteType.Text, 50);
                insertCommand.Parameters.Add("@age", SqliteType.Integer);
                insertCommand.Parameters.Add("@DESCRIPTION", SqliteType.Text, 150);

                string nameValue = "Name" + Guid.NewGuid().ToString();
                insertCommand.Parameters[0].Value = (int)DateTime.Now.Ticks;
                insertCommand.Parameters[1].Value = nameValue;
                insertCommand.Parameters[2].Value = 10;
                insertCommand.Parameters[3].Value = nameValue + "_Description";

                int affected = insertCommand.ExecuteNonQuery();
                Console.WriteLine("# of affected row: " + affected);

                // Update
                SqliteCommand updateCommand = new SqliteCommand();
                updateCommand.Connection = connection;
                updateCommand.CommandText = "UPDATE mytable SET DESCRIPTION=@DESCRIPTION WHERE NAME=@NAME";

                updateCommand.Parameters.Add("@NAME", SqliteType.Text, 50);
                updateCommand.Parameters.Add("@DESCRIPTION", SqliteType.Text, 150);

                updateCommand.Parameters[0].Value = nameValue;
                updateCommand.Parameters[1].Value = nameValue + "_Description2";

                affected = updateCommand.ExecuteNonQuery();
                Console.WriteLine("# of affected row: " + affected);

                // Select - ExecuteScalar
                SqliteCommand selectCommand = new SqliteCommand();
                selectCommand.Connection = connection;
                selectCommand.CommandText = "SELECT count(*) FROM mytable";

                object result = selectCommand.ExecuteScalar();
                Console.WriteLine("# of records: " + result);

                // Select - DataTable
                DataSet ds = new DataSet();

                /* DataAdapter 미구현

                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM mytable", sqlConnection);
                da.Fill(ds, "mytable");

                DataTable dt = ds.Tables["mytable"];
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine(string.Format("Name = {0}, Desc = {1}", dr["NAME"], dr["DESCRIPTION"]));
                }
                */

                // Delete
                SqliteCommand deleteCommand = new SqliteCommand();
                deleteCommand.Connection = connection;
                deleteCommand.CommandText = "DELETE FROM mytable WHERE NAME=@NAME";

                deleteCommand.Parameters.Add("@NAME", SqliteType.Text, 50);
                deleteCommand.Parameters[0].Value = nameValue;

                affected = deleteCommand.ExecuteNonQuery();
                Console.WriteLine("# of affected row: " + affected);
            }
        }

        private static void CreateTableIfNotExists(SqliteConnection conn)
        {
            string sql = "create table if not exists mytable(id int, NAME varchar(50), age int, DESCRIPTION varchar(150))";

            using (SqliteCommand command = new SqliteCommand(sql, conn))
            {
                command.ExecuteNonQuery();
            }

            sql = "create index if not exists idx_NAME on mytable(NAME)";
            using (SqliteCommand command = new SqliteCommand(sql, conn))
            {
                command.ExecuteNonQuery();
            }
        }
    }
}

단지 주요 차이점이라면 다음과 같은 정도가 됩니다.

  • 파일 기반
  • DataAdapter의 미구현
  • 타입명이 각각 SqliteConnection, SqliteCommand라는 것
  • SqliteType { Integer = 1, Real, Text, Blob } 4가지 필드 타입만 제공

(첨부 파일은 이 글의 예제 코드를 포함합니다.)




그 외에 유의할 사항으로는, 대량 데이터 INSERT 시 속도가 (대단히) 느리다는 점입니다. 이에 대해서는 다음의 글에도 나오지만,

SQLite is creating a transaction for each insert
; https://stackoverflow.com/questions/3852068/sqlite-insert-very-slow

다중 실행 시 "being", "end" 명령어로 명시적인 트랜잭션을 취하면 됩니다. 따라서, ADO.NET의 전통적인 방식에 따라 SqliteTransaction 타입을 이용해 처리하거나,

using (SqliteTransaction mytransaction = connection.BeginTransaction())
{
    insertCommand.Transaction = mytransaction;
    insertCommand.Connection = connection;

    for (int i = 0; i < 100; i++)
    {
        string nameValue = "Name" + Guid.NewGuid().ToString();
        insertCommand.Parameters[0].Value = (int)DateTime.Now.Ticks;
        insertCommand.Parameters[1].Value = nameValue;
        insertCommand.Parameters[2].Value = 10;
        insertCommand.Parameters[3].Value = nameValue + "_Description";

        int affected = insertCommand.ExecuteNonQuery();
        Console.WriteLine("# of affected row: " + affected);
    }

    mytransaction.Commit();
}

SqliteTransaction을 사용하지 않고 직접 "begin", "end" 명령어를 사용해도 무방합니다. 아래는 해당 명령어를 이용한 래퍼 클래스의 사용법을 보여줍니다.

using (SqliteBulk bulk = new SqliteBulk(connection))
{
    for (int i = 0; i < 100; i++)
    {
        string nameValue = "Name" + Guid.NewGuid().ToString();
        insertCommand.Parameters[0].Value = (int)DateTime.Now.Ticks;
        insertCommand.Parameters[1].Value = nameValue;
        insertCommand.Parameters[2].Value = 10;
        insertCommand.Parameters[3].Value = nameValue + "_Description";

        int affected = insertCommand.ExecuteNonQuery();
        Console.WriteLine("# of affected row: " + affected);
    }
}

public sealed class SqliteBulk : IDisposable
{
    SqliteConnection _connection;

    public SqliteBulk(SqliteConnection connection)
    {
        _connection = connection;
        new SqliteCommand("begin", connection).ExecuteNonQuery();
    }

    public void Dispose()
    {
        new SqliteCommand("end", _connection).ExecuteNonQuery();
    }
}




참고로, sqlite 파일의 내부를 보려면 별도의 도구를 다운로드해야 합니다.

DB Browser for SQLite
; https://sqlitebrowser.org/dl/

또한, Microsoft.Data.SQLite는 내부적으로 SQLitePCL을 라이선스해 사용하는 것으로 보이는데, 이에 대한 소스 코드도 다음의 사이트에 공개되어 있습니다.

ericsink/SQLitePCL.raw
    - A Portable Class Library (PCL) for low-level (raw) access to SQLite
; https://github.com/ericsink/SQLitePCL.raw

그 외에 다른 글을 찾아보면,

C#으로 SQLite 다루기
; http://www.gisdeveloper.co.kr/?p=2290

SQLite 공식 사이트에서도 배포하는 패키지가 있다고 하니, Microsoft.Data.SQLite 대신 그것을 사용해도 무방할 것입니다. (그나저나... 공식 사이트에서 배포한 버전이 좋을까요? Microsoft가 배포한 버전이 좋을까요? ^^)




예외 상황 하나 정리해 보면, SqliteTransaction 트랜잭션 사용 시 SqliteCommand의 Transaction 속성에 명시적으로 트랜잭션 인스턴스를 설정하지 않으면,

// insertCommand.Transaction = mytransaction;

다음과 같은 식의 예외가 발생합니다.

Unhandled Exception: System.InvalidOperationException: Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at ConsoleApp1.Program.Main(String[] args) in C:\ConsoleApp1\ConsoleApp1\Program.cs:line 57




[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]

[연관 글]





[최초 등록일: ]
[최종 수정일: 2/9/2020 ]

Creative Commons License
이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.
by SeongTae Jeong, mailto:techsharer@outlook.com

비밀번호

댓글 쓴 사람
 



2020-05-16 12시06분
[gwise] Microsoft.Data.Sqlite vs System.Data.SQLite 2개중에 어느게 좋은가요? 궁금합니다.
[손님]
2020-05-16 12시25분
그러게요... ^^ 어느게 좋을지 저도 모르겠군요. (gwise님의 블로그에 테스트 해보시고 정리한 글을 올리시면 어떨까요? ^^)
정성태
2020-05-16 02시18분
[gwise] Prepare()이 System.Data.SQLite는 안되어 있고 Microsoft.Data.Sqlite는 구현되어 있는거 같습니다.
그리고 저장/조회 속도면에서 Microsoft.Data.Sqlite가 조금 더 빠르긴 하나 이건 사용자가 느낄 정도는 아니라서..
저는 .net 4.5를 사용해야 해서 System.Data.SQLite를 사용하는데 이 조건이 아니라면 Microsoft.Data.Sqlite이 좋아 보입니다.
[손님]

1  2  [3]  4  5  6  7  8  9  10  11  12  13  14  15  ...
NoWriterDateCnt.TitleFile(s)
12168정성태3/8/2020369개발 환경 구성: 472. 윈도우 환경에서의 dockerd.exe("Docker Engine" 서비스)가 Linux의 것과 다른 점
12167정성태3/5/2020197개발 환경 구성: 471. C# - 닷넷 응용 프로그램에서 DB2 Express-C 데이터베이스 사용 (3) - ibmcom/db2express-c 컨테이너 사용
12166정성태3/14/2020245개발 환경 구성: 470. Windows Server 컨테이너 - DockerMsftProvider 모듈을 이용한 docker 설치
12165정성태3/2/2020253.NET Framework: 900. 실행 시에 메서드 가로채기 - CLR Injection: Runtime Method Replacer 개선 - 세 번째 이야기(Monitor.Enter 후킹)파일 다운로드1
12164정성태2/29/2020202오류 유형: 598. Surface Pro 6 - Windows Hello Face Software Device가 인식이 안 되는 문제
12163정성태2/27/2020284.NET Framework: 899. 익명 함수를 가리키는 delegate 필드에 대한 직렬화 문제
12162정성태2/28/2020333디버깅 기술: 166. C#에서 만든 COM 객체를 C/C++로 P/Invoke Interop 시 메모리 누수(Memory Leak) 발생파일 다운로드2
12161정성태2/26/2020158오류 유형: 597. manifest - The value "x64" of attribute "processorArchitecture" in element "assemblyIdentity" is invalid.
12160정성태2/26/2020219개발 환경 구성: 469. Reg-free COM 개체 사용을 위한 manifest 파일 생성 도구 - COMRegFreeManifest
12159정성태2/26/2020156오류 유형: 596. Visual Studio - The project needs to include ATL support
12158정성태2/26/2020240디버깅 기술: 165. C# - Marshal.GetIUnknownForObject/GetIDispatchForObject 사용 시 메모리 누수(Memory Leak) 발생파일 다운로드1
12157정성태2/27/2020238디버깅 기술: 164. C# - Marshal.GetNativeVariantForObject 사용 시 메모리 누수(Memory Leak) 발생 및 해결 방법파일 다운로드1
12156정성태2/25/2020179오류 유형: 595. LINK : warning LNK4098: defaultlib 'nafxcw.lib' conflicts with use of other libs; use /NODEFAULTLIB:library
12155정성태2/25/2020198오류 유형: 594. Warning NU1701 - This package may not be fully compatible with your project
12154정성태2/25/2020174오류 유형: 593. warning LNK4070: /OUT:... directive in .EXP differs from output filename
12153정성태2/23/2020255.NET Framework: 898. Trampoline을 이용한 후킹의 한계파일 다운로드1
12152정성태2/23/2020238.NET Framework: 897. 실행 시에 메서드 가로채기 - CLR Injection: Runtime Method Replacer 개선 - 세 번째 이야기(Trampoline 후킹)파일 다운로드1
12151정성태2/23/2020268.NET Framework: 896. C# - Win32 API를 Trampoline 기법을 이용해 C# 메서드로 가로채는 방법 - 두 번째 이야기 (원본 함수 호출)파일 다운로드1
12150정성태2/23/2020275.NET Framework: 895. C# - Win32 API를 Trampoline 기법을 이용해 C# 메서드로 가로채는 방법파일 다운로드1
12149정성태2/20/2020272.NET Framework: 894. eBEST C# XingAPI 래퍼 - 연속 조회 처리 방법
12148정성태2/23/2020347디버깅 기술: 163. x64 환경에서 구현하는 다양한 Trampoline 기법
12147정성태2/27/2020313디버깅 기술: 162. x86/x64의 기계어 코드 최대 길이
12146정성태2/18/2020325.NET Framework: 893. eBEST C# XingAPI 래퍼 - 로그인 처리파일 다운로드1
12145정성태2/18/2020269.NET Framework: 892. eBEST C# XingAPI 래퍼 - Sqlite 지원 추가파일 다운로드1
12144정성태2/23/2020328.NET Framework: 891. 실행 시에 메서드 가로채기 - CLR Injection: Runtime Method Replacer 개선 - 두 번째 이야기파일 다운로드1
12143정성태2/13/2020226.NET Framework: 890. 상황별 GetFunctionPointer 반환값 정리 - x64파일 다운로드1
1  2  [3]  4  5  6  7  8  9  10  11  12  13  14  15  ...