Contact
Subscribe
October 31, 2008 
The following notes were taken during a lecture in COMP 241 .NET Web Applications at Camosun College by Rob Thorndyke. These notes are here mainly to help me study, but if you can find some guidance through them then all the better!
Fundamental Objects
- DbConnection
- DBCommand
- DBDataReader
- DbDataAdapter
Connection Types
- SQL Server
- ODBC
- OLEDB (Old SQL Server)
- Access
- XML
- SqlDbConnection
- OleDbConnection
- OracleDbConnection
- ODBCDbConnection
Example Session
DbConnection connection = new SqlConnection(connectionString); DbCommand cmd = connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM table_name"; connection.Open(); DbDataReader reader = cmd.ExecuteReader(); // Returns a table of data // Do something with that data // ------------- connection.Close(); // *NOTE: After closing the connection, the data // in reader will no longer be available!
Command Examples
cmd.CommandText = "UPDATE table1 SET value = 30 WHERE value = 10"; connection.Open(); int count = cmd.ExecuteNonQuery(); connection.Close(); // ----------------------------- cmd.CommandText = "SELECT count(*) FROM user"; connection.Open(); int num_users = (int) cmd.ExecuteScalar(); // Must be cast connection.Close();
Data Reader
- Forward-only, read-only, cursor
- Highly efficient; use when you only need read-only data, that’s processed only once, in order
// Iterating through the reader DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int value = (int) reader["value"]; string name = (string) reader["name"]; } // Putting the data into a DataTable DbDataReader reader = cmd.ExecuteReader(); DataTable table = new DataTable(); table.Load(reader, LoadOption.Upsert); connection.Close(); GridView1.DataSource = table; //Data Bound Control GridView1.DataBind();
Load Options
(http://msdn.microsoft.com/en-us/library/system.data.loadoption.aspx)
| Member name | Description |
| OverwriteChanges | The incoming values for this row will be written to both the current value and the original value versions of the data for each column. |
| PreserveChanges | The incoming values for this row will be written to the original value version of each column. The current version of the data in each column will not be changed. [This] is the default. |
| Upsert | The incoming values for this row will be written to the current version of each column. The original version of each column’s data will not be changed. |
Data Adapters
- Allows automatic writeback
- Has 3 “update” properties
- InsertCommand
- DeleteCommand
- UpdateCommand
- These are all or nothing! You must implement all 3
- Formatting these things is not trivial
DbCommand cmd = connection.CreateCommand(); cmd.CommandText = "SELECT * FROM board"; // Create our data adapter DbDataAdapter adapter = new DataAdapter(cmd); adapter.Fill(ds, "table"); // ds is a DataSet!
- Formatting:
- Use a GUI wizard – Popped up when the DataAdapter is dropped on the form!
- Use a Builder:
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
No Comments 
Dan Beland is a web developer working out of Victoria BC Canada. He is currently finishing his diploma in Computer Systems Technology at Camosun College.