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())); }