CONCEPTUAL QUESTIONS

1. Name four features or tasks of a DBMS. Provide some examples on how a database supports data integrity?
2. What would we use a parameterized SQL statement? What information must be provided for each parameter?
3. How is a transaction created? Which classes are involved? What methods can be invoked at the conclusion of a transaction?
4. What is the difference between a data table row item and a DataGridView cell?
5. What is the difference between transaction processing and concurrency checking?
6. What is the difference between Optimistic and Pessimistic concurrency? What are the techniques used for each?

CLASS PROPERTY QUESTION

7. What is the key property of the Connection Class. What information is held in that property?
8. What property of the command class indicates whether a Stored Procedure should be used?
9. What property of the data table row class indicates whether the row has been added, deleted or modified?
10. What property of DataGridView enables us to choose only the columns we want?
11. Which property of the data adapter determines if an error is generated for a suspected concurrency problem?

CLASS CODE QUESTIONS

12. Why do we clear a data table within an event handler that would fill the data table?
13. What is the purpose of this statement:DataGridView1.DataSource = mydt
14. What class has a ContinueUpdateonErrror method? What does it do?

Modify the code provided to:
a) Delete a record
b) Enable the program to read uncommitted data from another instance of the same application that has initiated a transaction.

Imports System.Data.sqlclient
Public Class Form1
Dim myconn As SqlConnection
Dim mydt As New DataTable
Dim connstring As String = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Temp\WindowsApplication3(1)(1)\WindowsApplication3(1)\WindowsApplication3\WindowsApplication3\WindowsApplication3\karate_Data.MDF;Integrated Security=True;Connect Timeout=30"

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
   myconn = New SqlConnection
   myconn.ConnectionString = connstring
   myconn.Open()
   Dim myadapter As New SqlDataAdapter
   Dim mycmd As New SqlCommand
   myadapter.SelectCommand = mycmd
   mycmd.CommandText = "Select * from Members"
   mycmd.Connection = myconn
   mydt.Clear()
   myadapter.Fill(mydt)
   DataGridView1.AutoGenerateColumns = False
   DataGridView1.DataSource = mydt
   For i As Integer = 0 To DataGridView1.RowCount - 1
         DataGridView1.Rows(i).Cells(3).Value = DataGridView1.Rows(i).Cells(1).Value + " " + DataGridView1.Rows(i).Cells(2).Value
   Next
End Sub

Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
   myconn = New SqlConnection
   myconn.ConnectionString = connstring
   myconn.Open()

   Dim myadapter As New SqlDataAdapter
   Dim mycmd As New SqlCommand
   myadapter.SelectCommand = mycmd
   mycmd.CommandText = "Select * from Members where First_Name = @firstname and Last_Name = @LastName "
   mycmd.Parameters.Add("@Firstname", SqlDbType.VarChar)
   mycmd.Parameters("@Firstname").Value = TextBox1.Text
   mycmd.Parameters.Add("@Lastname", SqlDbType.VarChar)
   mycmd.Parameters("@Lastname").Value = TextBox2.Text
   mycmd.Connection = myconn

   Dim mydt As New DataTable
   myadapter.Fill(mydt)

   If mydt.Rows.Count = 0 Then
         DataGridView1.Visible = False
         MessageBox.Show("No records found")
   Else
         DataGridView1.Visible = True
         DataGridView1.DataSource = mydt
   End If

End Sub

Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick

End Sub

Private Sub DataGridView1_CellValidated(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
   Dim row As Integer
   row = e.RowIndex
   DataGridView1.Rows(row).Cells(3).Value = DataGridView1.Rows(row).Cells(1).Value + " " + DataGridView1.Rows(row).Cells(2).Value

End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
   Dim myrow As DataGridViewRow
   If DataGridView1.SelectedRows.Count = 0 Then
         MsgBox("select a row")
         Return
   End If
   myrow = DataGridView1.SelectedRows(0)

   myconn = New SqlConnection
   myconn.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Temp\WindowsApplication3(1)\WindowsApplication3\WindowsApplication3\WindowsApplication3\karate_Data.MDF;Integrated Security=True;Connect Timeout=30"
   myconn.Open()

   Dim mycmd As New SqlCommand
   mycmd.CommandText = "Update Members set Last_Name = @newname where ID= @ID "
   mycmd.Parameters.Add("@newname", SqlDbType.VarChar)
   mycmd.Parameters("@newname").Value = Me.NewName.Text
   mycmd.Parameters.Add("@ID", SqlDbType.SmallInt)
   mycmd.Parameters("@ID").Value = mydt.Rows(myrow.Index).Item(0)
   mycmd.Connection = myconn

   Dim numrows As Integer
   numrows = mycmd.ExecuteNonQuery
   MsgBox(numrows)

End Sub

Private Sub btnSaveAll_Click(sender As Object, e As EventArgs) Handles btnSaveAll.Click
   myconn = New SqlConnection
   myconn.ConnectionString = connstring
   myconn.Open()

   Dim myadapter As New SqlDataAdapter

   mydt.Columns(0).ColumnName = "ID"
   mydt.Columns(1).ColumnName = "dt_Last_Name"
   mydt.Columns(2).ColumnName = "dt_First_Name"
   mydt.Columns(5).ColumnName = "dt_TimeStamp"

   Dim updatecmd As New SqlCommand
   myadapter.UpdateCommand = updatecmd
   updatecmd.Connection = myconn
   updatecmd.CommandText = "Update Members set Last_Name = @newname where ID= @ID and UpdateTimeStamp = @olddatestamp "
   updatecmd.Parameters.Add("@newname", SqlDbType.VarChar, 40, "dt_Last_Name")

   Dim myparm As New SqlClient.SqlParameter
   myparm.ParameterName = "@olddatestamp"
   myparm.SqlDbType = SqlDbType.Timestamp
   myparm.SourceColumn = "dt_TimeStamp"

   updatecmd.Parameters.Add(myparm)

   updatecmd.Parameters.Add("@ID", SqlDbType.SmallInt, 4, "ID")

   Dim deletecmd As New SqlCommand
   myadapter.DeleteCommand = deletecmd
   deletecmd.Connection = myconn
   deletecmd.CommandText = "Delete Members where ID= @ID "
   deletecmd.Parameters.Add("@ID", SqlDbType.SmallInt, 4, "ID")

   Dim insertcmd As New SqlCommand
   myadapter.InsertCommand = insertcmd
   insertcmd.Connection = myconn
   insertcmd.CommandText = "Insert Into Members (ID,Last_Name,First_name) Values (@ID, @Last_Name, @First_Name)"
   insertcmd.Parameters.Add("@Last_Name", SqlDbType.VarChar, 40, "dt_Last_Name")
   insertcmd.Parameters.Add("@First_Name", SqlDbType.VarChar, 40, "dt_First_Name")
   insertcmd.Parameters.Add("@ID", SqlDbType.SmallInt, 4, "ID")

   Dim numrows As Integer

   myadapter.ContinueUpdateOnError = True

   numrows = myadapter.Update(mydt)

   MsgBox(numrows)


End Sub

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click

   mydt.RejectChanges()

End Sub
End Class

CONCEPTUAL QUESTIONS 1. Name four features or tasks of a DBMS....