SQL2005 XML TIMESTAMP byte[] konvertimine C#

Kui teed andmebaasi tabelist for XML TYPE päringu ja seal sees on ka timestamp väli siis tulemus on näiteks selline

<CvNodeElement CfPers_CVId="3" Title="Haridustee" CfLangCode="et-EE" CfClassId="1" CfClassSchemeId="49">
<Node ExpSkillsId="23" IsPublic="0" Timestamp="AAAAAAATo9I=" Pers_CV_CfPers_ExpSkillsId="16" Text="2005 TTÜ" />
<Node ExpSkillsId="21" IsPublic="0" Timestamp="AAAAAAATo9o=" Pers_CV_CfPers_ExpSkillsId="15" Text="1994 - 2005 Saku Gümnaasium" />
</CvNodeElement>

Timestamp on näiteks AAAAAAATo9I=

Kui tahad C# seda string tagasi TIMESTAMP-iks keerata siis käib see nii

byte[] bt = new byte[8];
bt=Convert.FromBase64String("AAAAAAATo9I=");

Blogged with the Flock Browser

DevExpress ASPxGrid CustomButtonCallback töölesaamine

Kui selline vidin kasutusel ja vaja saada GridViewCommandColumnCustomButton tööle, siis tuleb
Gridil endal seada EnableCallBacks="false"

Näide selline, et seal nüüd kaks nuppu rea peal


<dxwgv:ASPxGridView ID="ASPxGridViewElements" runat="server" KeyFieldName="CfPers_ExpSkillsId"
                                Settings-ShowColumnHeaders="false" AutoGenerateColumns="false" SettingsText-ConfirmDelete="Kas soovid kustutada ?"
                                onhtmldatacellprepared="ASPxGridViewElements_HtmlDataCellPrepared" SettingsBehavior-ConfirmDelete="true"
                                oncustombuttoncallback="ASPxGridViewElements_CustomButtonCallback"
                                onrowdeleting="ASPxGridViewElements_RowDeleting" EnableCallBacks="false"  >
                            <Columns>
                            <dxwgv:GridViewDataColumn VisibleIndex="0" FieldName="CfPers_ExpSkillsId">
                                <DataItemTemplate>
                                    <dxe:ASPxLabel runat="server" ID="ASPxLabelPeriod" Text=""></dxe:ASPxLabel>
                                </DataItemTemplate>
                            </dxwgv:GridViewDataColumn>
                            <dxwgv:GridViewDataTextColumn FieldName="CfNameEST" VisibleIndex="1">
                            </dxwgv:GridViewDataTextColumn>
                            <dxwgv:GridViewDataTextColumn FieldName="CfNameALT" VisibleIndex="2">
                            </dxwgv:GridViewDataTextColumn>
                            <dxwgv:GridViewCommandColumn ButtonType="Image" DeleteButton-Visible="true" Name="CommandColumn" VisibleIndex="3">
                            <CustomButtons>
                                <dxwgv:GridViewCommandColumnCustomButton Text="Muuda" Image-AlternateText="Muuda" ID="ButtonMuuda" Image-Url="../App_Themes/ETIS/gfx/icons/icon_edit.gif">
                                </dxwgv:GridViewCommandColumnCustomButton>
                            </CustomButtons>
                            <DeleteButton Image-Url="../App_Themes/ETIS/gfx/icons/icon_delete.gif" Image-AlternateText="Kustuta" Text="Kustuta">
                            </DeleteButton>
                            </dxwgv:GridViewCommandColumn>
                            <dxwgv:GridViewDataTextColumn FieldName="StartYear" VisibleIndex="4" Visible="false">
                            </dxwgv:GridViewDataTextColumn>
                            <dxwgv:GridViewDataTextColumn FieldName="EndYear" VisibleIndex="5" Visible="false">
                            </dxwgv:GridViewDataTextColumn>
                            <dxwgv:GridViewDataTextColumn FieldName="TimestampEST" VisibleIndex="6" Visible="false">
                            </dxwgv:GridViewDataTextColumn>
                            <dxwgv:GridViewDataTextColumn FieldName="TimestampALT" VisibleIndex="7" Visible="false">
                            </dxwgv:GridViewDataTextColumn>
                            <dxwgv:GridViewDataTextColumn FieldName="ExpSkillsIdEST" VisibleIndex="8" Visible="false">
                            </dxwgv:GridViewDataTextColumn>
                            <dxwgv:GridViewDataTextColumn FieldName="ExpSkillsIdALT" VisibleIndex="9" Visible="false">
                            </dxwgv:GridViewDataTextColumn>
                           
                            </Columns>
</dxwgv:ASPxGridView>

"Kustuta" - selle nupu jaoks et tuleks ennem hoiatusaken tuleb SettingsBehavior-ConfirmDelete="true" ja et ilus tekst tuleks siis SettingsText-ConfirmDelete="Kas soovid kustutada ?"
ja kustutamine ise tehakse

        protected void ASPxGridViewElements_RowDeleting(object sender, DevExpress.Web.Data.ASPxDataDeletingEventArgs e)
        {
            this.DeleteCVElement(Convert.ToInt32(e.Values["CfPers_ExpSkillsId"].ToString()));
            //see Cancel osa on ASPx DataGridi nõutud värk
            e.Cancel = true;
            this.ASPxGridViewElements.CancelEdit();
            this.FillDataGrid();
        }

meetodis, kus kõigepealt kustutab enda tehtud meetodiga, siis keelab Gridi enda kustutamise meetodi ära
            e.Cancel = true;
            this.ASPxGridViewElements.CancelEdit();

ja täidab Gridi andmetega uuesti
this.FillDataGrid(); // see siin on omalooming

