How to Import from Another Database¶

Many programs keep their video or movie list in a database: a media manager, a collection tracker, an asset system of your own. If you can read that database from C#, a script can move the records into a catalog. This article walks through the shipped ImportDatabase sample, which reads a SQLite database. Load it in the Script Console from the scripts\ImportDatabase folder in the program folder, or copy the code from the sample page.

Which databases¶

The sample uses Microsoft.Data.Sqlite, which Fast Video Cataloger ships and has loaded already, so SQLite files need nothing installed. Any database with an ADO.NET provider is read the same way; only the connection and command classes change:

A package the program does not ship goes into the program folder as a DLL and is loaded with a //css_ref line at the top of the script, for example //css_ref System.Data.OleDb;.

The database¶

The sample expects a table named movies with these columns:

Your database will have other names. Change the query and the column names in the script to match; nothing else depends on them.

The script, step by step¶

First the database file. The script takes it from the script argument, so a batch can pass it in, and asks with a file dialog when there is none:

string file = argument;
if (string.IsNullOrWhiteSpace(file))
{
    OpenFileDialog dlg = new OpenFileDialog();
    dlg.Filter = "SQLite database (*.sqlite;*.db;*.sqlite3)|*.sqlite;*.db;*.sqlite3|All files (*.*)|*.*";
    if (dlg.ShowDialog() != true)
        return;
    file = dlg.FileName;
}

The extra columns become extended properties. Registering them first makes them show up in the Video Details window, the search filter and the column picker:

var catalog = scripting.GetVideoCatalogService();
foreach (string property in new[] { "year", "studio", "trailer", "length", "comments" })
    catalog.SetPropertyMeta("video_property", property, "edit");

Then the database is opened read-only and the query runs. The using blocks close the reader and the connection whatever happens:

using (SqliteConnection connection = new SqliteConnection("Data Source=" + file + ";Mode=ReadOnly"))
{
    connection.Open();
    using (SqliteCommand command = connection.CreateCommand())
    {
        command.CommandText = "SELECT Name, LocalPath, description, url, year, studio, trailer, length, comments FROM movies";
        using (SqliteDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                ...
            }
        }
    }
}

Each row becomes a video. AddVideo returns the new video's id, which the extended properties are set on. A row whose file does not exist is skipped and named in the console, so the list of what did not import is there when the script ends:

string path = Text(reader, "LocalPath");
if (path == "" || !File.Exists(path))
{
    console.WriteLine("Skipped, file not found: " + path);
    skipped++;
    continue;
}

long video_id = catalog.AddVideo(path, Text(reader, "Name"), 0, Text(reader, "description"), 0, 0, Text(reader, "url"), null, 0, null, null);
foreach (string property in Properties)
{
    string value = Text(reader, property);
    if (value != "")
        catalog.SetVideoFileExtendedProperty(video_id, property, value);
}

Text is a small helper that returns a column as text and an empty string for NULL, which a database column often is:

static string Text(SqliteDataReader reader, string column)
{
    int i = reader.GetOrdinal(column);
    return reader.IsDBNull(i) ? "" : reader.GetValue(i).ToString();
}

Finally the user interface is refreshed so the new entries appear:

scripting.GetGUI().Refresh("");

After the import¶

AddVideo creates the catalog entry from the row alone. The new videos have no thumbnails until you select them in the Video Catalog window and choose Reindex file from the Index menu, which captures the thumbnails and reads the file's metadata the way adding the file would have.

If the source program stores keywords, ratings or actors as well, the same loop can carry them: AddVideoTag and TagVideo for keywords, the rating argument of AddVideo, and the actor calls of IVideoCatalogService.