代码管理(2)

Imports System.Data.SqlClient

 

Public Class WebForm1

    Inherits System.Web.UI.Page

 

    Private holiday As String = ""

    Dim flag As Integer

    Dim defarray() As String

    Dim SqlConnection1 As New SqlClient.SqlConnection("workstation id='NIIT-CF8DA03054';packet size=4096;user id=sa;data source='.';persist security info=False;initial catalog=pubs")

    'Dim SqlDataAdapter1 As New SqlClient.SqlDataAdapter

    Dim SqlDataAdapter2 As New SqlClient.SqlDataAdapter

    Dim DataSet1 As New DataSet

    Dim dataSet2 As New DataSet

    Private dt As New DataTable

 

 

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        '在此处放置初始化页的用户代码

        Dim intyear As Integer

        Dim intmonth As Integer

        If Not Me.IsPostBack Then

            '日历显示当前日期

 

            intyear = Now.Year()

            intmonth = Now.Month()

            Me.ddlYear.SelectedIndex = Me.ddlYear.Items.IndexOf(Me.ddlYear.Items.FindByValue(intyear.ToString))

            Me.ddlMonth.SelectedIndex = Me.ddlMonth.Items.IndexOf(Me.ddlMonth.Items.FindByValue(intmonth.ToString))

            ''end

 

            '  创建一个放在Session中的DataTable

            ' Declare DataColumn and DataRow variables.

            Dim myDataColumn As DataColumn

            Dim myDataRow As DataRow

 

            ' Create new DataColumn, set DataType, ColumnName and add to DataTable.   

            myDataColumn = New DataColumn

            myDataColumn.DataType = System.Type.GetType("System.String")

            myDataColumn.ColumnName = "Year"

            dt.Columns.Add(myDataColumn)

 

            ' Create second column.

            myDataColumn = New DataColumn

            myDataColumn.DataType = Type.GetType("System.String")

            myDataColumn.ColumnName = "Month"

            dt.Columns.Add(myDataColumn)

 

            ' Create three column.

            myDataColumn = New DataColumn

            myDataColumn.DataType = Type.GetType("System.String")

            myDataColumn.ColumnName = "Day"

            dt.Columns.Add(myDataColumn)

 

            ' Create four column.

            myDataColumn = New DataColumn

            myDataColumn.DataType = Type.GetType("System.String")

            myDataColumn.ColumnName = "flag"

            dt.Columns.Add(myDataColumn)

            ''end

 

            '定义一个Session

            Me.SqlConnection1.Open()

            Me.SqlDataAdapter1.SelectCommand = New SqlClient.SqlCommand("select * from holiday", Me.SqlConnection1)

            Me.SqlDataAdapter1.Fill(Me.DataSet1)

            Me.SqlConnection1.Close()

            dt = Me.DataSet1.Tables(0)

            Session("SelectDays") = dt

            ''end

        End If

 

        '避免每次页面载入时当前日期被选中

        Me.Calendar1.SelectedDate = Nothing

 

    End Sub

 

 

 

    Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.PreRender

        dt = CType(Session("SelectDays"), DataTable)

 

        '1)只有当没有点击日历上的日期时才实现      

        If Me.Calendar1.SelectedDate = Nothing Then

            Dim selstr As String

            Dim foundRows As DataRow()

            selstr = "year='" & Me.ddlYear.SelectedItem.Value & "'and month='" & Me.ddlMonth.SelectedItem.Value & "'"

            foundRows = dt.Select(selstr)

            '如果数据库中没有假日设定的纪录 ---- 得到默认的假日设定 例如:5,6        

            If foundRows.Length = 0 Then

 

                Me.holiday = Me.GetDefHolidy().Trim

                '将字符串生成数组              

                defarray = Split(holiday, ",")

                flag = 1

            End If

        End If

 

 

        '2)只有当点击了日历上的日期时才实现      

        If Not Me.Calendar1.SelectedDate = Nothing Then

            Dim newrow As DataRow = dt.NewRow

            Dim eachrow As DataRow

            '查询session，该日期是否已经存在于session中，如果在则删除该条记录;不在则添加该条记录         

            For Each eachrow In dt.Rows

                '有这条记录就删除它               

                If eachrow.Item(0) = Me.Calendar1.SelectedDate.Year And eachrow.Item(1) = Me.Calendar1.SelectedDate.Month And eachrow.Item(2) = Me.Calendar1.SelectedDate.Day Then

                    dt.Rows.Remove(eachrow)

                    Session("SelectDays") = dt

                    flag = 0

                    Exit Sub

                End If

            Next

            '没有这条记录就添加它      

            newrow.Item(0) = Me.Calendar1.SelectedDate.Year

            newrow.Item(1) = Me.Calendar1.SelectedDate.Month

            newrow.Item(2) = Me.Calendar1.SelectedDate.Day

            newrow.Item(3) = "1"

            dt.Rows.Add(newrow)

            Session("SelectDays") = dt

            flag = 0

        End If

    End Sub

 

 

 

    Private Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) Handles Calendar1.DayRender

        '如果不是这个月里的日期的天，则禁用该日期的Enabled,IsSelectable  

        If Me.Calendar1.VisibleDate <> Nothing Then

            If e.Day.Date.Month <> Me.Calendar1.VisibleDate.Month Then

                e.Day.IsSelectable = False

                e.Cell.Enabled = False

                Exit Sub

            End If

        Else

            If e.Day.Date.Month <> Me.Calendar1.TodaysDate.Month Then

                e.Day.IsSelectable = False

                e.Cell.Enabled = False

                Exit Sub

            End If

        End If

 

        dt = CType(Session("SelectDays"), DataTable)

        '如果数据库中没有假日设定的纪录,得到默认的假日设定    

 

        If flag = 1 Then

            Dim str As String

            For Each str In Me.defarray

                If e.Day.Date.DayOfWeek = Integer.Parse(str) Then

                    e.Cell.ForeColor = System.Drawing.Color.Red

                    '''先将默认假日设定的日期放入session变量中             

                    Dim newrow As DataRow = dt.NewRow

                    newrow.Item(0) = e.Day.Date.Year

                    newrow.Item(1) = e.Day.Date.Month

                    newrow.Item(2) = e.Day.Date.Day

                    newrow.Item(3) = "1"

                    dt.Rows.Add(newrow)

 

                End If

            Next

            Session("SelectDays") = dt

        Else

 

            '如果数据库中有假日设定的纪录,读session中的值      

            Dim i As Integer

            For i = 0 To dt.Rows.Count - 1

                If e.Day.Date.Year = dt.Rows.Item(i).Item(0) And e.Day.Date.Month = dt.Rows.Item(i).Item(1) And e.Day.Date.Day = dt.Rows.Item(i).Item(2) Then

                    e.Cell.ForeColor = System.Drawing.Color.Red

                End If

            Next

        End If

 

    End Sub

 

 

    Private Sub btn_sure1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_sure1.Click

 

        If Me.ModifyHoliday() = 1 Then

            Page.RegisterStartupScript("", "<script language='javascript'>alert('操作成功')</script>")

        Else

            Page.RegisterStartupScript("", "<script language='javascript'>alert('操作失败')</script>")

        End If

    End Sub

 

 

 

 

 

    Private Sub ddlYear_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlYear.SelectedIndexChanged

        ''把Session清空

        'dt = CType(Session("SelectDays"), DataTable)

        'dt.Clear()

        'Session("SelectDays") = dt

        Me.Calendar1.VisibleDate = New DateTime(Integer.Parse(Me.ddlYear.SelectedItem.Value), Integer.Parse(Me.ddlMonth.SelectedItem.Value), 1)

    End Sub

 

    Private Sub ddlMonth_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlMonth.SelectedIndexChanged

        ''把Session清空

        'dt = CType(Session("SelectDays"), DataTable)

        'dt.Clear()

        'Session("SelectDays") = dt

        Me.Calendar1.VisibleDate = New DateTime(Integer.Parse(Me.ddlYear.SelectedItem.Value), Integer.Parse(Me.ddlMonth.SelectedItem.Value), 1)

    End Sub

 

    Private Sub Calendar1_VisibleMonthChanged(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.MonthChangedEventArgs) Handles Calendar1.VisibleMonthChanged

        ''把Session清空

        'dt = CType(Session("SelectDays"), DataTable)

        'dt.Clear()

        'Session("SelectDays") = dt

        If e.NewDate.Month <> e.PreviousDate.Month Then

            Me.ddlYear.SelectedIndex = Me.ddlYear.Items.IndexOf(Me.ddlYear.Items.FindByValue(Me.Calendar1.VisibleDate.Year.ToString))

            Me.ddlMonth.SelectedIndex = Me.ddlMonth.Items.IndexOf(Me.ddlMonth.Items.FindByValue(Me.Calendar1.VisibleDate.Month.ToString))

        End If

 

    End Sub

 

    Private Function GetDefHolidy() As String

        'Me.DataSet2.Clear()

        Me.SqlConnection1.Open()

        Me.SqlDataAdapter2.SelectCommand = New SqlClient.SqlCommand("select * from def_holiday", Me.SqlConnection1)

        Me.SqlDataAdapter2.Fill(Me.DataSet2)

        Me.SqlConnection1.Close()

        Me.holiday = Me.DataSet2.Tables(0).Rows(0)(0)

        Return holiday

    End Function

 

    Private Function ModifyHoliday() As Integer

        dt = CType(Session("SelectDays"), DataTable)

        Dim insertCommand As New SqlClient.SqlCommand

        Me.SqlConnection2.Open()

      

        Me.SqlDataAdapter1.DeleteCommand = New SqlClient.SqlCommand("delete from holiday", Me.SqlConnection2)

        Try

            Me.SqlDataAdapter1.DeleteCommand.ExecuteNonQuery()

            Dim eachrow As DataRow

            Dim insertString As String

 

 

            For Each eachrow In dt.Rows

                insertString += "insert into holiday values('" + eachrow.Item(0) + "','" + eachrow.Item(1) + "','" + eachrow.Item(2) + "','" + eachrow.Item(3) + "')"

            Next

            insertCommand.Connection = Me.SqlConnection2

            insertCommand.CommandText = insertString

            insertCommand.ExecuteNonQuery()

            Return 1

        Catch ex As Exception

            Return 0

        Finally

            Me.SqlConnection2.Close()

        End Try

    End Function

 

 

 

End Class