J & J - 정성태의 닷넷 이야기

사용자

메뉴

최근 덧글

[orion] 글 읽고 찾아보니 디자인 타임에는 InitializeCompon...[orion] 연휴 전에 재현 프로젝트 올리자 생각해 놓고 여의치 않아서 못 ...[정성태] 아래의 글에 정리했으니 참고하세요. C# - Typed D...[정성태] 간단한 재현 프로젝트라도 있을까요? 저런 식으로 설명만 해...[정성태] 저도 해 본 것은 아니지만 어차피 in-process로 pyth...[정성태] Full Text Search With ElasticSearch...[정성태] When I define a window class with n...[정성태] What is the process by which the cu...[정성태] Why doesn't the clock in the taskba...[정성태] 오히려 그 글을 읽지 않았다고 생각하시는 것이 편합니다. (기억...

글쓰기

제목

이름

암호

전자우편

HTML

홈페이지

유형

내용

<div style='display: inline'>
<h1 style='font-family: Malgun Gothic, Consolas; font-size: 20pt; color: #006699; text-align: center; font-weight: bold'>C# - 닷넷 응용 프로그램에서 SQLite 사용</h1>

요즘엔, 웬만한 건 모두 NuGet에 다 들어 있으니... 검색을 해보면 가장 상단에 sqlite가 나옵니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
sqlite 3.13.0
; <a target='tab' href='https://www.nuget.org/packages/sqlite/'>https://www.nuget.org/packages/sqlite/</a>
</pre>
 
아쉽게도 저건 C/C++ 사용자를 위한 native DLL을 담고 있을 뿐 닷넷 개발자에게는 Microsoft.Data.Sqlite가 의미 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
[닷넷 프레임워크]
Install-Package Microsoft.Data.SQLite -Version 3.1.1
; <a target='tab' href='https://www.nuget.org/packages/Microsoft.Data.Sqlite/'>https://www.nuget.org/packages/Microsoft.Data.Sqlite/</a>

[닷넷 코어]
Install-Package Microsoft.Data.Sqlite.Core -Version 3.1.1
; <a target='tab' href='https://www.nuget.org/packages/Microsoft.Data.Sqlite.Core/'>https://www.nuget.org/packages/Microsoft.Data.Sqlite.Core/</a>
</pre>
 
현재(2020-02-09) 3.1.1 기준으로 .NET Standard 2.0을 요구하기 때문에 .NET Framework의 경우 4.6.1 이상의 프로젝트에서 참조 추가할 수 있습니다. 사용법은, System.Data의 명세를 따른 만큼 일전에 소개한 다른 DB들과, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
무료 데이터베이스 서버 성능 비교(SQL Server Express, IBM DB2 Express, MySQL, Sybase, PostgreSQL, Oracle XE)
; <a target='tab' href='https://www.sysnet.pe.kr/2/0/1411'>https://www.sysnet.pe.kr/2/0/1411</a>
</pre>
 
코드 상으로 거의 유사합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
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();
 }
 }
 }
}
</pre>
 
단지 주요 차이점이라면 다음과 같은 정도가 됩니다. 
 
<ul>
<li>파일 기반</li>
<li>DataAdapter의 미구현</li>
<li>타입명이 각각 SqliteConnection, SqliteCommand라는 것</li>
<li>SqliteType { Integer = 1, Real, Text, Blob } 4가지 필드 타입만 제공</li>
</ul> 
 
(<a target='tab' href='https://www.sysnet.pe.kr/bbs/DownloadAttachment.aspx?fid=1548&boardid=331301885'>첨부 파일은 이 글의 예제 코드를 포함</a>합니다.) 
 
<hr style='width: 50%' /> 
 
그 외에 유의할 사항으로는, 대량 데이터 INSERT 시 속도가 (대단히) 느리다는 점입니다. 이에 대해서는 다음의 글에도 나오지만, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
SQLite is creating a transaction for each insert
; <a target='tab' href='https://stackoverflow.com/questions/3852068/sqlite-insert-very-slow'>https://stackoverflow.com/questions/3852068/sqlite-insert-very-slow</a>
</pre>
 
다중 실행 시 "being", "end" 명령어로 명시적인 트랜잭션을 취하면 됩니다. 따라서, ADO.NET의 전통적인 방식에 따라 SqliteTransaction 타입을 이용해 처리하거나, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
using (SqliteTransaction mytransaction = connection.BeginTransaction())
{
 insertCommand.Transaction = mytransaction;
 insertCommand.Connection = connection;

for (int i = 0; i &lt; 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();
}
</pre>
 
SqliteTransaction을 사용하지 않고 직접 "begin", "end" 명령어를 사용해도 무방합니다. 아래는 해당 명령어를 이용한 래퍼 클래스의 사용법을 보여줍니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
using (SqliteBulk bulk = new SqliteBulk(connection))
{
 for (int i = 0; i &lt; 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();
 }
}
</pre>
 
<hr style='width: 50%' /> 
 
참고로, sqlite 파일의 내부를 보려면 별도의 도구를 다운로드해야 합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
DB Browser for SQLite
; <a target='tab' href='https://sqlitebrowser.org/dl/'>https://sqlitebrowser.org/dl/</a>
</pre>
 
또한, Microsoft.Data.SQLite는 내부적으로 SQLitePCL을 라이선스해 사용하는 것으로 보이는데, 이에 대한 소스 코드도 다음의 사이트에 공개되어 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
ericsink/SQLitePCL.raw
 - A Portable Class Library (PCL) for low-level (raw) access to SQLite
; <a target='tab' href='https://github.com/ericsink/SQLitePCL.raw'>https://github.com/ericsink/SQLitePCL.raw</a>
</pre>
 
그 외에 다른 글을 찾아보면, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
C#으로 SQLite 다루기
; <a target='tab' href='http://www.gisdeveloper.co.kr/?p=2290'>http://www.gisdeveloper.co.kr/?p=2290</a>
</pre>
 
SQLite 공식 사이트에서도 배포하는 패키지가 있다고 하니, Microsoft.Data.SQLite 대신 그것을 사용해도 무방할 것입니다. (그나저나... 공식 사이트에서 배포한 버전이 좋을까요? Microsoft가 배포한 버전이 좋을까요? ^^) 
 
<hr style='width: 50%' /> 
 
예외 상황 하나 정리해 보면, SqliteTransaction 트랜잭션 사용 시 SqliteCommand의 Transaction 속성에 명시적으로 트랜잭션 인스턴스를 설정하지 않으면, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
// insertCommand.Transaction = mytransaction;
</pre>
 
다음과 같은 식의 예외가 발생합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
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
</pre>

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

</div>

첨부파일

스팸 방지용 인증 번호

1157 (왼쪽의 숫자를 입력해야 합니다.)