(vb.net) SQL 연결방법

' 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