/// <summary>
/// Adds a new <see cref="SpannerParameter" /> to the <see cref="SpannerParameterCollection" />
/// </summary>
/// <param name="parameterName">
/// The name of the parameter. For Insert, Update and Delete commands, this name should
/// be the name of a valid Column in a Spanner table. In Select commands, this name should be the name of a parameter
/// used in the SQL Query. Must not be null.
/// </param>
/// <param name="dbType">
/// One of the <see cref="SpannerDbType" /> values that indicates the type of the parameter.
/// Must not be null.
/// </param>
/// <param name="value">An object that is the value of the SpannerParameter. May be null.</param>
/// <returns>The parameter which has been added.</returns>
public SpannerParameter Add(string parameterName, SpannerDbType dbType, object value)
{
var parameter = Add(parameterName, dbType);
parameter.Value = value;
return(parameter);
}
/// <summary>
/// Adds a new <see cref="SpannerParameter" /> to the <see cref="SpannerParameterCollection" />.
/// </summary>
/// <param name="parameterName">
/// The name of the parameter. For Insert, Update and Delete commands, this name should
/// be the name of a valid Column in a Spanner table. In Select commands, this name should be the name of a parameter
/// used in the SQL Query. Must not be null
/// </param>
/// <param name="dbType">
/// One of the <see cref="SpannerDbType" /> values that indicates the type of the parameter.
/// Must not be null.
/// </param>
public void Add(string parameterName, SpannerDbType dbType)
{
GaxPreconditions.CheckNotNull(parameterName, nameof(parameterName));
GaxPreconditions.CheckNotNull(dbType, nameof(dbType));
_innerList.Add(new SpannerParameter(parameterName, dbType));
}
private SpannerDbType GetSpannerFieldType(int i)
{
var fieldMetadata = PopulateMetadataAsync(CancellationToken.None).ResultWithUnwrappedExceptions().RowType
.Fields[i];
return(SpannerDbType.FromProtobufType(fieldMetadata.Type));
}
/// <inheritdoc />
public override System.Type GetFieldType(int i)
{
var fieldMetadata = PopulateMetadataAsync(CancellationToken.None).ResultWithUnwrappedExceptions().RowType
.Fields[i];
return(SpannerDbType.FromProtobufType(fieldMetadata.Type).DefaultClrType);
}
internal Value ToProtobufValue(SpannerDbType type) =>
type.TypeCode == TypeCode.Timestamp
? new Value
{
StringValue = ProtoStringValue
}
: throw new InvalidOperationException(
$"{nameof(SpannerParameter.CommitTimestamp)} is only valid for {nameof(SpannerDbType.Timestamp)} parameters");
/// <summary>
/// Adds a new <see cref="SpannerParameter" /> to the <see cref="SpannerParameterCollection" />.
/// </summary>
/// <param name="parameterName">
/// The name of the parameter. For Insert, Update and Delete commands, this name should
/// be the name of a valid Column in a Spanner table. In Select commands, this name should be the name of a parameter
/// used in the SQL Query. Must not be null
/// </param>
/// <param name="dbType">
/// One of the <see cref="SpannerDbType" /> values that indicates the type of the parameter.
/// Must not be null.
/// </param>
/// <returns>The parameter which has been added.</returns>
public SpannerParameter Add(string parameterName, SpannerDbType dbType)
{
GaxPreconditions.CheckNotNull(parameterName, nameof(parameterName));
GaxPreconditions.CheckNotNull(dbType, nameof(dbType));
var parameter = new SpannerParameter(parameterName, dbType);
_innerList.Add(parameter);
return(parameter);
}
/// <summary>
/// When enabled, returns the schema of the query as a <see cref="DataTable"/>. This feature needs
/// to be enabled in the connection string via the <see cref="SpannerConnectionStringBuilder.EnableGetSchemaTable"/> property.
/// </summary>
/// <remarks>
/// <para>
/// <see cref="DbDataAdapter"/> will use this method automatically, but there is not enough information
/// available for it to do so to reliably manage data sets. This method returns <c>null</c> by default to
/// avoid this causing problems.
/// </para>
/// <para>
/// When the <c>EnableGetSchemaTable</c> property in the connection string is set to <c>true</c>, a
/// <c>DataTable</c> is returned with the following columns populated:
/// <list type="bullet">
/// <item><description>ColumnName (String): The name of the column</description></item>
/// <item><description>ColumnOrdinal (Int32): The ordinal value of the column</description></item>
/// <item><description>DataType (Type): The default CLR type of the column</description></item>
/// <item><description>ProviderType (SpannerDbType): The Spanner-specific data type of the column</description></item>
/// </list>
/// The following additional columns are present in the table, but not currently populated:
/// <list type="bullet">
/// <item><description>ColumnSize</description></item>
/// <item><description>NumericPrecision</description></item>
/// <item><description>NumericScale</description></item>
/// </list>
/// Future releases may expand the set of columns, or populate more of the existing columns.
/// </para>
/// </remarks>
/// <returns>A <c>DataTable</c> with schema information about the query, or <c>null</c> if the feature
/// is not enabled in the connection string.</returns>
public override DataTable GetSchemaTable()
{
// Spanner does not provide enough information for a schema table.
// DbDataAdapter will adjust and fill the dataset with information from
// this datareader (such as field type and name). By default, we just
// return null. The feature needs to be explicitly enabled in the connection string.
if (!_provideSchemaTable)
{
return(null);
}
var resultSet = PopulateMetadata();
// If the metadata couldn't be loaded, or there were no fields, just return null to indicate
// that the schema isn't available.
if ((resultSet?.RowType?.Fields?.Count ?? 0) == 0)
{
return(null);
}
var table = new DataTable("SchemaTable")
{
Columns =
{
{ "ColumnName", typeof(string) },
{ "ColumnOrdinal", typeof(int) },
{ "DataType", typeof(System.Type) },
{ "ProviderType", typeof(SpannerDbType) },
// Additional columns that are never populated.
{ "ColumnSize", typeof(int) },
{ "NumericPrecision", typeof(int) },
{ "NumericScale", typeof(int) }
}
};
int ordinal = 0;
foreach (var field in resultSet.RowType.Fields)
{
var row = table.NewRow();
SpannerDbType dbType = SpannerDbType.FromProtobufType(field.Type);
row["ColumnName"] = field.Name;
row["ColumnOrdinal"] = ordinal;
row["DataType"] = dbType.DefaultClrType;
row["ProviderType"] = dbType;
table.Rows.Add(row);
ordinal++;
}
return(table);
}
/// <summary>
/// Adds a new <see cref="SpannerParameter" /> to the <see cref="SpannerParameterCollection" />
/// </summary>
/// <param name="parameterName">
/// The name of the parameter. For Insert, Update and Delete commands, this name should
/// be the name of a valid Column in a Spanner table. In Select commands, this name should be the name of a parameter
/// used in the SQL Query. Must not be null.
/// </param>
/// <param name="dbType">
/// One of the <see cref="SpannerDbType" /> values that indicates the type of the parameter.
/// Must not be null.
/// </param>
/// <param name="value">An object that is the value of the SpannerParameter. May be null.</param>
public void Add(string parameterName, SpannerDbType dbType, object value)
{
GaxPreconditions.CheckNotNull(parameterName, nameof(parameterName));
GaxPreconditions.CheckNotNull(dbType, nameof(dbType));
var parameter = new SpannerParameter(parameterName, dbType)
{
Value = value
};
_innerList.Add(parameter);
}
/// <summary>
/// Initializes a new instance of the SpannerParameter class.
/// </summary>
/// <param name="parameterName">The name of the parameter. For Insert, Update and Delete commands, this name should
/// be the name of a valid column in a Spanner table. In Select commands, this name should be the name of a parameter
/// used in the SQL Query. This value is case sensitive. Must not be null.</param>
/// <param name="type">One of the <see cref="SpannerDbType"/> values that indicates the type of the parameter.
/// Must not be null.</param>
/// <param name="value">An object that is the value of the SpannerParameter. May be null.</param>
/// <param name="sourceColumn">The name of the DataTable source column (SourceColumn) if this SpannerParameter is
/// used in a call to Update. May be null.</param>
/// <param name="size">The length of the parameter. The value is for informational purposes only.</param>
public SpannerParameter(
string parameterName,
SpannerDbType type,
object value = null,
string sourceColumn = null,
int size = 0)
{
GaxPreconditions.CheckNotNull(parameterName, nameof(parameterName));
GaxPreconditions.CheckNotNull(type, nameof(type));
ParameterName = parameterName;
SpannerDbType = type;
Value = value;
SourceColumn = sourceColumn;
Size = size;
}
/// <inheritdoc />
public override System.Type GetFieldType(int i)
{
var fieldMetadata = PopulateMetadata().RowType.Fields[i];
return(SpannerDbType.FromProtobufType(fieldMetadata.Type).DefaultClrType);
}
/// <summary> /// Returns the full schema of this struct as a <see cref="SpannerDbType"/>. /// </summary> /// <remarks>The returned object reflects the current fields in the struct. If more /// fields are added later, those changes will not be visible via the returned value. /// Instead, this method should be called again obtain the up-to-date schema.</remarks> /// <returns>The <see cref="SpannerDbType"/> representing the schema of this struct.</returns> public SpannerDbType GetSpannerDbType() => SpannerDbType.ForStruct(this);
/// <summary> /// Adds a new field to the struct with the given name, type and value. /// </summary> /// <param name="name">The name of the field. May be null, and does not need to be unique within the struct. /// If the value is null, the <see cref="Field"/> value added will have an empty string for the name.</param> /// <param name="type">The type of the field. Must not be null.</param> /// <param name="value">The value of the field. May be null.</param> /// <returns>The newly-added field.</returns> public Field Add(string name, SpannerDbType type, object value) => Add(new Field(name, type, value));
/// <summary>
/// Constructs a struct field with the given name, type and value.
/// </summary>
/// <param name="name">Name of the field. May be null, which will be converted into an empty string.</param>
/// <param name="type">Type of the field. Must not be null.</param>
/// <param name="value">Value of the field. May be null.</param>
public Field(string name, SpannerDbType type, object value)
{
Name = name ?? "";
Type = GaxPreconditions.CheckNotNull(type, nameof(type));
Value = value;
}
private SpannerDbType(TypeCode typeCode, SpannerDbType arrayElementType)
: this(typeCode) => ArrayElementType = arrayElementType;
private SpannerDbType GetSpannerFieldType(int i)
{
var fieldMetadata = PopulateMetadata().RowType.Fields[i];
return(SpannerDbType.FromProtobufType(fieldMetadata.Type));
}
public static Value ToValue(object value, SpannerDbType spannerDbType)
{
if (value == null)
{
return(Value.ForNull());
}
switch (spannerDbType.TypeCode)
{
case TypeCode.Bytes:
if (value is string s)
{
return(new Value {
StringValue = s
});
}
if (value is byte[] bArray)
{
return(new Value {
StringValue = Convert.ToBase64String(bArray)
});
}
throw new ArgumentException("TypeCode.Bytes only supports string and byte[]", nameof(value));
case TypeCode.Bool:
return(new Value {
BoolValue = Convert.ToBoolean(value)
});
case TypeCode.String:
case TypeCode.Int64:
return(new Value {
StringValue = value.ToString()
});
case TypeCode.Float64:
return(new Value {
NumberValue = Convert.ToDouble(value)
});
case TypeCode.Timestamp:
if (value is string s2)
{
return(new Value {
StringValue = s2
});
}
return(new Value
{
StringValue = XmlConvert.ToString(Convert.ToDateTime(value), XmlDateTimeSerializationMode.Utc)
});
case TypeCode.Date:
if (value is string s3)
{
return(new Value {
StringValue = s3
});
}
return(new Value
{
StringValue = StripTimePart(
XmlConvert.ToString(Convert.ToDateTime(value), XmlDateTimeSerializationMode.Utc))
});
case TypeCode.Array:
if (value is IEnumerable enumerable)
{
return(Value.ForList(
enumerable.Cast <object>()
.Select(x => ToValue(x, spannerDbType.ArrayElementType)).ToArray()));
}
throw new ArgumentException("The given array instance needs to implement IEnumerable.", nameof(spannerDbType));
case TypeCode.Struct:
if (value is IDictionary dictionary)
{
var structValue = new Struct();
foreach (var key in dictionary.Keys)
{
string keyString = key.ToString();
if (!structValue.Fields.ContainsKey(keyString))
{
throw new ArgumentException("The given struct instance has members not defined in the Struct.", nameof(value));
}
structValue.Fields[keyString] = ToValue(
dictionary[key],
spannerDbType.StructMembers[keyString]);
}
return(Value.ForStruct(structValue));
}
throw new ArgumentException("The given struct instance needs to implement IDictionary.", nameof(spannerDbType));
default:
throw new ArgumentOutOfRangeException(nameof(spannerDbType), spannerDbType, null);
}
}
/// <summary>
/// Given a string representation, returns an instance of <see cref="SpannerDbType"/>.
/// </summary>
/// <param name="spannerType">A string representation of a SpannerDbType. See <see cref="ToString"/>.</param>
/// <param name="spannerDbType">If parsing was successful, then an instance of <see cref="SpannerDbType"/>.
/// Otherwise null.</param>
/// <returns>True if the parse was successful.</returns>
public static bool TryParse(string spannerType, out SpannerDbType spannerDbType)
{
spannerDbType = null;
if (!TryParsePartial(spannerType, out TypeCode code, out int?size, out string remainder))
{
return(false);
}
switch (code)
{
case TypeCode.Unspecified:
case TypeCode.Bool:
case TypeCode.Int64:
case TypeCode.Float64:
case TypeCode.Timestamp:
case TypeCode.Date:
case TypeCode.String:
case TypeCode.Bytes:
if (!string.IsNullOrEmpty(remainder))
{
//unexepected inner remainder on simple type
return(false);
}
// If there's no size, we can use cached values.
spannerDbType = size == null?FromTypeCode(code) : new SpannerDbType(code, size);
return(true);
case TypeCode.Array:
if (!TryParse(remainder, out SpannerDbType elementType))
{
return(false);
}
spannerDbType = new SpannerDbType(code, elementType);
return(true);
case TypeCode.Struct:
// There could be nested structs, so we need to be careful about parsing the inner string.
var fields = new List <StructField>();
int currentIndex = 0;
while (remainder != null && currentIndex < remainder.Length)
{
int midfieldIndex = NonNestedIndexOf(remainder, currentIndex, ':');
int endFieldIndex = NonNestedIndexOf(remainder, currentIndex, ',');
if (endFieldIndex == -1)
{
// We reached the last field.
endFieldIndex = remainder.Length;
}
string fieldName;
int fieldTypeStartIndex;
if (midfieldIndex != -1 && midfieldIndex < endFieldIndex)
{
fieldName = remainder.Substring(currentIndex, midfieldIndex - currentIndex).Trim();
// Empty names can't be specified expicitly; STRUCT<INT64> is fine, but STRUCT<:INT64> is not.
if (fieldName == "")
{
return(false);
}
fieldTypeStartIndex = midfieldIndex + 1;
}
else
{
fieldName = "";
fieldTypeStartIndex = currentIndex;
}
if (!TryParse(remainder.Substring(fieldTypeStartIndex, endFieldIndex - fieldTypeStartIndex),
out SpannerDbType fieldDbType))
{
return(false);
}
fields.Add(new StructField(fieldName, fieldDbType));
currentIndex = endFieldIndex + 1;
}
spannerDbType = new SpannerDbType(code, fields);
return(true);
default:
return(false);
}
}
/// <summary>
/// Given a string representation, returns an instance of <see cref="SpannerDbType"/>.
/// </summary>
/// <param name="spannerType">A string representation of a SpannerDbType. See <see cref="ToString"/></param>
/// <param name="spannerDbType">If parsing was successful, then an instance of <see cref="SpannerDbType"/>.
/// Otherwise null.</param>
/// <returns></returns>
public static bool TryParse(string spannerType, out SpannerDbType spannerDbType)
{
spannerDbType = null;
if (!TryParsePartial(spannerType, out TypeCode code, out int?size, out string remainder))
{
return(false);
}
switch (code)
{
case TypeCode.Unspecified:
case TypeCode.Bool:
case TypeCode.Int64:
case TypeCode.Float64:
case TypeCode.Timestamp:
case TypeCode.Date:
case TypeCode.String:
case TypeCode.Bytes:
if (!string.IsNullOrEmpty(remainder))
{
//unexepected inner remainder on simple type
return(false);
}
spannerDbType = !size.HasValue ? FromTypeCode(code) : new SpannerDbType(code, size.Value);
return(true);
case TypeCode.Array:
if (!TryParse(remainder, out SpannerDbType elementType))
{
return(false);
}
spannerDbType = new SpannerDbType(code, elementType);
return(true);
case TypeCode.Struct:
//there could be nested structs, so we need to be careful about parsing the inner string.
List <Tuple <string, SpannerDbType> > fields = new List <Tuple <string, SpannerDbType> >();
int currentIndex = 0;
while (currentIndex < remainder.Length)
{
int midfieldIndex = NonNestedIndexOf(remainder, currentIndex, ':');
if (midfieldIndex == -1)
{
return(false);
}
int endFieldIndex = NonNestedIndexOf(remainder, currentIndex, ',');
if (endFieldIndex == -1)
{
//we reached the last field.
endFieldIndex = remainder.Length;
}
string fieldName = remainder.Substring(currentIndex, midfieldIndex - currentIndex).Trim();
if (!TryParse(remainder.Substring(midfieldIndex + 1, endFieldIndex - midfieldIndex - 1),
out SpannerDbType fieldDbType))
{
return(false);
}
fields.Add(new Tuple <string, SpannerDbType>(fieldName, fieldDbType));
currentIndex = endFieldIndex + 1;
}
spannerDbType = new SpannerDbType(code, fields);
return(true);
default:
return(false);
}
}
public static Value ToValue(object value, SpannerDbType spannerDbType)
{
if (value == null)
{
return(Value.ForNull());
}
switch (spannerDbType.TypeCode)
{
case TypeCode.Bytes:
if (value is string s)
{
return(new Value {
StringValue = s
});
}
if (value is byte[] bArray)
{
return(new Value {
StringValue = Convert.ToBase64String(bArray)
});
}
throw new ArgumentException("TypeCode.Bytes only supports string and byte[]", nameof(value));
case TypeCode.Bool:
return(new Value {
BoolValue = Convert.ToBoolean(value)
});
case TypeCode.String:
if (value is DateTime dateTime)
{
// If the value is a DateTime, we always convert using XmlConvert.
// This allows us to convert back to a datetime reliably from the
// resulting string (so roundtrip works properly if the developer uses
// a string as a backing field for a datetime for whatever reason).
return(new Value {
StringValue = XmlConvert.ToString(dateTime, XmlDateTimeSerializationMode.Utc)
});
}
return(new Value {
StringValue = Convert.ToString(value, InvariantCulture)
});
case TypeCode.Int64:
return(new Value {
StringValue = Convert.ToInt64(value, InvariantCulture)
.ToString(InvariantCulture)
});
case TypeCode.Float64:
return(new Value {
NumberValue = Convert.ToDouble(value, InvariantCulture)
});
case TypeCode.Timestamp:
return(new Value
{
StringValue = XmlConvert.ToString(Convert.ToDateTime(value, InvariantCulture), XmlDateTimeSerializationMode.Utc)
});
case TypeCode.Date:
return(new Value
{
StringValue = StripTimePart(
XmlConvert.ToString(Convert.ToDateTime(value, InvariantCulture), XmlDateTimeSerializationMode.Utc))
});
case TypeCode.Array:
if (value is IEnumerable enumerable)
{
return(Value.ForList(
enumerable.Cast <object>()
.Select(x => ToValue(x, spannerDbType.ArrayElementType)).ToArray()));
}
throw new ArgumentException("The given array instance needs to implement IEnumerable.", nameof(spannerDbType));
case TypeCode.Struct:
if (value is IDictionary dictionary)
{
var structValue = new Struct();
foreach (var key in dictionary.Keys)
{
string keyString = Convert.ToString(key, InvariantCulture);
if (!spannerDbType.StructMembers.ContainsKey(keyString))
{
throw new ArgumentException("The given struct instance has members not defined in the Struct.", nameof(value));
}
structValue.Fields[keyString] = ToValue(
dictionary[key],
spannerDbType.StructMembers[keyString]);
}
return(Value.ForStruct(structValue));
}
throw new ArgumentException("The given struct instance needs to implement IDictionary.", nameof(spannerDbType));
default:
throw new ArgumentOutOfRangeException(nameof(spannerDbType), spannerDbType, null);
}
}