/// <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);
				}
			}
		}
		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);
			}
		}
		/// <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);
				}
			}
		}
		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 _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 _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();
		}
		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 _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();
		}
		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();
			}
		}
Esempio n. 10
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;
		}
Esempio n. 11
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();
		}
Esempio n. 12
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();
		}
		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 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 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();
		}
		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();
				}
			}
		}
Esempio n. 18
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 {1} on {0}", table.TableName, info.indname);
				dbCmd.ExecuteNonQuery();
			}
		}
		/// <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);
		}
Esempio n. 20
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
		}
Esempio n. 21
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);

			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);
			}
		}
Esempio n. 22
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;
		}
Esempio n. 23
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();
						}
					}
				}
			}
		}
Esempio n. 24
0
		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;
		}
		/// <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);
		}