Esempio n. 1
0
 /// <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;
     }
 }
Esempio n. 2
0
        /// <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();
        }
Esempio n. 3
0
        /// <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);
            }
        }
Esempio n. 4
0
        /// <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);
        }
Esempio n. 5
0
 /// <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);
 }
Esempio n. 6
0
 /// <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);
 }