6. DB with ASP.NET

Lesson No. 6 Database Concepts and Interaction with ASP.NET
For IT Online MOCK EXAM   link 1 link 2
Lesson Summary


READ THE TOPIC NOTES AND THEN PRACTICE QUESTIONS ON 
 http://itonlineexam.com/fillups.php
http://exza.in/fillups.php
CHOOSE OPTION "ALL"  IN NO. OF QUESTION TO SEE ALL THE QUESTIONS

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

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.

Relationship

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) – GRANT and REVOKE

TCL (Transaction Control Language) - Commit, Rollback and Savepoint

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.NET

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.

Abbreviations

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