SQLitePCLの使用例

SQLitePCL.cs

// --------------------------------------------------------------------------------------------------------------------
// <copyright file="Program.cs" company="Tadahiro Ishisaka">
//   Copyright 2014 Tadahiro Ishisaka
//   
//   Licensed under the Apache License, Version 2.0 (the "License");
//   you may not use this file except in compliance with the License.
//   You may obtain a copy of the License at
//   
//       http://www.apache.org/licenses/LICENSE-2.0
//   
//   Unless required by applicable law or agreed to in writing, software
//   distributed under the License is distributed on an "AS IS" BASIS,
//   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
//   See the License for the specific language governing permissions and
//   limitations under the License.
// </copyright>
// <summary>
//   SQLitePCLのサンプル
// </summary>
// --------------------------------------------------------------------------------------------------------------------
namespace SqlitePclSample
{
    using System;

    using SQLitePCL;

    /// <summary>
    /// SQLitePCLのサンプル
    /// </summary>
    internal class Program
    {
        #region Methods

        /// <summary>
        /// SQLitePCLのサンプルを実行する。
        /// </summary>
        private static void Main()
        {
            using (var con = new SQLiteConnection("Test.DB"))
            {
                // CREATE TABLE
                SQLiteResult result;
                var stmCreateTable = con.Prepare("CREATE TABLE Test (ID INTEGER, Name TEXT)");
                int loop = 0;
                result = stmCreateTable.Step();
                Console.WriteLine(result.ToString());
                stmCreateTable.Dispose();

                // INSERT
                var stmInsert = con.Prepare("INSERT INTO Test (ID, Name) VALUES(1, 'Tadahiro Ishisaka')");
                result = stmInsert.Step();
                Console.WriteLine(result.ToString());
                stmInsert.Dispose();

                // INSERT With Parameter
                // パラメータドクエリを使用する場合
                var stmBind = con.Prepare("INSERT INTO Test (ID, Name) VALUES(@id, @name)");
                for (int i = 0; i < 10; i++)
                {
                    //ResetでStatementを初期状態に戻す(連続で同じステートメントを使用するなら必須)
                    stmBind.Reset();
                    //設定されたBindをクリアする。(連続で同じステートメントを使用するなら必須)
                    stmBind.ClearBindings();
                    //名前もしくはパラメータ順序, 値で指定する
                    stmBind.Bind("@id", i + 11);
                    stmBind.Bind("@name", "Jone" + i);
                    result = stmBind.Step();
                    Console.WriteLine("{0}:{1}", i, result);
                }
                stmBind.Dispose();

                // SELECT
                var stmSelect = con.Prepare("SELECT ID, Name FROM Test");
                stmSelect.Reset();
                do
                {
                    result = stmSelect.Step();
                    if (result == SQLiteResult.ROW)
                    {
                        // Statementにインデクサがあるから、それで列のデータを取得するよ。
                        var id = (long)stmSelect[0];
                        var name = (string)stmSelect[1];
                        Console.WriteLine(id + ":" + name);
                    }
                }
                while (result == SQLiteResult.ROW);
                stmSelect.Dispose();

            }

            Console.ReadLine();
        }

        #endregion
    }
}