Db operations
This presentation is the property of its rightful owner.
Sponsored Links
1 / 44

DB Operations PowerPoint PPT Presentation


  • 86 Views
  • Uploaded on
  • Presentation posted in: General

DB Operations. ADO.Net. 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. ADO.NET Providers. It enables connection to the data source

Download Presentation

DB Operations

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Db operations

DB Operations


Ado net

ADO.Net

  • 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


Ado net providers

ADO.NET Providers

  • 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


Ado net connection

ADO.Net – Connection

  • It provides the connection that is used for accessing data source

  • Common types

    • OleDbConnection

    • OdbcConnection

    • SqlConnection


Ado net oledbconnection

ADO.Net – OleDbConnection

  • 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


Ado net oledbconnection1

ADO.Net – OleDbConnection

  • Commonly used properties and functions

    • ConnectionString

    • ConnectionTimeout

    • BeginTransaction()

    • Close()

    • CreateCommand()

    • Open()


Ado net oledbconnection2

ADO.Net – OleDbConnection

To add a connection object to the form, just drag and drop an OleDbConnection from the toolbox


Ado net oledbconnection3

ADO.Net – OleDbConnection

  • Use ConnectionString property in to Properties Window to set connection information.

  • DataLink properties window will be opened when you ConnectionString property click


Ado net oledbconnection4

ADO.Net – OleDbConnection

  • 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


Ado net oledbconnection5

ADO.Net – OleDbConnection


Ado net oledbconnection6

ADO.Net – OleDbConnection


Ado net command

ADO.Net – Command

  • It performs CRUD (Create-Read-Update-Delete) operations on the database.

  • Common Types

    • OdbcCommand

    • OleDbCommand

    • SqlCommand


Ado net oledbcommand

ADO.Net – OleDbCommand

  • It uses OleDb framework

  • Common properties and functions

    • CommandText

    • Connection

    • Parameters

    • Transaction

    • ExecuteNonQuery()

    • ExecuteReader()


Ado net oledbcommand1

ADO.Net – OleDbCommand

  • Constructors

    • OleDbCommand()

    • OleDbCommand(string cmdText)

    • OleDbCommand(string cmdText, OleDbConnection myoledbConn)

    • OleDbCommand(string cmdText, OleDbConnection myoledbConn, OleDbTransaction myoledbtrans)


Ado net oledbcommand2

ADO.Net – OleDbCommand

Connection object

Type of the object

The SQL command that will be run

Name of the object


Ado net parameters

ADO.Net – Parameters

  • 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


Ado net parameters1

ADO.Net – Parameters

Adding parameters

Type of the parameter

Assigning values to the parameter

Length of the parameter


Ado net datareader

ADO.Net – DataReader

  • Used for retrieving data from datasource without modifying the actual data (works readonly)

  • Common types

    • OdbcDataReader

    • OleDbDataReader

    • SqlDataReader

    • OracleDataReader

    • Db2DataReader


Ado net oledbdatareader

ADO.Net – OleDbDataReader

  • 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);


Ado net oledb datareader

ADO.Net – OleDbDataReader

  • 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


Ado net oledb datareader example

ADO.Net – OleDbDataReader example

Create reader object by executing the command

Reader object

If there is data to be read

Fetch the data by providing the column number on the current row


Ado net oledbdatareader example

ADO.Net – OleDbDataReader example

Create reader object by executing the command

Reader object

If there is data to be read

Fetch the data by providing the column name on the current row


Ado net oledbdataadapter

ADO.Net – OleDbDataAdapter

  • 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)


Ado net oledbdataadapter1

ADO.Net – OleDbDataAdapter

  • Common properties and funtions

    • Fill()

    • Update()


Ado net oledbdataadapter2

ADO.Net – OleDbDataAdapter

Create adapter object

Connection object

Create DataSet object

Transfer data from DB to DataSet object


Ado net dataset

ADO.Net – DataSet

  • DataSet object used as a simple relational in-memory database in C# programs

  • It uses DataAdapter object to access and modify data source

  • Internal structures within a DataSet object

    • DataSet

    • DataTable

    • DataColumn

    • DataRow


Ado net dataset1

ADO.Net – DataSet

Hierarchically defined

DateSet objects


Ado net dataset2

ADO.Net – DataSet

  • 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()


Ado net dataset3

ADO.Net – DataSet

  • 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


Ado net datatable

ADO.Net – DataTable

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


Ado net datacolumn datarow

ADO.Net – DataColumn & DataRow

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


Ado net datacolumn datarow1

ADO.Net – DataColumn & DataRow

Command

DataSet object

DataAdapter object

From first row of first table of DataAdapter

Fill DataSet

Fetch data using column numbers


Where keyword

where keyword

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


Example

Example

  • 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


Delete keyword

delete keyword

  • 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 deletesql, first put it in to a command object, then call ExecuteNonQuery function.


Example1

Example

  • 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


Update keyword

update keyword

  • 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 updatesql, first put it in to a command object, then call ExecuteNonQuery function.


Example2

Example

  • 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


Fetching data from multiple tables

Fetching data from multiple tables

  • 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


Example3

Example

LECTURES

STUDENTS

GRADES


Exapmle

Exapmle

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


Example4

Example

  • 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


Example5

Example

  • Joining

    • Join STUDENTS (ID) with GRADES(ST_ID)

    • Join LECTURED(ID) with GRADES(LC_ID)


Example6

Example

  • 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


  • Login