Transcript select * from personel where adi




It is the primary data access model for .Net
applications
Next version of ADO
Can be divided into two parts
◦ Providers
◦ DataSets

Resides in System.Data namespace

It enables connection to the data source

Each data source has it’s own connection
provider

Common data access objects
◦
◦
◦
◦
◦
Connection
Command
Parameter
DataAdapter
DataReader


It provides the connection that is used for
accessing data source
Common types
◦ OleDbConnection
◦ OdbcConnection
◦ SqlConnection



Object Linking and Embedding Database
It aims to access to specific set of data sources
from .Net applications
Continuous access to data source even path of
the source is changes.

Odbc vs OleDb

System.Data.OleDb

Commonly used properties and functions
◦
◦
◦
◦
◦
◦
ConnectionString
ConnectionTimeout
BeginTransaction()
Close()
CreateCommand()
Open()
To add a connection
object to the form, just
drag and drop an
OleDbConnection from
the toolbox


Use ConnectionString property
in to Properties Window to set
connection information.
DataLink properties window
will be opened when you
ConnectionString property
click


From the first tab select
the type of data
provider
To define a connection
to an access database,
you should select
Microsoft Jet 4.0 OLE Db
Provider


It performs CRUD (Create-Read-UpdateDelete) operations on the database.
Common Types
◦ OdbcCommand
◦ OleDbCommand
◦ SqlCommand

It uses OleDb framework

Common properties and functions
◦
◦
◦
◦
◦
◦
CommandText
Connection
Parameters
Transaction
ExecuteNonQuery()
ExecuteReader()

Constructors
◦ OleDbCommand()
◦ OleDbCommand(string cmdText)
◦ OleDbCommand(string cmdText, OleDbConnection
myoledbConn)
◦ OleDbCommand(string cmdText, OleDbConnection
myoledbConn, OleDbTransaction myoledbtrans)
Type of
the object
Name of
the object
Connection
object
The SQL
command that
will be run


It provides a data parameter to command
object
Usage;
◦ First crate a command object with an SQL statement
which contains special characters for placing
parameters.
◦ Add parameters to the command object with
assigning values
◦ Execute the command object
Adding
parameters
Type of the
parameter
Length of the
parameter
Assigning
values to the
parameter


Used for retrieving data from datasource
without modifying the actual data (works
readonly)
Common types
◦
◦
◦
◦
◦
OdbcDataReader
OleDbDataReader
SqlDataReader
OracleDataReader
Db2DataReader

It has no public constructor. So, to crate a DataReader
object you should call ExecuteReader() function of the
releates command object.
◦ OleDbDataReader ordr = ocmd.ExecuteReader();

When Read() fucntion is called, DateReader object
starts to read data or moves to next record.
◦ if(ordr.Read())
◦ while(ordr.Read())

To access actual data one should use indexes or Get
functions of the reader
◦ ordr[0].ToString();
◦ ordr[“NameOfTheColumn”].ToString()
◦ ordr.GetString(0);

If you plan to continue to use Connection object
(e.g. to execute another SQL statement), then you
should call Close() function of the reader object.
◦ ordr.Close();

Common properties and functions
◦
◦
◦
◦
◦
◦
IsClosed
FieldCount
GetInt32(), GetDecimal(), GetString() ......
IsDBNull()
Read()
Close
Reader
object
If there is
data to be
read
Create reader
object by
executing the
command
Fetch the data
by providing the
column number
on the current
row
Reader
object
If there is
data to be
read
Create reader
object by
executing the
command
Fetch the data
by providing the
column name on
the current row

It is a kind of bridge between data source and
DataTable object

Constructors
◦ OleDbDataAdapter()
◦ OleDbDataAdapter(OleDbCommand myCmd)
◦ OleDbDataAdapter(string cmdString, OleDbConnection
mySqlConn)
◦ OleDbDataAdapter(string cmdString, string conn)

Common properties and funtions
◦ Fill()
◦ Update()
Create
adapter
object
Connection
object
Create
DataSet
object
Transfer data
from DB to
DataSet object

DataSet object used as a simple relational inmemory database in C# programs

It uses DataAdapter object to access and
modify data source

Internal structures within a DataSet object
◦
◦
◦
◦
DataSet
DataTable
DataColumn
DataRow
Hierarchically defined
DateSet objects




