SERVICE BROKERI-s protseduuri käivitamine

Kui paned SERVICE BROKERI tööle ja ei saa aru, mis ta tööle ei lähe siis üle ka kontrolida, kuids sul asi on ülesse ehitatud.
QUEUE PROCEDURE_NAME on asi mis tuleb selgeks teha
Kui vaja, et saadaks sõnumi "saatmise" peale tuleb teha üks QUEUE ja panna serviced sama QUEUEd kasutama

Kui saadad sama serveri piires, siis WITH ENCRYPTION = OFF, muidu ei pruugi töötada

CREATE MESSAGE TYPE [KAMPUS_INVOICE] VALIDATION = WELL_FORMED_XML

CREATE CONTRACT [KAMPUS_SEND_INVOICE_EMAIL]
([KAMPUS_INVOICE] SENT BY INITIATOR)


CREATE QUEUE KAMPUS_INVOICES
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = KAMPUS_ARVE_EPOST3,
MAX_QUEUE_READERS = 2,
EXECUTE AS 'DBO' )

CREATE SERVICE KAMPUS_SEND3
ON QUEUE KAMPUS_INVOICES ([KAMPUS_SEND_INVOICE_EMAIL])

CREATE SERVICE KAMPUS_RECEIVE3
ON QUEUE KAMPUS_INVOICES ([KAMPUS_SEND_INVOICE_EMAIL])

ja kui nüüd saata sõnum
DECLARE @StockDialog uniqueidentifier
DECLARE @Message XML
BEGIN DIALOG CONVERSATION @StockDialog
FROM SERVICE KAMPUS_SEND3
TO SERVICE 'KAMPUS_RECEIVE3'
ON CONTRACT KAMPUS_SEND_INVOICE_EMAIL
WITH ENCRYPTION = OFF
SET @Message = CAST('<mess>Add 12 widgets to inventory</mess>' AS XML);
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE KAMPUS_INVOICE (@Message)
SET @Message = CAST('<mess>Remove 4 springs from inventory</mess>' AS XML);
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE KAMPUS_INVOICE (@Message)

END CONVERSATION @StockDialog

siis tõmmatakse protseduur KAMPUS_ARVE_EPOST3 käima

ALTER PROCEDURE [dbo].[KAMPUS_ARVE_EPOST3]
AS
BEGIN
SET NOCOUNT ON
DECLARE @dialog_handle UNIQUEIDENTIFIER, @sonu NVARCHAR(2300), @mb VARBINARY(MAX), @jrk INT, @jrkmax INT, @sub NVARCHAR(400)
DECLARE @tulem TABLE ( jrk INT IDENTITY(1,1) PRIMARY KEY, [STATUS] TINYINT, [SERVICE_NAME] NVARCHAR(512), [conversation_handle] UNIQUEIDENTIFIER, message_body
VARBINARY(MAX) )
;
WAITFOR
(
RECEIVE [status], [SERVICE_NAME], [conversation_handle],message_body
FROM KAMPUS_INVOICES
INTO @tulem
), TIMEOUT 8000
SET @jrk = 1
SET @dialog_handle = (SELECT TOP 1 [conversation_handle] FROM @tulem)
SET @jrkmax=(SELECT MAX(jrk) FROM @tulem)
WHILE @jrk <= @jrkmax
BEGIN
SELECT @sonu = CAST(message_body AS NVARCHAR(2000)) FROM @tulem WHERE jrk = @jrk
IF @sonu IS NOT NULL
BEGIN

SET @sub='KAMPUS_INVOICES_SEND3 '+CAST(CURRENT_TIMESTAMP AS NVARCHAR(25))
EXECUTE [dbo].[SEND_MAIL_KUIDO_I]
@mail_post_from='saatja@email.com'
,@mail_to='saaja@email.com'
,@message_subject=@sub
,@sonum=@sonu
END
SET @jrk=@jrk+1

END

IF @dialog_handle IS NOT NULL
END CONVERSATION @dialog_handle

END
END


Kui on vaja panna "lugemise" peale tööle, ehk tehakse kaks QUEUE
CREATE MESSAGE TYPE [KAMPUS_INVOICE] VALIDATION = WELL_FORMED_XML

CREATE CONTRACT [KAMPUS_SEND_INVOICE_EMAIL]
([KAMPUS_INVOICE] SENT BY INITIATOR)

--TEEME KAKS QUEUED

CREATE QUEUE KAMPUS_INVOICES_SEND

CREATE QUEUE KAMPUS_INVOICES_READ
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = KAMPUS_ARVE_EPOST,
MAX_QUEUE_READERS = 2,
EXECUTE AS 'DBO' )

--ja SERVICED on erinevate QUEUE-de peal
CREATE SERVICE KAMPUS_SEND
ON QUEUE KAMPUS_INVOICES_SEND ([KAMPUS_SEND_INVOICE_EMAIL])

CREATE SERVICE KAMPUS_RECEIVE
ON QUEUE KAMPUS_INVOICES_READ ([KAMPUS_SEND_INVOICE_EMAIL])

