internal virtual void CreateTableFromObject(string tableName, IEnumerable <ColumnDefinition> columns)
{
if (!TableExists(tableName))
{
//Id in Zumo is nvarchar(255)
String tblSql = string.Format("CREATE TABLE {0} ({1} nvarchar(255) PRIMARY KEY)",
SqlHelpers.FormatTableName(tableName), SqlHelpers.FormatMember(MobileServiceSystemColumns.Id));
this.ExecuteNonQuery(tblSql, parameters: null);
}
var sql = string.Format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '{0}'", tableName);
IDictionary <string, JObject> existingColumns = this.ExecuteQuery((TableDefinition)null, sql, parameters: null)
.ToDictionary(c => c.Value <string>("COLUMN_NAME"), StringComparer.OrdinalIgnoreCase);
// new columns that do not exist in existing columns of table
var columnsToCreate = columns.Where(c => !existingColumns.ContainsKey(c.Name));
foreach (ColumnDefinition column in columnsToCreate)
{
string createSql = string.Format("ALTER TABLE {0} ADD {1} {2}",
SqlHelpers.FormatTableName(tableName),
SqlHelpers.FormatMember(column.Name),
column.StoreType);
this.ExecuteNonQuery(createSql, parameters: null);
}
//TODO: Should we allow dropping columns?
}
public override QueryNode Visit(MemberAccessNode nodeIn)
{
string memberName = SqlHelpers.FormatMember(nodeIn.MemberName);
this.sql.Append(memberName);
return(nodeIn);
}
public string FormatDelete()
{
var delQuery = this.query.Clone(); // create a copy to avoid modifying the original
delQuery.Selection.Clear();
delQuery.Selection.Add(MobileServiceSystemColumns.Id);
delQuery.IncludeTotalCount = false;
var formatter = new SqlQueryFormatter(delQuery);
string selectIdQuery = formatter.FormatSelect();
string idMemberName = SqlHelpers.FormatMember(MobileServiceSystemColumns.Id);
string tableName = SqlHelpers.FormatTableName(delQuery.TableName);
string command = string.Format("DELETE FROM {0} WHERE {1} IN ({2})", tableName, idMemberName, selectIdQuery);
this.Parameters = formatter.Parameters;
return(command);
}
private static void AppendUpdateValuesSql(StringBuilder sql, Dictionary <string, object> parameters, List <ColumnDefinition> columns, JObject item)
{
int colCount = 0;
foreach (var column in columns)
{
if (colCount > 0)
{
sql.Append(",");
}
JToken rawValue = item.GetValue(column.Name, StringComparison.OrdinalIgnoreCase);
object value = SqlHelpers.SerializeValue(rawValue, column.StoreType, column.JsonType);
//The paramname for this field must be unique within this statement
string paramName = "@p" + parameters.Count;
sql.Append(SqlHelpers.FormatMember(column.Name) + " = " + paramName);
parameters[paramName] = value;
colCount++;
}
}
public string FormatSelect()
{
var command = new StringBuilder("SELECT ");
// has top but not skip, use SELECT TOP
if (this.query.Top.HasValue && query.Top.Value > 0 && !this.query.Skip.HasValue)
{
command.AppendFormat(" TOP {0} ", this.query.Top.Value);
}
if (this.query.Selection.Any())
{
string columnNames = String.Join(", ", this.query.Selection.Select(c => SqlHelpers.FormatMember(c)));
command.Append(columnNames);
}
else
{
command.Append("*");
}
return(FormatQuery(command.ToString()));
}
