ASP.NET (VS2013)

連續三天的ASP.NET課程(104.5.18~5.20台中巨匠)，講師(恆逸資訊鄭淑芬老師)循序漸進教課以並對觀念清礎的解說，再次強化了些過去似懂非懂的觀念，有些用法其實可以更快更節省系統資源。例如只需要顯示的資料，只需要用SqlDataReader讀取資料，最後丟給Gridview顯示，過去確是撈到DataTable中，似乎沒必要。

此外控制項以及資料的Binding，過去都習慣視覺化+人工方式處理，但原來都可以由視覺化的方式完成。

日曆的控制項，過去只懂的取值，這次又知道透過 DayRender事件，在每格日子中加入自訂的內容。

最後經由一個簡單的小專案「網路書店」，並建置在IIS8.5，完成這三天的訓練。

常用SQL (Object Data Source)

Web.config設定

---------------------------------------------

 <connectionStrings>
        <add name="pubsConnectionString" connectionString="Data Source=JAMES;Initial Catalog=pubs;USER ID=AAA;PASSWORD=12345678" providerName="System.Data.SqlClient" />
        
 </connectionStrings>

------------------------------------------------
採用 App_data方式
-------------------
<add key="default_cn" value="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True"/>

'VB.NET讀取app.config
Dim cn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("default_cn"))

'ASP.NET讀取web.config
Imports System.Web.Configuration

 Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings("default_cn"))

Public Class membersDB

    '----------------------------------------------

    'Select ---

    Public Shared Function Get_members() As DataTable

        Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings("default_cn"))


       Try
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If

            Dim sqlString As String = ""

            sqlString += " SELECT * from members "

            Dim cmd As New SqlCommand(sqlString, cn)
            Dim adpt As New SqlDataAdapter
            Dim dt As New DataTable
            adpt.SelectCommand = cmd
            adpt.Fill(dt)
            Return dt

        Catch ex As Exception
           Throw ex
        Finally
            cn.Close()
        End Try
    End Function

    '----------------------------------------------
    'Insert ---

    Public Shared Function insert_members(ByVal name As String, ByVal tel As String, ByVal memo As String) As Integer

        ' Dim cn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("equip_cn"))

        Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings("default_cn"))

        Try

            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If

            Dim sqlstring As String = "insert into [members] ( name, tel, memo  ) values ( @name, @devtype, @memo )"

            Dim cmd As New SqlCommand(sqlstring, cn)

          cmd.Parameters.Add("@name", SqlDbType.Char, 4)
            cmd.Parameters("@name").Value = name
           cmd.Parameters.Add("@tel", SqlDbType.Char, 2)
            cmd.Parameters("@tel").Value = tel
           cmd.Parameters.Add("@memo", SqlDbType.Text, 2147483647)
            cmd.Parameters("@memo").Value = memo


            Dim i As Integer
            i = cmd.ExecuteNonQuery()
            Return i

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function




    Public Shared Function Update_members(ByVal Name As String, ByVal Tel As String, ByVal ID As Integer) As Integer

        Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings("default_cn"))

        Try

            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If

            Dim sqlString As String = String.Format("Update [members] set NAME=@NAME,TEL=@TEL  where id=@id")

            Dim cmd As New SqlCommand(sqlString, cn)


            cmd.Parameters.Add("@id", SqlDbType.Int)
            cmd.Parameters("@id").Value = ID

            cmd.Parameters.Add("@TEL", SqlDbType.VarChar, 20)
            cmd.Parameters("@TEL").Value = Tel

            cmd.Parameters.Add("@NAME", SqlDbType.NVarChar, 50)
            cmd.Parameters("@NAME").Value = Name

            Dim i As Integer
            i = cmd.ExecuteNonQuery()
            Return i
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function


    'Delete ---
    Public Shared Function Delete_alldev(ByVal ID As Integer) As Integer
        Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings("default_cn"))

        Try

            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If

            Dim sqlString As String = "Delete from [members] where ID=@ID"
            Dim cmd As New SqlCommand(sqlString, cn)

            cmd.Parameters.Add("@ID", SqlDbType.Int)
            cmd.Parameters("@ID").Value = ID

            Dim i As Integer
            i = cmd.ExecuteNonQuery()
            Return i

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
        End Try
    End Function

End Class