SQLSERVER: Format Number with comma

-- ตัวอย่าง
SELECT CONVERT(varchar,CONVERT(money,1234567.89),1)

-- ทดสอบ SIMPLE SELECT
SELECT CONVERT(varchar,CONVERT(money,SUM(bcbill)),1)
FROM ARDebit
WHERE year(billdate) = 2011 and month(billdate) = 10

-- ทดสอบการลบกันระหว่าง 2 query
SELECT CONVERT(varchar,CONVERT(money,
 (
 (SELECT SUM(bcBill) FROM ARDebit WHERE Year(BillDate)=2011 and Month(BillDate) = 10)
 -
 (SELECT SUM(BcPayment) FROM ARCredit WHERE Year(ReceiptDate) = 2011 and MONTH(ReceiptDate) = 10)
 )
),1)

เมื่อก่อนตอนเจอการตำนวณหลัก 10 ล้าน 100 ล้าน ต้องมานั่งเพ่งดูตัวเลขว่าเท่าไหร่กันแน่ หรือไม่งั้นก็ copy ไป excel พอตอนนี้สามารถสั่ง format ได้ พอมี comma กับจุดทศนิยมก็ง่ายขึ้นเยอะครับ

แต่ว่าพอ convert เป็น money แล้วทศนิยมมันปัดเป็น 2 ตำแหน่ง อันนี้ต้องระวังด้วยครับ

-- ทดสอบ ได้ผลลัพธ์ 1,234,567.90
SELECT CONVERT(varchar,CONVERT(money,1234567.8987),1)

Filter DataTable

สมมติว่าเรามี DataTable อยู่ 1 ตัว ทีนี้เราต้องการนับจำนวนรายการที่ตรงตามเงื่อนไขที่ต้องการ (หรือกรองเอาเฉพาะรายการใน DataTable ตามเงื่อนไข) เท่าที่นึกออกตอนนี้ทำได้ 3 วิธีครับ ลองดูต้วอย่างกันเลย

 
    Private Sub TestCountDataTable()

        Dim dtEmployee As New Data.DataTable
        dtEmployee.Columns.Add("EmployeeID", GetType(String))
        dtEmployee.Columns.Add("HireDate", GetType(Date))

        Dim strCommand As String = "SELECT EmployeeID, HireDate FROM tblEmployee WHERE HireDate IS NOT NULL"
        Dim strConnection As String = "Data Source=tg2000;Initial Catalog=ADMIN;Integrated Security=True"

        Using sqlConnection As New SqlClient.SqlConnection(strConnection)
            sqlConnection.Open()
            Using sqlCommand As New SqlClient.SqlCommand(strCommand, sqlConnection)
                Using sqlDataReader = sqlCommand.ExecuteReader()
                    dtEmployee.Load(sqlDataReader)
                End Using
            End Using

        End Using

        ' USE DataView.RowFilter
        Dim dv = dtEmployee.DefaultView
        dv.RowFilter = "HireDate>='2010-01-01' and HireDate<='2010-12-31'"
        Dim countFromDataView = dv.Count
        Debug.Print(String.Format("Count from DataView:{0:#,##0.00}", countFromDataView))

        ' USE LINQ TO DATASET
        Dim countFromLinqToDataSet = dtEmployee.AsEnumerable.Where(Function(w) w.Field(Of Date)("HireDate").Year.Equals(2010)).Count
        Debug.Print(String.Format("Count from Linq To DataSet:{0:#,##0.00}", countFromLinqToDataSet))

        ' USE Datatable.Select
        Dim dr() = dtEmployee.Select("HireDate>='2010-01-01' and HireDate<='2010-12-31'")
        Dim countFromSelect = dr.Length
        Debug.Print(String.Format("Count from Dataset.Select:{0:#,##0.00}", countFromSelect))

    End Sub

ถ้าใครนึกวิธีอื่นออกที่น่าสนใจ ก็ช่วยเพิ่มให้ด้วยครับ

ASP.NET Gridview export UNICODE to Excel (แก้ปัญหา export ภาษาไทยแล้วอ่านไม่ออก)

ผมเจอปัญหาว่าพอสั่ง Export Gridview ไปเป็น Excel แล้วภาษาไทยมันอ่านไม่ออกครับ
ลองแก้ Response.Charset แล้วก็ยังไม่ได้ครับ ค้นใน google ก็เลยเจอว่าน่าจะเป็นเพราะคำสั่งที่ export นั้นไม่สนับสนุน UTF8 ผมใช้ StringWriter ครับ พอลองมาค้นดูก็เจอว่า StreamWriter มันรองรับการทำ Encoding ได้ ก็เลยปิ๊งครับ

           
gvContact.DataSource = mlSummaries
gvContact.DataBind()

Response.Clear()

Response.AddHeader("content-disposition", "attachment;filename=EventMailingList.xls")
Response.Charset = "tis-620"
Response.ContentType = "application/vnd.ms-excel"

If Request("EventName") IsNot Nothing Then lblCaption.Text = Server.UrlDecode(Request("EventName"))

