private static decimal RandomDecimal(decimal from, decimal to)
{
Random rnd = new Random();
byte fromScale = new System.Data.SqlTypes.SqlDecimal(from).Scale;
byte toScale = new System.Data.SqlTypes.SqlDecimal(to).Scale;
byte scale = (byte)(fromScale + toScale);
if (scale > 28)
{
scale = 28;
}
decimal r = new decimal(rnd.Next(), rnd.Next(), rnd.Next(), false, scale);
if (Math.Sign(from) == Math.Sign(to) || from == 0 || to == 0)
{
return(decimal.Remainder(r, to - from) + from);
}
bool getFromNegativeRange = (double)from + rnd.NextDouble() * ((double)to - (double)from) < 0;
return(getFromNegativeRange ? decimal.Remainder(r, -from) + from : decimal.Remainder(r, to));
}
public static decimal?ToDecimal(System.Data.SqlTypes.SqlDecimal sqlDecimal)
{
if (sqlDecimal.IsNull)
{
return(null);
}
const int decimalMaxPrecision = 28;
const int decimalMaxScale = 28;
if (sqlDecimal.Precision <= decimalMaxPrecision)
{
// the maximum precision for decimal is 28. If the precision is higher accessing the Value property will throw an exception
return(sqlDecimal.Value);
}
else
{
// There is no easy way to actually convert all SqlDecimals which can be converted to System.Decimal.
// Therefore we have to find a scale that works without truncating the value by brute force.
for (int scale = 0; scale <= decimalMaxScale; scale++)
{
var normalized = System.Data.SqlTypes.SqlDecimal.ConvertToPrecScale(sqlDecimal, precision: decimalMaxPrecision, scale: scale);
if (normalized == sqlDecimal)
{
return(normalized.Value);
}
}
throw new OverflowException("SqlDecimal has a higher accuracy than can be represented by System.Decimal. Conversion failed");
}
}
public static decimal Next(this Random rnd, decimal from, decimal to)
{
byte fromScale = new System.Data.SqlTypes.SqlDecimal(from).Scale;
byte toScale = new System.Data.SqlTypes.SqlDecimal(to).Scale;
byte scale = (byte)(fromScale + toScale);
if (scale > 28)
{
scale = 28;
}
decimal r = new decimal(rnd.Next(), rnd.Next(), rnd.Next(), false, scale);
if (Math.Sign(from) == Math.Sign(to) || from == 0 || to == 0)
{
return(decimal.Remainder(r, to - from) + from);
}
if ((double)from + rnd.NextDouble() * ((double)to - (double)from) < 0)
{
return(decimal.Remainder(r, -from) + from);
}
return(decimal.Remainder(r, to));
}
private static object SqlDecimalToDecimal(System.Data.SqlTypes.SqlDecimal valor)
{
if (valor.IsNull)
{
return(DBNull.Value);
}
return(Convert.ToDecimal(valor.ToDouble()));
}
private System.Data.SqlTypes.SqlDecimal CircumventSqlBulkCopyBug(int i, object value)
{
// Workaround for Microsoft Bug (caused SUP-917)
var precisionAndScale = accessorForCircumventSqlBulkCopyBug(sqlBulkCopy, i);
var sqlDecimal = new System.Data.SqlTypes.SqlDecimal((decimal)value);
// We create an SqlDecimal with the same precision and scale as in the metadata. SqlBulkCopy can handle this.
sqlDecimal = System.Data.SqlTypes.SqlDecimal.ConvertToPrecScale(sqlDecimal, precision: precisionAndScale.Item1, scale: precisionAndScale.Item2);
return(sqlDecimal);
}
/// <summary>
/// This method allows to clear all the properties previously loaded by a call to the Refresh method.
/// </summary>
public void Reset()
{
this.col_JobPartId = System.Data.SqlTypes.SqlInt32.Null;
this.col_JobId = System.Data.SqlTypes.SqlInt32.Null;
this.col_Description = System.Data.SqlTypes.SqlString.Null;
this.col_JobPartTypeId = System.Data.SqlTypes.SqlInt32.Null;
this.col_Units = System.Data.SqlTypes.SqlDecimal.Null;
this.col_PricePerUnit = System.Data.SqlTypes.SqlMoney.Null;
this.col_TotalPrice = System.Data.SqlTypes.SqlMoney.Null;
}
public decimal Generate()
{
byte fromScale = new System.Data.SqlTypes.SqlDecimal(MinimumValue).Scale;
byte toScale = new System.Data.SqlTypes.SqlDecimal(MaximumValue).Scale;
byte scale = (byte)(fromScale + toScale);
if (scale > 28)
{
scale = 28;
}
decimal r = new decimal(random.Next(), random.Next(), random.Next(), false, scale);
if (Math.Sign(MinimumValue) == Math.Sign(MaximumValue) || MinimumValue == 0 || MaximumValue == 0)
{
return(decimal.Remainder(r, MaximumValue - MinimumValue) + MinimumValue);
}
bool getFromNegativeRange = (double)MinimumValue + random.NextDouble() * ((double)MaximumValue - (double)MinimumValue) < 0;
return(getFromNegativeRange ? decimal.Remainder(r, -MinimumValue) + MinimumValue : decimal.Remainder(r, MaximumValue));
}
public static decimal NextDecimal(this Random random, decimal min, decimal max)
{
byte fromScale = new System.Data.SqlTypes.SqlDecimal(min).Scale;
byte toScale = new System.Data.SqlTypes.SqlDecimal(max).Scale;
byte scale = (byte)(fromScale + toScale);
if (scale > 28)
{
scale = 28;
}
decimal r = new decimal(random.Next(), random.Next(), random.Next(), false, scale);
if (Math.Sign(min) == Math.Sign(max) || min == 0 || max == 0)
{
return(decimal.Remainder(r, max - min) + min);
}
bool getFromNegativeRange = (double)min + random.NextDouble() * ((double)max - (double)min) < 0;
return(getFromNegativeRange ? decimal.Remainder(r, -min) + min : decimal.Remainder(r, max));
}
public static decimal ToDecimal(object obj)
{
if (obj == null)
{
return(0);
}
if (obj is System.Data.SqlTypes.SqlDecimal)
{
System.Data.SqlTypes.SqlDecimal o = (System.Data.SqlTypes.SqlDecimal)obj;
if (o.IsNull)
{
return(0);
}
else
{
return(o.Value);
}
}
else if (obj is System.Data.SqlTypes.SqlMoney)
{
System.Data.SqlTypes.SqlMoney o = (System.Data.SqlTypes.SqlMoney)obj;
if (o.IsNull)
{
return(0);
}
else
{
return(o.Value);
}
}
else if (obj == DBNull.Value)
{
return(0);
}
return(System.Convert.ToDecimal(obj));
}
public decimal GetNextDecimal(decimal from, decimal to)
{
lock (_random)
{
// http://stackoverflow.com/questions/609501/generating-a-random-decimal-in-c-sharp?lq=1
byte fromScale = new System.Data.SqlTypes.SqlDecimal(from).Scale;
byte toScale = new System.Data.SqlTypes.SqlDecimal(to).Scale;
byte scale = (byte)(fromScale + toScale);
if (scale > 28)
{
scale = 28;
}
decimal r = new decimal(_random.Next(), _random.Next(), _random.Next(), false, scale);
if (Math.Sign(from) == Math.Sign(to) || from == 0 || to == 0)
{
return(decimal.Remainder(r, to - from) + from);
}
bool getFromNegativeRange = (double)from + _random.NextDouble() * ((double)to - (double)from) < 0;
return(getFromNegativeRange ? decimal.Remainder(r, -from) + from : decimal.Remainder(r, to));
}
}
public static DecimalInfo GetDecimalInfo(decimal dec)
{
var x = new System.Data.SqlTypes.SqlDecimal(dec);
return(new DecimalInfo((int)x.Precision, (int)x.Scale));
}
private static void appendCastEnd(Dictionary <String, DbParameter> parametersDictionary, String parameterName, StringBuilder sb)
{
DbParameter p = parametersDictionary[parameterName];
switch (p.DbType)
{
case DbType.Boolean:
sb.Append(" AS SMALLINT)");
break;
case DbType.Date:
sb.Append(" AS DATE)");
break;
case DbType.Time:
sb.Append(" AS TIME)");
break;
case DbType.Decimal:
if (p.Value is String)
{
p.Value = ((String)p.Value).Replace(".", ",");
}
decimal castedValue = p.Value is string?Decimal.Parse((string)p.Value) : (decimal)p.Value;
var sqlDecimal = new System.Data.SqlTypes.SqlDecimal(castedValue);
int precision = sqlDecimal.Precision;
int scale = sqlDecimal.Scale;
sb.Append(string.Format(" AS DECIMAL({0},{1}))", precision, scale));
break;
case DbType.Int16:
case DbType.Int32:
sb.Append(" AS INTEGER)");
break;
case DbType.Int64:
sb.Append(" AS BIGINT)");
break;
case DbType.String:
case DbType.StringFixedLength:
int length = Math.Max((p.Value == DBNull.Value ? String.Empty : (string)p.Value).Length, 1);
if (length <= MAX_VARCHAR_SIZE)
{
sb.Append(string.Format(" AS VARCHAR({0}))", length));
}
else
{
sb.Append(string.Format(" AS CLOB)", length));
}
break;
case DbType.DateTime:
case DbType.DateTime2:
sb.Append(" AS TIMESTAMP)");
break;
case DbType.Binary:
sb.Append(" AS BLOB)");
break;
default:
sb.Append(" AS VARCHAR(256))");
Console.Error.WriteLine(string.Format("[IDbCommandExtensions.cs] Unkown DbType, going to cast to VARCHAR(256). ParamName> {0} DbType> {1}", parameterName, p.DbType.ToString()));
break;
}
}
public static void WriteData(TdsParameter p, TdsRequestStream stream, Encoding encoder)
{
switch (p.TdsType)
{
case TdsType.TDS_VARCHAR:
if (p.Value == null)
{
stream.Write((byte)0);
}
else
{
var s = (string)p.Value;
var bytes = encoder.GetBytes(s);
int strLen = Math.Max(VAR_MAX, bytes.Length);
stream.Write((byte)strLen);
stream.Write(bytes, 0, strLen);
}
break;
case TdsType.TDS_VARBINARY:
if (p.Value == null)
{
stream.Write((byte)0);
}
else
{
var bytes = (byte[])p.Value;
int bytesLen = Math.Max(VAR_MAX, bytes.Length);
stream.Write((byte)bytesLen);
stream.Write(bytes, 0, bytesLen);
}
break;
case TdsType.TDS_INTN:
if (p.Value == null)
{
stream.Write((byte)0);
}
else if (p.Value.GetType() == typeof(long))
{
stream.Write((byte)8);
stream.WriteLong((long)p.Value);
}
else // convert all smaller ints to int32
{
stream.Write((byte)4);
stream.WriteInt((int)p.Value);
}
break;
case TdsType.TDS_FLTN:
if (p.Value == null)
{
stream.Write((byte)0);
}
else if (p.Value.GetType() == typeof(float))
{
stream.Write((byte)4);
stream.WriteInt(BitConverter.SingleToInt32Bits((float)p.Value));
}
else // double
{
stream.Write((byte)8);
stream.WriteLong(BitConverter.DoubleToInt64Bits((double)p.Value));
}
break;
case TdsType.TDS_DATETIMEN:
if (p.Value == null)
{
stream.Write((byte)0);
}
else
{
stream.Write((byte)8);
var dt = new System.Data.SqlTypes.SqlDateTime((DateTime)p.Value);
stream.WriteInt(dt.DayTicks);
stream.WriteInt(dt.TimeTicks);
}
break;
case TdsType.TDS_DECN:
if (p.Value == null)
{
stream.Write((byte)0);
}
else
{
var sqlDec = new System.Data.SqlTypes.SqlDecimal((decimal)p.Value);
stream.Write((byte)17);
stream.Write(sqlDec.IsPositive ? (byte)0 : (byte)1);
stream.Write(sqlDec.BinData.Reverse().ToArray());
}
break;
case TdsType.TDS_MONEYN:
//if (value == null)
//{
stream.Write((byte)0);
//}
//else
//{
//}
break;
case TdsType.TDS_BIT:
stream.Write((bool)p.Value ? (byte)1 : (byte)0);
break;
default:
throw new NotImplementedException($"Unsupported type {p.TdsType}");
}
}
public override object GetValue(byte[] value)
{
if(!CompressionContext.UsesVardecimals)
{
if (value.Length != FixedLength.Value)
throw new ArgumentException("Invalid value length: " + value.Length);
int[] ints = new int[4];
for (int i = 0; i < getNumberOfRequiredStorageInts(); i++)
ints[i] = BitConverter.ToInt32(value, 1 + i * 4);
var sqlDecimal = new System.Data.SqlTypes.SqlDecimal(precision, scale, Convert.ToBoolean(value[0]), ints);
// This will fail for any SQL Server decimal values exceeding the max value of a C# decimal.
// Might want to return raw bytes at some point, though it's ugly.
return sqlDecimal.Value;
}
else
{
// Zero values are simply stored as a 0-length variable length field
if (value.Length == 0)
return 0m;
// Sign is stored in the first bit of the first byte
decimal sign = (value[0] >> 7) == 1 ? 1 : -1;
// Exponent is stored in the remaining 7 bytes of the first byte. As it's biased by 64 (ensuring we won't
// have to deal with negative numbers) we need to subtract the bias to get the real exponent value.
byte exponent = (byte)((value[0] & 127) - 64);
// Mantissa is stored in the remaining bytes, in chunks of 10 bits
int totalBits = (value.Length - 1) * 8;
int mantissaChunks = (int)Math.Ceiling(totalBits / 10d);
var mantissaBits = new BitArray(value);
// Loop each chunk, adding the value to the total mantissa value
decimal mantissa = 0;
int bitPointer = 8;
for (int chunk = mantissaChunks; chunk > 0; chunk--)
{
// The cumulative value for this 10-bit chunk
decimal chunkValue = 0;
// For each bit in the chunk, shift it into position, provided it's set
for (int chunkBit = 9; chunkBit >= 0; chunkBit--)
{
// Since we're looping bytes left-to-right, but read bits right-to-left, we need
// to transform the bit pointer into a relative index within the current byte.
int byteAwareBitPointer = bitPointer + 7 - bitPointer % 8 - (7 - (7 - bitPointer % 8));
// If the bit is set and it's available (SQL Server will truncate 0's), shift it into position
if (mantissaBits.Length > bitPointer && mantissaBits[byteAwareBitPointer])
chunkValue += (1 << chunkBit);
bitPointer++;
}
// Once all bits are in position, we need to raise the significance according to the chunk
// position. First chunk is most significant, last is the least significant. Each chunk
// defining three digits.
mantissa += chunkValue * (decimal)Math.Pow(10, (chunk - 1) * 3);
}
// Mantissa has hardcoded decimal place after first digit
mantissa = mantissa / (decimal)Math.Pow(10, Math.Floor(Math.Log10((double)mantissa)));
// Apply sign and multiply by the exponent
return sign * mantissa * (decimal)Math.Pow(10, exponent);
}
}
/// <summary>
/// Allows you to load a specific record of the [JobPart] table.
/// </summary>
/// <param name="col_JobPartId">Update this description in the "Olymars/Description" extended property of the "JobPartId" column.</param>
public bool Refresh(System.Data.SqlTypes.SqlInt32 col_JobPartId)
{
bool Status;
Reset();
if (col_JobPartId.IsNull)
{
throw new ArgumentNullException("col_JobPartId", "The primary key 'col_JobPartId' can not have a Null value!");
}
this.col_JobPartId = col_JobPartId;
this.Param.Reset();
this.Param.Param_JobPartId = this.col_JobPartId;
System.Data.SqlClient.SqlDataReader DR;
SPs.spS_JobPart SP = new SPs.spS_JobPart(false);
if (SP.Execute(ref this.Param, out DR))
{
Status = false;
if (DR.Read())
{
if (!DR.IsDBNull(SPs.spS_JobPart.Resultset1.Fields.Column_JobId.ColumnIndex))
{
this.col_JobId = DR.GetSqlInt32(SPs.spS_JobPart.Resultset1.Fields.Column_JobId.ColumnIndex);
}
if (!DR.IsDBNull(SPs.spS_JobPart.Resultset1.Fields.Column_Description.ColumnIndex))
{
this.col_Description = DR.GetSqlString(SPs.spS_JobPart.Resultset1.Fields.Column_Description.ColumnIndex);
}
if (!DR.IsDBNull(SPs.spS_JobPart.Resultset1.Fields.Column_JobPartTypeId.ColumnIndex))
{
this.col_JobPartTypeId = DR.GetSqlInt32(SPs.spS_JobPart.Resultset1.Fields.Column_JobPartTypeId.ColumnIndex);
}
if (!DR.IsDBNull(SPs.spS_JobPart.Resultset1.Fields.Column_Units.ColumnIndex))
{
this.col_Units = DR.GetSqlDecimal(SPs.spS_JobPart.Resultset1.Fields.Column_Units.ColumnIndex);
}
if (!DR.IsDBNull(SPs.spS_JobPart.Resultset1.Fields.Column_PricePerUnit.ColumnIndex))
{
this.col_PricePerUnit = DR.GetSqlMoney(SPs.spS_JobPart.Resultset1.Fields.Column_PricePerUnit.ColumnIndex);
}
if (!DR.IsDBNull(SPs.spS_JobPart.Resultset1.Fields.Column_TotalPrice.ColumnIndex))
{
this.col_TotalPrice = DR.GetSqlMoney(SPs.spS_JobPart.Resultset1.Fields.Column_TotalPrice.ColumnIndex);
}
Status = true;
}
if (DR != null && !DR.IsClosed)
{
DR.Close();
}
if (CloseConnectionAfterUse && SP.Connection != null && SP.Connection.State == System.Data.ConnectionState.Open)
{
SP.Connection.Close();
SP.Connection.Dispose();
}
return(Status);
}
else
{
if (DR != null && !DR.IsClosed)
{
DR.Close();
}
if (CloseConnectionAfterUse && SP.Connection != null && SP.Connection.State == System.Data.ConnectionState.Open)
{
SP.Connection.Close();
SP.Connection.Dispose();
}
throw new Bob.DataClasses.CustomException(this.Param, "Bob.AbstractClasses.Abstract_JobPart", "Refresh");
}
}
public static void WriteFormat(TdsParameter p, TdsRequestStream stream, Encoding encoder)
{
if (p.ParameterName != null)
{
var nameBytes = encoder.GetBytes(p.ParameterName);
stream.Write((byte)nameBytes.Length);
stream.Write(nameBytes);
}
else
{
stream.Write(0);
}
stream.Write((byte)(p.IsOutput ? 1 : 0));
stream.WriteInt(0); // user type
stream.Write((byte)p.TdsType);
switch (p.TdsType)
{
case TdsType.TDS_VARBINARY:
case TdsType.TDS_VARCHAR:
stream.Write((byte)VAR_MAX);
break;
case TdsType.TDS_INTN:
if (p.DbType == System.Data.DbType.Int64)
{
stream.Write((byte)8);
}
else
{
stream.Write((byte)4);
}
break;
case TdsType.TDS_FLTN:
if (p.DbType == System.Data.DbType.Single)
{
stream.Write((byte)4);
}
else
{
stream.Write((byte)8);
}
break;
case TdsType.TDS_DATETIMEN:
stream.Write((byte)8);
break;
case TdsType.TDS_DECN:
if (p.Value == null)
{
stream.Write((byte)17);
stream.Write((byte)38);
stream.Write((byte)0);
}
else
{
var sqlDec = new System.Data.SqlTypes.SqlDecimal((decimal)p.Value);
stream.Write((byte)17);
stream.Write(sqlDec.Precision);
stream.Write(sqlDec.Scale);
}
break;
case TdsType.TDS_BIT:
break;
case TdsType.TDS_MONEYN:
default:
throw new NotImplementedException($"Unsupported type {p.TdsType}");
}
stream.Write((byte)0); // Locale information
}
public override object GetValue(byte[] value)
{
if (!CompressionContext.UsesVardecimals)
{
if (value.Length != FixedLength.Value)
{
throw new ArgumentException("Invalid value length: " + value.Length);
}
int[] ints = new int[4];
for (int i = 0; i < getNumberOfRequiredStorageInts(); i++)
{
ints[i] = BitConverter.ToInt32(value, 1 + i * 4);
}
var sqlDecimal = new System.Data.SqlTypes.SqlDecimal(precision, scale, Convert.ToBoolean(value[0]), ints);
// This will fail for any SQL Server decimal values exceeding the max value of a C# decimal.
// Might want to return raw bytes at some point, though it's ugly.
return(sqlDecimal.Value);
}
// Zero values are simply stored as a 0-length variable length field
if (value.Length == 0)
{
return(0m);
}
// Sign is stored in the first bit of the first byte
decimal sign = (value[0] >> 7) == 1 ? 1 : -1;
// Exponent is stored in the remaining 7 bytes of the first byte. As it's biased by 64 (ensuring we won't
// have to deal with negative numbers) we need to subtract the bias to get the real exponent value.
byte exponent = (byte)((value[0] & 127) - 64);
// Mantissa is stored in the remaining bytes, in chunks of 10 bits
int totalBits = (value.Length - 1) * 8;
int mantissaChunks = (int)Math.Ceiling(totalBits / 10d);
var mantissaBits = new BitArray(value);
// Loop each chunk, adding the value to the total mantissa value
decimal mantissa = 0;
int bitPointer = 8;
for (int chunk = mantissaChunks; chunk > 0; chunk--)
{
// The cumulative value for this 10-bit chunk
decimal chunkValue = 0;
// For each bit in the chunk, shift it into position, provided it's set
for (int chunkBit = 9; chunkBit >= 0; chunkBit--)
{
// Since we're looping bytes left-to-right, but read bits right-to-left, we need
// to transform the bit pointer into a relative index within the current byte.
int byteAwareBitPointer = bitPointer + 7 - bitPointer % 8 - (7 - (7 - bitPointer % 8));
// If the bit is set and it's available (SQL Server will truncate 0's), shift it into position
if (mantissaBits.Length > bitPointer && mantissaBits[byteAwareBitPointer])
{
chunkValue += (1 << chunkBit);
}
bitPointer++;
}
// Once all bits are in position, we need to raise the significance according to the chunk
// position. First chunk is most significant, last is the least significant. Each chunk
// defining three digits.
mantissa += chunkValue * (decimal)Math.Pow(10, (chunk - 1) * 3);
}
// Mantissa has hardcoded decimal place after first digit
mantissa = mantissa / (decimal)Math.Pow(10, Math.Floor(Math.Log10((double)mantissa)));
// Apply sign and multiply by the exponent
return(sign * mantissa * (decimal)Math.Pow(10, exponent));
}
public static int GetNumberOfDigitsBeforeDecimalPlace(this decimal dec)
{
var x = new System.Data.SqlTypes.SqlDecimal(dec);
return(x.Precision - x.Scale);
}
