ASP.NET programmers Irvine California, DataSet DataTable

ASP.NET programmers Irvine California

1. Two types of objects
A. Connection Dependent
1. Connection, Command, DataAdapter, DataReader
B. Connection Independent
1. DataSet, DataTable,DataView, DataRow
2. Data Reader…a connection specific firehose to a consumer
A. There is no client side caching of data as in a DataSet
1. (it is implicitly created when you create a DataSet to fill the ds)
B. Fastest way to read data into your application
C. You do not create an instance of a DataReader
1. Instead use command object to create it.
D. Cm.ExecuteReader(CommandBehavior.CloseConnection)
1. You are now running in a connected state…use it and close it!
2. The optional argument closes the connection when you close the datareader
3. dr = cm.ExecuteReader(CommandBehavior.CloseConnection)
A. Problem. Firehose is a connection that has to be closed when done
1. But it’s difficult to determine when “done” is
2. The .CloseConnection behavior takes care of that for you.
4. Looping Code is easy
A. dr = cm.ExecuteReader …..creates the dr
B. Do While dr.read
1. dr.read…movesnext and returns T or F based on position
C. Loop
5. Reading the dr
A. dr("CompanyName").ToString, dr("SupplierID").ToString))
1. Slower but easier to read
B. dr.GetString(1), dr.GetInt32(0)))
1. Get… is specific to datatype…#’s are ordinal positions.
6. Fastest is binding to the dr
A. .DataSource = dr
B. .DataTextField = "CompanyName"
C. .DataValueField = "SupplierID"
D. .DataBind()
7. Note Error in book
A. Page 262….SupplierID is an Integer!
If SupplierID <> 0 Then
strSQL += " and SupplierID = " & SupplierID
End If

8. Concept of Wrappers…Wrapping up generic Data Access Code in a Class module
A. DataHandler Class
1. GetDataReader, GetDataSet

9. Listbox…made up of ListItem Objects
A. dim oItem as ListItem
B. oItem= New ListItem(label,value)
1. both are strings
C. List.Items.Add(oItem)…appends
D. List.Items.Insert(index,oitem)
E. Code to insert an initial selection to the dropdown list
1.
F. .Items.Insert(0, "<--Select a Product-->")
10. Setting relations between multiple tables in a Dataset
A. A relation is linking tables together on a common field called a key
B. Very common in the business world!
1. Orders-OrderDetail
2. Employees-WeeklyPayroll
3. Members-Donations
C. Possible to maintain within a dataset
11. Goal is to create a “datarelation” object and add to the Relations tables
A. Identity one column from each table as the linking columns
B. Dim and create two column objects from the table names and ID fields
1. dcParent =ds.Tables(ParentTable).Columns(ParentField).
C. Now we can create a relation object from the two columns
D. Dim a DataRelation create new relation
1. drn = New DataRelation(Name, dcParent, dcChild)
E. Add it to the Relations collection
1. ds.Relations.Add(drn)
12. Primary key is a column of unique keys, one for each record
A. In code you may need to define which column in the dataset is the primary key.
1. Seems strange but this is how you do it.
2. Create a one dimensional array of columns
a) dim dsc(0) as datacolumn
3. Grab the column from the table you want to be the primary key and stuff it into the column you just created
a) dcs(0) = ds.Tables(Name).Columns(name)
4. Set the primary key of table to the column you just created.
a) ds.Tables(Name).PrimaryKey = dcs
13. Putting it all together
A. Goal is to show all the Orders for one Employee
B. Create an empty clone table of the orders table
1. dt = ds.tables(name).clone
C. From the select list get the Parent (row)…ie the employee
1. drwEmp=ds.Tables(name).Rows.Find(primarykeyvalue)
2. note that we have to have a primary key set
3. We will use this row to find all it’s children
D. Loop through the collection of the parents children in the orders table
1. For each row in drwEmp.GetChildRows(“childtable”)
a) dt.ImportRow(row)
2. Next row
E. Bind the clone table to a grid.
1. .datasource = dt
2. .databind()