' โค้ดเดิมที่ใช้ไม่ได้
'Dim stringWrite As New System.IO.StringWriter
'Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)

'divExport.RenderControl(htmlWrite)
'Response.Write(stringWrite.ToString())

Dim ms As New System.IO.MemoryStream()
Dim streamWrite As New System.IO.StreamWriter(ms, Encoding.UTF8)
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(streamWrite)
divExport.RenderControl(htmlWrite)

Dim strEncodedHTML As String = Encoding.UTF8.GetString(ms.ToArray)
Response.Write(strEncodedHTML)
Response.End()

ทดลองดูใช้ได้ครับ ออกมาเป็นภาษาไทยถูกต้องแล้ว

ASP.NET Dynamic Gridview

ผมได้รับ requirement จากยูสเซ่อว่าหน้าสอบถามข้อมูล (Inquiry) อยากให้สามารถเลือกว่าอยากเห็นข้อมูลคอลัมภ์ไหนบ้าง พร้อมทั้งสามารถ export ข้อมูลมาเป็น Excel ได้ (อนาคตจะต้องให้ user สามารถ save format ที่เลือกเก็บไว้ได้ด้วย แต่ผมยังไม่ได้ทำ)

เนื่องจากหน้าเดิมผมใช้ Gridview ในการแสดงผล ดังนั้นผมเลยเขียนโปรแกรมเพื่อให้ user สามารถเพิ่ม/ลด column ของ Gridview ได้

ก่อนอื่นเรามาเตรียมโค้ดของ Gridview ก่อนครับ ในเวอร์ชันนี้เราจะให้ Dynamic Gridview รองรับแค่ Label เท่านั้น ผมเริ่มจากการสร้าง GridViewLabelTemplate Class สำหรับ Class ตัวนี้มีไว้สำหรับสร้าง ItemTemplate ซึ่งประกอบด้วย Label 1 อัน

  
Public Class GridViewLabelTemplate
    Implements ITemplate

    Private _lblID As String

    Public Sub New(ByVal ID As String)
        _lblID = ID
    End Sub

    Public Sub InstantiateIn(ByVal container As System.Web.UI.Control) _
        Implements System.Web.UI.ITemplate.InstantiateIn

        Dim lbl As New Label
        lbl.ID = _lblID
        container.Controls.Add(lbl)
        AddHandler lbl.DataBinding, AddressOf Me.DataBinding
    End Sub

    Private Sub DataBinding(ByVal sender As Object, ByVal e As EventArgs)
       ' Do nothing but needed for row databinding event 
    End Sub

End Class

จากนั้นก็มาสร้าง Static Class อีกตัว เพื่อเป็น Gridview Utility สำหรับเพิ่ม Column (ItemTemplate)

  
Public Class GridViewUtility
    Public Shared Sub AddLabelTemplate(ByVal grid As GridView, _
                                                           ByVal labelID As String, _
                                                           ByVal HeaderText As String, _
                                                           ByVal columnWidth As Integer, _
                                                           Optional ByVal fontWeight As Integer = 11)

        Dim f As New TemplateField
        f.HeaderText = HeaderText
        f.HeaderStyle.Width = Unit.Pixel(columnWidth)
        f.HeaderStyle.HorizontalAlign = HorizontalAlign.Left
        f.HeaderStyle.Font.Name = "Tahoma"
        f.ItemStyle.HorizontalAlign = HorizontalAlign.Left
        f.ItemStyle.Font.Size = Unit.Pixel(fontWeight).Value
        f.ItemStyle.Font.Name = "Tahoma"

        f.ItemTemplate = New GridViewLabelTemplate(labelID)
        grid.Columns.Add(f)

    End Sub
End Class

ขั้นเตรียมตัวเรียบร้อย ก็มาเริ่มเขียน aspx กันครับ ส่วนหน้าจอที่ให้ user เลือก



ไม่ได้เขียนโค้ดให้ แค่คร่าวๆคือ เมื่อ user กดปุ่ม Advance จะแสดง div ที่ซ่อนไว้ ว่ามีคอลัมภ์อะไรให้เลือกบ้าง โดยจะมี Default อยู่ 8 คอลัมภ์ และ user สามารถเลือกได้ว่าอยากเห็นข้อมูลที่ห้น้าจอหรือ export เป็น excel หรือ pdf พอ user กดปุ่ม Export โปรแกรมก็จะไปสร้าง Gridview ให้ แล้วเอา Gridview ไปแสดงผลตามที่ต้องการ

คราวนี้มาดูโค้ดส่วนที่สร้าง Gridview บ้างครับ

  
        Dim lst As List(Of CMS.Library.ContactList) = Session(MAILING_LIST_SESSION)
        'Add empty row
        lst.Add(New CMS.Library.ContactList)

        Dim divReport As New Panel

        Dim pHeader As New Panel
        Dim lblHeader As New Label
        lblHeader.Text = "List of mailing list by lawyer."
        lblHeader.Font.Bold = True
        lblHeader.Font.Size = Unit.Pixel(16).Value
        pHeader.Controls.Add(lblHeader)
        divReport.Controls.Add(pHeader)

        Dim pCaption As New Panel
        Dim lblCaption As New Label
        lblCaption.Text = "Lawyer: " & Me.lblLawyerName.Text
        lblCaption.Font.Size = Unit.Pixel(14).Value
        lblCaption.Font.Italic = True
        pCaption.Controls.Add(lblCaption)
        divReport.Controls.Add(pCaption)

        Dim pLinespace As New Panel
        Dim lblSpace As New Label
        lblSpace.Text = "
