1Relational Databases, SQL and ADO.NET in 75 minutesWeb Application DevelopmentEstan and KivolowitzRelational Databasesz Data is organized into tables with rows and columnsz A row is a single instance of a recordz Columns are the attributes of a recordz Tables can be linked in relationshipsWeb Application DevelopmentEstan and KivolowitzKeys / Indexesz Keys are columns or groups of columns that are “Indexed” to make find / sorting them fasterz Index can be unique or allow duplicatesz One key (one or more columns) can be “primary,” must be uniqueWeb Application DevelopmentEstan and KivolowitzOrganizing data (schema)z How data (tables, rows, columns) are organized in a database is its “schema”z Data is organized best when it is organized in a “normal form”z You will be given existing tables so understanding normal forms is not necessaryz Please take CS 564 for more informationWeb Application DevelopmentEstan and KivolowitzRelationshipsz Only type of relationship discussed here is a “link” where rows / records in two tables share a common column / attributez Table 1: UID, Namez Table 2: UID, Grade1UID, Grade2 etc.z Find Joe’s name and grades where the UID in both tables refers to Joe. Web Application DevelopmentEstan and KivolowitzSQLz Structured Query Languagez A few words that impact your life every dayz We will focus on 4 commandsz Selectz Insertz Updatez Delete2Web Application DevelopmentEstan and KivolowitzQuotationz Specifying data in SQL commands are very fragile with respect to use of quotation marksz If specifying SQL commands from a program use “parameterized” arguments to avoid the problemz Parameterized arguments are discussed laterWeb Application DevelopmentEstan and Kivolowitzselectz Select columns from tables where certain conditions are true plus some optionsz Select all columns, all records:z select * from t;z Select all columns, some recordsz select * from t where age > 21;z Select all columns, some records, w/ optionsz select * from t where age > 21 order by lastname;Web Application DevelopmentEstan and Kivolowitzselectz Select some columnsz select firstname, lastname from t;z Select on more than one conditionz select * from t where age > 21 and age < 75;z Usual logical operators for conditionsz String columns can be pattern matchedz select firstname where firstname like ‘%th%’;Web Application DevelopmentEstan and Kivolowitzselect z Select (and summarize) by groupz select count(state),state from t group by state;z Select unique valuesz select distinct state from t order by state;z See:z http://dev.mysql.com/doc/refman/5.1/en/sql-syntax.htmlWeb Application DevelopmentEstan and KivolowitzSelecting from more than one table - Joinz There are several types of joins. We only look at the “inner join” (simply use “,” between table names)z Cross product of two tables (hopefully) limited by some constraintz select id, name, ordernumber from customers, orders where customers.id = orders.customeridorder by id;z If there is a column with the same name in two tables, you must disambiguated explicitlyWeb Application DevelopmentEstan and Kivolowitzinsertz insert into tbl set columnname=value;z Multiple columns can be set separated by “,”z Value can be “default” if column has a defaultz If there is a collision of a “unique” key, an error resultsz Use “ignore” syntax if you don’t carez insert ignore into t set id=29;z See http://dev.mysql.com/doc/refman/5.1/en/insert.html3Web Application DevelopmentEstan and Kivolowitzupdatez update [ignore] tbl set id=9 where id=6z Multiple columns may be set separated by “,”z Compound “where” conditions may be usedz Note the optional “ignore” if you are changing a key value that is supposed to be unique and a collision occursz See http://dev.mysql.com/doc/refman/5.1/en/insert.htmlWeb Application DevelopmentEstan and Kivolowitzdeletez delete [ignore] from tbl where id=9;z Don’t leave out the where condition unless you want to delete all records (not in this class)z Note optional “ignore” to ignore errorsz Multiple where conditions may be specifiedz Seez http://dev.mysql.com/doc/refman/5.1/en/delete.htmlWeb Application DevelopmentEstan and KivolowitzADO.NETz Active Data Objects for .NETz Object oriented wrapper to database methods and data structuresz We will use ODBC version of methodsz Open Database Connectivityz Independent of database backendWeb Application DevelopmentEstan and KivolowitzTypical flowz Define connection – the connection stringz Open the connectionz Issue commands, receive / transmit dataz Close the connectionWeb Application DevelopmentEstan and KivolowitzConnection stringz MySQL versionz DRIVER={MySQL ODBC 3.51 Driver}; z SERVER=oberon.cs.wisc.edu; z PORT=3400;z DATABASE=databaseName; z USER=userName;z PASSWORD=myPassword;z OPTION=3;" z One long stringEach of you will get yourown copy of the databaseWeb Application DevelopmentEstan and Kivolowitz{MySQL ODBC 3.51 Driver};z Refers to the MySQL connector which must be installed on your systemz Will be preloaded on instructional machinesz Found here:z http://dev.mysql.com/downloads/connector/odbc/3.51.html4Web Application DevelopmentEstan and KivolowitzConnection objectz Instantiate an OdbcConnectionz Pass connection string to constructorz Will use:z Methodsz Open – open the connectionz CreateCommand – create command objectsz Close – close the connectionz Attributesz StateWeb Application DevelopmentEstan and KivolowitzConnection objectz Remember to close an open connectionz Nice use of “finally”z Or web page’s “Unload” function – discussed in future lecturez Use open / close judiciously as operation is high overheadWeb Application DevelopmentEstan and KivolowitzCommand objectz Instantiate OdbcCommand object either by constructor or connection object CreateCommandz If you use the constructor, you need to specify the connection object to the Connection attributez Specify command in CommandTextz Use parameterized queries!z Command.Parameters.AddWithValue()Web Application DevelopmentEstan and KivolowitzParameterized queriesz If any part of a SQL command can come from user input, avoid SQL injection attacks by using parameterized queriesz Example:select c2 from t where c1 = ____;substitute1; drop table tz What happens?Web Application DevelopmentEstan and KivolowitzParameterized queriesz Cleaner looking codez Eliminates the headache of proper quotingWeb Application DevelopmentEstan and
View Full Document
Unlocking...