CRUD (CREATE, READ, UPDATE, DELETE) merupakan bagian yang tidak mungkin terpisahkan dari pemrograman, apalagi jika program yang digunakan sangat tergantung kepada database. berikut adalah module CRUD VB.NET dangan database MYSQL
Imports MySql.Data.MySqlClient
Module modCrud
''declaring a string
Public result As String
Public cmd As New MySqlCommand
Public cmd2 As New MySqlCommand
Public rdr As MySqlDataReader
Public rdr2 As MySqlDataReader
Public rdt As DataTable
'INSERT
Public Sub cInsert(ByVal strSql As String)
Try
With cmd2
.Connection = conn
.CommandTimeout = 0
.CommandText = strSql
'execute the data
result = cmd2.ExecuteNonQuery
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
'SELECT
Public Sub cSelect(ByVal strSql As String)
Try
With cmd
.Connection = conn
.CommandText = strSql
.CommandTimeout = 0
rdr = cmd.ExecuteReader
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
'UPDATE
Public Sub cUpdate(ByVal strSql As String)
Try
With cmd
.Connection = conn
.CommandText = strSql
.CommandTimeout = 0
result = cmd.ExecuteNonQuery
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
'DELETE
Public Sub cDelete(ByVal strSql As String)
Try
With cmd
.Connection = conn
.CommandText = strSql
.CommandTimeout = 0
result = cmd.ExecuteNonQuery
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Using cmd As New MySqlCommand(strSql, conn)
cmd.CommandType = CommandType.Text
Using sda As New MySqlDataAdapter(cmd)
Using dt As New DataTable()
sda.Fill(dt)
gv.DataSource = dt
End Using
End Using
End Using
End Sub
'SELECT DATA untuk Listview1
Public Sub PopDataLv(ByVal strSql As String, Lv As ListView)
Using cmd As New MySqlCommand(strSql, conn)
cmd.CommandType = CommandType.Text
Using sda As New MySqlDataAdapter(cmd)
Using dt As New DataTable()
sda.Fill(dt)
Dim itemctr As Integer
For itemctr = 0 To dt.Rows.Count - 1
Lv.Items.Add(dt.Rows(itemctr)(1))
Next
End Using
End Using
End Using
End Sub
'SELECT DATA untuk Listview2
Sub FillListView(ByVal strSql As String, Lv As ListView)
Using cmd As New MySqlCommand(strSql, conn)
cmd.CommandType = CommandType.Text
Using sda As New MySqlDataAdapter(cmd)
Using ds As New DataSet()
sda.Fill(ds)
Dim c As DataColumn
For Each c In ds.Tables(0).Columns
'adding names of columns as Listview columns
Dim h As New ColumnHeader
h.Text = c.ColumnName
Lv.Columns.Add(h)
Next
Dim dt As DataTable = ds.Tables(0)
Dim str(ds.Tables(0).Columns.Count) As String
'adding Datarows as listview Grids
Dim rr As DataRow
For Each rr In dt.Rows
For col As Integer = 0 To ds.Tables(0).Columns.Count - 1
str(col) = rr(col).ToString()
Next
Dim ii As New ListViewItem(str)
Lv.Items.Add(ii)
'showing the number of records still added
Next
End Using
End Using
End Using
End Sub
End Module
==========================================
Untuk penggunaannya tinggal panggil fungsi tersebut, contoh
cSELECT("SELECT * FROM data)
Do While rdr.read()
txtData1.text = rdr.item("data1")
txtData2.text = rdr.item("data2")
loop
rdr.close()
==========================================
0 comments:
Posting Komentar