private static DbCommand BasicFindTileCommand(DbConnection connection,
DecorateDbObjects qualifier, String schema, String table)
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = String.Format("SELECT [SIZE], [IMAGE] FROM {0} WHERE ({1}=@Level AND {2}=@Row AND {3}=@Col);",
qualifier(schema, table), qualifier(table, "Level"), qualifier(table, "Row"),
qualifier(table, "Col"));
DbParameter par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Level";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Row";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Col";
cmd.Parameters.Add(par);
return(cmd);
}
//private static DbCommand AddTileCommand(DbConnection connection,
// DecorateDbObjects qualifier, String schema, String table, char parameterPrefix = ':')
//{
// /*
// DbCommand cmd = connection.CreateCommand();
// cmd.CommandText = String.Format(
// "INSERT INTO {0} VALUES(:Level, :Col, :Row, :Image);", qualifier(schema, table));
// DbParameter par = cmd.CreateParameter();
// par.DbType = DbType.Int32;
// par.ParameterName = "Level";
// cmd.Parameters.Add(par);
// par = cmd.CreateParameter();
// par.DbType = DbType.Int32;
// par.ParameterName = "Row";
// cmd.Parameters.Add(par);
// par = cmd.CreateParameter();
// par.DbType = DbType.Int32;
// par.ParameterName = "Col";
// cmd.Parameters.Add(par);
// par = cmd.CreateParameter();
// par.DbType = DbType.Binary;
// par.ParameterName = "Image";
// cmd.Parameters.Add(par);
// return cmd;
// */
// throw new InvalidOperationException("Removing tiles from MbTiles is not allowed");
//}
//private static DbCommand RemoveTileCommand(DbConnection connection,
// DecorateDbObjects qualifier, String schema, String table, char parameterPrefix = ':')
//{
// /*
// DbCommand cmd = connection.CreateCommand();
// cmd.CommandText = string.Format("DELETE FROM {0} WHERE ({1}=:Level AND {3}=:Col AND {2}=:Row);",
// qualifier(schema, table), qualifier(table, "zoom_level"), qualifier(table, "tile_row"),
// qualifier(table, "tile_col"));
// DbParameter par = cmd.CreateParameter();
// par.DbType = DbType.Int32;
// par.ParameterName = "Level";
// cmd.Parameters.Add(par);
// par = cmd.CreateParameter();
// par.DbType = DbType.Int32;
// par.ParameterName = "Row";
// cmd.Parameters.Add(par);
// par = cmd.CreateParameter();
// par.DbType = DbType.Int32;
// par.ParameterName = "Col";
// cmd.Parameters.Add(par);
// return cmd;
// */
// throw new InvalidOperationException("Removing tiles from MbTiles is not allowed");
//}
private static DbCommand FindTileCommand(DbConnection connection,
DecorateDbObjects qualifier, String schema, String table, char parameterPrefix = ':')
{
var cmd = connection.CreateCommand();
cmd.CommandText = String.Format("SELECT {0} FROM {1} WHERE ({2}={5}Level AND {4}={5}Col AND {3}={5}Row);",
qualifier(table, "tile_data"), qualifier(schema, table), qualifier(table, "zoom_level"), qualifier(table, "tile_row"),
qualifier(table, "tile_column"), parameterPrefix);
DbParameter par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Level";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Row";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Col";
cmd.Parameters.Add(par);
return(cmd);
}
public DbCache(TConnection connection, DecorateDbObjects decorator, String schema, String table, AddTileCommand atc, RemoveTileCommand rtc, FindTileCommand ftc)
{
Connection = connection;
Schema = schema;
Table = table;
_decorator = decorator;
if (atc != null)
{
_addTileCommand = atc(connection, decorator, schema, table);
}
if (rtc != null)
{
_removeTileCommand = rtc(connection, decorator, schema, table);
}
if (ftc == null)
{
ftc = BasicFindTileCommand;
}
_findTileCommand = ftc(connection, decorator, schema, table);
_bank = new BankOfSelectTileCommands(_findTileCommand);
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
}
private static DbCommand BasicRemoveTileCommand(DbConnection connection,
DecorateDbObjects qualifier, String schema, String table, char parameterPrefix = '@')
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = string.Format("DELETE FROM {0} WHERE ({1}={4}Level AND {2}={4}Row AND {3}={4}Col);",
qualifier(schema, table), qualifier(table, "Level"), qualifier(table, "Row"),
qualifier(table, "Col"), parameterPrefix);
DbParameter par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Level";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Row";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Col";
cmd.Parameters.Add(par);
return(cmd);
}
public DbCache(TConnection connection, DecorateDbObjects decorator, String schema, String table, AddTileCommand atc, RemoveTileCommand rtc, FindTileCommand ftc)
{
Connection = connection;
Schema = schema;
Table = table;
_decorator = decorator;
_addTileCommand = atc(connection, decorator, schema, table);
_removeTileCommand = rtc(connection, decorator, schema, table);
_findTileCommand = ftc(connection, decorator, schema, table);
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
}
private static DbCommand BasicAddTileCommand(DbConnection connection,
DecorateDbObjects qualifier, String schema, String table)
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = String.Format(
"INSERT INTO {0} VALUES(@Level, @Row, @Col, @Size, @Image);", qualifier(schema, table));
DbParameter par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Level";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Row";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Col";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Size";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Object;
par.ParameterName = "Image";
cmd.Parameters.Add(par);
return(cmd);
}
private static DbCommand BasicAddTileCommand(DbConnection connection,
DecorateDbObjects qualifier, String schema, String table, char parameterPrefix = '@')
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = String.Format(
"INSERT INTO {0} VALUES({1}Level, {1}Row, {1}Col, {1}Size, {1}Image);", qualifier(schema, table), parameterPrefix);
DbParameter par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Level";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Row";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Col";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Size";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Binary;
par.ParameterName = "Image";
cmd.Parameters.Add(par);
return(cmd);
}
private static DbCommand SqlCeAddTileCommand(DbConnection connection,
DecorateDbObjects qualifier, String schema, String table, char parameterPrefix)
{
var cmd = ((SqlCeConnection)connection).CreateCommand();
cmd.CommandText = String.Format(
"INSERT INTO {0} VALUES({1}Level, {1}row, {1}Col, {1}Size, {1}Image);", qualifier(schema, table), parameterPrefix);
SqlCeParameter par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Level";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Row";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Col";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.DbType = DbType.Int32;
par.ParameterName = "Size";
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.SqlDbType = SqlDbType.Image;
par.ParameterName = "Image";
cmd.Parameters.Add(par);
return(cmd);
}
public DbCache(TConnection connection, DecorateDbObjects qualifier, String schema, String table)
: this(connection, qualifier, schema, table, BasicAddTileCommand, BasicRemoveTileCommand, BasicFindTileCommand)
{
}