CS 341 Programming Language Design and Implementation

1. CS 341 Programming Language Design and Implementation • Administrative • HW7 will be released today — due next Wed, 3/19, 9pm • Quiz 4 next Friday, 3/21 • Today: • Executing SQL from C# CS 341 -- 12 Mar 2014

2. Netflix • Netflixrelational database: Movies table Reviews table CS 341 -- 12 Mar 2014

3. Netflix.sdf System.Data.SqlServerCe.dll • What type of database is Netflx? • DBMS: Microsoft SQL Server Compact Edition 4.0 • Standalone database file • Engine is a DLL — dynamically-linked library request Select * from … ; CS 341 -- 12 Mar 2014

4. Executing SQL from C#: • Basic pattern: using System.Data.SqlServerCe; // 1. open connection to database // 2. execute SQL command(s) against database // 3. display results (if any) // 4. close connection CS 341 -- 12 Mar 2014

5. (1) Opening and closing a connection: • place Netflix database file in bin\Debug or bin\Release… using System.Data.SqlServerCe; string connectionInfo; SqlCeConnectiondb; connectionInfo= "Data Source=Netflix-65K.sdf"; db = new SqlCeConnection(connectionInfo); db.Open(); string msg = db.State.ToString(); // debugging: MessageBox.Show(msg); // open? db.Close(); CS 341 -- 12 Mar 2014

6. (2a) Executing a scalar query • Scalar queries return a single value • Executing in C#: SELECT MovieName FROM Movies WHERE MovieID = 124; The Seventh Seal using System.Data.SqlServerCe; string connectionInfo = "Data Source=Netflix-65K.sdf"; SqlCeConnectiondb = new SqlCeConnection(connectionInfo); db.Open(); SqlCeCommandcmd = new SqlCeCommand(); cmd.Connection = db; cmd.CommandText = "SELECT MovieName FROM ...;" ; object result = cmd.ExecuteScalar(); db.Close(); MessageBox.Show( result.ToString() ); CS 341 -- 12 Mar 2014

7. (2b) Executing non-scalar queries: • Non-scalar queries return atemporary table • Executing in C#: SELECT UserID, Rating FROM Reviews WHERE MovieID = 124; "TABLE" DataSet using System.Data; using System.Data.SqlServerCe; string connectionInfo = "Data Source=Netflix-65K.sdf"; SqlCeConnectiondb = new SqlCeConnection(connectionInfo); db.Open(); SqlCeCommandcmd = new SqlCeCommand(); cmd.Connection = db; SqlCeDataAdapteradapter = new SqlCeDataAdapter(cmd); DataSetds = new DataSet(); cmd.CommandText= "SELECT UserID, Rating FROM ...;" ; adapter.Fill(ds); db.Close(); DataTabledt = ds.Tables["TABLE"]; foreach (DataRowrow in dt.Rows) Console.WriteLine( row["UserID"] + ": " + row["Rating"] ); CS 341 -- 12 Mar 2014

8. + (2c) SQL joins the same way… • joining tables… • Since a scalar result, use ExecuteScalaras before: SELECT AVG(Rating) FROM Reviews INNER JOIN Movies ON Reviews.MovieID = Movies.MovieID WHERE Movies.MovieName = 'Finding Nemo'; 3.3487 using System.Data.SqlServerCe; string connectionInfo = "Data Source=Netflix-65K.sdf"; SqlCeConnectiondb = new SqlCeConnection(connectionInfo); db.Open(); SqlCeCommandcmd = new SqlCeCommand(); cmd.Connection = db; cmd.CommandText = "SELECT AVG(Rating) FROM ...;" ; object result = cmd.ExecuteScalar(); db.Close(); MessageBox.Show( result.ToString() ); CS 341 -- 12 Mar 2014

9. (2d) SQL action queries: • Inserting, updating, and deleting records: • Executing in C#: INSERTINTO Movies(MovieID, MovieName) Values(1, 'When Harry Met Salley'); UPDATEMovies SET MovieName = 'When Harry Met Sally' WHERE MovieID = 1; DELETE FROM Reviews WHERE UserID = 666; using System.Data.SqlServerCe; string connectionInfo = "Data Source=Netflix-65K.sdf"; SqlCeConnectiondb = new SqlCeConnection(connectionInfo); db.Open(); SqlCeCommandcmd = new SqlCeCommand(); cmd.Connection = db; cmd.CommandText = "INSERT / UPDATE / DELETE ...;" ; introwsModified= cmd.ExecuteNonQuery(); db.Close();

10. Beware of string values containing ' • PLs require escaping of special chars inside string literals • SQL, like C++ and Java, is a programming language // C++ string s = "The Title is "Once Upon a Time", written by …"; INSERTINTO Movies(MovieID, MovieName) Values(9, 'It's a Wonderful Life'); INSERTINTO Movies(MovieID, MovieName) Values(9, 'It''s a Wonderful Life'); CS 341 -- 12 Mar 2014