Tutorial Centre

In the last tutorial we covered connecting to a SQL Server and creating our database, ContactManager. The next step in the process is to create a table in which we can store the names and primary details of our contact. Using the Management Studio the process of creating a new table is fairly simple, and everything is managed via the GUI.

A table can be compared to a file, it represents a single container within a database used to store data. The data is stored in the form of columns, each representing a type of data to be stored, with a descriptive name. The data itself is stored as rows inside the table, each mapped to the correct column, and effectively creating a grid.

The first step is to create and open the table designer for the new table we need. The table will be called Contact, but it will only be named once we save the table and have added all the required columns.

1. Expand the ContactManager database in the Object Explorer using the + next to it.
2. Click on the Tables heading to select it.
3. Right Click on the Tables heading and select New Table from the drop down list.
4. A new window will open up with 3 columns: Column Name, DataType and Allow Nulls

For our contacts table we will require four columns initially. Before going into the creation of the columns, let's focus on DataType's. Each column has a format, which is defined by the type of information required to be stored. The most common type of data is text, and SQL provides a few different ways to store the data. Storage of data is an important factor to define the amount of space it will take up in the database. A detailed breakdown of data types in SQL can be found here.

For this tutorial we will be focussing on 3 specific data types: UNIQUEIDENTIFIER, NVARCHAR and CHAR. The first is effectively a 16-Byte GUID and the latter two can be used to store characters. The big difference between NVARCHAR and CHAR is the way it stores characters. NVARCHAR will always store the exact characters up to the maximum column size, and CHAR will store the exact number of characters based on the column size. Therefore a CHAR(3) will always store 3 characters, even if only one character is added.

The first field to be created is the Primary Key for the table. The primary key allows each individual row to be easily and uniquely recognised. In most case auto incrementing INT fields are used as primary keys, however GUID's have become a default for Microsoft. We will explore this in more detail when looking into implementing membership for the Contact Manager application.

1. Column Name: ContactID
2. Data Type: UNIQUEIDENTIFIER
3. Allow Nulls: Unticked
4. In the Column Properties / General look for Default Value or Binding and set it to NEWID()

Step 4 will ensure that the ID is generated by the server automatically when a new record is created, eliminating the need for generating the ID in code. NEWID() is a SQL Server function that create a random GUID. This does however add an extra step to the process when adding rows into the database, which will be covered in the relevant tutorial.

Repeat steps 1 - 3 until the grid matches the sample below.

The final step in the process is to save the table.

1. Click on File - Save Table_x
2. Enter Contact as the name
3. Click on OK.

If all went well the top tap will change to Table - dbo.Contact and the table will appear under the Table heading in the Object Explorer.

Have fun until next time!

The main purpose of the Contact Manager is to store information regarding contacts. To be able to do this we need to store the data in some sort of data source. A data source can be one of many things including text files, XML files, SQL databases, MySQL databases and many more database and file formats.

For these tutorials we will be using SQL Server Express to store the data in, using a SQL Database. In the first tutorial we will be covering how to create the database using the visual tools provided for SQL Server Express.

There are various methods in which one can approach project design. These tutorials are not specifically targeted at a design methodology, but rather as a guideline for complete beginners to get started with Development. Projects will generally go through a complete development live cycle, including Functional and Technical Specifications phases in which decisions regarding technologies to be used and data store options will be decided.

Once you have SQL Express and SQL Management Studio Express installed from Part 1, we need to connect to the SQL Server and create the actual database which we will use to store the contact information in.

A database general consists of various tables, each containing rows of information (Similar to an Excel spreadsheet). Within the database tables can have relationships, linking different tables together (Linked sheets and cells in Excel). We can however also create read-only views to display specific information and also create Stored Procedures to manipulate the data inside these tables and apply business logic should it be required.

Step 1: Connect to SQL Server Express

1. Click on Start - Programs - SQL Server Express - SQL Server Management Studio. (The location may differ dependant on your installation)

2. A login screen represented in figure 1.1 will be displayed once the Management Studio starts.

Server Type

SQL Server 2005 provides different server types, which can be accessed via the Management Studio. These include the Database Engine, Reporting Services and Analysis Services. For this tutorial we will be connecting to the Database Engine, which is selected by default.

Server Name

The server name represents the machine we want to connect to. It is possible to connect to other SQL Servers hosted on other machines or even the Internet. We will be connecting to the local machine, referred to as LocalHost. The Studio should have the local server selected by default. Alternatively you can enter your <machinename>.

Authentication

SQL Server provides two means of authentication. SQL Server Authentication uses usernames and passwords created inside SQL Server. Windows Authentication uses the local machine user to log into the database. SQL Server Express is by default configured to allow the local machine user access and therefore we will keep the default setting of Windows Authentication for this tutorial.

3. Click on Connect.

