Transcript DB Application Programming

Database Application Programming
(C#)
Chris North
CS 4604: DB
GUI/DB Application Model
Win Form
DataGrid
control
DB Connection
DB Adapter
DB SQL Query
DataSet
object
Database
Architecture Layers
App UI
App User
Application
WinForms, Swing
Application
…
Data Objects
ADO, JDBC
ODBC, OleDB, JDBC
OS
DB API
DB Driver
…
SQLserver, Jet
Internet, local
DBMS UI
DB Engine
SQLserver, Access
DB Admin
…
Database
Database
C# DataBase Access (ADO.net)
•
•
OleDB, ODBC, SQLdb, …
Steps to get data:
1.
2.
3.
4.
•
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
C# DB Example
• Get data:
Using System.Data.OleDb;
// “Jet” = MS Access DB driver
con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/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.Tables[0];
// show the table in the grid control
MessageBox.Show(data.Tables[0].Rows[0][5].ToString( )); // or process manually, this is row 0 col 5
DataSet vs. DataReader
DataSet:
• Like 2D array: a[r][c]
• 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
Object Model
DataSet:
• Tables[n]
– Rows[r]
• columns[c]
– Value, type, …
– Columns info [c]
• Name, type, …
DataReader: (standard)
• Columns[c] (current row)
– Value, type, …
• Columns info [c]
– Name, type, …
• Cursor:
– moveNext, MovePrev, moveTo
– EOF
Cursors
• Forward only vs. forward/back vs. random access
• Read only vs. writeable
• …
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
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( );
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.StoredProcedure;
cmd.Parameters.Add("?", myValue); // parameter value
DataBound UI Controls
• Display a table:
• DataGrid
• Display a column:
DataGrid control
-scroll, sort, edit, …
• DataList
• listBox
• ComboBox
• Display a cell value:
(of current row)
• TextBox
• Can bind any property of any UI control to any
DB column
Manual data processing
•
foreach(DataRow r in dataSet1.Tables[0].Rows) // for each row
{
doSomethingWith(r[“columnName”]); //or:
foreach(Object v in r.ItemArray) // for each column
doSomethingWith(v);
}
•
Saving Data Changes
• Manual update/insert/delete queries:
cmd = new OleDbCommand(“UPDATE table SET myAttr=value WHERE id=idval”, 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);
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 object[]{null,null,null,"TABLE"} );
•
Visualization
• Draw graphics in Paint event
• Scale data coords to pixel coords
• E.g. scatterplot:
foreach(DataRow r in dataSet1.Tables[0].Rows) // for each row
{
x = r[“attr1”] * Width / maxValue1; // Scale x,y to fit into window
y = Height - r[“attr2”] * Height / maxValue2; // also flip y axis
e.Graphics.FillEllipse(Brushes.Red, x, y, 10, 10);
}
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 >= {rect.left} AND attr1 <= {rect.right}
AND attr2 >= {rect.top} AND attr2 <= {rect.bottom}
doSomething with results…
Refresh( ); // repaint the window to show results in Paint event