DataSet object represents the whole DB
It includes tables and the relations between
tables
It is filled by calling Fill() function of the
DataAdapter object.
Common properties and functions
◦ Tables
◦ AcceptChanges()
◦ Clear()

Constructors
◦ DataSet()
◦ DataSet(string dataSetNameString)

Example
◦ DataSet myDataSet = new DataSet();
◦ DataSet myDataSet = new DataSet("myDataSet");
Type of the
DataSet
object
Name of the
DataSet
object
Create a
new DataSet
object

It resides in DataSet object

It has name, columns and rows


Instead of creating a new DataTable, we
usually use the one that is inside a DataSet
object
A DataSet object may have more than one
(multiple) DataTable objects.


DataColumn: It corresponds to a column in a
DB table. It holds name and data type
information.
DataRow: It corresponds to a row in a DB
table. It holds actual data and used in select,
update, insert and delete operations.
Command
DataSet
object
DataAdapter
object
Fill
DataSet
From first
row of first
table of
DataAdapter
Fetch data
using
column
numbers





Filter a set of data
Eleminates row mismathcing rows, passes
matching ones.
If a select sql is executed without a where
keyword, then all rows in tables that are used
in select sql will be returned.
It needs columns in order to be used
Pay importance to data types while using
where keyword.

SELECT * FROM PERSONEL WHERE ADI = ‘ALİ’
◦ This query will return all rows in table PERSONEL whose name are
equal to ALİ
◦ where keyword should be used after table name
◦ After then we should write filter statements

SELECT * FROM PERSONEL WHERE ADI LIKE ‘AL%’

◦ This query will return all rows in table PERSONEL whose name starts
with AL
◦ LIKE keyword is used to search for specified patterns in a column.
SELECT * FROM PERSONEL WHERE ADI = ‘ALİ’ AND SOYADI = ‘KAYA’
◦ Where keyword may include multiple filtering statements
◦ The sql query seen above will return the rows with name equals ALİ
and surname equals KAYA

It is used for erasing records from table

Generally used with where keyword


It is an irreversible operation (unless used
within a transaction), so use it very carefully.
To execute a delete sql, first put it in to a
command object, then call ExecuteNonQuery
function.

DELETE FROM PERSONEL
◦ Deletes all rows (records) in table PERSONEL

DELETE FROM PERSONEL WHERE AGE < 18
◦ Deletes rows in table PERSONEL who are younger
then 18

DELETE FROM PERSONEL WHERE ADI LIKE
‘AL%’
◦ Deletes rows in table PERSONEL whose names starts
with AL





It is used for changing values in a tables
More effective then delete->insert
Usually used with where keyword
User should supply column names that will be
updated
To execute a update sql, first put it in to a
command object, then call ExecuteNonQuery
function.

UPDATE PERSONEL SET ADI = ‘ALİ’
◦ Changes all names to ALİ in table PERSONEL

UPDATE PERSONEL SET YAS = 18 WHERE YAS
< 18
◦ Sets the age value of the records to 18 who are
younger than 18




This operation is called JOIN operation
It combines desired columns from multiple
tables in to one data set.
Usually one column of a table is matched to
anoter related column in other table
If two tables have column with same name,
then we should write table names before
column names to get over confusion
LECTURES
STUDENTS
ID
LC_NAME
KAYA
5
PHYSICS
TAN
6
CHEMISTRY
ID
ST_NAME
ST_SURNAME
1
ALİ
2
VELİ
GRADES
ID
ST_ID
LC_ID
GRADE
1
1
5
50
2
1
6
30
3
2
5
80




Id, name and surnames are stored in
STUDENT table
Id and lecture names are stored in LECTURES
table
In grades table we store the scores of
students in courses
How to find the score of a student in a
specified lecture.


Answer: We have to combine (join) three
tables.
How?
◦ Pick the id of the student from STUDENTS table
◦ Pick the lecture information from LECTURES table
◦ Pick the lectures that student attends from GRADES
table
◦ Fetch grades of students from desired lessons from
GRADES table

Joining
◦ Join STUDENTS (ID) with GRADES(ST_ID)
◦ Join LECTURED(ID) with GRADES(LC_ID)

To select student name, surname, attended
lectures and grades we should write the
following SQL query
SELECT ST_NAME, ST_SURNAME, LC_NAME, GRADE
FROM STUDENTS, GRADES, LECTURES
WHERE STUDENTS.ID = GRADES.ST_ID
AND LECTURES.ID = GRADES. LC_ID