michgan software studio

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;

namespace sqlitecs
{
    class Program
    {
        static void Main(string[] args)
        {
            //  Creating a database.
            SQLiteConnection.CreateFile("test.db");

            //  Open the database.
            using (var connection = new SQLiteConnection("Data Source=test.db"))
            {
                connection.Open();                 try
                {
                    //  Create table.
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "CREATE TABLE File (Name text, Size bigint, Modified datetime);";
                        command.ExecuteNonQuery();
                    }

                    //  Insert datas.
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "INSERT INTO File VALUES (?,?,?);";
                        SQLiteParameter p1 = new SQLiteParameter();
                        SQLiteParameter p2 = new SQLiteParameter();
                        SQLiteParameter p3 = new SQLiteParameter();
                        command.Parameters.Add(p1);
                        command.Parameters.Add(p2);
                        command.Parameters.Add(p3);

                        foreach (var fi in (new DirectoryInfo("c:\\Windows")).GetFiles())
                        {
                            p1.Value = fi.Name;
                            p2.Value = fi.Length;
                            p3.Value = fi.LastWriteTimeUtc;
                            command.ExecuteNonQuery();
                        }
                    }

                    //  Select files by its size between 1k and 1M
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "SELECT * from File where Size >= 1024 and Size <= 1048576 order by Size;";
                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {
                            while(reader.HasRows && reader.Read())
                            {
                                Console.WriteLine("{0}, {1}, {2}", reader.GetString(0), reader.GetInt64(1), reader.GetDateTime(2));
                            }
                        }
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}