DBCreateTableOperation.cs :  » SQL-Clients » Database-Commander » YariSoft » DBUtil » C# / CSharp Open Source

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace YariSoft.DBUtil{
  public class DBCreateTableOperation : DBSchemaOperation
  {
    #region Local variables
    private DataTable sourceTable = null;
    #endregion

    #region Constructor/Destructor
    public DBCreateTableOperation( OleDbConnection Connection, DataTable SourceTable )
    {
      this.connection = Connection;
      this.sourceTable = SourceTable;
    }
    #endregion

    #region Public functions
    public override bool PrepareSQL()
    {
      bool status = false;
      if( this.connection.Provider.ToUpper().Substring( 0, 8 ) == "SQLOLEDB" ){
        status = this.PrepareMSSQLString();
      } else {
        YariSoft.Utils.YMessages.Show( "Unsupported OLE DB data provider!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
      }
      return status;
    }
    #endregion

    #region Private functions
    private bool PrepareMSSQLString()
    {
      string tempSQL = "CREATE TABLE [" + this.sourceTable.TableName + "]( ";
      foreach( DataColumn column in this.sourceTable.Columns ){
        tempSQL  += column.ColumnName + " ";
        if( column.DataType == typeof ( System.String ) ){
          if( column.MaxLength > 1000 ){
            tempSQL  += "text ";
          } else {
            tempSQL  += "varchar(" + column.MaxLength + ") ";
          }
        } else if ( column.DataType == typeof ( System.Int32 ) ) {
          tempSQL  += "int ";
        } else if ( column.DataType == typeof ( System.DateTime ) ) {
          tempSQL  += "datetime ";
        } else if ( column.DataType == typeof ( System.Byte ) || column.DataType == typeof ( System.Boolean ) ) {
          tempSQL  += "bit ";
        } else if ( column.DataType == typeof ( System.Single ) ) {
          tempSQL  += "real ";
        } else if ( column.DataType == typeof ( System.Int32 ) ) {
          tempSQL  += "int ";
        } else if ( column.DataType == typeof ( System.Byte[] ) ) {
          tempSQL  += "image ";
        } else if ( column.DataType == typeof ( System.Decimal ) ) {
          tempSQL  += "money ";
        } else if ( column.DataType == typeof ( System.Int16 ) ) {
          tempSQL  += "smallint ";
        }

        if( column.AllowDBNull ){
          tempSQL  += "NULL ";
        } else {
          tempSQL  += "NOT NULL ";
        }

        if( column.AutoIncrement ){
          long autoIncrementSeed = column.AutoIncrementSeed;
          if( autoIncrementSeed == 0 ){
            autoIncrementSeed ++;
          }
          tempSQL  += "IDENTITY (" + autoIncrementSeed.ToString() +","+ column.AutoIncrementStep.ToString()+") ";
        }
        tempSQL  += ", ";
      }
      tempSQL = tempSQL.Substring ( 0, tempSQL.Length - 2 ) + ")";
      this.strSQL = tempSQL;

      bool status = this.PrepareMSSQLPrimaryKeys();
  
      return status;
    }

    private bool PrepareMSSQLPrimaryKeys()
    {
      string tempSQL ="";
      DataColumn[] primaryKeys = this.sourceTable.PrimaryKey;
      if( primaryKeys != null && primaryKeys.Length > 0 ){
        tempSQL  = "ALTER TABLE [" + this.sourceTable.TableName + "] ADD CONSTRAINT ";
        tempSQL += "PK_" + this.sourceTable.TableName.Replace( ' ', '_' ) + " PRIMARY KEY ( ";
        foreach ( DataColumn column in primaryKeys ){
          tempSQL  += column.ColumnName + ", ";
        }
        tempSQL = tempSQL.Substring ( 0, tempSQL.Length - 2 ) + ")";
        this.strSQL += "\r\n" + tempSQL;
      }
      return true; 
    }
    #endregion
  }
}