Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Manage Table: A sample using CUBRID Ado.net Driver


You can use SQL statement: CREATE TABLE, DROP TABLE, ALTER TABLE and RENAME TABLE to manage the tables.

The following is a C# Sample class,  it is using CUBRID Ado.net Driver:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Common;
using CUBRID.Data.CUBRIDClient;

namespace Sample
{
    /*
     * Data column
     */ 
    public struct Table_Column
    {
        public string name;         // Column name
        public string type;         // Data type
        public bool bPrimaryKey;    // Is primary key?

        /*
         * Constructor
         */
        public Table_Column(string colName, string colType, bool primary)
        {
            this.name = colName;
            this.type = colType;
            this.bPrimaryKey = primary;
        }
    }

    /*
     * Manage Tables
     */
    class Manage_Table
    {
        // connection string
        private string _connString = "";

        /// <summary>
        /// Constuctor
        /// </summary>
        public Manage_Table()
        {
        }

        /// <summary>
        /// Get/Set the connection string
        /// </summary>
        public string ConnectionString
        {
            get { return this._connString; }
            set { this._connString = value; }
        }

        /// <summary>
        /// Create a new table
        /// </summary>
        /// <param name="tableName">The table name</param>
        /// <param name="columns">The array of column.</param>
        public void create_table(string tableName, Table_Column[] columns)
        {
            // SQL statement
            string _create = "create table " + tableName + "(";
            // foreach column
            foreach (Table_Column column in columns)
            {
                _create += column.name;
                _create += " ";
                _create += column.type;
                if (column.bPrimaryKey)
                    _create += " primary key";
                _create += ",";
            }
            _create = _create.Substring(0, _create.Length - 1);
            _create += ");";

            if (this._connString != string.Empty || this._connString.Length != 0)
            {
                // connection string is not enpty string
                try
                {
                    // connect to the CUBRID Database
                    using (CUBRIDConnection conn = new CUBRIDConnection())
                    {
                        conn.ConnectionString = this._connString;
                        conn.Open();

                        // create a new table using CUBRIDCommand class.
                        using (CUBRIDCommand cmd = new CUBRIDCommand(_create, conn))
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception exp)
                {
                    Console.WriteLine(exp.Message);
                }
            }
        }

       /// <summary>
       /// Drop a table
       /// </summary>
       /// <param name="tableName">The table name</param>
        public void drop_table(string tableName)
        {
            // SQL statement
            string _drop = "drop table if exists " + tableName + ";";
            // If you use the following SQL statement, if the table does not exist, the program will throw an exception when execute SQL.
            //string _drop = "drop talble " + tableName;
            if (this._connString != string.Empty || this._connString.Length != 0)
            {
                // connection string is not enpty string
                try
                {
                    // connect to the CUBRID Database
                    using (CUBRIDConnection conn = new CUBRIDConnection())
                    {
                        conn.ConnectionString = this._connString;
                        conn.Open();

                        // drop a table using CUBRIDCommand class.
                        using (CUBRIDCommand cmd = new CUBRIDCommand(_drop, conn))
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception exp)
                {
                    Console.WriteLine(exp.Message);
                }
            }
        }

        /*
         */ 
        public bool Is_Exists(string tableName)
        {
            int count = 0;
            // SQL statement
            string _check = "select count(*) from db_class where class_name='" + tableName.ToLower() + "';";
            if (this._connString != string.Empty || this._connString.Length != 0)
            {
                // connection string is not enpty string
                try
                {
                    // connect to CUBRID database
                    using (CUBRIDConnection conn = new CUBRIDConnection())
                    {
                        conn.ConnectionString = this._connString;
                        conn.Open();

                        // execute SQL
                        using (CUBRIDCommand cmd = new CUBRIDCommand(_check, conn))
                        {
                            count = (int)cmd.ExecuteScalar();
                        }
                    }
                }
                catch (Exception exp)
                {
                    Console.WriteLine(exp.Message);
                }
            }

            if (count > 0)
                return true;

            return false;
        }

       /// <summary>
        /// Rename the specified table
       /// </summary>
        /// <param name="oldName">the specified table name</param>
       /// <param name="newName">the new table name</param>
        public void rename_table(string oldName, string newName)
        {
            // Check the table exists
            if (Is_Exists(oldName))
            {
                // SQL statement
                string _rename = "rename table " + oldName + " to " + newName;
                if (this._connString != string.Empty || this._connString.Length != 0)
                {
                    try
                    {
                        // connect to CUBRID database
                        using (CUBRIDConnection conn = new CUBRIDConnection())
                        {
                            conn.ConnectionString = this._connString;
                            conn.Open();

                            // rename
                            using (CUBRIDCommand cmd = new CUBRIDCommand(_rename, conn))
                            {
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception exp)
                    {
                        Console.WriteLine(exp.Message);
                    }
                }
            }
        }
    }
}

NOTE: In db_class table, the table/class name is lowercase letter.In the Is_Exists function, the table name is converted to lowercase letters. 

The following code is How to use this class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            Manage_Table _manager = new Manage_Table();
            _manager.ConnectionString = "server=127.0.0.1;database=demodb;port=33000;user=public;password=";

            Table_Column[] _columns = {
                new Table_Column("col1","string", false),
                new Table_Column("col2","string", false),
                new Table_Column("col3","string", false),
            };

            _manager.create_table("T", _columns);

            _manager.rename_table("T", "T1");
            if (_manager.Is_Exists("T"))
            {
                Console.WriteLine("T");
                _manager.drop_table("T");
            }
            else if (_manager.Is_Exists("T1"))
            {
                Console.WriteLine("T1");
                _manager.drop_table("T1");
            }
            else
                Console.WriteLine("false");
        }
    }
}
comments powered by Disqus
Page info
viewed 1109 times
translations en
Author
posted last year by
kevinyang
Contributors
updated last year by
View revisions
Share this article