cara menampilkan struktur tabel dan export data ke excel
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
Dim Conn As SqlConnection
Dim DA As SqlDataAdapter
Dim DS As DataSet
Dim CMD As SqlCommand
Dim DR As SqlDataReader
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.Items.Add("USER-PC\SQLEXPRESS") 'silakan ganti dengan nama server anda..
End Sub
Sub KoneksiMaster()
CONN = New SqlConnection("Data Source=" & ComboBox1.Text & ";Database=Master;integrated security=SSPI;")
CONN.Open()
CONN.Close()
End Sub
Sub Koneksiwindows()
CONN = New SqlConnection("Data Source=" & Trim(ComboBox1.Text) & ";initial catalog=" & listbox1.Text & ";integrated security=true;")
CONN.Open()
End Sub
Sub KoneksiServer()
Try
Conn = New SqlConnection("Data Source=" & ComboBox1.Text & ";initial catalog=" & ListBox1.Text & ";User Id=" & TextBox1.Text & ";password=" & TextBox2.Text & "")
Conn.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Public Sub Koneksi()
Conn = New SqlConnection("data source='" & ComboBox1.Text & "';initial catalog='" & ListBox1.Text & ";integrated security=true")
Conn.Open()
End Sub
Sub CariDatabase()
Conn = New SqlConnection("Data Source=" & Trim(ComboBox1.Text) & ";Database=Master;integrated security=SSPI;")
CONN.Open()
ListBox1.Items.Clear()
CMD = New SqlCommand("select * from sysdatabases", CONN)
DR = CMD.ExecuteReader
While DR.Read
ListBox1.Items.Add(DR(0))
End While
DR.Close()
End Sub
Sub CaraLogin()
If ComboBox2.Text = "Windows Authentication" Then
TextBox1.Enabled = False : TextBox1.Clear()
TextBox2.Enabled = False : TextBox2.Clear()
Else
TextBox1.Enabled = True
TextBox2.Enabled = True
End If
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
ComboBox2.Items.Clear()
ComboBox2.Items.Add("Windows Authentication")
ComboBox2.Items.Add("SQL Server Authentication")
TextBox1.Clear()
TextBox2.Clear()
ListBox1.Text = ""
ListBox1.Items.Clear()
Call CaraLogin()
End Sub
Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Call Koneksiwindows()
CMD = New SqlCommand("select TABLE_NAME from INFORMATION_SCHEMA.TABLES", Conn)
DR = CMD.ExecuteReader
ListBox2.Items.Clear()
Do While DR.Read
ListBox2.Items.Add(DR.Item(0))
Loop
End Sub
Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
Call CaraLogin()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If ComboBox2.Text = "Windows Authentication" Then
Call Koneksiwindows()
Else
Call KoneksiServer()
End If
Call CariDatabase()
End Sub
Private Sub ListBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox2.SelectedIndexChanged
If ComboBox2.Text = "Windows Authentication" Then
Call Koneksiwindows()
DA = New SqlDataAdapter("select * from " & ListBox2.Text & "", Conn)
DS = New DataSet
DA.Fill(DS)
DGV.DataSource = DS.Tables(0)
DGV.ReadOnly = True
Label1.Text = Application.StartupPath + "\" + ListBox2.Text + ".xls"
Else
Call KoneksiServer()
DA = New SqlDataAdapter("select * from " & ListBox2.Text & "", Conn)
DS = New DataSet
DA.Fill(DS)
DGV.DataSource = DS.Tables(0)
DGV.ReadOnly = True
Label1.Text = Application.StartupPath + "\" + ListBox2.Text + ".xls"
End If
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If ListBox2.Text = "" Then
MsgBox("pilih nama tabel di listbox sebelah kanan")
Exit Sub
End If
Dim dt As New DataTable()
For Each col As DataGridViewColumn In DGV.Columns
dt.Columns.Add(col.HeaderText, col.ValueType)
Next
Dim count As Integer = 0
For Each row As DataGridViewRow In DGV.Rows
If count < DGV.Rows.Count - 1 Then
dt.Rows.Add()
For Each cell As DataGridViewCell In row.Cells
dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString()
Next
End If
count += 1
Next
Dim wr As New StreamWriter(Application.StartupPath + "\" + ListBox2.Text + ".xls")
For i As Integer = 0 To dt.Columns.Count - 1
wr.Write(dt.Columns(i).ToString().ToUpper() & vbTab)
Next
wr.WriteLine()
For i As Integer = 0 To (dt.Rows.Count) - 1
For j As Integer = 0 To dt.Columns.Count - 1
If dt.Rows(i)(j) IsNot Nothing Then
wr.Write(Convert.ToString(dt.Rows(i)(j)) & vbTab)
Else
wr.Write(vbTab)
End If
Next
wr.WriteLine()
Next
wr.Close()
MsgBox("Data Exported Successfully")
If MessageBox.Show("open file...?", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
Try
System.Windows.Forms.Help.ShowHelp(Me, Label1.Text)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
End Sub
End Class