DHJJ [Hatsune's Journal Japan] blog

Hatsune's Journal Japan blog

目次

Blog 利用状況

ニュース

最新ツイート

運営サイト

Hatsune's Journal Japan
DHJJ

著作など

資格など

OCP
MCP

書庫

日記カテゴリ

わんくま同盟

SQLiteを使ったWindowsアプリを作成する

System.Data.SQLiteはSQLiteと接続するためのクラスライブラリではなく、SQLiteをC#で書きなおしてSQLiteそのものをクラスライブラリ化したものです。よって、プログラムの実行には、System.Data.SQLite.dllがあれば基本的に動作します。

System.Data.SQLiteを使うためのサンプルプログラムを作成し、.NET Framewoekが導入済みでSQLiteは導入していない環境にファイルコピーして動作するかを確認してみましょう。

サンプル用プロジェクトの作成

.NET Framework 2.0のWIndowsアプリケショーンプロジェクトを新規作成します。

参照設定で、System.Data.SQLite.dllを選択します。

image

また、配布の事を考えてコンポーネントをローカルにコピーするように設定します。

image

さらに「SQLite3を使ってみた」で作成したtest.dbファイルもプロジェクトに加えて、[出力ディレクトリにコピー]プロパティを「新しいときはコピーする」に設定します。

image image

これで、プロジェクトの準備はできました。

余談ですが、.NET Frameworkのクラスライブラリの命名から考えると、System.Data.SQLiteではなく、SQLite.Dataのような名前が適切だと思いました(ODP.NETは、Oracle.DataAccess)。

Visual Basic 2008でプログラミングする

System.Data.SQLiteは、ADO.NET 2.0相当のデータアクセスメソッドをもっているので、VB2005でもVB2008でも同じようにプログラミングできます。そこで次のような画面をもつサンプルプログラムを作成して、接続、データ取得、データ更新のコードを確認してみましょう。

image

SQLiteのファイルと接続する

Imports System.Data.SQLite
Public Class SampSQLite
  Private Ds As New DataSet
  Private ConnectionString As String = "data source={0}"

  Private Sub SampSQLite_Shown(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles Me.Shown
    ConnectionString = String.Format(ConnectionString, _
                                     System.IO.Path.Combine(My.Application.Info.DirectoryPath, _
                                                            "test.db"))
  End Sub

  Private Sub Connect_Button_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles Connect_Button.Click
    Cursor.Current = Cursors.WaitCursor
    Using _cn As New SQLiteConnection
      Try
        _cn.ConnectionString = ConnectionString
        _cn.Open()
        MessageBox.Show("Open成功", Me.Text)
      Catch ex As Exception
        MessageBox.Show(ex.Message, Me.Text)
      Finally
        Try
          _cn.Close()
        Catch ex As Exception
        End Try
        Cursor.Current = Cursors.Default
      End Try
    End Using
  End Sub
End Class

System.Data.SQLiteはADO.NET 2.0互換のメンバ(プロパティ、メソッド、イベントなど)を持っています。

例えば、接続ならばSystem.Data.SQLite.SQLiteConnectionクラスのConnectionStringプロパティに接続文字列(SQLiteファイルの絶対パスファイル名)を入れてからOpenメソッドを実行します。クラスこそ独自ですが、ConnectionStringプロパティやOpenメソッドはSQL ServerやOracle Databaseを使うときと同じです。

SQLiteのテーブルから値を取得する

ADO.NETを使ってテーブルから値を取得する方法には、DataReaderを使う方法とDataAdapterを使う方法があります。ここでは、DataAdapterを使う方法をご紹介します。

Public Class SampSQLite
  Private Ds As New DataSet
  Private ConnectionString As String = "data source={0}"

  Private Sub SampSQLite_Shown(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles Me.Shown
    ConnectionString = String.Format(ConnectionString, _
                                     System.IO.Path.Combine(My.Application.Info.DirectoryPath, _
                                                            "test.db"))
  End Sub

  Private Sub GetRecords_Button_Click(ByVal sender As System.Object, _
                                      ByVal e As System.EventArgs) _
                                      Handles GetRecords_Button.Click
    Cursor.Current = Cursors.WaitCursor
    Using _cn As New SQLiteConnection
      Try
        _cn.ConnectionString = ConnectionString
        _cn.Open()
        Using _cmd As New SQLiteCommand("SELECT * FROM EMP", _cn)
          Using _da As New SQLiteDataAdapter(_cmd)
            _da.Fill(Ds, "EMP")
            Me.Result_Grid.DataSource = Ds.Tables("EMP")
          End Using
        End Using
      Catch ex As Exception
        MessageBox.Show(ex.Message, Me.Text)
      Finally
        Try
          _cn.Close()
        Catch ex As Exception
        End Try
        Cursor.Current = Cursors.Default
      End Try
    End Using
  End Sub
End Class

データを習得するためには、以下の手順を実行します。 SQLiteConnectionをOpen SQLiteCommandにSELECT文を設定 SELECT文を設定したSQLiteCommandを指定してSQLiteDataAdapterを生成 SQLiteDataAdatpterのFilllメソッドによりDataSetにレコード設定 DataSetに入ってしまえば、データの取得元は関係なくなりますので、DataGridViewのデータソースとして指定すればテーブルの内容をグリッド表示できます。

SQLiteのテーブルの値を更新する

Imports System.Data.SQLite
Public Class SampSQLite
  Private Ds As New DataSet
  Private ConnectionString As String = "data source={0}"

  Private Sub SampSQLite_Shown(ByVal sender As Object, _
                               ByVal e As System.EventArgs) _
                               Handles Me.Shown
    ConnectionString = String.Format(ConnectionString, _
                                     System.IO.Path.Combine(My.Application.Info.DirectoryPath, _
                                                            "test.db"))
  End Sub

  Private Sub SetRecords_Button_Click(ByVal sender As System.Object, _
                                      ByVal e As System.EventArgs) _
                                      Handles SetRecords_Button.Click
    Cursor.Current = Cursors.WaitCursor
    Using _cn As New SQLiteConnection
      Try
        _cn.ConnectionString = ConnectionString
        _cn.Open()
        Using _tr As SQLiteTransaction = _cn.BeginTransaction
          Try
            Using _cmd As New SQLiteCommand("SELECT * FROM EMP", _cn)
              _cmd.Transaction = _tr      '###重要###
              Using _da As New SQLiteDataAdapter(_cmd)
                Using cb As New SQLiteCommandBuilder(_da)
                  cb.SetAllValues = False
                  cb.ConflictOption = ConflictOption.OverwriteChanges
                  _da.UpdateCommand = cb.GetUpdateCommand()
                  _da.InsertCommand = cb.GetInsertCommand()
                  _da.DeleteCommand = cb.GetDeleteCommand()
                  MessageBox.Show(_da.UpdateCommand.CommandText, Me.Text)
                  MessageBox.Show(_da.InsertCommand.CommandText, Me.Text)
                  MessageBox.Show(_da.DeleteCommand.CommandText, Me.Text)
                  _da.Update(Ds, "EMP")
                  MessageBox.Show("Update", Me.Text)
                End Using
              End Using
            End Using
            _tr.Commit()
          Catch ex As Exception
            MessageBox.Show(ex.Message, Me.Text)
            _tr.Rollback()
          Finally
          End Try
        End Using
      Catch ex As Exception
        MessageBox.Show(ex.Message, Me.Text)
      Finally
        Try
          _cn.Close()
        Catch ex As Exception
        End Try
      End Try
      Cursor.Current = Cursors.Default
    End Using
  End Sub
End Class

画面上での変更は随時DataSetに自動反映されますので、SQLiteのデータ更新はDataSetの値を反映する処理になります。

DataSetからの反映にもSQLiteDataAdapterを使いますが、SELECT文ではなくUPDATE、INSERT、DELETEのSQL文が必要です。重要なのは、反映時にどのSQL文を使うかはSQLiteDataAdapterの内部実装になるので、必ず3つとも設定しておかないと実行時にエラーが発生する危険性があることを忘れないようにしてください。

3つのSQL文はそれぞれ記述してもいいのですが、SQLiteCommandBuilderを使って、SELECT文から自動生成するのが楽です。

SetAllValuesプロパティとConflictOptionプロパティをサンプルコードのように設定すれば次のようなSQL文を生成してくれます。

image

image

image

DataAdapterでの更新にはトランザクションを設定が必須

SQLiteに限らず、ADO.NETのDataAdapterでデータ更新をする場合、かならず明示的にトランザクションを開始して、Updateメソッドが成功したらCommit、失敗したらRollBackする必要があります。

なぜならば、データ更新対象が常に1つになるような画面構成ならば1SQL文の実行ですみますが、グリッド表示のように削除、追加、複数行の変更が行われる可能性がある場合、DataAdapterのUpdateメソッドの内部ではUPDATE、INSERT、DELETEのSQL文を別個に発行しているからです。

これは非常に重要な点です。忘れないようにしましょう。

配布する

今回のサンプルの場合、.NET Framework 2.0が入っている環境であれば、次の3つのファイルをコピーするだけでアプリが稼働しました。

「SampSQLite.exe」「System.Data.SQLite.dll」「test.db」

以上のように、SQLiteを使った.NETアプリはADO.NETの知識があれば簡単につくれますし配布も簡単にできますので、XMLファイルでは心もとない、かといって本格的なRDBMSを入れるほどではないようなときに活用して頂けたらと思います。

投稿日時 : 2008年10月16日 0:19

Feedback

# re: SQLiteを使ったWindowsアプリを作成する 2008/10/16 13:44 とっちゃん

うひゃひゃ格好のネタだ。いただきまーすw

# データベースファイルも配布する場合。。。 2008/10/16 13:45 とっちゃん's Blog

データベースファイルも配布する場合。。。

# [C#][SQLite]System.Data.SQLiteを試してみた 2009/06/14 10:30 かずきのBlog

[C#][SQLite]System.Data.SQLiteを試してみた

# Computer 2010/10/10 22:33 Josh Thomas

[url]http://frederickpctech.com[/url]

# re: SQLiteを使ったWindowsアプリを作成する 2011/10/29 14:05 NICKNAME

Stop hack the program!!!

# re: SQLiteを使ったWindowsアプリを作成する 2011/10/29 14:11 NICKNAME

Stop hack the program!!!

# re: SQLiteを使ったWindowsアプリを作成する 2011/10/29 14:36 NICKNAME

Stop hack the program!!!

# re: SQLiteを使ったWindowsアプリを作成する 2011/10/29 14:43 NICKNAME

Stop hack the program!!!

# SQLite????????????Windows???????????????????????? | TATSUYA.info 2012/08/29 10:44 Pingback/TrackBack

SQLite????????????Windows???????????????????????? | TATSUYA.info

# Cheap Canada Goose 2012/10/19 16:46 http://www.supercoatsale.com

Great website. A lot of helpful information here. I am sending it to a few friends ans also sharing in delicious. And of course, thanks to your effort!

# cheap tie 2012/10/26 4:09 http://www.burberryoutletscarfsale.com/accessories

As soon as I observed this website I went on reddit to share some of the love with them.
cheap tie http://www.burberryoutletscarfsale.com/accessories/burberry-ties.html

# Unknown topic 2012/10/26 17:05 Unknown

Unknown message

# mens shirts 2012/10/27 22:54 http://www.burberryoutletonlineshopping.com/burber

I gotta favorite this internet site it seems extremely helpful extremely helpful
mens shirts http://www.burberryoutletonlineshopping.com/burberry-men-shirts.html

# cheap tie 2012/10/28 17:00 http://www.burberryoutletonlineshopping.com/burber

I was reading through some of your articles on this site and I believe this internet site is really informative! Keep putting up.
cheap tie http://www.burberryoutletonlineshopping.com/burberry-ties.html

# Nike Free 3.0 2012/10/30 21:46 http://www.nikefree3runschuhe.com/

Happen to be ultimate if each one roommate perceives she has hook favourable position regarding the alternative.
Nike Free 3.0 http://www.nikefree3runschuhe.com/

# clarisonic mia best price 2012/10/30 21:46 http://www.clarisonicmia-coupon.com/

Absolutely love is truly frail towards entry into the world, having said that it will grow more potent with each passing year whether it's very well provided with.
clarisonic mia best price http://www.clarisonicmia-coupon.com/

# womens shirts 2012/11/03 1:50 http://www.burberrysalehandbags.com/burberry-women

Hello, you used to write great, but the last several posts have been kinda boring… I miss your tremendous writings. Past several posts are just a little out of track! come on!
womens shirts http://www.burberrysalehandbags.com/burberry-womens-shirts.html

# mens shirts 2012/11/03 1:50 http://www.burberrysalehandbags.com/burberry-men-s

Really excellent info can be found on blog . "The absence of flaw in beauty is itself a flaw." by Havelock Ellis.
mens shirts http://www.burberrysalehandbags.com/burberry-men-shirts.html

# burberry watches for women 2012/11/03 1:50 http://www.burberrysalehandbags.com/burberry-watch

Some really wonderful blog posts on this web site , thankyou for contribution.
burberry watches for women http://www.burberrysalehandbags.com/burberry-watches.html

# burberry wallets 2012/11/03 1:50 http://www.burberrysalehandbags.com/burberry-walle

You could certainly see your skills in the work you write. The arena hopes for even more passionate writers like you who aren't afraid to say how they believe. Always follow your heart. "If you feel yourself falling, let go and glide." by Steffen Francisco.
burberry wallets http://www.burberrysalehandbags.com/burberry-wallets-2012.html

# t shirt scarf 2012/11/03 1:50 http://www.burberrysalehandbags.com/burberry-scarf

Thanks, I've just been searching for information approximately this topic for ages and yours is the best I've discovered so far. However, what concerning the conclusion? Are you sure concerning the source?
t shirt scarf http://www.burberrysalehandbags.com/burberry-scarf.html

# wallet 2012/11/03 2:34 http://www.burberryoutletscarfsale.com/accessories

Some really quality blog posts on this web site , saved to fav.
wallet http://www.burberryoutletscarfsale.com/accessories/burberry-wallets-2012.html

# burberry bags 2012/11/03 2:34 http://www.burberryoutletscarfsale.com/burberry-ba

wonderful issues altogether, you simply received a logo new|a new} reader. What might you suggest about your publish that you simply made some days ago? Any positive?
burberry bags http://www.burberryoutletscarfsale.com/burberry-bags.html

# burberry womens shirts 2012/11/03 2:34 http://www.burberryoutletscarfsale.com/burberry-wo

I haven't checked in here for a while as I thought it was getting boring, but the last few posts are great quality so I guess I'll add you back to my daily bloglist. You deserve it friend :)
burberry womens shirts http://www.burberryoutletscarfsale.com/burberry-womens-shirts.html

# burberry mens shirts 2012/11/03 2:34 http://www.burberryoutletscarfsale.com/burberry-me

Great ? I should definitely pronounce, impressed with your web site. I had no trouble navigating through all the tabs as well as related information ended up being truly easy to do to access. I recently found what I hoped for before you know it at all. Quite unusual. Is likely to appreciate it for those who add forums or anything, web site theme . a tones way for your customer to communicate. Excellent task.
burberry mens shirts http://www.burberryoutletscarfsale.com/burberry-men-shirts.html

# burberry scarf 2012/11/03 2:34 http://www.burberryoutletscarfsale.com/accessories

Enjoyed looking through this, very good stuff, appreciate it. "All of our dreams can come true -- if we have the courage to pursue them." by Walt Disney.
burberry scarf http://www.burberryoutletscarfsale.com/accessories/burberry-scarf.html

# Burberry Watches 2012/11/03 2:34 http://www.burberryoutletscarfsale.com/accessories

I like this post, enjoyed this one thankyou for putting up.
Burberry Watches http://www.burberryoutletscarfsale.com/accessories/burberry-watches.html

# burberry outlet 2012/11/05 21:07 http://www.burberryoutletonlineshopping.com/burber

I consider something really special in this internet site.
burberry outlet http://www.burberryoutletonlineshopping.com/burberry-tote-bags.html

# mulberry sale 2012/11/06 23:56 http://www.outletmulberryuk.co.uk

But a smiling visitant here to share the love (:, btw outstanding style .
mulberry sale http://www.outletmulberryuk.co.uk

# mulberry handbag 2012/11/07 0:38 http://www.outletmulberryuk.co.uk/mulberry-handbag

Merely wanna state that this is extremely helpful, Thanks for taking your time to write this.
mulberry handbag http://www.outletmulberryuk.co.uk/mulberry-handbags-c-9.html

# mulberry handbag 2012/11/07 0:38 http://www.mulberrybagukoutlet.co.uk/mulberry-hand

Very superb info can be found on web blog . "Life without a friend is death without a witness." by Eugene Benge.
mulberry handbag http://www.mulberrybagukoutlet.co.uk/mulberry-handbags-c-9.html

# mulberry handbag 2012/11/07 0:38 http://www.bagmulberryuk.co.uk/mulberry-handbags-c

Appreciate it for helping out, wonderful info .
mulberry handbag http://www.bagmulberryuk.co.uk/mulberry-handbags-c-9.html

# Mens Supra TK Society 2012/12/08 15:09 http://suprafashionshoes.webs.com/

Some truly fantastic articles on this site, thanks for contribution. "When he has ceased to hear the many, he may discern the One - the inner sound which kills the outer." by H Hahn Blavatsky.

# トリーバーチ バッグ 2012/12/14 23:03 http://www.torybruchjp.info/category/トリーバーチ-店舗

this is definitely something may very well never at any time read.

# トリーバーチ 2012 2012/12/15 14:00 http://www.torybruchjp.info/category/トリーバーチ-店舗

we re-watched our creator of this Rings trilogy, the Godfather trilogy, and related to twenty various other movies that people loved and even hadn¡¯t watched inside of a while.

# burberry uk outlet 2012/12/16 4:47 http://www.burberryuksale.info/category/burberry-o

make these individuals red by having a yellow indy!!

# エルメス腕時計男性 2012/12/16 22:14 http://www.hermespairs.info/category/エルメス時計

Think it similar to a journal as well as a magazine about general interest for anyone to look at. Funny, witty, controversial, entertaining, useful, acerbic, thoughtful, serious, curious, unexpected comebacks are generally welcome.

# burberry coat 2012/12/17 8:17 http://www.burberrycanadaoutlet.info/category/burb

I realize the expensive garbage opinion. I can't stand the appearance, sound or feel in the Beats.

# long champs 2012/12/17 17:27 http://www.longchampbagoutlet.info/category/longch

I usage earbuds overseas because of the portability, even though I favor over the ear.

# isabel marant chaussures femmes 2012/12/17 19:07 http://www.isabelmarant-stores.com/category/isabel

gripping avenues of commentary bursting from a photos.

# bag burberry 2012/12/17 21:24 http://www.burberrycanadaoutlet.info/category/burb

i love this information and your own whole site!

# isabelmarantbottes.webnode.fr 2012/12/18 6:08 http://isabelmarantbottes.webnode.fr

gripping waters of feedback bursting in the photos.

# sac longchamps 2012/12/18 6:09 http://sacslongchampsolde.monwebeden.fr

Go thru these elder items and look for your that tickle an individual's fancy.

# burberry outlet 2012/12/18 12:24 http://www.burberryuksale.org/category/burberry-uk

The fashion don't flip flat along with Philips doesn't supplies a travel pouch in your package.

# burberry outlet 2012/12/18 12:31 http://www.burberryuksale.co/2012-burberry-handbag

I have never looked directly into Sennheisers as well as am trying to find new tote.

# longchamp cuir 2012/12/18 18:20 http://www.longchampfr.info/category/sac-longchamp

Keep in the excellent work.

# burberry bags 2012/12/18 22:31 http://burberryukoutlets.wordpress.com

we re-watched god, the father of your Rings trilogy, the Godfather trilogy, and on the subject of twenty different movies that we loved along with hadn¡¯t watched in any while.

# burberry scarf 2012/12/19 11:51 http://burberryoutlet2013sale.webeden.co.uk

Ill be down again the track to look at other threads that.

# burberry bags 2012/12/20 18:44 http://burberryukoutlets.wordpress.com

These look great.

# sac michael kors 2012 2012/12/22 14:06 http://michael-kors-canada.webnode.fr/

If many sound fantastic I'd absolutely wear these in the home.

# coach outlet online shopping 2013/04/07 23:08 http://www.coachfactoryoutlet44.com/

Preceptor‘testosterone levels strive so faithfully, the right items occur after you minimal hope them to.

# More memorandum hither this product 2016/08/24 18:00 Itsackm

Pet free to surf to my vkontakte

# My determination record! 2016/12/31 12:27 mpwqdrg

http://cailisonline.com/ , , http://ciailscoupon.com/ , , http://ciailscost.com/ , ,

タイトル  
名前  
Url
コメント