public IBaseProps Create(IBaseProps p)
{
int rowsAffected = 0;
CustomerProps props = (CustomerProps)p;
DBCommand command = new DBCommand();
command.CommandText = "usp_CustomerCreate";
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["@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;
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 void TestSetState()
{
string cString = c.GetState();
CustomerProps c2 = new CustomerProps();
c2.SetState(cString);
Assert.AreEqual(c.ID, c2.ID);
Assert.AreEqual(c.name, c2.name);
Assert.AreEqual(c.address, c2.address);
Assert.AreEqual(c.city, c2.city);
Assert.AreEqual(c.state, c2.state);
Assert.AreEqual(c.zip, c2.zip);
Assert.AreEqual(c.ConcurrencyID, c2.ConcurrencyID);
}
public void TestUpdate()
{
CustomerProps props = (CustomerProps)db.Retrieve(1);
props.name = "Frog, Frog";
props.address = "123 Saturday Market";
props.city = "Eugene";
props.state = "OR";
props.zipCode = "97403";
db.Update(props);
CustomerProps updatedProps = (CustomerProps)db.Retrieve(1);
Assert.AreEqual("Frog, Frog", updatedProps.name);
Assert.AreEqual("Eugene", updatedProps.city);
}
public void TestCreate()
{
CustomerProps props = new CustomerProps();
props.name = "Blingy, Lingy";
props.address = "1234 Earth";
props.city = "Eugene";
props.state = "OR";
props.zipcode = "97402";
CustomerProps newprops = (CustomerProps)db.Create(props);
props = (CustomerProps)db.Retrieve(newprops.ID);
Assert.AreEqual(newprops.ID, props.ID);
Assert.AreEqual(newprops.name, props.name);
}
public void GetStateTest()
{
CustomerProps props = new CustomerProps();
props.ID = 1;
props.name = "Mickey Mouse";
props.address = "Main Street";
props.city = "Orlando";
props.state = "FL";
props.zipCode = "10001";
props.ConcurrencyID = 4;
string output = props.GetState();
Console.WriteLine(output);
}
public void TestCreate()
{
CustomerProps props = new CustomerProps();
props.name = "Bob Bob";
props.address = "McKenzie View Drive";
props.city = "Springfield";
props.state = "OR";
props.zipCode = "97477";
CustomerProps newProps = (CustomerProps)db.Create(props);
props = (CustomerProps)db.Retrieve(newProps.ID);
Assert.AreEqual(props.name, newProps.name);
Assert.AreEqual(props.state, newProps.state);
Assert.AreEqual(props.zipCode, newProps.zipCode);
}
//test the methods we wrote
public void TestClone()
{
CustomerProps c2 = (CustomerProps)testc.Clone();
//assert that areEqual all the properties
Assert.AreEqual(testc.customerID, c2.customerID);
Assert.AreEqual(testc.name, c2.name);
Assert.AreEqual(testc.address, c2.address);
Assert.AreEqual(testc.city, c2.city);
Assert.AreEqual(testc.state, c2.state);
Assert.AreEqual(testc.zipCode, c2.zipCode);
Assert.AreEqual(testc.concurrencyID, c2.concurrencyID);
testc.customerID = 4;
Assert.AreNotEqual(testc.customerID, c2.customerID);
}
protected override void SetUp()
{
mProps = new CustomerProps();
mOldProps = new CustomerProps();
if (this.mConnectionString == "")
{
mdbReadable = new CustomerDB();
mdbWriteable = new CustomerDB();
}
else
{
mdbReadable = new CustomerDB(this.mConnectionString);
mdbWriteable = new CustomerDB(this.mConnectionString);
}
}
} // end of Delete()
// returns one object
public IBaseProps Retrieve(Object key)
{
DBDataReader data = null;
CustomerProps props = new CustomerProps();
DBCommand command = new DBCommand();
command.CommandText = "usp_CustomerSelect";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CustomerID", SqlDbType.Int);
command.Parameters["@CustomerID"].Value = (Int32)key;
try
{
// creates data reader
data = RunProcedure(command);
// if data reader is not closed
if (!data.IsClosed)
{
// if data reader is read
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();
}
}
}
} //end of Retrieve()
public void TestResetDatabase()
{
db = new CustomerDB(dataSource);
DBCommand command = new DBCommand();
command.CommandText = "usp_testingResetData";
command.CommandType = CommandType.StoredProcedure;
db.RunNonQueryProcedure(command);
newProps = new CustomerProps
{
name = "Mouse, Mickey",
address = "123 Main Street",
city = "Orlando",
state = "FL",
zipCode = "10001",
};
}
public void SetStateTest()
{
CustomerProps props2 = new CustomerProps();
props2.SetState(props.GetState());
Assert.AreEqual(props.ID, props2.ID);
Assert.AreEqual(props.name, props2.name);
Assert.AreEqual(props.address, props2.address);
Assert.AreEqual(props.state, props2.state);
Assert.AreEqual(props.city, props2.city);
Assert.AreEqual(props.zipcode, props2.zipcode);
Assert.AreEqual(props.ConcurrencyID, props2.ConcurrencyID);
// do productprops in the same way.
//
}
public void TestDelete()
{
CustomerSQLDB db = new CustomerSQLDB(dataSource);
CustomerProps props = new CustomerProps();
props.name = "Trevor";
props.address = "123 Main St";
props.city = "Eugene";
props.state = "OR";
props.zipcode = "97478";
db.Create(props);
CustomerProps retCustomer = (CustomerProps)db.Retrieve(props.ID);
db.Delete(retCustomer);
Assert.Throws <Exception>(() => db.Retrieve(props.ID));
}
public void Setup()
{
db = new CustomerSQLDB(dataSource);
props = new CustomerProps();
DBCommand command = new DBCommand();
command.CommandText = "usp_testingResetData";
command.CommandType = CommandType.StoredProcedure;
db.RunNonQueryProcedure(command);
testc = new CustomerProps();
testc.customerID = 1;
testc.name = "Daisy Duck";
testc.address = "12 Banana Dr";
testc.city = "Islamorada";
testc.state = "FL";
testc.zipCode = "85762";
testc.concurrencyID = 1;
}
/// <summary>
/// </summary>
public bool Delete(IBaseProps p)
{
//return true;
CustomerProps props = (CustomerProps)p;
int rowsAffected = 0;
DBCommand command = new DBCommand();
command.CommandText = "usp_CustomerDelete";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CustomerID", SqlDbType.Int);
command.Parameters.Add("@ConcurrencyID", SqlDbType.Int);
command.Parameters["@CustomerID"].Value = props.id;
command.Parameters["@ConcurrencyID"].Value = props.concurrencyID;
try
{
rowsAffected = RunNonQueryProcedure(command);
if (rowsAffected == 1)
{
return(true);
}
else
{
string message = "Record cannot be deleted. 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();
}
}
} // end of Delete()
public void TestCreate()
{
CustomerSQLDB db = new CustomerSQLDB(dataSource);
CustomerProps props = new CustomerProps();
props.name = "Trevor";
props.address = "123 Main St";
props.city = "Eugene";
props.state = "OR";
props.zipcode = "97478";
db.Create(props);
CustomerProps retCustomer = (CustomerProps)db.Retrieve(props.ID);
Assert.AreEqual("Trevor", retCustomer.name);
Assert.AreEqual("123 Main St", retCustomer.address);
Assert.AreEqual("Eugene", retCustomer.city);
Assert.AreEqual("OR", retCustomer.state);
Assert.AreEqual("97478", retCustomer.zipcode);
}
public void TestUpdate()
{
CustomerSQLDB db = new CustomerSQLDB(dataSource);
CustomerProps props = (CustomerProps)db.Retrieve(23);
props.name = "zzz";
props.address = "zzz";
props.city = "zzz";
props.state = "OR";
props.zipcode = "97478";
db.Update(props);
CustomerProps propsUpdated = (CustomerProps)db.Retrieve(23);
Assert.AreEqual("zzz", propsUpdated.name);
Assert.AreEqual("zzz", propsUpdated.address);
Assert.AreEqual("zzz", propsUpdated.city);
Assert.AreEqual("OR", propsUpdated.state);
Assert.AreEqual("97478", propsUpdated.zipcode);
}
public void UpdateTest()
{
CustomerProps props = (CustomerProps)db.Create(newProps);
props.name = "Mouse, Minne";
props.address = "987 First Street";
props.city = "Anaheim";
props.state = "CA";
props.zipCode = "99999";
int id = props.ID;
Assert.IsTrue(db.Update(props));
props = (CustomerProps)db.Retrieve(id);
Assert.AreEqual(id, props.ID);
Assert.AreEqual("Mouse, Minne", props.name);
Assert.AreEqual("987 First Street", props.address);
Assert.AreEqual("Anaheim", props.city);
Assert.AreEqual("CA", props.state);
Assert.AreEqual("99999", props.zipCode);
Assert.AreEqual(2, props.ConcurrencyID);
}
public void TestCreateCustomer()
{
CustomerSQLDB db = new CustomerSQLDB(dataSource);
// declare and instantiate new CustomerProps c
CustomerProps c = new CustomerProps();
c.name = "John Rolfe";
c.address = "1 Branch Hut";
c.city = "Jamestown";
c.state = "VA";
c.zipcode = "23233";
// Create record for c in the database
db.Create(c);
// Retrieve Customer c from the database and store in variable cCreated
CustomerProps cCreated = (CustomerProps)db.Retrieve(c.ID);
Assert.AreEqual("John Rolfe", cCreated.name);
Assert.AreEqual("23233", cCreated.zipcode);
}
public void TestUpdate()
{
// extract all column data from (1)
CustomerProps props = (CustomerProps)db.Retrieve(1);
// change columns data
props.name = "Linda Fillery";
props.address = "345 South Fairy LN";
props.city = "Casino Bay";
props.state = "FL";
props.zipcode = "77897";
// update columns data
db.Update(props);
//check all new column data
CustomerProps prop = (CustomerProps)db.Retrieve(1);
Assert.AreNotEqual(prop.name, "Molunguri, A");
Assert.AreNotEqual(prop.address, "1108 Johanna Bay Drive");
Assert.AreNotEqual(prop.city, "Birmingham");
Assert.AreNotEqual(prop.state, "AL");
Assert.AreNotEqual(prop.zipcode, "35216-6909");
}
public object RetrieveAll(Type type)
{
List <CustomerProps> list = new List <CustomerProps>();
DBDataReader reader = null;
CustomerProps props;
try
{
reader = RunProcedure("usp_CustomerSelectAll");
if (!reader.IsClosed)
{
while (reader.Read())
{
props = new CustomerProps();
props.SetState(reader);
list.Add(props);
}
}
return(list);
}
catch (Exception e)
{
throw;
}
finally
{
if (!reader.IsClosed)
{
reader.Close();
}
}
}
public void TestCreate()
{
CustomerProps props = new CustomerProps();
props.name = "Linda Seafoam";
props.address = "145 South Lindholm Ave";
props.city = "Yukata";
props.state = "FL";
props.zipcode = "88765";
props = (CustomerProps)db.Create(props);
CustomerProps prop2 = (CustomerProps)db.Retrieve(props.ID);
Assert.AreEqual(props.name, prop2.name);
Assert.AreEqual(props.address, prop2.address);
Assert.AreEqual(props.city, prop2.city);
Assert.AreEqual(props.state, prop2.state);
Assert.AreEqual(props.zipcode, prop2.zipcode);
//CustomerProps props2 = (CustomerProps)db.Retrieve(780);
//Assert.AreEqual(props2.ID, 780);
}
// *** I added these 2 so that I could create a
// business object from a properties object
// I added the new constructors to the base class
public Customer(CustomerProps props)
: base(props)
{
SetUp();
LoadProps(props);
}
public static void Delete(CustomerProps c)
{
CustomerDB db = new CustomerDB();
db.Delete(c);
}
public Customer(CustomerProps props, string cnString)
: base(props, cnString)
{
}
// *** I added these 2 so that I could create a
// business object from a properties object
// I added the new constructors to the base class
public Customer(CustomerProps props)
: base(props)
{
}
public void TestClone()
{
CustomerProps newC = (CustomerProps)c.Clone();
Assert.IsTrue(c.name == newC.name);
}
public void TestRetrieve()
{
CustomerProps createdc = (CustomerProps)cdb.Retrieve(c.customerID);
Assert.AreEqual(createdc.customerID, c.customerID);
}
public IBaseProps Create(IBaseProps p) // possibly might need to change to props to match origional parameter.
{
// @CustomerID int output,
//@Name varchar(100)
// @Address varchar(50)
// @City varchar(20)
// @State char(2)
// @ZipCode char(15)
int rowsAffected = 0;
CustomerProps props = (CustomerProps)p;
DBCommand command = new DBCommand();
command.CommandText = "usp_CustomerCreate";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CustomerID", SqlDbType.Int);
command.Parameters.Add("@Name", SqlDbType.VarChar);// these match the parameters int he stored procedure. name adreess etc
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;
// there were three statements here before, it did not include customerID ask about if it needs to be
//added
// assuming it would be redundant if it wasnt there before....
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;
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 void CloneTest()
{
CustomerProps props2 = new CustomerProps();
}
