Session 4 : Data Access

1
Session 4 Data Access

• Peter Villadsen

2
Matters arising
3
Data.

• Data is what makes the enterprise work.
• The collected data is the enterprise's most
  valuable asset.
• As such, data is storage and access is vitally
  important.
• Data can come from various sources
• Databases
• Spreadsheets
• Mail
• Flat text files
• XML

4
The 1 tier model

• In the old days, organizations ran huge sites,
  and everyone connected to the mainframe using
  dumb terminals.

5
The 2 tier model

• When the PC hit the market, distributed
  architectures with clients and servers sitting on
  a network became common.
• Only the business data, not presentation images
  etc. would be transmitted.
• Does not scale well.

6
The 3 tier model

• This is exemplified in web sites. The data lives
  on its own server, the application logic is on a
  server that also provides the generation of the
  UI (HTML in the web case).
• The clients are thin, because they only provide
  the rendering.
• Fat clients do more on their own

7
Tier model comparison
8
The Data tier Databases

• Databases are programs (servers) that allow a
  number of programs to manipulate data in a safe
  way.
• The main commercial database servers today are
• MS SQL server
• Oracle
• IBM DB/2
• (MySQL)
• Data can get pretty voluminous
• Consider terraserver http//terraserver.microsoft
  .com/ (also available as a web service, btw).

9
Database Concepts

• Data within databases is stored as tables. Each
  table has a name.
• Each table has a number of rows containing the
  data.
• Tables have a set of columns of a specific name,
  type and other metadata.

10
Concepts

• Each table has any number of keys, that uniquely
  identify rows.
• The keys can contain any number of fields, whose
  combination of values make the key unique.
• The keys are used to construct indexes, that make
  it possible to access data efficiently.
• The database can maintain a number of constraints
  that must be valid at all times.

11
Concepts

• Transactions define a set of operations that may
  be viewed as atomic.
• All the operations within the transaction can be
  committed or rolled back in one operation.
• Scenario Moving money from one account to
  another

12
Concepts

• Before you can use a database, you must connect
  to it.
• DEMO (enterprise management tool).
• You communicate with a database server by giving
  it commands in a specific language.
• The de facto language for data manipulation is
  SQL (IBM, 1976, ANSI ISO).
• The database interprets the commands (in clear
  text) to modify or select data.
• We will learn how to query, insert, update and
  delete data.

13
CREATE Statement

• The CREATE command creates a database table. The
  syntax is
• CREATE Table (ColumnDefinition,)
• A ColumnDefinition is
• ColumnName DataType DEFAULT expr constraint
• A constraint is
• NULL NOT NULL PRIMARY KEY UNIQUE
    FOREIGN KEY REFERENCES ref_table (
  ref_column )      ON DELETE CASCADE NO
  ACTION      ON UPDATE CASCADE NO ACTION
    

14
DROP Statement

• The DROP command deletes an existing table (and
  all the data that is in it)
• DROP TABLE myTable

15
SELECT Statement

• The select statement gets data as requested by
  the user. The user may specify
• The columns he wants.
• An constraint to be fulfilled by all returned
  data. If no constraints are given, TRUE is
  assumed, meaning "all rows".
• An order in which the data is to be delivered.
• Examples
• SELECT ContactName, Country FROM Customers
• SELECT FROM Customers
• SELECT FROM Customers WHERE Country'Spain'
• SELECT FROM Customers WHERE Country'Spain'
  ORDER BY PostalCode
• SELECT FROM customers WHERE companyName LIKE
  'Futter'

16
SELECT

• DISTINCT columns
• SELECT DISTINCT City FROM Customers
• Aggregate functions.
• SELECT COUNT() AS antal FROM Customers
• SELECT AVG(Discount) from Order Details
• SELECT MIN(Discount) from Order Details
• SELECT MAX(Discount) from Order Details
• SELECT SUM(Price) FROM Order Details

17
Views

• The database support storing a select statement
  as a view.
• The view may subsequently be used like a table.
• DEMO

18
DELETE Statement

• The DELETE command is used to delete commands
  from the table.
