public void ExplicitlyTypedArrayType()
        {
            var parameter = new BigQueryParameter(BigQueryDbType.Array, new[] { new DateTimeOffset(new DateTime(2016, 10, 31), new TimeSpan(8, 30, 0)) });

            parameter.ArrayElementType = BigQueryDbType.DateTime;
            string actualJson = JsonConvert.SerializeObject(parameter.ToQueryParameter());

            var expectedResult = new QueryParameter
            {
                ParameterType = new QueryParameterType
                {
                    Type      = BigQueryDbType.Array.ToParameterApiType(),
                    ArrayType = new QueryParameterType {
                        Type = BigQueryDbType.DateTime.ToParameterApiType()
                    }
                },
                ParameterValue = new QueryParameterValue
                {
                    ArrayValues = new[] { new QueryParameterValue {
                                              Value = "2016-10-31 00:00:00"
                                          } }
                }
            };
            string expectedJson = JsonConvert.SerializeObject(expectedResult);

            Assert.Equal(actualJson, expectedJson);
        }
        public void ParameterValueTest()
        {
            var emptyParam = new BigQueryParameter();

            Assert.Null(emptyParam.Value);

            var paramInitializeValue = new BigQueryParameter(parameterName, DbType.String)
            {
                Value = stringValue
            };

            Assert.Equal(stringValue, paramInitializeValue.Value);

            var intParam = new BigQueryParameter(parameterName, DbType.Int64);

            Assert.Equal(default(long), intParam.Value);

            var stringParam = new BigQueryParameter(parameterName, DbType.String);

            Assert.Null(stringParam.Value);

            var objectParam = new BigQueryParameter(parameterName, DbType.Object);

            Assert.Null(objectParam.Value);
        }
        public void NullParameter_Legacy()
        {
            // Assumption: no other test inserts a row with a null player name.
            var client    = BigQueryClient.Create(_fixture.ProjectId);
            var table     = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId);
            var parameter = new BigQueryParameter("player", BigQueryDbType.String, "Angela");
            var command   = new BigQueryCommand($"SELECT score FROM {table} WHERE player=@player")
            {
                Parameters = { parameter }
            };
            var resultSet = client.ExecuteQuery(command).ReadPage(5);

            Assert.Equal(1, resultSet.Rows.Count);
            Assert.Equal(95, (long)resultSet.Rows[0]["score"]);

            // SQL rules: nothing equals null
            parameter.Value = null;
            resultSet       = client.ExecuteQuery(command).ReadPage(5);
            Assert.Equal(0, resultSet.Rows.Count);

            // But we should be able to find the null value this way.
            command.Sql = $"SELECT score FROM {table} WHERE player=@player OR (player IS NULL AND @player IS NULL)";
            resultSet   = client.ExecuteQuery(command).ReadPage(5);
            Assert.Equal(1, resultSet.Rows.Count);
            Assert.Equal(1, (long)resultSet.Rows[0]["score"]);
        }
    public void QueryWithTimestampParameters(string projectId = "project-id")
    {
        var timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeKind.Utc);

        // Note: Standard SQL is required to use query parameters.
        var query = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";

        // Initialize client that will be used to send requests.
        var client = BigQueryClient.Create(projectId);

        var parameters = new BigQueryParameter[]
        {
            new BigQueryParameter("ts_value", BigQueryDbType.Timestamp, timestamp),
        };

        var job = client.CreateQueryJob(
            sql: query,
            parameters: parameters,
            options: new QueryOptions {
            UseQueryCache = false
        });

        // Wait for the job to complete.
        job = job.PollUntilCompleted().ThrowOnAnyError();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine(row[0]);
        }
    }
        public void Constructor_TypeOnly()
        {
            var parameter = new BigQueryParameter(BigQueryDbType.String);

            Assert.Null(parameter.Name);
            Assert.Equal(BigQueryDbType.String, parameter.Type);
            Assert.Null(parameter.Value);
        }
        public void ValidateChangeIsnullableTest()
        {
            var param = new BigQueryParameter(parameterName, DbType.String);

            Assert.False(param.IsNullable);
            param.IsNullable = false;
            Assert.Throws <ArgumentOutOfRangeException>(() => param.IsNullable = true);
        }
        public void ToQueryParameter_Valid(string name, BigQueryParameter parameter, QueryParameter expectedResult)
        {
            // Positional vs named mode difference is only in validation, tested elsewhere.
            string actualJson   = JsonConvert.SerializeObject(parameter.ToQueryParameter());
            string expectedJson = JsonConvert.SerializeObject(expectedResult);

            Assert.Equal(actualJson, expectedJson);
        }
