Exemple #1
0
        private void SyncSequence(APDatabase db, APGenBusinessModelSection businessModel)
        {
            List <string> seqnames = new List <string>();
            DbCommand     dbCmd    = db.CreateSqlCommand("select sequence_name from user_sequences");

            using (IDataReader reader = dbCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    seqnames.Add(reader.GetString(0));
                }
            }

            foreach (APGenTable table in businessModel.Tables)
            {
                foreach (APGenColumn column in table.Columns)
                {
                    if (column.IdentityType == APColumnIdentityType.Provider &&
                        businessModel.CanIdentityRelyOnProvider(column) &&
                        column.ParsedType != typeof(Guid))
                    {
                        string seqname = SEQ_name(table.TableName, column.ColumnName).ToUpper();
                        if (!seqnames.Contains(seqname))
                        {
                            dbCmd = db.CreateSqlCommand("create sequence {0} increment by 1 start with {1} nomaxvalue nocycle nocache",
                                                        seqname, column.ProviderIdentityBase);
                            dbCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
Exemple #2
0
        private void _createTable(APDatabase db, APGenTable table)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat("create table {0} (", table.TableName);
            foreach (APGenColumn column in table.Columns)
            {
                if (column.IdentityType == APColumnIdentityType.Database)
                {
                    ThrowDBUnsupport("identityType=\"Database\"");
                }
                else
                {
                    sb.AppendFormat("\r\n\t{0} {1} {2} {3},",
                                    column.ColumnName, DBTypeName(column), DF_constraint(table, column), IsNullableString(column));
                }
            }
            sb.Length--;
            sb.Append("\r\n)");


            // create table table_name (
            //    column_name column_type [default ...] [not] null,
            //    ...
            // )

            DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());

            dbCmd.ExecuteNonQuery();
        }
		/// <summary>
		/// Synchronize database.
		/// </summary>
		/// <param name="businessModel">Business model.</param>
		public override void Sync(APGenBusinessModelSection businessModel)
		{
		   using (APDatabase db = new APDatabase(this))
		   {
		      try
		      {
					db.BeginTrans();

					foreach (APGenTable table in GetAllTables(businessModel))
					{
						_setScanTable(table.TableName);

						DbCommand dbCmd = db.CreateSqlCommand("select table_name from user_tables where temporary = 'N' and table_name = '{0}'", table.TableName.ToUpper());
						object id = dbCmd.ExecuteScalar();
						if (id == null)
							CreateTable(db, table);
						else
							ModifyTable(db, table);
					}


					SyncSequence(db, businessModel);

					db.Commit();
				}
				catch(Exception ex)
				{
					db.Rollback();

					throw new ProviderException(String.Format("Table: {0}, Column: {1}", scanTableName, scanColumnName), ex);
				}
			}
		}
Exemple #4
0
        private void SyncCurrentId(APDatabase db, APGenBusinessModelSection businessModel)
        {
            List <string> tcnames = new List <string>();
            DbCommand     dbCmd   = db.CreateSqlCommand("select {1} from {0}", currentIdTable, currentIdName);

            using (IDataReader reader = dbCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    tcnames.Add(reader.GetString(0));
                }
            }

            foreach (APGenTable table in businessModel.Tables)
            {
                foreach (APGenColumn column in table.Columns)
                {
                    if (column.IdentityType == APColumnIdentityType.Provider &&
                        businessModel.CanIdentityRelyOnProvider(column) &&
                        column.ParsedType != typeof(Guid))
                    {
                        string tcname = table.TableName + "." + column.ColumnName;
                        if (!tcnames.Contains(tcname))
                        {
                            dbCmd = db.CreateSqlCommand("insert into {0} ({1}, {2}) values ('{3}', '{4}')",
                                                        currentIdTable, currentIdName, currentIdValue,
                                                        tcname, column.ProviderIdentityBase);
                            dbCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
Exemple #5
0
 /// <summary>
 /// Map extensions.
 /// </summary>
 /// <typeparam name="T">T.</typeparam>
 /// <param name="db">APDatabase.</param>
 /// <param name="reader">IDataReader.</param>
 /// <param name="fillup">Fill function.</param>
 /// <returns>IEnumerable.</returns>
 public static IEnumerable <T> Map <T>(this APDatabase db, IDataReader reader, Func <IDataReader, T> fillup)
 {
     while (reader.Read())
     {
         yield return(fillup(reader));
     }
 }
Exemple #6
0
        private bool _isTableEmpty(APDatabase db, APGenTable table)
        {
            DbCommand dbCmd = db.CreateSqlCommand(
                @"select count(*)
from {0}", table.TableName);
            int count = Convert.ToInt32(dbCmd.ExecuteScalar());

            return(count == 0);
        }
Exemple #7
0
 /// <summary>
 /// Query extensions.
 /// </summary>
 /// <typeparam name="T">T.</typeparam>
 /// <param name="db">APDatabase.</param>
 /// <param name="rawSql">The Raw SQL expression.</param>
 /// <param name="fillup">Fill function.</param>
 /// <returns>IEnumerable.</returns>
 public static IEnumerable <T> Query <T>(this APDatabase db, string rawSql, Func <IDataReader, T> fillup)
 {
     using (IDataReader reader = db.CreateSqlCommand(rawSql).ExecuteReader())
     {
         while (reader.Read())
         {
             yield return(fillup(reader));
         }
     }
 }
Exemple #8
0
 /// <summary>
 /// Query extensions.
 /// </summary>
 /// <typeparam name="T">T.</typeparam>
 /// <param name="db">APDatabase.</param>
 /// <param name="command">The 'SELECT' command.</param>
 /// <param name="fillup">Fill function.</param>
 /// <returns>IEnumerable.</returns>
 public static IEnumerable <T> Query <T>(this APDatabase db, APSqlSelectCommand command, Func <IDataReader, T> fillup)
 {
     using (IDataReader reader = db.ExecuteReader(command))
     {
         while (reader.Read())
         {
             yield return(fillup(reader));
         }
     }
 }
Exemple #9
0
        private void _dropColumn(APDatabase db, APGenTable table, columninfo info)
        {
            if (info.dfname != "")
            {
                _dropConstraint(db, table, info.dfname);
            }

            DbCommand dbCmd = db.CreateSqlCommand("alter table {0} drop column {1}", table.TableName, info.colname);

            dbCmd.ExecuteNonQuery();
        }
Exemple #10
0
 private void _dropIndex(APDatabase db, APGenTable table, indexinfo info)
 {
     if (info.indtype != indexType.Index)
     {
         _dropConstraint(db, table, info.indname);
     }
     else
     {
         DbCommand dbCmd = db.CreateSqlCommand("drop index {0}", info.indname);
         dbCmd.ExecuteNonQuery();
     }
 }
		private void CreateTable(APDatabase db, APGenTable table)
		{
			_createTable(db, table);
			_createPrimaryKey(db, table);
			foreach (APGenIndex index in table.Indexes)
			{
				_createIndex(db, table, index);
			}
			foreach (APGenIndex unique in table.Uniques)
			{
				_createUnique(db, table, unique);
			}
		}
Exemple #12
0
 private void CreateTable(APDatabase db, APGenTable table)
 {
     _createTable(db, table);
     _createPrimaryKey(db, table);
     foreach (APGenIndex index in table.Indexes)
     {
         _createIndex(db, table, index);
     }
     foreach (APGenIndex unique in table.Uniques)
     {
         _createUnique(db, table, unique);
     }
 }
Exemple #13
0
        private Dictionary <string, columninfo> _getColumns(APDatabase db, APGenTable table)
        {
            DbCommand dbCmd = db.CreateSqlCommand(
                @"select
	col.colid,
	col.name as colname,
	tp.name as typename,
	tp.variable,
	col.length,
	col.xprec,
	col.xscale,
	col.isnullable,
	df.name as dfname,
	df.definition as dfvalue,
	id.is_identity
from
	syscolumns as col
	inner join systypes as tp on col.xtype = tp.xtype and tp.name <> 'sysname'
	left join sys.default_constraints as df on col.cdefault = df.object_id
	left join sys.identity_columns as id on col.colid = id.column_id and id.object_id = col.id
where col.id = object_id('{0}')
order by col.colid", table.TableName);

            Dictionary <string, columninfo> dbColumns = new Dictionary <string, columninfo>();

            using (IDataReader reader = dbCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int        idx  = 0;
                    columninfo data = new columninfo()
                    {
                        colid       = Convert.ToInt32(reader.GetValue(idx++)),
                        colname     = Convert.ToString(reader.GetValue(idx++)),
                        typename    = Convert.ToString(reader.GetValue(idx++)),
                        variable    = Convert.ToBoolean(reader.GetValue(idx++)),
                        length      = Convert.ToInt32(reader.GetValue(idx++)),
                        xprec       = Convert.ToInt32(reader.GetValue(idx++)),
                        xscale      = Convert.ToInt32(reader.GetValue(idx++)),
                        isnullable  = Convert.ToBoolean(reader.GetValue(idx++)),
                        dfname      = Convert.ToString(reader.GetValue(idx++)),
                        dfvalue     = Convert.ToString(reader.GetValue(idx++)),
                        is_identity = reader.IsDBNull(idx++) ? false : true,
                    };
                    data.Resolve();
                    dbColumns.Add(data.colname, data);
                }
            }
            return(dbColumns);
        }
Exemple #14
0
        private Dictionary <string, columninfo> _getColumns(APDatabase db, APGenTable table)
        {
            OracleCommand dbCmd = db.CreateSqlCommand(
                @"select
	column_id,
	column_name,
	data_type,
	data_length,
	nvl(data_precision, 0),
	nvl(data_scale, 0),
	nullable,
	nvl(default_length, 0),
	data_default
from user_tab_columns
where table_name = '{0}'
order by column_id", table.TableName.ToUpper()) as OracleCommand;

            dbCmd.InitialLONGFetchSize = -1;

            Dictionary <string, columninfo> dbColumns = new Dictionary <string, columninfo>(StringComparer.InvariantCultureIgnoreCase);

            using (OracleDataReader reader = dbCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int idx = 0;

                    columninfo data = new columninfo()
                    {
                        colid      = Convert.ToInt32(reader.GetValue(idx++)),
                        colname    = Convert.ToString(reader.GetValue(idx++)),
                        typename   = Convert.ToString(reader.GetValue(idx++)).ToLower(),
                        length     = Convert.ToInt32(reader.GetValue(idx++)),
                        xprec      = Convert.ToInt32(reader.GetValue(idx++)),
                        xscale     = Convert.ToInt32(reader.GetValue(idx++)),
                        isnullable = Convert.ToString(reader.GetValue(idx++)) == "Y",
                        dflength   = Convert.ToInt32(reader.GetValue(idx++)),
                        dfvalue    = "",
                    };
                    if (data.dflength > 0)
                    {
                        data.dfvalue = reader.GetString(idx++).Trim();
                    }
                    data.Resolve();
                    dbColumns.Add(data.colname, data);
                }
            }
            return(dbColumns);
        }
Exemple #15
0
        private void _createColumn(APDatabase db, APGenTable table, APGenColumn column, bool isTableEmpty)
        {
            bool changeNullable = (!isTableEmpty && !column.IsNullable);

            if (changeNullable)
            {
                column.IsNullable = true;
            }

            string sql;

            if (column.IdentityType == APColumnIdentityType.Database)
            {
                sql = string.Format("alter table {0} add {1} {2} IDENTITY {3} {4}",
                                    table.TableName,
                                    column.ColumnName,
                                    DBTypeName(column),
                                    IsNullableString(column),
                                    DF_constraint(table, column));
            }
            else
            {
                sql = string.Format("alter table {0} add {1} {2} {3} {4}",
                                    table.TableName,
                                    column.ColumnName,
                                    DBTypeName(column),
                                    IsNullableString(column),
                                    DF_constraint(table, column));
            }

            DbCommand dbCmd = db.CreateSqlCommand(sql);

            dbCmd.ExecuteNonQuery();

            if (changeNullable)
            {
                column.IsNullable = false;
                string defaultValue = column.DBDefaultValue == "" ? "''" : column.DBDefaultValue;

                dbCmd = db.CreateSqlCommand("update {0} set {1} = {2}", table.TableName, column.ColumnName, defaultValue);
                dbCmd.ExecuteNonQuery();

                dbCmd = db.CreateSqlCommand("alter table {0} alter column {1} {2} {3}",
                                            table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column));
                dbCmd.ExecuteNonQuery();
            }
        }
Exemple #16
0
        private void _tempSql(string sql)
        {
            DbCommand dbCmd;

            using (APDatabase dbtemp = new APDatabase(this))
            {
                try
                {
                    dbtemp.BeginTrans();
                    dbCmd = dbtemp.CreateSqlCommand(sql);
                    dbCmd.ExecuteNonQuery();
                    dbtemp.Commit();
                }
                catch
                {
                    dbtemp.Rollback();
                }
            }
        }
Exemple #17
0
        private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info)
        {
            if (info.dfname != "")
            {
                _dropConstraint(db, table, info.dfname);
            }


            DbCommand dbCmd;

            // no safe mode to change identity, so ingone this, throw sql exception.


            if (info.isnullable && !column.IsNullable)
            {
                dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName);
                if ((int)dbCmd.ExecuteScalar() > 0)
                {
                    // when column nullable change to not nullable and has data,
                    // set default value.
                    dbCmd = db.CreateSqlCommand("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, info.GetDefaultValue());
                    dbCmd.ExecuteNonQuery();
                }
            }

            dbCmd = db.CreateSqlCommand("alter table {0} alter column {1} {2} {3}",
                                        table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column));
            dbCmd.ExecuteNonQuery();


            if (column.DBDefaultValue != "")
            {
                dbCmd = db.CreateSqlCommand("alter table {0} add {1} for {2}",
                                            table.TableName, DF_constraint(table, column), column.ColumnName);
                dbCmd.ExecuteNonQuery();
            }

            // alter table table_nae alter column column_name type_name [not] null
            // go
            // alter table add constraint DF_tablename_columnname default dfvalue for column_name
            // go
        }
Exemple #18
0
        private void _createUnique(APDatabase db, APGenTable table, APGenIndex index)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat("alter table {0} add constraint {1} unique (", table.TableName, index.Name);
            foreach (APGenOrder order in index.Orders)
            {
                sb.AppendFormat("\r\n\t{0},", table.Columns[order.Name].ColumnName);
            }
            sb.Length--;
            sb.Append("\r\n)");

            // alter table table_name add constraint uq_name (
            //		column_name,
            //		...
            // )

            DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());

            dbCmd.ExecuteNonQuery();
        }
Exemple #19
0
        private void _createPrimaryKey(APDatabase db, APGenTable table)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat("alter table {0} add constraint {1} primary key (", table.TableName, PK_name(table));
            foreach (APGenColumn column in table.PrimaryKeyColumns)
            {
                sb.AppendFormat("\r\n\t{0},", column.ColumnName);
            }
            sb.Length--;
            sb.Append("\r\n)");

            // alter table tablen_ame add constraint PK_table_name primary key(
            //    column_name,
            //    ...
            // )

            DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());

            dbCmd.ExecuteNonQuery();
        }
