public void TestMethodInsertIntoCategory()
{
var c = new SqlConnection(Properties.Settings.Default.dbConnectionString);
Boolean expectedInsert = true;
Boolean expectedDelete = true;
int expectedCategoryID = dbG.GetID("Category", c);
string expectedName = "New Fall Books 2017 Edition";
string expectedDescription = "sample";
List <object> bookstore = new List <object>(
new object[] { expectedName, expectedDescription }
);
Boolean actualInsert = dbQ.INSERT_INTO_TABLE("Category", bookstore);
Assert.AreEqual(expectedInsert, actualInsert);
ds = dbQ.SELECT_FROM_TABLE("SELECT * FROM Category WHERE Name = '" + expectedName + "'");
string actualName = ds.Tables[0].Rows[0]["Name"].ToString();
string actualDescription = ds.Tables[0].Rows[0]["Description"].ToString();
int actualCategoryID = Int32.Parse(ds.Tables[0].Rows[0]["CategoryID"].ToString());
Assert.AreEqual(expectedName, actualName);
Assert.AreEqual(expectedDescription, actualDescription);
Assert.AreEqual(expectedCategoryID, actualCategoryID);
Boolean actualDelete = dbQ.DELETE_FROM_TABLE("DELETE FROM Category WHERE Name = '" + expectedName + "'");
Assert.AreEqual(expectedDelete, actualDelete);
c.Close();
}
public Boolean AdminUsers_Insert(List <object> bookstore, SqlConnection conn)
{
DBGetID gID = new DBGetID();
int id = gID.GetID("AdminUsers", conn);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO AdminUsers VALUES "
+ "(@UserID, @AdminLevel)";
cmd.Parameters.AddWithValue("@UserID", id);
cmd.Parameters.AddWithValue("@AdminLevel", bookstore[0]);
conn.Open();
int result = cmd.ExecuteNonQuery();
return(!(result < 0));
}
public Boolean Supplier_Insert(List <object> bookstore, SqlConnection conn)
{
DBGetID gID = new DBGetID();
int id = gID.GetID("Supplier", conn);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO Supplier VALUES "
+ "(@SupplierId, @Name)";
cmd.Parameters.AddWithValue("@SupplierId", id);
cmd.Parameters.AddWithValue("@Name", bookstore[0]);
conn.Open();
int result = cmd.ExecuteNonQuery();
return(!(result < 0));
}
public Boolean Category_Insert(List <object> bookstore, SqlConnection conn)
{
DBGetID gID = new DBGetID();
int id = gID.GetID("Category", conn);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO Category VALUES "
+ "(@CategoryID, @Name, @Description)";
cmd.Parameters.AddWithValue("@CategoryID", id);
cmd.Parameters.AddWithValue("@Name", bookstore[0]);
cmd.Parameters.AddWithValue("@Description", bookstore[1]);
conn.Open();
int result = cmd.ExecuteNonQuery();
return(!(result < 0));
}
public int PlaceOrderFinal(int userID)
{
DBQueries dbQ = new DBQueries();
bool res = false;
var conn = new SqlConnection(Properties.Settings.Default.dbConnectionString);
int orderId = 0;
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO Orders (UserID, Status) VALUES "
+ " (@UserID, @Status)";
cmd.Parameters.AddWithValue("@UserID", userID);
cmd.Parameters.AddWithValue("@Status", "P");
conn.Open();
cmd.ExecuteScalar();
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
DBGetID dbG = new DBGetID();
orderId = (dbG.GetID("Orders", conn)) - 1;
foreach (var item in orderItemList)
{
List <object> bookstore1 = new List <object>(
new object[] { orderId, item.BookID, item.Quantity }
);
dbQ.INSERT_INTO_TABLE("OrderItem", bookstore1);
}
return(orderId);
}
public void TestMethodDeleteFromSupplier()
{
var c = new SqlConnection(Properties.Settings.Default.dbConnectionString);
Boolean expectedInsert = true;
Boolean expectedDelete = true;
string expectedName = "Google";
int expectedSupplierID = dbG.GetID("Supplier", c);
List <object> bookstore = new List <object>(
new object[] { expectedName }
);
Boolean actualInsert = dbQ.INSERT_INTO_TABLE("Supplier", bookstore);
Assert.AreEqual(expectedInsert, actualInsert);
ds = dbQ.SELECT_FROM_TABLE("SELECT * FROM Supplier WHERE Name = '" + expectedName + "'");
int actualSupplierID = Int32.Parse(ds.Tables[0].Rows[0]["SupplierId"].ToString());
string actualName = ds.Tables[0].Rows[0]["Name"].ToString();
Assert.AreEqual(expectedSupplierID, actualSupplierID);
Assert.AreEqual(expectedName, actualName);
//Delete from Database
Boolean actualDelete = dbQ.DELETE_FROM_TABLE("DELETE FROM Supplier WHERE Name = '" + expectedName + "'");
Assert.AreEqual(expectedDelete, actualDelete);
Boolean expectedFinalDeleteReturn = true;
Boolean finalDeleteReturn = false;
ds = dbQ.SELECT_FROM_TABLE("SELECT * FROM Supplier WHERE Name = '" + expectedName + "'");
if (ds.Tables[0].Rows.Count == 0)
{
finalDeleteReturn = true;
}
Assert.AreEqual(expectedFinalDeleteReturn, finalDeleteReturn);
c.Close();
}
public Boolean UserData_Insert(List <object> bookstore, SqlConnection conn)
{
DBGetID gID = new DBGetID();
int id = gID.GetID("UserData", conn);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO UserData VALUES "
+ "(@UserID, @Username, @Password, @Type, @Manager, @FullName)";
cmd.Parameters.AddWithValue("@UserID", id);
cmd.Parameters.AddWithValue("@Username", bookstore[0]);
cmd.Parameters.AddWithValue("@Password", bookstore[1]);
cmd.Parameters.AddWithValue("@Type", bookstore[2]);
cmd.Parameters.AddWithValue("@Manager", bookstore[3]);
cmd.Parameters.AddWithValue("@FullName", bookstore[4]);
conn.Open();
int result = cmd.ExecuteNonQuery();
return(!(result < 0));
}