/// <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);
}