Exemple #20
0
        private void _createIndex(APDatabase db, APGenTable table, APGenIndex index)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat("create index {0} on {1} (", index.Name, table.TableName);
            foreach (APGenOrder order in index.Orders)
            {
                sb.AppendFormat("\r\n\t{0} {1},", table.Columns[order.Name].ColumnName, order.According.ToString());
            }
            sb.Length--;
            sb.Append("\r\n)");

            // create index index_name on (
            //		column_name [ASC|DESC],
            //		...
            // )

            DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());

            dbCmd.ExecuteNonQuery();
        }
Exemple #21
0
        /// <summary>
        /// Synchronize database.
        /// </summary>
        /// <param name="businessModel">Business model.</param>
        public override void Sync(APGenBusinessModelSection businessModel)
        {
            using (APDatabase db = new APDatabase(this))
            {
                try
                {
                    db.BeginTrans();


                    foreach (APGenTable table in GetAllTables(businessModel))
                    {
                        _setScanTable(table.TableName);

                        DbCommand dbCmd = db.CreateSqlCommand("select id from sysobjects where type = 'U' and name = '{0}'", table.TableName);
                        object    id    = dbCmd.ExecuteScalar();
                        if (id == null)
                        {
                            CreateTable(db, table);
                        }
                        else
                        {
                            ModifyTable(db, table);
                        }
                    }


                    SyncCurrentId(db, businessModel);
                    //_createCurrentIdProc(db);


                    db.Commit();
                }
                catch (Exception ex)
                {
                    db.Rollback();

                    throw new ProviderException(String.Format("Table: {0}, Column: {1}", scanTableName, scanColumnName), ex);
                }
            }
        }
