Creating MySQL Database Objects Using dotConnect for MySQL

This tutorial describes how to create tables, stored procedures and other objects on a MySQL server. The tutorial consists of the following sections:

Requirements

In order to create database objects you have to connect to server. This process is described in details 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 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 MySQL Command Line Client or a component like MySqlCommand. Another way is to use IDE - visual shells that provide graphical user interface to manage database. We will discuss both ways.

Using MySQL Command Line Client

1. Launch the MySQL Client and authorize yourself.
2. Type CREATE DATABASE demo; and press Enter. This will create new database with name demo, which we will use later. From now on typing a query and pressing Enter will be referred as running a query.
3. Run query USE demo;. This will allow to omit database name in later queries.
4. Run the following query:

   CREATE TABLE dept (
     deptno INT PRIMARY KEY,
     dname VARCHAR(14),
     loc VARCHAR(13)
   ) ENGINE = InnoDB;
   

   Press Enter. This will create first of the tables we'll use for tutorial purposes.

5. Run the following query:

   CREATE TABLE emp (
     empno INT PRIMARY KEY,
     ename VARCHAR(10),
     job VARCHAR(9),
     mgr INT,
     hiredate DATETIME,
     sal FLOAT,
     comm FLOAT,
     deptno INT,
     CONSTRAINT emp_fk FOREIGN KEY (deptno) REFERENCES dept (deptno)
   ) ENGINE = InnoDB;
   

6. These two tables are enough to demonstrate basic functionality. Now you can type exit to exit the MySQL Client.

Conclusion

This article describes creating MySQL tables that are used in other tutorials for dotConnect for MySQL, a high-performance ADO.NET provider from Devart with ORM support.

There are more ways to create tables on a MySQL server. Any tool or component that is capable of running a SQL query, can be used to manage database objects. For example, such dotConnect for MySQL class as MySqlCommand suits fine for creating objects one by one, while MySqlScript is designed for executing series of DDL/DML statements. For information on DDL statements syntax refer to MySQL documentation.

Back to list