Nyein Family: IT

Showing posts with label IT. Show all posts

SQL : Change all varchar columns to nvarchar datatype

တစ္ခါတစ္ေလ အလုပ္ထဲမွာ ကိုယ့္ရဲ႕ Database ထဲက Table အကုန္လံုး (သုိ႕မဟုတ္) တစ္ခုရဲ႕ Data Type ကုိ varchar ကေန nvarchar ကုိ ေျပာင္းခ်င္တဲ့ အခါ ဒီ script ေလးက အသံုး၀င္လွပါတယ္။

database ထဲက table ေတြအကုန္ကုိ ေျပာင္းမယ္ဆုိရင္ေတာ့ table_name filter ေလးကုိ ျဖဳတ္ေပးလုိက္ရံုပါပဲ။

ကိုယ့္အတြက္လည္း တစ္ခါတစ္ရံ လုိတယ္ဆုိေတာ့ ဒီထဲမွာပဲ ရွာရေဖြရလြယ္ေအာင္ လာမွတ္ထားပါရေစ။

SELECT 'ALTER Table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']
ALTER Column [' + COLUMN_NAME + '] nvarchar(' + CAST(CHARACTER_MAXIMUM_LENGTH As VARCHAR) + ')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'VARCHAR'

Rowspan in GridView ( VB.Net )

Private Sub GridView1_PreRender(sender As Object, e As EventArgs) Handles GridView1.PreRender
MergeRowsWithSameContent(sender)
End Sub

Public Sub MergeRowsWithSameContent(gvw As GridView)
For rowIndex As Integer = gvw.Rows.Count - 2 To 0 Step -1
Dim row As GridViewRow = gvw.Rows(rowIndex)
Dim previousRow As GridViewRow = gvw.Rows(rowIndex + 1)

For i As Integer = 0 To row.Cells.Count - 1
If row.Cells(i).Text = previousRow.Cells(i).Text Then
row.Cells(i).RowSpan = If(previousRow.Cells(i).RowSpan < 2, 2, previousRow.Cells(i).RowSpan + 1)
previousRow.Cells(i).Visible = False`
End If
Next
Next
End Sub

(Ref: http://stackoverflow.com/questions/11129762/how-to-implement-rowspan-in-a-gridview-for-net-3-5)

SQL : How to generate XML in MSSQL ( table => xml )

I. Table Design

II. Case I
(a) XML format which has sale voucher header and detail information

<Sale>

<Head>

<CashierNo>AFRIA</CashierNo>

<CustomerNo>SAFRA</CustomerNo>

<Remarks>SAFRA MEMBER DISCOUNT</Remarks>

</Head>

<Body>

<Item>

<ItemNo>PTISSUE</ItemNo>

<ItemName>PENTA TISSUE BOX</ItemName>

</Item>

<Item>

<ItemName>UMBERELLA</ItemName>

</Item>

<Item>

<ItemNo>COLMW</ItemNo>

<ItemName>COLGATE MOUTH WASH</ItemName>

</Item>

</Body>

</Sale>

(b) SQL query to generate above xml format

declare @voucherno as varchar(20)

set @voucherno = 'SG0234234'

SELECT V.VOUCHER_NO as [Head/VoucherNo]

,V.SALE_DATE as [Head/SaleDate]

,V.COUNTER_NO as [Head/CounterNo]

,V.CASHIER_NO as [Head/CashierNo]

,V.CUSTOMER_NO as [Head/CustomerNo]

,V.REMARKS as [Head/Remarks]

SELECT I.ITEM_NO as [Item/ItemNo]

,I.ITEM_NAME as [Item/ItemName]

,cast(I.ITEM_PRICE as decimal(10,2)) as [Item/ItemPrice]

,I.ITEM_QTY as [Item/ItemQty]

,I.ITEM_REMARK as [Item/Remark]

FROM SALE_VOUCHER_ITEM I

WHERE I.VOUCHER_NO = @voucherno

FOR XML PATH(''),Root('Body'), TYPE

)

FROM SALE_VOUCHER V

WHERE V.VOUCHER_NO = @voucherno

FOR XML PATH(''), ROOT('Sale')

II. Case II

(a) XML format which displays sale voucher header and detail information, some header info is displayed at detail level

<Sale>

<Head>

<CashierNo>AFRIA</CashierNo>

</Head>

<Body>

<CustomerNo>SAFRA</CustomerNo>

<Remarks>SAFRA MEMBER DISCOUNT</Remarks>

<Item>

<ItemNo>PTISSUE</ItemNo>

<ItemName>PENTA TISSUE BOX</ItemName>

</Item>

<Item>

<ItemName>UMBERELLA</ItemName>

</Item>

<Item>

<ItemNo>COLMW</ItemNo>

<ItemName>COLGATE MOUTH WASH</ItemName>

</Item>

</Body>

</Sale>

(b) SQL query to generate above xml format

declare @voucherno as varchar(20)

set @voucherno = 'SG0234234'

SELECT V.VOUCHER_NO as [Head/VoucherNo]

,V.SALE_DATE as [Head/SaleDate]

,V.COUNTER_NO as [Head/CounterNo]

,V.CASHIER_NO as [Head/CashierNo]

,( SELECT V.CUSTOMER_NO as [CustomerNo]

,V.REMARKS as [Remarks]

SELECT I.ITEM_NO as [Item/ItemNo]

,I.ITEM_NAME as [Item/ItemName]

,cast(I.ITEM_PRICE as decimal(10,2))

as [Item/ItemPrice]

,I.ITEM_QTY as [Item/ItemQty]

,I.ITEM_REMARK as [Item/Remark]

FROM SALE_VOUCHER_ITEM I

WHERE I.VOUCHER_NO = @voucherno

FOR XML PATH(''), TYPE

)

FROM SALE_VOUCHER V

WHERE V.VOUCHER_NO = @voucherno

FOR XML PATH('Body'),TYPE

)

FROM SALE_VOUCHER V

WHERE V.VOUCHER_NO = @voucherno

FOR XML PATH(''), ROOT('Sale')

Nyein Family

Categories

SQL : Change all varchar columns to nvarchar datatype

Rowspan in GridView ( VB.Net )

SQL : How to generate XML in MSSQL ( table => xml )

Penang 4D3N Free & Easy - Intro