示例#8
0
        public void UnnamedParameterCannotBeConvertedWithNamedMode()
        {
            var parameter = new BigQueryParameter(BigQueryDbType.String, "foo");

            // Prove that it's fine for a positional parameter
            parameter.ToQueryParameter(BigQueryParameterMode.Positional);
            Assert.Throws <InvalidOperationException>(() => parameter.ToQueryParameter(BigQueryParameterMode.Named));
        }
        public void DirectionTypeTest()
        {
            var param = new BigQueryParameter(parameterName, DbType.String);

            Assert.Equal(ParameterDirection.Input, param.Direction);
            Assert.Throws <ArgumentOutOfRangeException>(() => param.Direction = ParameterDirection.Output);
            param.Direction = ParameterDirection.Input;
        }
        public void Constructor_NameOnly()
        {
            var parameter = new BigQueryParameter("name");

            Assert.Equal("name", parameter.Name);
            Assert.Null(parameter.Type);
            Assert.Null(parameter.Value);
        }
 public void ChangeDbTypeTest(DbType dbType, BigQueryDbType bigQueryDbType)
 {
     var param = new BigQueryParameter(parameterName, DbType.Single) { Value = intValue };
     Assert.Equal(DbType.Single, param.DbType);
     Assert.Equal(BigQueryDbType.Float, param.BigQueryDbType);
     param.DbType = dbType;
     Assert.Equal(dbType, param.DbType);
     Assert.Equal(bigQueryDbType, param.BigQueryDbType);
 }
        public void ValidateValueCanConvertTest()
        {
            var param = new BigQueryParameter(parameterName, DbType.Int64)
            {
                Value = floatValue
            };

            param.Validate();
        }
        public void Add_Parameter()
        {
            var collection = new BigQueryParameterCollection();
            var parameter  = new BigQueryParameter();
            var added      = collection.Add(parameter);

            Assert.Same(parameter, added);
            Assert.Same(parameter, collection[0]);
        }
        public void UnnamedParameterCannotBeConvertedWith_DefaultNamedMode()
        {
            var    client    = BigQueryClient.Create(_fixture.ProjectId);
            var    table     = client.GetTable(_fixture.DatasetId, _fixture.HighScoreTableId);
            string sql       = $"SELECT score FROM {table} WHERE player=@player";
            var    parameter = new BigQueryParameter(BigQueryDbType.String, "Angela");

            Assert.Throws <ArgumentException>(() => client.ExecuteQuery(sql, new[] { parameter }));
        }
        public void ValidateValueCanNotConvertTest()
        {
            var param = new BigQueryParameter(parameterName, DbType.DateTime)
            {
                Value = floatValue
            };

            Assert.Throws <ArgumentException>(() => param.Validate());
        }
        public void ValidateDuplicateParametersTest()
        {
            var collection = new BigQueryParameterCollection();
            var param      = new BigQueryParameter("@param1", DbType.Object);

            collection.Add(param);
            collection.Add(param);
            Assert.Throws <DuplicateNameException>(() => collection.Validate());
        }
        public void TypeInference(string name, object value, BigQueryDbType expectedType)
        {
            var parameter = new BigQueryParameter();

            parameter.Value = value;
            var queryParameter = parameter.ToQueryParameter();
            var actualType     = EnumMap <BigQueryDbType> .ToValue(queryParameter.ParameterType.Type);

            Assert.Equal(expectedType, actualType);
        }
        public void InferDbTypeAndBigQueryDbTypeFromValueTest(object value, DbType exceptedDbType, BigQueryDbType exceptedBQDbType)
        {
            var param = new BigQueryParameter()
            {
                Value = value
            };

            Assert.Equal(value, param.Value);
            Assert.Equal(exceptedDbType, param.DbType);
            Assert.Equal(exceptedBQDbType, param.BigQueryDbType);
        }
        public void ValidationEmptyNameTest()
        {
            var param = new BigQueryParameter {
                Value  = intValue,
                DbType = DbType.Single
            };

            Assert.True(string.IsNullOrEmpty(param.ParameterName));
            Assert.Throws <ArgumentException>(() => param.Validate());
            param.ParameterName = parameterName;
            param.Validate();
        }
