private static int ProcessBinary(SqlBytes data, SqlConnection cn, SqlTransaction transaction, long id)
        {
            //Add the binary data
            SqlCommand sqlCmd;
            //if (transaction != null)
            //    sqlCmd = new SqlCommand("spx_EntityBinaryAdd", cn, transaction);
            //else
            //    sqlCmd = new SqlCommand("spx_EntityBinaryAdd", cn);
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            if (transaction != null)
                sqlCmd = new SqlCommand(ProcessBinarySQL, cn, transaction);
            else
                sqlCmd = new SqlCommand(ProcessBinarySQL, cn);
            sqlCmd.CommandType = CommandType.Text;

            sqlCmd.Parameters.Add("@Entity_Sequence_ID", SqlDbType.BigInt).Value = id;
            sqlCmd.Parameters.Add("@Data", SqlDbType.Binary).Value = data;

            //SqlParameter paramRet = new SqlParameter("@return_value", SqlDbType.Int);
            //paramRet.Direction = ParameterDirection.ReturnValue;
            //sqlCmd.Parameters.Add(paramRet);

            int count = sqlCmd.ExecuteNonQuery();

            return (count>0)?200:500;
        }

 override public void SetCapacity(int capacity) {
     SqlBytes[] newValues = new SqlBytes[capacity];
     if (null != values) {
         Array.Copy(values, 0, newValues, 0, Math.Min(capacity, values.Length));
     }
     values = newValues;
 }

 public static SqlString Date2JulianMarkup(SqlBytes input)
 {
     if (input.IsNull)
     {
       return SqlString.Null;
     }
     byte[] inputBytes = input.Value;
     int year = (inputBytes[0] << 4 | inputBytes[1] >> 4) - 1024;
     int month = (inputBytes[1] & 0x0F) - 1;
     int day = inputBytes[2] >> 3;
     string result;
     DateTime date = new DateTime(year, month, day);
     JulianCalendar julianCalendar = new JulianCalendar();
     int jYear = julianCalendar.GetYear(date);
     int jMonthOffset = julianCalendar.GetMonth(date) - 1;
     int jDay = julianCalendar.GetDayOfMonth(date);
     if (jYear == year)
     {
       result = "[Day[" + date_time_format_info.GetMonthName(month) + "-" + day + "|" + date_time_format_info.GetMonthName(month) + " " + day + "]] (O.S. " + date_time_format_info.GetMonthName(month) + " " + jDay + "), " + "[Year[" + year + "]]";
     }
     else
     {
       result = "[Day[" + date_time_format_info.GetMonthName(month) + "-" + day + "|" + date_time_format_info.GetMonthName(month) + " " + day + "]], [Year[" + year + "|" + year + "]], (O.S. " + date_time_format_info.GetMonthName(month) + " " + jDay + ", " + jYear + ")";
     }
     if ((inputBytes[2] & 0x02) == 0)
     {
       result = "circa " + result;
     }
     if ((inputBytes[2] & 0x04) == 0)
     {
       result = "? " + result;
     }
     return new SqlString(result);
 }

		public void SqlBytesItem ()
		{
			SqlBytes bytes = new SqlBytes ();
			try {
				Assert.AreEqual (bytes [0], 0, "#1 Should throw SqlNullValueException");
				Assert.Fail ("Should throw SqlNullValueException");
			} catch (Exception ex) {
				Assert.AreEqual (typeof (SqlNullValueException), ex.GetType (), "Should throw SqlNullValueException");
			}
			byte [] b = null;
			bytes = new SqlBytes (b);
			try {
				Assert.AreEqual (bytes [0], 0, "#2 Should throw SqlNullValueException");
				Assert.Fail ("Should throw SqlNullValueException");
			} catch (Exception ex) {
				Assert.AreEqual (typeof (SqlNullValueException), ex.GetType (), "Should throw SqlNullValueException");
			}
			b = new byte [10];
			bytes = new SqlBytes (b);
			Assert.AreEqual (bytes [0], 0, "");
			try {
				Assert.AreEqual (bytes [-1], 0, "");
				Assert.Fail ("Should throw ArgumentOutOfRangeException");
			} catch (Exception ex) {
				Assert.AreEqual (typeof (ArgumentOutOfRangeException), ex.GetType (), "Should throw ArgumentOutOfRangeException");
			}
			try {
				Assert.AreEqual (bytes [10], 0, "");
				Assert.Fail ("Should throw ArgumentOutOfRangeException");
			} catch (Exception ex) {
				Assert.AreEqual (typeof (ArgumentOutOfRangeException), ex.GetType (), "Should throw ArgumentOutOfRangeException");
			}
		}

 public static char FileFormat(SqlBytes data)
 {
     //get the first line of the file
     var fileHeader = ReadHeader(data.Buffer, Encoding.UTF8, 500).Trim().Normalize();  //database for header is only 450 char
     char code = '?';
     using (var connection = new SqlConnection("context connection=true"))
     {
         connection.Open();
         const string sql = "SELECT [Header], [FileFormat], [Regex] FROM [dbo].[LookupCollarFileHeaders]";
         using (var command = new SqlCommand(sql, connection))
         {
             using (SqlDataReader results = command.ExecuteReader())
             {
                 while (results.Read())
                 {
                     var header = results.GetString(0).Normalize();
                     var format = results.GetString(1)[0]; //.GetChar() is not implemented
                     var regex = results.IsDBNull(2) ? null : results.GetString(2);
                     if (fileHeader.StartsWith(header, StringComparison.OrdinalIgnoreCase) ||
                         (regex != null && new Regex(regex).IsMatch(fileHeader)))
                     {
                         code = format;
                         break;
                     }
                 }
             }
         }
     }
     if (code == '?' && new ArgosEmailFile(data.Buffer).GetPrograms().Any())
         // We already checked for ArgosAwsFile with the header
         code = 'E';
     return code;
 }

 /// <summary>
 /// Creates the result reader bad mock.
 /// </summary>
 /// <returns></returns>
 public static IDataReader CreateResultReaderBadMock()
 {
     var stamp = new SqlBytes(UnitTestValues.TimestampBegin);
     var mock = MockRepository.GenerateMock<SqlDataReader>();
     mock.Expect(x => x.GetOrdinal("Id")).Return(1);
     mock.Expect(x => x.IsDBNull(1)).Return(true);
     mock.Expect(x => x.GetInt32(1)).Throw(new InvalidOperationException("You did something bad!"));
     mock.Expect(x => x.GetOrdinal("VersionTimestamp")).Return(2);
     mock.Expect(x => x.GetSqlBytes(2)).Return(stamp);
     return mock;
 }

        public static void ssb_get_certificate_blob(
            SqlString dbName,
            SqlString certName,
            out SqlBytes blob)
        {
            SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
            scsb.ContextConnection = true;
            SqlConnection connection = new SqlConnection(scsb.ConnectionString);
            connection.Open();

            using (connection)
            {
                connection.ChangeDatabase(dbName.Value);

                string tempPath = Path.GetTempPath();
                string certFile = Path.Combine(
                    tempPath, Path.GetRandomFileName());

                try
                {
                    if (false == Directory.Exists(tempPath))
                    {
                        Directory.CreateDirectory(tempPath);
                    }

                    SqlCommand cmd = new SqlCommand(
                        String.Format(
                            @"BACKUP CERTIFICATE [{0}] TO FILE = '{1}';",
                            certName.Value.Replace("]", "]]"),
                            certFile),
                        connection);

                    cmd.ExecuteNonQuery();

                    blob = new SqlBytes(File.ReadAllBytes(certFile));
                }
                finally
                {
                    if (File.Exists(certFile))
                    {
                        try
                        {
                            File.Delete(certFile);
                        }
                        catch (IOException)
                        {
                        }
                    }
                }
            }
        }

    public static TListInt32 FromCompressedBinary(SqlBytes AData)
    {
        if (AData.IsNull) return TListInt32.Null;

        TListInt32 LResult = new TListInt32();
        System.IO.BinaryReader r = new System.IO.BinaryReader(AData.Stream);

        int LCount = Sql.Read7BitEncodedInt(r);

        LResult.FList.Capacity = LCount;
        for(; LCount > 0; LCount--)
          LResult.FList.Add(Sql.Read7BitEncodedInt(r));

        return LResult;
    }

    public static Boolean BinaryContains(SqlBytes AData, Int32 AValue)
    {
        if(AData.IsNull) return false;

        System.IO.BinaryReader r = new System.IO.BinaryReader(new System.IO.MemoryStream(AData.Buffer));

        #if DEBUG
        int LCount = r.ReadInt32();
        #else
        int LCount = Sql.Read7BitEncodedInt(r);
        #endif

        for(; LCount > 0; LCount--)
          if(r.ReadInt32() == AValue) return true;

        return false;
    }

 public void SqlBytesItem()
 {
     SqlBytes bytes = new SqlBytes();
     try
     {
         Assert.Equal(bytes[0], 0);
         Assert.False(true);
     }
     catch (Exception ex)
     {
         Assert.Equal(typeof(SqlNullValueException), ex.GetType());
     }
     byte[] b = null;
     bytes = new SqlBytes(b);
     try
     {
         Assert.Equal(bytes[0], 0);
         Assert.False(true);
     }
     catch (Exception ex)
     {
         Assert.Equal(typeof(SqlNullValueException), ex.GetType());
     }
     b = new byte[10];
     bytes = new SqlBytes(b);
     Assert.Equal(bytes[0], 0);
     try
     {
         Assert.Equal(bytes[-1], 0);
         Assert.False(true);
     }
     catch (Exception ex)
     {
         Assert.Equal(typeof(ArgumentOutOfRangeException), ex.GetType());
     }
     try
     {
         Assert.Equal(bytes[10], 0);
         Assert.False(true);
     }
     catch (Exception ex)
     {
         Assert.Equal(typeof(ArgumentOutOfRangeException), ex.GetType());
     }
 }

    public static SqlBinary GetHash(SqlString algorithm, SqlBytes src)
    {
        if (src.IsNull)
            return null;

        switch (algorithm.Value.ToUpperInvariant())
        {
            case "MD5":
                return new SqlBinary(MD5.Create().ComputeHash(src.Stream));
            case "SHA1":
                return new SqlBinary(SHA1.Create().ComputeHash(src.Stream));
            case "SHA2_256":
                    return new SqlBinary(SHA256.Create().ComputeHash(src.Stream));
            case "SHA2_512":
                    return new SqlBinary(SHA512.Create().ComputeHash(src.Stream));
            default:
                throw new ArgumentException("HashType", "Unrecognized hashtype: " + algorithm.Value);
        }
    }

        private SqlDataReaderWrapper CreateSqlDataReaderWrapper(object spatialProviderValueToReturn, string providerDataType)
        {
            var mockSqlDataReader = new Mock<SqlDataReaderWrapper>();

            using (var memoryStream = new MemoryStream())
            {
                var writer = new BinaryWriter(memoryStream);

                MethodInfo writeMethod = spatialProviderValueToReturn.GetType().GetMethod("Write", BindingFlags.Public | BindingFlags.Instance,
                    binder: null, types: new[] { typeof(BinaryWriter) }, modifiers: null);
                writeMethod.Invoke(spatialProviderValueToReturn, new[] { writer });
                var sqlBytes = new SqlBytes(memoryStream.ToArray());

                mockSqlDataReader.Setup(m => m.GetSqlBytes(0)).Returns(sqlBytes);
                mockSqlDataReader.Setup(m => m.GetFieldValueAsync<SqlBytes>(0, CancellationToken.None)).Returns(Task.FromResult(sqlBytes));
                mockSqlDataReader.Setup(m => m.GetDataTypeName(0)).Returns(providerDataType);
            }

            return mockSqlDataReader.Object;
        }

    public static IEnumerable AddFilterToSnap(SqlBytes arrayBinary, short snap, int phkey)
    {
        List<object[]> results = new List<object[]>();

            SqlBigIntArrayMax myIds = new SqlBigIntArrayMax(arrayBinary);

            const int expectedFilterCapacity = 1000;
            float errorRate = BloomFilter.Filter<long>.bestErrorRate(expectedFilterCapacity);
            int hashFunctions = BloomFilter.Filter<long>.bestK(expectedFilterCapacity, errorRate);
            BloomFilter.Filter<long> filter = new BloomFilter.Filter<long>(expectedFilterCapacity, errorRate, hashFunctions);

            long[] idsArray = myIds.ToArray();
            for (int i = 0; i < idsArray.Length; i++)
            {
                filter.Add(idsArray[i]);
            }
            object[] item = { snap, phkey, filter.convertToByteArray(), hashFunctions, expectedFilterCapacity };
            results.Add(item);
            return results;
            //finalFilter = filter.convertToByteArray();
    }

        private static int ProcessEntity(SqlBytes data, SqlConnection cn, SqlTransaction transaction, 
            out SqlInt64 insertID, out Guid typeID)
        {
            Entity ent = new Entity(data.Stream, false);
            typeID = ent.tid;
            string[] entityType = ent.mcType.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

            if (entityType.Length < 2)
                throw new InvalidAQNException(string.Format("AQN is invalid: {0}", ent.mcType));

            //Add the base entity
            SqlCommand sqlCmd;
            insertID = SqlInt64.Null;

            if (transaction!=null)
                sqlCmd = new SqlCommand(EntityCreateSQL, cn, transaction);
            else
                sqlCmd = new SqlCommand(EntityCreateSQL, cn);

            sqlCmd.CommandType = CommandType.Text;

            sqlCmd.Parameters.Add("@Entity_Version_ID", SqlDbType.UniqueIdentifier).Value = ent.vid;
            sqlCmd.Parameters.Add("@Entity_Content_ID", SqlDbType.UniqueIdentifier).Value = ent.cid;
            sqlCmd.Parameters.Add("@Entity_Type_ID", SqlDbType.UniqueIdentifier).Value = typeID;
            sqlCmd.Parameters.Add("@Assembly_Qualified_Name", SqlDbType.NVarChar, 255).Value = entityType[0] + ", " + entityType[1];
            sqlCmd.Parameters.Add("@Create_Date", SqlDbType.DateTime).Value = DateTime.Now;

            using (SqlDataReader reader = sqlCmd.ExecuteReader(CommandBehavior.SingleRow))
            {
                if (reader.HasRows && reader.Read())
                {
                    insertID = reader.GetSqlInt64(0);
                }
            }

            if (insertID.IsNull)
                return 515;

            return 200;
        }

        // note: length < 0 indicates write everything
        private static void SetSqlBytes_Unchecked( SmiEventSink_Default sink, ITypedSettersV3 setters, int ordinal, SqlBytes value, int offset, long length ) {
            if ( value.IsNull ) {
                setters.SetDBNull( sink, ordinal );
                sink.ProcessMessagesAndThrow();
            }
            else {
                int chunkSize;
                if ( length > __maxByteChunkSize || length < 0 ) {
                    chunkSize = __maxByteChunkSize;
                }
                else {
                    chunkSize = checked( (int)length );
                }

                byte[] buffer = new byte[ chunkSize ];
                long bytesRead;
                long bytesWritten = 1;  // prime value to get into write loop
                long currentOffset = offset;
                long lengthWritten = 0;

                while ( (length < 0  || lengthWritten < length) &&
                        0 != ( bytesRead = value.Read( currentOffset, buffer, 0, chunkSize ) ) &&
                        0 != bytesWritten ) {
                    bytesWritten = setters.SetBytes( sink, ordinal, currentOffset, buffer, 0, checked( (int) bytesRead ) );
                    sink.ProcessMessagesAndThrow();
                    checked{ currentOffset += bytesWritten; }
                    checked{ lengthWritten += bytesWritten; }
                }

                // Make sure to trim any left-over data
                setters.SetBytesLength( sink, ordinal, currentOffset );
                sink.ProcessMessagesAndThrow();
            }
        }

 private static void SetSqlBytes_LengthChecked( SmiEventSink_Default sink, ITypedSettersV3 setters, int ordinal, SmiMetaData metaData, SqlBytes value, int offset ) {
     int length = 0;
     if ( !value.IsNull ) {
         // Deal with large values by sending bufferLength of NoLengthLimit (== assume 
         //  CheckXetParameters will ignore requested-length checks in this case
         long bufferLength = value.Length;
         if ( bufferLength > Int32.MaxValue ) {
             bufferLength = NoLengthLimit;
         }
         length = CheckXetParameters( metaData.SqlDbType, metaData.MaxLength, NoLengthLimit /* actual */, 0, checked( (int) bufferLength ), offset, checked( (int) bufferLength ) );
     }
     SetSqlBytes_Unchecked( sink, setters, ordinal, value, 0, length ); 
 }

        internal static void SetSqlBytes( SmiEventSink_Default sink, ITypedSettersV3 setters, int ordinal, SmiMetaData metaData, SqlBytes value ) {
            ThrowIfInvalidSetterAccess( metaData, ExtendedClrTypeCode.SqlBytes );

            SetSqlBytes_LengthChecked( sink, setters, ordinal, metaData, value, 0 );
        }

        private Boolean ReadValues(System.IO.BinaryReader r)
        {
            Int32 count;
              if(r.BaseStream.Length == 0) return false;
              count = Sql.Read7BitEncodedInt(r);

              if (count == 0) return true;

              for (Int32 i = 0; i < count; i++)
              {
            String    name  = r.ReadString();
            SqlDbType LType = (SqlDbType)r.ReadUInt16();
            Object    value = null;
            Int32 len;
            //Int32 lcid;
            //SqlCompareOptions co;

            switch (LType)
            {
              case SqlDbType.Bit      : value = new SqlBoolean(r.ReadBoolean()); break;
              case SqlDbType.TinyInt  : value = new SqlByte(r.ReadByte()); break;
              case SqlDbType.SmallInt : value = new SqlInt16((Int16)r.ReadInt16()); break;
              case SqlDbType.Int      : value = new SqlInt32((Int32)r.ReadInt32()); break;
              case SqlDbType.BigInt   : value = new SqlInt64(r.ReadInt64()); break;

              case SqlDbType.Binary   :
              case SqlDbType.VarBinary: len = r.ReadUInt16(); value = new SqlBytes(r.ReadBytes(len)); break;

              case SqlDbType.Char     :
              case SqlDbType.VarChar  : //value = new Sql.SqlAnsiString(r); break;
              case SqlDbType.NChar:
              case SqlDbType.NVarChar:
            //co = (SqlCompareOptions)r.ReadUInt16();
            //lcid = r.ReadInt32();
            //value = new SqlString(r.ReadString(), lcid, co);
            value = new SqlString(r.ReadString());
            break;

              case SqlDbType.DateTime     : value = new SqlDateTime(DateTime.FromBinary(r.ReadInt64())); break;
              case SqlDbType.SmallDateTime:
              case SqlDbType.Date         :
              case SqlDbType.DateTime2    : value = DateTime.FromBinary(r.ReadInt64()); break;
              case SqlDbType.Time         : value = TimeSpan.FromTicks(r.ReadInt64()); break;
              case SqlDbType.DateTimeOffset:
            DateTime LDateTime = DateTime.FromBinary(r.ReadInt64());
            value = new DateTimeOffset(LDateTime, TimeSpan.FromTicks(r.ReadInt64()));
            break;

              case SqlDbType.Decimal: value = new SqlDecimal(r.ReadDecimal()); break;
              case SqlDbType.Float  : value = new SqlDouble(r.ReadDouble()); break;
              // Not support SqlDbType.Image
              case SqlDbType.Money  : value = new SqlMoney(r.ReadDecimal()); break;
              case SqlDbType.Real   : value = new SqlSingle(r.ReadDouble()); break;
              case SqlDbType.SmallMoney: value = new SqlMoney(r.ReadDecimal()); break;
              // Not support SqlDbType.Structured
              // Not support SqlDbType.Text
              // Not support SqlDbType.Timestamp
              case SqlDbType.UniqueIdentifier: value = new SqlGuid(r.ReadString()); break;
              // Not support SqlDbType.Variant
              case SqlDbType.Xml:
            XmlReader rXml = XmlReader.Create(new System.IO.StringReader(r.ReadString()));
            value = new SqlXml(rXml);
            break;

              case SqlDbType.Udt:
            // TODO: Пока поддержа только TParams
            //String LTypeName = r.ReadString();
            //value = CreateUdtObject(LTypeName);
            //if (value is IBinarySerialize)
            //  (value as IBinarySerialize).Read(r);
            //else
            //  throw new Exception(String.Format("Невозможно прочитать данные типа UDT '{0}' - не поддерживается IBinarySerialize", LTypeName));
            value = new SqlUdt(r);
            break;

              default:
            throw new Exception(String.Format("Невозможно прочитать данные, тип '{0}' не поддерживается текущей версией {1}", LType.ToString(), this.GetType().Name));
              // Not support SqlDbType.NText
            }
            if (value != null) FData.Add(name, value);
              }

              return true;
        }

 public void Write(SqlBytes b)
 {
     this.Write(b.Value.LongLength);
     base.Write(b.Value);
 }

 private IComparable Deserialize(SqlBytes bytes)
 {
     using ( MemoryStream stream = new MemoryStream(bytes.Buffer) )
      {
     return (IComparable)_formatter.Deserialize(stream);
      }
 }