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

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