[DB] c#으로 SQLite 연결하기

SQLite는 EmbededDB로써 Application내에서 In-process로 동작하는 DataBase입니다. OpenSource로써 무료로 이용할 수 있으며, 구글, MS, Apple 등 광범위하게 이용되고 있는 검증된 Database입니다.

iOS App, Android, Java, C++, C# 등 다양한 환경에서 동작하는데 여기서는 .NET환경에서 ADO.NET, LINQ to SQL, EntityFramework6을 통해 사용하는 방법에 대해 설명합니다.

SQLite Database 생성하기

1. SQLite DB의 생성 및 관리를 도와주는 GUI 도구는 SQLite 공식 홈페이지에서 제공합니다. 공식 홈페이지(http://system.data.sqlite.org/)로 이동하여 Download 링크를 클릭합니다.
2. ‘Visual Studio’ 텍스트를 검색하여 다음과 같이 자신에게 맞는 버전을 설치합니다. 반드시 32bit 버전을 설치해야 SQLite 디자인도구가 설치되므로, 반드시 32bit 버전을 설치합니다.  여기서는 Setups for 32-bit Windows (.NET Framework 4.5) for Visual Studio 2012를 설치하였습니다.
   
3. 설치 후 Visual Studio를 재실행하고 프로젝트를 다시 엽니다.
4. Server Explorer를 열고 ‘Add Connection’을 진행합니다.
   
5. Data source:의 Change버튼을 클릭하여, 다음과 같이 System.Data.SQLite Database File을 선택합니다.
   
6. New 버튼을 클릭하여 DB를 생성하고 OK버튼을 클릭합니다.
   
   ※ 참고: SQLite DB파일의 확장자 형식에는 제한이 없습니다. 보통 다음에서 많이 사용됩니다.
   • db: 일반적으로 사용되는 확장자 입니다
   • sdb: SQLite 무료 GUI 도구인 SQLite Administrator에서 사용하는 확장자
   • .sqlite: SQLite Manager에서 사용 
     
7. Table을 추가하여 DB를 구성하실 수 있습니다. 여기서는 다음과 같이 Company Table을 생성해두었습니다. 
   
8. Table 데이터도 편집하실 수 있습니다. 
    

SQLite를 프로젝트에 추가

가장 쉬운 방법은, NuGet Pakcage Manger를 통해 설치하는 것입니다. 프로젝트의 Nuget Package Manager를 열고 ‘System.Data.SQLite’를 설치합니다.

설치가 완료되면 다음과 같이 3가지 Assembly가 추가됩니다.

설치 후 다음과 같은 오류가 발생할 수 있습니다.

'/' 응용 프로그램에 서버 오류가 있습니다.

The Entity Framework provider type 'System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SQLite' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

설명: 현재 웹 요청을 실행하는 동안 처리되지 않은 예외가 발생했습니다. 스택 추적을 검토하여 발생한 오류 및 코드에서 오류가 발생한 위치에 대한 자세한 정보를 확인하십시오.

예외 정보: System.InvalidOperationException: The Entity Framework provider type 'System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SQLite' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

이 경우, 다음과 같이 References에서 System.Data.SQLite.EF6와 System.Data.SQLite.Linq의 Copy Local 속성을 True로 설정해줍니다.

ADO.NET으로 연결하기

System.Data.SQLite는 ADO.NET을 이용하여 SQLite DB를 제어할 수 있는 Class를 제공합니다..

List<string[]> model = new List<string[]>();

string connStr = @"Data Source=D:\Source\Downloader\2015072.db;Pooling=true;FailIfMissing=false";
string sql = "SELECT * FROM Company";
using (SQLiteConnection sqliteConnection = new SQLiteConnection(connStr))
{
    SQLiteCommand sqliteCommand = new SQLiteCommand(sql, sqliteConnection);
    SQLiteDataReader sqliteDataReader = null;
    sqliteConnection.Open();
    try
    {
        sqliteDataReader = sqliteCommand.ExecuteReader(CommandBehavior.CloseConnection);
        while (sqliteDataReader.Read())
        {
            string[] rowValue = new string[] 
            {
                ((long)sqliteDataReader["CompanyId"]).ToString(),
                sqliteDataReader["CompanyName"] as string
            };
            model.Add(rowValue);
        }
    }
    finally
    {
        sqliteDataReader.Close();
    }
}

LINQ to SQL 이용하기

LINQ to SQL을 사용하는 방법은 매우 간단합니다. System.Data.SQLite.dll에 구현된 SQLiteConnection을 DataContext에 넘겨주면 LINQ to SQL을 이용하실 수 있습니다.

string connStr = @"Data Source=D:\Source\Downloader\2015072.db;Pooling=true;FailIfMissing=false";
using (SQLiteConnection conn = new SQLiteConnection(connStr)))
{
  using (DataContext db = new DataContext(conn))
  {
    var companies = db.GetTable<Company>();
    //.. companies 조회
  }
}

