private void ValidateSave()
{
try
{
if (_dataTable == null)
{
DataObjectException ValidationError = new DataObjectException("The object's data table has not been set.");
throw ValidationError;
}
for (int i = 0; i < PrimaryKeys.Count; i++)
{
if (_dataTable.Columns[PrimaryKeys[i].Name] == null)
{
DataObjectException ValidationError = new DataObjectException("The primary key field does not exist in the datatable.");
throw ValidationError;
}
}
if (_dataRow == null)
{
DataObjectException.NoCurrentRecordException ValidationError = new DataObjectException.NoCurrentRecordException();
throw ValidationError;
}
}
catch (DataObjectException err)
{
throw err;
}
catch (Exception err)
{
throw err;
}
}
/// <summary>
/// Loads a data object using a primary key
/// </summary>
/// <param name="ID">Primary key value to load.</param>
public void Load(int ID)
{
// Load a dataobject by it's Primary[0] ID
if (PrimaryKeys.Count == 0)
{
DataObjectException ValidationError = new DataObjectException("Only one key was given for a multiple key object.");
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 " + PrimaryKeys[0].Name +
" = " + ID.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);
}
}
public void Remove(int index)
{
if (index > Count - 1 || index < 0)
{
// Invalid index
DataObjectException InvalidIndex = new DataObjectException("Invalid index.");
throw InvalidIndex;
}
else
{
List.RemoveAt(index);
}
}
/// <summary>
/// Delete the object with this ID from the database.
/// </summary>
public void Delete(int ID)
{
if (PrimaryKeys.Count > 1)
{
DataObjectException ValidationError = new DataObjectException("Only one key was given for a multiple key object.");
throw ValidationError;
}
if (_dataRow != null)
{
if (_dataRow[PrimaryKeys[0].Name] is int)
{
if ((int)_dataRow[PrimaryKeys[0].Name] == ID)
{
Delete();
return;
}
}
}
try
{
string Sql;
Sql = "DELETE FROM " + TableName + " WHERE " + PrimaryKeys[0].Name + " = " + ID.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>
/// 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);
}
}
private void BuildSql()
{
DataCommand.CommandText = "SELECT * FROM " + _dataTable.TableName + " WHERE";
for (int i = 0; i < _dataTable.Columns.Count; i++)
{
if ((!DataCommand.CommandText.EndsWith(" AND")) &&
(!DataCommand.CommandText.EndsWith(" WHERE")))
{
DataCommand.CommandText += " AND";
}
if ((_dataTable.Columns[i].DataType == System.Type.GetType("System.Int32")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Byte")) ||
(_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.Boolean")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Int64")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Decimal")) ||
(_dataTable.Columns[i].DataType == System.Type.GetType("System.Int16")))
{
// Number
if (_dataRow[i].ToString() != String.Empty)
{
DataCommand.CommandText += " [" + _dataTable.Columns[i].ColumnName +
"] = " + _dataRow[i].ToString();
}
}
else if (_dataTable.Columns[i].DataType == System.Type.GetType("System.String"))
{
// Text
if (_dataRow[i].ToString() != String.Empty)
{
DataCommand.CommandText += " [" + _dataTable.Columns[i].ColumnName + "]";
if (_dataRow[i].ToString().IndexOf("'") > 0)
{
_dataRow[i] = _dataRow[i].ToString().Replace("'", "''");
}
switch (_dataSearchType)
{
case SearchTypes.BeginsWith:
DataCommand.CommandText += " LIKE '" + _dataRow[i].ToString() + "%'";
break;
case SearchTypes.EndsWith:
DataCommand.CommandText += " LIKE '%" + _dataRow[i].ToString() + "'";
break;
case SearchTypes.Contains:
DataCommand.CommandText += " LIKE '%" + _dataRow[i].ToString() + "%'";
break;
case SearchTypes.Exact:
DataCommand.CommandText += " LIKE '" + _dataRow[i].ToString() + "'";
break;
}
}
}
else if (_dataTable.Columns[i].DataType == System.Type.GetType("System.DateTime"))
{
// Date / Time
if (_dataRow[i].ToString() != String.Empty)
{
DataCommand.CommandText += " DateDiff(\"d\", " + _dataTable.Columns[i].ColumnName + "," +
"'" + _dataRow[i].ToString() + "') = 0";
}
}
else
{
// Unsupported Type
DataObjectException InvalidDataType = new DataObjectException("Unsupported data type. [" +
_dataTable.Columns[i].DataType.ToString() + "]");
throw InvalidDataType;
}
}
if (DataCommand.CommandText.EndsWith("WHERE"))
{
DataCommand.CommandText = DataCommand.CommandText.Substring(0, DataCommand.CommandText.Length - 6);
}
if (DataCommand.CommandText.EndsWith(" AND"))
{
DataCommand.CommandText = DataCommand.CommandText.Substring(0, DataCommand.CommandText.Length - 4);
}
}
/// <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>
/// 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);
}
}