สอบถามเกี่ยวกับการวางฐานข้อมูล Calendar ใน VB.Net หน่อยครับ

Imports System.Data.SqlClient
Imports System.Data

Partial Class HoildayCalendar
    Inherits System.Web.UI.Page
    Dim clsdatabase As String = ConfigurationSettings.AppSettings("ConnDatabase")
    Dim connect As New SqlConnection(clsDatabase)
    Dim clsTCDB As New clsTCDB
    Dim ClassDatabase As New clsDatabase
    Public da As New SqlDataAdapter
    Public cb As SqlCommandBuilder
    Dim dr As SqlDataReader
    Dim ds As New DataSet
    Dim sql As String


    Private Property _dtMyDates As DataTable
        Get
            Return ViewState("_MyDates")
        End Get
        Set(value As DataTable)
            ViewState("_MyDates") = value
        End Set
    End Property

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            LoadMonths()
            LoadYears()
            LoadMyImportantDates() ' load our important dates before the calendar is loaded '
            LoadCalendar()
        End If
    End Sub

    Protected Sub LoadMonths()
        For i As Integer = 1 To 12
            Dim li As New ListItem
            li.Text = MonthName(i) ' MonthName() gets the month name based on the users computer settings (their local month name rather than English default) '
            li.Value = i.ToString
            ddl_month.Items.Add(li)
            If li.Value = DateTime.Now.Month.ToString Then
                li.Selected = True
            End If
        Next
    End Sub

    Protected Sub LoadYears()
        Dim yearsBack As Integer = 3
        Dim yearsForward As Integer = 3
        For i As Integer = DateTime.Now.AddYears(-yearsBack).Year To DateTime.Now.AddYears(yearsForward).Year
            Dim li As New ListItem
            li.Text = i.ToString
            li.Value = i.ToString
            ddl_year.Items.Add(li)
            If li.Value = DateTime.Now.Year Then
                li.Selected = True
            End If
        Next
    End Sub

    Protected Sub LoadMyImportantDates()

        connect.Open()
        Dim sqlstring As String
        sqlstring = "select cld_date,cld_Hoilday from TCcalendar"
        Dim da As SqlDataAdapter = New SqlDataAdapter(sqlstring, connect)
        Dim ds As DataSet = New DataSet
        da.SelectCommand = New SqlCommand(sql, connect)
        cb = New SqlCommandBuilder(da)


        Me._dtMyDates = New DataTable
        Me._dtMyDates.Columns.Add(New DataColumn("MyDate", GetType(System.DateTime))) ' This will hold the event date '
        Me._dtMyDates.Columns.Add(New DataColumn("MyEvent", GetType(System.String))) ' This will hold the event details '
        Me._dtMyDates.Columns.Add(New DataColumn("Important", GetType(System.Boolean)))    ' This will hold a True/False value based on importance '

        Dim dr As DataRow = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 1, 1)
        dr.Item("MyEvent") = "วันขึ้นปีใหม่"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 1, 2)
        dr.Item("MyEvent") = "ชดเชยวันขึ้นปีใหม่"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 1, 13)
        dr.Item("MyEvent") = "วันเด็กแห่งชาติ"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 1, 16)
        dr.Item("MyEvent") = "วันครู"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 2, 14)
        dr.Item("MyEvent") = "วันวาเลนไทน์"
        dr.Item("Important") = True
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 2, 16)
        dr.Item("MyEvent") = "วันตรุษจีน"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 3, 1)
        dr.Item("MyEvent") = "วันมาฆบูชา"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 4, 6)
        dr.Item("MyEvent") = "วันจักรี"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 4, 13)
        dr.Item("MyEvent") = "วันวงกรานต์"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 5, 1)
        dr.Item("MyEvent") = "วันแรงงานแห่งชาติ"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 5, 14)
        dr.Item("MyEvent") = "วันพืชมงคล"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 5, 29)
        dr.Item("MyEvent") = "วันวิสาขบูชา"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 7, 1)
        dr.Item("MyEvent") = "วันหยุดภาคครึ่งปีธนาคาร"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 7, 2)
        dr.Item("MyEvent") = "ชกเชยวันหยุดภาคครึ่งปีธนาคาร"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 7, 27)
        dr.Item("MyEvent") = "วันอาสาฬหบูชา"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 7, 28)
        dr.Item("MyEvent") = "วันเฉลิมพระชนมพรรษาสมเด็จพระเจ้าอยู่หัว"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 7, 30)
        dr.Item("MyEvent") = "ชดเชยวันเฉลิมพระชนมพรรษาสมเด็จพระเจ้าอยู่หัวา"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 8, 12)
        dr.Item("MyEvent") = "วันเฉลิมพระชนมพรรษาสมเด็จพระนางเจ้าฯพระบรราชินีนาถ"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 8, 13)
        dr.Item("MyEvent") = "ชดเชยวันเฉลิมพระชนมพรรษาสมเด็จพระนางเจ้าฯพระบรราชินีนาถ"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 10, 13)
        dr.Item("MyEvent") = "วันคล้ายวันสวรรคตพระบาทสมเด็จพระปรมินทรมหาภูมิพลอดุลยเดช"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 10, 15)
        dr.Item("MyEvent") = "ชดเชยวันคล้ายวันสวรรคตพระบาทสมเด็จพระปรมินทรมหาภูมิพลอดุลยเดช"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 10, 23)
        dr.Item("MyEvent") = "วันปิยมหาราช"
        dr.Item("Important") = False
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 10, 31)
        dr.Item("MyEvent") = "วันฮาโลวีน"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 11, 22)
        dr.Item("MyEvent") = "วันลอยกระทง"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 12, 10)
        dr.Item("MyEvent") = "วันรัฐธรรมนูญ"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 12, 25)
        dr.Item("MyEvent") = "วันคริสต์มาส"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)
        dr = Me._dtMyDates.NewRow()
        dr.Item("MyDate") = New DateTime(2018, 12, 31)
        dr.Item("MyEvent") = "วันสิ้นปี"
        dr.Item("Important") = False
        Me._dtMyDates.Rows.Add(dr)

    

    End Sub

    Protected Sub lnk_loadCalendar_Click(sender As Object, e As EventArgs) Handles lnk_loadCalendar.Click
        LoadCalendar()
    End Sub

    Protected Sub LoadCalendar()
        Dim dates As New List(Of DateTime)
        Dim dtLastMonth As Integer = 0
        Dim dtLastYear As Integer = 0
        dates.Clear()

        Dim m As Integer = Convert.ToInt16(ddl_month.SelectedItem.Value)
        Dim y As Integer = Convert.ToInt16(ddl_year.SelectedItem.Value)


        If m = 1 Then
            dtLastMonth = m + 11
            dtLastYear = y - 1
        Else
            dtLastMonth = m - 1
            dtLastYear = y
        End If

        Dim dtFirst As New DateTime(y, m, 1)
        Dim daysInThisMonth As Integer = System.DateTime.DaysInMonth(y, m)
        Dim daysInLastMonth As Integer = System.DateTime.DaysInMonth(dtLastYear, dtLastMonth)

        Select Case dtFirst.DayOfWeek.ToString()

            Case Is = "Sunday"

            Case Is = "Monday"
                For i As Integer = 1 To 1
                    Dim d As New DateTime(dtLastYear, dtLastMonth, ((daysInLastMonth - 1) + i))
                    dates.Add(d)
                Next

            Case Is = "Tuesday"
                For i As Integer = 1 To 2
                    Dim d As New DateTime(dtLastYear, dtLastMonth, ((daysInLastMonth - 2) + i))
                    dates.Add(d)
                Next
            Case Is = "Wednesday"
                For i As Integer = 1 To 3
                    Dim d As New DateTime(dtLastYear, dtLastMonth, ((daysInLastMonth - 3) + i))
                    dates.Add(d)
                Next
            Case Is = "Thursday"
                For i As Integer = 1 To 4
                    Dim d As New DateTime(dtLastYear, dtLastMonth, ((daysInLastMonth - 4) + i))
                    dates.Add(d)
                Next
            Case Is = "Friday"
                For i As Integer = 1 To 5
                    Dim d As New DateTime(dtLastYear, dtLastMonth, ((daysInLastMonth - 5) + i))
                    dates.Add(d)
                Next
            Case Is = "Saturday"
                For i As Integer = 1 To 6
                    Dim d As New DateTime(dtLastYear, dtLastMonth, ((daysInLastMonth - 6) + i))
                    dates.Add(d)
                Next

        End Select

        For i As Integer = 1 To Date.DaysInMonth(y, m)
            Dim d As New DateTime(y, m, i)
            dates.Add(d)
        Next

        rpt_calendar.DataSource = dates
        rpt_calendar.DataBind()
    End Sub

    Protected Sub rpt_calendar_ItemCreated(sender As Object, e As RepeaterItemEventArgs) Handles rpt_calendar.ItemCreated
        Dim lnk_dayLink As LinkButton = CType(e.Item.FindControl("lnk_dayLink"), LinkButton)
        AddHandler lnk_dayLink.Command, AddressOf DayLinkClicked ' tell ASP.NET to fire DayLinkClicked() when the linkbutton is clicked '
    End Sub

    Protected Sub rpt_calendar_ItemDataBound(sender As Object, e As RepeaterItemEventArgs) Handles rpt_calendar.ItemDataBound
      Dim pnl_calendarDay As Panel = CType(e.Item.FindControl("pnl_calendarDay"), Panel)
        Dim lnk_dayLink As LinkButton = CType(e.Item.FindControl("lnk_dayLink"), LinkButton)
        Dim ltl_dayEvents As Literal = CType(e.Item.FindControl("ltl_dayEvents"), Literal)

        Dim d As DateTime = CType(e.Item.DataItem, DateTime)

        Dim sb As New StringBuilder
        sb.Append(d.Day.ToString)
        sb.Append(" ")
        sb.Append(d.ToString("dddd"))
        lnk_dayLink.Text = sb.ToString
        lnk_dayLink.CommandArgument = d

        Dim dvr As New DataView(Me._dtMyDates)
       



        dvr.RowFilter = String.Format("MyDate='{0}'", d.ToShortDateString)
        Dim dt As DataTable = dvr.ToTable
        If dt.Rows.Count > 0 Then
            Dim sb_day As New StringBuilder
            For Each dr As DataRow In dt.Rows
                sb_day.Append("<p class=""event")
                If Convert.ToBoolean(dr.Item("Important")) Then
                    sb_day.Append(" important")
                End If
                sb_day.Append(""">")
                sb_day.Append(dr.Item("MyEvent").ToString)
                sb_day.Append("</p>")
            Next
            ltl_dayEvents.Text = sb_day.ToString
        End If
    End Sub

    Protected Sub DayLinkClicked(sender As Object, e As CommandEventArgs) ' Handles set by rpt_calendar_ItemCreated() '
        Dim d As DateTime = Convert.ToDateTime(e.CommandArgument)

    End Sub

End Class