GetTable<T>()에 지정된 Company Entity는 다음과 같이 Table, Column Attribute와 함께 생성해주시면 됩니다.

using System.Data.Linq.Mapping;

//Company Entity 정의
[Table(Name = "Company")]
class Company
{
    [Column(Name = "CompanyId")]
    public long CompanyId { get; set; }

    [Column(Name = "CompanyName")]
    public string CompanyName { get; set; }
}

EntityFramework와 함께 이용하기

가장 먼저, Nuget Package Manager를 통해 SQLite를 설치할 때 자동으로 추가된 web.config의 내용을 일부 수정해야 합니다. System.Data.SQLite의 버그로 인해 System.Data.SQLite.EF6가 아닌 System.Data.SQLite를 Provider로 사용하려고 하기 때문에 System.Data.SQLite를 추가해주어야 합니다.

1. <EntityFramework> Providers에 System.Data.SQLite를 추가해줍니다.
   

   <entityFramework>
       <providers>
           <!-- System.Data.SQLite를 추가해 준다 -->
           <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
           <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
       </providers>
   </entityFramework>

2. <DbProviderFactories>으로 이동하여 다음과 같이 System.Data.SQLite를 추가해줍니다.  반드시<remove> Element는 <add> Element 상단에 위치해야 합니다.

   <system.data>
       <DbProviderFactories>
           <remove invariant="System.Data.SQLite" />
           <remove invariant="System.Data.SQLite.EF6" />
           <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
           <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
       </DbProviderFactories>
   </system.data>

다음으로 <ConnectionString>을 추가해줍니다. 추가하실 때 주의사항은 다음과 같습니다.

• System.Data.SQLite의 버그로 인해, ProviderName은 System.Data.SQLite.EF6가 아닌 System.Data.SQLite를 지정해주어야 합니다.
• 경로는 상대경로가 아닌 절대경로를 이용해주세요. 상대경로는 환경에 따라 Temporary ASP.NET Files 폴더나, C:\Program Files (x86)\IIS Express 등을 이용하기도 하기 때문에, 오류를 야기하기도 합니다.
• 여기서 |DataDirectory|는 App_Data 폴더를 가리킵니다.

<configuration>
    <connectionStrings>
        <add name="SqliteDb" connectionString="Data Source=|DataDirectory|2015072.db" providerName="System.Data.SQLite"/>
    </connectionStrings>
</configuration>

이제 DataContext와 Entity를 정의해줍니다.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

//EF DbContext 정의
public class SqliteEFContext : DbContext
{
    //DB생성은 지원하지 않으므로, Database.SetInitializer<SqliteEFContext>(null);을 설정하여 EF가 DB를 생성하지 못하게 한다.
    public SqliteEFContext() : base("SqliteDb") { Database.SetInitializer<SqliteEFContext>(null); }
    public DbSet<Person> Persons { get; set; }
    public DbSet<Company> Companies { get; set; }
}

//Model 정의
[Table("Person")]
public class Person
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("PersonId")]
    public long PersonId { get; set; }

    [Column("PersonName")]
    public string Name { get; set; }

    public long CompanyId { get; set; }
    public virtual Company Company { get; set; }
}
[Table("Company")]
public class Company
{
    public Company() { Persons = new List<Person>(); }

    public long CompanyId { get; set; }
    public string CompanyName { get; set; }

    public virtual ICollection<Person> Persons { get; set; }
}