Movie Navigator

using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using MovieNavigator.Core.DbContext;
using MovieNavigator.Core.Services;

namespace MovieNavigator.Core
{
    public class SearchStatisticsService : ISearchStatisticsService
    {
        private string TableName = "SearchStatistics";

        public IEnumerable<SearchMovieItem> GetRecentSearchList(int  maxItems = 5)
        {
            var result = new List<SearchMovieItem>();
            using (var dbConnection = DatabaseService.SqlConn())
            {
                return dbConnection.Query<SearchMovieItem>($"SELECT * FROM {DbTableName.SearchStatistics.ToName()} ORDER BY {TableSearchStatistics.SearchDate.ToName()} DESC LIMIT {maxItems}");
            }
        }

        public void CreateSearchStatistics(string movieTitle, int foundItems)
        {
            using (var cmd = new SQLiteCommand($"INSERT INTO {DbTableName.SearchStatistics.ToName()} ({TableSearchStatistics.Query.ToName()}, {TableSearchStatistics.FoundItems.ToName()}) VALUES (@Query, @FoundItems)"))
            {
                cmd.Parameters.Add(new SQLiteParameter("@Query", movieTitle));
                cmd.Parameters.Add(new SQLiteParameter("@FoundItems", foundItems));
                DatabaseService.ExecuteCommand(cmd);
            }
        }

        public void UpdateSearchStatistics(string movieTitle, int foundItems)
        {
            using (var cmd = new SQLiteCommand($"UPDATE {DbTableName.SearchStatistics.ToName()} SET {TableSearchStatistics.Query.ToName()} = @Query, {TableSearchStatistics.FoundItems.ToName()} = @FoundItems, {TableSearchStatistics.SearchDate.ToName()} = @Date WHERE Query = @Query"))
            {
                cmd.Parameters.Add(new SQLiteParameter("@Query", movieTitle));
                cmd.Parameters.Add(new SQLiteParameter("@FoundItems", foundItems));
                cmd.Parameters.Add(new SQLiteParameter("@Date", DateTime.Now));
                DatabaseService.ExecuteCommand(cmd);
            }
        }

        public bool IsSearchExists(string movieTitle)
        {
            var result = false;
            using (var dbConnection = DatabaseService.SqlConn())
            {
                var sql = $"SELECT Id FROM {DbTableName.SearchStatistics.ToName()} WHERE {TableSearchStatistics.Query.ToName()} = @Query";
                var data = dbConnection.QueryFirstOrDefault<SearchMovieItem>(sql, new { Query = movieTitle });
                if (data != null)
                    result = data.Id > 0;
            }

            return result;
        }
    }
}

.NET Framework

Project Request

ProjectC

Vehicle Maintenance

Movie Navigator

Customers Exchange

ATM

.NET Core

Mail Daemon

Custom

Buns of code

PHP

Mediabox

Loan Castle

Content Management

Laravel

Phone Check

Frontend

Forex Wall

Business projects

Certification Center

Amerikas Auto

Pet projects

Geolocation Assistant

Web Dynamics

Blackball

Good old times

DOS Clock

BrainOut

Airport Administrator

Atomizer

Movie Navigator