If all goes well this will successfully connect to the SQL Server and the default Studio layout should come up. This consists of the Object Explorer on the left hand side and the Object Explorer Details pane on the right.

Should you encounter any problems with any of the steps of the tutorial please use the comment option at the bottom to post the error(s) or any questions you may have regarding the specific tutorial. Feedback will be provided to the best of the Author's knowledge and this will also provide a reference for other readers that may be experiencing the same or similar problems with certain steps in the tutorials.

Step 2: Create the Database

1. In the Object Explorer, expand the Server, and then expand Databases, if not expanded already.

2. Right Click on the Databases option and select New Database. (See Figure 1.2)

3. Give the database a name, for the purposes of the tutorial we will use ContactManager.

4. Click OK and the Object Explorer should refresh to reflect the new Database under Databases.

5. Expand Contact Manager, which will display the various options for the selected database. (See Figure 1.3)

When creating a Database using the Studio, a physical database file is created with a .mdf extension. This file is the physical data store attached to SQL Server. A second .ldf file is created, generally known as the Log File. All changes to the .mdf file is stored inside the .ldf file. These files can be backed up, either to be moved from one machine to another, or alternatively to allow for rollback should anything go wrong. A separate series of tutorials are being planned to covered the various options inside SQL Server.

All commands issued through the Studio can be run and created as actual SQL Queries. The Studio effectively generates a SQL Script in the background which is executed against the SQL Server or Database. For example, the script to create the SQL Database would be:

CREATE DATABASE [ContactManager] ON PRIMARY
( NAME = N'ContactManager', FILENAME = N'D:\Databases\MSSQL\ContactManager.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ContactManager_log', FILENAME = N'D:\Databases\MSSQL\ContactManager_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

This brings us to the end of the first tutorial. Short and sweet. In the next tutorial we will create the table we need to store the Contact details in, and work with the Table Designer inside the Management Studio. We will also look at the different data types available and what they do.

I've had a lot of questions lately around starting an ASP.Net 2.0 Website. I have been quoted often to say that anyone can develop a website using ASP.Net 2.0, and often get very strange looks from others. So I've decided to take a step back and show how simple it really is to build an application. This series of tutorials will focus on the complete development of a Contact Manager. It will cover every aspects of the development in various parts, each focus on a single important step in the process. Along with this hopefully it will also teach people some small trick around development, and design.

To complete this project we are first going to need to get the right tools. Microsoft has been kind enough to provide all the tools we need for this project free.

Microsoft Visual Web Developer Express

Built on the power of Visual Studio 2005, Web Developer provides everything that is required to build a powerful web application with. Registered users even receive some cool free components and other tools to make their experiences even better. The software can be downloaded here or alternatively you can collect a copy from your nearest Community Distribution Agent.

Microsoft SQL Server Express Edition

Built on the tried and tested SQL Server Platform, SQL Server Express offers powerful database design functionality as well as easy integration into Visual Web Developer. SQL Server is provided free of charge by Microsoft, however does have some limitations regarding database size and concurrent connections. For a development environment it provides the perfect solution for database integration. SQL Server Express is available from Microsoft here. While your there also get a copy of Microsoft SQL Server Manager Express, the graphical interface to SQL Server which will allow you to create, view, and manage database created on SQL Server Express. The Manager is available for download here and both are available for from your nearest Community Distribution Agent.

Once we have all the required tools downloaded and installed we can start developing the system. The tutorial series will be broken up as follows:

• Creation of Contact Manager Database
• Creation of Database Tables
• Creation of Views
• Creation of Contact List Page (With AJAX)
• Creation of Contact Add Page (With AJAX)
• Creation of Stored Procedures

Further updates to this will be made as the series progresses. So while everyone runs off to get their bits and pieces together, Part 1 will be made ready available shortly!

With the huge amount of push Microsoft has been doing regarding the training of beginners and juniors, Dotnet.org.za decided to join the campaign by starting it's own set of Tutorials. The reason for keeping this blog separate was simply to have a central, well tagged area on the site where visitors can easily search and find specific tutorials for specific technologies. The idea is to have various members of the site starts a tutorial or series of tutorials regarding a particular technology, and in very simple step by step guides, explain to beginners and intermediate developers how to build their own application, website or even model car. Something similar to the old 4GuysFromRolla and other such sites from the past.

The first series of tutorials will be related to ASP.Net 2.0 and AJAX. We will be building an ASP.Net 2.0 AJAX Enabled Contact Manager from the ground up using Visual Studio Web Express and SQL Server Express Edition. Armand has offered to provide the same tutorial side by side using Ruby on Rails. We also hope in the process that other members will come forward and start similar tutorials on other technologies or languages, making information sharing and teaching, the core goal.

So with no further ado, I announce the Tutorial Centre open!