SQLite는 MySQL나 PostgreSQL와 같은 데이터베이스 관리 시스템이지만, 서버가 아니라 응용 프로그램에 넣어 사용하는 비교적 가벼운 데이터베이스이다. 
일반적인 RDBMS에 비해 대규모 작업에는 적합하지 않지만, 중소 규모라면 속도에 손색이 없다. 또 API는 단순히 라이브러리를 호출하는 것만 있으며, 데이터를 저장하는 데 하나의 파일만을 사용하는 것이 특징이다. 버전 3.3.8에서는 풀텍스트 검색 기능을 가진 FTS1 모듈이 지원된다. 컬럼을 삭제하거나 변경하는 것 등이 제한된다.
구글 안드로이드 운영 체제에 기본 탑재된 데이터베이스이기도 하다.

RDBMS( oracle, mssql, sysbase, mysql, mariadb 등)와 동일하게 ansi sql을 사용할 수 있다.
다른 RDBMS와의 가장 큰 차이점은 아래와 같다.
데이터베이스별로 파일1개로 구성된다.
로컬에 파일로 저장하므로 원격접속이 안된다. 
( 웹서버와 DB서버를 별도로 구성 X, 다른 대안은 찾아보면 있다. 다른아이피의 PC를 마운트 해서 사용한다던가..등)
접속아이디나 패스워드가 없다.
다중 접속이 많아질 경우 예기치 않은 문제가 발생할 수 있다.
구글 안드로이드 운영 체제에 기본 탑재되어 있어 앱을 만들어본 경험이 있다면, 누구나 한번쯤은 사용해보고 들어봤을 것이다.
윈도우PC에서도 설치하여 사용할 수 있으며, 간단한 웹페이지를 구현할때도 별도의 설치과정이 없이 실행파일만 있으면 바로 사용할 수 있다.


1. SQLite 다운로드
http://www.sqlite.org/download.html 에 접속하여 sqllite-tools-win32-3340000.zip 파일을 다운로드 받는다.

2. 압축해제 후 sqlite3.exe 실행
적당한 곳(ex. C:\SQLite)에 압축을 푼 다음 sqlite.exe파일을 실행한다.
간혹 아래와 같이 Windows의 PC 보호라고 알림 창이 뜰 경우 "추가 정보"를 클릭 후 나타나는 "실행" 버튼을 클릭하면 된다.

3. sqlite 명령프롬프트
실행하면 윈도우 명령 프롬프트가 실행되면서 sqlite 상태로 들어간다.

5. 데이터베이스 생성
실행했으니 데이터베이스를 하나 만들고 테이블도 하나 만들어서 값을 입력하고 결과를 조회해보자

데이터베이스를 만드는 법은 2가지가 있는데, 명령 프롬프트에서 sqlite3를 실행하면서 만드는 방법과 sqlite3 안에 들어가서 만드는 방법이 있다.
방법1. sqlite3 실행과 동시에 만들기

6. 생성된 데이터베이스에 테이블 만들기
일반적인 DDL 문법으로 TEST_TABLE 이라는 테이블을 만들고 .table 명령으로 테이블 목록을 조회

7. 생성된 테이블에 데이터 넣고 조회 해보기

8. GUI 툴을 사용하여 편하게 입력한다.
  sqlitebrowser.org/dl/


개인작업내용)
SQLite를 사용하여 C#에서 테이블을 생성하고 데이터를 입력 및 조회하는 예제를 아래에 제공하겠습니다. 이 예제에서는 System.Data.SQLite를 사용합니다.

먼저, NuGet 패키지 관리자를 통해 System.Data.SQLite 패키지를 설치해야 합니다.
먼저, NuGet 패키지 관리자를 통해 System.Data.SQLite 패키지를 설치해야 합니다.

BUILD & ALL
$ find ./ -name "*Interop*.dll"
./frm/WinFrmTicker/packages/Microsoft.Office.Interop.Excel.15.0.4795.1001/lib/net20/Microsoft.Office.Interop.Excel.dll
./frm/WinFrmTicker/packages/Microsoft.Office.Interop.Excel.15.0.4795.1001/lib/netstandard2.0/Microsoft.Office.Interop.Excel.dll
./frm/WinFrmTicker/packages/MSOffice.Interop.16.0.55555/lib/Microsoft.Office.Interop.Access.Dao.dll
./frm/WinFrmTicker/packages/MSOffice.Interop.16.0.55555/lib/Microsoft.Office.Interop.Access.dll
./frm/WinFrmTicker/packages/MSOffice.Interop.16.0.55555/lib/Microsoft.Office.Interop.Excel.dll
./frm/WinFrmTicker/packages/MSOffice.Interop.16.0.55555/lib/Microsoft.Office.Interop.Visio.WorkflowAuthoring.dll
./frm/WinFrmTicker/WinFrmTicker/bin/Debug/Microsoft.Office.Interop.Visio.WorkflowAuthoring.dll
./frm/WinFrmTicker/WinFrmTicker/bin/Release/Microsoft.Office.Interop.Visio.WorkflowAuthoring.dll
./mdifrm/packages/Stub.Systehttp://m.Data.SQLite.Core.NetFramework.1.0.118.0/build/net20/x64/SQLite.Interop.dll
./mdifrm/packages/Stub.Systehttp://m.Data.SQLite.Core.NetFramework.1.0.118.0/build/net20/x86/SQLite.Interop.dll

