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