unit 5 inbuilt functions and database access using ado · ado.net , populating data in ado.net,...
TRANSCRIPT
SYLLABUS
5.1 Inbuilt Functions : Mathematical Functions( The Abs
function, The Exp function ,The Fix Function , The Int
Function, The Log Function , The Rnd( ) Function, The
Round Function, The Sqrt Function),
5.2 String manipulation( The Mid Function , The Right
Function, The Left Function , The Trim Function, The
Ltrim Function , The Rtrim Function , The InStr function
, The Ucase and the Lcase Functions , The Chr and the
Asc functions, Formatting Functions), Format Functions
( Formatting Using ToString Method ,Formatting Date and
Time ) 2
5.3 ADO .NET Object Model: Dataprovider( connection,
command, data reader, data adapter, datareaders)
Dataset (datatablecollection(datatable, datarows,
datacolumns, data constraints), datarelationcollection)
5.4 ADO .NET Programming :Creating a Database
Application, Creating Connection to a Database using
ADO.NET , Populating Data in ADO.NET, Browsing
Records, Datagrid view, Editing, Saving, Adding and
Deleting Records using bounded and unbounded
3
5.1 INBUILT FUNCTIONS
There are there types of inbuilt function in
VB.NET.
1) Mathematical Functions
2) String Manipulation Functions
3) Formatting Functions
4
1. MATHEMATICAL FUNCTIONS
All the Math functions of VB have been replaced
by the methods of the Math class.
1) Abs Function
This method returns the absolute value of its argument.
Syntax: Math. Abs (number)
Example: Math.Abs(-8)
5
2) Exp function The Exp of a number x is the exponential value of x,
i.e. ex .
Syntax: Math.Exp (number)
3) Fix Function
The Fix function truncate the decimal part of a positive
number and returns the largest integer but if
negative number give it returns smaller than the
number.
Syntax: Fix(Number)
Example: Fix(9.7)
6
4) Int Function
The Int is a function that converts a number into an
integer by truncating its decimal part.
Syntax: Int(Number)
Example: Int(2.4
5) Log Function
The Log function is the function that returns the natural
logarithm of a number.
Syntax: Math.Log (Number)
Example: Math.Log(10)
7
6) Rnd( ) Function
The Rnd function returns a random value between
0 and 1.
Syntax: Rnd(Number)
Number is an optional argument
7) Round Function
The Round function is the function that rounds up a
number to a certain number of decimal places.
Syntax: Math.Round(Number)
Example: Math.Round (7.2567, 2)8
8) Sqrt Function
The Sqrt function is the function that takes the
square root of a number.
Syntax: Math.Sqrt (number)
Example: Math. Sqrt(49)
9
5.2. STRING MANIPULATION FUNCTIONS
1) Mid(string, start, [length]) Function
The Mid() function returns a section of a string of
length characters, starting at position start.
Syntax: Mid(string, start, [length])
Example: Mid(“Good Morning”,1,4)
2) Right(string, number) Function
This function returns a number of characters from
the right side of a string.
Syntax: Right(string, number)
Example: Right(“Morning”,1)
10
3) Left(string, number)Function
This function returns a number of characters
from the left side(beginning) of a string.
Syntax: Left(string, number)
Example: Left(“Morning”,1)
4) Trim(“String”)Function
The Ttrim function trims the empty spaces on both side
of the string .It remove empty space both side.
Syntax: Trim(“String”)
Example: Trim (“ Visual Basic.net ”) 11
5) LTrim(“String”) Function
The Ltrim function trims the remove empty spaces of
the left portion of the String.
Syntax : Ltrim(“String”)
Example: Ltrim (“ Visual Basic.net”)
6) RTrim(“String”) Function
The Rtrim function trims the remove empty spaces of
the right portion of the String.
Syntax: Rtrim(“String”)
Example: Rtrim (“ Visual Basic.net ”)
12
7) InStr Function
This function searches one string into another string
and returns the starting position of the first
occurrence of a search string.
Syntax: InStr([start,],string1,string2,[compare])
Example: InStr(1,”Good Night”,
”Good”,CompareMethod.Text)
13
8) Ucase Function This function accepts a string or character as an
argument and returns a string or character converted
into upper case.
Syntax : UCase(“String” or Character)
Example: UCase(“vpmp”)
9) Lcase Function This function accepts a string or character as an
argument and returns a string or character converted
into lowercase.
Syntax : LCase(“String” or Character)
Example: LCase(“VPMP”)14
9) Chr Function This function returns a character corresponds to the
ASCII value specified as an argument.
Syntax : Chr(char code)
Example: Chr(65)
10) Asc Function
This function returns a ASCII value corresponds to
the given character.
Syntax : Asc(“character”)
Example: Asc(“A”)
15
3. FORMATTING FUNCTION
1. Now() Function
This function returns both the system date and time. The statement
Syntax:
MsgBox(Now())
displays a date/time combination such as 9/13/1998 09:23:10 PM in a message box. To extract the date or time part of the value returned by the Now() function, use the Date and TimeOfDay
properties:
Console.WriteLine(Now.Date.ToString)
Console.WriteLine(Now.TimeOfDay.ToString)16
Formate Styles
17
Formate Style Description
General Date Format the Current Date & Time
Long Date or D Display the Current Date in long format
Short Date or d Display the Current Date in Short format
Long Time or T Display the Current Time in long format
Short Time or t Display the Current Time in Short format
Example
18
Example Output
Format(Date.Now,”Gener
al Date”)
28/3/2017 3:10 PM
Format(Date.Now, “D”) Monday , March 28 2017
Format(Date.Now, “d”) 28/3/2017
5.3 ADO.NET OBJECT MODEL
ADO.NET stands for Active X Data Object.
ADO.NET is an Object Oriented Set of Libraries
that allows to interact with Data Sources.& Data
Source is a Database.
ADO.NET provides data access services in the Microsoft
.NET platform.
19
ADO.NET OBJECT MODEL
You can use ADO.NET to access data by using the new
.NET Framework data providers which are:
1. Data Provider for SQL Server
(System.Data.SqlClient).
2. Data Provider for OLEDB (System.Data.OleDb).
3. Data Provider for ODBC (System.Data.Odbc).
4. Data Provider for Oracle (System.Data.OracleClient).
21
ADO.NET OBJECT MODEL
Architecture of ADO.net basically consist of
two Components
1) .NET Framework Data Provider
(connected objects)
2) DataSet
(disconnected Objects)
22
1.1 CONNECTION OBJECT The Connection object Create the connection to the
database.
The ADO Connection Object is used to create an open connection to a data source. Through this connection, you can access and manipulate a database.
It is Two type of connection provide.
1. SqlConnection
2. OleDbConnection
Syntax of create connection object
1) Microsoft Access
Dim cn As New SqlConnection()
or
Dim cn As New OleDbConnection(“ConnectionString”)
23
2) Microsoft SqlConnection
Syntax of create connection object
Dim cn As New SqlConnection()
or
Dim cn As New SqlConnection(“ConnectionString”)
Connection String
Connection string is a sequence of name and
value pair separated by semicolon.
Connection string is used to specify name of provider,
type of data source , its location , username, password
etc.24
EXAMPLE OF CONNECTION STRING
Dim cn As New SqlConnection()
cn.ConnectionString=
"DataSource=.\SQLEXPRESS;AttachDbFilename
=C:\Users\-\documents\visual studio
2010\Projects\WindowsApplication1\Windows
Application1\Database1.mdf;Integrated
Security=True;User Instance=True"
25
2. COMMAND OBJECT
The ADO Command object is used specify query to
perform with data source or database.
The query can perform actions like creating,
adding, retrieving, deleting or updating records.
If the query is used to retrieve data, the data will
be returned as a RecordSet object. This means that
the retrieved data can be manipulated by properties,
collections, methods, and events of the RecordSet object.
28
COMMAND OBJECT
Syntax to create Command Object
1) For Microsoft Access
Dim cmd As New OleDbCommand()
Or
Dim cmd As New OleDbCommand(Query, connection)
2) For Sql Server
Dim cmd As New SqlCommand()
Or
Dim cmd As New SqlCommand(Query, connection)
30
Example-1Dim cn As New SqlConnection
("DataSource=.\SQLEXPRESS;AttachDbFilename=C:\Users\-
\documents\visual studio
2010\Projects\WindowsApplication1\WindowsApplication1\Database
1.mdf;Integrated Security=True;User Instance=True“)
Dim cm As New SqlCommand()
cm.Connection = cn
cm.CommandText = “Select * from student;”
31
3. DATA READER
DataReader provides a Read-only, Forward-only
stream of result from a database.
Data reader object is used to retrieve records from data
source and examine them one by one.
The important property of data reader are
1) Fast
2) Forward only
3) Read only
The SqlCommand.ExecuteReader() Method
returns a SqlDataReader Object.
32
4. DATA ADAPTER
A DataAdapter (DataAdapter class) is an object used to
exchange data between a data source and a data set.
In other words, a DataAdapter is used to read data from
a data source into a data set, as well as writing changed
data from the data set back to the data source.
A DataAdapter is used to fill a DataTable or DataSet
with data from the database with its Fill() Method.
34
Properties of DataAdapter
35
Properties Description
DeleteCommand Represents a DELETE Statement or deleted
records from the data Table
InsertCommand Represents a INSERT Statement or
inserting records from the data Table
SelectCommand Represents a SELECT Statement or select
records from the data Table
UpdateCommand Represents a UPDATE Statement or
Updating records from the data Table
TableMappings Represents a collection of mapping
between actual data source table & a Data
Table object.
Create Data Adapter Object
Syntax
Dim adp As sqlDataAdapter()
Create DataAdapter object by passing
parameter
Syntax
Dim adp as sqlDataAdapter(“query”,connection)
36
Example-1Dim cn As New SqlConnection(“Connection String”)
Dim q1 As String = “ select * from student;”
Dim cm As New SqlCommand(q1,cn)
Dim da As New SqlDataAdapter(cm)
37
DIFFERENCES BETWEEN DATA ADAPTER &
DATA READER
Data Reader DataAdapter
Data Reader provides open &
static forward–only connection.
DataAdapter provides a
disconnected data
architechecture.
Single Row can be read at a
Time.
Multiple Rows can be read at a
Time.
Provides record in read-only
mode.
Provides property to
insert,update delete& retrieve
rows using commands.
Good choice for retrieving large
amount of data.
Good choice for retrieving as
well as to insert,update delete
data.
It works quickly It is not works quickly
38
2. DATASET
The DataSet is a Disconnected storage &
disconnected in Memory representation of Data.
The DtaSet contains a collection of one or more
DataTable object made up of rows & columns of data,
as well as primary key ,foreign key ,relation
information about the data in the Data Table object.
The data in DataSet can be loaded from any valid data
source like Microsoft SQL server database, an Oracle
database or from a Microsoft Access database.
39
DataSet objects are in-memory representations of
data. They contain multiple Datatable objects, which
contain columns and rows, just like normal database
tables.
You can even define relations between tables to
create parent-child relationships.
Create Data Reader Object
Syntax
Dim ds as new DataSet()
or
Dim ds as new DataSet(“Dataset_Name”)
40
DATA TABLE
DataTable is a collection of DataColumn, DataRow
and Constrain. The concept of DataTable is similar
to the concept of tables in DataBase.
DataTable object allows you to examine data
through collection of rows and columns.
You can store the result of a query in a DataTable
through DataAdapter objects fill method.
Create DataTable object
Syntax: Dim dt as new DataTable ()
Or
Dim dt as new DataTable (“TableName”)
44
DATACOLUMN COLLECTION
Each DataTable has a column collection
The concept of DataColumn is similar to the concept
of column in the DataBase table.
DataColumn doesn’t actually contain data but it
contain the information about the structure of the
column.
Create DataColumn
Syntax: Dim col as new DataColumn()
Or
Dim col as new DataColumn(“ColumnName”)
45
DATA ROWS COLLECTION
The DataRow and DataColumn objects are
primary components of a DataTable.
Use the DataRow object and its properties and
methods to retrieve and evaluate; and insert,
delete, and update the values in the DataTable.
The DataRowCollection represents the actual
DataRow objects in the DataTable, and the
DataColumnCollection contains the DataColumn
objects that describe the schema of the
DataTable. Use the overloaded Item property to
return or set the value of a DataColumn.
46
DATA RELATION OBJECT
DataRelation object allows user to specify
relationship between different columns of different
DataTable objects.
Relationship is useful to provide data integrity and
consistency. Relationship can be established
between two tables by defining a primary key in one
table and using a foreign key in the other table.
Create Data Relation object
Syntax:
Dim dr as new DataRelation(“Relation
Name”, ”ParentColumn”, ”ChildColumn”)
47
5.4 Bounded Gridview Step Step 1 : Go to Data Sources and click at "Add New Data
Source".
Step 2 : A new window will be open.
Step 3 : Click the Next button.
Step 4 : Click the Next button.
Step 5 : Click the "New connection" button to make a
new database connection.
48
Step 6 : A new window will open asking to
save the connection string.
Step 7 : Click the Next button. In the new
window click the Table node to explore all
the tables of your database.
Step 8 : Click the table node to explore all
columns. Then check the checkbox for each
column to select them. Look at the
following figure.
Step 9 : Click the finish button.
49
GTU IMP QUESTION
1) Draw and Explain ADO.net Architecture .
2) Explain DataAdapter object with Example.
3) Explain DataTable, DataRow, and DataColumn
4) Explain DataConnection with Example.
5) Explain CommandObject with Example.
6) Explain (i) Abs (ii) Sqrt (iii)Int() (iv) Rnd()
7) Explain (i) Exp (ii) MAX (iii)Ceil (iv) Pow
8) Explain DataSet in brief.
50