1 / 54

Working with MS SQL Server

Working with MS SQL Server. Textbook Chapter 14. Objectives. You will be able to Use Visual Studio for GUI based interactive access to a Microsoft SQL Server database. Populate a table with data from an Excel worksheet.

jeb
Télécharger la présentation

Working with MS SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Working with MS SQL Server Textbook Chapter 14

  2. Objectives You will be able to • Use Visual Studio for GUI based interactive access to a Microsoft SQL Server database. • Populate a table with data from an Excel worksheet. • Use sqlcmd for command line interactive access to a Microsoft SQL Server database. • Using either Visual Studio or sqlcmd to: • Create and delete tables. • Insert new rows into tables. • Modify table definitions. • Modify existing rows. • Retrieve and display table data.

  3. CoE Resources • We all have accounts, with a single database each, on a Microsoft SQL Server on the CoE network: • scorpius.eng.usf.edu • Classroom and lab computers can access scorpius directly. • A VPN connection is required for your home computer or wireless laptop to access scorpius. • or maybe not for wireless access on campus

  4. The Database Server • This is the same machine that we use as a web server. • Different server software. • Different user accounts. • Be sure to use the username and password that you have been given for the SQL Server • Same username as for the web server (wpusrxx). • Different password.

  5. Using Visual Studio • Visual Studio includes database access functions. • View "Server Explorer" • Set up a Data Connection • Visual Studio 2010 will not work with scorpius. • The database server version on scorpius is too old! • We have to use Visual Studio 2008.

  6. Download Visual Studio 2008 • If you don't have Visual Studio 2008 on your computer, you can get a free 90 day trial from Microsoft. • http://www.microsoft.com/en-us/download/details.aspx?id=3713 • Very large download! • .iso file • Can't be installed directly. • Must use Virtual CD program or burn a real CD • See http://msdn.microsoft.com/en-us/subscriptions/aa948864.aspx • OR • https://www.dreamspark.com/

  7. Adding a Data Connection in Visual Studio 2008 Right click on Data Connections and select Add Connection.

  8. Adding a Data Connection in Visual Studio 2008 You will see this the first time you add a data connection.

  9. Adding a Data Connection Your SQL Server Username Your SQL Server Password

  10. Test the Connection Click here

  11. Adding a Table • We will add a table to the database. • Populate the table with data from an Excel spreadsheet. • Define the table in Visual Studio. • Download the table as a CSV file. • Open the spreadsheet in Excel. • Copy the spreadsheet data in Excel. • Paste the data into the database table in Visual Studio. • Modify the table definition to add a unique ID column.

  12. The Addresses Table • We will create a new table to hold the addresses in file Addresses.csv. http://www.cse.usf.edu/~turnerr/Web_Application_Design/Downloads/ File Addresses.csv

  13. Adding a Table

  14. Adding a Table

  15. Data Types • Common SQL Data Types • int • nvarchar(n) n = max number chars • char(n) n = number chars • decimal (p,s) p = total number of digits s = number decimal places • datetime • Many more!

  16. Define the Columns

  17. Save the Table Definition Save

  18. Table "Addresses" is Now Present

  19. Viewing Table Data Right click on Addresses and select “Show Table Data”.

  20. Viewing Table Data Table is currently empty.

  21. Populating a Table • Download file Addresses.csv from the Downloads area of the class web site: • http://www.cse.usf.edu/~turnerr/Web_Application_Design/Downloads/Addresses.csv • Double click to open in Excel

  22. Excel Worksheet

  23. Populating the Database Table • We will use the contents of this Excel worksheet to populate the Address table in the database. • Click inside the worksheet, then press Ctrl-A to select all. • Press Ctrl-C to copy the entire worksheet to the clipboard.

  24. Populating the Database Table Click here to select all of the table. Press Ctrl-V to paste the clipboard into the table. (This may take a while to complete.)

  25. Setting an ID Field • It is good practice to include an ID field for every row of a database table. • Unique identifier • Normally an Identity column. • Value set by server as each row is added • Not present in the Excel worksheet.

  26. Open Table Definition Right click on Addresses and select Open Table Definition. Add column ID as int.

  27. Add Column ID Select row and drag to the top.

  28. Set Column Properties Make ID an identity column.

  29. Set Primary Key Right click on ID and select Set Primary Key.

  30. Save

  31. Show Table Data End of Section

  32. The sqlcmd Utility • Command line utility for MS SQL Server databases. • http://msdn.microsoft.com/en-us/library/ms162773.aspx • Previous version called osql • Available on classroom and lab PCs. • In Windows command window • Connect to a Database Server • Enter SQL commands on the command line. • Results output to the console.

  33. Getting and Installing sqlcmd • Included with Management Studio Express. • Free download from Microsoft • http://www.microsoft.com/en-us/download/details.aspx?id=8824 • In Visual Studio Help, search for sqlcmd. • Or just google sqlcmd

  34. The SQL Language • The following slides demonstrate SQL commands using the sqlcmd program. • Remember SQL is used everywhere we interact with a database server: • Command line • Database management programs • Our own programs

  35. Using sqlcmd MyUsername The Server Password entered here Execute commands in buffer

  36. SQL Command Line Example • View a subset of the columns.

  37. Retrieve Specific Entries Find all addresses with zip code 33707

  38. Wildcard Selection Find all addresses with last name beginning with S

  39. Wildcard Selection All addresses with last name containing an s

  40. Case Sensitivity • SQL commands and keywords are not case sensitive. • Data is case sensitive • Characters are stored as upper case or lower case. • Sorting order is a mode. • Default is not case sensitive • Search comparisons same as sorting order.

  41. Case Sensitivity

  42. Sorting the Output Zip_Code is a string. So sort is lexicographic, not numeric.

  43. Zip Codes Zip codes are sorted legicographically, not numerically.

  44. Counting Rows How many addresses are there with zip code 33707?

  45. Updating Records

  46. Updating Multiple Fields

  47. Deleting a Record

  48. SQL Commands in Visual Studio • We can also use Visual Studio to issue arbitrary SQL commands to the server. • On the Data menu select New Query

  49. SQL Commands in Visual Studio

  50. SQL Commands in Visual Studio Click here

More Related