/// <summary> /// This method overloads the base class method to support DAS specific properties. /// </summary> /// <param name="table">Table to be merged.</param> /// <param name="preserveChanges"><c>True</c> means all changes are preserved.</param> /// <param name="missingSchemaAction"><seealso cref="System.Data.MissingSchemaAction"></seealso></param> public void Merge(DASDataTable table, bool preserveChanges, MissingSchemaAction missingSchemaAction) { base.Merge(table, preserveChanges, missingSchemaAction); foreach (DASDataRow row in table.Rows) { var _with1 = Rows.ItemByDBKey(row.DBKey); _with1.SQL = row.SQL; } }
/// <summary> /// This sub creates a given <see cref="DASDataTable"></see> object with the schema information found for the given /// table. /// </summary> /// <param name="columnList">String with a comma separated list of all columns.</param> /// <param name="tableName">Name of the database table which schema should be used.</param> /// <exception cref="ArgumentNullException"></exception> /// <remarks> /// <para> /// You can use the column list to select the needed columns. You can also rename the database column by using /// aliases in the list. /// </para> /// <para> /// To use aliases just name them like in normal sql. For example /// <quote>column1 as mycolumn1, column2 as mycolumn2</quote>. /// </para> /// </remarks> /// <exception cref="NotConnectedException">Thrown when there is no established connection.</exception> public DASDataTable CreateDASDataTable(string columnList, string tableName) { if ((string.IsNullOrEmpty(columnList))) { throw new ArgumentNullException(nameof(columnList)); } if ((string.IsNullOrEmpty(tableName))) { throw new ArgumentNullException(nameof(tableName)); } if (IsConnected) { var dataTable = new DASDataTable(this); var cmd = _providerFactory.CreateCommand(); cmd.CommandText = $"SELECT {columnList} FROM {tableName}"; cmd.Connection = Connection; cmd.Transaction = _transaction; dataTable.Load(cmd.ExecuteReader(CommandBehavior.SchemaOnly)); var dasDataTable = new DASDataTable(this); foreach (DataColumn column in dataTable.Columns) { var dasColumn = new DASDataColumn { ColumnName = column.ColumnName, DBColumnName = column.ColumnName, DataType = column.DataType, MaxLength = column.MaxLength, AllowDBNull = column.AllowDBNull, Unique = column.Unique }; dasDataTable.Columns.Add(dasColumn); } var keys = new DASDataColumn[dataTable.PrimaryKey.Length + 1]; var i = 0; foreach (var column in dataTable.PrimaryKey) { keys[i] = dasDataTable.Columns.ItemByName(column.ColumnName); i += 1; } dasDataTable.PrimaryKey = keys; dasDataTable.DBTableName = tableName; dasDataTable.BuildAndSetBaseSQL(); return(dasDataTable); } throw new NotConnectedException(); }
/// <summary> /// This sub fills a given <see cref="DASDataTable"></see> object with the resulting rows of the given SQL Query. /// </summary> /// <param name="dataTable"><see cref="DASDataTable"></see> object to be filled.</param> /// <param name="sql">SQL-Statement to query data. Must be a SELECT-Statement.</param> /// <remarks> /// You can use parameters in the SQL-Statement to increase performance and making the SQL-statement reusable. /// <seealso cref="AddParameter(string,object,PKSim.Infrastructure.ORM.DAS.DAS.ParameterModes)"></seealso> /// <seealso /// cref="AddParameter(string,object,PKSim.Infrastructure.ORM.DAS.DAS.ParameterModes,PKSim.Infrastructure.ORM.DAS.DAS.ServerTypes)"> /// </seealso> /// Don't forget to remove the parameter later on with <see cref="RemoveParameter "></see>, because a parameter /// name is unique within the whole session and trying to create some with an used name will cause an error. /// </remarks> /// <exception cref="NotConnectedException">Thrown when there is no established connection.</exception> public void FillDataTable(DASDataTable dataTable, string sql) { if (!IsConnected) { throw new NotConnectedException(); } var paramValues = new ParameterValue[_parameters.Count]; var i = 0; var cmd = _providerFactory.CreateCommand(); cmd.CommandText = sql; cmd.Connection = Connection; cmd.Transaction = _transaction; foreach (DbParameter param in _parameters) { if (!sql.Contains(param.ParameterName)) { continue; } var newParam = _providerFactory.CreateParameter(); newParam.ParameterName = param.ParameterName; newParam.Direction = param.Direction; newParam.DbType = param.DbType; newParam.Value = param.Value; cmd.Parameters.Add(newParam); } //store actual parameters and their values to support refreshing later on foreach (DbParameter parameter in _parameters) { var paramValue = new ParameterValue { ParameterName = parameter.ParameterName, Value = parameter.Value }; switch (parameter.Direction) { case ParameterDirection.Input: paramValue.ParameterMode = ParameterModes.PARM_IN; break; case ParameterDirection.Output: paramValue.ParameterMode = ParameterModes.PARM_OUT; break; case ParameterDirection.InputOutput: paramValue.ParameterMode = ParameterModes.PARM_INOUT; break; case ParameterDirection.ReturnValue: paramValue.ParameterMode = ParameterModes.PARM_RETURN; break; } switch (parameter.DbType) { case DbType.Date: case DbType.DateTime: case DbType.Time: paramValue.ServerType = ServerTypes.DATE; break; case DbType.Decimal: case DbType.Double: case DbType.Int16: case DbType.Int32: case DbType.Int64: case DbType.Single: case DbType.UInt16: case DbType.UInt32: case DbType.UInt64: paramValue.ServerType = ServerTypes.NUMBER; break; case DbType.AnsiString: case DbType.AnsiStringFixedLength: case DbType.String: case DbType.StringFixedLength: paramValue.ServerType = ServerTypes.STRING; break; default: throw new UnsupportedDataTypeException(parameter.DbType.ToString()); } paramValues[i] = paramValue; i += 1; } dataTable.ParameterValues = paramValues; dataTable.SQL = sql; dataTable.Rows.Clear(); try { using (var adapter = _providerFactory.CreateDataAdapter()) { adapter.SelectCommand = cmd; adapter.Fill(dataTable); } } catch (Exception ex) { throw new Exception($"Error occurred with statement <{sql}>.", ex); } }
/// <summary> /// This function checks whether the row does exists in the database. /// </summary> /// <returns><c>True</c>, if row exists.</returns> public bool ExistsInDB() { bool ReturnValue = false; var stringBuilder = new StringBuilder(); var TableDAS = Table.DAS; DataRowVersion rowVersion; var dataTable = new DASDataTable(TableDAS) { DBTableName = Table.DBTableName }; switch (RowState) { case DataRowState.Added: rowVersion = DataRowVersion.Current; break; case DataRowState.Detached: rowVersion = DataRowVersion.Default; break; default: rowVersion = DataRowVersion.Original; break; } long i = 1; foreach (DASDataColumn column in Table.PrimaryKey) { switch (column.DASDataType) { case DASDataColumn.DASDataTypes.DASDATE: TableDAS.AddParameter(string.Format("@P{0}", i), this[column, rowVersion], DAS.ParameterModes.PARM_IN, DAS.ServerTypes.DATE); break; case DASDataColumn.DASDataTypes.DASDOUBLE: case DASDataColumn.DASDataTypes.DASLONG: TableDAS.AddParameter(string.Format("@P{0}", i), this[column, rowVersion], DAS.ParameterModes.PARM_IN, DAS.ServerTypes.NUMBER); break; case DASDataColumn.DASDataTypes.DASSTRING: TableDAS.AddParameter(string.Format("@P{0}", i), this[column, rowVersion], DAS.ParameterModes.PARM_IN, DAS.ServerTypes.STRING); break; default: throw new UnsupportedDataTypeException(column.DASDataType.ToString()); } i++; } try { stringBuilder.AppendFormat("SELECT NULL FROM {0}", dataTable.DBTableName); i = 1; foreach (DASDataColumn column in Table.PrimaryKey) { if (i == 1) { stringBuilder.Append(" WHERE "); } else { stringBuilder.Append(" AND "); } stringBuilder.AppendFormat("{0} = @P{1}", column.ColumnName, i++); } TableDAS.FillDataTable(dataTable, stringBuilder.ToString()); } catch (Exception ex) { throw new Exception(ex.Message, ex.InnerException); } finally { for (var k = 1; k <= Table.PrimaryKey.Length; k++) { TableDAS.RemoveParameter(string.Format("@P{0}", k)); } } if (dataTable.Rows.Count() == 1) { ReturnValue = true; } else if (dataTable.Rows.Count() > 1) { throw new TooManyRowsFoundException(); } return(ReturnValue); }
/// <summary> /// This method overloads the base class method to support DAS specific properties. /// </summary> /// <param name="table">Table to be merged.</param> public void Merge(DASDataTable table) { Merge(table, true, MissingSchemaAction.Error); }
/// <summary> /// This method overloads the base class method to support DAS specific properties. /// </summary> /// <param name="table">Table to be merged.</param> /// <param name="preserveChanges"><c>True</c> means all changes are preserved.</param> public void Merge(DASDataTable table, bool preserveChanges) { Merge(table, preserveChanges, MissingSchemaAction.Error); }