/// <summary>
/// Looks up the next incremental primary key value to use for a new record.
/// </summary>
/// <returns></returns>
private ReservedKey GetNextKey()
{
object dbResult;
int nextid = 0;
ReservedKey keyReservation;
try
{
DataCommand.Connection = ConnHandler.RequestConnection(Alias, this);
DataCommand.CommandText = "SELECT MAX(" + PrimaryKeys[0].Name + ") FROM " + TableName;
dbResult = DataCommand.ExecuteScalar();
if (dbResult is int)
{
nextid = (int)dbResult;
}
nextid++;
keyReservation = ReserveKey(new ReservedKey(TableName, nextid));
}
catch (Exception err)
{
throw err;
}
finally
{
ConnHandler.ReleaseConnection(this, DataCommand.Connection);
}
return(keyReservation);
}
public DataTable Search()
{
OleDbDataAdapter dbadpSearch = new OleDbDataAdapter(DataCommand);
DataTable SearchResults = new DataTable();
try
{
if (_searchSQL == String.Empty)
{
BuildSql();
}
else
{
_dbcmdData.CommandText = _searchSQL;
_searchSQL = "";
}
DataCommand.Connection = ConnHandler.RequestConnection(Alias, this);
dbadpSearch.Fill(SearchResults);
}
catch (Exception err)
{
throw err;
}
finally
{
ConnHandler.ReleaseConnection(this, DataCommand.Connection);
}
return(SearchResults);
}
public void ExecuteNonQuery(string sql)
{
DataCommand.Connection = ConnHandler.RequestConnection(Alias, this);
DataCommand.CommandText = sql;
DataCommand.ExecuteNonQuery();
ConnHandler.ReleaseConnection(this, DataCommand.Connection);
}
/// <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);
}
}
/// <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>
/// CAUTION! Deletes every record in the table.
/// </summary>
public void Zap()
{
try
{
DataCommand.Connection = ConnHandler.RequestConnection(Alias, this);
DataCommand.CommandTimeout = 120;
DataCommand.CommandText = "TRUNCATE TABLE " + TableName;
DataCommand.ExecuteNonQuery();
}
catch (Exception err)
{
throw new DataObjectException("Table Zap failed.", err);
}
finally
{
ConnHandler.ReleaseConnection(this, DataCommand.Connection);
}
}
public DataTable GetAllData(int maxRecords)
{
DataTable AllRecords = new DataTable(TableName);
try
{
if (maxRecords > 0)
{
_dbcmdData.CommandText = "SELECT TOP " + maxRecords + " * FROM " + TableName +
" ORDER BY ";
}
else
{
_dbcmdData.CommandText = "SELECT * FROM " + TableName +
" ORDER BY ";
}
for (int i = 0; i < PrimaryKeys.Count; i++)
{
if (i > 0)
{
_dbcmdData.CommandText += ", ";
}
_dbcmdData.CommandText += PrimaryKeys[i].Name;
}
DataCommand.Connection = ConnHandler.RequestConnection(Alias, this);
DataAdapter.Fill(AllRecords);
return(AllRecords);
}
catch (Exception err)
{
throw err;
}
finally
{
ConnHandler.ReleaseConnection(this, DataCommand.Connection);
}
}
/// <summary>
/// Retrieves all data from the specified object
/// </summary>
/// <param name="orderBy">The field name you wish to order by</param>
/// <returns></returns>
public DataTable GetAllData(string orderBy)
{
DataTable AllRecords = new DataTable(TableName);
try
{
_dbcmdData.CommandText = "SELECT * FROM " + TableName +
" ORDER BY " + orderBy;
DataCommand.Connection = ConnHandler.RequestConnection(Alias, this);
DataAdapter.Fill(AllRecords);
return(AllRecords);
}
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);
}
}
/// <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>
/// 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);
}
}