public IBaseProps Create(IBaseProps p) { int rowsAffected = 0; CustomersProps props = (CustomersProps)p; DBCommand command = new DBCommand(); command.CommandText = "usp_CustomersCreate"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@CustomerID", SqlDbType.Int); command.Parameters.Add("@Name", SqlDbType.VarChar); command.Parameters.Add("@Address", SqlDbType.VarChar); command.Parameters.Add("@City", SqlDbType.VarChar); command.Parameters.Add("@State", SqlDbType.Char); command.Parameters.Add("@ZipCode", SqlDbType.Char); command.Parameters[0].Direction = ParameterDirection.Output; command.Parameters["@Name"].Value = props.name; command.Parameters["@Address"].Value = props.address; command.Parameters["@City"].Value = props.city; command.Parameters["@State"].Value = props.state; command.Parameters["@ZipCode"].Value = props.zipCode; //props.ID = (int) command.Parameters["@ProductID"].Value; try { rowsAffected = RunNonQueryProcedure(command); if (rowsAffected == 1) { props.ID = (int)command.Parameters[0].Value; props.ConcurrencyID = 1; return(props); } else { throw new Exception("Unable to insert record. " + props.ToString()); } } catch (Exception e) { // log this error throw; } finally { if (mConnection.State == ConnectionState.Open) { mConnection.Close(); } } }
public bool Update(IBaseProps p) { int rowsAffected = 0; CustomersProps props = (CustomersProps)p; DBCommand command = new DBCommand(); command.CommandText = "usp_CustomersUpdate"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@CustomerID", SqlDbType.Int); command.Parameters.Add("@Name", SqlDbType.NVarChar); command.Parameters.Add("@Address", SqlDbType.NVarChar); command.Parameters.Add("@City", SqlDbType.NVarChar); command.Parameters.Add("@State", SqlDbType.Char); command.Parameters.Add("@ZipCode", SqlDbType.Char); command.Parameters.Add("@ConcurrencyID", SqlDbType.Int); command.Parameters["@CustomerID"].Value = props.ID; command.Parameters["@Name"].Value = props.name; command.Parameters["@Address"].Value = props.address; command.Parameters["@City"].Value = props.city; command.Parameters["@State"].Value = props.state; command.Parameters["@ZipCode"].Value = props.zipCode; command.Parameters["@ConcurrencyID"].Value = props.ConcurrencyID; try { rowsAffected = RunNonQueryProcedure(command); if (rowsAffected == 1) { props.ConcurrencyID++; return(true); } else { string message = "Record cannot be updated. It has been edited by another user."; throw new Exception(message); } } catch (Exception e) { // log this exception throw; } finally { if (mConnection.State == ConnectionState.Open) { mConnection.Close(); } } }
/// <summary> /// /// </summary> public void SetState(string xml) { XmlSerializer serializer = new XmlSerializer(this.GetType()); StringReader reader = new StringReader(xml); CustomersProps p = (CustomersProps)serializer.Deserialize(reader); this.ID = p.ID; this.name = p.name; this.address = p.address; this.city = p.city; this.ConcurrencyID = p.ConcurrencyID; this.state = p.state; this.zipCode = p.zipCode; }
/// <summary> /// Clones this object. /// </summary> /// <returns>A clone of this object.</returns> public Object Clone() { CustomersProps p = new CustomersProps(); p.ID = this.ID; p.name = this.name; p.address = this.address; p.ConcurrencyID = this.ConcurrencyID; p.city = this.city; p.state = this.state; p.zipCode = this.zipCode; return(p); }
public IBaseProps Retrieve(object key) { DBDataReader data = null; CustomersProps props = new CustomersProps(); DBCommand command = new DBCommand(); command.CommandText = "usp_CustomersSelect"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@CustomerID", SqlDbType.Int); command.Parameters["@CustomerID"].Value = (Int32)key; try { data = RunProcedure(command); if (!data.IsClosed) { if (data.Read()) { props.SetState(data); } else { throw new Exception("Record does not exist in the database."); } } return(props); } catch (Exception e) { // log this exception throw; } finally { if (data != null) { if (!data.IsClosed) { data.Close(); } } } }
public object RetrieveAll(Type type) { List <CustomersProps> results = new List <CustomersProps>(); DBDataReader data = null; CustomersProps props = new CustomersProps(); DBCommand command = new DBCommand(); command.CommandText = "usp_CustomersSelectAll"; command.CommandType = CommandType.StoredProcedure; try { data = RunProcedure(command); if (!data.IsClosed) { while (data.Read()) { props = new CustomersProps(); props.SetState(data); results.Add(props); } } return(results); } catch (Exception e) { // log this exception throw; } finally { if (data != null) { if (!data.IsClosed) { data.Close(); } } } }
public bool Delete(IBaseProps props) { int rowsAffected = 0; CustomersProps p = (CustomersProps)props; DBCommand command = new DBCommand(); command.CommandText = "usp_CustomersDelete"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@CustomerID", SqlDbType.Int); command.Parameters["@CustomerID"].Value = p.ID; command.Parameters.Add("@ConcurrencyID", SqlDbType.Int); command.Parameters["@ConcurrencyID"].Value = p.ConcurrencyID; try { rowsAffected = RunNonQueryProcedure(command); if (rowsAffected != 1) { string message = "Record was not deleted. Perhaps the key you specified does not exist."; throw new Exception(message); } return(true); } catch (Exception e) { // log this error throw; } finally { if (mConnection.State == ConnectionState.Open) { mConnection.Close(); } } return(rowsAffected != 0); }