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