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