private void BatchInsert(string tableName, IEnumerable <JObject> items, List <ColumnDefinition> columns) { if (columns.Count == 0) // we need to have some columns to insert the item { return; } // Generate the prepared insert statement string sqlBase = String.Format( "INSERT OR IGNORE INTO {0} ({1}) VALUES ", SqlHelpers.FormatTableName(tableName), String.Join(", ", columns.Select(c => c.Name).Select(SqlHelpers.FormatMember)) ); // Use int division to calculate how many times this record will fit into our parameter quota int batchSize = ValidateParameterCount(columns.Count); foreach (var batch in items.Split(maxLength: batchSize)) { var sql = new StringBuilder(sqlBase); var parameters = new Dictionary <string, object>(); foreach (JObject item in batch) { AppendInsertValuesSql(sql, parameters, columns, item); sql.Append(","); } if (parameters.Any()) { sql.Remove(sql.Length - 1, 1); // remove the trailing comma this.ExecuteNonQuery(sql.ToString(), parameters); } } }
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 => String.Format("{0} {1}", SqlHelpers.FormatMember(c.Name), c.StoreType)).ToList(); if (idColumn != null) { colDefinitions.Insert(0, String.Format("{0} {1} PRIMARY KEY", SqlHelpers.FormatMember(idColumn.Name), idColumn.StoreType)); } String tblSql = string.Format("CREATE TABLE IF NOT EXISTS {0} ({1})", SqlHelpers.FormatTableName(tableName), String.Join(", ", colDefinitions)); this.ExecuteNonQuery(tblSql, parameters: null); string infoSql = string.Format("PRAGMA table_info({0});", SqlHelpers.FormatTableName(tableName)); IDictionary <string, JObject> existingColumns = this.ExecuteQuery((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 = string.Format("ALTER TABLE {0} ADD COLUMN {1} {2}", SqlHelpers.FormatTableName(tableName), SqlHelpers.FormatMember(column.Name), column.StoreType); this.ExecuteNonQuery(createSql, parameters: null); } // NOTE: In SQLite you cannot drop columns, only add them. }
/// <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 } }; return(this.operationSemaphore.WaitAsync() .ContinueWith(t => { try { IList <JObject> results = this.ExecuteQueryInternal(tableName, sql, parameters); return results.FirstOrDefault(); } finally { this.operationSemaphore.Release(); } })); }
/// <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)); }
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); } 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); } }
private void BatchDelete(string tableName, IEnumerable <string> ids) { var parameters = ids .Select((value, index) => (value, index)) .ToDictionary(pair => "@id" + pair.index, pair => (object)pair.value); string sql = string.Format("DELETE FROM {0} WHERE {1} IN ({2})", SqlHelpers.FormatTableName(tableName), MobileServiceSystemColumns.Id, string.Join(",", parameters.Keys)); this.ExecuteNonQueryInternal(sql, parameters); }
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> /// 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); } return(this.operationSemaphore.WaitAsync() .ContinueWith(t => { try { this.ExecuteNonQueryInternal(sql, parameters); } finally { this.operationSemaphore.Release(); } })); }
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 = String.Format("UPDATE {0} SET ", SqlHelpers.FormatTableName(tableName)); 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)); this.ExecuteNonQuery(sql.ToString(), parameters); } }
/// <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())); }