본문으로 바로가기

(vb.net) SQL 연결방법

category vb.net 2014. 11. 12. 15:15
' SQL Client 연결
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim SqlConn As New SqlConnection("User ID=sa;Password=sa;Persist Security Info=True;Initial Catalog=pubs;Data Source=kblee4")
        SqlConn.Open()
        Dim ds As New DataSet()
        Dim da As Object
        Dim strSQL As String = "Select * from Authors"
        da = New SqlClient.SqlDataAdapter(strSQL, SqlConn)
        da.Fill(ds, "authors")
        SqlConn.Close()
        DataGrid1.DataSource = ds.Tables(0).DefaultView
    End Sub

'OLE DB 연결
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        ' Conntring 의 변경
        Dim OleConn As New OleDbConnection("Provider=SQLOLEDB;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=pubs;Data Source=kblee4")
        Dim strSQL As String = "Select * from Authors"
        OleConn.Open()
        Dim Ds As New DataSet()
        Dim OleAdapter As New OleDbDataAdapter(strSQL, OleConn)

        OleAdapter.Fill(Ds, "authors")
        OleConn.Close()
        DataGrid1.DataSource = Ds.Tables(0).DefaultView
    End Sub

'ODBC 연결
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ' Conntring 의 변경
        Try
            Dim myConnection As New OdbcConnection("DSN=test;uid=sa;pwd=sa")
            myConnection.Open()
            'Driver={SQL Server};Server=MyServer;UID=sa;PWD=sqLs$5xr;Database=Northwind;
            'Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;UID=odbcuser;PWD=odbc$5xr

            Dim strSQL As String = "Select * from Authors"

            Dim adapter As New OdbcDataAdapter()
            Dim Ds As New DataSet()
            adapter.SelectCommand = New OdbcCommand(strSQL, myConnection)
            adapter.Fill(Ds)


            DataGrid1.DataSource = Ds.Tables(0).DefaultView
        Catch ex As Exception
            MsgBox(ex.Message.ToString)

        End Try

    End Sub


    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim c_ds As New DataSet()
        DataGrid1.DataSource = c_ds

    End Sub

 


간단 예제
Imports System.Data.SqlClient
Imports System.Data

Public Class Form1
    Dim conn As SqlConnection
    Dim str As String = String.Format("Data Source=컴퓨터이름; Initial Catalog=DB명;Integrated Security=False; uid=sa; pwd=패스워드")

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

        Dim csds As New DataSet
        Dim csda As New SqlDataAdapter
        Dim itmCur As ListViewItem
 
        Try
            conn = New SqlConnection(str)
            conn.Open()
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        End Try

        Dim strsql As String = "select * from 테이블명"
        csda.SelectCommand = New SqlCommand(strsql, conn)
        csda.Fill(csds)
 
        For Each ds As DataRow In csds.Tables(0).Rows
            itmCur = New ListViewItem
            itmCur.Text = ds.Item("ACTCODE").ToString()
            itmCur.SubItems.Add(ds.Item("ACTNAME").ToString())
            ListView1.Items.Add(itmCur)
        Next
 
    End Sub


End Class