Web Server Programming

1
Web Server Programming

• Direct Data Access, Data Binding

2
Content

• Direct Data Access
• Data Binding

3
Direct Data Access
4
Important Note!!!

• Last week, you learned how to connect to
  databases using built-in controls.
• Use them in your applications whenever possible.
• If you sure that you need to connect to database
  manually, follow the instructions explained in
  this class.

5
Direct Data Access Querying

• Create Connection, Command, and DataReader
  objects
• Use the DataReader to retrieve information from
  the database, and display it in a control
• Close your connection
• Send the page to the user

6
Updating, Inserting, Deleting

• Create new Connection and Command objects
• Execute the Command with the appropriate SQL
  statement

7
Direct Data Access with ADO.NET
8
ADO.NET Data Provider Classes

• Use OracleConnection, OracleCommand, etc. for
  Oracle data providers
• Use OdbcConnection, OdbcCommand, etc. for ODBC
  data providers

9
Namespace Imports

• Import following namespaces for SQL Server
• using System.Data
• using System.Data.SqlClient
• Import following namespaces for Access
• using System.Data
• using System.OleDb

10
Connecting Access Database

• OleDbConnection conn new OleDbConnection()
• conn.ConnectionString _at_"ProviderMicrosoft.Jet.O
  LEDB.4.0Data SourceDataDirectory\Survey.mdb"
  
• conn.Open()
• // Database operations will be here...
• conn.Close()

11
Connecting SQL Server Express

• SqlConnection conn new SqlConnection()
• conn.ConnectionString _at_"Data Source.\SQLEXPRESS
  AttachDbFilenameDataDirectory\Survey.mdf
  Integrated SecurityTrueUser InstanceTrue"
• conn.Open()
• // Database operations will be here...
• conn.Close()

12
How to Obtain Connection Strings

• Use SqlDataSource or AccessDataSource objects to
  connect to the database and copy the connection
  strings into your code
• http//www.connectionstrings.com shows many
  connection string options

13
Storing the Connection String

• Write the connection string into connectionString
  section of web.config file
• ltconfigurationgt
• ltconnectionStringsgt
• ltadd name"Pubs" connectionString"Data
  SourcelocalhostInitial CatalogPubsIntegrated
  SecuritySSPI"/gt
• lt/connectionStringsgt
• ...
• lt/configurationgt

14
Retrieving the Connection String

• string connectionString WebConfigurationManager.
  ConnectionStrings"Pubs".ConnectionString

15
Execute Command

• Command object has several methods starting with
  the "Execute" string
• ExecuteNonQuery() Used for queries that don't
  return any records (e.g. Update, Insert, Delete
  queries)
• ExecuteReader() Used for queries that return one
  or more records (e.g. Select query)
• ExecuteScalar() Used for queries that return one
  or more records but this method returns only the
  first column of the first row (suitable for
  obtaining number of records, maximum value of a
  column)

16
The DataReader

• Allows you to quickly retrieve all your results
• Uses a live connection and should be used quickly
  and then closed
• Can retrieve only one record at a time
• Supports fast-forward-only and read-only access
  to the results (previous record cannot be
  reached)
• Provides better performance than the DataSet

17
The DataReader

• Create a DataReader by ExecuteReader method of
  the Command object
• Retrieve the record by the Read() method of the
  DataReader object
• To retrieve the next record, use Read() method
  again
• If next record is successfully read, the Read()
  method returns true
• So, continue reading until the Read() method
  returns false

18
The DataReader

• OleDbConnection conn new OleDbConnection(_at_"Prov
  iderMicrosoft.Jet.OLEDB.4.0Data
  SourceDataDirectory\Survey.mdb")
• OleDbCommand cmd new OleDbCommand("SELECT
  FROM UserInfo", conn)
• conn.Open()
• OleDbDataReader reader cmd.ExecuteReader()
• while (reader.Read())
• Label1.Text reader"FirstName" "ltbr
  /gt"
• reader.Close()
• conn.Close()

19
ExecuteScalar Example

• OleDbConnection conn new OleDbConnection(_at_"Prov
  iderMicrosoft.Jet.OLEDB.4.0Data
  SourceDataDirectory\Survey.mdb")
• OleDbCommand cmd new OleDbCommand("SELECT
  MAX(FavoriteNumber) FROM UserInfo", conn)
• conn.Open()
• int maxfav (int)cmd.ExecuteScalar()
• conn.Close()

20
ExecuteNonQuery Example

• OleDbConnection conn new OleDbConnection(_at_"Prov
  iderMicrosoft.Jet.OLEDB.4.0Data
  SourceDataDirectory\Survey.mdb")
• OleDbCommand cmd new OleDbCommand("DELETE
  FROM UserInfo WHERE UserID5", conn)
• conn.Open()
• int affectedRowNumber cmd.ExecuteNonQuery()
• conn.Close()

21
Data Binding
22
Data Binding

• You can use the DataSet or the DataReader to
  retrieve rows of information, format them
  individually, and add them to an HTML table on a
  web page
• Conceptually, this isnt too difficult. However,
  it still requires a lot of repetitive code to
  move through the data, format columns, and
  display it in the correct order
• Repetitive code may be easy, but its also
  error-prone, difficult to enhance, and unpleasant
  to read
• Fortunately, ASP.NET adds a feature that allows
  you to skip this process and pop data directly
  into HTML elements and fully formatted controls.
  Its called data binding

23
Data Binding

• The basic principle of data binding is this you
  tell a control where to find your data and how
  you want it displayed, and the control handles
  the rest of the details.
• ASP.NET data binding works in one direction only.
  Information moves from a data object into a
  control. Then the data objects are thrown away,
  and the page is sent to the client. If the user
  modifies the data in a data-bound control, your
  program can update the corresponding record in
  the database, but nothing happens automatically.

24
Types of ASP.NET Data Binding

• Single-Value, or "Simple", Data Binding
• Single-value data binding allows you to take a
  variable, a property, or an expression and insert
  it dynamically into a page
• Single-value binding also helps you create
  templates for the rich data controls
• Repeated-Value, or "List", Binding
• Allows you to display an entire table (or just a
  single field from a table)

25
Using Data Binding

• To use single-value binding, you must insert a
  data binding expression into the markup in the
  .aspx file (not the code-behind file).
• To use repeated-value binding, you must set one
  or more properties of a data control.
• Once you specify data binding, you need to
  activate it. You accomplish this task by calling
  the DataBind() method of the control.
• Alternatively, you can bind the whole page at
  once by calling the DataBind() method of the
  current Page object.

26
A Simple List Binding Example

• ArrayList fruit new ArrayList()
• fruit.Add("Kiwi")
• fruit.Add("Mango")
• fruit.Add("Blueberry")
• fruit.Add("Apricot")
• fruit.Add("Banana")
• lstItems.DataSource fruit
• lstItems.DataBind() // or
• this.DataBind()

27
References

• Beginning ASP.NET 3.5 in C 2008 From Novice to
  Professional
• Visual Studio and MSDN Help