/// <summary>
/// Saves the current record to the database.
/// </summary>
public virtual void Save()
{
string Sql;
try
{
// Ensure the object is ready to be saved
ValidateSave();
ReservedKey savekey = null;
if (_dataRow.RowState == DataRowState.Detached)
{
// Build SQL Insert Statement
// Set All Auto Keys
if (PrimaryKeys.Count > 1)
{
for (int i = 0; i < PrimaryKeys.Count; i++)
{
if (_dataRow[PrimaryKeys[i].Name].ToString() == String.Empty)
{
// Error Primary Key Not Set
DataObjectException PrimaryKeyError = new DataObjectException("One or more required fields do not have valid values.");
throw PrimaryKeyError;
}
}
}
else
{
// Only a single primary key, check to see if it has already been assigned
if (_dataRow[PrimaryKeys[0].Name].ToString() == String.Empty)
{
// Autonumber
savekey = GetNextKey();
Row[PrimaryKeys[0].Name] = savekey.Value;
}
// Else key has already been assigned
}
_dataTable.Rows.Add(_dataRow);
Sql = "INSERT INTO " + TableName + "(";
// Column names
for (int i = 0; i < _dataTable.Columns.Count; i++)
{
Sql += "[" + _dataTable.Columns[i].ColumnName + "], ";
}
Sql = Sql.Substring(0, Sql.Length - 2) + ") VALUES(";
// Column values
for (int i = 0; i < _dataTable.Columns.Count; i++)
{
if (_dataTable.Columns[i].DataType == System.Type.GetType("System.DateTime"))
{
if (_dataRow[i].ToString() == String.Empty)
{
Sql += "null";
}
else
{
Sql += "'" + ((DateTime?)_dataRow[i]).Value.ToString("yyyy/MM/dd HH:mm:ss") + "'";
}
}
else if (_dataTable.Columns[i].DataType == System.Type.GetType("System.String"))
{
Sql += "'" + _dataRow[i].ToString().Replace("'", "''") + "'";
}
else if (_dataTable.Columns[i].DataType == System.Type.GetType("System.Int32"))
{
if (_dataRow[i].ToString() == String.Empty)
{
Sql += "null";
}
else
{
Sql += _dataRow[i].ToString();
}
}
else if ((_dataTable.Columns[i].DataType == System.Type.GetType("System.Single")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Double")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Decimal")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Int64")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Int16")))
{
if (_dataRow[i].ToString() == String.Empty)
{
Sql += "null";
}
else
{
Sql += _dataRow[i].ToString();
}
}
else if (_dataTable.Columns[i].DataType == System.Type.GetType("System.Boolean"))
{
if ((bool)_dataRow[i] == true)
{
Sql += "1";
}
else
{
Sql += "0";
}
}
else
{
DataObjectException InvalidDataType = new DataObjectException("Unsupported data type. [" + _dataTable.Columns[i].DataType.ToString() + "]");
throw InvalidDataType;
}
if (i != _dataTable.Columns.Count - 1)
{
Sql += ", ";
}
}
Sql += ")";
}
else
{
// Build SQL Update Statement
Sql = "UPDATE " + TableName + " SET ";
for (int i = 0; i < _dataTable.Columns.Count; i++)
{
if (!IsPrimaryKey(_dataTable.Columns[i].ColumnName))
{
Sql += "[" + _dataTable.Columns[i].ColumnName + "]" + " = ";
if (_dataTable.Columns[i].DataType == System.Type.GetType("System.DateTime"))
{
if (_dataRow[i].ToString() == String.Empty)
{
Sql += "null";
}
else
{
Sql += "'" + ((DateTime)_dataRow[i]).ToString("yyyy/MM/dd HH:mm:ss") + "'";
}
}
else if ((_dataTable.Columns[i].DataType == System.Type.GetType("System.Int32")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Decimal")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Double")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Int64")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Int16")))
{
if (_dataRow[i].ToString() == String.Empty)
{
Sql += "null";
}
else
{
Sql += _dataRow[i].ToString();
}
}
else if (_dataTable.Columns[i].DataType == System.Type.GetType("System.String"))
{
Sql += "'" + _dataRow[i].ToString().Replace("'", "''") + "'";
}
else if (_dataTable.Columns[i].DataType == System.Type.GetType("System.Boolean"))
{
if ((bool)_dataRow[i] == true)
{
Sql += "1";
}
else
{
Sql += "0";
}
}
else
{
Sql += _dataRow[i].ToString();
}
if (i != _dataTable.Columns.Count - 1)
{
Sql += ", ";
}
}
}
Sql += " WHERE";
string GetID = "0";
for (int i = 0; i < PrimaryKeys.Count; i++)
{
if (i > 0)
{
Sql += " AND";
}
if (_dataRow[PrimaryKeys[i].Name] is int)
{
GetID = ((int)_dataRow[PrimaryKeys[i].Name]).ToString();
}
else if (_dataRow[PrimaryKeys[i].Name] is string)
{
GetID = (string)_dataRow[PrimaryKeys[i].Name];
}
else
{
DataObjectException InvalidDataType = new DataObjectException("Unsupported data type. [" + _dataTable.Columns[i].DataType.ToString() + "]");
throw InvalidDataType;
}
Sql += " " + PrimaryKeys[i].Name + " = " + GetID;
}
}
DataCommand.CommandText = Sql;
DataCommand.Connection = ConnHandler.RequestConnection(Alias, this);
while (true) // Loop until Break for success
{
try
{
DataCommand.ExecuteNonQuery();
break; // Save Successful
}
catch (OleDbException err)
{
if (err.ErrorCode == -2147217873) // Primary Key violation
{
// Primary Key that was used already existed
savekey++;
DataCommand.CommandText = DataCommand.CommandText.Replace("VALUES (" + (savekey.Value - 1) + ",", "VALUES (" + savekey.Value.ToString() + ",");
}
else
{
throw err;
}
}
}
if (Sql.StartsWith("INSERT INTO"))
{
ReleaseKey(savekey);
}
}
catch (Exception err)
{
throw err;
}
finally
{
ConnHandler.ReleaseConnection(this, DataCommand.Connection);
}
}
/// <summary>
/// Loads a data object using multiple primary keys
/// </summary>
/// <param name="IDs">Primary key array to load.</param>
public void Load(int[] IDs)
{
// Load a dataobject by all of it's IDs
if (PrimaryKeys.Count != IDs.Length)
{
DataObjectException ValidationError = new DataObjectException("The object's primary keys do match the load values given.");
throw ValidationError;
}
if (TableName.Trim().Length == 0)
{
DataObjectException ValidationError = new DataObjectException("The object's table has not been set.");
throw ValidationError;
}
try
{
DataCommand.Connection = ConnHandler.RequestConnection(Alias, this);
DataCommand.CommandText = "SELECT * FROM " +
TableName + " WHERE";
for (int i = 0; i < PrimaryKeys.Count; i++)
{
if (i > 0)
{
DataCommand.CommandText += " AND";
}
DataCommand.CommandText += " " + PrimaryKeys[i].Name + " = " + IDs[i].ToString();
}
DataAdapter.SelectCommand = _dbcmdData;
int x = (int)DataAdapter.Fill(_dataTable);
if (x == 0)
{
// Record doesn't exist
_dataRow = null;
DataObjectException.InvalidRecordException PrimaryKeyError =
new DataObjectException.InvalidRecordException();
throw PrimaryKeyError;
}
else
{
_dataRow = _dataTable.Rows[_dataTable.Rows.Count - 1];
if (RecordChanged != null)
{
RecordChangedEventArgs args = new RecordChangedEventArgs(RowAction.Loaded);
RecordChanged(this, args);
}
}
}
catch (Exception err)
{
throw err;
}
finally
{
ConnHandler.ReleaseConnection(this, DataCommand.Connection);
}
}
/// <summary>
/// Delete the object with these IDs from the database.
/// </summary>
public void Delete(int[] IDs)
{
if (PrimaryKeys.Count != IDs.Length)
{
DataObjectException ValidationError = new DataObjectException("The object's primary keys do match the ID values given.");
throw ValidationError;
}
if (_dataRow != null)
{
for (int i = 0; i < PrimaryKeys.Count; i++)
{
if (_dataRow[PrimaryKeys[i].Name] is int)
{
if ((int)_dataRow[PrimaryKeys[i].Name] != IDs[i])
{
break;
}
else if (i == PrimaryKeys.Count - 1)
{
// The object trying to be deleted is this object
Delete(); // Delete self
return;
}
}
else
{
break;
}
}
}
try
{
string Sql;
Sql = "DELETE FROM " + TableName + " WHERE";
for (int i = 0; i < PrimaryKeys.Count; i++)
{
if (i > 0)
{
Sql += " AND";
}
Sql += " " + PrimaryKeys[i].Name + " = " + IDs[i].ToString();
}
DataCommand.Connection = ConnHandler.RequestConnection(Alias, this);
_dbcmdData.CommandText = Sql;
int x = (int)_dbcmdData.ExecuteNonQuery();
if (x == 0)
{
// Record doesn't exist
_dataRow = null;
DataObjectException.InvalidRecordException PrimaryKeyError =
new DataObjectException.InvalidRecordException();
throw PrimaryKeyError;
}
}
catch (Exception err)
{
throw err;
}
finally
{
ConnHandler.ReleaseConnection(this, DataCommand.Connection);
}
}
/// <summary>
/// Reloads the data for this object from the database.
/// </summary>
public virtual void Refresh()
{
try
{
DataCommand.Connection = this.ConnHandler.RequestConnection(Alias, this);
int GetID = -1;
if (_dataRow == null)
{
// No current record
DataObjectException.NoCurrentRecordException NoRecordError =
new DataObjectException.NoCurrentRecordException();
throw NoRecordError;
}
DataCommand.CommandText = "SELECT * FROM " +
TableName + " WHERE";
for (int i = 0; i < PrimaryKeys.Count; i++)
{
if (i > 0)
{
DataCommand.CommandText += " AND";
}
if (_dataTable.Columns.Count > 0)
{
if (_dataRow[PrimaryKeys[i].Name] is int)
{
GetID = (int)_dataRow[PrimaryKeys[i].Name];
}
else
{
GetID = -1;
}
DataCommand.CommandText += " " + PrimaryKeys[i].Name + " = " + GetID.ToString();
}
else
{
// This command will only return the table's structure, no actual records
DataCommand.CommandText += " " + PrimaryKeys[i].Name + " = -1";
}
}
if ((int)DataAdapter.Fill(_dataTable) > 0)
{
// Existing record
_dataRow = _dataTable.Rows[_dataTable.Rows.Count - 1];
}
else
{
// New record
_dataRow = _dataTable.NewRow();
}
}
catch (Exception err)
{
throw err;
}
finally
{
ConnHandler.ReleaseConnection(this, DataCommand.Connection);
}
}
/// <summary>
/// Deletes this record from the database.
/// </summary>
public virtual void Delete()
{
try
{
// Ensure the object is ready to be deleted
ValidateSave();
string Sql;
if (_dataRow == null)
{
// No current row
DataObjectException.NoCurrentRecordException NoRecordError =
new DataObjectException.NoCurrentRecordException();
throw NoRecordError;
}
if (_dataRow.RowState == DataRowState.Detached)
{
_dataRow = null;
return;
}
else
{
Sql = "DELETE FROM " + TableName + " WHERE";
for (int i = 0; i < PrimaryKeys.Count; i++)
{
if (i > 0)
{
Sql += " AND";
}
if (_dataRow[PrimaryKeys[i].Name].ToString() != String.Empty)
{
Sql += " " + PrimaryKeys[i].Name + " = " + _dataRow[PrimaryKeys[i].Name].ToString();
}
else
{
DataObjectException PrimaryKeyError = new DataObjectException("One or more required fields do not have valid values.");
throw PrimaryKeyError;
}
}
DataCommand.Connection = ConnHandler.RequestConnection(Alias, this);
_dbcmdData.CommandText = Sql;
_dbcmdData.ExecuteNonQuery();
_dataRow = null;
}
if (RecordChanged != null)
{
RecordChangedEventArgs args = new RecordChangedEventArgs(RowAction.Deleted);
RecordChanged(this, args);
}
}
catch (Exception err)
{
throw err;
}
finally
{
ConnHandler.ReleaseConnection(this, DataCommand.Connection);
}
}