VB로 MS-SQL 연동

insert,update,delete

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles MyBase.Load
        Dim connectionString As String
        Dim sqlConn As New SqlConnection
        Dim sqlComm As New SqlCommand
        connectionString = "server = 127.0.0.1,1433; uid = sa; pwd = password; database = member;"
        sqlConn = New SqlConnection(connectionString)
        sqlComm = New SqlCommand()
        sqlComm.Connection = sqlConn
        sqlComm.CommandText = "insert into tbl_member (code,id,addr) values (@param1,@param2,param3)"
        'sqlComm.CommandText = "update tbl_member set addr=@param3 where code=@param1 and id=param2"
        'sqlComm.CommandText = "delete tbl_member where code=@param1 and id=param2"
        sqlComm.Parameters.AddWithValue("@param1", "1")
        sqlComm.Parameters.AddWithValue("@param2", "abc")
        sqlComm.Parameters.AddWithValue("@param3", "서울")
        Try
            sqlConn.Open()
            sqlComm.ExecuteNonQuery()
            sqlConn.Close()
        Catch ex As Exception
            Response.Write(ex)
        End Try
    End Sub
End Class

---

select

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles MyBase.Load
        Dim connectionString As String
        Dim sqlConn As New SqlConnection
        Dim sqlComm As New SqlCommand
        Dim dt As DataTable
        dt = New DataTable("MEMBER")
        dt.Columns.Add("code", GetType(Integer))
        dt.Columns.Add("id", GetType(String))
        dt.Columns.Add("addr", GetType(String))
        connectionString = "server = 127.0.0.1,1433; uid = sa; pwd = password; database = member;"
        sqlConn = New SqlConnection(connectionString)
        sqlComm = New SqlCommand()
        sqlComm.Connection = sqlConn
        sqlComm.CommandText = "select top 10 code,id,addr from tbl_member where m_id=@param1 order by m_id asc"
        sqlComm.Parameters.AddWithValue("@param1", "master")
        Try
            sqlConn.Open()
            Dim rs As SqlDataReader = sqlComm.ExecuteReader()
            If rs.HasRows Then
                Do While rs.Read()
                    dt.Rows.Add(rs(0), rs(1), rs(2))
                Loop
            End If
            rs.Close()
            sqlConn.Close()
        Catch ex As Exception
            Response.Write(ex)
        End Try
        Response.Write("<table border=""1""><thead><tr><th>CODE</th><th>ID</th><th>ADDR</th></tr></thead><tbody>")
        For i As Integer = 0 To dt.Rows.Count - 1
            Response.Write(String.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td></tr>", dt.Rows(i).Item("code"), dt.Rows(i).Item("id"), dt.Rows(i).Item("addr")))
        Next
        Response.Write("</tbody></table>")
    End Sub
End Class