Raporti andmeallika akna taastamine

Ctrl+Alt+D klahvikombinatsioon toob raporti Report Data andmeallikate akna tagasi, kui see peaks kadunud olema

Eesti isikukoodi kontrollsumma arvutamise funktsioon Transact-SQL

-- =============================================
-- Author: Kuido Külm
-- Description: Arvutab Eesti isikukoodi kontrollsumma
-- =============================================
CREATE FUNCTION [dbo].[EST_PERSON_IDCODE_CHECKSUM_S](@isikukood NVARCHAR(11))
RETURNS NVARCHAR(1)
AS
BEGIN
DECLARE  @checksum INT = -1, @sum INT=0, @weight INT=1, @i INT=1,@k INT
WHILE @i <= 10
BEGIN
    SET @k=CAST(SUBSTRING(@isikukood,@i,1) AS INT)*@weight
SET @sum=@sum+@k
IF @weight = 9
 SET @weight=1
ELSE
 SET @weight=@weight+1
SET @i=@i+1
END
SET @checksum=@sum%11
IF @checksum != 10
  RETURN CAST(@checksum AS NVARCHAR(1))

SET @sum=0
SET @weight=3
SET @i=1
WHILE @i <= 10
BEGIN
    SET @k=CAST(SUBSTRING(@isikukood,@i,1) AS INT)*@weight
SET @sum=@sum+@k
IF @weight = 9
 SET @weight=1
ELSE
 SET @weight=@weight+1
SET @i=@i+1
END
SET @checksum=@sum%11
IF @checksum != 10
  RETURN CAST(@checksum AS NVARCHAR(1))

RETURN '0'
END

ID-kaardi tarkvara alates versionist 3.10.2 ning IIS6 ja IIS7

