private (List <ResultSetFieldMetadata>, string) ExtractResultSetMetadata(string resultSetXml)
{
try
{
var xmlSerializer = new System.Xml.Serialization.XmlSerializer(typeof(SqlResultSetFieldCollection));
resultSetXml = $"<FieldCollection>{resultSetXml}</FieldCollection>";
using (var sr = new StringReader(resultSetXml))
{
var val = (xmlSerializer.Deserialize(sr) as Settings.ObjectModel.SqlResultSetFieldCollection);
// look for error
if (val.Fields.Count > 0 && !string.IsNullOrWhiteSpace(val.Fields[0].ErrorMsg))
{
var f = val.Fields[0];
return(null, $"({f.ErrorDescription}) {f.ErrorMsg}");
}
else
{
return(val.Fields.Select(f => new ResultSetFieldMetadata()
{
ColumnName = f.Name,
DataType = RoutineParameterV2.GetCSharpDataTypeFromSqlDbType(f.Type),
DbDataType = f.Type,
ColumnSize = f.Size,
NumericalPrecision = f.Precision,
NumericalScale = f.Scale
}).ToList(), null);
}
}
}
catch (Exception ex)
{
SessionLog.Exception(ex);
return(null, "Failed to parse ResultSetXml:" + ex.Message);
}
}
private static object ConvertParameterValue(string paramName, SqlDbType sqlType, string value, string udtType)
{
// if the expected value is a string return as is
if (SqlStringTypes.Contains(sqlType))
{
return(value);
}
if (string.IsNullOrWhiteSpace(value))
{
return(DBNull.Value);
}
switch (sqlType)
{
case SqlDbType.UniqueIdentifier:
return(new Guid((string)value));
case SqlDbType.DateTime:
var expectedFormat = "yyyy-MM-dd'T'HH:mm:ss.FFFK";
// DateTimeOffset expect the Date & Time to be in LOCAL time
if (DateTimeOffset.TryParseExact(value, expectedFormat, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out var dto))
{
return(dto.DateTime);
}
else
{
throw new JsdalServerParameterValueException(paramName, $"Invalid DateTime value of {value ?? "(null)"}. Expected format is: {expectedFormat} e.g. {DateTime.Now.ToString(expectedFormat)}");
}
case SqlDbType.Bit:
return(ConvertToSqlBit(value));
case SqlDbType.VarBinary:
return(ConvertToSqlVarbinary(value));
case SqlDbType.Timestamp:
return(BitConverter.GetBytes(long.Parse(value)).Reverse().ToArray() /*have to reverse to match the endianness*/);
case SqlDbType.Time:
return(value);
case SqlDbType.Float:
return(double.Parse(value));
case SqlDbType.Decimal:
return(decimal.Parse(value));
case SqlDbType.Udt:
{
// TODO: Refractor into separate function
// TODO: Add support for geometry
// TODO: Throw if unable to convert? (e.g. see DateTime section)
if (udtType.Equals("geography", StringComparison.OrdinalIgnoreCase))
{
// for geography we only support { lat: .., lng: ...} for now - in future we might support WKT strings
var obj = JsonConvert.DeserializeObject <dynamic>(value);
int srid = 4326;
if (obj["srid"] != null)
{
srid = (int)obj.srid;
}
// Use NetTopologySuite until MS adds support for Geography/Geometry in dotcore sql client
// See https://github.com/dotnet/SqlClient/issues/30
var geometry = new NetTopologySuite.Geometries.Point((double)obj.lng, (double)obj.lat)
{
SRID = srid
};
var geometryWriter = new NetTopologySuite.IO.SqlServerBytesWriter {
IsGeography = true
};
var bytes = geometryWriter.Write(geometry);
return(new System.Data.SqlTypes.SqlBytes(bytes));
//return SqlGeography.Point((double)obj.lat, (double)obj.lng, srid);
}
return(value);
}
default:
{
var typeName = RoutineParameterV2.GetCSharpDataTypeFromSqlDbType(sqlType.ToString().ToLower());
var type = Type.GetType(typeName);
return(Convert.ChangeType(value, type));
}
}
}