"
        pLinespace.Controls.Add(lblSpace)
        divReport.Controls.Add(pLinespace)

        Dim gvReport As New GridView
        divReport.Controls.Add(gvReport)

        gvReport.AutoGenerateColumns = False
        gvReport.RowStyle.VerticalAlign = VerticalAlign.Top
        gvReport.BorderStyle = BorderStyle.Solid
        gvReport.BorderWidth = Unit.Pixel(1)
        gvReport.GridLines = GridLines.Both
        gvReport.HeaderStyle.BackColor = Drawing.Color.Silver


        AddHandler gvReport.RowDataBound, AddressOf GridView_RowDataBound


        If chkPrefix.Checked Then GridViewUtility.AddLabelTemplate(gvReport, "Prefix", "Prefix", 100)
        If chkContactname.Checked Then GridViewUtility.AddLabelTemplate(gvReport, "FirstName", "First Name", 100)
        If chkContactMiddleName.Checked Then _
               GridViewUtility.AddLabelTemplate(gvReport, "MiddleName", "Middle Name", 100)
        If chkContactLastName.Checked Then GridViewUtility.AddLabelTemplate(gvReport, "LastName", "Last Name", 100)

        If chkCompanyName.Checked Then _
               GridViewUtility.AddLabelTemplate(gvReport, "CompanyName", "Company", 300)
        If chkClientNo.Checked Then GridViewUtility.AddLabelTemplate(gvReport, "ClientNo", "Client No", 100)
        If chkDepartment.Checked Then _ 
               GridViewUtility.AddLabelTemplate(gvReport, "DepartmentTitle", "DepartmentTitle", 100)
        If chkTitle.Checked Then GridViewUtility.AddLabelTemplate(gvReport, "JobTitle", "Job Title", 100)
        ...
        ...
        ...
        gvReport.DataSource = lst
        gvReport.DataBind()

พอเราสั่ง DataBind โปรแกรมก็จะไปทำ RowDataBinding เราก็ต้องไปเขียนโค้ดเพื่อเอาข้อมูลไปใส่ใน ItemTemplate ให้ถูกต้อง

  
    Private Sub GridView_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

        'Skip for last empty row
        If e.Row.DataItemIndex = TryCast(Session(MAILING_LIST_SESSION), IList).Count - 1 Then Exit Sub

        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim ml As CMS.Library.ContactList = TryCast(e.Row.DataItem, CMS.Library.ContactList)

            If chkPrefix.Checked AndAlso ml.Contact.Prefix IsNot Nothing Then _
                     DirectCast(e.Row.FindControl("Prefix"), Label).Text = ml.Contact.Prefix.PrefixCode
            If chkContactname.Checked AndAlso ml.Contact.FirstName IsNot Nothing Then _
                     DirectCast(e.Row.FindControl("FirstName"), Label).Text = ml.Contact.FirstName.ToString
            If chkContactMiddleName.Checked AndAlso ml.Contact.MiddleName IsNot Nothing Then _
                     DirectCast(e.Row.FindControl("MiddleName"), Label).Text = ml.Contact.MiddleName.ToString
            If chkContactLastName.Checked AndAlso ml.Contact.LastName IsNot Nothing Then _
                     DirectCast(e.Row.FindControl("LastName"), Label).Text = ml.Contact.LastName.ToString
 
            If chkClientNo.Checked AndAlso ml.Company.ClientNo IsNot Nothing Then _
                      DirectCast(e.Row.FindControl("ClientNo"), Label).Text = ml.Company.ClientNo.ToString
            If chkDepartment.Checked AndAlso ml.DepartmentTitle IsNot Nothing Then _
                      DirectCast(e.Row.FindControl("DepartmentTitle"), Label).Text = ml.DepartmentTitle
            ...
            ...
            ...
            If ml.Active <> CMS.Library.Constants.ACTIVE Then
                e.Row.ForeColor = Drawing.Color.Red
                e.Row.Font.Italic = True
            End If
        End If
    End Sub

โค้ดส่วนที่สำคัญก็มีเท่านี้ครับ ส่วนที่ export ไป excel หรือ pdf ก็ไม่ยากแล้ว

ถ้าอนาคตมีการ modify หรือรองรับ control อื่นๆนอกจาก label เพิ่มจะมาอัพเดทอีกทีครับ

โค้ดสั่ง Suppress Picture ใน Crystal Report

            Dim picture1 As CrystalDecisions.CrystalReports.Engine.PictureObject
            picture1 = reportDoc.ReportDefinition.ReportObjects("Picture1")
            picture1.ObjectFormat.EnableSuppress = True