186 lines
9.4 KiB
C#
186 lines
9.4 KiB
C#
|
using Microsoft.Data.Sqlite;
|
|||
|
using Newtonsoft.Json;
|
|||
|
using RestSharp;
|
|||
|
using System;
|
|||
|
using System.Collections.Generic;
|
|||
|
using System.Globalization;
|
|||
|
using System.Linq;
|
|||
|
using System.Text;
|
|||
|
using System.Threading.Tasks;
|
|||
|
|
|||
|
namespace SQLiteTest {
|
|||
|
class Program {
|
|||
|
const string APIKey = "badd41f2c2c9879186a8082b1d16fd69";
|
|||
|
const string Language = "en-US";
|
|||
|
const string filename = @"C:\Users\micha\source\repos\SQLiteTest\MovieDatabase.db";
|
|||
|
const string CreateGenreTable = @"CREATE TABLE `Genres` (
|
|||
|
`ID` INTEGER NOT NULL UNIQUE,
|
|||
|
`Name` TEXT NOT NULL,
|
|||
|
PRIMARY KEY(`ID`)
|
|||
|
);";
|
|||
|
static void Main(string[] args) {
|
|||
|
RefreshGenreList();
|
|||
|
Random rnd = new Random();
|
|||
|
int random = rnd.Next(999999);
|
|||
|
Movie newMovie = new Movie() { ID = random, Title = "Movie no. " + random.ToString(), Overview = "The Inception is amazing!", ReleaseDate = null, Adult = true, ImdbID = "tt" + rnd.Next(0000000, 9999999), Status = "Released", Runtime = 148 };
|
|||
|
AddMovie(in newMovie);
|
|||
|
foreach (Movie movie in QueryMovies())
|
|||
|
Console.WriteLine(movie.ToString());
|
|||
|
Console.ReadKey();
|
|||
|
}
|
|||
|
|
|||
|
private static void RefreshGenreList() {
|
|||
|
var client = new RestClient($"https://api.themoviedb.org/3/genre/movie/list?language={Language}&api_key={APIKey}");
|
|||
|
var request = new RestRequest(Method.GET);
|
|||
|
request.AddParameter("undefined", "{}", ParameterType.RequestBody);
|
|||
|
IRestResponse response = client.Execute(request);
|
|||
|
try {
|
|||
|
dynamic responseObject = JsonConvert.DeserializeObject<dynamic>(response.Content);
|
|||
|
if (!(responseObject.status_message is null))
|
|||
|
Console.WriteLine("Status: {0}", responseObject.status_message);
|
|||
|
Dictionary<int, string> genres = new Dictionary<int, string>();
|
|||
|
foreach (var a in responseObject.genres) {
|
|||
|
genres.Add((int)a.id, (string)a.name);
|
|||
|
}
|
|||
|
} catch (Exception e) {
|
|||
|
Console.WriteLine("Response could not be parsed: {0}", e.Message);
|
|||
|
}
|
|||
|
if (!response.IsSuccessful) {
|
|||
|
Console.WriteLine("Request failed: {0}", response.ErrorMessage);
|
|||
|
return;
|
|||
|
}
|
|||
|
}
|
|||
|
|
|||
|
private static IEnumerable<Movie> QueryMovies() {
|
|||
|
const string tableCommand = "SELECT * FROM Movies";
|
|||
|
using (SqliteConnection db = new SqliteConnection($"Data Source={filename};")) {
|
|||
|
try {
|
|||
|
db.Open();
|
|||
|
} catch (SqliteException e) {
|
|||
|
Console.WriteLine("Database connection failed: {0}", e.Message);
|
|||
|
yield break;
|
|||
|
}
|
|||
|
using (SqliteCommand readTableCommand = new SqliteCommand(tableCommand, db)) {
|
|||
|
SqliteDataReader query;
|
|||
|
try {
|
|||
|
query = readTableCommand.ExecuteReader();
|
|||
|
} catch (SqliteException e) {
|
|||
|
Console.WriteLine("Query failed: {0}", e.Message);
|
|||
|
yield break;
|
|||
|
}
|
|||
|
while (query.Read()) {
|
|||
|
yield return new Movie(ref query);
|
|||
|
}
|
|||
|
}
|
|||
|
}
|
|||
|
}
|
|||
|
|
|||
|
private static void Query(in string command) {
|
|||
|
using (SqliteConnection db = new SqliteConnection($"Data Source={filename};")) {
|
|||
|
try {
|
|||
|
db.Open();
|
|||
|
} catch (SqliteException e) {
|
|||
|
Console.WriteLine("Database connection failed: {0}", e.Message);
|
|||
|
return;
|
|||
|
}
|
|||
|
using (SqliteCommand insertCommand = new SqliteCommand(command, db)) {
|
|||
|
SqliteDataReader query;
|
|||
|
try {
|
|||
|
query = insertCommand.ExecuteReader();
|
|||
|
} catch (SqliteException e) {
|
|||
|
Console.WriteLine("Query failed: {0}", e.Message);
|
|||
|
return;
|
|||
|
}
|
|||
|
}
|
|||
|
try {
|
|||
|
db.Close();
|
|||
|
} catch (SqliteException e) {
|
|||
|
Console.WriteLine("Database closing failed: {0}", e.Message);
|
|||
|
return;
|
|||
|
}
|
|||
|
}
|
|||
|
Console.WriteLine("Query successful!");
|
|||
|
}
|
|||
|
private static void AddMovie(in Movie movie) {
|
|||
|
using (SqliteConnection db = new SqliteConnection($"Data Source={filename};")) {
|
|||
|
try {
|
|||
|
db.Open();
|
|||
|
} catch (SqliteException e) {
|
|||
|
Console.WriteLine("Database connection failed: {0}", e.Message);
|
|||
|
return;
|
|||
|
}
|
|||
|
using (SqliteCommand insertCommand = new SqliteCommand()) {
|
|||
|
insertCommand.Connection = db;
|
|||
|
insertCommand.CommandText = "INSERT INTO `Movies`(`ID`,`Title`,`Tagline`,`Overview`,`BackdropPath`,`PosterPath`,`ImdbID`,`Adult`,`Budget`,`Genres`,`Popularity`,`ReleaseDate`,`Revenue`,`Runtime`,`Status`) VALUES (@ID, @Title, @Tagline, @Overview, @BackdropPath, @PosterPath, @ImdbID, @Adult, @Budget, @Genres, @Popularity, @ReleaseDate, @Revenue, @Runtime, @Status);";
|
|||
|
#region Params
|
|||
|
insertCommand.Parameters.AddWithValue("@ID", movie.ID);
|
|||
|
insertCommand.Parameters.AddWithValue("@Title", movie.Title);
|
|||
|
insertCommand.Parameters.AddWithValue("@Overview", movie.Overview);
|
|||
|
if (string.IsNullOrWhiteSpace(movie.Tagline))
|
|||
|
insertCommand.Parameters.AddWithValue("@Tagline", DBNull.Value);
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@Tagline", movie.Tagline);
|
|||
|
if (string.IsNullOrWhiteSpace(movie.BackdropPath))
|
|||
|
insertCommand.Parameters.AddWithValue("@BackdropPath", DBNull.Value);
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@BackdropPath", movie.BackdropPath);
|
|||
|
if (string.IsNullOrWhiteSpace(movie.PosterPath))
|
|||
|
insertCommand.Parameters.AddWithValue("@PosterPath", DBNull.Value);
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@PosterPath", movie.PosterPath);
|
|||
|
if (string.IsNullOrWhiteSpace(movie.ImdbID))
|
|||
|
insertCommand.Parameters.AddWithValue("@ImdbID", DBNull.Value);
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@ImdbID", movie.ImdbID);
|
|||
|
if (string.IsNullOrWhiteSpace(movie.Status))
|
|||
|
insertCommand.Parameters.AddWithValue("@Status", DBNull.Value);
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@Status", movie.Status);
|
|||
|
if (string.IsNullOrWhiteSpace(movie.Genres))
|
|||
|
insertCommand.Parameters.AddWithValue("@Genres", DBNull.Value);
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@Genres", movie.Genres);
|
|||
|
if (movie.Adult.HasValue)
|
|||
|
insertCommand.Parameters.AddWithValue("@Adult", movie.Adult.Value ? "1" : "0");
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@Adult", DBNull.Value);
|
|||
|
if (movie.Budget.HasValue)
|
|||
|
insertCommand.Parameters.AddWithValue("@Budget", movie.Budget.Value.ToString());
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@Budget", DBNull.Value);
|
|||
|
if (movie.Popularity.HasValue)
|
|||
|
insertCommand.Parameters.AddWithValue("@Popularity", movie.Popularity.Value.ToString());
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@Popularity", DBNull.Value);
|
|||
|
if (movie.Runtime.HasValue)
|
|||
|
insertCommand.Parameters.AddWithValue("@Runtime", movie.Runtime.Value.ToString());
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@Runtime", DBNull.Value);
|
|||
|
if (movie.Revenue.HasValue)
|
|||
|
insertCommand.Parameters.AddWithValue("@Revenue", movie.Revenue.Value.ToString());
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@Revenue", DBNull.Value);
|
|||
|
if (movie.ReleaseDate.HasValue)
|
|||
|
insertCommand.Parameters.AddWithValue("@ReleaseDate", movie.ReleaseDate.Value.ToString("yyyy-MM-dd HH:mm:ss.fff"));
|
|||
|
else
|
|||
|
insertCommand.Parameters.AddWithValue("@ReleaseDate", DBNull.Value);
|
|||
|
#endregion
|
|||
|
SqliteDataReader query;
|
|||
|
try {
|
|||
|
query = insertCommand.ExecuteReader();
|
|||
|
} catch (SqliteException e) {
|
|||
|
Console.WriteLine("Insertion failed: {0}", e.Message);
|
|||
|
return;
|
|||
|
}
|
|||
|
}
|
|||
|
try {
|
|||
|
db.Close();
|
|||
|
} catch (SqliteException e) {
|
|||
|
Console.WriteLine("Database closing failed: {0}", e.Message);
|
|||
|
return;
|
|||
|
}
|
|||
|
}
|
|||
|
}
|
|||
|
}
|
|||
|
}
|