1 / 22

ADO.NET Part 2

ADO.NET Part 2. Overview. Introduction to the DataGridView Control. It’s a two-dimensional grid containing rows and columns Its use in unbound mode was mentioned earlier in the course Now, we will use it in bound mode. The DataGridView Control (Capabilities).

makya
Download Presentation

ADO.NET Part 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADO.NET Part 2

  2. Overview

  3. Introduction to the DataGridView Control • It’s a two-dimensional grid containing rows and columns • Its use in unbound mode was mentioned earlier in the course • Now, we will use it in bound mode

  4. The DataGridView Control (Capabilities) • Displays rows from a single table (DataTable) or view • Allows editing (add, change, delete) • A rich set of events for error handling • A rich set of properties and objects for formatting

  5. The DataGridView Control (Binding) • Set the DataSource and DataMember properties • These can be • A DataSet (DataSource) and DataTable (DataMember) • A BindingSource (DataSource – the DataMember is not set)

  6. The DataGridView Control (Formatting) • Much can be done through the user interface

  7. The DataGridView Control(Columns Editor)

  8. DataGridView(Row Events) • In all, there are about 100 different events • Only selected events are of interest to us • RowEnter fires as a row gets input focus • RowLeave fires as a row loses focus • RowValidating fires after RowLeave and provides the chance for validation • This event can be cancelled

  9. DataGridView(Row Events 2) • Event arguments are complex types • DataGridViewCellEventArgs • RowIndex contains the 0-based index of the row • ColIndex contains the 0-based index of the column • DataGridViewCellCancelEventArgs • Set Cancel to true to cancel the subsequent events

  10. DataGridView(Row Events 3) • See tblLocationDataGridView event handlers in IS389ADOPart 2

  11. DataGridView(Cell Events 1) • CellEnter fires when a cell gets focus • CellBeginEdit fires when editing begins • CellEndEdit fires when editing ends • CellLeave fires after end edit when cell loses focus

  12. DataError Event • Remember that in this context, the DataGridView is bound to a data source • Trying to store type invalid values in the underlying DataTables will throw exceptions • Handle this event to handle those exceptions

  13. Using the OleDbCommand Object (ExecuteScalar) • Remember the ExecuteScalar method returns a single value • Use to get a max ID value

  14. ExecuteScalar (Example) • Get Max of field fldBoreHoleID in tblBoreHoleData System.Data.OleDb.OleDbCommandoCmd = new System.Data.OleDb.OleDbCommand(); oCmd.CommandType = CommandType.Text; oCmd.CommandText = "SELECT MAX(fldBoreHoleID) FROM tblBoreholeData"; oCmd.Connection = tblBoreholeDataTableAdapter.Connection; oCmd.Connection.Open(); inti; i = (int) oCmd.ExecuteScalar(); oCmd.Connection.Close();

  15. Using the OleDbCommand Object (ExecuteNonQuery) • Use to Execute INSERT, UPDATE, or DELETE commands that do not return rows • Example 1: System.Data.OleDb.OleDbCommandoCmd = new System.Data.OleDb.OleDbCommand(); oCmd.CommandType = CommandType.Text; oCmd.CommandText = "INSERT INTO tblCasIndex (fldCASIndexNumber, fldChemicalName, fldCASRegNo, fldCarcinogenic) VALUES (123,'abc','def',false)"; oCmd.Connection = tblBoreholeDataTableAdapter.Connection; oCmd.Connection.Open(); oCmd.ExecuteNonQuery(); oCmd.Connection.Close();

  16. Using the OleDbCommand Object with Parameters • Unlike the previous example, we generally don’t want to hardcode the data values • This is where parameters come in • Add one parameter for each dynamic field

  17. Creating a Parameter • An OleDbParameter has a • Name • Data type • Size • Mapping field • Example: System.Data.OleDb.OleDbParameter prop = new System.Data.OleDb.OleDbParameter("fldCASIndexNumber",System.Data.OleDb.OleDbType.Integer,0,"fldCASIndexNumber");

  18. Adding the Parameter • Each parameter must be added to the Parameters collection of the OleDbCommand • Example oCmd.Parameters.Add(prop);

  19. Filling in the Parameter Values • Set the Value property of a particular parameter oCmd.Parameters["fldCASIndexNumber"].Value = 234; oCmd.Parameters["fldChemicalName"].Value = "XXX"; oCmd.Parameters["fldCASRegNo"].Value = "YYY"; oCmd.Parameters["fldCarcinogenic"].Value = true;

  20. A Parameterized insert statement • The INSERT statement looks like this oCmd.CommandText = "INSERT INTO tblCasIndex (fldCASIndexNumber, fldChemicalName, fldCASRegNo, fldCarcinogenic) VALUES (?,?,?,?)";

  21. Using the OleDbDataReader • Place an SQL SELECT statement in the OleDbcommand • Call ExecuteReader to create the forward-only reader • Call Read to read each record • Then call the various Get method to get the value of each field

  22. Programmatically Selecting Records • See Example

More Related