UNIT 9 2 Database access from code Database

UNIT 9. 2 Database access from code • • • Database Cycle Review SQL Command Types INSERT, UPDATE, DELETE Putting it all together: Execution Hands on Activity

Accessing Data Sql. Data. Source gy olo n h ) tec aces t ha esp A. WNam ( Browser location B. Database string) n (Connectio C. What data (Command) ad Web server e. g. Fire. Fox e. g. Windows Server HTML ASPX D. R un (D ! ata Re er) Database server e. g. MS SQL Server SQL ["1", “Registration Fee", 5. 00, ” 2”, “Vehicle License Fee", 7. 5, ” 3”, “Weight Fee“, 12. 500] er) rse n R Pa op i. E Lo ( d ea

The Five Grand Steps to Database Access in Code 1. Get READY • Add namespaces 2. WHERE the database is • Create a Connection String (name, path) 3. WHAT needs to be done • Create a Command Object (SQL) cs. Fees my. Command 4. EXECUTE! • Execute the Command output data stream into a Reader 5. Loop through the Reader and read the data "1", “Registration Fee", 5. 00, ” 2”, “Vehicle License Fee", 7. 50, ” 3”, “Weight Fee“, 12. 50 For commands that do not CHANGE the database

SQL Command Types Based on whether or not they AFFECT the database A. SELECT B. INSERT, UPDATE, or DELETE Images source: artistitvalley. com

Handling SELECT commands • SELECT commands output data • Temporary holder: Sql. Data. Reader • The reader needs to be looped through Sql. Connection my. Connection = new Sql. Connection(str. Connection); Sql. Command my. Command = new Sql. Command(str. Sql, my. Connection); Sql. Data. Reader my. Reader = my. Command. Execute. Reader ();

INSERT commands • Adds new records to the database • Syntax Example: INSERT INTO table. Name (list_of_fields) VALUES (list_of_values) • Best Practice: – – INSERT INTO OUCourses (CName, CNum, CHrs) VALUES (“Coding”, 101, 3) comma separated, enclosed in ( ) in same order as fields Include the primary key, only if it does NOT auto-increment Include all fields that don’t allow nulls & have default values Include any foreign keys Ensure data to be in the correct data type

UPDATE Commands • Used to modify existing database records • Syntax: Example: UPDATE table. Name SET field 1=value 1 [, …] WHERE field. X =Y • Best Practice: UPDATE OUCourses SET CName=“coding”, Cnum=101 WHERE CID=5476 One or more values Which record to update Don’t update the primary key Use the primary key to Identify a single record Ensure compliance with records that don’t allow nulls Ensure compliance with foreign keys Without a WHERE clause all records in the table will be updated – If no records are updated, it is because no records qualified – – –

DELETE Commands • Used to remove records from the database • Syntax DELETE FROM table. Name WHERE condition DELETE FROM OUCourses WHERE CID=5476 • Best Practice: – – Omitting the WHERE clause will delete all records in the table If no records are deleted, it is because no records qualified Cannot delete records on the ONE side of a 1 -to-many relationship Always confirm a delete actions

The Execution - Step #D • Use. Execute. Non. Query(), instead of. Execute. Reader() • Syntax: int. Cnt = my. Command. Execute. Non. Query(); Number of records affected • Best Practice: – – – Syntax for the command connection object are unchanged In SQL: use parameters for any data coming from Text. Fields Assign values to the parameters Counter estimates whether the command was successful Question: What if int. Cnt =0?

A B Execution i. e. Step #D (in Code) string str. Connection = Configuration. Manager. Connection. Strings["cs 3200"]. To. String(); Sql. Connection my. Connection = new Sql. Connection(str. Connection); C 1 string str. Sql= "INSERT INTO OUCourses (CName, CNum, CHrs) VALUES (@CName, @CNum, @Credit. Hrs)"; Sql. Command my. Command = new Sql. Command(str. Sql, my. Connection); C 2 string str. CName = txt. CName. Text; my. Command. Parameters. Add("@CName", System. Data. Sql. Db. Type. NVar. Char, 50). Value = str. CName; int. CNum = Convert. To. Int 32(txt. Hrs. Text); my. Command. Parameters. Add("@CNum", System. Data. Sql. Db. Type. int). Value = int. CNum; int. Hrs = Convert. To. Int 32(txt. Hrs. Text); my. Command. Parameters. Add("@Credit. Hrs", System. Data. Sql. Db. Type. int). Value = int. Hrs; int. Cnt =-1 my. Connection. Open(); int. Cnt = my. Command. Execute. Non. Query(); // check whether insert succeed if (int. Cnt = 0) lbl. Insert. Text = “One Records was added"; Else lbl. Insert. Text = “No records were added"; my. Connection. Close(); D

L 2: Hands-on to add new fees to the fee table Load the Sample Page Setup a new page: 1. Create a new page in your Unit 9 folder: last. Name. U 9 L 2. aspx 2. Use the sample page to add an H 2 heading: INSERTING AND UPDATING DATA FROM CODE 3. Use the sample pageto add an H 3 heading: THE FOLLOWING FEES ARE CURRENTLY RECORDED

U 9 L 2 - 2 Add Database GUI stuff: 4. Add a Grid. View under the H 3 heading 5. Create a SQL Data. Source for the Grid. View • • Based on the sample page, select the appropriate table and fields No WHERE clause needed 6. Select a nice format for the Grid. View and add a Select link (used in in U 9 L 2. 2, not in U 9 L 2)

U 9 L 2 - 3 Set up text boxes for manual entry of data: 7. Below the Data. Source, add an H 3 title that says ADD A NEW FEE: 8. Below that add functionality for New Fee description • Text, text box and required field validator 9. Below that add functionality for New Fee • Text, text box, required field validator, and another validator to make sure the number is a positive decimal number

U 9 L 2 - 4 10. Below that add a button • Update button ID and button text 11. Below the button and a Label named lbl. Insert 12. Below the button and a validation summary • • Put all the validators, the button and the validation summary in the same validation group Set the validation summary to use the popup window

U 9 L 2 - 5 13. Double click the button to create a method A. Add the namespaces for database and configuration B. Create a connection object in the new method (exactly the same as in Unit 9. 1) C 1. Create a string with the following SQL statement C 2. Create a new command object C 3. Add parameters to the command object that assign values to the two parameters used in the SQL Parameters were first used in Unit 8 while using the WHERE button of the Sql. Data. Source

U 9 L 2 - 6 – Create a try/catch block – Inside the try Caution! Use your own command name here • Open the connection • Enter the following line of code • On the following line, check to how many records were changed: – if (int. Cnt == 1) (display a message saying “one records were added”) – else if (int. Cnt > 1) (display a message saying “records were added”) – else (display a message saying “no records were added”) • Close the try block

U 9 L 2 - 7 – Write the catch statement in the form – Inside the catch block, write a message to the label saying you were unable to insert the record and then display the standard Exception message found in ex. Message – Close the catch block, and create a finally block – Close the connection in the finally block – Databind the Grid. View – Set the Selected. Index of the Grid. View to -1 – Clear the text boxes

U 9 L 2 - 8 14. Test your page to be sure you can insert new fees 15. Link the page to your portfolio page 16. Upload your ASPPub to ASPNET 17. Put a link to your portfolio page in the dropbox AFTER you test to be sure that everything still works correctly on ASPNET