Database Application Programming C

1
Database Application Programming(C)

• Chris North
• CS 4604 DB

2
GUI/DB Application Model
Win Form
DataGrid control
DataSet object
DB Connection DB Adapter DB SQL Query
Database
3
Architecture Layers

Application
App UI
WinForms, Swing
App User
Data Objects
Application
ADO, JDBC
DB API
ODBC, OleDB, JDBC
DB Driver

OS
SQLserver, Jet
Internet, local

DB Engine
DBMS UI
SQLserver, Access
DB Admin
Database
Database
4
C DataBase Access (ADO.net)

• OleDB, ODBC, SQLdb,
• Steps to get data
• dbConnection connect to DB
• dbCommand SQL query text
• dbAdapter executes query
• DataSet resulting data
• Steps to display data
• Bind to UI control, e.g. DataGrid
• or Manual data processing

DB
Alternative DataReader, retrieve data
incrementally
5
C DB Example

• Get data
• Using System.Data.OleDb // Jet MS Access DB
  driver
• con new OleDbConnection("ProviderMicrosoft.Jet.
  OLEDB.4.0Data Sourcec/mydb.mdb)
• cmd new OleDbCommand("SELECT FROM mytable,
  con) // SQL query
• adpt new OleDbDataAdapter(cmd)
• data new DataSet( )
• adpt.Fill(data) // execute the query and put
  result in data
• Display data
• dataGrid1.DataSource data.Tables0 // show
  the table in the grid control
• MessageBox.Show(data.Tables0.Rows05.ToString
  ( )) // or process manually, this is row 0 col 5

6
DataSet vs. DataReader

• DataSet
• Like 2D array arc
• Full dataset read from DB at query execution time
• Dataset cached locally in mem
• Can disconnect from DB
• Implemented using DataReader (Adapter)
• random data access
• - Limited scalability
• - initial read very slow

• DataReader (standard)
• 1 row at a time
• No local memory storage
• Fetch each row from DB on demand
• cursor current row
• Must stay connected to DB
• scalability
• - each row slow
• - random access difficult

7
Object Model

• DataSet
• Tablesn
• Rowsr
• columnsc
• Value, type,
• Columns info c
• Name, type,

• DataReader (standard)
• Columnsc (current row)
• Value, type,
• Columns info c
• Name, type,
• Cursor
• moveNext, MovePrev, moveTo
• EOF

8
Cursors

• Forward only vs. forward/back vs. random access
• Read only vs. writeable

9
Connections

• Connection strings
• Tons o examples http//www.connectionstrings.com/
  
• Con.open( )
• Queries here
• Con.close( ) // connections consume resources
• Adapter.Fill( ) does open/close automatically

10
Commands (Queries)

• Command Types
• SQL Query
• Relation SELECT
• Scalar SELECT that returns 1 row, 1 col
• Non-query INSERT, UPDATE, DELETE
• Table name
• View name
• Rdr Cmd.ExecuteReader( )

11
Query Parameters

• Want to substitute a parameter value into a query
• Dynamic SQL query construction
• cmd new OleDbCommand(
• "SELECT FROM table WHERE myAttr "
  myValue, con)
• but what if myValue contains weird chars?
• Parameterized query (more robust, reusable)
• cmd new OleDbCommand(
• "SELECT FROM table WHERE myAttr ?",
  con) // ? parameter
• cmd.Parameters.Add(?, myValue) // parameter
  value
• Or, put param query in DB as view / stored
  procedure
• cmd new OleDbCommand("MyStoredQuery", con) //
  name of view
• cmd.CommandType System.Data.CommandType.StoredPr
  ocedure
• cmd.Parameters.Add("?", myValue) // parameter
  value

12
DataBound UI Controls

• Display a table
• DataGrid
• Display a column
• DataList
• listBox
• ComboBox
• Display a cell value (of current row)
• TextBox
• Can bind any property of any UI control to any DB
  column

DataGrid control -scroll, sort, edit,
13
Manual data processing

• foreach(DataRow r in dataSet1.Tables0.Rows)
  // for each row
• doSomethingWith(rcolumnName) //or
• foreach(Object v in r.ItemArray) // for each
  column
• doSomethingWith(v)

14
Saving Data Changes

• Manual update/insert/delete queries
• cmd new OleDbCommand(UPDATE table SET
  myAttrvalue WHERE ididval, con)
• cmd.ExecuteNonQuery() // query does not return
  data.
• Adapters and bound UI Controls
• User can edit in DataGrid, etc.
• Writes DataSet changes to DB on Update( ) method
• Must have param update/insert/delete commands in
  Adapter
• cmd new OleDbCommand("UPDATE table SET attr1?,
  attr2? WHERE id?", con)
• cmd.Parameters.Add(new OleDbParameter(?, ,
  attr1, DataRowVersion.Current ))
• cmd.Parameters.Add(new OleDbParameter(?, ,
  attr2, DataRowVersion.Current ))
• cmd.Parameters.Add(new OleDbParameter(?, ,
  id, DataRowVersion.Original ))
• adpt.UpdateCommand cmd
• adpt.Update(data) // analogous to
  adpt.Fill(data)

15
Getting DB Schema Info

• Get list of tables in the DB
• con.Open()
• System.Data.DataTable t con.GetOleDbSchemaTable(
  
• System.Data.OleDb.OleDbSchemaGuid.Tables,
• new objectnull,null,null,"TABLE" )

16
Visualization

• Draw graphics in Paint event
• Scale data coords to pixel coords
• E.g. scatterplot
• foreach(DataRow r in dataSet1.Tables0.Rows)
  // for each row
• x rattr1 Width / maxValue1 //
  Scale x,y to fit into window
• y Height - rattr2 Height / maxValue2
  // also flip y axis
• e.Graphics.FillEllipse(Brushes.Red, x, y, 10,
  10)

17
Interaction

• Mouse Events
• Scale mouse coords back into data coords
• Parameterized SQL query
• E.g. 2 clicks define a rectangle
• Rect.left click1.x / Width maxValue1 //
  scale click x,y back to data range
• SELECT FROM table
• WHERE attr1 gt rect.left AND attr1 lt
  rect.right
• AND attr2 gt rect.top AND attr2 lt
  rect.bottom
• doSomething with results
• Refresh( ) // repaint the window to show
  results in Paint event