Exemple #22
0
        private void _createColumn(APDatabase db, APGenTable table, APGenColumn column, bool isTableEmpty)
        {
            string sql = "";

            if (column.IdentityType == APColumnIdentityType.Database)
            {
                ThrowDBUnsupport("identityType=\"Database\"");
            }
            else
            {
                sql = string.Format("alter table {0} add {1} {2} {3} {4}",
                                    table.TableName,
                                    column.ColumnName,
                                    DBTypeName(column),
                                    DF_constraint(table, column),
                                    IsNullableString(column));
            }

            DbCommand dbCmd = db.CreateSqlCommand(sql);

            dbCmd.ExecuteNonQuery();
        }
Exemple #23
0
        /// <summary>
        /// Synchronize database.
        /// </summary>
        /// <param name="businessModel">Business model.</param>
        public override void Sync(APGenBusinessModelSection businessModel)
        {
            using (APDatabase db = new APDatabase(this))
            {
                try
                {
                    db.BeginTrans();

                    foreach (APGenTable table in GetAllTables(businessModel))
                    {
                        _setScanTable(table.TableName);

                        DbCommand dbCmd = db.CreateSqlCommand("select table_name from user_tables where temporary = 'N' and table_name = '{0}'", table.TableName.ToUpper());
                        object    id    = dbCmd.ExecuteScalar();
                        if (id == null)
                        {
                            CreateTable(db, table);
                        }
                        else
                        {
                            ModifyTable(db, table);
                        }
                    }


                    SyncSequence(db, businessModel);

                    db.Commit();
                }
                catch (Exception ex)
                {
                    db.Rollback();

                    throw new ProviderException(String.Format("Table: {0}, Column: {1}", scanTableName, scanColumnName), ex);
                }
            }
        }
		/// <summary>
		/// Synchronize database.
		/// </summary>
		/// <param name="businessModel">Business model.</param>
		public override void Sync(APGenBusinessModelSection businessModel)
		{
		   using (APDatabase db = new APDatabase(this))
		   {
		      try
		      {
					db.BeginTrans();


					foreach (APGenTable table in GetAllTables(businessModel))
					{
						_setScanTable(table.TableName);

						DbCommand dbCmd = db.CreateSqlCommand("select id from sysobjects where type = 'U' and name = '{0}'", table.TableName);
						object id = dbCmd.ExecuteScalar();
						if (id == null)
							CreateTable(db, table);
						else
							ModifyTable(db, table);
					}


					SyncCurrentId(db, businessModel);
					//_createCurrentIdProc(db);


					db.Commit();
				}
				catch (Exception ex)
				{
					db.Rollback();

					throw new ProviderException(String.Format("Table: {0}, Column: {1}", scanTableName, scanColumnName), ex);
				}
			}
		}
