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 … App UI Application WinForms, Swing App User Data Objects Application ADO, JDBC DB API ODBC, OleDB, JDBC DB Driver … OS SQLserver, Jet Internet, local … DBMS UI DB Engine DB Admin SQLserver, Access 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("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

6. 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 DataSet vs. DataReader

7. 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 Object Model

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.StoredProcedure; 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.Tables[0].Rows) // for each row { doSomethingWith(r[“columnName”]); //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 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);

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 object[]{null,null,null,"TABLE"} );

16. 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); }

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 >= {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