} // end GetAllShippingOrdersPicked(..)
public static List <ShippingOrder> GetAllShippingOrdersNotPicked(SqlConnection myConnection)
{
var shippingOrderList = new List <ShippingOrder>();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetAllShippingOrdersNotPicked", myConnection)
{
CommandType = CommandType.StoredProcedure
};
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
var shippingOrder = new ShippingOrder(mySqlReader.GetInt32(0))
{
PurchaseOrderId = mySqlReader.GetInt32(1),
UserId = mySqlReader[2] as int?,
UserLastName = mySqlReader[3] as string,
UserFirstName = mySqlReader[4] as string,
Picked = (Boolean)mySqlReader.GetSqlBoolean(5),
ShipDate = mySqlReader[6] as DateTime?,
ShippingTermId = mySqlReader.GetInt32(7),
ShippingTermDesc = mySqlReader.GetString(8),
ShippingVendorName = mySqlReader.GetString(9),
ShipToName = mySqlReader[10] as string,
ShipToAddress = mySqlReader[11] as string,
ShipToCity = mySqlReader[12] as string,
ShipToState = mySqlReader[13] as string,
ShipToZip = mySqlReader[14] as string
};
//Add item to list
shippingOrderList.Add(shippingOrder);
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(shippingOrderList);
} // end GetAllShippingOrdersNotPicked(..)
}//End of fetchProduct(..)
public static List <Product> FetchProductsByActive(Boolean activeState, SqlConnection connection)
{
List <Product> products = new List <Product>();
//?? Null-coalescing operator.
//If the connection is null a new connection will be returned.
SqlConnection conn = connection ?? GetInventoryDbConnection();
try
{
//Establishes the connection.
conn.Open();
//Creates the command object, passing the SP and connection object.
SqlCommand sqlCmd = new SqlCommand("proc_GetProductsByActive", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@Active", activeState ? 1 : 0);
//Creates the reader object by ExecutingReader on the cmd object.
SqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var product = new Product(reader.GetInt32(0))
{
available = reader.GetInt32(1),
reserved = reader.GetInt32(2),
description = reader.GetString(3),
location = reader[4] as string,
unitPrice = (Decimal)reader.GetSqlMoney(5),
Name = reader.GetString(6),
_reorderThreshold = reader[7] as int?,
_reorderAmount = reader[8] as int?,
_onOrder = reader.GetInt32(9),
_shippingDemensions = reader[10] as string,
_shippingWeight = reader[11] as double?,
Active = reader.GetBoolean(12)
};
//Add the current product to the list.
products.Add(product);
//Null the product reference.
}
}
reader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
conn.Close();
}
return(products);
}//End of fetchProductsByActive(..)
}//End of insertProduct(..)
public static Boolean UpdateProduct(Product product, Product originalProduct, SqlConnection connection)
{
//?? Null-coalescing operator.
//If the connection is null a new connection will be returned.
SqlConnection conn = connection ?? GetInventoryDbConnection();
try
{
//Establishes the connection.
conn.Open();
//Creates the command object, passing the SP and connection object.
SqlCommand sqlCmd = new SqlCommand("proc_UpdateProducts", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@ProductID", originalProduct.Id);
sqlCmd.Parameters.AddWithValue("@OnHand", product.reserved);
sqlCmd.Parameters.AddWithValue("@Available", product.available);
sqlCmd.Parameters.AddWithValue("@Description", product.description);
sqlCmd.Parameters.AddWithValue("@Location", product.location ?? Convert.DBNull);
sqlCmd.Parameters.AddWithValue("@UnitPrice", product.unitPrice);
sqlCmd.Parameters.AddWithValue("@ShortDesc", product.Name);
sqlCmd.Parameters.AddWithValue("@ReorderThreshold", product._reorderThreshold ?? Convert.DBNull);
sqlCmd.Parameters.AddWithValue("@ReorderAmount", product._reorderAmount ?? Convert.DBNull);
sqlCmd.Parameters.AddWithValue("@OnOrder", product._onOrder);
sqlCmd.Parameters.AddWithValue("@ShippingDimensions", product._shippingDemensions ?? Convert.DBNull);
sqlCmd.Parameters.AddWithValue("@ShippingWeight", product._shippingWeight ?? Convert.DBNull);
sqlCmd.Parameters.AddWithValue("@Active", product.Active ? 1 : 0);
sqlCmd.Parameters.AddWithValue("@OriginalOnHand", originalProduct.reserved);
sqlCmd.Parameters.AddWithValue("@OriginalAvailable", originalProduct.available);
sqlCmd.Parameters.AddWithValue("@OriginalDescription", originalProduct.description);
sqlCmd.Parameters.AddWithValue("@OriginalLocation", originalProduct.location ?? Convert.DBNull);
sqlCmd.Parameters.AddWithValue("@OriginalUnitPrice", originalProduct.unitPrice);
sqlCmd.Parameters.AddWithValue("@OriginalShortDesc", originalProduct.Name);
sqlCmd.Parameters.AddWithValue("@OriginalReorderThreshold", originalProduct._reorderThreshold ?? Convert.DBNull);
sqlCmd.Parameters.AddWithValue("@OriginalReorderAmount", originalProduct._reorderAmount ?? Convert.DBNull);
sqlCmd.Parameters.AddWithValue("@OriginalOnOrder", originalProduct._onOrder);
sqlCmd.Parameters.AddWithValue("@OriginalShippingDimensions", originalProduct._shippingDemensions ?? Convert.DBNull);
sqlCmd.Parameters.AddWithValue("@OriginalShippingWeight", originalProduct._shippingWeight ?? Convert.DBNull);
sqlCmd.Parameters.AddWithValue("@OriginalActive", originalProduct.Active ? 1 : 0);
//If the procedure returns 1 set to true, if 0 remain false.
if (sqlCmd.ExecuteNonQuery() == 1)
{
return(true);
}
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
conn.Close();
}
return(false);
}//End of updateProduct(...)
}// end GetShippingVendorById
public static List <ShippingVendor> GetAllShippingVendors(SqlConnection myConnection)
{
var shippingVendorList = new List <ShippingVendor>();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetAllShippingVendors", myConnection)
{
CommandType = CommandType.StoredProcedure
};
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
var shippingVendor = new ShippingVendor(mySqlReader.GetInt32(0))
{
Name = mySqlReader.GetString(1),
Address = mySqlReader.GetString(2),
City = mySqlReader.GetString(3),
State = mySqlReader.GetString(4),
Country = mySqlReader.GetString(5),
Zip = mySqlReader.GetString(6),
Phone = mySqlReader.GetString(7),
Contact = mySqlReader.GetString(8),
ContactEmail = mySqlReader.GetString(9),
Active = mySqlReader.GetBoolean(10)
};
//Add item to list
shippingVendorList.Add(shippingVendor);
}
}
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return(shippingVendorList);
}// end GetAllShippingVendors
