7. The FCL: files, databases, & data structures

7. The FCL: files, databases, & data structures

Objectives “The Framework Class Library (FCL) contains thousands of classes, from graphics to I/O to networking to XML processing. The goal of the FCL? To present an abstract, portable view of the underlying operating system…” • File I/O • Database access • Data structures

Part 1 • File I/O…

I/O library • Input/output library in System.IO namespace • Compiled into mscorlib.dll assembly • Support provided for: • file and directory management • text files • binary files

Character I/O • Classes provided to do character IO • most methods defined in base classes • Two possible data locations • disk • string TextReader TextWriter StreamReader StringReader StreamWriter StringWriter write to disk write to StringBuilder read from disk read from string

StreamWriter • StreamWriter usage: • open file with one of many constructors • write with overloaded Write / WriteLine methods • close automatically converted to string using ToString char, bool, string, short, int, long, float, double, etc. text file StreamWriter sw = new StreamWriter("Chores.txt"); int n = 3; sw.WriteLine("Go to pet store"); sw.Write("Feed all "); sw.Write(n); sw.WriteLine(" cats"); sw.Close(); open write close

StreamReader • StreamReader usage: • open file with one of many constructors • read characters or strings with Read / ReadLine methods • close can read only char or string text file char, string StreamReader sr = new StreamReader("Chores.txt"); string s; while ((s = sr.ReadLine()) != null) Console.WriteLine(s); sr.Close(); open read close

Files and directories • Lots of utility classes for working with files & directories • Directory: for manipulating directories and drives • File: for manipulating files • Path: for manipulating path strings using System.IO; string[] drives = Directory.GetLogicalDrives(); foreach (string s in drives) { if (Directory.Exists(s)) ... } all drives disk in drive?

Part 2 • Database access…

Database library • Database access provided by System.Data.* namespaces • Compiled into System.Data.dll assembly • Known collectively as ADO.NET • native support for SQL Server and Oracle • support for other databases via older OleDB technology • requires a knowledge of SQL • Core namespaces: • general: System.Data, System.Data.Common • SQL Server: System.Data.SqlClient • Oracle: System.Data.OracleClient • OleDB: System.Data.OleDb

Relational technology • ADO.NET is designed to access relational databases • Example: • Sales database with customers, orders, and products

Overview of database access • Three steps: • open connection to database • execute SQL to update DB / retrieve records • close connection

connection Step 1: open connection • Connections are opened based on connection string info • here we open a connection to a MS Access 2000 database • "Sales.mdb" must exist in same dir as .EXE (e.g. bin\Debug) using System.Data; using System.Data.OleDb; string sConnection; sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=Sales.mdb"; IDbConnection dbConn; dbConn = new OleDbConnection(sConnection); dbConn.Open(); MessageBox.Show( dbConn.State.ToString() );

Building connection strings • Connection strings are vendor-specific, not well-documented • Where to turn for help? • www.connectionstrings.com • www.able-consulting.com/ADO_conn.htm