• Example
• DELETE FROM Suppliers WHERE Country'Germany'

19
INSERT Statement

• Insert is used to create new rows in a table,
  containing the specified data.
• If a value is omitted for a given field, the
  default value is used.
• Syntax
• INSERT INTO tablename ( columnname,) VALUES
  (value,)
• There has to be the same number of columns as
  values.
• The column type and the value type have to be
  compatible.
• Example
• INSERT INTO CustomerCities (City) VALUES
  ('London')

20
INSERT Statement

• The insert command has an alternative syntax
  allowing data to be fetched from an existing
  table
• Example
• INSERT INTO CustomerCities (City)
• SELECT DISTINCT City FROM Customers

21
UPDATE Statement

• The update statement is used to update values of
  single columns on the rows designated by the
  where clause.
• The syntax is
• UPDATE table SET fieldnameexpr, WHERE expr
  
• Examples
• The company has decided to give all the managers
  a 10 percent pay increase
• UPDATE Employee SET pay pay1.1 WHERE
  type'Manager'

22
Transactions revisited

• Moving money from one account to the other
• BEGIN TRANSACTIONUPDATE Accounts SET
  amountamount-1000 WHERE AccountID'Deposit'UPDAT
  E Accounts SET amountamount1000 WHERE
  AccountID'Savings'COMMIT TRANSACTION
• (or ROLLBACK TRANSACTION)
• This ensures that we never end up with a
  situation where the mones has been removed from
  the deposit account and not inserted into the
  savings account.

23
Concepts Modelling Relationships

• We have seen 1n and 11 relationships in the
  past.
• This is easily modelled in tables by using
  Primary Key / Foreign Key.
• This constraint is maintained by the database
  engine. You cannot insert an order detail line
  that does not have an Orders row.

24
SELECTS revisited

• To deal with these relationships, it is possible
  to perform joins on tables.
