internal virtual void CreateTableFromObject(string tableName, IEnumerable <ColumnDefinition> columns)
{
ColumnDefinition idColumn = columns.FirstOrDefault(c => c.Name.Equals(MobileServiceSystemColumns.Id));
var colDefinitions = columns.Where(c => c != idColumn).Select(c =>
$"{SqlHelpers.FormatMember(c.Name)} {c.StoreType}").ToList();
if (idColumn != null)
{
colDefinitions.Insert(0, $"{SqlHelpers.FormatMember(idColumn.Name)} {idColumn.StoreType} PRIMARY KEY");
}
String tblSql =
$"CREATE TABLE IF NOT EXISTS {SqlHelpers.FormatTableName(tableName)} ({String.Join(", ", colDefinitions)})";
ExecuteNonQueryInternal(tblSql, parameters: null);
string infoSql = $"PRAGMA table_info({SqlHelpers.FormatTableName(tableName)});";
IDictionary <string, JObject> existingColumns = ExecuteQueryInternal("table_info", (TableDefinition)null, infoSql, parameters: null)
.ToDictionary(c => c.Value <string>("name"), StringComparer.OrdinalIgnoreCase);
// new columns that do not exist in existing columns
var columnsToCreate = columns.Where(c => !existingColumns.ContainsKey(c.Name));
foreach (ColumnDefinition column in columnsToCreate)
{
string createSql =
$"ALTER TABLE {SqlHelpers.FormatTableName(tableName)} ADD COLUMN {SqlHelpers.FormatMember(column.Name)} {column.StoreType}";
ExecuteNonQueryInternal(createSql, parameters: null);
}
// NOTE: In SQLite you cannot drop columns, only add them.
}
private void BatchUpdate(string tableName, IEnumerable <JObject> items, List <ColumnDefinition> columns)
{
if (columns.Count <= 1)
{
return; // For update to work there has to be at least once column besides Id that needs to be updated
}
ValidateParameterCount(columns.Count);
string sqlBase = $"UPDATE {SqlHelpers.FormatTableName(tableName)} SET ";
foreach (JObject item in items)
{
var sql = new StringBuilder(sqlBase);
var parameters = new Dictionary <string, object>();
ColumnDefinition idColumn = columns.FirstOrDefault(c => c.Name.Equals(MobileServiceSystemColumns.Id));
if (idColumn == null)
{
continue;
}
foreach (var column in columns.Where(c => c != idColumn))
{
string paramName = AddParameter(item, parameters, column);
sql.AppendFormat("{0} = {1}", SqlHelpers.FormatMember(column.Name), paramName);
sql.Append(",");
}
if (parameters.Any())
{
sql.Remove(sql.Length - 1, 1); // remove the trailing comma
}
sql.AppendFormat(" WHERE {0} = {1}", SqlHelpers.FormatMember(MobileServiceSystemColumns.Id), AddParameter(item, parameters, idColumn));
ExecuteNonQueryInternal(sql.ToString(), parameters);
}
}
