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.
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.
CoE Resources
We all have accounts, with a single database each, on a Microsoft SQL Server on the CoE network:
[Link]
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
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.
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.
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.
[Link]
Very large download! .iso file
Can't be installed directly. Must use Virtual CD program or burn a real CD See [Link]
OR
[Link]
Adding a Data Connection in Visual Studio 2008
Right click on Data Connections and select Add Connection.
7
Adding a Data Connection in Visual Studio 2008
You will see this the first time you add a data connection.
Adding a Data Connection
Your SQL Server Username
Your SQL Server Password
Test the Connection
Click here
10
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.
11
The Addresses Table
We will create a new table to hold the addresses in file [Link].
[Link] File [Link]
12
Adding a Table
13
Adding a Table
14
Data Types
Common SQL Data Types
int nvarchar(n) char(n) decimal (p,s) datetime
n = max number chars n = number chars p = total number of digits s = number decimal places
Many more!
15
Define the Columns
16
Save the Table Definition
Save
17
Table "Addresses" is Now Present
18
Viewing Table Data
Right click on Addresses and select Show Table Data. 19
Viewing Table Data
Table is currently empty.
20
Populating a Table
Download file [Link] from the Downloads area of the class web site:
[Link]
Double click to open in Excel
21
Excel Worksheet
22
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.
23
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.)
24
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.
25
Open Table Definition
Right click on Addresses and select Open Table Definition.
Add column ID as int.
26
Add Column ID
Select row and drag to the top.
27
Set Column Properties
Make ID an identity column.
28
Set Primary Key
Right click on ID and select Set Primary Key.
29
Save
30
Show Table Data
End of Section 31
The sqlcmd Utility
Command line utility for MS SQL Server databases.
[Link]
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.
32
Getting and Installing sqlcmd
Included with Management Studio Express.
Free download from Microsoft
[Link]
In Visual Studio Help, search for sqlcmd.
Or just google sqlcmd
33
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
34
Using sqlcmd
The Server
My Username Password entered here
Execute commands in buffer
35
SQL Command Line Example
View a subset of the columns.
36
Retrieve Specific Entries
Find all addresses with zip code 33707
37
Wildcard Selection
Find all addresses with last name beginning with S
38
Wildcard Selection
All addresses with last name containing an s
39
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.
40
Case Sensitivity
41
Sorting the Output
Zip_Code is a string. So sort is lexicographic, not numeric.
42
Zip Codes
Zip codes are sorted legicographically, not numerically.
43
Counting Rows
How many addresses are there with zip code 33707?
44
Updating Records
45
Updating Multiple Fields
46
Deleting a Record
47
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
48
SQL Commands in Visual Studio
49
SQL Commands in Visual Studio
Click here
50
SQL Commands in Visual Studio
Enter SQL command here.
51
SQL Commands in Visual Studio
Click here to execute the command.
52
SQL Commands in Visual Studio
Results appear below the Query window. 53
Learning SQL
Many books available One that I recommend:
Teach Yourself SQL in 24 Hours (Third Edition)
Ryan Stephens and Ron Plew, SAMS, 2003
Lots of information on the web
A sampling:
[Link] [Link] [Link] [Link]
54