Download
1 / 13
130 likes | 219 Views
CTEC2902 Advanced Programming. Introduction to A ctiveX D ata O bjects .NET (.NET Class Library for Database Processing). Getting Started. The story so far… You know how to Use existing classes (essential when using ADO.NET) Structure data in a RDB Use SQL to process relational data
E N D
CTEC2902Advanced Programming Introduction to ActiveX Data Objects.NET (.NET Class Library for Database Processing) Intro to ADO.NET
Getting Started • The story so far… • You know how to • Use existing classes (essential when using ADO.NET) • Structure data in a RDB • Use SQL to process relational data You are now ready to start developing DB applications • Let’s carry on… Intro to ADO.NET
Introduction to ADO.NET RDB Classes in ADO.NET RDB ComponentADO.NET class Database DataSet (for future use) Table DataTable Record DataRow Field Item The focus of our module All underpinned by SQL Intro to ADO.NET
Introduction to ADO.NET Data Table Connector Data Adapter SQL-populated data tables in VB + ADO.NET No need to get involved; Instead, use custom-made class, named … clsSQLServer Make sure you have a copy of its API ADO.NET Programming Components DBMS (Source) SQL Server or Access or MySQL or Oracle Application Written in ASP + VB.NET Intro to ADO.NET
Introduction to ADO.NET • Programming Steps • Create a database object, using clsSQLServer • (to link to & process a database, via SQL in stored procedures ) • 2.Create a data table object, using DataTable class • (to save the data returned by SELECT) • 3.Use the QueryResults property of your database object to populate your data table object, for viewing and generating reports Intro to ADO.NET
Introduction to ADO.NET Name of database to open Your local object Class name Step 1 – Creating Database objects DimobjectAs New clsSQLServer(DBName) E.g. Dim MyDB As New clsSQLServer(“BSC_Computing.mdf”) Dim AnotherDB As New clsSQLServer(txtDBName.Text) Intro to ADO.NET
Introduction to ADO.NET • Step 2 – Creating Data Table Objects • Dimdatatable_object As NewDataTable • e.g. to create data table objects • Dim dtStudents As New DataTable • Dim dtModules As New DataTable • Note the prefix “dt” for data table objects • When data tables are created, they are empty; • so we must populate them To hold the records returned by SELECT Intro to ADO.NET
Introduction to ADO.NET Step 3 - Populating Data Tables Dim DB As New clsSQLServer(“MyDB.mdf") Dim dtStaff As New DataTable DB.Execute("sp_tblStaff_GetName") dtStaff = DB.QueryResults Results from SELECT after execution Name of stored procedure Your table Intro to ADO.NET
Introduction to ADO.NET Sample Stored Procedure CREATE PROCEDURE dbo.sp_tblStaff_GetAll AS SELECT * FROM tblStaff Intro to ADO.NET
Introduction to ADO.NET Another Sample Stored Procedure CREATE PROCEDURE dbo.[sp_tblStaff_GetName] AS SELECT Surname + ', ' + FirstName AS Fullname FROM tblStaff Intro to ADO.NET
Introduction to ADO.NET Fields, Columns, Attributes 0 1 2 3 etc. Records Rows Tuples 0 1 2 3 etc Can also be referred to by field name Data item Cell value Structure of a Data Table A data table can be viewed as a zero-based, 2-D array Intro to ADO.NET
A Data table may contain (depending on the SELECT command): 1. All the records from an RDB table e.g. all students on a given module 2. Some of the records from an RDB table; called “subset” e.g. students who have not yet sat the phase test 3. Records from 2 or more RDB tables; called “superset” e.g. students + their course & mentors Intro to ADO.NET
How do you view data table contents? Use Data-Bound List Boxes (see example) Use Data-Grid (see example) Intro to ADO.NET