--kui nüüd saata
DECLARE @StockDialog uniqueidentifier
DECLARE @Message XML
BEGIN DIALOG CONVERSATION @StockDialog
FROM SERVICE KAMPUS_SEND
TO SERVICE 'KAMPUS_RECEIVE'
ON CONTRACT KAMPUS_SEND_INVOICE_EMAIL
WITH ENCRYPTION = OFF
SET @Message = CAST('<mess>Add 12 widgets to inventory</mess>' AS XML);
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE KAMPUS_INVOICE (@Message)
SET @Message = CAST('<mess>Remove 4 springs from inventory</mess>' AS XML);
SEND ON CONVERSATION @StockDialog
MESSAGE TYPE KAMPUS_INVOICE (@Message)

END CONVERSATION @StockDialog

--tõmmatakse protseduur käima KAMPUS_ARVE_EPOST mis loeb ainult KAMPUS_INVOICES_READ QUEUE-st

ALTER PROCEDURE [dbo].[KAMPUS_ARVE_EPOST]
AS
BEGIN
SET NOCOUNT ON
DECLARE @dialog_handle UNIQUEIDENTIFIER, @sonu NVARCHAR(2300), @mb VARBINARY(MAX), @jrk INT, @jrkmax INT, @sub NVARCHAR(50)
DECLARE @tulem TABLE ( jrk INT IDENTITY(1,1) PRIMARY KEY, [STATUS] TINYINT, [SERVICE_NAME] NVARCHAR(512), [conversation_handle] UNIQUEIDENTIFIER, message_body
VARBINARY(MAX) )
;
WAITFOR
(
RECEIVE [status], [SERVICE_NAME], [conversation_handle],message_body
FROM [KAMPUS_INVOICES_READ]
INTO @tulem
), TIMEOUT 8000

SET @jrk = 1
SET @dialog_handle = (SELECT TOP 1 [conversation_handle] FROM @tulem)
SET @jrkmax=(SELECT MAX(jrk) FROM @tulem)
WHILE @jrk <= @jrkmax
BEGIN
SELECT @sonu = CAST(message_body AS NVARCHAR(2000)) FROM @tulem WHERE jrk = @jrk
IF @sonu IS NOT NULL
BEGIN
SET @sub='KAMPUS_INVOICES_READ '+CAST(CURRENT_TIMESTAMP AS NVARCHAR(25))
EXECUTE [dbo].[SEND_MAIL_KUIDO_I]
@mail_post_from='saatja@email.com'
,@mail_to='saaja@email.com'
,@message_subject=@sub
,@sonum=@sonu
END
SET @jrk=@jrk+1

END

IF @dialog_handle IS NOT NULL
END CONVERSATION @dialog_handle

END


salakaval ; peab olema õige koha peal

Blogged with the Flock Browser

GridView footeri dünaamiline määramine

// kui paned olemasolevale gridview-le tulpasid juurde
for (int i = 0; i < majasi.Length; i++)
{
//see miskipärast ei tööta
this.GridViewKulud.Columns[i+2].FooterText = String.Format("{0:F2}",Convert.ToDecimal(tulpsummad.FindByText(majasi[i]).Value));
//see töötab
this.GridViewKulud.FooterRow.Cells[i + 2].Text = String.Format("{0:F2}", Convert.ToDecimal(tulpsummad.FindByText(majasi[i]).Value));
}

//olemasolevatele tulpadele töötab aga just vastupidi

SqlDataSource Timeouti muutmine

Selecting event kinni püüda ja siis muuta


this.SqlDataSourceKohataotlused.Selecting += new SqlDataSourceSelectingEventHandler(SqlDataSourceKohataotlused_Selecting);

void SqlDataSourceKohataotlused_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.CommandTimeout = 600;
}

Blogged with the Flock Browser

asp:FileUpload faili pööramine Image-ks ja bitijadaks

Vaja laadida pilt ülesse ja panna andmebaasi aga enne andmebaasi pistmist nõutud pildi mudimine

using System.Drawing;
using System.IO;


//teeme FileUpload failist Image
byte[] image = new byte[0];
if (this.FileUploadCVPicture.HasFile) //midagi on ülesse laetud
{
    if (FileUploadCVPicture.PostedFile.ContentType.Contains("image/")) //on tegu pildiga
    {
          //loeme striimist asja pildiks
          Stream iplit = FileUploadCVPicture.PostedFile.InputStream;
          System.Drawing.Image pilt = Image.FromStream(iplit); //pilt olemas
    }
}

//Kui pilt vaja bitijadaks pöörata
using (MemoryStream ms = new MemoryStream())
{
    pilt.Save(ms, System.Drawing.Imaging.ImageFormat.Png);  // salvestame PNG tüüpi antud juhul
    image = ms.ToArray();  //bitijada olemas
}

Blogged with the Flock Browser

SQL UPDATE Table-Valued muutujaga

@tvpEditedCategories on siin SQL2008 table-valued muutuja, sama süntaks ka table-valued funktsiooniga

UPDATE dbo.Categories
SET Categories.CategoryName = ec.CategoryName
FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
ON dbo.Categories.CategoryID = ec.CategoryID;

Pikem tekst siin:
http://msdn.microsoft.com/en-us/library/bb675163.aspx

Kui vaja kahe tabelmuutuja vahel UPDATE teha siis see käib sellise süntaksiga

UPDATE @korterid
SET [@korterid].january_max=[@korterid].january_max+koru.january_max
FROM @korterid INNER JOIN @kort AS koru ON ([@korterid].flat_id = koru.flat_id)


INNER JOIN @kort AS koru või ka kirjutada ilma AS kasutamata INNER JOIN @kort koru

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