Software/Visual_Studio/Tango.Synchronization/Local/SqliteDataBase.cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
pre { line-height: 125%; }
td.linenos .normal { color: inherit; background-color: transparent; padding-left: 5px; padding-right: 5px; }
span.linenos { color: inherit; background-color: transparent; padding-left: 5px; padding-right: 5px; }
td.linenos .special { color: #000000; background-color: #ffffc0; padding-left: 5px; padding-right: 5px; }
span.linenos.special { color: #000000; background-color: #ffffc0; padding-left: 5px; padding-right: 5px; }
.highlight .hll { background-color: #ffffcc }
.highlight .c { color: #888888 } /* Comment */
.highlight .err { color: #a61717; background-color: #e3d2d2 } /* Error */
.highlight .k { color: #008800; font-weight: bold } /* Keyword */
.highlight .ch { color: #888888 } /* Comment.Hashbang */
.highlight .cm { color: #888888 } /* Comment.Multiline */
.highlight .cp { color: #cc0000; font-weight: bold } /* Comment.Preproc */
.highlight .cpf { color: #888888 } /* Comment.PreprocFile */
.highlight .c1 { color: #888888 } /* Comment.Single */
.highlight .cs { color: #cc0000; font-weight: bold; background-color: #fff0f0 } /* Comment.Special */
.highlight .gd { color: #000000; background-color: #ffdddd } /* Generic.Deleted */
.highlight .ge { font-style: italic } /* Generic.Emph */
.highlight .ges { font-weight: bold; font-style: italic } /* Generic.EmphStrong */
.highlight .gr { color: #aa0000 } /* Generic.Error */
.highlight .gh { color: #333333 } /* Generic.Heading */
.highlight .gi { color: #000000; background-color: #ddffdd } /* Generic.Inserted */
.highlight .go { color: #888888 } /* Generic.Output */
.highlight .gp { color: #555555 } /* Generic.Prompt */
.highlight .gs { font-weight: bold } /* Generic.Strong */
.highlight .gu { color: #666666 } /* Generic.Subheading */
.highlight .gt { color: #aa0000 } /* Generic.Traceback */
.highlight .kc { color: #008800; font-weight: bold } /* Keyword.Constant */
.highlight .kd { color: #008800; font-weight: bold } /* Keyword.Declaration */
.highlight .kn { color: #008800; font-weight: bold } /* Keyword.Namespace */
.highlight .kp { color: #008800 } /* Keyword.Pseudo */
.highlight .kr { color: #008800; font-weight: bold } /* Keyword.Reserved */
.highlight .kt { color: #888888; font-weight: bold } /* Keyword.Type */
.highlight .m { color: #0000DD; font-weight: bold } /* Literal.Number */
.highlight .s { color: #dd2200; background-color: #fff0f0 } /* Literal.String */
.highlight .na { color: #336699 } /* Name.Attribute */
.highlight .nb { color: #003388 } /* Name.Builtin */
.highlight .nc { color: #bb0066; font-weight: bold } /* Name.Class */
.highlight .no { color: #003366; font-weight: bold } /* Name.Constant */
.highlight .nd { color: #555555 } /* Name.Decorator */
.highlight .ne { color: #bb0066; font-weight: bold } /* Name.Exception */
.highlight .nf { color: #0066bb; font-weight: bold } /* Name.Function */
.highlight .nl { color: #336699; font-style: italic } /* Name.Label */
.highlight .nn { color: #bb0066; font-weight: bold } /* Name.Namespace */
.highlight .py { color: #336699; font-weight: bold } /* Name.Property */
.highlight .nt { color: #bb0066; font-weight: bold } /* Name.Tag */
.highlight .nv { color: #336699 } /* Name.Variable */
.highlight .ow { color: #008800 } /* Operator.Word */
.highlight .w { color: #bbbbbb } /* Text.Whitespace */
.highlight .mb { color: #0000DD; font-weight: bold } /* Literal.Number.Bin */
.highlight .mf { color: #0000DD; font-weight: bold } /* Literal.Number.Float */
.highlight .mh { color: #0000DD; font-weight: bold } /* Literal.Number.Hex */
.highlight .mi { color: #0000DD; font-weight: bold } /* Literal.Number.Integer */
.highlight .mo { color: #0000DD; font-weight: bold } /* Literal.Number.Oct */
.highlight .sa { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Affix */
.highlight .sb { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Backtick */
.highlight .sc { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Char */
.highlight .dl { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Delimiter */
.highlight .sd { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Doc */
.highlight .s2 { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Double */
.highlight .se { color: #0044dd; background-color: #fff0f0 } /* Literal.String.Escape */
.highlight .sh { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Heredoc */
.highlight .si { color: #3333bb; background-color: #fff0f0 } /* Literal.String.Interpol */
.highlight .sx { color: #22bb22; background-color: #f0fff0 } /* Literal.String.Other */
.highlight .sr { color: #008800; background-color: #fff0ff } /* Literal.String.Regex */
.highlight .s1 { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Single */
.highlight .ss { color: #aa6600; background-color: #fff0f0 } /* Literal.String.Symbol */
.highlight .bp { color: #003388 } /* Name.Builtin.Pseudo */
.highlight .fm { color: #0066bb; font-weight: bold } /* Name.Function.Magic */
.highlight .vc { color: #336699 } /* Name.Variable.Class */
.highlight .vg { color: #dd7700 } /* Name.Variable.Global */
.highlight .vi { color: #3333bb } /* Name.Variable.Instance */
.highlight .vm { color: #336699 } /* Name.Variable.Magic */
.highlight .il { color: #0000DD; font-weight: bold } /* Literal.Number.Integer.Long */<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <ProjectGuid>{22F87980-E990-4686-BE81-BE63D562C4D5}</ProjectGuid>
    <OutputType>Library</OutputType>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <RootNamespace>Tango.Serialization</RootNamespace>
    <AssemblyName>Tango.Serialization</AssemblyName>
    <TargetFrameworkVersion>v4.6.1</TargetFrameworkVersion>
    <FileAlignment>512</FileAlignment>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <OutputPath>..\Build\Core\Debug\</OutputPath>
    <DefineConstants>DEBUG;TRACE</DefineConstants>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <OutputPath>..\Build\Core\Release\</OutputPath>
    <DefineConstants>TRACE</DefineConstants>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <ItemGroup>
    <Reference Include="Google.Protobuf, Version=3.4.1.0, Culture=neutral, PublicKeyToken=a7d26565bac4d604, processorArchitecture=MSIL">
      <HintPath>..\packages\Google.Protobuf.3.4.1\lib\net45\Google.Protobuf.dll</HintPath>
    </Reference>
    <Reference Include="Newtonsoft.Json, Version=9.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed, processorArchitecture=MSIL">
      <HintPath>..\packages\Newtonsoft.Json.9.0.1\lib\net45\Newtonsoft.Json.dll</HintPath>
    </Reference>
    <Reference Include="System" />
    <Reference Include="System.Core" />
    <Reference Include="System.Xml.Linq" />
    <Reference Include="System.Data.DataSetExtensions" />
    <Reference Include="Microsoft.CSharp" />
    <Reference Include="System.Data" />
    <Reference Include="System.Net.Http" />
    <Reference Include="System.Xml" />
  </ItemGroup>
  <ItemGroup>
    <Compile Include="..\Versioning\GlobalVersionInfo.cs">
      <Link>GlobalVersionInfo.cs</Link>
    </Compile>
    <Compile Include="BinaryDataSerializer.cs" />
    <Compile Include="CryptographyHelper.cs" />
    <Compile Include="IDataSerializer.cs" />
    <Compile Include="JsonDataSerializer.cs" />
    <Compile Include="Properties\AssemblyInfo.cs" />
    <Compile Include="SerializationHelper.cs" />
    <Compile Include="XmlDataSerializer.cs" />
  </ItemGroup>
  <ItemGroup>
    <None Include="packages.config" />
  </ItemGroup>
  <Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />
  <ProjectExtensions>
    <VisualStudio>
      <UserProperties BuildVersion_StartDate="2000/1/1" BuildVersion_UseGlobalSettings="False" BuildVersion_BuildVersioningStyle="None.None.Increment.TimeStamp" BuildVersion_UpdateAssemblyVersion="True" BuildVersion_AssemblyInfoFilename="Properties\AssemblyInfo.cs" />
    </VisualStudio>
  </ProjectExtensions>
</Project>
using Tango.Synchronization;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;

namespace Tango.Synchronization.Local
{
    /// <summary>
    /// Represents an SQLite database adapter used for synchronization by <see cref="LocalDBComparer"/>.
    /// </summary>
    /// <seealso cref="Tango.Synchronization.ISqlDataBase" />
    public class SQLiteDataBase : ILocalDataBase
    {
        private SQLiteConnection _connection;

        /// <summary>
        /// Gets the database source URL/File.
        /// </summary>
        public String Source { get; private set; }

        /// <summary>
        /// Gets the database tables collection.
        /// </summary>
        public List<DataTable> Tables { get; private set; }

        /// <summary>
        /// Initializes a new instance of the <see cref="SQLiteDataBase"/> class.
        /// </summary>
        /// <param name="filePath">The file path.</param>
        public SQLiteDataBase(String filePath)
        {
            Source = filePath;

            _connection = new SQLiteConnection(String.Format("Data Source={0};Version=3;New={1};Compress=FALSE;",
                filePath,
                (File.Exists(filePath) ? "False" : "True")
                ));

            _connection.Open();
        }

        /// <summary>
        /// Loads the tables (Must be done before any synchronization).
        /// </summary>
        public void LoadTables()
        {
            Tables = new List<DataTable>();

            var command = _connection.CreateCommand();
            command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table'";
            var reader = command.ExecuteReader();

            while (reader.Read())
            {
                String name = reader.GetString(0);

                if (name != Constants.SEQUENCE_TABLE_NAME)
                {
                    Debug.WriteLine(name);
                    Tables.Add(LoadTable(name));
                }
            }
        }

        /// <summary>
        /// Loads the table.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        private DataTable LoadTable(String name)
        {
            var tableCommand = _connection.CreateCommand();
            tableCommand.CommandText = "SELECT * FROM " + name;

            DataTable table = new DataTable(name);
            table.Load(tableCommand.ExecuteReader());

            var infoCommand = _connection.CreateCommand();
            infoCommand.CommandText = String.Format("PRAGMA table_info({0});", name);
            DataTable infoTable = new DataTable(name);
            infoTable.Load(infoCommand.ExecuteReader());

            table.ExtendedProperties.Add(Constants.TABLE_INFO, infoTable);

            for (int i = 0; i < table.Columns.Count; i++)
            {
                DataColumn column = table.Columns[i];
                column.ExtendedProperties.Add(Constants.COLUMN_TYPE, infoTable.Rows[i][Constants.COLUMN_TYPE]);
                column.ExtendedProperties.Add(Constants.IS_NOT_NULL, infoTable.Rows[i][Constants.IS_NOT_NULL]);
                column.ExtendedProperties.Add(Constants.DEFAULT_VALUE, infoTable.Rows[i][Constants.DEFAULT_VALUE]);
            }

            return table;
        }

        /// <summary>
        /// Clears the data base.
        /// </summary>
        public void ClearDataBase()
        {
            foreach (var table in Tables)
            {
                var dropCommand = _connection.CreateCommand();
                dropCommand.CommandText = String.Format("DELETE FROM {0};", table.TableName);
                dropCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Clones a table from another database into this database.
        /// </summary>
        /// <param name="otherDB">The other database.</param>
        /// <param name="otherTable">The other table.</param>
        /// <returns></returns>
        public DataTable CloneTableFrom(ILocalDataBase otherDB, DataTable otherTable)
        {
            String cmd = String.Format("CREATE TABLE {0} (", otherTable.TableName);

            foreach (DataColumn column in otherTable.Columns)
            {
                cmd += String.Format("{0} {1} {2} {3} {4} {5}",
                    column.ColumnName,
                    column.GetSQLType(),
                    otherTable.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null,
                    column.Unique ? "UNIQUE" : null,
                    column.IsNotNull() ? "NOT NULL" : null,
                    column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null)

                    + ", " + Environment.NewLine;
            }

            cmd += ");";

            cmd = cmd.Remove(cmd.LastIndexOf(","), 1);

            var createCommand = _connection.CreateCommand();
            createCommand.CommandText = cmd;
            createCommand.ExecuteNonQuery();

            var attacheCommand = _connection.CreateCommand();
            attacheCommand.CommandText = String.Format("ATTACH DATABASE '{0}' AS other;", otherDB.Source);
            attacheCommand.ExecuteNonQuery();

            var copyCommand = _connection.CreateCommand();
            copyCommand.CommandText = String.Format("INSERT INTO main.{0} SELECT * FROM {1}.{0};", otherTable.TableName, "other");
            copyCommand.ExecuteNonQuery();

            var detacheCommand = _connection.CreateCommand();
            detacheCommand.CommandText = "DETACH other;";
            detacheCommand.ExecuteNonQuery();

            var table = LoadTable(otherTable.TableName);
            Tables.Add(table);
            return table;
        }

        /// <summary>
        /// Replaces the table data with the data from another table.
        /// </summary>
        /// <param name="otherTable">The other table.</param>
        public void ReplaceTableData(ILocalDataBase otherDB, DataTable otherTable)
        {
            var dropCommand = _connection.CreateCommand();
            dropCommand.CommandText = String.Format("DELETE FROM {0};", otherTable.TableName);
            dropCommand.ExecuteNonQuery();

            var attacheCommand = _connection.CreateCommand();
            attacheCommand.CommandText = String.Format("ATTACH DATABASE '{0}' AS other;", otherDB.Source);
            attacheCommand.ExecuteNonQuery();

            var copyCommand = _connection.CreateCommand();
            copyCommand.CommandText = String.Format("INSERT INTO main.{0} SELECT * FROM {1}.{0};", otherTable.TableName, "other");
            copyCommand.ExecuteNonQuery();

            var detacheCommand = _connection.CreateCommand();
            detacheCommand.CommandText = "DETACH other;";
            detacheCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// Gets the SQL command for <see cref="ReplaceTableData(ILocalDataBase, DataTable)"/>.
        /// </summary>
        /// <param name="otherDB"></param>
        /// <param name="otherTable">The other table.</param>
        /// <returns></returns>
        public string GetReplaceTableDataCommand(ILocalDataBase otherDB, DataTable otherTable)
        {
            String cmd = String.Format("DELETE FROM {0};", otherTable.TableName) + Environment.NewLine;
            cmd += String.Format("ATTACH DATABASE '{0}' AS other;", otherDB.Source) + Environment.NewLine;
            cmd += String.Format("INSERT INTO main.{0} SELECT * FROM {1}.{0};", otherTable.TableName, "other") + Environment.NewLine;
            cmd += "DETACH other;" + Environment.NewLine;
            return cmd;
        }

        /// <summary>
        /// Gets the SQL command for <see cref="CloneTableFrom(ILocalDataBase, DataTable)" />.
        /// </summary>
        /// <param name="otherDB">The other database.</param>
        /// <param name="otherTable">The other table.</param>
        /// <returns></returns>
        public String GetCloneTableFromCommand(ILocalDataBase otherDB, DataTable otherTable)
        {
            String cmd = String.Format("CREATE TABLE {0} (", otherTable.TableName);

            foreach (DataColumn column in otherTable.Columns)
            {
                cmd += String.Format("{0} {1} {2} {3} {4} {5}",
                    column.ColumnName,
                    column.GetSQLType(),
                    otherTable.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null,
                    column.Unique ? "UNIQUE" : null,
                    column.IsNotNull() ? "NOT NULL" : null,
                    column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null)

                    + ", " + Environment.NewLine;
            }

            cmd += ");";

            cmd = cmd.Remove(cmd.LastIndexOf(","), 1);

            cmd += Environment.NewLine;

            cmd += String.Format("ATTACH DATABASE '{0}' AS other;", otherDB.Source);

            cmd += Environment.NewLine;

            cmd += String.Format("INSERT INTO main.{0} SELECT * FROM {1}.{0};", otherTable.TableName, "other");

            cmd += Environment.NewLine;

            cmd += "DETACH other;";

            return cmd;
        }

        /// <summary>
        /// Adds the specified column to the specified table.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="column">The column.</param>
        /// <returns></returns>
        public DataColumn AddColumn(DataTable table, DataColumn column)
        {
            var insertCommand = _connection.CreateCommand();
            insertCommand.CommandText = String.Format("ALTER TABLE {0} ADD COLUMN {1} {2} {3} {4} {5}",
                table.TableName,
                column.ColumnName,
                column.GetSQLType(),
                table.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null,
                column.Unique ? "UNIQUE" : null,
                //column.IsNotNull() ? "NOT NULL" : null,
                column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null);


            insertCommand.ExecuteNonQuery();
            var t = LoadTable(table.TableName);
            var index = Tables.IndexOf(Tables.Single(x => x.TableName == table.TableName));
            Tables[index] = t;
            return t.Columns[column.ColumnName];
        }

        /// <summary>
        /// Gets the SQL command for <see cref="AddColumn(DataTable, DataColumn)" />.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="column">The column.</param>
        /// <returns></returns>
        public String GetAddColumnCommand(DataTable table, DataColumn column)
        {
            String cmd = String.Format("ALTER TABLE {0} ADD COLUMN {1} {2} {3} {4} {5}",
                table.TableName,
                column.ColumnName,
                column.GetSQLType(),
                table.PrimaryKey.Contains(column) ? "PRIMARY KEY" : null,
                column.Unique ? "UNIQUE" : null,
                //column.IsNotNull() ? "NOT NULL" : null,
                column.HasDefaultValue() ? ("DEFAULT " + column.GetDefaultValue()) : null);

            return cmd;
        }

        /// <summary>
        /// Adds the specified row to the specified table.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="row">The row.</param>
        public void AddRow(DataTable table, DataRow row)
        {
            var insertCommand = _connection.CreateCommand();

            List<String> values = new List<string>();

            for (int i = 0; i < row.ItemArray.Length; i++)
            {
                String value = row.ItemArray[i].ToString();

                double num = 0;

                if (row.ItemArray[i].GetType() == typeof(bool))
                {
                    value = ((bool)row.ItemArray[i]) ? 1.ToString() : 0.ToString();
                }

                if (row.ItemArray[i].GetType() == typeof(DateTime))
                {
                    value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString();
                }

                if (!double.TryParse(value, out num))
                {
                    value = "'" + value + "'";
                }

                values.Add(value);
            }

            insertCommand.CommandText = String.Format("INSERT INTO {0} VALUES({1});", table.TableName, String.Join(",", values));
            insertCommand.ExecuteNonQuery();

            var desRow = table.NewRow();
            desRow.ItemArray = row.ItemArray.Clone() as object[];
        }

        /// <summary>
        /// Gets the SQL command for <see cref="AddRow(DataTable, DataRow)" />.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="row">The row.</param>
        /// <returns></returns>
        public String GetAddRowCommand(DataTable table, DataRow row)
        {
            List<String> values = new List<string>();

            for (int i = 0; i < row.ItemArray.Length; i++)
            {
                String value = row.ItemArray[i].ToString();

                double num = 0;

                if (row.ItemArray[i].GetType() == typeof(bool))
                {
                    value = ((bool)row.ItemArray[i]) ? 1.ToString() : 0.ToString();
                }

                if (row.ItemArray[i].GetType() == typeof(DateTime))
                {
                    value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString();
                }

                if (!double.TryParse(value, out num))
                {
                    value = "'" + value + "'";
                }

                values.Add(value);
            }

            String cmd = String.Format("INSERT INTO {0} VALUES({1});", table.TableName, String.Join("," + Environment.NewLine, values));
            return cmd;
        }

        /// <summary>
        /// Updates the matching row by the row GUID.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="row">The row.</param>
        public void UpdateRow(DataTable table, DataRow row)
        {
            var updateCommand = _connection.CreateCommand();

            List<String> values = new List<string>();

            for (int i = 0; i < row.ItemArray.Length; i++)
            {
                String value = row.ItemArray[i].ToString();

                double num = 0;

                if (row.ItemArray[i].GetType() == typeof(bool))
                {
                    value = ((bool)row.ItemArray[i]) ? 1.ToString() : 0.ToString();
                }

                if (row.ItemArray[i].GetType() == typeof(DateTime))
                {
                    value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString();
                }

                if (!double.TryParse(value, out num))
                {
                    value = "'" + value + "'";
                }

                values.Add(value);
            }

            String cmd = String.Format("UPDATE {0} SET ", table.TableName);

            String guid = String.Empty;

            for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
            {
                DataColumn column = table.Columns[columnIndex];
                if (column.ColumnName == Constants.ID)
                {
                    continue;
                }
                else if (column.ColumnName == Constants.GUID)
                {
                    guid = values[columnIndex];
                    continue;
                }

                cmd += String.Format("{0} = {1},{2}", column.ColumnName, values[columnIndex], Environment.NewLine);
            }

            cmd = cmd.Remove(cmd.LastIndexOf(","), 1);

            cmd += "WHERE" + Environment.NewLine;
            cmd += String.Format("{0} = {1};", Constants.GUID, guid);

            updateCommand.CommandText = cmd;
            updateCommand.ExecuteNonQuery();
            var desRow = table.NewRow();
            desRow.ItemArray = row.ItemArray.Clone() as object[];
        }

        /// <summary>
        /// Gets the SQL command for <see cref="UpdateRow(DataTable, DataRow)" />.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="row">The row.</param>
        /// <returns></returns>
        public String GetUpdateRowCommand(DataTable table, DataRow row)
        {
            List<String> values = new List<string>();

            for (int i = 0; i < row.ItemArray.Length; i++)
            {
                String value = row.ItemArray[i].ToString();

                double num = 0;

                if (row.ItemArray[i].GetType() == typeof(bool))
                {
                    value = ((bool)row.ItemArray[i]) ? 1.ToString() : 0.ToString();
                }

                if (row.ItemArray[i].GetType() == typeof(DateTime))
                {
                    value = ((DateTime)row.ItemArray[i]).ToSQLiteDateString();
                }

                if (!double.TryParse(value, out num))
                {
                    value = "'" + value + "'";
                }

                values.Add(value);
            }

            String cmd = String.Format("UPDATE {0} SET ", table.TableName);

            String guid = String.Empty;

            for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
            {
                DataColumn column = table.Columns[columnIndex];
                if (column.ColumnName == Constants.ID)
                {
                    continue;
                }
                else if (column.ColumnName == Constants.GUID)
                {
                    guid = values[columnIndex];
                    continue;
                }

                cmd += String.Format("{0} = {1},{2}", column.ColumnName, values[columnIndex], Environment.NewLine);
            }

            cmd = cmd.Remove(cmd.LastIndexOf(","), 1);

            cmd += "WHERE" + Environment.NewLine;
            cmd += String.Format("{0} = {1};", Constants.GUID, guid);

            return cmd;
        }

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            _connection.Close();
            _connection.Dispose();
            GC.Collect();

            DateTime startTime = DateTime.Now;

            while (startTime.AddSeconds(2) > DateTime.Now)
            {
                try
                {
                    using (FileStream stream = File.Open(Source, FileMode.Open, FileAccess.Read))
                    {
                        return;
                    }
                }
                catch (IOException)
                {
                    GC.Collect();
                    Thread.Sleep(200);
                }
            }
        }
    }
}