示例#20
0
        public void ArrayTypeInference(string name, object value, BigQueryDbType expectedArrayType)
        {
            var parameter = new BigQueryParameter();

            parameter.Value = value;
            var queryParameter = parameter.ToQueryParameter(BigQueryParameterMode.Positional);

            Assert.Equal(BigQueryDbType.Array.ToParameterApiType(), queryParameter.ParameterType.Type);
            var actualArrayType = EnumMap <BigQueryDbType> .ToValue(queryParameter.ParameterType.ArrayType.Type);

            Assert.Equal(expectedArrayType, actualArrayType);
        }
        public void ValidateSizeTest()
        {
            var param      = new BigQueryParameter(parameterName, DbType.String);
            var longString = new string('*', maxStringSize + 1);

            param.Value = longString;
            Assert.Equal(longString.Length, param.Size);
            Assert.Throws <ArgumentException>(() => param.Validate());

            param.Value = longString.Substring(0, maxStringSize);
            param.Validate();
        }
        public void CopyToTest()
        {
            var count = Collection.Count;

            BigQueryParameter[] collectionForCopy = new BigQueryParameter[count];
            Collection.CopyTo(collectionForCopy, 0);
            Assert.Equal(count, collectionForCopy.Length);
            for (int i = 0; i < count; i++)
            {
                Assert.Same(Collection[i], collectionForCopy[i]);
            }
        }
        public void ChangeBigQueryDbTypeTest(BigQueryDbType bigQueryDbType, DbType dbType)
        {
            var param = new BigQueryParameter(parameterName, DbType.Single)
            {
                Value = intValue
            };

            Assert.Equal(DbType.Single, param.DbType);
            Assert.Equal(BigQueryDbType.Float, param.BigQueryDbType);
            param.BigQueryDbType = bigQueryDbType;
            Assert.Equal(bigQueryDbType, param.BigQueryDbType);
            Assert.Equal(dbType, param.DbType);
        }
        public void ValidationNullValueTest()
        {
            var param = new BigQueryParameter {
                ParameterName = parameterName
            };

            Assert.Null(param.Value);
            Assert.Throws <ArgumentException>(() => param.Validate());
            param.Value = DBNull.Value;
            Assert.Throws <ArgumentException>(() => param.Validate());
            param.Value = intValue;
            param.Validate();
        }
        public void ValidateUnsupportedBigQueryDbTypeTest()
        {
            var param = new BigQueryParameter(parameterName, DbType.Byte)
            {
                Value = intValue
            };

            Assert.Equal(intValue, param.Value);
            Assert.Equal(BigQueryDbType.Unknown, param.BigQueryDbType);
            Assert.Throws <NotSupportedException>(() => param.Validate());
            param.BigQueryDbType = BigQueryDbType.Integer;
            param.Validate();
        }
示例#26
0
        private void AssertParameterRoundTrip(BigQueryClient client, BigQueryParameter parameter)
        {
            var results = client.ExecuteQuery(
                "SELECT ? AS value",
                new[] { parameter },
                new QueryOptions {
                ParameterMode = BigQueryParameterMode.Positional
            }).ToList();

            Assert.Equal(1, results.Count);
            Assert.Equal(parameter.Value, results[0]["value"]);
            if (parameter.Value is DateTime)
            {
                AssertDateTimeEqual((DateTime)parameter.Value, (DateTime)results[0]["value"]);
            }
        }
        private void AssertParameterRoundTrip(BigQueryClient client, BigQueryParameter parameter)
        {
            var command = new BigQueryCommand($"SELECT ? AS value")
            {
                Parameters    = { parameter },
                ParameterMode = BigQueryParameterMode.Positional
            };
            var results = client.ExecuteQuery(command).ToList();

            Assert.Equal(1, results.Count);
            Assert.Equal(parameter.Value, results[0]["value"]);
            if (parameter.Value is DateTime)
            {
                AssertDateTimeEqual((DateTime)parameter.Value, (DateTime)results[0]["value"]);
            }
        }
        public void ListMemberDelegation()
        {
            var p1         = new BigQueryParameter();
            var p2         = new BigQueryParameter();
            var collection = new BigQueryParameterCollection {
                p1, p2
            };

            Assert.Equal(new[] { p1, p2 }, collection);
            Assert.Same(p1, collection[0]);
            Assert.Same(p2, collection[1]);
            Assert.Equal(2, collection.Count);

            collection.Clear();
            Assert.Equal(0, collection.Count);
        }
        public void SizeTest()
        {
            var param = new BigQueryParameter(parameterName, DbType.Int64);

            param.Value = 1;
            Assert.Equal(0, param.Size);

            param.Value = stringValue;
            Assert.Equal(0, param.Size);
            param.DbType = DbType.String;
            Assert.Equal(stringValue.Length, param.Size);

            var size = 42;

            param.Size = size;
            Assert.Equal(size, param.Size);
        }
 public void CloneParameterTest()
 {
     var param = new BigQueryParameter {
         Value = parameterName,
         ParameterName = stringValue
     };
     var clone = param.Clone();
     Assert.NotNull(clone);
     Assert.NotSame(param, clone);
     bool equal = (param.Value == clone.Value) &&
         (param.ParameterName == clone.ParameterName) &&
         (param.DbType == clone.DbType) &&
         (param.BigQueryDbType == clone.BigQueryDbType) &&
         (param.IsNullable == clone.IsNullable) &&
         (param.Direction == clone.Direction) &&
         (param.IsNullable == clone.IsNullable) &&
         (param.Size == clone.Size);
     Assert.True(equal);
 }
