VB.NET 복사 붙여넣기 / insert into / update table set 등등 잡다 source Mysql/Access(accdb)/ExcelDB

철저히 메모용 !

간단하게 뽑아서 쓰기 좋기 위해 만듬.

​<붙여 넣었을 때>

    Public Sub PasteToListbox(sender As Object, e As KeyEventArgs) Handles lstUp.KeyDown
        _dt = New DataTable
        lstUp.Items.Clear()
        '// Control 키 + v 키 눌렀을 때
        If (e.Control = True And e.KeyCode = Keys.V) Then
            '// 클립보드 얻어와서 Datatable에 담음 (clipboard_to_datatable로 별도로 만듬)
            _dt = clp.Clipboard_to_DataTable(Forms.Clipboard.GetText())
            If (_dt.Rows.Count > 0) Then
                For Each dr As DataRow In _dt.Rows
                    lstUp.Items.Add(dr(0).ToString)
                Next
            End If
        End If
    End Sub

<Clipboard_to_DataTable> : DataTabel로 return

Public Function Clipboard_to_DataTable(ByRef pClipboard As String) As DataTable

    '// List<T> 안에 또 List를 생성함. 
    Dim table As List(Of List(Of String)) = New List(Of List(Of String))

    '// Clipboard String을 변수에 담음.
    Dim importText As String = pClipboard

    importText = importText.Replace(vbLf, "")
    '// vbCrLf : 간단히 엔터 값이라고 보면 됨
    '// vbCrLf 를 기준으로 자르기 
    Dim lines() As String = importText.Split(vbCrLf)

    '// Split 된 문자열 배열의 length 만큼 반복
    For i As Integer = 0 To lines.Length - 1

        If (String.IsNullOrEmpty(lines(i))) Then
            Exit For
        End If

        '// 임시로 String을 담을 List<T> 선언
        Dim cellList As New List(Of String)
        Dim cells() As String = lines(i).Split(vbTab)

        For Each str As String In cells
            str = str.Replace(Environment.NewLine, String.Empty)
        Next

        '// 임시 List<T>에 값을 담고, 담아진 List를 
        cellList.AddRange(cells)

        '// List 배열에 List를 Add한다.
        table.Add(cellList)

    Next

    '// DataTable을 만든다.
    Dim dt As DataTable = New DataTable
    Dim dCol As DataColumn = New DataColumn
    If (table.Count > 0) Then
        For i As Integer = 0 To table.Item(0).Count - 1
            With dCol
                dCol = New DataColumn : .DataType = GetType(String)
                .ColumnName = "Column_" & i
                .Caption = "Column_" & i
                dt.Columns.Add(dCol)
            End With
        Next
    End If
    '// DataTable에 List 값을 담는다.
    For Each s As List(Of String) In table
        If s.Count > 0 Then
            dt.Rows.Add(s.ToArray)
        End If
    Next
    '// DataTable을 Return
    Return dt
End Function

<쿼리 만들고 데이터 DB에 올리기>

Insert Into

 

 Private Sub InsertData(sender As Object, e As EventArgs) Handles btnMDSave.Click
        '// Databse에 올릴 때
        Dim sql As String, sumsql As String = ""
        Dim cnt As Integer = 0
        Dim dt As DataTable = _dt

        sql = "insert into td_defect.`td_ota_app` (AppName) values "

        For Each dr As DataRow In _dt.Rows
            Try
                '// 문자열을 ('test'), ('test'), ('test'), ('test') 이런식으로 묶어서 한 번에 Insert
                sumsql += String.Format("('{0}'),", dr.Item(0).ToString)
                cnt += 1
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        Next

        If (cnt > 0) Then
            '// 맨 마지막 string은 ('test'), <- 이 상태이므로 마지막 콤마 잘라줌.
            sumsql = sumsql.Substring(0, Len(sumsql) - 1)
            sql += sumsql
            dbc.Query_to_Mysql(sql) '<-- 이건 내가 자체적으로 만든 함수.
            MsgBox(cnt & "건 추가완료.")
        End If

    End Sub

<DB올릴 때 만든 함수>

Public Function Query_to_Mysql(ByRef sql As String, Optional ByRef connstring As String = "Server=127.0.0.1;Uid=ss_user;Pwd=asdf123;Database=suns") As Boolean
    Dim chkErr As Boolean = False
    Using cn As New MySql.Data.MySqlClient.MySqlConnection With {.ConnectionString = connstring}
        Using cmd As New MySql.Data.MySqlClient.MySqlCommand With {
        .Connection = cn,
        .CommandType = System.Data.CommandType.Text,
        .CommandText = sql}
            Try
                cn.Open()
                cmd.ExecuteNonQuery()
                chkErr = True
                Debug.Print("[Query result] > " & "Successfull")
            Catch ex As Exception
                Qportals.Debugging.Print(ex.Message)
                chkErr = False
            Finally
                cn.Close()
            End Try
        End Using
        Return If(chkErr = False, False, True)
    End Using
