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);
}
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();
}
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();
}
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);
}
[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());
}