示例#31
0
        [InlineData("2004-07-26T15:25:28.173333Z")] // Value is "1.090855528173333E9"
        public void TimestampRounding(string timestamp)
        {
            var client        = BigQueryClient.Create(_fixture.ProjectId);
            var parsedLocally = DateTime.ParseExact(timestamp, "yyyy-MM-dd'T'HH:mm:ss.FFFFFF'Z'",
                                                    CultureInfo.InvariantCulture, DateTimeStyles.AdjustToUniversal | DateTimeStyles.AssumeUniversal);
            // Note: we're deliberately passing the parameter as a string so the conversion happens on the server.
            // This means we can't use AssertParameterRoundTrip.
            var parameter = new BigQueryParameter(BigQueryDbType.String, timestamp);
            var results   = client.ExecuteQuery(
                "SELECT CAST(? AS TIMESTAMP) AS value",
                new[] { parameter },
                new QueryOptions {
                ParameterMode = BigQueryParameterMode.Positional
            }).ToList();

            Assert.Equal(1, results.Count);
            var returned = Assert.IsType <DateTime>(results[0]["value"]);

            AssertDateTimeEqual(parsedLocally, returned);
        }
    public void QueryWithPositionalParameters(string projectId = "project-id")
    {
        var corpus       = "romeoandjuliet";
        var minWordCount = 250;

        // Note: Standard SQL is required to use query parameters.
        var query = @"
                SELECT word, word_count
                FROM `bigquery-public-data.samples.shakespeare`
                WHERE corpus = ?
                AND word_count >= ?
                ORDER BY word_count DESC;";

        // Initialize client that will be used to send requests.
        var client = BigQueryClient.Create(projectId);

        // Set the name to None to use positional parameters.
        // Note that you cannot mix named and positional parameters.
        var parameters = new BigQueryParameter[]
        {
            new BigQueryParameter(null, BigQueryDbType.String, corpus),
            new BigQueryParameter(null, BigQueryDbType.Int64, minWordCount)
        };

        var job = client.CreateQueryJob(
            sql: query,
            parameters: parameters,
            options: new QueryOptions
        {
            UseQueryCache = false,
            ParameterMode = BigQueryParameterMode.Positional
        });

        // Wait for the job to complete.
        job = job.PollUntilCompleted().ThrowOnAnyError();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["word"]}: {row["word_count"]}");
        }
    }
        public void SizeTest()
        {
            var param = new BigQueryParameter(parameterName, DbType.Int64);
            param.Value = 1;
            Assert.Equal(0, param.Size);

            param.Value = stringValue;
            Assert.Equal(0, param.Size);
            param.DbType = DbType.String;
            Assert.Equal(stringValue.Length, param.Size);

            var size = 42;
            param.Size = size;
            Assert.Equal(size, param.Size);
        }
 public void CopyToTest()
 {
     var count = Collection.Count;
     BigQueryParameter[] collectionForCopy = new BigQueryParameter[count];
     Collection.CopyTo(collectionForCopy, 0);
     Assert.Equal(count, collectionForCopy.Length);
     for(int i = 0; i < count; i++) {
         Assert.Same(Collection[i], collectionForCopy[i]);
     }
 }
 public void ValidateDuplicateParametersTest()
 {
     var collection = new BigQueryParameterCollection();
     var param = new BigQueryParameter("@param1", DbType.Object);
     collection.Add(param);
     collection.Add(param);
     Assert.Throws<DuplicateNameException>(() => collection.Validate());
 }
 public void DirectionTypeTest()
 {
     var param = new BigQueryParameter(parameterName, DbType.String);
     Assert.Equal(ParameterDirection.Input, param.Direction);
     Assert.Throws<ArgumentOutOfRangeException>(() => param.Direction = ParameterDirection.Output);
     param.Direction = ParameterDirection.Input;
 }
 public void InferDbTypeAndBigQueryDbTypeFromValueTest(object value, DbType exceptedDbType, BigQueryDbType exceptedBQDbType)
 {
     var param = new BigQueryParameter() {Value = value};
     Assert.Equal(value, param.Value);
     Assert.Equal(exceptedDbType, param.DbType);
     Assert.Equal(exceptedBQDbType, param.BigQueryDbType);
 }
        public void ParameterValueTest()
        {
            var emptyParam = new BigQueryParameter();
            Assert.Null(emptyParam.Value);

            var paramInitializeValue = new BigQueryParameter(parameterName, DbType.String) {Value = stringValue};
            Assert.Equal(stringValue, paramInitializeValue.Value);

            var intParam = new BigQueryParameter(parameterName, DbType.Int64);
            Assert.Equal(default(long), intParam.Value);

            var stringParam = new BigQueryParameter(parameterName, DbType.String);
            Assert.Null(stringParam.Value);

            var objectParam = new BigQueryParameter(parameterName, DbType.Object);
            Assert.Null(objectParam.Value);
        }
 public void ValidationEmptyNameTest()
 {
     var param = new BigQueryParameter {
         Value = intValue,
         DbType = DbType.Single
     };
     Assert.True(string.IsNullOrEmpty(param.ParameterName));
     Assert.Throws<ArgumentException>(() => param.Validate());
     param.ParameterName = parameterName;
     param.Validate();
 }
 public void ValidateChangeIsnullableTest()
 {
     var param = new BigQueryParameter(parameterName, DbType.String);
     Assert.False(param.IsNullable);
     param.IsNullable = false;
     Assert.Throws<ArgumentOutOfRangeException>(() => param.IsNullable = true);
 }
 public void ValidateUnsupportedBigQueryDbTypeTest()
 {
     var param = new BigQueryParameter(parameterName, DbType.Byte) {Value = intValue};
     Assert.Equal(intValue, param.Value);
     Assert.Equal(BigQueryDbType.Unknown, param.BigQueryDbType);
     Assert.Throws<NotSupportedException>(() => param.Validate());
     param.BigQueryDbType = BigQueryDbType.Integer;
     param.Validate();
 }
 public void ValidationNullValueTest()
 {
     var param = new BigQueryParameter {ParameterName = parameterName};
     Assert.Null(param.Value);
     Assert.Throws<ArgumentException>(() => param.Validate());
     param.Value = DBNull.Value;
     Assert.Throws<ArgumentException>(() => param.Validate());
     param.Value = intValue;
     param.Validate();
 }
        public void ConstructorTest()
        {
            var emptyParam = new BigQueryParameter();
            Assert.Equal(DbType.Object, emptyParam.DbType);
            Assert.Equal(BigQueryDbType.Unknown, emptyParam.BigQueryDbType);
            Assert.Null(emptyParam.Value);
            Assert.Null(emptyParam.ParameterName);

            var paramDbType = new BigQueryParameter(parameterName, DbType.Int64);
            Assert.Equal(parameterName, paramDbType.ParameterName);
            Assert.Equal(DbType.Int64, paramDbType.DbType);
            Assert.Equal(BigQueryDbType.Integer, paramDbType.BigQueryDbType);
            Assert.Equal(default(long), paramDbType.Value);

            var paramBQDbType = new BigQueryParameter(parameterName, BigQueryDbType.Integer);
            Assert.Equal(parameterName, paramBQDbType.ParameterName);
            Assert.Equal(DbType.Int64, paramBQDbType.DbType);
            Assert.Equal(BigQueryDbType.Integer, paramBQDbType.BigQueryDbType);
            Assert.Equal(default(long), paramBQDbType.Value);
        }
 public void ValidateValueCanConvertTest()
 {
     var param = new BigQueryParameter(parameterName, DbType.Int64) { Value = floatValue };
     param.Validate();
 }
        public void ValidateSizeTest()
        {
            var param = new BigQueryParameter(parameterName, DbType.String);
            var longString = new string('*', maxStringSize + 1);

            param.Value = longString;
            Assert.Equal(longString.Length, param.Size);
            Assert.Throws<ArgumentException>(() => param.Validate());

            param.Value = longString.Substring(0, maxStringSize);
            param.Validate();
        }
 public void ValidateValueCanNotConvertTest()
 {
     var param = new BigQueryParameter(parameterName, DbType.DateTime) { Value = floatValue };
     Assert.Throws<ArgumentException>(() => param.Validate());
 }