Active X Data Object Model ADO ADO NET
Active. X Data Object Model (ADO) ADO. NET 1
ADO Model Active. X Data Object Model (ADO) Open Database Connectivity (ODBC) Object Linking and Embedding Database (OLE-DB) Structured Query Language (SQL) 2
ADO. NET Core Objects 3
ADO. NET Connection Object Provides the connection to the database Requires a connection string that can be manually configured, or created in the Data Connections window of Visual Studio. NET Connection string Provider – name of the managed provider. The name of the SQL Server OLE-DB Provider is Provider=SQLOLEDB. 1 Data Source - name of the server User ID and Password - identifies the authentication Initial Catalog - name of the database 4
Command Object Properties Connection • Provides the connection to the database • Connection string • Data Source - name of the server • User ID and Password - identifies the authentication • Initial Catalog - name of the database Command. Type • Indicates the type of command being executed • Text - to identify what records to add, delete, or modify within the database • Stored. Procedure - SQL commands that are stored within the database Execute • Execute. Non. Query() = executes command but does not return any output • Execute. Reader()= execute command return a typed IData. Reader • Execute. Scalar() = Execute command return one value • Execute. Xml. Reader() = execute command return an Xml. Reader to the caller • Dataset =Offline container of data, contains datatable(data. Column and data. Row) 5
Using ADO. NET and the Data. Set Object 6
Data. Adapter Object accesses a Data. Set object, because the Data. Set is disconnected, there must be methods used to maintain the original set of data and the changes Data. Set object is a disconnected collection of one or more tables that are stored in memory on the server The Data. Set is effectively a private copy of the database, and does not necessarily reflect the current state of the database If you want to see the latest changes made by other users, you can refresh the dataset by calling the appropriate Fill method of the Data. Adapter object 7
Data. Adapter Object Methods and Properties Fill method of the Data. Adapter inserts the data returned from the Select. Command into the Data. Set object Select. Command is used to retrieve data Insert. Command is used to add a new record Update. Command is used to modify the data within an existing record Delete. Command is used to permanently remove a record from the database So, the Data. Adapter provides the bridge between the Data. Set object and the data source 8
Data. Set Object Consists of the Data. Table. Collection and the Data. Relation. Collection Data. Table. Collection is a collection of one or more Data. Table Objects Each Data. Table object consists of a Data. Row. Collection, Data. Column. Collection, and Constraint. Collection The Data. Row. Collection and Data. Column. Collection store information about the rows and columns of data The Constraint. Collection includes information about the primary and foreign keys, and constraint rules 9
Data. Set Object Continued A primary key is used to ensure that no duplicate records appear in this column. Therefore, if one customer has the customer number, no other customer can have that number The Constraint rules are used to ensure that the field contains the correct datatype and values The Unique. Contraint and Foreign. Key. Constraint are used to create the relationships The Data. Relation. Collection contains the data required to maintain relationships between the Data. Tables Relational data can be exposed via ADO. NET because relationships can be made between Data. Tables The tables are joined using the primary and foreign keys that are defined in the Data. Table object 10
The ADO. NET Data. Reader Object To deliver a stream of data from the database Provides a high-performance method of accessing read-only data Read-only, forward-only stream of data from the database Requires continual access to the database, while the Data. Adapter uses a disconnected dataset to access the data You must remain connected until you have finished reading the stream of data 11
Data. Reader Methods and Properties Read - returns a single row and caches each row in memory only once, then moves the current record pointer to the next record or row Command. Behavior property - closes the connection as Close. Connection Close method - closes the Data. Reader object and releases the references to the rowset Execute. Reader method - retrieves rows of data as they are located in the database 12
Data. Table. Collection 13
Data. View Object The Data. Table object has a Default. View property that returns all the records in the Data. Table. However, you can select a subset of records from a table You an add columns to the Data. Column. Collection as well as to filter and sort the data in a Data. View The Row. Filter property of the Data. View allows you to filter a subset of the Data. View The Sort property of the Data. View also allows you to sort the data based upon a specific criteria in one or more of the columns 14
Data Related Namespaces The ADO. NET data objects are stored within the Data base class 15
- Slides: 15