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);
}
private JToken DeserializeValue(ColumnDefinition column, object value)
{
if (value == null || value.Equals(DBNull.Value))
{
return(null);
}
string sqlType = column.StoreType;
JTokenType jsonType = column.JsonType;
if (sqlType == SqlColumnType.BigInt)
{
return(SqlHelpers.ParseInteger(jsonType, value));
}
if (sqlType == SqlColumnType.DateTime)
{
var date = (value as DateTime?).GetValueOrDefault();
if (date.Kind == DateTimeKind.Unspecified)
{
date = DateTime.SpecifyKind(date, DateTimeKind.Utc);
}
return(date);
}
if (sqlType == SqlColumnType.Double)
{
return(SqlHelpers.ParseDouble(jsonType, value));
}
if (sqlType == SqlColumnType.Bit)
{
return(SqlHelpers.ParseBoolean(jsonType, value));
}
if (sqlType == SqlColumnType.UniqueIdentifier)
{
return(SqlHelpers.ParseUniqueIdentier(jsonType, value));
}
if (sqlType == SqlColumnType.NText || sqlType == SqlColumnType.NVarchar)
{
return(SqlHelpers.ParseText(jsonType, value));
}
return(null);
}
public override QueryNode Visit(ConvertNode nodeIn)
{
this.sql.Append("CAST(");
QueryNode source = nodeIn.Source.Accept(this);
this.sql.Append(" AS ");
string sqlType = SqlHelpers.GetColumnType(nodeIn.TargetType);
this.sql.Append(sqlType);
this.sql.Append(")");
if (source != nodeIn.Source)
{
return(new ConvertNode(source, nodeIn.TargetType));
}
return(nodeIn);
}
/// <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 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++;
}
}
private string GetStoreType(JProperty property)
{
return(SqlHelpers.GetColumnType(property.Value.Type, allowNull: false));
}
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.SqlCeStore_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 insertSqlBase = String.Format(
"INSERT INTO {0} ({1}) VALUES ",
SqlHelpers.FormatTableName(tableName),
String.Join(", ", columns.Select(c => c.Name).Select(SqlHelpers.FormatMember))
);
var updateSqlBase = String.Format("UPDATE {0} SET ", SqlHelpers.FormatTableName(tableName));
// 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.SqlCeStore_TooManyColumns, MaxParametersPerUpsertQuery));
}
foreach (var batch in items.Split(maxLength: batchSize))
{
StringBuilder sql = new StringBuilder();
var parameters = new Dictionary <string, object>();
foreach (JObject item in batch)
{
// there's no upsert in SQL CE so we'll settle if an 'If Not Exist, Insert' approach
if (!RowExists(SqlHelpers.FormatTableName(tableName),
item.GetValue(MobileServiceSystemColumns.Id, StringComparison.OrdinalIgnoreCase).ToString()))
{
sql = new StringBuilder(insertSqlBase);
AppendInsertValuesSql(sql, parameters, columns, item);
}
else
{
sql = new StringBuilder(updateSqlBase);
AppendUpdateValuesSql(sql, parameters, columns, item);
string updateCondition = string.Format(" WHERE {0} = '{1}'", MobileServiceSystemColumns.Id, item.GetValue(MobileServiceSystemColumns.Id, StringComparison.OrdinalIgnoreCase).ToString());
sql.Append(updateCondition);
}
if (parameters.Any())
{
this.ExecuteNonQuery(sql.ToString(), parameters);
}
}
}
return(Task.FromResult(0));
}
public static object SerializeValue(JValue value, bool allowNull)
{
string columnType = SqlHelpers.GetColumnType(value.Type, allowNull);
return(SerializeValue(value, columnType, value.Type));
}
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()));
}