data reader record record record Step 2: retrieve records • Retrieve records via SQL Select query • read-only access by database field names string sql, name; sql = "Select * From Customers Order By LastName Asc, FirstName Asc;"; IDbCommand dbCmd; dbCmd = new OleDbCommand(); dbCmd.CommandText = sql; dbCmd.Connection = dbConn; IDataReader dbReader; dbReader = dbCmd.ExecuteReader(); while (dbReader.Read()) { // retrieve records 1-by-1... name = dbReader["LastName"] + ", " + dbReader["FirstName"]; this.listBox1.Items.Add(name); }

Step 3: close connection • Be sure to close connection… • to flush pending updates • so others can access DB (connections are limited resources) dbConn.Close();

Guaranteed close? • Ensure DB is closed via try-catch-finally: IDbConnection dbConn = null; try { dbConn.Open(); . . . } catch(Exception ex) { System.Diagnostics.EventLog.WriteEntry("MyApp", ex.Message); System.Diagnostics.EventLog.WriteEntry("MyApp", ex.StackTrace); throw ex; } finally { if ((dbConn != null) && (dbConn.State != ConnectionState.Closed)) dbConn.Close(); }

Updating a database • To update database, execute an SQL Action query • Example: • delete customer by their id number int result, cid = ?; string sql; sql = String.Format("Delete From Customers Where CID={0};", cid); IDbCommand dbCmd; dbCmd = new OleDbCommand(); dbCmd.CommandText = sql; dbCmd.Connection = dbConn; dbConn.Open(); result = dbCmd.ExecuteNonQuery(); dbConn.Close(); if (result != 1) throw new Exception("Action failed to delete 1 customer?!");

Example of action queries • Insert, update and delete: • Insert Into Customers(CID, FirstName, LastName, CreditLimit, Balance) Values(118, 'Jia', 'Zhang', 10000.0, 0.0); • Update Customers Set CreditLimit = 40000000000.0, Balance = 0.0 Where LastName = 'Gates' and FirstName = 'Bill'; • Delete From Customers Where CID = 666;

DataSets • DataSets are an in-memory, read-write data structure • easily filled with data from a database • easily displayed in a GUI app DataAdapter Command Connection DB DataSet

Example • Retrieve product info and display in a DataGrid: sql = "Select * From Products;" . . . DataSet ds; IDataAdapter adapter; ds = new DataSet(); adapter = new OleDbDataAdapter((OleDbCommand) dbCmd); dbConn.Open(); adapter.Fill(ds); dbConn.Close(); this.dataGrid1.SetDataBinding(ds, "Table");

Flushing changes back to database • Reconnect, and apply adapter's Update() method: sql = "Select * From Products;" . . . DataSet ds; IDataAdapter adapter; ds = (DataSet) this.dataGrid1.DataSource; adapter = new OleDbDataAdapter((OleDbCommand) dbCmd); OleDbCommandBuilder cmdBuilder; cmdBuilder = new OleDbCommandBuilder((OleDbDataAdapter) adapter); dbConn.Open(); adapter.Update(ds); // this fails if updates conflict dbConn.Close();

Part 3 • Data structures…

Collections library • Data structures in .NET are generally known as Collections • Located in the namespace System.Collections • Compiled into mscorlib.dll assembly • Defined in terms of object for generic use • Core classes: • Array • ArrayList • Hashtable • Stack • Queue

Collection interfaces • Collections implement various interfaces to ensure uniformity • classes that implement the same interface offer same services • makes library easier to learn and use • allows generic code to be written against interface • Core interfaces: • ICollection • IEnumerable • IEnumerator • IList • IComparer • IComparable

ArrayList • ArrayList provides storage for sequence of elements • duplicate values ok • data stored internally as an array, automatically resized • primarily manipulated via Ilist public class ArrayList : IList, IEnumerable, ... { // IList services ... // additional services int Capacity { get... set... } void TrimToSize() int BinarySearch(object value) int IndexOf (object value, int startIndex) int LastIndexOf (object value, int startIndex) ... } control of memory in underlying array searching

IList interface • IList defines sequence of elements • can be accessed by index public interface IList : ICollection { int Add (object value); void Insert(int index, object value); void Remove (object value); void RemoveAt(int index); void Clear (); bool Contains(object value); int IndexOf (object value); object this[int index] { get; set; } bool IsReadOnly { get; } bool IsFixedSize { get; } } add new elements remove containment testing read/write existing element structural properties

Example • Creating and using an ArrayList: using System.Collections; ArrayList a = new ArrayList(); a.Add("mom"); // added to end... a.Add("dad"); a.Add("sister"); Console.WriteLine(a[2]); // direct access if (a.Contains("dad")) // search ... foreach (string s in a) // iterate Console.WriteLine(s); element 0 element 1 element 2 "sister" true handles iteration interfaces, casting

Hashtable • Hashtable provides collection of key/value pairs • keys must be unique, values hold the data • stores object reference for both key and value • GetHashCode method of key used to determine placement Hashtable ages = new Hashtable(); ages["Ann"] = 27; ages["Bob"] = 32; ages.Add("Tom", 15); ages["Ann"] = 28; int a = (int) ages["Ann"]; create add update retrieve

Hashtable traversal • Can traverse Hashtable contents • each element is DictionaryEntry struct • data exposed in Key and Value properties Hashtable ages = new Hashtable(); ages["Ann"] = 27; ages["Bob"] = 32; ages["Tom"] = 15; foreach (DictionaryEntry entry in ages) { string name = (string) entry.Key; int age = (int) entry.Value; ... } enumerate entries get key and value

Summary • The FCL is huge • The FCL is quite powerful • The FCL is essentially a portable OS • fully-implemented on Windows 98 and above • partially-implemented on FreeBSD, Mac OS X, and Linux

References • Books: • J. Richter, "Applied Microsoft .NET Framework Programming" • T. Thai and H. Lam, ".NET Framework Essentials" • W. Vaughn and P. Blackburn, "ADO.NET Examples and Best Practices for C# Programmers" • B. Beauchemin, "Essential ADO.NET" • Web sites: • http://msdn.microsoft.com/net • Oracle for .NET: http://otn.oracle.com/tech/windows/odpnet/ • Rotor (SSCLI): http://msdn.microsoft.com/net/sscli • Mono: http://www.go-mono.com/

Lab? • Work on lab #4, "Interfaces and FCL"…

7. The FCL: files, databases, & data structures