Question
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
Solution Preview
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.
1. Name four features or tasks of a DBMS. Provide some examples on how a database supports data integrity?a. Data Dictionary Management, Data Storage Management, Security Management, Data Integrity Management, Database Access Languages and Application Programming Interfaces.
b. Entity integrity is supported by primary key, such that each entry has unique representer. Referential integrity is maintained usually by foreign key, i.e. for each relation, referencing object is uniquely determined
2. What would we use a parameterized SQL statement? What information must be provided for each parameter?
a. (meaning why would we use) To unify the statements and prevent some attacks like SQL injection. And for each parameter one should provide the real value (i.e. data)
3. How is a transaction created? Which classes are involved? What methods can be invoked at the conclusion of a transaction?
a. By invoking a COMMIT command after series of SQL commands. After transaction is commited, one can call ROLLBACK, SAVEPOINT or SET NAME...
By purchasing this solution you'll be able to access the following files:
Solution.docx.