public static VideoTagCollection GetVideoTags(VideoSeries _series)
{
var tags = new VideoTagCollection();
using (var command = new SQLiteCommand())
{
command.CommandText = "Select [Id], [Vid], [Text], [Intensity] From [VideoTags] Where [Deleted] = 0 and [Series] = @series;";
command.Parameters.Add(new SQLiteParameter("@series")
{
DbType = DbType.String, Value = _series.Series
});
using (var reader = accesser.ExecuteReader(command))
{
if (!reader.HasRows)
{
return(tags);
}
while (reader.Read())
{
VideoTag tag = new VideoTag(_series);
tag.Id = reader.GetInt32(0);
tag.Vid = reader.GetString(1);
tag.Text = reader.GetString(2);
tag.Intensity = reader.GetIntensity(3);
tags.Add(tag);
}
}
}
return(tags);
}
public static VideoRecordCollection GetVideoRecords(VideoSeries _series)
{
var records = new VideoRecordCollection();
using (var command = new SQLiteCommand())
{
command.CommandText = "Select [Vid],[File_Path],[File_Name],[File_Extention],[File_Size],[Alias],[Alt_Alias],[Series],[Alt_Series],[Icon],[Score],[Favorite],[Duration],[Intensity],[Last_playback],[Format],[Resolution],[Checksum],[Inserted] From [VideoRecords] Where [Deleted] = 0 and [Series] = @series;";
command.Parameters.Add(new SQLiteParameter("@series")
{
DbType = DbType.String, Value = _series.Series
});
using (var reader = accesser.ExecuteReader(command))
{
if (!reader.HasRows)
{
return(records);
}
while (reader.Read())
{
VideoRecord record = new VideoRecord();
record.Vid = reader.GetString(0);
record.File_Path = reader.GetString(1);
record.File_Name = reader.GetString(2);
record.File_Extention = reader.GetString(3);
record.File_Size = reader.GetInt64(4);
record.Alias = reader.GetString(5);
record.Alt_Alias = reader.GetNullableString(6);
record.Series = reader.GetString(7);
record.Alt_Series = reader.GetNullableString(8);
record.Icon = reader.GetBitmap(9);
record.Score = reader.GetInt32(10);
record.Favorite = reader.GetBoolean(11);
record.Duration = reader.GetTimeSpan(12);
record.Intensity = reader.GetNullableString(13);
record.Last_playback = reader.GetDateTime(14);
record.Format = reader.GetNullableString(15);
record.Resolution = reader.GetNullableString(16);
record.Checksum = reader.GetNullableString(17);
record.Inserted = reader.GetDateTime(18);
records.Add(record);
}
}
}
return(records);
}
public static VideoSeriesCollection GetVideoSeries()
{
var collection = new VideoSeriesCollection();
using (var command = new SQLiteCommand())
{
command.CommandText = @"
Select [Series], [Alt_Series], [Icon], [Inserted]
From (
Select
Row_Number() Over (Partition by [Series] Order by Random()) as n
,[Series]
,[Alt_Series]
,[Icon]
,[Inserted]
From [VideoRecords]
Where Deleted = 0
) t1
Where n = 1;";
using (var reader = accesser.ExecuteReader(command))
{
if (!reader.HasRows)
{
return(collection);
}
while (reader.Read())
{
VideoSeries series = new VideoSeries();
series.Series = reader.GetString(0);
series.Alt_Series = reader.GetNullableString(1);
series.Icon = reader.GetBitmap(2);
series.Inserted = reader.GetDateTime(3);
collection.Add(series);
}
}
}
return(collection);
}
public static SearchEntityCollection GetSearchEntities(VideoSeries _series)
{
var entities = new SearchEntityCollection();
using (var command = new SQLiteCommand())
{
command.CommandText = @"Select distinct [Text] From [VideoTags] t1 Inner Join [VideoRecords] t2 on t1.[Vid] = t2.[Vid] Where t1.[Deleted] = 0 and t2.[Series] = @series;";
command.Parameters.Add(new SQLiteParameter("@series")
{
DbType = DbType.String, Value = _series.Series
});
using (var reader = accesser.ExecuteReader(command))
{
if (reader.HasRows)
{
while (reader.Read())
{
entities.Add(new SearchEntity("Tag", reader.GetString(0)));
}
}
}
}
using (var command = new SQLiteCommand())
{
command.CommandText = "Select distinct [Series] From [VideoRecords] Where [Deleted] = 0 and [Series] = @series;";
command.Parameters.Add(new SQLiteParameter("@series")
{
DbType = DbType.String, Value = _series.Series
});
using (var reader = accesser.ExecuteReader(command))
{
if (reader.HasRows)
{
while (reader.Read())
{
entities.Add(new SearchEntity("Series", reader.GetString(0)));
}
}
}
}
using (var command = new SQLiteCommand())
{
command.CommandText = "Select distinct [Alt_Series] From [VideoRecords] Where [Deleted] = 0 and [Series] = @series;";
command.Parameters.Add(new SQLiteParameter("@series")
{
DbType = DbType.String, Value = _series.Series
});
using (var reader = accesser.ExecuteReader(command))
{
if (reader.HasRows)
{
while (reader.Read())
{
entities.Add(new SearchEntity("Alt_Series", reader.GetNullableString(0)));
}
}
}
}
return(entities);
}
public VideoTag(VideoSeries _series)
{
this.VideoSeries = _series;
}
