 | Sams Teach Yourself ASP.NET in 21 Days, Second Edition
By Chris Payne
| | Table of Contents | | | Appendix A. Answers to Quiz Questions |
Quiz | 1: | What are the two major parts of ADO.NET? | | A1: | The DataSet object and the managed providers. | | 2: | What do managed providers do? | | A1: | They provide the interface for communication between data stores and DataSets. The two managed providers for ADO.NET are SQL and ADO. | | 3: | What is disconnected data? | | A1: | A data representation, such a DataSet, that doesn't require a continuous database connection. | | 4: | Which of the following constructors isn't valid for the OleDbCommand object (line 4, 5, 6, 8, or 9)? dim strSQL as string = "SELECT * FROM tblUsers") dim Conn as new OleDbConnection("DSN=21DaysBanking") dim objCmd as new OleDbCommand() dim objCmd as new OleDbCommand(strSQL) dim objCmd as new OleDbCommand(strSQL, _Conn) dim objCmd as new OleDbCommand(Conn) dim objCmd as new OleDbCommand(strSQL, _ "DSN=21DaysBanking") | | A1: | Line 8 is an invalid constructor. | | 5: | What type of data model does an OleDbCommand object fill? | | A1: | An ADODataReader object. | | 6: | Does an OleDbCommand object need to be closed? | | A1: | No, only the OleDbDataReader and OleDbConnection object need to be closed explicitly. | | 7: | What are the five members of the OleDbDataAdapter object? | | A1: | UpdateCommand, InsertCommand, DeleteCommand, SelectCommand, and TableMappings. | | 8: | Which of the following constructors isn't valid for the OleDbDataAdapter object (line 5, 6, 7, 9, or 10)? dim strSQL as string = "SELECT * FROM tblUsers") dim Conn as new OleDbConnection("DSN=21DaysBanking") dim objADOCmd as new OleDbCommand(strSQL, Conn) dim objCmd as new OleDbDataAdapter() dim objCmd as new OleDbDataAdapter(objADOCmd) dim objCmd as new OleDbDataAdapter(strSQL, _ Conn) dim objCmd as new OleDbDataAdapter(Conn) dim objCmd as new OleDbDataAdapter(strSQL, _ "DSN=21DaysBanking") | | A1: | Line 9 is the invalid constructor. |
Exercise | Q1: | Modify the example application you built today to use the OleDbDataAdapter and DataSet objects instead of OleDbCommand and OleDbDataReader. If you use the OleDbCommandBuilder as well, don't forget the limitation about the initial select statement matching the columns in the DataSet that was described earlier today. | | A1: | The new code declaration block should read as follows: 1: <%@ Page Language="VB" %> 2: <%@ Import Namespace="System.Data" %> 3: <%@ Import Namespace="System.Data.OleDb" %> 4: 5: <script runat="server"> 6: 'declare connection 7: dim Conn as new OleDbConnection( _ 8: "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 9: "Data Source=C:\ASPNET\data\banking.mdb") 10: dim ds as new DataSet("tblUsers") 11: 12: sub Page_Load(Sender as Object, e as EventArgs) 13: if Not Page.IsPostBack then 14: FillDataGrid() 15: end if 16: end sub 17: 18: sub GetData() 19: 'open connection 20: dim objCmd as new OleDbDataAdapter _ 21: ("select * from tblUsers", Conn) 22: 'fill dataset 23: objCmd.Fill(ds, "tblUsers") 24: 25: 'define primary key 26: dim keys() as DataColumn = {ds.Tables("tblUsers"). _ 27: Columns("UserID")} 28: ds.Tables("tblUsers").PrimaryKey = keys 29: ds.Tables("tblUsers").Columns("UserID").AutoIncrement = true 30: end sub 31: 32: sub Submit(Sender as Object, e as eventargs) 33: 'insert new data 34: dim i, j as integer 35: dim params(7) as string 36: dim strText, strSQL as string 37: dim blnGo as boolean = true 38: 39: j = 0 40: GetData() 41: 42: dim dr as DataRow = ds.Tables("tblUsers").NewRow() 43: for i = 0 to AddPanel.Controls.Count - 1 44: if AddPanel.controls(i).GetType Is GetType(TextBox) then 45: strText = Ctype(AddPanel.Controls(i), TextBox).Text 46: if strText <> "" then 47: dr(j) = strText 48: Ctype(AddPanel.Controls(i), TextBox).Text = "" 49: else 50: blnGo = false 51: lblMessage.Text = lblMessage.Text & "You forgot to " & _ 52: "enter a value for " & AddPanel.Controls(i).ID & "<p>" 53: lblMessage.Style("ForeColor") = "Red" 54: end if 55: j = j + 1 56: end if 57: next 58: 59: if not blnGo then 60: exit sub 61: end if 62: 63: ds.Tables("tblUsers").Rows.Add(dr) 64: 65: ExecuteStatement() 66: 67: FillDataGrid() 68: end sub 69: 70: sub dgData_Edit(Sender as Object, e as DataGridCommandEventArgs) 71: FillDataGrid(e.Item.ItemIndex) 72: end sub 73: 74: sub dgData_Delete(Sender as Object, e as DataGridCommandEventArgs) 75: GetData() 76: 77: ds.Tables("tblUsers").Rows(e.Item.ItemIndex).Delete 78: 79: ExecuteStatement() 80: 81: FillDataGrid() 82: end sub 83: 84: sub dgData_Update(Sender as Object, e as DataGridCommandEventArgs) 85: if UpdateDataStore then 86: FillDataGrid(-1) 87: end if 88: end sub 89: 90: sub dgData_Cancel(Sender as Object, e as DataGridCommandEventArgs) 91: FillDataGrid(-1) 92: end sub 93: 94: sub dgData_PageIndexChanged(Sender as Object, e as DataGridPageChangedEventArgs) 95: dgData.DataBind() 96: end sub 97: 98: function UpdateDataStore(e as DataGridCommandEventArgs) _ 99: as boolean 100: 101: dim i,j as integer 102: dim params(7) as string 103: dim strText as string 104: dim blnGo as boolean = true 105: 106: j = 0 107: GetData() 108: 109: for i = 1 to e.Item.Cells.Count - 3 110: strText = Ctype(e.Item.Cells(i).Controls(0), TextBox).Text 111: if strText <> "" then 112: ds.Tables("tblUsers").Rows(e.Item.ItemIndex)(j) = strText 113: j = j + 1 114: else 115: blnGo = false 116: lblMessage.Text = lblMessage.Text & "You forgot to " & _ 117: "enter a value<p>" 118: end if 119: next 120: 121: if not blnGo then 122: return false 123: exit function 124: end if 125: 126: ExecuteStatement() 127: return blnGo 128: end function 129: 130: sub FillDataGrid(Optional EditIndex as integer=-1) 131: GetData() 132: 133: if not EditIndex.Equals(Nothing) then 134: dgData.EditItemIndex = EditIndex 135: end if 136: 137: 'select data view and bind to server control 138: dgData.DataSource = ds 139: dgData.DataMember = "tblUsers" 140: dgData.DataBind() 141: end sub 142: 143: function ExecuteStatement() 144: 'open connection 145: dim objCmd as new OleDbDataAdapter _ 146: ("select UserID, FirstName, LastName, Address, City, State, Zip, Phone  from tblUsers", Conn) 147: dim objAutoGen as New OleDbCommandBuilder(objCmd) 148: 149: try 150: objCmd.Update(ds, "tblUsers") 151: catch ex as Exception 152: lblMessage.Text = ex.Message 153: end try 154: 155: ds.Clear 156: end function 157: </script> |
|
|