Exemple #25
0
        private void ModifyTable(APDatabase db, APGenTable table)
        {
            Dictionary <string, columninfo> dbColumns = _getColumns(db, table);
            Dictionary <string, indexinfo>  dbIndexes = _getIndexes(db, table);
            bool isTableEmpty = _isTableEmpty(db, table);



            // analysis columns

            List <APGenColumn> listCreateColumn           = new List <APGenColumn>();
            List <APGenColumn> listModifyColumn           = new List <APGenColumn>();
            Dictionary <string, columninfo> deleteColumns = new Dictionary <string, columninfo>(dbColumns, StringComparer.InvariantCultureIgnoreCase);

            foreach (APGenColumn column in table.Columns)
            {
                _setScanColumn(column.ColumnName);

                string colname = column.ColumnName;

                if (!dbColumns.ContainsKey(colname))
                {
                    // db has not this column, wait for create.
                    listCreateColumn.Add(column);
                }
                else
                {
                    deleteColumns.Remove(colname);

                    columninfo colinfo = dbColumns[colname];

                    if (column.IsNullable != colinfo.isnullable ||
                        column.DBDefaultValue != colinfo.dfvalue ||
                        DBTypeName(column) != colinfo.GetTypeFullName()
                        // no safe mode to change identity, so ingone change this
                        // || (column.IdentityType == APColumnIdentityType.Database ^ colinfo.is_identity)
                        )
                    {
                        listModifyColumn.Add(column);
                    }
                }
            }


            // analysis indexes

            List <APGenIndex> listCreateIndex = new List <APGenIndex>();

            foreach (APGenIndex index in table.Indexes)
            {
                string ix_name = index.Name;

                if (!dbIndexes.ContainsKey(ix_name))
                {
                    // db has not this index, wait for create.
                    listCreateIndex.Add(index);
                }
                else
                {
                    // db has this index and columns according fitted, then to nothing.
                    // elsewise, wait fo modify this index.
                    // drop in db indexes residual in dbIndexes.
                    indexinfo info    = dbIndexes[ix_name];
                    string    ix_keys = IndKeys(table, index, false);

                    if (info.indkeys.Equals(ix_keys, StringComparison.InvariantCultureIgnoreCase))
                    {
                        bool maybe = false;
                        foreach (APGenColumn column in listModifyColumn)
                        {
                            if (info.columns.ContainsKey(column.ColumnName))
                            {
                                listCreateIndex.Add(index);
                                maybe = true;
                                break;
                            }
                        }
                        if (!maybe)
                        {
                            dbIndexes.Remove(ix_name);
                        }
                    }
                    else
                    {
                        listCreateIndex.Add(index);
                    }
                }
            }


            // analysis uniques

            List <APGenIndex> listCreateUnique = new List <APGenIndex>();

            foreach (APGenIndex index in table.Uniques)
            {
                string ix_name = index.Name;

                if (!dbIndexes.ContainsKey(ix_name))
                {
                    // db has not this unique, wait for create.
                    listCreateUnique.Add(index);
                }
                else
                {
                    // db has this unique, then to nothing.
                    // elsewise, wait fo modify this unique.
                    // drop in db uniques residual in dbIndexes.
                    indexinfo info    = dbIndexes[ix_name];
                    string    uq_keys = IndKeys(table, index, true);

                    if (info.indkeys.Equals(uq_keys, StringComparison.InvariantCultureIgnoreCase))
                    {
                        bool maybe = false;
                        foreach (APGenColumn column in listModifyColumn)
                        {
                            if (info.columns.ContainsKey(column.ColumnName))
                            {
                                listCreateUnique.Add(index);
                                maybe = true;
                                break;
                            }
                        }
                        if (!maybe)
                        {
                            dbIndexes.Remove(ix_name);
                        }
                    }
                    else
                    {
                        listCreateUnique.Add(index);
                    }
                }
            }

            // 1. dbIndexes for 'drop', but PK_ index analysis columns.
            // 2. listCreateIndex for 'create'.
            // 2. listCreateUnique for 'create'.


            // process

            string pkKeys = "";

            foreach (APGenColumn column in table.PrimaryKeyColumns)
            {
                if (pkKeys != "")
                {
                    pkKeys += ",";
                }
                pkKeys += column.ColumnName;
            }


            // 1. drop indexes

            bool needAddPrimary = true;

            foreach (indexinfo info in dbIndexes.Values)
            {
                if (info.indtype == indexType.Primary && info.indkeys.Equals(pkKeys, StringComparison.InvariantCultureIgnoreCase))
                {
                    needAddPrimary = false;
                }
                else
                {
                    _dropIndex(db, table, info);
                }
            }

            // 2. drop columns

            foreach (columninfo info in deleteColumns.Values)
            {
                _dropColumn(db, table, info);
            }

            // 3. modify columns

            foreach (APGenColumn column in listModifyColumn)
            {
                _alterColumn(db, table, column, dbColumns[column.ColumnName]);
            }

            // 4. create columns

            foreach (APGenColumn column in listCreateColumn)
            {
                _createColumn(db, table, column, isTableEmpty);
            }

            // 5. mayby primary key

            if (needAddPrimary)
            {
                _createPrimaryKey(db, table);
            }

            // 6. create indexes

            foreach (APGenIndex index in listCreateIndex)
            {
                _createIndex(db, table, index);
            }

            // 7. create unique

            foreach (APGenIndex unique in listCreateUnique)
            {
                _createUnique(db, table, unique);
            }
        }
		private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info)
		{
			DbCommand dbCmd;
			int totalCount, nullCount = 0;

			dbCmd = db.CreateSqlCommand("select count(*) from {0}", table.TableName, column.ColumnName);
			totalCount = Convert.ToInt32(dbCmd.ExecuteScalar());
			if (totalCount > 0)
			{
				dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName);
				nullCount = Convert.ToInt32(dbCmd.ExecuteScalar());

			}

			if (totalCount > 0)
			{
				if (!_sameType(DBTypeName(column), info.GetTypeFullName()) && nullCount != totalCount)
				{
					// change db type, the column value must be null
					if (!info.isnullable)
					{
						dbCmd = db.CreateSqlCommand("alter table {0} modify {1} null",
						  table.TableName, column.ColumnName);
						dbCmd.ExecuteNonQuery();
						info.isnullable = true;
					}
					_tempSql(String.Format("update {0} set {1} = null where {1} is not null", table.TableName, column.ColumnName));
					nullCount = totalCount;
				}
			}

			string sqlPadding = "";
			if (DBTypeName(column) != info.GetTypeFullName())
				sqlPadding += " " + DBTypeName(column);
			if (column.DBDefaultValue != info.dfvalue)
				sqlPadding += " " + DF_constraint(table, column);
			bool lazyNotNullable = false;
			if (column.IsNullable != info.isnullable)
			{
				if (column.IsNullable || nullCount == 0)
					sqlPadding += " " + IsNullableString(column);
				else
					lazyNotNullable = true;
			}
			if (sqlPadding != "")
			{
				dbCmd = db.CreateSqlCommand("alter table {0} modify {1}", table.TableName, column.ColumnName);
				dbCmd.CommandText += sqlPadding;
				dbCmd.ExecuteNonQuery();
			}

			if (lazyNotNullable)
			{
				string dfv = column.DBDefaultValue;
				if (dfv == "")
					dfv = "0";
				_tempSql(String.Format("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, dfv));

				dbCmd = db.CreateSqlCommand("alter table {0} modify {1} not null", table.TableName, column.ColumnName);
				dbCmd.ExecuteNonQuery();
			}


			// alter table table_nae modify column_name type_name default dfvalue [not] null
			// go
		}
		private void SyncCurrentId(APDatabase db, APGenBusinessModelSection businessModel)
		{
			List<string> tcnames = new List<string>();
			DbCommand dbCmd = db.CreateSqlCommand("select {1} from {0}", currentIdTable, currentIdName);
			using (IDataReader reader = dbCmd.ExecuteReader())
			{
				while (reader.Read())
				{
					tcnames.Add(reader.GetString(0));
				}
			}

			foreach (APGenTable table in businessModel.Tables)
			{
				foreach (APGenColumn column in table.Columns)
				{
					if (column.IdentityType == APColumnIdentityType.Provider
						&& businessModel.CanIdentityRelyOnProvider(column)
						&& column.ParsedType != typeof(Guid))
					{
						string tcname = table.TableName + "." + column.ColumnName;
						if (!tcnames.Contains(tcname))
						{
							dbCmd = db.CreateSqlCommand("insert into {0} ({1}, {2}) values ('{3}', '{4}')",
								currentIdTable, currentIdName, currentIdValue,
								tcname, column.ProviderIdentityBase);
							dbCmd.ExecuteNonQuery();
						}
					}
				}
			}
		}
		private void _createColumn(APDatabase db, APGenTable table, APGenColumn column, bool isTableEmpty)
		{
			string sql = "";

			if (column.IdentityType == APColumnIdentityType.Database)
				ThrowDBUnsupport("identityType=\"Database\"");
			else
				sql = string.Format("alter table {0} add {1} {2} {3} {4}",
					table.TableName,
					column.ColumnName,
					DBTypeName(column),
					DF_constraint(table, column),
					IsNullableString(column));

			DbCommand dbCmd = db.CreateSqlCommand(sql);
			dbCmd.ExecuteNonQuery();
		}
Exemple #29
0
        private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info)
        {
            DbCommand dbCmd;
            int       totalCount, nullCount = 0;

            dbCmd      = db.CreateSqlCommand("select count(*) from {0}", table.TableName, column.ColumnName);
            totalCount = Convert.ToInt32(dbCmd.ExecuteScalar());
            if (totalCount > 0)
            {
                dbCmd     = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName);
                nullCount = Convert.ToInt32(dbCmd.ExecuteScalar());
            }

            if (totalCount > 0)
            {
                if (!_sameType(DBTypeName(column), info.GetTypeFullName()) && nullCount != totalCount)
                {
                    // change db type, the column value must be null
                    if (!info.isnullable)
                    {
                        dbCmd = db.CreateSqlCommand("alter table {0} modify {1} null",
                                                    table.TableName, column.ColumnName);
                        dbCmd.ExecuteNonQuery();
                        info.isnullable = true;
                    }
                    _tempSql(String.Format("update {0} set {1} = null where {1} is not null", table.TableName, column.ColumnName));
                    nullCount = totalCount;
                }
            }

            string sqlPadding = "";

            if (DBTypeName(column) != info.GetTypeFullName())
            {
                sqlPadding += " " + DBTypeName(column);
            }
            if (column.DBDefaultValue != info.dfvalue)
            {
                sqlPadding += " " + DF_constraint(table, column);
            }
            bool lazyNotNullable = false;

            if (column.IsNullable != info.isnullable)
            {
                if (column.IsNullable || nullCount == 0)
                {
                    sqlPadding += " " + IsNullableString(column);
                }
                else
                {
                    lazyNotNullable = true;
                }
            }
            if (sqlPadding != "")
            {
                dbCmd              = db.CreateSqlCommand("alter table {0} modify {1}", table.TableName, column.ColumnName);
                dbCmd.CommandText += sqlPadding;
                dbCmd.ExecuteNonQuery();
            }

            if (lazyNotNullable)
            {
                string dfv = column.DBDefaultValue;
                if (dfv == "")
                {
                    dfv = "0";
                }
                _tempSql(String.Format("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, dfv));

                dbCmd = db.CreateSqlCommand("alter table {0} modify {1} not null", table.TableName, column.ColumnName);
                dbCmd.ExecuteNonQuery();
            }


            // alter table table_nae modify column_name type_name default dfvalue [not] null
            // go
        }
		private void _tempSql(string sql)
		{
			DbCommand dbCmd;
			using (APDatabase dbtemp = new APDatabase(this))
			{
				try
				{
					dbtemp.BeginTrans();
					dbCmd = dbtemp.CreateSqlCommand(sql);
					dbCmd.ExecuteNonQuery();
					dbtemp.Commit();
				}
				catch
				{
					dbtemp.Rollback();
				}
			}
		}
		private void _createPrimaryKey(APDatabase db, APGenTable table)
		{
			StringBuilder sb = new StringBuilder();

			sb.AppendFormat("alter table {0} add constraint {1} primary key (", table.TableName, PK_name(table));
			foreach (APGenColumn column in table.PrimaryKeyColumns)
			{
				sb.AppendFormat("\r\n\t{0},", column.ColumnName);
			}
			sb.Length--;
			sb.Append("\r\n)");

			// alter table tablen_ame add constraint PK_table_name primary key(
			//    column_name,
			//    ...
			// )

			DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
			dbCmd.ExecuteNonQuery();
		}
		private void _createTable(APDatabase db, APGenTable table)
		{
			StringBuilder sb = new StringBuilder();

			sb.AppendFormat("create table {0} (", table.TableName);
			foreach (APGenColumn column in table.Columns)
			{
				if (column.IdentityType == APColumnIdentityType.Database)
					sb.AppendFormat("\r\n\t{0} {1} identity {2} {3},",
						column.ColumnName, DBTypeName(column), IsNullableString(column), DF_constraint(table, column));
				else
					sb.AppendFormat("\r\n\t{0} {1} {2} {3},",
						column.ColumnName, DBTypeName(column), IsNullableString(column), DF_constraint(table, column));
			}
			sb.Length--;
			sb.Append("\r\n)");


			// create table table_name (
			//    column_name column_type [identity] [not] null [constraint ... default ...],
			//    ...
			// )

			DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
			dbCmd.ExecuteNonQuery();
		}
Exemple #33
0
        private Dictionary <string, indexinfo> _getIndexes(APDatabase db, APGenTable table)
        {
            OracleCommand dbCmd = db.CreateSqlCommand(
                @"select col.index_name, col.column_name, expr.column_expression, col.descend, nvl(cst.constraint_type,'C')
from user_ind_columns col
  left join user_ind_expressions expr
    on col.index_name=expr.index_name and col.table_name=expr.table_name and col.column_position=expr.column_position
  left join user_constraints cst on col.table_name=cst.table_name and col.index_name=cst.constraint_name
where col.table_name = '{0}'
order by col.index_name, col.column_position", table.TableName.ToUpper()) as OracleCommand;

            dbCmd.InitialLONGFetchSize = -1;

            Dictionary <string, indexinfo> dbIndexes = new Dictionary <string, indexinfo>(StringComparer.InvariantCultureIgnoreCase);

            using (OracleDataReader reader = dbCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    string name = Convert.ToString(reader.GetValue(0));
                    string col  = Convert.ToString(reader.GetValue(1));
                    if (!reader.IsDBNull(2))
                    {
                        col = reader.GetString(2);
                        col = col.Substring(1, col.Length - 2);
                    }
                    bool   is_descending_key = Convert.ToString(reader.GetValue(3)) == "DESC";
                    string type = Convert.ToString(reader.GetValue(4));

                    indexinfo data;
                    if (dbIndexes.ContainsKey(name))
                    {
                        data = dbIndexes[name];
                    }
                    else
                    {
                        data = new indexinfo()
                        {
                            indname = name
                        };
                        if (type == "P")
                        {
                            data.indtype = indexType.Primary;
                        }
                        else if (type == "U")
                        {
                            data.indtype = indexType.Unique;
                        }
                        else
                        {
                            data.indtype = indexType.Index;
                        }
                        dbIndexes.Add(name, data);
                    }
                    data.columns.Add(col, is_descending_key);
                }
            }
            foreach (var item in dbIndexes.Values)
            {
                item.pickKeys();
            }
            return(dbIndexes);
        }
		private void _dropIndex(APDatabase db, APGenTable table, indexinfo info)
		{
			if (info.indtype != indexType.Index)
			{
				_dropConstraint(db, table, info.indname);
			}
			else
			{
				DbCommand dbCmd = db.CreateSqlCommand("drop index {1} on {0}", table.TableName, info.indname);
				dbCmd.ExecuteNonQuery();
			}
		}
		private void _dropConstraint(APDatabase db, APGenTable table, string name)
		{
			DbCommand dbCmd = db.CreateSqlCommand("alter table {0} drop constraint {1}", table.TableName, name);
			dbCmd.ExecuteNonQuery();
		}
		private void _createUnique(APDatabase db, APGenTable table, APGenIndex index)
		{
			StringBuilder sb = new StringBuilder();

			sb.AppendFormat("alter table {0} add constraint {1} unique (", table.TableName, index.Name/* UQ_name(table, index)*/);
			foreach (APGenOrder order in index.Orders)
			{
				sb.AppendFormat("\r\n\t{0} {1},", table.Columns[order.Name].ColumnName, order.According.ToString());
			}
			sb.Length--;
			sb.Append("\r\n)");

			// alter table table_name add constraint uq_name (
			//		column_name DESC,
			//		...
			// )

			DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
			dbCmd.ExecuteNonQuery();
		}
		private void _createIndex(APDatabase db, APGenTable table, APGenIndex index)
		{
			StringBuilder sb = new StringBuilder();

			sb.AppendFormat("create index {0} on {1} (", index.Name/* IX_name(table, index)*/, table.TableName);
			foreach (APGenOrder order in index.Orders)
			{
				sb.AppendFormat("\r\n\t{0} {1},", table.Columns[order.Name].ColumnName, order.According.ToString());
			}
			sb.Length--;
			sb.Append("\r\n)");

			// create index index_name on (
			//		column_name [ASC|DESC],
			//		...
			// )

			DbCommand dbCmd = db.CreateSqlCommand(sb.ToString());
			dbCmd.ExecuteNonQuery();
		}
Exemple #38
0
        private void _dropConstraint(APDatabase db, APGenTable table, string name)
        {
            DbCommand dbCmd = db.CreateSqlCommand("alter table {0} drop constraint {1}", table.TableName, name);

            dbCmd.ExecuteNonQuery();
        }
Exemple #39
0
 /// <summary>
 /// Execute.
 /// </summary>
 /// <param name="command">The command.</param>
 /// <param name="db">The database.</param>
 /// <returns>The reader.</returns>
 public static IDataReader executeReader(this APSqlSelectCommand command, APDatabase db)
 {
     return(db.ExecuteReader(command));
 }
Exemple #40
0
 /// <summary>
 /// Execute.
 /// </summary>
 /// <param name="command">The command.</param>
 /// <param name="db">The database.</param>
 public static void execute(this APSqlDeleteCommand command, APDatabase db)
 {
     db.ExecuteNonQuery(command);
 }
		/// <summary>
		/// Execute.
		/// </summary>
		/// <param name="command">The command.</param>
		/// <param name="db">The database.</param>
		public static void execute(this APSqlUpdateCommand command, APDatabase db)
		{
			db.ExecuteNonQuery(command);
		}
		private Dictionary<string, columninfo> _getColumns(APDatabase db, APGenTable table)
		{
			OracleCommand dbCmd = db.CreateSqlCommand(
@"select
	column_id,
	column_name,
	data_type,
	data_length,
	nvl(data_precision, 0),
	nvl(data_scale, 0),
	nullable,
	nvl(default_length, 0),
	data_default
from user_tab_columns
where table_name = '{0}'
order by column_id", table.TableName.ToUpper()) as OracleCommand;
			dbCmd.InitialLONGFetchSize = -1;

			Dictionary<string, columninfo> dbColumns = new Dictionary<string, columninfo>(StringComparer.InvariantCultureIgnoreCase);
			using (OracleDataReader reader = dbCmd.ExecuteReader())
			{
				while (reader.Read())
				{
					int idx = 0;

					columninfo data = new columninfo(){
						colid = Convert.ToInt32(reader.GetValue(idx++)),
						colname = Convert.ToString(reader.GetValue(idx++)),
						typename = Convert.ToString(reader.GetValue(idx++)).ToLower(),
						length = Convert.ToInt32(reader.GetValue(idx++)),
						xprec = Convert.ToInt32(reader.GetValue(idx++)),
						xscale = Convert.ToInt32(reader.GetValue(idx++)),
						isnullable = Convert.ToString(reader.GetValue(idx++)) == "Y",
						dflength = Convert.ToInt32(reader.GetValue(idx++)),
						dfvalue = "",
					};
					if (data.dflength > 0)
						data.dfvalue = reader.GetString(idx++).Trim();
					data.Resolve();
					dbColumns.Add(data.colname, data);
				}
			}
			return dbColumns;
		}
		private Dictionary<string, indexinfo> _getIndexes(APDatabase db, APGenTable table)
		{
			DbCommand dbCmd = db.CreateSqlCommand(
@"select
	idx.name,
	col.name as col,
	idxCol.is_descending_key,
	idx.is_unique,
	idx.is_primary_key,
	idx.is_unique_constraint
from
	sys.indexes as idx
	inner join sys.index_columns as idxCol on (idx.object_id = idxCol.object_id AND idx.index_id = idxCol.index_id)
	inner join sys.columns as col on (idx.object_id = col.object_id AND idxCol.column_id = col.column_id)
where idx.object_id = object_id('{0}')
order by idx.name", table.TableName);

			Dictionary<string, indexinfo> dbIndexes = new Dictionary<string, indexinfo>();
			using (IDataReader reader = dbCmd.ExecuteReader())
			{
				while (reader.Read())
				{
					string name = Convert.ToString(reader.GetValue(0));
					string col = Convert.ToString(reader.GetValue(1));
					bool is_descending_key = Convert.ToBoolean(reader.GetValue(2));
					bool is_unique = Convert.ToBoolean(reader.GetValue(3));
					bool is_primary_key = Convert.ToBoolean(reader.GetValue(4));
					bool is_unique_constraint = Convert.ToBoolean(reader.GetValue(5));

					indexinfo data;
					if (dbIndexes.ContainsKey(name))
					{
						data = dbIndexes[name];
					}
					else
					{
						data = new indexinfo() { indname = name };
						if (is_primary_key)
							data.indtype = indexType.Primary;
						else if (is_unique_constraint)
							data.indtype = indexType.Unique;
						else
							data.indtype = indexType.Index;
						dbIndexes.Add(name, data);
					}
					data.columns.Add(col, is_descending_key);
				}
			}
			foreach (var item in dbIndexes.Values)
			{
				item.pickKeys();
			}
			return dbIndexes;
		}
Exemple #44
0
 /// <summary>
 /// Query extensions.
 /// </summary>
 /// <param name="db">APDatabase.</param>
 /// <param name="command">The 'SELECT' command.</param>
 /// <returns>The count of "SELECT COUNT(*)" command.</returns>
 public static int SizeOf(this APDatabase db, APSqlSelectCommand command)
 {
     return(db.ExecuteSizeOfSelect(command));
 }
		private void ModifyTable(APDatabase db, APGenTable table)
		{
			Dictionary<string, columninfo> dbColumns = _getColumns(db, table);
			Dictionary<string, indexinfo> dbIndexes = _getIndexes(db, table);
			bool isTableEmpty = _isTableEmpty(db, table);



			// analysis columns

			List<APGenColumn> listCreateColumn = new List<APGenColumn>();
			List<APGenColumn> listModifyColumn = new List<APGenColumn>();
			Dictionary<string, columninfo> deleteColumns = new Dictionary<string, columninfo>(dbColumns);

			foreach (APGenColumn column in table.Columns)
			{
				_setScanColumn(column.ColumnName);

				string colname = column.ColumnName;

				if (!dbColumns.ContainsKey(colname))
				{
					// db has not this column, wait for create.
					listCreateColumn.Add(column);
				}
				else
				{
					deleteColumns.Remove(colname);

					columninfo colinfo = dbColumns[colname];

					if (column.IsNullable != colinfo.isnullable
						|| column.DBDefaultValue != colinfo.dfvalue
						|| DBTypeName(column) != colinfo.GetTypeFullName()
						// no safe mode to change identity, so ingone change this
						// || (column.IdentityType == APColumnIdentityType.Database ^ colinfo.is_identity)
						)
					{
						listModifyColumn.Add(column);
					}
				}
			}


			// analysis indexes

			List<APGenIndex> listCreateIndex = new List<APGenIndex>();

			foreach (APGenIndex index in table.Indexes)
			{
				string ix_name = index.Name;// IX_name(table, index);

				if (!dbIndexes.ContainsKey(ix_name))
				{
					// db has not this index, wait for create.
					listCreateIndex.Add(index);
				}
				else
				{

					// db has this index and columns according fitted, then to nothing.
					// elsewise, wait fo modify this index.
					// drop in db indexes residual in dbIndexes.
					indexinfo info = dbIndexes[ix_name];
					string ix_keys = IndKeys(table, index);

					if (info.indkeys == ix_keys)
					{
						bool maybe = false;
						foreach (APGenColumn column in listModifyColumn)
						{
							if (info.columns.ContainsKey(column.ColumnName))
							{
								listCreateIndex.Add(index);
								maybe = true;
								break;
							}
						}
						if (!maybe)
							dbIndexes.Remove(ix_name);
					}
					else
					{
						listCreateIndex.Add(index);
					}
				}
			}


			// analysis uniques

			List<APGenIndex> listCreateUnique = new List<APGenIndex>();

			foreach (APGenIndex index in table.Uniques)
			{
				string ix_name = index.Name;// UQ_name(table, index);

				if (!dbIndexes.ContainsKey(ix_name))
				{
					// db has not this unique, wait for create.
					listCreateUnique.Add(index);
				}
				else
				{

					// db has this unique, then to nothing.
					// elsewise, wait fo modify this unique.
					// drop in db uniques residual in dbIndexes.
					indexinfo info = dbIndexes[ix_name];
					string uq_keys = IndKeys(table, index);

					if (info.indkeys == uq_keys)
					{
						bool maybe = false;
						foreach (APGenColumn column in listModifyColumn)
						{
							if (info.columns.ContainsKey(column.ColumnName))
							{
								listCreateUnique.Add(index);
								maybe = true;
								break;
							}
						}
						if (!maybe)
							dbIndexes.Remove(ix_name);
					}
					else
					{
						listCreateUnique.Add(index);
					}
				}
			}

			// 1. dbIndexes for 'drop', but PK_ index analysis columns.
			// 2. listCreateIndex for 'create'.
			// 2. listCreateUnique for 'create'.


			// process

			string pkKeys = "";
			foreach (APGenColumn column in table.PrimaryKeyColumns)
			{
				if (pkKeys != "") pkKeys += ",";
				pkKeys += column.ColumnName;
			}


			// 1. drop indexes

			bool needAddPrimary = true;
			foreach (indexinfo info in dbIndexes.Values)
			{
				if (info.indtype == indexType.Primary && info.indkeys == pkKeys)
					needAddPrimary = false;
				else
					_dropIndex(db, table, info);
			}

			// 2. drop columns

			foreach (columninfo info in deleteColumns.Values)
			{
				_dropColumn(db, table, info);
			}

			// 3. modify columns
			
			foreach (APGenColumn column in listModifyColumn)
			{
				_alterColumn(db, table, column, dbColumns[column.ColumnName]);
			}

			// 4. create columns

			foreach (APGenColumn column in listCreateColumn)
			{
				_createColumn(db, table, column, isTableEmpty);
			}

			// 5. mayby primary key

			if (needAddPrimary)
			{
				_createPrimaryKey(db, table);
			}

			// 6. create indexes

			foreach (APGenIndex index in listCreateIndex)
			{
				_createIndex(db, table, index);
			}

			// 7. create unique

			foreach (APGenIndex unique in listCreateUnique)
			{
				_createUnique(db, table, unique);
			}
		}
		private void SyncSequence(APDatabase db, APGenBusinessModelSection businessModel)
		{
			List<string> seqnames = new List<string>();
			DbCommand dbCmd = db.CreateSqlCommand("select sequence_name from user_sequences");
			using (IDataReader reader = dbCmd.ExecuteReader())
			{
				while (reader.Read())
				{
					seqnames.Add(reader.GetString(0));
				}
			}

			foreach (APGenTable table in businessModel.Tables)
			{
				foreach (APGenColumn column in table.Columns)
				{
					if (column.IdentityType == APColumnIdentityType.Provider
						&& businessModel.CanIdentityRelyOnProvider(column)
						&& column.ParsedType != typeof(Guid))
					{
						string seqname = SEQ_name(table.TableName, column.ColumnName).ToUpper();
						if (!seqnames.Contains(seqname))
						{
							dbCmd = db.CreateSqlCommand("create sequence {0} increment by 1 start with {1} nomaxvalue nocycle nocache",
								seqname, column.ProviderIdentityBase);
							dbCmd.ExecuteNonQuery();
						}
					}
				}
			}
		}
		private void _createColumn(APDatabase db, APGenTable table, APGenColumn column, bool isTableEmpty)
		{
			bool changeNullable = (!isTableEmpty && !column.IsNullable);
			if (changeNullable)
			{
				column.IsNullable = true;
			}

			string sql;

			if (column.IdentityType == APColumnIdentityType.Database)
				sql = string.Format("alter table {0} add {1} {2} IDENTITY {3} {4}",
					table.TableName,
					column.ColumnName,
					DBTypeName(column),
					IsNullableString(column),
					DF_constraint(table, column));
			else
				sql = string.Format("alter table {0} add {1} {2} {3} {4}",
					table.TableName,
					column.ColumnName,
					DBTypeName(column),
					IsNullableString(column),
					DF_constraint(table, column));

			DbCommand dbCmd = db.CreateSqlCommand(sql);
			dbCmd.ExecuteNonQuery();

			if (changeNullable)
			{
				column.IsNullable = false;
				string defaultValue = column.DBDefaultValue == "" ? "''" : column.DBDefaultValue;

				dbCmd = db.CreateSqlCommand("update {0} set {1} = {2}", table.TableName, column.ColumnName, defaultValue);
				dbCmd.ExecuteNonQuery();

				dbCmd = db.CreateSqlCommand("alter table {0} alter column {1} {2} {3}",
				  table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column));
				dbCmd.ExecuteNonQuery();
			}
		}
Exemple #48
0
 /// <summary>
 /// Execute.
 /// </summary>
 /// <param name="command">The command.</param>
 /// <param name="db">The database.</param>
 /// <returns>The scale.</returns>
 public static object executeScale(this APSqlSelectCommand command, APDatabase db)
 {
     return(db.ExecuteScalar(command));
 }
		private bool _isTableEmpty(APDatabase db, APGenTable table)
		{
			DbCommand dbCmd = db.CreateSqlCommand(
@"select count(*)
from {0}", table.TableName);
			int count = Convert.ToInt32(dbCmd.ExecuteScalar());
			return count == 0;
		}
Exemple #50
0
 /// <summary>
 /// Execute
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="command"></param>
 /// <param name="db"></param>
 /// <param name="fillup"></param>
 /// <returns></returns>
 public static IEnumerable <T> query <T>(this APSqlSelectCommand command, APDatabase db, Func <IDataReader, T> fillup)
 {
     return(db.Query(command, fillup));
 }
		private Dictionary<string, columninfo> _getColumns(APDatabase db, APGenTable table)
		{
			DbCommand dbCmd = db.CreateSqlCommand(
@"select
	col.colid,
	col.name as colname,
	tp.name as typename,
	tp.variable,
	col.length,
	col.xprec,
	col.xscale,
	col.isnullable,
	df.name as dfname,
	df.definition as dfvalue,
	id.is_identity
from
	syscolumns as col
	inner join systypes as tp on col.xtype = tp.xtype and tp.name <> 'sysname'
	left join sys.default_constraints as df on col.cdefault = df.object_id
	left join sys.identity_columns as id on col.colid = id.column_id and id.object_id = col.id
where col.id = object_id('{0}')
order by col.colid", table.TableName);

			Dictionary<string, columninfo> dbColumns = new Dictionary<string, columninfo>();
			using (IDataReader reader = dbCmd.ExecuteReader())
			{
				while (reader.Read())
				{
					int idx = 0;
					columninfo data = new columninfo()
					{
						colid = Convert.ToInt32(reader.GetValue(idx++)),
						colname = Convert.ToString(reader.GetValue(idx++)),
						typename = Convert.ToString(reader.GetValue(idx++)),
						variable = Convert.ToBoolean(reader.GetValue(idx++)),
						length = Convert.ToInt32(reader.GetValue(idx++)),
						xprec = Convert.ToInt32(reader.GetValue(idx++)),
						xscale = Convert.ToInt32(reader.GetValue(idx++)),
						isnullable = Convert.ToBoolean(reader.GetValue(idx++)),
						dfname = Convert.ToString(reader.GetValue(idx++)),
						dfvalue = Convert.ToString(reader.GetValue(idx++)),
						is_identity = reader.IsDBNull(idx++) ? false : true,
					};
					data.Resolve();
					dbColumns.Add(data.colname, data);
				}
			}
			return dbColumns;
		}
		private void _dropColumn(APDatabase db, APGenTable table, columninfo info)
		{
			if (info.dfname != "")
				_dropConstraint(db, table, info.dfname);

			DbCommand dbCmd = db.CreateSqlCommand("alter table {0} drop column {1}", table.TableName, info.colname);
			dbCmd.ExecuteNonQuery();
		}
		private Dictionary<string, indexinfo> _getIndexes(APDatabase db, APGenTable table)
		{
			OracleCommand dbCmd = db.CreateSqlCommand(
@"select col.index_name, col.column_name, expr.column_expression, col.descend, nvl(cst.constraint_type,'C')
from user_ind_columns col
  left join user_ind_expressions expr
    on col.index_name=expr.index_name and col.table_name=expr.table_name and col.column_position=expr.column_position
  left join user_constraints cst on col.table_name=cst.table_name and col.index_name=cst.constraint_name
where col.table_name = '{0}'
order by col.index_name, col.column_position", table.TableName.ToUpper()) as OracleCommand;
			dbCmd.InitialLONGFetchSize = -1;

			Dictionary<string, indexinfo> dbIndexes = new Dictionary<string, indexinfo>(StringComparer.InvariantCultureIgnoreCase);
			using (OracleDataReader reader = dbCmd.ExecuteReader())
			{
				while (reader.Read())
				{
					string name = Convert.ToString(reader.GetValue(0));
					string col = Convert.ToString(reader.GetValue(1));
					if (!reader.IsDBNull(2))
					{
						col = reader.GetString(2);
						col = col.Substring(1, col.Length - 2);
					}
					bool is_descending_key = Convert.ToString(reader.GetValue(3)) == "DESC";
					string type = Convert.ToString(reader.GetValue(4));

					indexinfo data;
					if (dbIndexes.ContainsKey(name))
					{
						data = dbIndexes[name];
					}
					else
					{
						data = new indexinfo() { indname = name };
						if (type == "P")
							data.indtype = indexType.Primary;
						else if (type == "U")
							data.indtype = indexType.Unique;
						else
							data.indtype = indexType.Index;
						dbIndexes.Add(name, data);
					}
					data.columns.Add(col, is_descending_key);
				}
			}
			foreach (var item in dbIndexes.Values)
			{
				item.pickKeys();
			}
			return dbIndexes;
		}
		private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info)
		{
			if (info.dfname != "")
				_dropConstraint(db, table, info.dfname);


			DbCommand dbCmd;
			// no safe mode to change identity, so ingone this, throw sql exception.


			if (info.isnullable && !column.IsNullable)
			{
				dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName);
				if ((int)dbCmd.ExecuteScalar() > 0)
				{
					// when column nullable change to not nullable and has data,
					// set default value.
					dbCmd = db.CreateSqlCommand("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, info.GetDefaultValue());
					dbCmd.ExecuteNonQuery();
				}
			}

			dbCmd = db.CreateSqlCommand("alter table {0} alter column {1} {2} {3}",
			  table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column));
			dbCmd.ExecuteNonQuery();


			if (column.DBDefaultValue != "")
			{
				dbCmd = db.CreateSqlCommand("alter table {0} add {1} for {2}",
					table.TableName, DF_constraint(table, column), column.ColumnName);
				dbCmd.ExecuteNonQuery();
			}

			// alter table table_nae alter column column_name type_name [not] null
			// go
			// alter table add constraint DF_tablename_columnname default dfvalue for column_name
			// go
		}
		/// <summary>
		/// Execute.
		/// </summary>
		/// <param name="command">The command.</param>
		/// <param name="db">The database.</param>
		/// <returns>The scale.</returns>
		public static object executeScale(this APSqlInsertCommand command, APDatabase db)
		{
			return db.ExecuteScalar(command);
		}