常用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