Sellise asja peale juhtusin, kus peale ID-kaardi baastarakvara uuendamist alates versioonist 3.10.2 ei hakanud enam tööle ID-kaardiga autentimine, ehk

 HttpClientCertificate sert = Request.ClientCertificate;
 if (!sert.IsPresent)
 {

sai lati vastuseks, et sertifikaati pole, ehk PIN1 ei küsitud

Asja saab taas töökorda Windows'i serveri registris asukohas HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL võtme SendTrustedIssuerList väärtusega 0 (tüüp REG_DWORD) lisamisega

Nimeruumiga XML faili foreach tsükli läbiketramine ja XPath nimeruumi kasutamine

Mõnikord pannakse XML standardi kohaselt nimeruum dokumendile juurde.
Näiteks ISO20022 kasutamine võib tekitada sellise dokumendi
kus dokumendis on defineeritud ka nimeruum
xmlns="urn:iso:std:iso:20022:tech:xsd:camt.052.001.02"

<?xml version='1.0' encoding='UTF-8'?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.052.001.02">
<BkToCstmrAcctRpt>
<GrpHdr>
<MsgId>2014013002396394670001</MsgId>
</GrpHdr>
<Rpt>
<Id>552859428-EUR-1</Id>

</Rpt>
            <CreDtTm>2014-01-30T16:00:09</CreDtTm>
            <FrToDt>
                <FrDtTm>2014-01-30T00:00:00</FrDtTm>
                <ToDtTm>2014-01-30T16:00:15</ToDtTm>
            </FrToDt>
            <Acct>
                <Id>
                    <IBAN>EE112670221023582381</IBAN>
                </Id>
            <Acct>
             <NtryDtls>
                    <TxDtls>
                        <Refs>
                            <AcctSvcrRef>2014012300101119-1</AcctSvcrRef>
                            <EndToEndId>NOT PROVIDED</EndToEndId>
                        </Refs>
                        <AmtDtls>
                            <InstdAmt>
                                <Amt Ccy="EUR">132.29</Amt>
                            </InstdAmt>
                            <TxAmt>
                                <Amt Ccy="EUR">132.29</Amt>
                            </TxAmt>
                        </AmtDtls>
                        <RltdPties>
                            <Dbtr>
                                <Nm>PAOLO OSCAR GARCIA</Nm>
                                <PstlAdr>
                                    <Ctry>ES</Ctry>
                                    <AdrLine>CL.SAN ROQUE, 61 12-4 12004 CASTELLON DE LA PLANA, CASTELLON/C</AdrLine>
                                </PstlAdr>
                            </Dbtr>
                            <DbtrAcct>
                                <Id>
                                    <IBAN>ES9862342069989523042901</IBAN>
                                </Id>
                            </DbtrAcct>
                        </RltdPties>
                        <RltdAgts>
                            <DbtrAgt>
                                <FinInstnId>
                                    <BIC>EVOBESMMXXX</BIC>
                                </FinInstnId>
                            </DbtrAgt>
                        </RltdAgts>
                        <RmtInf>
                            <Ustrd>1098052330002 PAOLO OSCAR GARCIA</Ustrd>
                        </RmtInf>
                    </TxDtls>
                </NtryDtls>
            </Ntry>
            <Ntry>
                <NtryRef>2014012300203432-1</NtryRef>
                <Amt Ccy="EUR">68.97</Amt>
                <CdtDbtInd>CRDT</CdtDbtInd>
                <Sts>BOOK</Sts>
                <BookgDt>
                    <Dt>2014-01-23</Dt>
                </BookgDt>
                <ValDt>
                    <Dt>2014-01-23</Dt>
                </ValDt>
                <AcctSvcrRef>2014012300203432-1</AcctSvcrRef>
                <BkTxCd>
                    <Domn>
                        <Cd>PMNT</Cd>
                        <Fmly>
                            <Cd>RCDT</Cd>
                            <SubFmlyCd>DMCT</SubFmlyCd>
                        </Fmly>
                    </Domn>
                    <Prtry>
                        <Cd>MK</Cd>
                        <Issr>Swedbank AS</Issr>
                    </Prtry>
                </BkTxCd>
                <NtryDtls>
                    <TxDtls>
                        <Refs>
                            <AcctSvcrRef>2014012300203432-1</AcctSvcrRef>
                            <InstrId>11</InstrId>
                        </Refs>
                        <AmtDtls>
                            <TxAmt>
                                <Amt Ccy="EUR">68.97</Amt>
                            </TxAmt>
                        </AmtDtls>
                        <RltdPties>
                            <Dbtr>
                                <Nm>Paul Hark</Nm>
                                <Id>
                                    <PrvtId>
                                        <Othr>
                                            <Id>3933034216</Id>
                                        </Othr>
                                    </PrvtId>
                                </Id>
                            </Dbtr>
                            <DbtrAcct>
                                <Id>
                                    <IBAN>EE634129278536542306</IBAN>
                                </Id>
                            </DbtrAcct>
                        </RltdPties>
                        <RltdAgts>
                            <DbtrAgt>
                                <FinInstnId>
                                    <BIC>EKRDEE22</BIC>
                                </FinInstnId>
                            </DbtrAgt>
                        </RltdAgts>
                        <RmtInf>
                            <Ustrd>Nooruse 17-2 Arve nr.  109316 Paul Hark</Ustrd>
                            <Strd>
                                <CdtrRefInf>
                                    <Tp>
                                        <CdOrPrtry>
                                            <Cd>SCOR</Cd>
                                        </CdOrPrtry>
                                    </Tp>
                                    <Ref>1093160210005</Ref>
                                </CdtrRefInf>
                            </Strd>
                        </RmtInf>
                    </TxDtls>
                </NtryDtls>
            </Ntry>
</BkToCstmrAcctRpt>
</Document>


Sellise faili lugemina käib nagu ikka

XmlDocument xdoc = new XmlDocument();
xdoc.Load(file);

kuna dokumendil on nimeruum siis tuleb see XmlNamespaceManageri kasutades ära näidata

XmlNamespaceManager nsmgr = new XmlNamespaceManager(xdoc.NameTable);
nsmgr.AddNamespace("bk", "urn:iso:std:iso:20022:tech:xsd:camt.052.001.02");

bk on nüüd nimeruumi alias mida XPathis kasutada tuleb.

Paar asja mis tuleb tähele panna

Esiteks nimeruumi kasutamine XPath otsingus, alias tuleb iga pathi ette kirjutada

bk defineerisime AddNamespace  meetodiga
XmlNode nod = xdoc.SelectSingleNode("//bk:Rpt//bk:Acct//bk:Id//bk:IBAN", nsmgr);
string pkood = nod.InnerText.Trim();

ehk näite põhjal tuleb vastuseks  EE112670221023582381

Teiseks foreach tsüklis tipule viitamine, siin tuleb XPath avaldist alustada punktiga

 XmlNodeList rows = xdoc.SelectNodes("//bk:Ntry", nsmgr);

foreach (XmlNode nrow in rows)
{
 nod = nrow.SelectSingleNode(".//bk:NtryDtls//bk:TxDtls//bk:RltdPties//bk:Dbtr//bk:Nm", nsmgr);
}

kui punkti algusse ei pane siis tsükkel võtab alati esimese leitud XPathi avaldise tulemuse

TableAdapteri CommandTimeouti muutmine

Mõnikord juhtub, et andmebaasis pikk päring ja vaja CommandTimeouti pikemaks panna, et veebiserver ära ei katkestaks, seda saab teha partial klassiga

PAST_DUE_ACCOUNTS_S on TableAdapteri klassi nimi millele tuleb uus klass lisada

public partial class PAST_DUE_ACCOUNTS_STableAdapter
{
        public void SetCommandTimeout(int timeout)
        {
        foreach (IDbCommand command in CommandCollection)
        command.CommandTimeout = timeout;
        }
}

IIS8 kliendi sertfikaadiga veebilehe autentimine

Kui on veebirakendusel mõnele aspx lehele peale seatud Client Certificates - Require, ehk
lehele ligipääsuks tuleb ID-kaardiga autentida. Kui hakkab pilduma viga 403-Forbidden: Access is denied

Siis asja töölesaamiseks kontrolli ka, et IIS-i URL Authorization role komponent oleks installitud

IIS8 tõstutundlikkus ehk Ambiguous match found

IIS8 peal selline asi enam läbi ei lähe

Veebirakendus on .NET. 4.0 tehtud ja veebilehel on kontroll ID-ga "LTA"

<asp:Literal ID="LTA" runat="server" Text="0.0"></asp:Literal>

ja code-behindis on defineeritud muutuja

private decimal lta = 0.0M;
this.LTA.Text=String.Format("{0:F2}", this.lta);

ehk on nii lta kui LTA mille peale projekt VisualStudio2010 õnnelikult kompileerub, kuid kävitamisel veebilehe avamisel saab vea Ambiguous match found.

Lahenduseks see, et ühe muutuja nime tuleb muuta

<asp:Literal ID="LiTA" runat="server" Text="0.0"></asp:Literal>

 this.LiTA.Text=String.Format("{0:F2}", this.lta);