コード例 #1
0
        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);
            }
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        private JToken DeserializeValue(ColumnDefinition column, object value)
        {
            if (value == null || value.Equals(DBNull.Value))
            {
                return(null);
            }

            string     sqlType  = column.StoreType;
            JTokenType jsonType = column.JsonType;

            switch (sqlType)
            {
            case SqlColumnType.BigInt:
                return(SqlHelpers.ParseInteger(jsonType, value));

            case SqlColumnType.DateTime:
                var date = (value as DateTime?).GetValueOrDefault();

                if (date.Kind == DateTimeKind.Unspecified)
                {
                    date = DateTime.SpecifyKind(date, DateTimeKind.Utc);
                }
                return(date);

            case SqlColumnType.Double:
                return(SqlHelpers.ParseDouble(jsonType, value));

            case SqlColumnType.Bit:
                return(SqlHelpers.ParseBoolean(jsonType, value));

            case SqlColumnType.UniqueIdentifier:
                return(SqlHelpers.ParseUniqueIdentier(jsonType, value));

            case SqlColumnType.NVarcharMax:
                return(SqlHelpers.ParseText(jsonType, value));

            case SqlColumnType.VarBinaryMax:
                return(SqlHelpers.ParseBytes(jsonType, value));

            default:
                return(null);
            }
        }
コード例 #4
0
        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);
        }
コード例 #5
0
        /// <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()));
        }
コード例 #6
0
 private string GetStoreType(JProperty property)
 {
     return(SqlHelpers.GetColumnType(property.Value.Type, allowNull: false));
 }
コード例 #7
0
        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));
        }
コード例 #8
0
        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()));
        }
コード例 #9
0
ファイル: SqlHelpers.cs プロジェクト: phaufe/ZumoContrib
        public static object SerializeValue(JValue value, bool allowNull)
        {
            string columnType = SqlHelpers.GetColumnType(value.Type, allowNull);

            return(SerializeValue(value, columnType, value.Type));
        }