• Joins are inner joins ("Give all records that
  exist in both tables") or outer joins ("Give all
  records in either table").
• DEMO
• Inner joins are the default.
• Examples
• SELECT Orders.CustomerId, od.ProductId FROM
  Orders JOIN Order Details AS od ON
  Orders.OrderIdod.Orderid
• select Orders.CustomerId, p.ProductName from
  Ordersjoin Order Details as od on
  Orders.OrderIdod.Orderidjoin Products as p on
  od.ProductId p.ProductId

25
More SELECTS

• Examples
• Find all products that have not been sold
• select Products.ProductId from Productswhere not
  exists ( select orders.OrderId from orders,
  Order Details as od where orders.OrderId
  od.orderid and od.ProductId
  products.ProductId)

26
Misc

• Metadata is stored in the database
• sp_tables
• sp_columns orders
• sp_help tablename

27
The Business Layer Data in .NET

• Data and Objects fit together like a hammer and a
  screw.

28
ADO.NET

• ADO.NET provides consistent access to data
  available in many datasources, specifically in
  databases.
• A bag of mixed goodies, to be used separately or
  in concert.
• The data used by ADO.NET (what is known as a
  DataSet) may be transported between tiers and
  through Webservices.
• ADO.NET is built on XML.
• ADO.NET solutions scale well because data may be
  disconnected from the database.

29
ADO.NET architecture

• ADO.NET consists of two main parts
• The DataSet.
• The .NET Framework Data Provider.

30
ADO.NET Data Providers

• A .NET Framework data provider is used for
  connecting to a data source, executing commands,
  and retrieving results.
• The results are either processed directly, or
  placed in a DataSet. The .NET Framework data
  provider is designed to be lightweight, creating
  a minimal layer between the data source and your
  code, increasing performance without sacrificing
  functionality.
• There are several different data providers
  shipped with .NET.
• SQL Server Data provider (in System.Data.SqlClient
  ). Provides access to newer MS SQL server
  versions.
• The Ole DB provider (in System.Data.OleDb).
  Provides access to any OleDB server.
• Others exists for different purposes (ODBC and
  Oracle).

31
ADO.NET Data Providers
32
The DataSet

• The DataSet is designed to be independent of the
  data source used.
• It consists of one or more DataTables
• Contains Keys, Constraints, Relations etc.
• It is a "mini database" that maintains data in
  memory as XML.
• It may be sent between tiers.

33
Connecting to an ADO.NET data source.

• The connection is defined through a connection
  string, defining all aspects of the connection.
• SqlConnection c new SqlConnection( "Data
  Sourcelocalhost" "Integrated
  SecuritySSPI" "Initial Catalognorthwind")
  c.Open()// Fetch data etc.c.Close() //
  Release resources

34
Demo

• Demo the ReadFromSQL solution.

35
Connecting to an OLEDB data source

• Connecting to an excel spreadsheet.
• OleDbConnection c new OleDbConnection(
  _at_"ProviderMicrosoft.Jet.OLEDB.4.0" _at_"Data
  Source" "C\MyExcel.xlsExtended
  Properties'Excel 8.0HDRYesIMEX1'")c.Open()
  // Get Datac.Close()
• HDR YES means first row contains field names.

36
Demo

• Show the ReadFromExcel solution.

37
Command objects

• After you have connected to a data source, you
  define the commands that you want to execute on
  the data source.
• Select commands
• SqlCommand selectCommand new
  SqlCommand("SELECT FROM Customers",
  connection)
• SqlDataReader reader selectCommand.ExecuteReade
  r()
• The reader is subsequently used to fetch the
  data.

38
Command objects

• Insert commands
• SqlCommand addStudent new SqlCommand("INSERT
  INTO Students(ID, Name) "
  "Values(_at_Id, _at_Name)", northwindDBConnection)
• addStudent.Parameters.Add("_at_Id",
  SqlDbType.NVarChar, 8).Value "PVILL"addStudent
  .Parameters.Add("_at_Name", SqlDbType.NVarChar,
  32).Value
  "Peter Villadsen"
• northwindDBConnection.Open()addStudent.ExecuteNo
  nQuery()northwindDBConnection.Close()

39
Transactions

• The connection object may be used to start and
  end transactions
• .
• SqlConnection c new SqlConnection( "Data
  Sourcelocalhost" "Integrated
  SecuritySSPI" "Initial Catalognorthwind")
  c.Open() // Open the connection// Start a
  transactionSqlTransaction myTrans
  myConnection.BeginTransaction() SqlCommand
  command // Run the command within the
  transactioncommand.Transaction
  myTransactioncommand.ExecuteNonQuery()
  c.Close() // Release resources

40
The DataReader

• Instances of the DataReader object are useful for
  reading data resulting from a command on an open
  connection.
• The DataReader instance contains the active row.
• Values are fetched by using the relevant
• Object reader.GetltTypegt(int index)
• The connection must be kept open for the extent
  of the reading of data.

41
Using DataSets

• Datasets are useful because the can load the
  contents from a command, allowing the connection
  to be closed.
• When update needs to take place, the connection
  is reopened.
• DataSets contain an in memory XML representation
  of the loaded data.
• DataSets contain any number of tables and may
  maintain relations and constraints.
• Apart from the data (stored internally as XML),
  the dataset also contains the table metadata.

42
Demo

• The DataSetRead solution shows how to read data
  into a DatSet, how to exploit metadata from the
  dataset and how to generate XML from it.

43
Updating Data in the DataSet

• When the data in the dataset is modified (by
  either changing record data, or inserting, or
  deleting rows changes need to be written back
  into the data store.
• This is done by opening a connection and calling
  the Update method on the adapter talking to the
  data source.
• The adapter will determine what commands need to
  be fired to update the data source.
• To do this, the adapter needs the commands for
  performing inserts, deletes and updates.
• You can code them by hand, or rely on the
  SqlCommandBuilder to do it for you.

44
Demo

• Show how updates may be performed using the
  SqlCommandBuilder. (DatSetUpdate).

45
Next Time

• In the next session we'll be dealing with
  developing applications for the windows platform.
• We will see how to perform painless databinding
  to data views.
• Please read chapter 13 in the textbook.