C# SQLite Data Type 으로의 변환 및 구조 분석

C# 에서 SQLite DB를 연동하려고 하니 Data Type 을 잘 몰라서 생소하다.

그래서 SQLite 로 DB를 변환해주는 툴(SQLiteConverter)을 이용하여 MySQL 테이블 구조와 데이터를 변환해봤다.

아래 테이블 구조는 구글링하다가 발견했던 자료(http://www.codeproject.com/Articles/71346/Connecting-to-MySQL-Database-using-C-and-NET)를 기준으로 기능 파악을 위해 이것저것 연습해보는 자료다.

이 자료가 100% 만족스런 결과를 가져다 주지는 못한다. 이 자료를 기반으로 응용해서 만들 때 Update, Insert 등을 해보면서 스스로 파악하면 된다.

MySQL 테이블 구조

CREATE TABLE IF NOT EXISTS items (
  uid int(11) NOT NULL AUTO_INCREMENT,
  ItemName varchar(100) NOT NULL,
  Price double NOT NULL,
  Quantity int(11) NOT NULL,
  d_regis datetime NOT NULL,
  PRIMARY KEY (uid)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

SQLite 테이블 구조

CREATE TABLE [items] (
'uid' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
'ItemName' TEXT NOT NULL,
'Price' REAL NOT NULL,
'Quantity' INTEGER NOT NULL,
'd_regis' DATETIME NOT NULL);

로 SQLite Expert 프로그램에서 확인할 수 있다.

보는 바와 같이 데이터타입(Data Type) 구조가 다르다.

http://www.sqlite.org/datatype3.html 에 가면 Data Type 구조에 대한 설명이 나온다.

NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.

SQLite 는 데이터베이스의 모든 정보가 하나의 파일에 저장된다.

테이블 스키마, 레코드 데이터, 인덱스와 같은 모든 정보가 한 파일에 저장된다.

파일 하나로 동작하기 때문에 다중 프로세스나 다중 스레드로 동작하는 경우 파일 잠금 이슈가 발생할 수 있다.

SQLite3 는 테이블 이름과 칼럼 이름에 대해 대소문자를 구별하지 않는다.

SQLite 는 동적 타이핑(dynamic typing)을 사용한다. 데이터 타입 제약을 강제하지 않는다.
어떤 데이터도 아무 컬럼에나 들어갈 수 있다.

대략적인 구조파악이 되었다면 이제 C# 과 SQLite 간에 연동을 위한 구조를 파악해야 한다.

SQLite Expert 에서 확인해보면 테이블 size 에 길이 제한이 없다.

아래 코드는 dataGridView1 에서 데이터를 Update, Insert 하는 코드인데, MySQL 기준으로 동작하던 걸 약간 수정해서 사용해보니 에러가 발생해서

adapter.UpdateCommand.Parameters.Add("@ItemName", DbType.String, 100, "ItemName");

와 같은 데이터 타입을 맞춰야 SQLite에 맞게 수정해야 한다.

http://www.blogbus.com/hyangl-logs/2219450.html 에 나온 SQLite ADO.NET wrapper 를 참조하여 적정하게 매핑처리하면 된다. 테이블의 사이즈가 없으니까 DbType.Int16 을 사용하던, DbType.Int32 를 사용하던 상관이 없을거 같다.

datetime 을 MySQL 에서는 now() 로 하면 되었지만, SQLite 에서는 에러가 발생한다.

datetime('now','localtime') 로 localtime 까지 해줘야만 한국시간으로 정확하게 표시가 된다.

private void btnSave_Click(object sender, EventArgs e)
{
    try
    {
        sqliteConn = new SQLiteConnection(ConnectionString);
        sqliteConn.Open();

        // Set the UPDATE command and parameters.
        adapter.UpdateCommand = new SQLiteCommand(
            "UPDATE items SET ItemName=@ItemName, Price=@Price, Quantity=@Quantity, d_regis=datetime('now','localtime') WHERE uid=@uid;", sqliteConn);
        adapter.UpdateCommand.Parameters.Add("@uid", DbType.Int16, 4, "uid");
        adapter.UpdateCommand.Parameters.Add("@ItemName", DbType.String, 100, "ItemName");
        adapter.UpdateCommand.Parameters.Add("@Price", DbType.Decimal, 10, "Price");
        adapter.UpdateCommand.Parameters.Add("@Quantity", DbType.Int16, 11, "Quantity");
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        // Set the INSERT command and parameter.
        adapter.InsertCommand = new SQLiteCommand(
            "INSERT INTO items VALUES (@uid,@ItemName,@Price,@Quantity,datetime('now','localtime'));",
            sqliteConn);
        adapter.InsertCommand.Parameters.Add("@uid", DbType.Int16, 4, "uid");
        adapter.InsertCommand.Parameters.Add("@ItemName", DbType.String, 100, "ItemName");
        adapter.InsertCommand.Parameters.Add("@Price", DbType.Decimal, 10, "Price");
        adapter.InsertCommand.Parameters.Add("@Quantity", DbType.Int16, 11, "Quantity");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        adapter.Update(ds);
        ds = GetSqliteAllItems();
        MessageBox.Show("성공적으로 저장되었습니다...");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

MySQL 연동과 다르게 에러가 발생하는 부분이 좀 있어서 기능을 하나 하나 테스트하면서 수정해보고 있다.

SQLite 특징 및 분석 추천 사이트

http://crystalcube.co.kr/89

http://blog.naver.com/kai4th/20179602367

http://sqlite.org/faq.html#q1