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