6. DB with ASP.NET

Lesson No. 6 Database Concepts and Interaction with ASP.NET
Lesson Summary



Important Terms
Data – Collection of facts (unorganized)
Information – Processed data (manipulated data to produce results)
Database – Collection of related information grouped together
RDBMS – Relational Database Management System E.g. Oracle, MySQL, Access, MS SQL server
MS Access is an RDBMS software.
Database Objects – Table, Query, Form, Report

Table – Grid (collection) of rows and columns, where data is stored.

Record – Rows/Tuple  in a database table (collection of fields)
Field – Stores attribute value. Column in a table containing discrete element of information
Primary Key – is a column used to uniquely identify rows in the table. Used for relating a table to another one. Cannot have duplicate values. Applied to one column in MS Access.(Not compulsory)
Foreign Key – is a column from another table(primary key column) used to relate tables.

One to One – a field in one table is associated with one field of another table and vice versa.
One to Many – a field in one table is associated with more than one fields in another table.
Many to Many – a field in many table is associated with more than one fields in another table.

MS Access – Data Types for Columns
Text – (default data type) For textual data max 255 characters
Memo – For textual data max 65536 characters
Number – To store numeric data, types – Integer,
                      Long, Single, Double
Date/Time - To Store Date and Time values.
Currency– Used for Currency. Max 15 digits and 4 decimal places
AutoNumber – To give Auto number starts from 1
Yes/No – To store True/False. Takes 1 bit
OLE Object – To store binary files- audio, video, pictures upto 1 GB
Hyperlink – Contains links to other files
Attachment– For attaching files.
Calculated– To store result of expressions involving other column data
Lookup Wizard– To store list of options, which can be chosen

Views in MS Access
Design View – Used to define table, reports, forms. You can modify structure.
Datasheet View – To view, add, edit, delete table rows.
Tables can be created in Design view and and Datasheet view

MS Access Queries & its Types
Query – Used to get specific information from tables, search a database for a specific record.
 IT is fundamental means of accessing and displaying data from tables.
 Used to create, view, change and analyze data tables.
Select Query(default) – To retrieve data from one or more tables.
Parameter Query – Used with other query type. It displays dialogue box prompting for information
Cross Tab Query – To get summarized information. (count, sum, average and other aggregate functions and groups records)
Action Query – To make changes to, or move records. DELETE, UPDATE, APPEND and Make Table queries.
Queries can be created by two ways – Using Wizard, Using Design View

Options used in Query
Criteria option – To apply logical expressions/conditions on a query. It is associated with fields in the query design indicates how to filter records in query output.
Sort option – To sort the rows ascending or descending

QBE – Query By Example

SQL Structured Query Language
SQL – Language used to access data from database. Add, retrieve, modify, delete database. Developed by IBM
Types of SQL statements/queries
DDL (Data Definition Language) – Create, Alter objects
DML (Data Manipulation Language) – Select, Insert, Update, Delete
DCL (Data Control Language) – Commit, RollBack
TCL (Transaction Control Language)
A SQL statement/query is made up of SQL clauses.
SELECT – To query a table, to select a table and access data from the database.

SQL clauses
SELECT – To query a table, to select a table and access data from the database.
FROM – specifies the name of table (compulsory clause in Select)
ORDER BY – To sort the table rows in ascending or descending order. Keywords – ASC and DESC
WHERE – To define criteria for rows to selected for output
GROUP BY – To group records on values of a field
INSERT – To add the records in database

Comparison of SQL and MS-Access
SQL is designed for Multi user computer system, while Access is designed for Single computer system.
SQL can support as a developer tool while Access cannot support as a developer tool.
In SQL procedures are supported while in Access procedures are not supported.
Locking of data is available in SQL.
SQL provides security for structure

MS Access - Reports
Report – Reports are based on table or query.
IT is used to display records in the prescribed form.
It is a printable presentation of data gathered from a query.
They are used to view, format, print and to summarize data.
The data displayed on report cannot be edited.
Report design has total 3 sections.
Reports can be created from Design view and Report Wizard

MS Access - Form
Form – Forms provide users with an easy-to-read interface where they can enter table data.
It displays data from one or more table.
Data can be inserted, updated, or deleted from a table using a Form object.
Data entry forms are primary means of entering data into tables of the database.

Data access with ASP.NET
Communication can be established between application and an MS Access database by creating a connection that points to the actual database file (.accdb  or  .mdb)
Data in MS Access can be connected to ASP.NET / VB.NET by running the Data Source Configuration Wizard and selecting Database on the Choose a Data Source Type