End Function

<Mysql to datatable>

mysql서버에서 쿼리로 데이터를 가져와 datatable로 리턴

Public Function Mysql_to_datatable(ByRef sql As String, Optional ByRef connstring As String = "Server=10.169.88.40;Uid=rs_user;Pwd=lge1234;Database=td_defect") As DataTable
    Dim dt As New System.Data.DataTable
    '//Dim connstring As String = MyBase._connectionString & MyBase._schemaSet
    Using cn As New MySql.Data.MySqlClient.MySqlConnection With {.ConnectionString = connstring}
        Using cmd As New MySql.Data.MySqlClient.MySqlCommand With {.Connection = cn}
            cmd.CommandType = System.Data.CommandType.Text
            cmd.CommandText = sql
            cn.Open()
            Using da As New MySql.Data.MySqlClient.MySqlDataAdapter(cmd)
                Try
                    da.Fill(dt)
                    Debug.Print("[Query result] > " & "Successfull")
                Catch ex As Exception
                    Debug.Print(ex.Message, "devil castle", 0, 64)
                End Try
            End Using
            cn.Close()
        End Using
    End Using

    Return dt

End Function

<쿼리를 날려 임의 하나의 값을 string형으로 리턴받기>

주로 급하게 쿼리 내용 하나 가져올 때 사용 함.

Public Function GetQueryResult(ByRef sql As String, Optional ByRef connstring As String = "Server=10.169.88.40;Uid=rs_user;Pwd=lge1234;Database=td_defect") As String
    Dim result As String = Nothing
    Using cn As New MySql.Data.MySqlClient.MySqlConnection With {.ConnectionString = connstring}
        Using cmd As New MySql.Data.MySqlClient.MySqlCommand With {.Connection = cn}
            cmd.CommandType = System.Data.CommandType.Text
            cmd.CommandText = sql
            cn.Open()
            Dim dr As MySql.Data.MySqlClient.MySqlDataReader = cmd.ExecuteReader
            If dr.HasRows Then
                dr.Read()
                result = dr.GetString(0)
                Debug.Print("[Query result] > " & result)
            Else
                result = Nothing
            End If
            cn.Close()
        End Using
    End Using
    Return result
End Function

<Access로 된 DB에서 값을 가져와 Datatable로 변환>

Private vConn As System.Data.OleDb.OleDbConnection
Private DS As DataSet
Private myCmd As System.Data.OleDb.OleDbDataAdapter
'// connstring : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c://abcd.accdb;Jet OLEDB:Database Password = a1234;"
Public Function Access_to_datatable(ByRef connstring As String, ByRef sql As String) As DataTable
    Dim dt As New System.Data.DataTable
    Using cn As New System.Data.OleDb.OleDbConnection With {.ConnectionString = connstring}
        Using cmd As New System.Data.OleDb.OleDbCommand With {.Connection = cn}
            cmd.CommandType = System.Data.CommandType.Text
            cmd.CommandText = sql
            cn.Open()
            Using da As New System.Data.OleDb.OleDbDataAdapter(cmd)
                Try
                    da.Fill(dt)
                Catch ex As Exception
                End Try
            End Using
            cn.Close()
        End Using
    End Using
    Return dt
End Function

<엑셀 Excel Data를 가져와 Datatable에 담음>

Private _Provider As String = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source="
Private _Properties As String = ";Extended Properties=""Excel 12.0;HDR=YES;"""
'// path : c://test.xlsx
'// Sheet : "Sheet1" or "Sheet1$B3:Z1000"
Public Function Excel_to_datatable(ByRef path As String, ByRef Sheet As String, ByRef sql As String) As DataTable
    Dim connstring As String = String.Format("{0}{1}{2}", _Provider, path, _Properties)

    vConn = New OleDb.OleDbConnection(connstring)

    Try
        vConn.Open()
    Catch ex As Exception
        Debugging.Show(ex.Message)
    End Try

    '# Connection 
    Dim dt As DataTable = New DataTable

    '# DB 연결하여 실행
    myCmd = New System.Data.OleDb.OleDbDataAdapter("Select * FROM [" & Sheet & "$A1:P1000]", vConn)

    Try
        DS = New DataSet
        myCmd.Fill(DS)      '# DataSet에 엑셀에 있는 내용 모두 담음(조회 된 Query)
        dt = DS.Tables(0)
    Catch ex As Exception
        Debugging.Show(ex.Message)
    End Try

    Return If(dt Is Nothing, Nothing, dt)

End Function