Exemple #1
0
        public static DataTable GetDataTable(string query)
        {
            DataTable table = new DataTable();

            using (IDbConnection connection = DataSettings.GetConnection())
                using (IDbCommand command = connection.CreateCommand()) {
                    command.CommandText = query;

                    connection.Open();

                    using (IDataReader reader = command.ExecuteReader()) {
                        table.Load(reader);
                    }

                    connection.Close();
                }

            return(table);
        }
        public static void DeleteObject(int id)
        {
            if (!RemotingClient.OpenDentBusinessIsLocal)
            {
                FactoryClient <T> .SendRequest("DeleteObject", default(T), new object[] { id });

                return;
            }

            string primaryKeyFieldName = DataObjectInfo <T> .GetPrimaryKeyFieldName();

            string tableName = DataObjectInfo <T> .GetTableName();

            string query;

            if (useParameters)
            {
                query = string.Format("DELETE FROM {0} WHERE {1} = {2}{1}", tableName, primaryKeyFieldName, ParameterPrefix);
            }
            else
            {
                query = string.Format("DELETE FROM {0} WHERE {1} = '{2}'", tableName, primaryKeyFieldName, POut.PInt(id));
            }

            using (IDbConnection connection = DataSettings.GetConnection())
                using (IDbCommand command = connection.CreateCommand()) {
                    if (useParameters)
                    {
                        IDataParameter parameter = command.CreateParameter();
                        parameter.ParameterName = ParameterPrefix + primaryKeyFieldName;
                        parameter.Value         = id;
                        command.Parameters.Add(parameter);
                    }

                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                }
        }
        public static void DeleteObject(T value)
        {
            if (value == null)
            {
                throw new ArgumentNullException("value");
            }

            if (value.IsDeleted)
            {
                throw new InvalidOperationException(Resources.ObjectAlreadyDeleted);
            }

            if (value.IsNew)
            {
                throw new InvalidOperationException(Resources.ObjectNotSaved);
            }

            if (!RemotingClient.OpenDentBusinessIsLocal)
            {
                FactoryClient <T> .SendRequest("DeleteObject", value, new object[] {});

                value.OnDeleted(EventArgs.Empty);
                return;
            }

            Collection <DataFieldInfo> identityFields = DataObjectInfo <T> .GetDataFields(DataFieldMask.PrimaryKey);

            using (IDbConnection connection = DataSettings.GetConnection())
                using (IDbCommand command = connection.CreateCommand()) {
                    StringBuilder commandTextBuilder = new StringBuilder();

                    commandTextBuilder.Append(string.Format("DELETE FROM {0} WHERE ", DataObjectInfo <T> .GetTableName()));

                    if (useParameters)
                    {
                        // For each field, create a parameter
                        foreach (DataFieldInfo dataField in identityFields)
                        {
                            IDbDataParameter parameter = command.CreateParameter();
                            parameter.ParameterName = ParameterPrefix + dataField.DatabaseName;
                            parameter.Value         = dataField.Field.GetValue(value);
                            command.Parameters.Add(parameter);
                        }
                    }

                    for (int i = 0; i < identityFields.Count; i++)
                    {
                        if (useParameters)
                        {
                            commandTextBuilder.Append(string.Format("{0} = {1}{0}", identityFields[i].DatabaseName, ParameterPrefix));
                        }
                        else
                        {
                            commandTextBuilder.Append(string.Format("{0} = '{1}'", identityFields[i].DatabaseName, POut.PObject(identityFields[i].Field.GetValue(value))));
                        }

                        if (i != identityFields.Count - 1)
                        {
                            commandTextBuilder.Append(" AND ");
                        }
                    }

                    command.CommandText = commandTextBuilder.ToString();

                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                }

            // The object has been deleted
            value.OnDeleted(EventArgs.Empty);
        }
        /// <summary>
        ///  <para>
        ///   Saves an object to the database. If the IsNew propery of the object is set to false, an existing
        ///   row will be updated. If the IsNew property is set to true, a new row will be created.
        ///  </para>
        ///  <para>
        ///  If the object contains a single PrimaryKey field, with AutoNumber set to true, a new primary key
        ///  will be generated. Depending on the current settings, this may be a new, random key or an incremental
        ///  key.
        /// </summary>
        /// <remarks>
        ///  <para>
        ///   When an existing object is saved to the database, only modified values are being sent to the database.
        ///   This is to avoid concurrency issues as much as possible.
        ///  </para>
        ///  <para>
        ///   This method is not thread safe.
        ///  </para>
        /// </remarks>
        /// <param name="value">The object to be saved.</param>
        /// <param name="overrideAutoNumber">
        ///  Sometimes a new object will be added to the database, but the primary key will be set beforehand.
        ///  To prevent the code (or database) from auto-assigning a new key, this parameter should be set.
        /// </param>
        public static void WriteObject(T value, bool overrideAutoNumber)
        {
            if (value == null)
            {
                throw new ArgumentNullException("value");
            }

            if (value.IsDeleted)
            {
                throw new InvalidOperationException(Resources.CannotSaveDeletedObject);
            }

            // If the value is not new, and none of the values has changed, there is nothing we
            // should do.
            if (!value.IsNew && !value.IsDirty)
            {
                return;
            }

            // Should we update the primary key? Yes, if the value is new and the data object has a primary key
            // which is auto-numbered (IDENTITY in SQL).
            bool updatePrimaryKey = value.IsNew && DataObjectInfo <T> .HasPrimaryKeyWithAutoNumber();

            // Make sure the overrideAutoNumber parameter is set only if the code would attempt
            // to update the primary key.
            if (overrideAutoNumber && !updatePrimaryKey)
            {
                throw new InvalidOperationException(Resources.CannotOverridePrimaryKey);
            }

            // If overrideAutoNumber is set, we don't update the primary key -- obviously!
            if (overrideAutoNumber)
            {
                updatePrimaryKey = false;
            }

            // Should we generate a new, random key?
            bool generateRandomKey = updatePrimaryKey && PrefB.RandomKeys;

            if (generateRandomKey)
            {
                int key = MiscData.GetKey(DataObjectInfo <T> .GetTableName(), DataObjectInfo <T> .GetPrimaryKeyFieldName());
                DataObjectInfo <T> .SetPrimaryKey(value, key);

                // The primary key as already been updated. No need to retrieve it from the database.
                updatePrimaryKey = false;
            }

            if (!RemotingClient.OpenDentBusinessIsLocal)
            {
                DtoObjectInsertedAck ack = (DtoObjectInsertedAck)FactoryClient <T> .SendRequest("WriteObject", value, new object[] { overrideAutoNumber });

                DataObjectInfo <T> .SetPrimaryKeys(value, ack.PrimaryKeys);

                value.OnSaved(EventArgs.Empty);
                return;
            }

            Collection <DataFieldInfo> dataFields = DataObjectInfo <T> .GetDataFields(DataFieldMask.Data);

            Collection <DataFieldInfo> primaryKeyFields = DataObjectInfo <T> .GetDataFields(DataFieldMask.PrimaryKey);

            Collection <DataFieldInfo> allFields = DataObjectInfo <T> .GetDataFields();

            if (allFields.Count == 0)
            {
                throw new InvalidOperationException(Resources.NoFields);
            }

            // In queries, the first field always is special (because of the use of commas). This helper variable
            // helps us generate correct queries.
            bool isFirstField = true;

            using (IDbConnection connection = DataSettings.GetConnection())
                using (IDbCommand command = connection.CreateCommand()) {
                    if (useParameters)
                    {
                        // For each field, create a parameter
                        foreach (DataFieldInfo dataField in allFields)
                        {
                            IDbDataParameter parameter = command.CreateParameter();
                            parameter.ParameterName = ParameterPrefix + dataField.DatabaseName;

                            // Get the value of the field
                            object fieldValue = dataField.Field.GetValue(value);

                            // If the value is of type string and the value is null, we replace it
                            // by an empty string.
                            if (fieldValue == null && dataField.Field.FieldType == typeof(string))
                            {
                                fieldValue = string.Empty;
                            }

                            parameter.Value = fieldValue;
                            command.Parameters.Add(parameter);
                        }
                    }

                    // Create the SQL query. If it is a new field, create an "INSERT" statement, else
                    // an "UPDATE" statement.

                    StringBuilder commandTextBuilder = new StringBuilder();
                    if (value.IsNew)
                    {
                        // Create a new row, using an INSERT statement.
                        // The values to set always include the data values (not part of the PK)
                        commandTextBuilder.Append(string.Format("INSERT INTO {0} (", DataObjectInfo <T> .GetTableName()));
                        foreach (DataFieldInfo field in dataFields)
                        {
                            if (isFirstField)
                            {
                                isFirstField = false;
                            }
                            else
                            {
                                commandTextBuilder.Append(',');
                            }

                            commandTextBuilder.Append(field.DatabaseName);
                        }

                        // If the PK is auto-generated, it it shouldn't be included. If the PK is generated by the code
                        // (be it that is some external variable or that the PK is a random number generated previously);
                        // it should be included
                        if (!updatePrimaryKey)
                        {
                            foreach (DataFieldInfo primaryKeyField in primaryKeyFields)
                            {
                                if (isFirstField)
                                {
                                    isFirstField = false;
                                }
                                else
                                {
                                    commandTextBuilder.Append(',');
                                }

                                commandTextBuilder.Append(primaryKeyField.DatabaseName);
                            }
                        }

                        commandTextBuilder.Append(") VALUES (");
                        isFirstField = true;

                        foreach (DataFieldInfo field in dataFields)
                        {
                            if (isFirstField)
                            {
                                isFirstField = false;
                            }
                            else
                            {
                                commandTextBuilder.Append(',');
                            }

                            if (useParameters)
                            {
                                commandTextBuilder.Append(ParameterPrefix + field.DatabaseName);
                            }
                            else
                            {
                                commandTextBuilder.AppendFormat("{0}", POut.PObject(field.Field.GetValue(value)));
                            }
                        }

                        if (!updatePrimaryKey)
                        {
                            foreach (DataFieldInfo primaryKeyField in primaryKeyFields)
                            {
                                if (isFirstField)
                                {
                                    isFirstField = false;
                                }
                                else
                                {
                                    commandTextBuilder.Append(',');
                                }

                                if (useParameters)
                                {
                                    commandTextBuilder.Append(ParameterPrefix + primaryKeyField.DatabaseName);
                                }
                                else
                                {
                                    commandTextBuilder.AppendFormat("{0}", POut.PObject(primaryKeyField.Field.GetValue(value)));
                                }
                            }
                        }

                        commandTextBuilder.Append(')');
                    }
                    else
                    {
                        // Update an existing row, using the UPDATE statement.
                        // The WHERE clause contains all PK fields, other data fields go directly into
                        // the SET clause.
                        commandTextBuilder.Append(string.Format("UPDATE {0} SET ", DataObjectInfo <T> .GetTableName()));
                        foreach (DataFieldInfo field in dataFields)
                        {
                            // If the data field has not changed, we don't need to update it -- obviously
                            if (!DataObjectInfo <T> .HasChanged(field, value))
                            {
                                continue;
                            }

                            if (isFirstField)
                            {
                                isFirstField = false;
                            }
                            else
                            {
                                commandTextBuilder.Append(',');
                            }

                            if (useParameters)
                            {
                                commandTextBuilder.Append(string.Format("{0} = {1}{0}", field.DatabaseName, ParameterPrefix));
                            }
                            else
                            {
                                commandTextBuilder.Append(string.Format("{0} = {1}", field.DatabaseName, POut.PObject(field.Field.GetValue(value))));
                            }
                        }

                        commandTextBuilder.Append(" WHERE ");

                        isFirstField = true;
                        foreach (DataFieldInfo field in primaryKeyFields)
                        {
                            if (isFirstField)
                            {
                                isFirstField = false;
                            }
                            else
                            {
                                commandTextBuilder.Append(',');
                            }

                            if (useParameters)
                            {
                                commandTextBuilder.Append(string.Format("{0} = {1}{0}", field.DatabaseName, ParameterPrefix));
                            }
                            else
                            {
                                commandTextBuilder.Append(string.Format("{0} = {1}", field.DatabaseName, POut.PObject(field.Field.GetValue(value))));
                            }
                        }
                    }

                    command.CommandText = commandTextBuilder.ToString();

                    connection.Open();

                    // This executes the UPDATE/INSERT command.
                    command.ExecuteNonQuery();

                    // Update the PK if required
                    if (updatePrimaryKey)
                    {
                        // This is currently not implemented for Oracle. Open Dental uses a special mechanism, OracleInsertId,
                        // but using a SEQUENCE might be better. See http://coldfusion.sys-con.com/read/43794.htm .
                        //
                        // For MS SQL (not implemented, either), this would be SCOPE_IDENTITY()
                        if (DataSettings.DbType != DatabaseType.MySql)
                        {
                            throw new NotImplementedException();
                        }

                        command.CommandText = "SELECT LAST_INSERT_ID()";

                        // The type returned by command.ExecuteScalar() is System.Int64.
                        // We need to cast it to System.Int32, that's what we use here.
                        int key = Convert.ToInt32(command.ExecuteScalar());
                        DataObjectInfo <T> .SetPrimaryKey(value, key);
                    }

                    connection.Close();

                    // The object has been saved
                    value.OnSaved(EventArgs.Empty);
                }
        }