/// <summary> /// Deletes items from local table with the given list of ids /// </summary> /// <param name="tableName">Name of the local table.</param> /// <param name="ids">A list of ids of the items to be deleted</param> /// <returns>A task that completes when delete query has executed.</returns> public override Task DeleteAsync(string tableName, IEnumerable <string> ids) { if (tableName == null) { throw new ArgumentNullException("tableName"); } if (ids == null) { throw new ArgumentNullException("ids"); } this.EnsureInitialized(); string idRange = String.Join(",", ids.Select((_, i) => "@id" + i)); string sql = string.Format("DELETE FROM {0} WHERE {1} IN ({2})", SqlHelpers.FormatTableName(tableName), MobileServiceSystemColumns.Id, idRange); var parameters = new Dictionary <string, object>(); int j = 0; foreach (string id in ids) { parameters.Add("@id" + (j++), id); } this.ExecuteNonQuery(sql, parameters); return(Task.FromResult(0)); }
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} ", //we make the other columns nullable SqlHelpers.FormatTableName(tableName), SqlHelpers.FormatMember(column.Name), column.StoreType); this.ExecuteNonQuery(createSql, parameters: null); } //TODO: Should we allow dropping columns? }
private string FormatQuery(string command) { Reset(); this.sql.Append(command); string tableName = SqlHelpers.FormatTableName(this.query.TableName); this.sql.AppendFormat(" FROM {0}", tableName); if (this.query.Filter != null) { this.FormatWhereClause(this.query.Filter); } if (this.query.Ordering.Count > 0) { this.FormatOrderByClause(this.query.Ordering); } //if (this.query.Skip.HasValue || this.query.Top.HasValue) //{ // this.FormatLimitClause(this.query.Top, this.query.Skip); //} // OFFSET with FETCH if (this.query.Skip.HasValue && query.Skip.Value > 0 && this.query.Top.HasValue && query.Top.Value > 0) { this.FormatLimitClause(this.query.Top.Value, this.query.Skip.Value); } return(GetSql()); }
private void FormatCountQuery() { string tableName = SqlHelpers.FormatTableName(this.query.TableName); this.sql.AppendFormat("SELECT COUNT(1) AS [count] FROM {0}", tableName); if (this.query.Filter != null) { this.FormatWhereClause(this.query.Filter); } }
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); }
/// <summary> /// Executes a lookup against a local table. /// </summary> /// <param name="tableName">Name of the local table.</param> /// <param name="id">The id of the item to lookup.</param> /// <returns>A task that will return with a result when the lookup finishes.</returns> public override Task <JObject> LookupAsync(string tableName, string id) { if (tableName == null) { throw new ArgumentNullException("tableName"); } if (id == null) { throw new ArgumentNullException("id"); } this.EnsureInitialized(); string sql = string.Format("SELECT * FROM {0} WHERE {1} = @id", SqlHelpers.FormatTableName(tableName), MobileServiceSystemColumns.Id); var parameters = new Dictionary <string, object> { { "@id", id } }; IList <JObject> results = this.ExecuteQuery(tableName, sql, parameters); return(Task.FromResult(results.FirstOrDefault())); }
private Task UpsertAsyncInternal(string tableName, IEnumerable <JObject> items, bool fromServer) { TableDefinition table = GetTable(tableName); var first = items.FirstOrDefault(); if (first == null) { return(Task.FromResult(0)); } // Get the columns which we want to map into the database. var columns = new List <ColumnDefinition>(); foreach (var prop in first.Properties()) { ColumnDefinition column; // If the column is coming from the server we can just ignore it, // otherwise, throw to alert the caller that they have passed an invalid column if (!table.TryGetValue(prop.Name, out column) && !fromServer) { throw new InvalidOperationException(string.Format(Properties.Resources.SqlStore_ColumnNotDefined, prop.Name, tableName)); } if (column != null) { columns.Add(column); } } if (columns.Count == 0) { // no query to execute if there are no columns in the table return(Task.FromResult(0)); } var mergeIntoSql = String.Format("MERGE INTO {0} AS Target ", SqlHelpers.FormatTableName(tableName)); var mergeAsSourceSql = string.Format(" AS SOURCE ({0}) ", String.Join(", ", columns.Select(c => c.Name).Select(SqlHelpers.FormatMember))); var mergeConditionSql = string.Format(" ON Target.{0} = Source.{1} ", MobileServiceSystemColumns.Id, MobileServiceSystemColumns.Id); var whenMatchedSql = String.Format(" WHEN MATCHED THEN UPDATE SET {0} ", String.Join(", ", columns.Select(c => c.Name).Select(c => SqlHelpers.FormatMember(c) + " = " + "Source." + SqlHelpers.FormatMember(c)))); var whenNotMatchedSql = String.Format(" WHEN NOT MATCHED BY TARGET THEN INSERT ({0}) VALUES ({1}); ", String.Join(", ", columns.Select(c => c.Name).Select(SqlHelpers.FormatMember)), String.Join(", ", columns.Select(c => " Source." + SqlHelpers.FormatMember(c.Name)))); // Use int division to calculate how many times this record will fit into our parameter quota int batchSize = MaxParametersPerUpsertQuery / columns.Count; if (batchSize == 0) { throw new InvalidOperationException(string.Format(Properties.Resources.SqlStore_TooManyColumns, MaxParametersPerUpsertQuery)); } foreach (var batch in items.Split(maxLength: batchSize)) { var mergeValues = new StringBuilder(); var parameters = new Dictionary <string, object>(); foreach (JObject item in batch) { AppendInsertValuesSql(mergeValues, parameters, columns, item); mergeValues.Append(","); } if (parameters.Any()) { mergeValues.Remove(mergeValues.Length - 1, 1); // remove the trailing comma var upsertSql = mergeIntoSql + string.Format("USING (VALUES {0})", mergeValues) + mergeAsSourceSql + mergeConditionSql + whenMatchedSql + whenNotMatchedSql; this.ExecuteNonQuery(upsertSql, parameters); } } return(Task.FromResult(0)); }