1 / 41

Working with MS SQL Server

Working with MS SQL Server. 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.

glen
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

  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 • Create and delete tables. • Modify table definitions. • Insert new rows into tables. • Modify existing rows. • Retrieve and display table data.

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

  4. VPN Software Download VPN client software can be downloaded from Academic Computing: http://vpn.usf.edu Caution: Some network oriented software on your computer may not work correctly while you have the VPN connection in place. Example: Sending Email

  5. Setting Up a VPN Connection

  6. Setting Up a VPN Connection

  7. Setting Up a VPN Connection Click Start

  8. The Connection is Up This is Windows XP. The window will be different on other systems, but you should see the same information. If you don't get a window similar to this, your connection has not been established.

  9. Using Visual Studio 2008 • Visual Studio 2008 includes database access functions. • View "Server Explorer" • Set up a Data Connection

  10. Data Connections in Visual Studio 2008 Right click on Data Connections and select Add Connection.

  11. Adding a Data Connection

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

  13. Test the Connection Click here

  14. Adding a Table • We will create a new table to hold the addresses in file Addresses.csv.

  15. Adding a Table

  16. Adding a Table

  17. 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!

  18. Setting an ID Field • It is good practice to include an ID field for every row. • Unique identifier • Not present in the Excel worksheet.

  19. Setting an ID Field We will designate this as the “Primary ID”

  20. Setting the Primary Key Right Click here System ensures that the Primary Key is unique.

  21. Define Other Columns

  22. Save the Table Definition Save

  23. Table "Addresses" is Now Present

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

  25. Viewing Table Data Table is currently empty.

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

  27. Excel Worksheet

  28. Add ID Field • In order to use this data in the database table we need to add an ID field. • In Excel (2007) , right click on the column header “A” and select “insert” in the dropdown menu. • Adds a new column.

  29. Adding an ID Column

  30. Setting ID Values • Set the ID for the first row to 1. • Select ID on second row • Press = • Click the cell above (ID of the first row) • Click in the formula window and add “+1” • Formula window should now say =A1+1

  31. Setting ID Values Press Enter

  32. Setting ID Values

  33. Setting ID Values • Copy the formula in 2A down into all the cells below it. • Click on 2A • Shift click on 175A • Press Ctrl-d (to copy Down) • Should now see consecutive numbers 1 – 175 in the first column

  34. Addresses with IDs

  35. 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.

  36. 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.)

  37. The Table is Populated

  38. The sqlcmd Utility • Command line utility for MS SQL Server databases. • 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.

  39. Getting and Installing sqlcmd • Included with Management Studio Express. • Free download from Microsoft, • Documentation available in SQL Server 2005 Books Online • Free download from Microsoft. • In Visual Studio 2008, search for sqlcmd.

  40. 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

  41. Using sqlcmd MyUsername The Server Password entered here Execute commands in buffer Continued next class.

More Related