"Muuda" nupp, siin see ennam korralikult tööle ei hakka, kui Gridil endal EnableCallBacks="false" , muidu klikid j aklikid aga ekraanil midagi ei juhtu


        protected void ASPxGridViewElements_CustomButtonCallback(object sender, ASPxGridViewCustomButtonCallbackEventArgs e)
        {
            if (e.ButtonID == "ButtonMuuda")
            {
                try
                {
                    ASPxGridView grid = sender as ASPxGridView;
                    string[] copiedFields = new string[] { "CfNameEST", "CfNameALT", "CfPers_ExpSkillsId", "StartYear", "EndYear", "TimestampEST", "TimestampALT", "ExpSkillsIdEST", "ExpSkillsIdALT" };
                    System.Collections.Hashtable copiedValues = new System.Collections.Hashtable();
                    foreach (string fieldName in copiedFields)
                    {
                        copiedValues[fieldName] = grid.GetRowValues(e.VisibleIndex, fieldName);
                    }
                    this.ASPxButtonLisa.Visible = false;
                    this.ASPxLabelTekstLisa.Text = "Muuda " + this.ASPxLabelTextName.Text.ToLower();
                    this.PanelElementLisa.Visible = true;

                    if (this.TekstMemo)
                    {
                        this.ASPxMemoEST.Text = copiedValues["CfNameEST"].ToString();
                        this.ASPxMemoALT.Text = copiedValues["CfNameALT"].ToString();
                    }
                    else
                    {
                        this.ASPxTextBoxEST.Text = copiedValues["CfNameEST"].ToString();
                        this.ASPxTextBoxALT.Text = copiedValues["CfNameALT"].ToString();
                    }
                    if (this.alguspaevaridanahtav)
                    {
                        this.ASPxTextBoxAlgus.Text = copiedValues["StartYear"].ToString();
                    }
                    if (this.lopppaevaridanahtav)
                    {
                        this.ASPxTextBoxLopp.Text = copiedValues["EndYear"].ToString();
                    }
                }
                catch (SystemException ex)
                {
                    this.CustomValidator1.ErrorMessage = ex.Message;
                    this.CustomValidator1.IsValid = false;
                }
            }
        }




 

Blogged with the Flock Browser

Kuupäeva pp.kk.aaaa formaadis kontroll

Et vaja kohe kindlasti kontrollida, et kuupäeva sisestus oleks pp.kk.aaaa formaadis
siis näiteks selline meetod mis kasutab DateTime.ParseExact meetodit
using System.Globalization;

public bool ValidateDateTimeFormat(string date, string format)
{
CultureInfo provider = CultureInfo.InvariantCulture;
try
{
DateTime.ParseExact(date, format, provider);
return true;
}
catch
{
return false;
}
}

ja vastava textboxi CustomValidatori ServerValidate meetodi teed niimoodi

protected void CustomValidatorSynniaeg_ServerValidate(object source, ServerValidateEventArgs args)
{
args.IsValid = ValidateDateTimeFormat(this.TextBoxSynniaeg.Text.Trim(),"dd.MM.yyyy");
}

ning rahu majas

Kui üldse vaja pp.kk.aaaa tekstivälja kuupäevaks pöörata siis
CultureInfo provider = CultureInfo.InvariantCulture;
System.DateTime paeve = DateTime.ParseExact(this.TextBoxPaev.Text.Trim(), "dd.MM.yyyy", provider);

Blogged with the Flock Browser

XML väljaga info edasiandmine trigerisse

Selline juhtum, et triger on pandud andmebaasi muutusi logima ja nüüd situatsioon, et alustabelis kõiki andmeid ei ole aga hädasti oleks vaja üht-teist trigerile lisaks teada anda. Üks lahendus selline, et teeb baastabelisse juurde XML tüüpi välja näiteks APPLICATIONS tabelile APPOPTIONS

ja kui nüüd SP-ga tabelis midagi muuta

--kirjutame APPOPTIONS xml tüüp välja sisse lepingu numbri väärtuse

DECLARE @aop XML, @lep INT, @relcon NVARCHAR(2000)
--siin on üks string muutuja, mida edaspidi kasutame, et saaks sql:variable kasutada ja siia paneme meid huvitava info kirja
SET @relcon='<RELATED_CONTRACT>'+CAST(@contractId AS NVARCHAR(20))+'</RELATED_CONTRACT>'
--loeme enne kirjutamist APPOPTIONS välja väärtuse (siin oleks muidugi ilus CURSORIT kasutada)
SET @aop=( SELECT APPLICATIONS.[appoptions] FROM [dbo].[APPLICATIONS] WHERE APPLICATIONS.[ID]= @application_id)

IF @aop IS NULL --kui XML-i ei ole midagi kirjutatud
SET @aop=CAST(@relcon AS XML) --pöörame muutuja XML-iks
ELSE
BEGIN
--kontrollime, kas RELATED_CONTRACT on juba olemas
SET @lep = @aop.value('/RELATED_CONTRACT[1]', 'INT') --LEPINGU NUMBER ON int andmetüüp, mis meid huvitav
IF @lep IS NULL --KUI RELATED_CONTRACT on puudu, lisame lõppu vastava NODE
SET @aop.modify('insert text{sql:variable("@relcon")} as last into (/)[1]')
END

ja nüüd UPDATE-me tabeli välja
UPDATE [dbo].[APPLICATIONS]
SET [appoptions]=ISNULL(@aop,[appoptions]) WHERE ID=@application_id

ja trigeris saame asja teada nii

SELECT @aop=INSERTED.appoptions FROM INSERTED
DECLARE @lep INT
SET @lep = @aop.value('/RELATED_CONTRACT[1]', 'INT')

ja käes ta nüüd ongi ning salvestatud @lep muutujasse see mille ennist @contractId sisse andsime

Blogged with the Flock Browser