This tutorial describes how to create tables, stored procedures and other objects at SQL Server.

In this walkthrough:

• Requirements
• General information
• Using dotConnect for SQL Server design time functionality
• Using Server Explorer
• Additional information

Requirements

In order to create database objects you have to connect to a server. This process is described in detail in the tutorial Logging onto the server.

General information

Database objects are created using Data Definition Language (DDL), which is a part of SQL. The DDL statements can be executed on a server by account that has necessary privileges.

There are two ways to manipulate a database. You can build DDL statements manually and run them within a component like SqlCommand or SqlScript. SqlCommand suits fine for creating objects one by one, while SqlScript is designed for executing series of DDL/DML statements. Another way is to use Visual Studio Server Explorer that provide graphical user interface to manage databases. We will discuss both ways.

Using dotConnect for SQL Server design time functionality

1. Launch Visual Studio.
2. Create new windows application and place SqlConnection and SqlScript components onto the form designer.
3. Click on SqlConnection's smart tag and choose Connect option. Fill in connection string attributes and press Connect button.
4. Fill in the Connection property of SqlScript component with the name of SqlConnection component.
5. Click on SqlScript's smart tag and choose ScriptText option.
6. In the appeared window type the following query:

   CREATE DATABASE demo;
   CREATE TABLE demo.dbo.dept (
     deptno INT PRIMARY KEY,
     dname VARCHAR(14),
     loc VARCHAR(13)
   );

   and press Execute button. This will create first of the tables we use for tutorial purposes.
7. Run the following query:

   CREATE TABLE demo.dbo.emp (
     empno INT PRIMARY KEY,
     ename VARCHAR(10),
     job VARCHAR(9),
     mgr INT,
     hiredate DATETIME,
     sal FLOAT,
     comm FLOAT,
     deptno INT REFERENCES dept
   );

   This is another table.
8. These two tables are enough to demonstrate basic functionality.

Using Server Explorer

Things are much simpler when you control them visually. The same statements can be executed without writing a line of code. This section describes how to manipulate database objects visually in Server Explorer.

1. Open the Server Explorer toolwindow if it is not open yet. To do this, in View menu click Server Explorer item.
2. Click on the Connect to Database button on the toolbar.
3. In the Add Connection dialog specify connection parameters and click OK.
4. Expand a database and right-click on Tables node.
5. Choose Add New Table from popup menu.
6. In the Column Editor describe the columns and press Ctrl+S.
7. In the appeared Choose Name dialog enter a name for the table: dept and press OK.
8. Repeat steps from 4 to 7 for the table emp.
9. The tables will be created in the database.

Additional information

Actually there are lots of ways to create tables on server. Any tool or component that is capable of running a SQL query, can be used to manage database objects. For information on DDL statements syntax refer to SQL Server documentation.

See Also

dotConnect for SQL Server Tutorials  | SqlCommand Class  | SqlScript Class