public object Execute(DatabaseRouteData data)
{
IDatabaseCommand command;
if (!Commands.TryGetValue(data.Method, out command))
{
throw new NotSupportedException("The specified HTTP method is not supported by the Restful Database Layer.");
}
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
EnsureTableExists(connection, data.AppId, data.Table);
if (data.NavigationTable != null)
{
EnsureTableExists(connection, data.AppId, data.NavigationTable);
}
var response = command.Execute(connection, data);
connection.Close();
return(response);
}
}
public object Execute(SqlConnection connection, DatabaseRouteData data)
{
var table = this.GetTableName(data);
var query = $"DELETE FROM {table} WHERE [Id] = @ID";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@ID", data.Key);
command.ExecuteNonQuery();
}
return(null);
}
public object Execute(SqlConnection connection, DatabaseRouteData data)
{
var table = this.GetTableName(data);
if (data.NavigationTable != null)
{
return(this.ReadJoinedRecords(connection, data.AppId, data.Table, data.NavigationTable, data.Key));
}
else if (data.Key != null)
{
return(this.ReadRecord(connection, table, data.Key));
}
else
{
return(this.ReadRecords(connection, table));
}
}
public object Execute(SqlConnection connection, DatabaseRouteData data)
{
var table = this.GetTableName(data);
var keys = string.Join(",", data.Data.Select(i => $"\"{i.Key}\""));
var parameters = string.Join(",", data.Data.Select(i => "@" + i.Key));
var query = $"INSERT INTO {table} ({keys}) VALUES ({parameters}); SELECT SCOPE_IDENTITY();";
using (var command = new SqlCommand(query, connection))
{
foreach (var item in data.Data)
{
command.Parameters.AddWithValue("@" + item.Key, item.Value);
}
return(command.ExecuteScalar());
}
}
public object Execute(SqlConnection connection, DatabaseRouteData data)
{
var table = this.GetTableName(data);
var parameters = string.Join(",", data.Data.Select(i => $"{i.Key}=@{i.Key}"));
var query = $"UPDATE {table} SET {parameters} WHERE [Id] = @ID;";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@ID", data.Key);
foreach (var item in data.Data)
{
command.Parameters.AddWithValue("@" + item.Key, item.Value);
}
return(command.ExecuteScalar());
}
}
public static string GetTableName(this IDatabaseCommand command, DatabaseRouteData data)
{
return($"[{data.AppId}].[{data.Table}]");
}