Introduction to Database Systems
CSE 444 Outline
• SQL in C#
Lecture 06 – Read Chapter 8
SQL in C#, Project – But note that it does not talk about C#
April
p 11,, 2008 • Project Phase 1
1 2
C# - Crash Course C# - Highlights
• Hello World • C# = C++ Sytnax + Java Semantics
• Properties (getters/setters) • It is a “safe” language (like Java)
• Enums
• Partial classes • Can be embedded in Webpages
• Dataset: DataTable,
DataTable DataRow
DataRo • Can access a database
• Connecting to a database – Complex, but you should see the predecessors !
http://www.ecma-international.org/activities/Languages/Introduction%20to%20Csharp.pdf
3 4
1
Properties: Getters and Setters
Hello World
public class Point {
private int x; Point uvw = new Point();
using
i System;
S t
private string c;
uvw.position = 55;
class Hello { public int position { uvw.color = “green”;
static void Main() { get { return x; } uvw.position =
Console.WriteLine("Hello world"); set { x = value; c = “red”; } uvw.position * 2;
} } if (uvw.color == “green”)
} public string color { …
get { return c; }
set { c = value; x++; }
5 6
}
Indexers Enum
publicGetters
class Stuff { with […]
and setters
private
i int
i x[];[]
enum Color: byte {
public int this[int i] { Red = 1,
get {x[2*i+1]=0; return x[2*i]; } Green = 2,
set { x[2*i] = value; x[2*i+1]=1; } Blue = 4,
} Black = 00,
Stuff uvw = new Stuff(); White = Red | Green | Blue,
}
uvw[12] = 55;
uvw[99] = uvw[12]*7 + 2;7 8
2
Partial Classes The DataSet Class
• Some fields defined in file 1 This is an important class that allows you to
• Other fields defined in file 2 interact with a database
• Why ? DataSet = a “mini” database (cache) in main
We create file 1,
1 you create file 2 memoryy
• DataTable
• DataRow
9 10
DataSet Connecting to a Database
DataSet myLocalDB = new DataSet();
..... • Create or edit web.config
web config file
– Specify iisqlsrv, user, password
. . . . . /* create inside a table called “books” */
– Give a ‘name’
. . . . . /* (this is shown on a following slide) */
• Create a SqlConnection
– refer to ‘name’
/* now use “books” */
DataTable x = myLocalDB.Tables[
myLocalDB Tables[“books”]
books ] • Create a SqlDataAdaptor
– embed SQL query string
foreach (DataRow y in x.Rows) { • Execute the Fill( ) method to run query and store
if (y[“title”] == “Harry Potter”) y[“price”]++;; answers in a datarow
} 11 12
3
Connecting to a Database Phase 1
//* create inside a table called “books”
books *// • Task 1: Schema design
SqlConnection c = new SqlConnection( . . . “name” . . .);
• Task 2: Import sample data
string q = “select title, price year SQL = a string !!!
from products “impedance
p
where price < 100”; mismatch” • Task 3: Modify
Modif starter code
SqlDataAdapter a = new SqlDataAdapter(q, c);
DataSet myLocalDB = new DataSet();
a.Fill(myLocalDB, “books”); 13 14
Task 1: Schema Design Task 1: Schema Design
Official requirement • Optional: draw an E/R diagram
• Read the project description • Create a file:
CREATE TABLE Customer ( . . . )
• Design a “good” database schema CREATE TABLE Invoice ( . . . )
...
– More about this in lecture the next week
• Create a second file:
DROP TABLE Customer
DROP TABLE Invoice
...
(why ?)
15 16
4
Task 1: Schema Design Task 1: Schema Design
What you should do: Things to worry about:
• Read description AND look inside the • Keys/foreign keys: note table order matters!
starter code App_code/Provided/… • Make sure you represent all the data
• Read the classes, determine the fields… • Null-able or not (don’t worry too much)
Things not to worry
orr about:
abo t:
• fname or FirstName or PersonFirstName ?
• varchar(20) or char(200) or varchar(120) ?
17 18
Task 2: Import Sample Data Task 3: Modify Starter Code
INSERT INTO Customer ( . . . )
• Create a file: VALUES (‘John’,
(‘J h ’ ….))
Thee starter
sta te code:
INSERT INTO Customer ( . . . ) • C#
VALUES (‘Sue’, ….) • ASP.NET (you do not need to understand it)
...
It provides a Website for accessing your online store
• You may need to run this: BUT it misses the fragments of code that get the
DROP TABLE Customer
data from the database
DROP TABLE Invoice See
http://iisqlsrv.cs.washington.edu/444/Phase1_Example/
(why ?) ...
19 20
5
Task 3: Modify Starter Code Task 3: Modify Starter Code
/* your GetInvoices code goes here */
• What you have to do: Substitutes
string s = String.Format( id for {0}
• App_Code/Phase1/Billing and Shipping/…
@“SELECT …
Public partial class Customer { FROM ….
WHERE x.customerId = {0} …”, id); Defined in
/* add your own fields, like: */
Provided
private int id,
id
StoreContext store = StoreContext.current;
Procedure List<invoice> GetInvoices() { DataSet ds = new DataSet( );
/* your GetInvoices code goes here */ DataTable invoices = store.GetDataTable(s, ds, “invoices”);
} 21
/* continued on next slide…. */ 22
Task 3: Modify Starter Code Task 3: Modify Starter Code
/* … continued from previous slide */ public partial class Invoice {
public
bli Invoice(DataRow
I i (D t R invoiceData)
i i D t ){
List<Invoice> invoiceList new List<Invoice> ( ); /* here goes your code, something like that: */
init(invoiceData); /* may need it in several places */
foreach(datarow r in invoices.Rows) { }
invoiceList.Add(new Invoice( r )); ....
} private void init(DataRow invoiceData) {
Now you need invoiceId = (int) invoiceData[“invoiceId”];
return invoiceList; to implement this orderDate = (DateTime) invoiceData[“date”];
} .....
23 In Provided In you SQL 24
6
Time Estimate
• Task
as 1:: about 9 tables
tab es or
o so
– 2 hours or more
• Task 2: try 2 tuples per table
– 1 hour
• Task 3:
– Need to find out WHAT to modify in starter code
– May need to DROP TABLEs then go to Task 1
– Total time here: anywhere from 2 to 14
25