ODBC provides a uniform access to data stored in different formats and databases.
We communicate with a database through ASP using ActiveX Data Object (ADO)
DSN – Data Source Name      Types – File, System, User

ADO stands for ActiveX Data Objects.
It is a Microsoft Technology.
ADO is a Microsoft Active-X component.
It is a programming interface to access data in a database.
It provides consistent access to data sources such as MS SQL Server, MS Access and other data sources through OLE DB and XML.
ADO provides an object oriented programming interface for accessing data source such as SQL Server.

Connected Architecture of ADO.NET
The architecture of ADO.net, in which connection must be opened to access the data retrieved from database is called as connected architecture.

ADODB – ActiveX Data Object Database
OLE – Object Linking and Embedding
OLEDB – Object Linking Embedding Database
ODBC – Open Database Connectivity
JDBC - Java Database Connectivity

Disconnected Architecture of ADO.NET
The architecture of ADO.NET in which data retrieve from database can be accessed even when connection to database was closed is called as disconnected architecture.

Disconnected architecture of ADO.NET was built on classes connection, DataAdapter, command builder and dataset and dataview.

ADO.NET Objects
ADO Connection Object – used to create an open connection to a data source. Types of connection depends on what database system you are working. E.g. SqlConnection, OleDbConnection, OdbcConnection

Connection.Open statement of connection object open the database.
ODBC driver should be installed and a data source name should be provided while using an ODBC data source.
Data Adapter Object – is a integral part of the ADO.NET which serves as a bridge between a DataSet and Data Source. The DataAdapter can perform Select, Insert, Update and Delete, SQL operations in the Data Source.

Command object – executes SQL statements and Stored Procedures against the Data source in the Connection object.

Property of Command object – CommandText which contains a string value that represents the command that will be executed in the Data source.

Recordset object – used to hold a subset of the records of a table.
    It is filled with records by using the Open method.
                Properties – BOF, EOF, state, locktype, maxrecords
                Methods – Open, Move, AddNew, Update,

Datareader – Used to retrieve the result set, It reads data from data store in forward only mode

SQL Statements performed on recordset/table
SELECT – To select data from table/database
INSERT INTO – To insert a new row in a table/recordset
DELETE – To delete rows in a table./delete current record in a recordset.
UPDATE – To update existing record in a table/recordset.

Dataset object – It has a collection of DataTables and DataRelation objects.

The DataTable object contains DataRow and DataColumn Collections.

The DataRelation object stores information about related tables, including which columns contain the primary keys and foreign keys that link the tables.

Access Data Source Control
This control is designed to work with Microsoft Access.
It uses OleDb data provider internally.
It enables to retrieve data from database file.

Access Data Source Control
Properties of AccessDataSource Control
Sorting – Sets the Data SourceMode property to the DataSet value.
Filtering – Sets the FilterExpression property to a filtering expression, used to filter the data when the select method is called.
Deleting – Sets the DeleteCommand property to a SQL statement, used to delete data.
Updating – Sets the UpdateCommand property to a SQL statement, used to update data.
Inserting – Sets the InsertCommand property to a SQL statement, used to insert data.

Short Questions
  • Define Data and Database
  • Explain the types of Relationships that can be created in MS-Access.
  • Define the term Record and Field.
  • What are data types in Access?
  • Explain the concept of Primary key in MS-Access.
  • Define the term Table and Queries
  • What is Query? Explain used of Query
  • Explain various methods of report creation.
  • What is SQL? Explain need of SQL.
  • Explain the terms ADO, OLEDB, ODBC nand DSN
  • Explain the ADO.NET objects: Command, Connecton and Recordset
  • Explain SQL Statements.
  • Explain AccessDataSource Control and its properties. 
Importance of Lesson No. 6 in Exam

Online Exam   (Weight-age: 10 marks)

  • MCQ1 Select one Answer - 3  questions 
  • Short/Long Answers  -  1 question  
  • Fill in the blanks -  1  question
  • True or False - 1  question
  • MCQ2 Select two Answers   - 1 question  
  • Rearrange the following - 1 question (may come)
  • MCQ3 Select three Answers -  1 question  (may come)
For Objective Questions Practice LINK 1 LINK 2

For Appearing an IT TEST  link 1 link 2


  1. This comment has been removed by the author.

  2. It's very good web site designed by you. It is most valuable for all science and commerce students.

  3. Perfect to refer i love it thnks 😍😍😍😍😍

  4. Very good app!it helped me a lot...

  5. Visit http://exza.in

    Since ITOnlineExam.com not working due to Bandwidth limit got exceeded yesterday....