Se garantiza que las operaciones en una transacción ocurren todas, o no ocurren todas. Su programa también puede "revertir" una transacción para cancelarla.
En VB .NET, usted inicia una transacción llamando al método BeginTransaction de un objeto de conexión. Luego, crea objetos de comando para realizar las tareas, pasando a sus constructores el objeto de transacción. Ejecuta los comandos y llama al método Commit del objeto de transacción para que las acciones sean permanentes o llama a su método Rollback para cancelar las acciones de la transacción.
Fuente de texto: http://www.vb-helper.com/howto_net_db_transaction.html
A continuación, mostraré cómo hacer uso de las Transacciones en VB.NET, con un ejemplo simple usando SQLite como base de datos.Requisitos
- Microsoft Visual Studio (cualquier versión), yo utilicé Visual Studio 2012
Abra Visual Studio y haga clic en el menú Archivo / Nuevo / Proyecto y seleccione "Windows Forms Application". Necesitará 2 formularios para este ejemplo.El primer Formulario contendrá los siguientes componentes:
- 1 Panel
- 1 Datagridview
- 3 TextBox
- 1 MaskedTextBox
- 5 botones
- 4 etiqueta
El segundo formulario contendrá los siguientes componentes:
- 1 Datagridview
Código para el primer formulario
Imports Finisar.SQLite
Public Class Form1
Public DdConnection As Finisar.SQLite.SQLiteConnection
Public cmd As Finisar.SQLite.SQLiteCommand
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
DdConnection = New Finisar.SQLite.SQLiteConnection
DdConnection.ConnectionString = “Data Source=database.db3;Version=3;”
End Sub
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
DdConnection.Open()
Dim myDate = Format(CDate(txtBirthday.Text), “yyyy-MM-dd”)
Dim myTrans As SQLiteTransaction
myTrans = DdConnection.BeginTransaction()
Try
‘Inserting data
‘——————————————————————–
cmd = New SQLiteCommand(“insert into transaction_log(description,date) values(‘New record’,@MYDATE)”, DdConnection)
cmd.Parameters.Add(“@MYDATE”, DbType.Date).Value = Now
cmd.ExecuteNonQuery()
cmd = New SQLiteCommand(“insert into personal_data(name,birthday) “ & _
“values(‘” & txtName.Text & “‘,@MYDATE)”, DdConnection)
cmd.Parameters.Add(“@MYDATE”, DbType.Date).Value = myDate
cmd.ExecuteNonQuery()
‘———————————————————————–
cmd.Transaction = myTrans
myTrans.Commit()
DdConnection.Close()
SearchDataBase()
Catch ex As Exception
‘ If Error rollback
myTrans.Rollback()
DdConnection.Close()
MessageBox.Show(“Update fail!” & vbCrLf & ex.Message)
End Try
End Sub
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
If txtID.Text <> “0” Then
If MessageBox.Show(“Update this record?”, “AT”, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = vbYes Then
DdConnection.Open()
Dim myDate = Format(CDate(txtBirthday.Text), “yyyy-MM-dd”)
Dim myTrans As SQLiteTransaction
myTrans = DdConnection.BeginTransaction()
‘Inserting data
‘——————————————————————–
cmd = New SQLiteCommand(“insert into transaction_log(description,date) values(‘Updated record’,@MYDATE)”, DdConnection)
cmd.Parameters.Add(“@MYDATE”, DbType.Date).Value = Now
cmd.ExecuteNonQuery()
cmd = New SQLiteCommand(“update personal_data set name='” & txtName.Text & “‘, “ & _
“birthday=@MYDATE where id='” & txtID.Text & “‘”, DdConnection)
cmd.Parameters.Add(“@MYDATE”, DbType.Date).Value = myDate
cmd.ExecuteNonQuery()
‘———————————————————————–
cmd.Transaction = myTrans
Try
myTrans.Commit()
DdConnection.Close()
SearchDataBase()
Catch ex As Exception
‘ If Error rollback
myTrans.Rollback()
DdConnection.Close()
MessageBox.Show(“Update fail!” & vbCrLf & ex.Message)
End Try
End If
End If
End Sub
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
If MessageBox.Show(“Delete this record?”, “Atencion”, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = vbYes Then
DdConnection.Open()
Dim myTrans As SQLiteTransaction
myTrans = DdConnection.BeginTransaction()
cmd = New SQLiteCommand(“insert into transaction_log(description,date) values(‘Deleted record’,@MYDATE)”, DdConnection)
cmd.Parameters.Add(“@MYDATE”, DbType.Date).Value = Now
cmd.ExecuteNonQuery()
cmd = New SQLiteCommand(“delete from personal_data where id='” & txtID.Text & “‘”, DdConnection)
cmd.ExecuteNonQuery()
‘———————————————————————–
cmd.Transaction = myTrans
Try
myTrans.Commit()
DdConnection.Close()
SearchDataBase()
Catch ex As Exception
‘ If Error rollback
myTrans.Rollback()
DdConnection.Close()
MessageBox.Show(“Update fail!” & vbCrLf & ex.Message)
End Try
End If
End Sub
Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
CleanFields()
End Sub
Private Sub CleanFields()
txtID.Text = “0”
txtName.Clear()
txtBirthday.Text = “”
txtID.Focus()
End Sub
Private Sub txtSearch_TextChanged(sender As Object, e As EventArgs) Handles txtSearch.TextChanged
SearchDataBase()
End Sub
Private Sub SearchDataBase()
Try
If DdConnection.State = ConnectionState.Closed Then
DdConnection.Open()
End If
Dim da As SQLiteDataAdapter = New SQLiteDataAdapter(“select * from personal_data where name like’%” & txtSearch.Text & “%'”, DdConnection)
Dim ds As New DataSet
da.Fill(ds)
If ds.Tables(0).Rows.Count > 0 Then
DataGridView1.DataSource = ds.Tables(0)
Else
DataGridView1.DataSource = Nothing
End If
DdConnection.Close()
Catch ex As Exception
End Try
End Sub
Private Sub TypeText() Handles txtSearch.GotFocus
Dim obj As New TextBox
obj.Text = txtSearch.Text
If obj.Text = “<Type here to search…>” Then
obj.Text = “”
End If
txtSearch.Text = obj.Text
End Sub
Private Sub btnView_Click(sender As Object, e As EventArgs) Handles btnView.Click
TransactionLog.Show()
End Sub
Private Sub DataGridView1_CellEnter(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEnter
If DataGridView1.RowCount > 0 Then
txtID.Text = DataGridView1.CurrentRow.Cells(“ID”).Value.ToString
txtName.Text = DataGridView1.CurrentRow.Cells(“NAME”).Value.ToString
txtBirthday.Text = DataGridView1.CurrentRow.Cells(“BIRTHDAY”).Value.ToString
Else
txtID.Clear()
txtName.Clear()
txtBirthday.Text = “”
End If
End Sub
End Class
Código para el segundo formulario
Imports Finisar.SQLite
Public Class TransactionLog
Private Sub LogTransacciones_Load(sender As Object, e As EventArgs) Handles MyBase.Load
TransactionSearch()
End Sub
Private Sub TransactionSearch()
Try
If Form1.DdConnection.State = ConnectionState.Closed Then
Form1.DdConnection.Open()
End If
Dim comando As New SQLiteCommand(“select * from transaction_log where date between @MYDATE and @MYDATE1”, Form1.DdConnection)
comando.Parameters.Add(“@MYDATE”, DbType.Date).Value = Today.Date
comando.Parameters.Add(“@MYDATE1”, DbType.Date).Value = Today.Date.AddDays(1)
Dim da As New SQLiteDataAdapter(comando)
Dim ds As New DataSet
da.Fill(ds)
If ds.Tables(0).Rows.Count > 0 Then
DataGridView1.DataSource = ds.Tables(0)
Else
DataGridView1.DataSource = Nothing
End If
Form1.DdConnection.Close()
Catch ex As Exception
End Try
End Sub
End Class


0 comentarios:
Publicar un comentario