【ASP】【VB】データベースへのアクセス

サンプル

* スケジュール管理を作成して、ASP.NETのデータベースへのアクセスを学ぶ

データベース

構成：Scheduleテーブル

 * id int型(PK)
 * title varchar(50)型
 * date date型
 * time char(5)型
 * remarks varchar(255)型

【1】ScheduleTool.aspx

構成

 * Calendarコントロール x 1

プログラム

Imports System.Data.Common

Public Class ScheduleTool
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim setting As ConnectionStringSettings = _
    ConfigurationManager.ConnectionStrings("SampleDBConnectionString")
        Dim factory As DbProviderFactory = _
            DbProviderFactories.GetFactory(setting.ProviderName)

        Using db As DbConnection = factory.CreateConnection()
            db.ConnectionString = setting.ConnectionString
            Dim command As DbCommand = factory.CreateCommand()
            command.CommandText = _
                "SELECT date FROM Schedule ORDER BY date DESC"
            command.Connection = db
            db.Open()

            Dim reader As DbDataReader = command.ExecuteReader()
            If reader.HasRows Then
                Do While reader.Read()
                    Calendar1.VisibleDate = Date.Parse(reader("date"))
                    Exit Do
                Loop
            End If

            db.Close()
        End Using
    End Sub

    Protected Sub Calendar1_DayRender(sender As Object, e As System.Web.UI.WebControls.DayRenderEventArgs) Handles Calendar1.DayRender
        Dim setting As ConnectionStringSettings = _
            ConfigurationManager.ConnectionStrings("SampleDBConnectionString")
        Dim factory As DbProviderFactory = _
            DbProviderFactories.GetFactory(setting.ProviderName)

        Using db As DbConnection = factory.CreateConnection()
            db.ConnectionString = setting.ConnectionString
            Dim command As DbCommand = factory.CreateCommand()
            command.CommandText = _
                "SELECT id, title, time FROM Schedule WHERE date = @date"
            command.Connection = db
            Dim parameter As DbParameter = factory.CreateParameter()
            parameter.ParameterName = "@date"
            parameter.Value = e.Day.Date.ToString("yyyy/MM/dd")
            command.Parameters.Add(parameter)
            db.Open()

            Dim reader As DbDataReader = command.ExecuteReader()
            Do While reader.Read()
                Dim literalControl As New LiteralControl("<br />")
                Dim link As New HyperLink()
                link.NavigateUrl = "Details.aspx?id=" & reader("id")
                link.Text = reader("time") & " " & reader("title")
                e.Cell.Controls.Add(literalControl)
                e.Cell.Controls.Add(link)
            Loop
            db.Close()
        End Using
    End Sub
End Class

【2】Details.aspx

構成

 * FormViewコントロール x 1
 * ValidationSummaryコントロール x 1

サンプル

Protected Sub FormView1_ItemInserting(sender As Object, e As System.Web.UI.WebControls.FormViewInsertEventArgs) Handles FormView1.ItemInserting
    If Not Page.IsValid Then
        e.Cancel = True
    End If
End Sub

Protected Sub FormView1_ItemUpdating(sender As Object, e As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles FormView1.ItemUpdating
    If Not Page.IsValid Then
        e.Cancel = True
    End If
End Sub

Protected Sub FormView1_ItemInserted(sender As Object, e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted
    Response.Redirect("ScheduleTool.aspx")
End Sub

Protected Sub FormView1_ItemUpdated(sender As Object, e As System.Web.UI.WebControls.FormViewUpdatedEventArgs) Handles FormView1.ItemUpdated
    Response.Redirect("ScheduleTool.aspx")
End Sub

Protected Sub FormView1_ItemDeleted(sender As Object, e As System.Web.UI.WebControls.FormViewDeletedEventArgs) Handles FormView1.ItemDeleted
    Response.Redirect("ScheduleTool.aspx")
End Sub