Basics of Database Manager

Walkthrough Goal

This walkthrough will guide you through the process of setting up and using DBManager in a hosted environment. We’ll cover provisioning a database, adding a table, managing primary keys, managing foreign keys, managing data in the table and executing custom SQL commands.

 

Prerequisites

This walkthrough requires the following prerequisites:

• Administration Pack Technical Preview
• SQL Server installed on a server accessible from the web server.
• SQL Server may be installed on the same machine as the web server and for this walkthrough we will assume it is.

 

Database Provisioning

To provision a database for a customer you’ll want to create a user account and a database for the customer. Give the user db_ddladmin, db_datawriter and db_datareader permissions on the database. This can easily be done using the ‘sp_addrolemember’ stored procedure in SQL Server.  For the purposes of this walk through we’ll assume you named the database ‘CustomerDatabase’, the customer’s database user name is ‘CustomerName’ and the database password is ‘CustomerPassword’.

 

Adding a Connection String

Assuming you have already provisioned a web site named ‘Customer Web Site’ for the user the next step is to add a new connectionString to his web site using the database user name and password. This can be done though the Inetmgr UI by navigating to the customers website and using the ‘Connection Strings’ feature to add the connection string.

 It’s also a good idea to lock the connectionStrings section so the customer can’t create any additional connection strings but this is entirely optional.  You can lock the connectionStrings section for all customers by using the ‘Feature Delegation’ feature of the UI (available at the server level) to set the connectionString section to ‘Not Delegated’ or for a particular website using appcmd.exe. appcmd.exe lock config -section:connectionStrings "Default Web Site" -commit:MACHINE/WEBROOT

 

Getting Started Managing SQL Server

Now we are going to switch roles and we will be the customer that is connecting to the server and see the experience they get.

1.  Connect to ‘Customer Web Site’ using Inetmgr.

2.  Click on the ‘Database Manager’ feature.

3.  Expand the node corresponding to the connection string you added in the last step.Now you’re ready to manage the database. For the below scenario pretend you’re setting up a database to track customer orders. You’ll have one table for customer information and one table for order information.

  

Adding a Table and a Primary Key

1.  Right click on the ‘Tables’ folder and select New Table. A new tab will appear to the right of the connection strings pane. This will be where we create our table.

2.  Click on the new column icon to the far left of the tool strip in the newly opened tab. Use the property grid editor to create a column named ‘CustomerID’ that is of data type int.

3.  Create another column title ‘CustomerName’.

4.  Set the “Data Type” to varchar and the length to 255.

5.  Click on the ”Indexes/Keys” button the tool strip.

6.  Click ‘Add’ to add a new index or primary key

7.  The ‘CustomerID’ column should already be selected under the ‘Columns’ property. Change ‘Type’ property to ‘Primary Key’. Name the primary key under Identity to ‘PK_Customers’ and click the ‘Close’ button.

8.  Click on the “Save Table” button in the tool strip. This will pop up a box asking for the table name. Name the table Customers and click OK.

9.  The tab containing the table designer will close and the tables node will reload to reflect the new table.

10.  Create another table named ‘Orders’ with columns ‘CustomerID’ of type int, and ‘OrderDescription’ of type varchar.

Creating a Foreign Key Relationship

Now we’d like to create a foreign key relationship between the ‘CustomerID’ column of the ‘Orders’ table and the ‘CustomerID’ column of the ‘Customers’ table.

1.  Double click on the ‘Orders’ table to start editing the table definition or select ‘Edit Table Definition’ from the right-click context menu.

2.  Click on the Relationships button in the tool strip.

3.  Click the Add button to add a new relationship and expand the ‘Tables and Columns Specification’ property.

4.  Click on the ‘…’ button to the right of the property.

5.  In the ‘Primary key table’ drop down select ‘Customers’. The tool will automatically populate the left side of the table below with the columns that make up the primary key for the ‘Customers’ table. In this case that is the ‘CustomerID’ column.

6.  In the drop down to the right of the ‘CustomerID’ column drop down select the column that you would like to associate with the ‘CustomerID’ column. In this case that would be the ‘CustomerID’ column.

7.  Click ‘OK’. Notice the tables and columns you selected now populate the ‘Tables and Columns Specification’ property. Click ‘Close’ to dismiss the dialog.

8.  Click the ‘Save Table’ button.

  

Adding and Editing Rows to a Table

The next step is to add some customers to our customer table.

1.  Right click on the ‘Customers’ table and select ‘Show Table Data’

2.  Click in the ‘CustomerID’ field of the new row. Enter ‘1’ for the ID and ‘Alice’ for the name.

3.  Click on the ‘CustomerID’ field of the new row that appeared below the first row. Enter ‘2’ for the ID and ‘Bob’ for the name.

To edit any row simply edit the value of any cell.  The edit will be committed once focus leaves the row.

Enter some rows into the ‘Orders’ table as well. Notice the foreign key constraint will be enforced and you will receive an error if you try to add or edit a row that contains a ‘CustomerID’ not corresponding to a row in the ‘Customers’ table.

  

Executing Custom SQL Commands

Let’s view all the orders and the names of the people that made the order.

1.  Right click on the database icon or the ‘Tables’ folder and select ‘New Query’.

2.  In the text area enter:

SELECT Customers.CustomerName, Orders.OrderDescription

FROM Customers, OrdersWHERE Customers.CustomerID=Orders.CustomerID

3.  Click the ‘Execute’ button in the tool strip

The results of the query are returned in the lower half of the pane.

Summary

You have provisioned a database for a customer, created tables similar to what a customer would use, managed the data in the database and executed custom queries against the data.

Comments