visual studio 2008 + windows mobile 에서 SQLite 사용

0. 참고

 다운로드 : http://sqlite.phxsoftware.com/

 SQLite.dll : http://www.sqlite.org/

 

1. 세팅

System.Data.SQLite.dll 참조하고,

배포시에 SQLite.Interop.<version?>.dll 을 수동 배포해준다.

windows mobile 에서 사용할경우(특히 emulator)

SQLite.Interop.dll은 자동 배포되지 않으므로,

"visual studio remote tool - remote file viewer"

를 통해 SQLite.Interop.dll을 복사해준다.

 

2. 사용샘플코드

   1: using System.Reflection;

   2: using System.Diagnostics;

   3: using System.IO;

   4: using System.Data.SQLite;

   5:  

   6: 

   7:     public class Store {

   8:  

   9:         public Store() {

  10:             this.Install();

  11:         }

  12:  

  13:         string FileName{

  14:             get {

  15:                 string root = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName);

  16:                 string fileName = Path.Combine(root, "tet.s3db");

  17:                 return fileName;

  18:             }            

  19:         }

  20:  

  21:         void Install() {

  22:             if (!File.Exists(this.FileName)) {

  23:                 SQLiteConnection.CreateFile(this.FileName);

  24:                 string sql = "create table log(userId varchar(30) , latitude float, longitude float, altitude float , speed float , regdate date);";

  25:                 using (SQLiteConnection con = new SQLiteConnection(string.Format("Data Source={0}", this.FileName))) {

  26:  

  27:                     using (SQLiteCommand cmd = new SQLiteCommand(con)) {

  28:                         cmd.CommandText = sql;

  29:  

  30:                         con.Open();

  31:                         cmd.ExecuteNonQuery();

  32:                         con.Close();

  33:                     }

  34:                 }

  35:             }

  36:         }

  37:  

  38:         public void Add(string userId, double latitude, double longitude, double altitude , double speed) {

  39:             string sql = string.Format(

  40:                 "insert into log(userId ,latitude , longitude , altitude , speed , regdate) values('{0}',{1},{2},{3},{4},datetime('now'))"

  41:                 , userId, latitude, longitude, altitude, speed);

  42:             using (SQLiteConnection con = new SQLiteConnection(string.Format("Data Source={0}", this.FileName))) {

  43:                 using (SQLiteCommand cmd = new SQLiteCommand(con)) {

  44:                     cmd.CommandText = sql;

  45:                     con.Open();

  46:                     cmd.ExecuteNonQuery();

  47:                     con.Close();

  48:                 }

  49:             }                        

  50:         }