DDL Commands

In This Topic

DDL (Data Definition Language) commands are used to define, modify, and remove the structure of database objects such as tables. The ADO.NET Provider for Snowflake implements DbDataReader to perform DDL statements to create, delete, and alter tables in a data source.

CREATE TABLE Statements

To add new elements to a database, use CREATE TABLE commands.

C#	Copy Code
public void CreateSimpleTable() { string createDepatamentsTable = "CREATE TABLE departments (" + "department_id INT PRIMARY KEY," + "department_name VARCHAR(100) UNIQUE," + "location VARCHAR(100), manager_id INT);"; using (var con = new C1SnowflakeConnection(ConnectionString)) { con.Open(); var command = con.CreateCommand(); command.CommandText = createDepatamentsTable; var result = command.ExecuteNonQuery(); Console.WriteLine("Table created successfully \n\n"); } }

Copy Code

public void CreateSimpleTable()
 {
    string createDepatamentsTable = "CREATE TABLE departments (" +
                                             "department_id INT PRIMARY KEY," +
                                             "department_name VARCHAR(100) UNIQUE," +
                                             "location VARCHAR(100), manager_id INT);";
    using (var con = new C1SnowflakeConnection(ConnectionString))
    {
        con.Open();
        var command =  con.CreateCommand();
        command.CommandText = createDepatamentsTable;
        var result = command.ExecuteNonQuery();
        Console.WriteLine("Table created successfully \n\n");
    }
 }

DROP TABLE Statements

To remove a table and all its associated data from a data source, utilize DROP TABLE statements.

C#	Copy Code
public void DropTable() { string sql = @"DROP TABLE IF EXISTS DEPARTAMENTS"; using (var con = new C1SnowflakeConnection(ConnectionString)) { con.Open(); var command = con.CreateCommand(); command.CommandText = sql; var result = command.ExecuteNonQuery(); Console.WriteLine("Table successfully dropped \n\n"); } }

Copy Code

public void DropTable()
 {
    string sql = @"DROP TABLE IF EXISTS DEPARTAMENTS";
    using (var con = new C1SnowflakeConnection(ConnectionString))
    {
        con.Open();
        var command = con.CreateCommand();
        command.CommandText = sql;
        var result = command.ExecuteNonQuery();
        Console.WriteLine("Table successfully dropped \n\n");
    }
 }

ALTER TABLE Statements

Use the ALTER TABLE statement to add, delete, or modify the columns of a table.

The following query renames a table using the ALTER TABLE statement:

C#	Copy Code
public void AlterRenameTable() { var sql = @"ALTER TABLE SQLITE_DATATYPE Rename to SNOWFLAKE_DATATYPES;"; using (var con = new C1SnowflakeConnection(ConnectionString)) { con.Open(); var command = con.CreateCommand(); command.CommandText = sql; var result = command.ExecuteNonQuery(); } }

Copy Code

public void AlterRenameTable()
 {
     var sql = @"ALTER TABLE SQLITE_DATATYPE Rename to SNOWFLAKE_DATATYPES;";
     using (var con = new C1SnowflakeConnection(ConnectionString))
     {
         con.Open();
         var command = con.CreateCommand();
         command.CommandText = sql;
         var result = command.ExecuteNonQuery();
     }
 }

The following query adds a new column with data type int to the table:

C#	Copy Code
public void AlterTableAddColumn() { var sql = @"ALTER TABLE SNOWFLAKE_DATATYPES ADD COLUMN new_alter_column NUMBER(38,0) DEFAULT 10;"; using (var con = new C1SnowflakeConnection(ConnectionString)) { con.Open(); var command = con.CreateCommand(); command.CommandText = sql; var result = command.ExecuteNonQuery(); Assert.AreEqual(1, result); } }

Copy Code

public void AlterTableAddColumn()
 {
     var sql = @"ALTER TABLE SNOWFLAKE_DATATYPES ADD COLUMN new_alter_column NUMBER(38,0) DEFAULT 10;";
    using (var con = new C1SnowflakeConnection(ConnectionString))
    {
        con.Open();
        var command = con.CreateCommand();
        command.CommandText = sql;
        var result = command.ExecuteNonQuery();
        Assert.AreEqual(1, result);
    }
 }

The following query removes the new_alter_column column:

C#	Copy Code
public void AlterTableDropColumn() { string alterTableQuery = @"ALTER TABLE SNOWFLAKE_DATATYPES DROP COLUMN new_alter_column;"; using (var con = new C1SnowflakeConnection(ConnectionString)) { con.Open(); var command = con.CreateCommand(); command.CommandText = sql; var result = command.ExecuteNonQuery(); } }

Copy Code

public void AlterTableDropColumn()
{
    string alterTableQuery = @"ALTER TABLE SNOWFLAKE_DATATYPES DROP COLUMN new_alter_column;";
    using (var con = new C1SnowflakeConnection(ConnectionString))
    {
        con.Open();
        var command = con.CreateCommand();
        command.CommandText = sql;
        var result = command.ExecuteNonQuery();
    }
}