public static SqlGeometry GetImageBound(SqlDouble Longitude, SqlDouble Latitude, SqlInt32 Width, SqlInt32 Height,
        SqlDouble Zoom, SqlInt32 PixelYOffset)
    {
        long cpX, cpY, LeftTopX, LeftTopY, RightBottomX, RightBottomY;
        long halfWidth = ((long) Width) >> 1;
        long halfHeight = ((long) Height) >> 1;
        double dZoom = (double) Zoom;
        // получить центральный пиксел по коорд
        cpX = (long) FromLongitudeToXPixel(Longitude, Zoom);
        cpY = (long) (FromLatitudeToYPixel(Latitude, Zoom) + PixelYOffset);
        LeftTopX = cpX - halfWidth;
        LeftTopY = cpY - halfHeight;
        RightBottomX = cpX + halfWidth;
        RightBottomY = cpY + halfHeight;
        double Lat1, Lon1, Lat2, Lon2;
        Lat1 = FromYPixelToLat(LeftTopY, dZoom);
        Lon1 = FromXPixelToLon(LeftTopX, dZoom);
        Lat2 = FromYPixelToLat(RightBottomY, dZoom);
        Lon2 = FromXPixelToLon(RightBottomX, dZoom);

        //
        var geomBuilder = new SqlGeometryBuilder();
        geomBuilder.SetSrid((0));
        geomBuilder.BeginGeometry(OpenGisGeometryType.Polygon);
        geomBuilder.BeginFigure(Lon1, Lat1);
        geomBuilder.AddLine(Lon1, Lat2);
        geomBuilder.AddLine(Lon2, Lat2);
        geomBuilder.AddLine(Lon2, Lat1);
        geomBuilder.AddLine(Lon1, Lat1);
        geomBuilder.EndFigure();
        geomBuilder.EndGeometry();
        return geomBuilder.ConstructedGeometry;
    }

 public static void FillMatchRow( object data,
    out SqlInt32 index, out SqlChars text )
 {
     MatchNode node = (MatchNode)data;
       index = new SqlInt32( node.Index );
       text = new SqlChars( node.Value.ToCharArray( ) );
 }

 public static SqlBinary MergePHkeys(SqlBinary oldPHkeys, SqlInt16 newSnap, SqlInt32 newPHkey)
 {
     SqlIntArray keyArray = new SqlIntArray(oldPHkeys);
     int[] keys = keyArray.ToArray();
     keys[(short) newSnap] = (int) newPHkey;
     return SqlIntArray.FromArray(keys).ToSqlBuffer();
 }

    public static void SqlSucker_GetConInfo(SqlInt32 magic)
    {
        if (conInfoMagic != magic.Value) {
            throw new System.Exception(
                "This procedure is internal to SqlSucker. "
                + "Do not call it directly.");
        }

        lock (conInfoLock) {
            conInfoOk = false;

            using (SqlConnection ctxCon = new SqlConnection(
                "context connection=true"))
            using (SqlCommand ctxConCmd = new SqlCommand(
                "SELECT server, username, password, tablename, dbname "
                + "FROM SqlSuckerConfig", ctxCon)) {

                ctxCon.Open();

                using (SqlDataReader reader = ctxConCmd.ExecuteReader()) {
                    if (!reader.Read()) {
                        throw new System.Exception(
                            "No rows in SqlSuckerConfig");
                    }

                    tempServer = reader.GetString(0);
                    tempUser = reader.GetString(1);
                    tempPassword = reader.GetString(2);
                    tempTable = reader.GetString(3);
                    tempDatabase = reader.GetString(4);
                    conInfoOk = true;
                }
            }
        }
    }

 public static SqlInt32 OrInt3(
     SqlInt32 value0,
     SqlInt32 value1,
     SqlInt32 value2)
 {
     return OrSqlInt32(value0, value1, value2);
 }

        public static IEnumerable GetEventData(SqlInt32 eventID)
        {
            const string query =
                "SELECT " +
                "    TimeDomainData, " +
                "    FrequencyDomainData " +
                "FROM EventData " +
                "WHERE ID = " +
                "(" +
                "    SELECT EventDataID " +
                "    FROM Event " +
                "    WHERE ID = @id " +
                ")";

            DataSet eventDataSet = new DataSet();

            using (SqlConnection connection = new SqlConnection("context connection=true"))
            using (SqlCommand command = new SqlCommand(query, connection))
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                connection.Open();
                command.Parameters.AddWithValue("@id", eventID);
                adapter.Fill(eventDataSet);
            }

            DataRow row = eventDataSet.Tables[0].Rows[0];

            return ReadFrom(Inflate((byte[])row["TimeDomainData"]))
                .Concat(ReadFrom(Inflate((byte[])row["FrequencyDomainData"]), freq: true))
                .ToArray();
        }

 public static SqlInt32 IsNucX(SqlInt64 posStart, SqlString misMNuc, SqlInt64 refPos, SqlString refNuc, SqlString countNuc)
 {
     SqlInt32 result;
     Dictionary<long, string> mutationPositions = new Dictionary<long, string>();
     string mutationPattern = @"[0-9]+ [ACGTN]+";
     MatchCollection matches = Regex.Matches(misMNuc.Value, mutationPattern);
     foreach (Match match in matches)
     {
         var foundMutation = match.Value;
         string[] foundMutParts = foundMutation.Split(' ');
         long mutStartPos = posStart.Value + Int32.Parse(foundMutParts[0]);
         var mutNuc = foundMutParts[1];
         mutationPositions.Add(mutStartPos, mutNuc);
     }
     string mutValue;
     if (mutationPositions.TryGetValue(refPos.Value, out mutValue))
     {
         result = new SqlInt32(countNuc.Value.Equals(mutValue) ? 1 : 0);
     }
     else
     {
         result = new SqlInt32(countNuc.Value.Equals(refNuc.Value) ? 1 : 0);
     }
     return result;
 }

        public void UpdateIPP(SqlString nIPPId,SqlInt32 nPaymentId)
        {
            myRP.NNullIPPId = nIPPId;
            myRP.NPaymentID = nPaymentId;

            myRP.UpdateAllWnIPP_PaymentIDLogic();
        }

        public static void AddContainerACL(
            SqlString accountName, SqlString sharedKey, SqlBoolean useHTTPS,
            SqlString containerName,
            SqlString containerPublicReadAccess,
            SqlString accessPolicyId,
            SqlDateTime start, SqlDateTime expiry,
            SqlBoolean canRead,
            SqlBoolean canWrite,
            SqlBoolean canDeleteBlobs,
            SqlBoolean canListBlobs,
            SqlGuid LeaseId,
            SqlInt32 timeoutSeconds,
            SqlGuid xmsclientrequestId)
        {
            Enumerations.ContainerPublicReadAccess cpraNew;
            if(!Enum.TryParse<Enumerations.ContainerPublicReadAccess>(containerPublicReadAccess.Value, out cpraNew))
            {
                StringBuilder sb = new StringBuilder("\"" + containerPublicReadAccess.Value + "\" is an invalid ContainerPublicReadAccess value. Valid values are: ");
                foreach (string s in Enum.GetNames(typeof(Enumerations.ContainerPublicReadAccess)))
                    sb.Append("\"" + s + "\" ");
                throw new ArgumentException(sb.ToString());
            }

            AzureBlobService abs = new AzureBlobService(accountName.Value, sharedKey.Value, useHTTPS.Value);
            Container cont = abs.GetContainer(containerName.Value);

            Enumerations.ContainerPublicReadAccess cpra;
            SharedAccessSignature.SharedAccessSignatureACL sasACL = cont.GetACL(out cpra,
                LeaseId.IsNull ? (Guid?)null : LeaseId.Value,
                timeoutSeconds.IsNull ? 0 : timeoutSeconds.Value,
                xmsclientrequestId.IsNull ? (Guid?)null : xmsclientrequestId.Value);

            string strPermissions = "";
            if (canRead.IsTrue)
                strPermissions += "r";
            if (canWrite.IsTrue)
                strPermissions += "w";
            if (canDeleteBlobs.IsTrue)
                strPermissions += "d";
            if (canListBlobs.IsTrue)
                strPermissions += "l";

            SharedAccessSignature.AccessPolicy ap = new SharedAccessSignature.AccessPolicy()
            {
                Start = start.Value,
                Expiry = expiry.Value,
                Permission = strPermissions
            };

            sasACL.SignedIdentifier.Add(new SharedAccessSignature.SignedIdentifier()
                {
                    AccessPolicy = ap,
                    Id = accessPolicyId.Value
                });

            cont.UpdateContainerACL(cpraNew, sasACL,
                LeaseId.IsNull ? (Guid?)null : LeaseId.Value,
                timeoutSeconds.IsNull ? 0 : timeoutSeconds.Value,
                xmsclientrequestId.IsNull ? (Guid?)null : xmsclientrequestId.Value);
        }

 public static SqlString TakeNotNull2(
     SqlInt32 index,
     SqlString text0,
     SqlString text1)
 {
     return TakeNotNull(index, text0, text1);
 }

 private void validateResultSetNumber(SqlInt32 resultsetNo)
 {
     if (resultsetNo < 0 || resultsetNo.IsNull)
     {
         throw new InvalidResultSetException("ResultSet index begins at 1. ResultSet index [" + resultsetNo.ToString() + "] is invalid.");
     }
 }

        private static Byte[] GetFileContents(string table, SqlInt32 fileId, char format)
        {
            Byte[] bytes = null;

            using (var connection = new SqlConnection("context connection=true"))
            {
                connection.Open();

                string sql = "SELECT [Contents] FROM [dbo].[" + table + "] WHERE [FileId] = @fileId AND [Format] = @format";
                using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add(new SqlParameter("@fileId", SqlDbType.Int) { Value = fileId });
                    command.Parameters.Add(new SqlParameter("@format", SqlDbType.Char) { Value = format });

                    using (SqlDataReader results = command.ExecuteReader())
                    {
                        while (results.Read())
                        {
                            bytes = results.GetSqlBytes(0).Buffer;
                        }
                    }
                }
            }
            return bytes;
        }

 public static void GetSkyline(SqlString strQuery, SqlString strOperators, SqlInt32 numberOfRecords, SqlInt32 sortType, SqlInt32 upToLevel)
 {
     SPMultipleSkylineBNLLevel skyline = new SPMultipleSkylineBNLLevel();
     string[] additionalParameters = new string[5];
     additionalParameters[4] = upToLevel.ToString();
     skyline.GetSkylineTable(strQuery.ToString(), strOperators.ToString(), numberOfRecords.Value, false, Helper.CnnStringSqlclr, Helper.ProviderClr, additionalParameters, sortType.Value, true);
 }

        public void sendSelectedResultSetToSqlContext(SqlInt32 resultsetNo, SqlString command)
        {
            validateResultSetNumber(resultsetNo);

            SqlDataReader dataReader = testDatabaseFacade.executeCommand(command);

            int ResultsetCount = 0;
            if (dataReader.FieldCount > 0)
            {
                do
                {
                    ResultsetCount++;
                    if (ResultsetCount == resultsetNo)
                    {
                        sendResultsetRecords(dataReader);
                        break;
                    }
                } while (dataReader.NextResult());
            }
            dataReader.Close();

            if(ResultsetCount < resultsetNo)
            {
                throw new InvalidResultSetException("Execution returned only " + ResultsetCount.ToString() + " ResultSets. ResultSet [" + resultsetNo.ToString() + "] does not exist.");
            }
        }

        public static void ChangeContainerPublicAccessMethod(
            SqlString accountName, SqlString sharedKey, SqlBoolean useHTTPS,
            SqlString containerName,
            SqlString containerPublicReadAccess,            
            SqlGuid LeaseId,
            SqlInt32 timeoutSeconds,
            SqlGuid xmsclientrequestId)
        {
            Enumerations.ContainerPublicReadAccess cpraNew;
            if (!Enum.TryParse<Enumerations.ContainerPublicReadAccess>(containerPublicReadAccess.Value, out cpraNew))
            {
                StringBuilder sb = new StringBuilder("\"" + containerPublicReadAccess.Value + "\" is an invalid ContainerPublicReadAccess value. Valid values are: ");
                foreach (string s in Enum.GetNames(typeof(Enumerations.ContainerPublicReadAccess)))
                    sb.Append("\"" + s + "\" ");
                throw new ArgumentException(sb.ToString());
            }

            AzureBlobService abs = new AzureBlobService(accountName.Value, sharedKey.Value, useHTTPS.Value);
            Container cont = abs.GetContainer(containerName.Value);

            Enumerations.ContainerPublicReadAccess cpra;
            SharedAccessSignature.SharedAccessSignatureACL sasACL = cont.GetACL(out cpra,
                LeaseId.IsNull ? (Guid?)null : LeaseId.Value,
                timeoutSeconds.IsNull ? 0 : timeoutSeconds.Value,
                xmsclientrequestId.IsNull ? (Guid?)null : xmsclientrequestId.Value);

            cont.UpdateContainerACL(cpraNew, sasACL,
                LeaseId.IsNull ? (Guid?)null : LeaseId.Value,
                timeoutSeconds.IsNull ? 0 : timeoutSeconds.Value,
                xmsclientrequestId.IsNull ? (Guid?)null : xmsclientrequestId.Value);
        }

 public static void FormatE_FillRow(
     object inputObject,
     out SqlInt32 lineNumber,
     out SqlString programNumber,
     out SqlString platformId,
     out DateTime transmissionDate,
     out DateTime? locationDate,
     out float? latitude,
     out float? longitude,
     out float? altitude,
     out char? locationClass,
     out Byte[] message)
 {
     var transmission = (ArgosTransmission) inputObject;
     lineNumber = transmission.LineNumber;
     programNumber = transmission.ProgramId;
     platformId = transmission.PlatformId;
     transmissionDate = transmission.DateTime;
     locationDate = transmission.Location == null ? (DateTime?) null : transmission.Location.DateTime;
     latitude = transmission.Location == null ? (float?) null : transmission.Location.Latitude;
     longitude = transmission.Location == null ? (float?)null : transmission.Location.Longitude;
     altitude = transmission.Location == null ? (float?)null : transmission.Location.Altitude;
     locationClass = transmission.Location == null ? (char?)null : transmission.Location.Class;
     message = transmission.Message;
 }

 public static object Box(SqlInt32 a)
 {
     if (a.IsNull)
         return null;
     else
         return a.Value;
 }

        public static void GetSkyline(SqlString strQuery, SqlString strOperators, SqlInt32 numberOfRecords,
            SqlInt32 sortType, SqlInt32 count, SqlInt32 dimension, SqlString algorithm, SqlBoolean hasIncomparable)
        {
            try
            {
                Type strategyType = Type.GetType("prefSQL.SQLSkyline." + algorithm.ToString());

                if (!typeof (SkylineStrategy).IsAssignableFrom(strategyType))
                {
                    throw new Exception("passed algorithm is not of type SkylineStrategy.");
                }

                var strategy = (SkylineStrategy) Activator.CreateInstance(strategyType);

                strategy.Provider = Helper.ProviderClr;
                strategy.ConnectionString = Helper.CnnStringSqlclr;
                strategy.RecordAmountLimit = numberOfRecords.Value;
                strategy.HasIncomparablePreferences = hasIncomparable.Value;
                strategy.SortType = sortType.Value;

                var skylineSample = new SkylineSampling
                {
                    SubsetCount = count.Value,
                    SubsetDimension = dimension.Value,
                    SelectedStrategy = strategy
                };

                DataTable dataTableReturn = skylineSample.GetSkylineTable(strQuery.ToString(), strOperators.ToString());
                SqlDataRecord dataRecordTemplate = skylineSample.DataRecordTemplateForStoredProcedure;

                //throw new Exception(dataTableReturn.Rows[0].Table.Columns.Count.ToString());

                if (SqlContext.Pipe != null)
                {
                    SqlContext.Pipe.SendResultsStart(dataRecordTemplate);

                    foreach (DataRow recSkyline in dataTableReturn.Rows)
                    {
                        for (var i = 0; i < recSkyline.Table.Columns.Count; i++)
                        {
                            dataRecordTemplate.SetValue(i, recSkyline[i]);
                        }
                        SqlContext.Pipe.SendResultsRow(dataRecordTemplate);
                    }
                    SqlContext.Pipe.SendResultsEnd();
                }
            }
            catch (Exception ex)
            {
                //Pack Errormessage in a SQL and return the result
                var strError = "Error in prefSQL_SkylineSampling: ";
                strError += ex.Message;

                if (SqlContext.Pipe != null)
                {
                    SqlContext.Pipe.Send(strError);
                }
            }
        }

    public static IEnumerable FindPHkeysAndSlots(SqlInt16 partsnap, SqlInt16 fofsnap, SqlString temp_partid_table_name, SqlInt32 numparts)
    {
        //string temp_partid_table = "#temp_partid_list";
        List<PhkeySlot> keysAndSlots = new List<PhkeySlot>();
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            string getFoFGroupIDsCommandString = "select partid from " + temp_partid_table_name.ToString();
            SqlCommand getFoFGroupIDsListCommand = new SqlCommand(getFoFGroupIDsCommandString, connection);
            SqlDataReader getFOFGroupIDsReader = getFoFGroupIDsListCommand.ExecuteReader();
            Int64[] partids = new Int64[(int) numparts];
            int numlines = 0;
            while (getFOFGroupIDsReader.Read())
            {
                partids[numlines] = Int64.Parse(getFOFGroupIDsReader["partid"].ToString());
                ++numlines;
            }
            getFOFGroupIDsReader.Close();
            // Find the index blocks we need to look in
            HashSet<Int64> blocks = new HashSet<Int64>();
            foreach (Int64 id in partids)
            {
                blocks.Add((id - 1) / 1024 / 1024);
            }

            //means we want all snaps
            string rawCommand = "";
            if (partsnap < 0)
            {
                rawCommand = "select a.snap, a.phkey, a.slot from particleDB.dbo.index{0} a, " + temp_partid_table_name.ToString() + " b "
                    + " where a.partid = b.partid";
            }
            else
            {
                rawCommand = "select a.snap, a.phkey, a.slot from particleDB.dbo.index{0} a, " + temp_partid_table_name.ToString() + " b "
                    + " where a.partid = b.partid and a.snap = @partsnap";
            }

            foreach (Int64 blockID in blocks)
            {
                string joinFoFGroupIndexCommandString = string.Format(rawCommand, blockID);
                SqlCommand joinFofGroupIndexCommand = new SqlCommand(joinFoFGroupIndexCommandString, connection);
                SqlParameter partsnapParam = new SqlParameter("@partsnap", System.Data.SqlDbType.SmallInt);
                partsnapParam.Value = partsnap;
                joinFofGroupIndexCommand.Parameters.Add(partsnapParam);
                SqlDataReader joinReader = joinFofGroupIndexCommand.ExecuteReader();
                while (joinReader.Read())
                {
                    int snap = Int32.Parse(joinReader["snap"].ToString());
                    int phkey = Int32.Parse(joinReader["phkey"].ToString());
                    short slot = Int16.Parse(joinReader["slot"].ToString());
                    PhkeySlot current = new PhkeySlot(snap, phkey, slot);
                    keysAndSlots.Add(current);
                }
                joinReader.Close();
            }
        }
        return keysAndSlots;
    }

    public static SqlString fnGetErrorMessage(SqlString aErrorMessage, SqlInt32 aLCID, SqlInt32 aDefaultLCID)
    {
        if (aErrorMessage.IsNull)
        {
            return SqlString.Null;
        }
        string errmsg = aErrorMessage.Value;
        string[] criterias = {
            "'(?<pattern>(CK_.+?))'",
            "'(?<pattern>(FK_.+?))'",
            "'(?<pattern>(PK_.+?))'",
            "'(?<pattern>(UQ_.+?))'",

            "\"(?<pattern>(CK_.+?))\"",
            "\"(?<pattern>(FK_.+?))\"",
            "\"(?<pattern>(PK_.+?))\"",
            "\"(?<pattern>(UQ_.+?))\"" 
        };

        Match m = null;
        for (int i = 0; i < criterias.Length; i++)
        {
            m = Regex.Match(errmsg, criterias[i]);
            if (m.Success)
                break;
        }

        if (m.Success)
        {
            string ConstraintName = m.Groups["pattern"].Value;
            SqlString msg = null;
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand command = new SqlCommand(
                               " select dbo.fnXMLGetMessageValue(cm.Description, @lcid, @default_lcid)" +
                               "   from dbo.ConstraintMessage cm" +
                               "  where cm.ConstraintName = @ConstraintName",
                               conn);
                command.Parameters.Add(new SqlParameter("@lcid", aLCID.IsNull ? 1033 : aLCID.Value));
                command.Parameters.Add(new SqlParameter("@default_lcid", aDefaultLCID.IsNull ? 1033 : aDefaultLCID.Value));
                command.Parameters.Add(new SqlParameter("@ConstraintName", ConstraintName));
                conn.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        msg = reader.GetSqlString(0);
                    }
                }
                conn.Close();
            }
            return (msg.IsNull) ? aErrorMessage : msg;
        }
        else
        {
            return aErrorMessage;
        }

    }