public async Task Load(SQLiteConnection connection)
        {
            using (var command = new SQLiteCommand("SELECT * FROM `Tracks`", connection))
            {
                var reader = await command.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    PlayableBase track;
                    using (var xmlReader = reader.GetTextReader(8))
                        track = (PlayableBase) _serializer.Deserialize(xmlReader);

                    track.Title = reader.GetString(0);
                    track.Artist = _artistProvider.ArtistDictionary[reader.ReadGuid(1)];

                    var albumGuid = reader.ReadGuid(2);
                    if (albumGuid != Guid.Empty)
                        track.Album = _albumsProvider.Collection[albumGuid];
                    track.Guid = reader.ReadGuid(3);
                    track.LastTimePlayed = reader.GetDateTime(4);
                    track.MusicBrainzId = reader.GetValue(5)?.ToString();
                    track.Duration = XmlConvert.ToTimeSpan(reader.GetString(6));

                    var coverId = reader.ReadGuid(7);
                    if (coverId != Guid.Empty)
                        track.Cover = _imageProvider.Collection[coverId];

                    Collection.Add(track.Guid, track);
                    Tracks.Add(track);
                }
            }

            _connection = connection;
        }

        /// <summary>
        ///   Removes the old entries (entries that have been in the database more than the specified time) from the database.
        /// </summary>
        /// <param name="olderThan"> The number of days in the database after which the entry is considered old... </param>
        public static void CleanNewsOlderThan(int olderThan)
        {
            try
            {
                using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
                {
                    sqLiteConnection.Open();

                    DateTime olderDate = DateTime.Now;
                    TimeSpan daySpan = new TimeSpan(olderThan, 0, 0, 0);
                    olderDate = olderDate.Subtract(daySpan);

                    SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
                                                      {
                                                          CommandText = "DELETE " +
                                                                        "FROM NEWS_STORAGE " +
                                                                        "WHERE NEWSITEM_AQUISITION_DATE <= ?"
                                                      };
                    sqLiteCommand.Parameters.AddWithValue(null, olderDate);
                    sqLiteCommand.ExecuteNonQuery();

                    sqLiteConnection.Close();
                }
            }
            catch (Exception ex)
            {
                ErrorMessageBox.Show(ex.Message, ex.ToString());
                Logger.ErrorLogger("error.txt", ex.ToString());
            }
        }

 /// <summary>
 /// Сохраняет изменения в таблице
 /// </summary>
 /// <param name="question">вопрос</param>
 public void SaveOrUpdate(SQLiteConnection conn, QuestionResult question)
 {
     if (question.ID == 0)
     {
         int ID = indexService.NextID(TABLE_NAME);
         using (SQLiteCommand insertSQL = new SQLiteCommand("insert into QUESTION_RESULT (ID, QuestionTitle, TEST_RESULT_ID) values (@ID, @QuestionTitle, @TEST_RESULT_ID)", conn))
         {
             insertSQL.Parameters.AddWithValue("@QuestionTitle", question.QuestionTitle);
             insertSQL.Parameters.AddWithValue("@TEST_RESULT_ID", question.testResult.ID);
             insertSQL.Parameters.AddWithValue("@ID", ID);
             insertSQL.ExecuteNonQuery();
             question.ID = ID;
         }
         
     }
     else
     {
         using (SQLiteCommand insertSQL = new SQLiteCommand("UPDATE QUESTION_RESULT SET QuestionTitle = @QuestionTitle, TEST_RESULT_ID = @TEST_RESULT_ID WHERE ID = @ID", conn))
         {
             insertSQL.Parameters.AddWithValue("@QuestionTitle", question.QuestionTitle);
             insertSQL.Parameters.AddWithValue("@TEST_RESULT_ID", question.testResult.ID);
             insertSQL.Parameters.AddWithValue("@ID", question.ID);
             insertSQL.ExecuteNonQuery();
         }
     }
 }

		/// <summary>Write data with a given key to cache</summary>
		/// <param name="key">Key of data to be stored</param>
		/// <param name="data">Data to be stored</param>
		public void Store(string key, string data)
		{
			SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File);
			try
			{
				DateTime now = DateTime.Now; 
				connection.Open();

				SQLiteCommand command = connection.CreateCommand();
				command.CommandText = "DELETE FROM [InetCache] WHERE [Keyword]=?";
				command.Parameters.Add("kw", DbType.String).Value = key;
				command.ExecuteNonQuery();	// out with the old

				command = connection.CreateCommand();
				command.CommandText = "INSERT INTO [InetCache] (1653527584, [Keyword], [Content]) VALUES (?, ?, ?)";
				command.Parameters.Add("ts", DbType.Int32).Value = now.Year * 10000 + now.Month * 100 + now.Day;
				command.Parameters.Add("kw", DbType.String).Value = key;
				command.Parameters.Add("dt", DbType.String).Value = data;
				command.ExecuteNonQuery();	// in with the new
			}
			finally
			{
				connection.Close();
			}
		}

        public static DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();

            try

            {

                SQLiteConnection cnn = new SQLiteConnection(@"Data Source=C:\Projects\showdownsharp\db\showdown.db");

                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = sql;

                SQLiteDataReader reader = mycommand.ExecuteReader();

                dt.Load(reader);

                reader.Close();

                cnn.Close();

            } catch {

            // Catching exceptions is for communists

            }

            return dt;
        }

        public List<AttachmentDetail> getWitAttachments()
        {
            List<AttachmentDetail> attachments;
            try
            {
                sql_con = new SQLiteConnection(Common.localDatabasePath, true);
                sql_cmd = new SQLiteCommand("select * from wit_attachments", sql_con);

                sql_con.Open();
                SQLiteDataReader reader = sql_cmd.ExecuteReader();

                attachments = new List<AttachmentDetail>();
                while (reader.Read())
                {
                    AttachmentDetail attachment = new AttachmentDetail();
                    attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
                    attachment.fileName = StringUtils.ConvertFromDBVal<string>(reader["file_name"]);
                    attachment.witId = StringUtils.ConvertFromDBVal<string>(reader["wit_id"]);
                    attachment.fileMimeType = StringUtils.ConvertFromDBVal<string>(reader["file_mime_type"]);
                    attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
                    attachment.seqNumber = StringUtils.ConvertFromDBVal<string>(reader["seq_number"]);
                    attachment.extention = StringUtils.ConvertFromDBVal<string>(reader["extention"]);
                   
                    attachments.Add(attachment);
                }

            }
            catch (SQLiteException e) { throw e; }
            finally { sql_con.Close(); }

            return attachments;

        }

        public Block_Edit(int id_)
        {
            InitializeComponent();
            id = id_;

            using (SQLiteConnection conn = new SQLiteConnection(connString))
            {
                conn.Open();
                SQLiteCommand command = new SQLiteCommand(conn);
                command.CommandText = "SELECT * FROM Block WHERE [email protected]";
                command.Parameters.Add(new SQLiteParameter("@id", id));
                using (command)
                {
                    using (SQLiteDataReader rdr = command.ExecuteReader())
                    {
                        while (rdr.Read())
                        {

                            labelName.Text = rdr.GetValue(1).ToString();
                            txtImieNazw.Text = rdr.GetValue(1).ToString();

                        }
                    }
                }
                conn.Close();
            }
        }

    private static SQLiteConnection GetConnection(string filePath)
    {
        SQLiteConnection connection = new SQLiteConnection(
            string.Format("Data Source={0};Version=3;", filePath));

        return connection;
    }

        public static void ChapterFinished(string mangaTitle)
        {
            try
            {
                using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
                {
                    sqLiteConnection.Open();

                    SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
                                                      {
                                                          CommandText = "UPDATE READING_LIST " +
                                                                        "SET READ_CURRENT_CHAPTER = READ_CURRENT_CHAPTER + 1, READ_LAST_TIME = ? " +
                                                                        "WHERE MANGA_ID = ?"
                                                      };
                    sqLiteCommand.Parameters.AddWithValue(null, DateTime.Now);
                    sqLiteCommand.Parameters.AddWithValue(null, GetMangaId(mangaTitle));
                    sqLiteCommand.ExecuteNonQuery();

                    sqLiteConnection.Close();
                }
            }
            catch (Exception ex)
            {
                ErrorMessageBox.Show(ex.Message, ex.ToString());
                Logger.ErrorLogger("error.txt", ex.ToString());
            }
        }

        /// <summary>
        /// Takes a GIS model and a file and writes the model to that file.
        /// </summary>
        /// <param name="model">
        /// The GisModel which is to be persisted.
        /// </param>
        /// <param name="fileName">
        /// The name of the file in which the model is to be persisted.
        /// </param>
        public void Persist(GisModel model, string fileName)
        {
            Initialize(model);
            PatternedPredicate[] predicates = GetPredicates();

            if (	File.Exists(fileName))
            {
                File.Delete(fileName);
            }

            using (mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";New=True;Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3"))
            {
                mDataConnection.Open();
                mDataCommand = mDataConnection.CreateCommand();
                CreateDataStructures();

                using (mDataTransaction = mDataConnection.BeginTransaction())
                {
                    mDataCommand.Transaction = mDataTransaction;

                    CreateModel(model.CorrectionConstant, model.CorrectionParameter);
                    InsertOutcomes(model.GetOutcomeNames());
                    InsertPredicates(predicates);
                    InsertPredicateParameters(model.GetOutcomePatterns(), predicates);

                    mDataTransaction.Commit();
                }
                mDataConnection.Close();
            }
        }

        public addDeletionInstructionDialog(string workingDirectory, modEditor me, SQLiteConnection conn, int editing)
        {
            InitializeComponent();
            this.workingDirectory = workingDirectory;
            this.me = me;
            this.conn = conn;
            this.editing = editing;

            if (editing != 0)
            {
                string sql = "SELECT file_name, type FROM files_delete WHERE id = " + editing + " LIMIT 1";
                SQLiteCommand command = new SQLiteCommand(sql, conn);
                SQLiteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    char[] chars = { '/' };
                    string[] pieces = reader["file_name"].ToString().Split(chars, 2);
                    if (pieces.Length == 1)
                        fileName.Text = pieces[0];
                    else if (pieces.Length == 2)
                    {
                        filePrefix.SelectedItem = pieces[0];
                        fileName.Text = pieces[1];
                    }

                    whatIs_Dir.Checked = reader["type"].ToString() == "dir";
                    whatIs_File.Checked = reader["type"].ToString() == "file";
                }
            }
        }

        internal static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, SQLiteTransaction transaction,
                                           CommandType commandType, string commandText, SQLiteParameter[] commandParameters,
                                           out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");

            if (connection.State == ConnectionState.Open)
                mustCloseConnection = false;
            else
            {
                mustCloseConnection = true;
                connection.Open();
            }

            command.Connection = connection;
            command.CommandText = commandText;

            if (transaction != null)
            {
                if (transaction.Connection == null)
                    throw new ArgumentException(
                        "The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }

            command.CommandType = commandType;

            if (commandParameters != null)
                AttachParameters(command, commandParameters);
            return;
        }

        private bool CreateDatabase()
        {
            SQLiteConnection tempConnection = null;
            try
            {
                SQLiteConnection.CreateFile(ConfigurationManager.AppSettings["dbPath"]);
                tempConnection = new SQLiteConnection("Data Source=" + ConfigurationManager.AppSettings["dbPath"] + ";Version=3;");
                tempConnection.Open();

                SQLiteCommand command = tempConnection.CreateCommand();
                command.CommandText = "CREATE TABLE pelaaja (id INTEGER PRIMARY KEY AUTOINCREMENT, etunimi TEXT NOT NULL, sukunimi TEXT NOT NULL, seura TEXT NOT NULL, hinta FLOAT NOT NULL, kuva_url TEXT NULL)";
                command.ExecuteNonQuery();

                tempConnection.Close();
            }
            catch(Exception e)
            {
                errors.Add("Tietokannan luominen epäonnistui");
                return false;
            }
            finally
            {
                if(tempConnection != null) tempConnection.Close();
            }

            return true;
        }

 public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
 {
     using (SQLiteConnection conn = new SQLiteConnection("StaticConfig.DataSource"))
     {
         using (SQLiteCommand cmd = new SQLiteCommand())
         {
             cmd.Connection = conn;
             conn.Open();
             SQLiteHelper sh = new SQLiteHelper(cmd);
             int countCurrent = 0;
             string sqlCommandCurrent = String.Format("select count(*) from QuestionInfo where Q_Num={0};", value);//判断当前题号是否存在
             try
             {
                 Int32.Parse((string)value);
                 countCurrent = sh.ExecuteScalar<int>(sqlCommandCurrent);
                 conn.Close();
                 if (countCurrent > 0)
                 {
                     return "更新";
                 }
                 else
                 {
                     return "保存";
                 }
             }
             catch (Exception)
             {
                 return "保存";
             }
         }
     }
 }

 private DoorInfo Retrieve(string DoorID)
 {
     var ret = new DoorInfo();
     var cs = ConfigurationManager.ConnectionStrings["DoorSource"].ConnectionString;
     var conn = new SQLiteConnection(cs);
     conn.Open();
     var cmd = new SQLiteCommand(conn);
     cmd.CommandText = "SELECT DoorID, Location, Description, EventID FROM Doors WHERE DoorID = @DoorID LIMIT 1";
     cmd.Parameters.Add(new SQLiteParameter("@DoorID", DoorID));
     SQLiteDataReader res = null;
     try
     {
         res = cmd.ExecuteReader();
         if (res.HasRows && res.Read())
         {
             ret.DoorID = DoorID;
             ret.Location = res.GetString(1);
             ret.Description = res.GetString(2);
             ret.EventID = res.GetInt64(3);
         }
         return ret;
     }
     catch(Exception ex)
     {
         throw;
     }
     finally
     {
         if (null != res && !res.IsClosed)
             res.Close();
     }
 }

 public static DataTable GetClients(string sqlitefile)
 {
     SQLiteConnection con = new SQLiteConnection("data source=" + sqlitefile);
     DataTable dt = new DataTable("CompanyInfo");
     dt = ExecuteNonQueryDt("SELECT * FROM CompanyInfo", con);
     return dt;
 }

        private void Consulta_Load(object sender, EventArgs e)
        {
            string appPath = Path.GetDirectoryName(Application.ExecutablePath);
            string connString = @"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;";

            DataSet DS = new DataSet();
            SQLiteConnection con = new SQLiteConnection(connString);
            con.Open();
            SQLiteDataAdapter DA = new SQLiteDataAdapter("select * from Expediente", con);
            DA.Fill(DS, "Expediente");
            dataGridView1.DataSource = DS.Tables["Expediente"];
            con.Close();

            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;

            int i = 0;
            foreach (DataGridViewColumn c in dataGridView1.Columns)
            {
                i += c.Width;

            }
            if ((i + dataGridView1.RowHeadersWidth + 2) > 616)
            {
                dataGridView1.Width = 616;
            }
            else
            {
                dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2;
            }
        }

        public SQLiteCommand CreateAddProductCommand(SQLiteConnection conn, SQLiteTransaction transaction)
        {
            var cmd = new SQLiteCommand(_sql, conn, transaction);
            CreateParameters(cmd);

            return cmd;
        }

 private void DeleteCardBase()
 {
     if (dataGridView1.SelectedRows.Count > 0)
     {
         id = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
         if (dataGridView1.RowCount != 0) index = dataGridView1.SelectedRows[0].Index;
         DialogResult result = MessageBox.Show("Czy na pewno chcesz usunąć bazę karty numer " + id + "? \n\nOperacji nie można cofnąć.", "Ważne", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
         if (result == DialogResult.Yes)
             using (SQLiteConnection conn = new SQLiteConnection(connString))
             {
                 conn.Open();
                 SQLiteCommand command = new SQLiteCommand(conn);
                 command.CommandText = "DELETE FROM [CardBase] WHERE id = @id";
                 command.Parameters.Add(new SQLiteParameter("@id", id));
                 command.ExecuteNonQuery();
                 conn.Close();
                 Odswierz();
                 if (dataGridView1.RowCount != 0)
                 {
                     if (index == dataGridView1.RowCount) dataGridView1.CurrentCell = dataGridView1.Rows[index - 1].Cells[0];
                     else dataGridView1.CurrentCell = dataGridView1.Rows[index].Cells[0];
                 }
             }
     }
 }

        private List<Result> QuerySqllite(Doc doc, string key)
        {
            string dbPath = "Data Source =" + doc.DBPath;
            SQLiteConnection conn = new SQLiteConnection(dbPath);
            conn.Open();
            string sql = GetQuerySqlByDocType(doc.DBType).Replace("{0}", key);
            SQLiteCommand cmdQ = new SQLiteCommand(sql, conn);
            SQLiteDataReader reader = cmdQ.ExecuteReader();

            List<Result> results = new List<Result>();
            while (reader.Read())
            {
                string name = reader.GetString(reader.GetOrdinal("name"));
                string docPath = reader.GetString(reader.GetOrdinal("path"));

                results.Add(new Result
                    {
                        Title = name,
                        SubTitle = doc.Name.Replace(".docset", ""),
                        IcoPath = doc.IconPath,
                        Action = (c) =>
                            {
                                string url = string.Format(@"{0}\{1}\Contents\Resources\Documents\{2}#{3}", docsetPath,
                                                           doc.Name+".docset", docPath, name);
                                string browser = GetDefaultBrowserPath();
                                Process.Start(browser, String.Format("\"file:///{0}\"", url));
                                return true;
                            }
                    });
            }

            conn.Close();

            return results;
        }