dotnet40에서, 64비트)
dotnet40에서, 64비트)
./mdifrm/packages/Stub.Systehttp://m.Data.SQLite.Core.NetFramework.1.0.118.0/build/net40/x64/SQLite.Interop.dll

./mdifrm/packages/Stub.Systehttp://m.Data.SQLite.Core.NetFramework.1.0.118.0/build/net40/x86/SQLite.Interop.dll

dotnet45에서 , 64비트)
dotnet45에서 , 64비트)
./mdifrm/packages/Stub.Systehttp://m.Data.SQLite.Core.NetFramework.1.0.118.0/build/net45/x64/SQLite.Interop.dll


./mdifrm/packages/Stub.Systehttp://m.Data.SQLite.Core.NetFramework.1.0.118.0/build/net45/x86/SQLite.Interop.dll
./mdifrm/packages/Stub.Systehttp://m.Data.SQLite.Core.NetFramework.1.0.118.0/build/net451/x64/SQLite.Interop.dll
./mdifrm/packages/Stub.Systehttp://m.Data.SQLite.Core.NetFramework.1.0.118.0/build/net451/x86/SQLite.Interop.dll
./mdifrm/packages/Stub.Systehttp://m.Data.SQLite.Core.NetFramework.1.0.118.0/build/net46/x64/SQLite.Interop.dll
./mdifrm/packages/Stub.Systehttp://m.Data.SQLite.Core.NetFramework.1.0.118.0/build/net46/x86/SQLite.Interop.dll
./mdifrm/WINCalcMdi/bin/Release/x64/SQLite.Interop.dll
./mdifrm/WINCalcMdi/bin/Release/x86/SQLite.Interop.dll



1. SQLite 데이터베이스 및 테이블 생성

using System;
using Systehttp://m.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=sample.db;Version=3;";

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

            string createTableQuery = @"
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    age INTEGER NOT NULL
                )";

            using (SQLiteCommand command = new SQLiteCommand(createTableQuery, connection))
            {
                command.ExecuteNonQuery();
            }

            connection.Close();
        }

        Console.WriteLine("Table created successfully.");
    }
}

2. 데이터 입력

using System;
using Systehttp://m.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=sample.db;Version=3;";

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

            string insertQuery = "INSERT INTO users (name, age) VALUES (@name, @age)";

            using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
            {
                command.Parameters.AddWithValue("@name", "Alice");
                command.Parameters.AddWithValue("@age", 30);
                command.ExecuteNonQuery();
            }

            connection.Close();
        }

        Console.WriteLine("Data inserted successfully.");
    }
}

3. 데이터 조회

using System;
using Systehttp://m.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=sample.db;Version=3;";

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

            string selectQuery = "SELECT * FROM users";

            using (SQLiteCommand command = new SQLiteCommand(selectQuery, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("ID: " + reader["id"]);
                        Console.WriteLine("Name: " + reader["name"]);
                        Console.WriteLine("Age: " + reader["age"]);
                        Console.WriteLine();
                    }
                }
            }

            connection.Close();
        }
    }
}

전체 코드 예제

using System;
using Systehttp://m.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=sample.db;Version=3;";

        // 테이블 생성
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();

            string createTableQuery = @"
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    age INTEGER NOT NULL
                )";

            using (SQLiteCommand command = new SQLiteCommand(createTableQuery, connection))
            {
                command.ExecuteNonQuery();
            }

            connection.Close();
        }

        Console.WriteLine("Table created successfully.");

        // 데이터 입력
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();

            string insertQuery = "INSERT INTO users (name, age) VALUES (@name, @age)";

            using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
            {
                command.Parameters.AddWithValue("@name", "Alice");
                command.Parameters.AddWithValue("@age", 30);
                command.ExecuteNonQuery();
            }

            connection.Close();
        }

        Console.WriteLine("Data inserted successfully.");

        // 데이터 조회
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();

            string selectQuery = "SELECT * FROM users";

            using (SQLiteCommand command = new SQLiteCommand(selectQuery, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("ID: " + reader["id"]);
                        Console.WriteLine("Name: " + reader["name"]);
                        Console.WriteLine("Age: " + reader["age"]);
                        Console.WriteLine();
                    }
                }
            }

            connection.Close();
        }
    }
}

이 코드는 SQLite 데이터베이스를 생성하고, 테이블을 생성한 후, 데이터를 입력하고 조회하는 예제입니다. SQLite 데이터베이스 파일(sample.db)이 실행 디렉토리에 생성됩니다.


저작자표시

'c# 언어 > 중급과정' 카테고리의 다른 글

정보분배수신/(동기식)HttpListener Interface/SQLite DB저장(file)  (0) 2024.07.24
(System.Data.SQLite)를 활용한 TCP정보분배 데이타 수신및 저장  (0) 2024.07.23
(정보분배)TCP로 받은 증권데이타를 파싱해서 DataGridView에 표시해봅니다.  (0) 2024.07.12
금리별.원리금균등상환.계산.시뮬/  (0) 2024.06.20
c# > windows.pre.foundation > 원리금균등상환.금리비교시.대출상환포함  (0) 2024.06.12

+ Recent posts

티스토리툴바