This tutorial describes how to create tables, stored procedures and other objects at SQLite server.

In this tutorial:

• Requirements
• General Information
• Creating Database Object
• Inserting Data into Table
• Additional Information

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 SQL statements and run them within our components like SQLiteCommand or SQLiteScript (design-time). Another way is to execute them from your code (run-time). In this walkthrough we will create a table via design-time and input some data into it using run-time.

Creating Database Object

1. Create the SQLiteConnection object in design-time ? drag and drop it from Toolbox on the Form Designer, assign its properties and connect to your database (tutorial Logging Onto The Server).

2. Put also the SQLiteScript component to your Form Designer. Click the tip on it and assign its Connection property to the SQLiteConnection object that you have created before.

3. Click on the ScriptText property and input the following DDL in the window of SQLiteScript Editor:

   CREATE TABLE DEPT(
     DEPTNO INTEGER PRIMARY KEY,
     DNAME VARCHAR(14),
     LOC VARCHAR(13)
   );

4. This table is enough to demonstrate basic functionality. Now press the Execute script button to create the table in your database.

Inserting Data into Table

The following code fragment executes the query:

SQLiteConnection conn = new SQLiteConnection(@"Data Source=D:\TestApplication\database.db;FailIfMissing=True;");
SQLiteCommand cmd = new SQLiteCommand();
cmd.CommandText = "INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (10,'Accounting','New York')";
cmd.Connection = conn;
conn.Open();
try {
  int aff = cmd.ExecuteNonQuery();
  MessageBox.Show(aff + " rows were affected.");
}
catch {
  MessageBox.Show("Error encountered during INSERT operation.");
}
finally {
  conn.Close();
}

Dim conn As SQLiteConnection = New SQLiteConnection("Data Source=D:\TestApplication\database.db;FailIfMissing=True;")
Dim cmd As SQLiteCommand = New SQLiteCommand()
cmd.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (10,'Accounting','New York')"
cmd.Connection = conn
conn.Open()
Try
  Dim aff As Integer = cmd.ExecuteNonQuery()
  MessageBox.Show(aff & " rows were affected.")
Catch
  MessageBox.Show("Error encountered during INSERT operation.")
Finally
  conn.Close()
End Try

The sample first creates a connection with hardcoded connection string. Then it creates SQLiteCommand object, assigns the query text and connection to the SQLiteCommand instance. Connection is opened then. The ExecuteNonQuery() method of SQLiteCommand runs SQL statement in the CommandText property and returns number of rows affected by the query. This method is not intended to run SELECT statements.

If the query is executed successfully you are notified about number of affected rows. If some error occurs you get the error message. The connection is closed anyway. It is recommended that you use try ... finally clauses to make sure the connections are closed properly.

After this, in the same way insert some more records to the Dept table. These records will be used in the latter tutorial Retrieving and Modifying Data. Execute the following SQL statements:

INSERT INTO dept (deptno, dname, loc) VALUES (20,'Sales','Dallas');
INSERT INTO dept (deptno, dname, loc) VALUES (30,'Sales2','Chicago');

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 example, SQLiteCommand suits fine for creating objects one by one, while SQLiteScript is designed for executing series of DDL/DML statements. For information on DDL statements syntax refer to SQLite documentation.

See Also

Getting Started  | SQLiteCommand Class  | SQLiteScript Class