Introduction to Structured Query Language SQL

1. Introduction to Structured Query Language SQL

2. SQL Select Command SELECT * FROM tableName WHERE criteria;

3. Creating A String Containing SQL Select Command • Assuming the CID is selected from a list box: • string strSQL = "select * from customer where cid='" + listBox1.SelectedItem + "'";

4. SQL Insert Command INSERT INTO tableName VALUES (field values separated by commas); Ex 1. Customer table with CID, CNAME, CITY, RATING. INSERT INTO CUSTOMER VALUES (‘C1’, ‘SMITH’, ‘SF’, ‘A’); Ex 2. Orders table with OID, OrderDate, CID, SalesPerson INSERT INTO ORDERS VALUES (‘O11’, #9/28/02#, ‘C1’, ‘Peter’);

5. Creating A String Containing SQL Insert Command Assuming the four fields of the new customer record are entered in textboxes: string strSQLInsert; strSQLInsert = "Insert into Customer values ('"; strSQLInsert += textBox1.Text + "','" + textBox2.Text + "','"; strSQLInsert += textBox3.Text + "','" + textBox4.Text + "')";

6. String.Format Method • http://sharpertutorials.com/string-formatting/ String sqlInsert= String.Format(“insert into customer values (‘{0:s}’,’{1:s}’,’{2:s}’,’{3:s}’)”,textbox1.text,textbox2.text,textbox3.text,textbox4.text);

7. SQL Delete Command DELETE FROM tableName WHERE criteria; Ex 1. Delete a record from the Customer table. DELETE FROM CUSTOMER WHERE CID = ‘C1’;

8. Creating A String Containing SQL Delete Command Assuming the deleted record’s ID is selected from a list box: string strSQL = "delete from customer where cid = '" + listBox1.SelectedItem + "'";

9. SQL Update Command UPDATE tableName SET field = new value WHERE criteria; Ex. UPDATE CUSTOMER SET RATING = ‘A’ WHERE CID=‘C1’;

10. Creating A String Containing SQL Update Command Assuming the CID is selected from a list box, and the new rating is entered in a text box: string strSQL = "Update customer set rating = '" + newRating + "' where cid='" + listBox1.SelectedItem + "'";