ASP.NET ADO.NET objects SQL DataAdapter DataReader Dataset
1. The ADO.NET objects (classes) …
A. Base class is System.Data
B. DataSet Object = to work with the data (once we have it).
1. Independent from the connection
a) System.Data.DataSet
C. Objects to access (connect to) data sources (ie databases)
1. Generic Namespace: ODBC
a) System.Data.OleDB
2. Specific Namespace: SQL
a) System.Data.SqlClient
b) Consider adding to imports to save tying.
3. Each namespace has it’s own DataReaders and DataAdapters
2. The Objects…the Data
A. DataSet. Holds a mini database made up of DataTables in XML
1. Can be manipulated and updated just like a full database
2. Independent of the connection…a database connection is just one (very common) source of putting data into a dataset
3. DataSet is one very long XML string.
4. DataTable: Has datarow (rows) objects and DataColumn objects (columns)
a) Can add/modify one row at a time (think record update)
b) Or use the .Fill method of the DataAdapter to populate with records from a datasource…
5. DataView: to sort and filter DataTables
a) You do not sort a DataTable…you sort the dataview
3. The Objects…the connection (get data)
A. Data source specific objects…used to talk to the backend
B. Connection: Your link to the backend
C. Command: (Used with DataReader); Caches data access information and used pass parameters to stored procedures commands to the backend
D. DataAdapter:
1. Adapters are used to exchange data between a data source and a dataset
2. Provides one table to the DataSet using fill method and then disconnects
E. DataReader: Forwardonly readonly (fire hose) connection in the most efficient manner. Effective method to fill list boxes
1. DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on).
2. Creation: Dim dr, cn, cm
a) Need select and connect strings
b) Cn = new connection(connect string)
c) Cn.open
d) cm = New cm(strSQL, cn)
e) dr = cm.ExecuteReader
4. They are disconnected from the backend
A. Faster…
B. Scalability: disconnected…no locks
C. Interoperability: XML with other platforms or other datasources
1. XML can be both the input and output.
D. Eaiser! Datasets come with all the schema built in.
5. Manually creating a Dataset
A. A few thoughts
1. Dataset holds tables
2. Tables have rows and columns
3. Columns have Names & data types
4. Dim a new table
5. Create a new column and set properties
6. Add the column to the Table columns collection
7. Repeat for each column
8. Create a new row dt.NewRow
9. Add data to the row columns
10. Add the row to the rows collection of the table.
11. Add table to the Dataset
12. Bind
B. Where do you find the code to create dataset manually
1. Look in help under DataTable Class
C. Where to get the datatype listing
1. Look for help under Data Type Summary
6. Sample Code
A. SQL Data Adapter fills a dataset
Dim da As System.Data.SqlClient.SqlDataAdapter
Dim ds As New DataSet()
Dim rw As DataRow
Dim strSQL As String = "Select CompanyName, City from Customers"
'this is a sqlclient connect string
Dim strCon As String = "Server=Surf7\VSdotNet;Database=Northwind;User ID=sa;Password=password"
da = New SqlClient.SqlDataAdapter(strSQL, strCon)
da.Fill(ds)
For Each rw In ds.Tables(0).Rows
lstDemo.Items.Add(rw.Item("CompanyName").ToString)
Next
ds = Nothing
da = Nothing
rw = Nothing
End Sub
Access data directly from command object and data reader.
Private Sub btnDataReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataReader.Click
Dim strSQL As String = "Select CompanyName from Customers"
Dim strCon As String = "Server=Surf7\VSdotNet;Database=Northwind;User ID=sa;Password=password"
Dim cn As New SqlClient.SqlConnection(strCon)
Dim cm As SqlClient.SqlCommand
Dim dr As SqlClient.SqlDataReader
'open the connection
cn.Open()
'create the command object
cm = New SqlClient.SqlCommand(strSQL, cn)
'create a datareader and close connection when done with it.
dr = cm.ExecuteReader(CommandBehavior.CloseConnection)
Do While dr.Read
lstDemo.Items.Add(dr.Item("CompanyName").ToString)
'see help on SqlDataReader Class
Loop
dr.Close()
dr = Nothing
cm = Nothing
cn = Nothing
A. Base class is System.Data
B. DataSet Object = to work with the data (once we have it).
1. Independent from the connection
a) System.Data.DataSet
C. Objects to access (connect to) data sources (ie databases)
1. Generic Namespace: ODBC
a) System.Data.OleDB
2. Specific Namespace: SQL
a) System.Data.SqlClient
b) Consider adding to imports to save tying.
3. Each namespace has it’s own DataReaders and DataAdapters
2. The Objects…the Data
A. DataSet. Holds a mini database made up of DataTables in XML
1. Can be manipulated and updated just like a full database
2. Independent of the connection…a database connection is just one (very common) source of putting data into a dataset
3. DataSet is one very long XML string.
4. DataTable: Has datarow (rows) objects and DataColumn objects (columns)
a) Can add/modify one row at a time (think record update)
b) Or use the .Fill method of the DataAdapter to populate with records from a datasource…
5. DataView: to sort and filter DataTables
a) You do not sort a DataTable…you sort the dataview
3. The Objects…the connection (get data)
A. Data source specific objects…used to talk to the backend
B. Connection: Your link to the backend
C. Command: (Used with DataReader); Caches data access information and used pass parameters to stored procedures commands to the backend
D. DataAdapter:
1. Adapters are used to exchange data between a data source and a dataset
2. Provides one table to the DataSet using fill method and then disconnects
E. DataReader: Forwardonly readonly (fire hose) connection in the most efficient manner. Effective method to fill list boxes
1. DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on).
2. Creation: Dim dr, cn, cm
a) Need select and connect strings
b) Cn = new connection(connect string)
c) Cn.open
d) cm = New cm(strSQL, cn)
e) dr = cm.ExecuteReader
4. They are disconnected from the backend
A. Faster…
B. Scalability: disconnected…no locks
C. Interoperability: XML with other platforms or other datasources
1. XML can be both the input and output.
D. Eaiser! Datasets come with all the schema built in.
5. Manually creating a Dataset
A. A few thoughts
1. Dataset holds tables
2. Tables have rows and columns
3. Columns have Names & data types
4. Dim a new table
5. Create a new column and set properties
6. Add the column to the Table columns collection
7. Repeat for each column
8. Create a new row dt.NewRow
9. Add data to the row columns
10. Add the row to the rows collection of the table.
11. Add table to the Dataset
12. Bind
B. Where do you find the code to create dataset manually
1. Look in help under DataTable Class
C. Where to get the datatype listing
1. Look for help under Data Type Summary
6. Sample Code
A. SQL Data Adapter fills a dataset
Dim da As System.Data.SqlClient.SqlDataAdapter
Dim ds As New DataSet()
Dim rw As DataRow
Dim strSQL As String = "Select CompanyName, City from Customers"
'this is a sqlclient connect string
Dim strCon As String = "Server=Surf7\VSdotNet;Database=Northwind;User ID=sa;Password=password"
da = New SqlClient.SqlDataAdapter(strSQL, strCon)
da.Fill(ds)
For Each rw In ds.Tables(0).Rows
lstDemo.Items.Add(rw.Item("CompanyName").ToString)
Next
ds = Nothing
da = Nothing
rw = Nothing
End Sub
Access data directly from command object and data reader.
Private Sub btnDataReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataReader.Click
Dim strSQL As String = "Select CompanyName from Customers"
Dim strCon As String = "Server=Surf7\VSdotNet;Database=Northwind;User ID=sa;Password=password"
Dim cn As New SqlClient.SqlConnection(strCon)
Dim cm As SqlClient.SqlCommand
Dim dr As SqlClient.SqlDataReader
'open the connection
cn.Open()
'create the command object
cm = New SqlClient.SqlCommand(strSQL, cn)
'create a datareader and close connection when done with it.
dr = cm.ExecuteReader(CommandBehavior.CloseConnection)
Do While dr.Read
lstDemo.Items.Add(dr.Item("CompanyName").ToString)
'see help on SqlDataReader Class
Loop
dr.Close()
dr = Nothing
cm = Nothing
cn = Nothing
posted by MJ at
10:34 PM
<< Home