public static List <OrderHeader> List()
{
List <OrderHeader> OrderHeaderList = new List <OrderHeader>();
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [OrderId] "
+ "FROM "
+ " [OrderHeader] "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
OrderHeader OrderHeader = new OrderHeader();
while (reader.Read())
{
OrderHeader = new OrderHeader();
OrderHeader.OrderId = System.Convert.ToInt32(reader["OrderId"]);
OrderHeaderList.Add(OrderHeader);
}
reader.Close();
}
catch (SqlException)
{
return(OrderHeaderList);
}
finally
{
connection.Close();
}
return(OrderHeaderList);
}
public static List <CatalogItem> List()
{
List <CatalogItem> CatalogItemList = new List <CatalogItem>();
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [CatalogItemId] "
+ "FROM "
+ " [CatalogItem] "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
CatalogItem CatalogItem = new CatalogItem();
while (reader.Read())
{
CatalogItem = new CatalogItem();
CatalogItem.CatalogItemId = System.Convert.ToInt32(reader["CatalogItemId"]);
CatalogItemList.Add(CatalogItem);
}
reader.Close();
}
catch (SqlException)
{
return(CatalogItemList);
}
finally
{
connection.Close();
}
return(CatalogItemList);
}
public static List <ItemOption> List()
{
List <ItemOption> ItemOptionList = new List <ItemOption>();
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [ItemOptionId] "
+ "FROM "
+ " [ItemOption] "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
ItemOption ItemOption = new ItemOption();
while (reader.Read())
{
ItemOption = new ItemOption();
ItemOption.ItemOptionId = System.Convert.ToInt32(reader["ItemOptionId"]);
ItemOptionList.Add(ItemOption);
}
reader.Close();
}
catch (SqlException)
{
return(ItemOptionList);
}
finally
{
connection.Close();
}
return(ItemOptionList);
}
public static DataTable SelectAll()
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [ItemOption].[ItemOptionId] "
+ " ,[ItemOption].[Description] "
+ "FROM "
+ " [ItemOption] "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static bool Delete(OrderDetail OrderDetail)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string deleteStatement
= "DELETE FROM "
+ " [OrderDetail] "
+ "WHERE "
+ " [OrderDetailId] = @OldOrderDetailId "
+ " AND [OrderId] = @OldOrderId "
+ " AND [Quantity] = @OldQuantity "
+ " AND [CatalogItemId] = @OldCatalogItemId "
+ " AND [Price] = @OldPrice "
+ " AND ((@OldSpecialInstructions IS NULL AND [SpecialInstructions] IS NULL) OR [SpecialInstructions] = @OldSpecialInstructions) "
+ " AND [DiscountPercent] = @OldDiscountPercent "
+ "";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.CommandType = CommandType.Text;
deleteCommand.Parameters.AddWithValue("@OldOrderDetailId", OrderDetail.OrderDetailId);
deleteCommand.Parameters.AddWithValue("@OldOrderId", OrderDetail.OrderId);
deleteCommand.Parameters.AddWithValue("@OldQuantity", OrderDetail.Quantity);
deleteCommand.Parameters.AddWithValue("@OldCatalogItemId", OrderDetail.CatalogItemId);
deleteCommand.Parameters.AddWithValue("@OldPrice", OrderDetail.Price);
if (OrderDetail.SpecialInstructions != null)
{
deleteCommand.Parameters.AddWithValue("@OldSpecialInstructions", OrderDetail.SpecialInstructions);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldSpecialInstructions", DBNull.Value);
}
deleteCommand.Parameters.AddWithValue("@OldDiscountPercent", OrderDetail.DiscountPercent);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static OrderDetail Select_Record(OrderDetail OrderDetailPara)
{
OrderDetail OrderDetail = new OrderDetail();
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [OrderDetailId] "
+ " ,[OrderId] "
+ " ,[Quantity] "
+ " ,[CatalogItemId] "
+ " ,[Price] "
+ " ,[SpecialInstructions] "
+ " ,[DiscountPercent] "
+ "FROM "
+ " [OrderDetail] "
+ "WHERE "
+ " [OrderDetailId] = @OrderDetailId "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
selectCommand.Parameters.AddWithValue("@OrderDetailId", OrderDetailPara.OrderDetailId);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
OrderDetail.OrderDetailId = System.Convert.ToInt32(reader["OrderDetailId"]);
OrderDetail.OrderId = System.Convert.ToInt32(reader["OrderId"]);
OrderDetail.Quantity = System.Convert.ToDecimal(reader["Quantity"]);
OrderDetail.CatalogItemId = System.Convert.ToInt32(reader["CatalogItemId"]);
OrderDetail.Price = System.Convert.ToDecimal(reader["Price"]);
OrderDetail.SpecialInstructions = reader["SpecialInstructions"] is DBNull ? null : reader["SpecialInstructions"].ToString();
OrderDetail.DiscountPercent = System.Convert.ToByte(reader["DiscountPercent"]);
}
else
{
OrderDetail = null;
}
reader.Close();
}
catch (SqlException)
{
return(OrderDetail);
}
finally
{
connection.Close();
}
return(OrderDetail);
}
public static CatalogItem Select_Record(CatalogItem CatalogItemPara)
{
CatalogItem CatalogItem = new CatalogItem();
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [CatalogItemId] "
+ " ,[Name] "
+ " ,[Description] "
+ " ,[Sku] "
+ " ,[QbItemId] "
+ "FROM "
+ " [CatalogItem] "
+ "WHERE "
+ " [CatalogItemId] = @CatalogItemId "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
selectCommand.Parameters.AddWithValue("@CatalogItemId", CatalogItemPara.CatalogItemId);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
CatalogItem.CatalogItemId = System.Convert.ToInt32(reader["CatalogItemId"]);
CatalogItem.Name = System.Convert.ToString(reader["Name"]);
CatalogItem.Description = reader["Description"] is DBNull ? null : reader["Description"].ToString();
CatalogItem.Sku = reader["Sku"] is DBNull ? null : reader["Sku"].ToString();
CatalogItem.QbItemId = reader["QbItemId"] is DBNull ? null : reader["QbItemId"].ToString();
}
else
{
CatalogItem = null;
}
reader.Close();
}
catch (SqlException)
{
return(CatalogItem);
}
finally
{
connection.Close();
}
return(CatalogItem);
}
public static bool Update(OrderDetailOptions oldOrderDetailOptions,
OrderDetailOptions newOrderDetailOptions)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string updateStatement
= "UPDATE "
+ " [OrderDetailOptions] "
+ "SET "
+ " [OrderDetailId] = @NewOrderDetailId "
+ " ,[ItemOptionId] = @NewItemOptionId "
+ " ,[Price] = @NewPrice "
+ "WHERE "
+ " [OrderOptionId] = @OldOrderOptionId "
+ " AND [OrderDetailId] = @OldOrderDetailId "
+ " AND [ItemOptionId] = @OldItemOptionId "
+ " AND [Price] = @OldPrice "
+ "";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.CommandType = CommandType.Text;
updateCommand.Parameters.AddWithValue("@NewOrderDetailId", newOrderDetailOptions.OrderDetailId);
updateCommand.Parameters.AddWithValue("@NewItemOptionId", newOrderDetailOptions.ItemOptionId);
updateCommand.Parameters.AddWithValue("@NewPrice", newOrderDetailOptions.Price);
updateCommand.Parameters.AddWithValue("@OldOrderOptionId", oldOrderDetailOptions.OrderOptionId);
updateCommand.Parameters.AddWithValue("@OldOrderDetailId", oldOrderDetailOptions.OrderDetailId);
updateCommand.Parameters.AddWithValue("@OldItemOptionId", oldOrderDetailOptions.ItemOptionId);
updateCommand.Parameters.AddWithValue("@OldPrice", oldOrderDetailOptions.Price);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static OrderDetailOptions Select_Record(OrderDetailOptions OrderDetailOptionsPara)
{
OrderDetailOptions OrderDetailOptions = new OrderDetailOptions();
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [OrderOptionId] "
+ " ,[OrderDetailId] "
+ " ,[ItemOptionId] "
+ " ,[Price] "
+ "FROM "
+ " [OrderDetailOptions] "
+ "WHERE "
+ " [OrderOptionId] = @OrderOptionId "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
selectCommand.Parameters.AddWithValue("@OrderOptionId", OrderDetailOptionsPara.OrderOptionId);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
OrderDetailOptions.OrderOptionId = System.Convert.ToInt32(reader["OrderOptionId"]);
OrderDetailOptions.OrderDetailId = System.Convert.ToInt32(reader["OrderDetailId"]);
OrderDetailOptions.ItemOptionId = System.Convert.ToInt32(reader["ItemOptionId"]);
OrderDetailOptions.Price = System.Convert.ToDecimal(reader["Price"]);
}
else
{
OrderDetailOptions = null;
}
reader.Close();
}
catch (SqlException)
{
return(OrderDetailOptions);
}
finally
{
connection.Close();
}
return(OrderDetailOptions);
}
public static bool Add(OrderDetailOptions OrderDetailOptions)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string insertStatement
= "INSERT "
+ " [OrderDetailOptions] "
+ " ( "
+ " [OrderDetailId] "
+ " ,[ItemOptionId] "
+ " ,[Price] "
+ " ) "
+ "VALUES "
+ " ( "
+ " @OrderDetailId "
+ " ,@ItemOptionId "
+ " ,@Price "
+ " ) "
+ "";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.CommandType = CommandType.Text;
insertCommand.Parameters.AddWithValue("@OrderDetailId", OrderDetailOptions.OrderDetailId);
insertCommand.Parameters.AddWithValue("@ItemOptionId", OrderDetailOptions.ItemOptionId);
insertCommand.Parameters.AddWithValue("@Price", OrderDetailOptions.Price);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static DataTable SelectAll()
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [OrderHeader].[OrderId] "
+ " ,[OrderHeader].[UserId] "
+ " ,[OrderHeader].[OrderDate] "
+ " ,[OrderHeader].[CustomerId] "
+ " ,[OrderHeader].[OrderTotal] "
+ " ,[OrderHeader].[SalesTax] "
+ " ,[OrderHeader].[SalesTaxCode] "
+ " ,[OrderHeader].[ShippingCharge] "
+ " ,[OrderHeader].[QbUpdated] "
+ " ,[OrderHeader].[SalesTaxAmt] "
+ " ,[OrderHeader].[DiscountAmount] "
+ " ,[OrderHeader].[Status] "
+ " ,[OrderHeader].[bTestOrder] "
+ "FROM "
+ " [OrderHeader] "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static ItemOption Select_Record(ItemOption ItemOptionPara)
{
ItemOption ItemOption = new ItemOption();
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [ItemOptionId] "
+ " ,[Description] "
+ "FROM "
+ " [ItemOption] "
+ "WHERE "
+ " [ItemOptionId] = @ItemOptionId "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
selectCommand.Parameters.AddWithValue("@ItemOptionId", ItemOptionPara.ItemOptionId);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
ItemOption.ItemOptionId = System.Convert.ToInt32(reader["ItemOptionId"]);
ItemOption.Description = System.Convert.ToString(reader["Description"]);
}
else
{
ItemOption = null;
}
reader.Close();
}
catch (SqlException)
{
return(ItemOption);
}
finally
{
connection.Close();
}
return(ItemOption);
}
public static bool Update(ItemOption oldItemOption,
ItemOption newItemOption)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string updateStatement
= "UPDATE "
+ " [ItemOption] "
+ "SET "
+ " [Description] = @NewDescription "
+ "WHERE "
+ " [ItemOptionId] = @OldItemOptionId "
+ " AND [Description] = @OldDescription "
+ "";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.CommandType = CommandType.Text;
updateCommand.Parameters.AddWithValue("@NewDescription", newItemOption.Description);
updateCommand.Parameters.AddWithValue("@OldItemOptionId", oldItemOption.ItemOptionId);
updateCommand.Parameters.AddWithValue("@OldDescription", oldItemOption.Description);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static bool Delete(OrderDetailOptions OrderDetailOptions)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string deleteStatement
= "DELETE FROM "
+ " [OrderDetailOptions] "
+ "WHERE "
+ " [OrderOptionId] = @OldOrderOptionId "
+ " AND [OrderDetailId] = @OldOrderDetailId "
+ " AND [ItemOptionId] = @OldItemOptionId "
+ " AND [Price] = @OldPrice "
+ "";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.CommandType = CommandType.Text;
deleteCommand.Parameters.AddWithValue("@OldOrderOptionId", OrderDetailOptions.OrderOptionId);
deleteCommand.Parameters.AddWithValue("@OldOrderDetailId", OrderDetailOptions.OrderDetailId);
deleteCommand.Parameters.AddWithValue("@OldItemOptionId", OrderDetailOptions.ItemOptionId);
deleteCommand.Parameters.AddWithValue("@OldPrice", OrderDetailOptions.Price);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static bool Add(ItemOption ItemOption)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string insertStatement
= "INSERT "
+ " [ItemOption] "
+ " ( "
+ " [Description] "
+ " ) "
+ "VALUES "
+ " ( "
+ " @Description "
+ " ) "
+ "";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.CommandType = CommandType.Text;
insertCommand.Parameters.AddWithValue("@Description", ItemOption.Description);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static DataTable SelectAll()
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [OrderDetail].[OrderDetailId] "
+ " ,[OrderDetail].[OrderId] "
+ " ,[OrderDetail].[Quantity] "
+ " ,[OrderDetail].[CatalogItemId] "
+ " ,[OrderDetail].[Price] "
+ " ,[OrderDetail].[SpecialInstructions] "
+ " ,[OrderDetail].[DiscountPercent] "
+ "FROM "
+ " [OrderDetail] "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static bool Delete(OrderHeader OrderHeader)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string deleteStatement
= "DELETE FROM "
+ " [OrderHeader] "
+ "WHERE "
+ " [OrderId] = @OldOrderId "
+ " AND [UserId] = @OldUserId "
+ " AND [OrderDate] = @OldOrderDate "
+ " AND ((@OldCustomerId IS NULL AND [CustomerId] IS NULL) OR [CustomerId] = @OldCustomerId) "
+ " AND [OrderTotal] = @OldOrderTotal "
+ " AND [SalesTax] = @OldSalesTax "
+ " AND ((@OldSalesTaxCode IS NULL AND [SalesTaxCode] IS NULL) OR [SalesTaxCode] = @OldSalesTaxCode) "
+ " AND [ShippingCharge] = @OldShippingCharge "
+ " AND [QbUpdated] = @OldQbUpdated "
+ " AND ((@OldSalesTaxAmt IS NULL AND [SalesTaxAmt] IS NULL) OR [SalesTaxAmt] = @OldSalesTaxAmt) "
+ " AND ((@OldDiscountAmount IS NULL AND [DiscountAmount] IS NULL) OR [DiscountAmount] = @OldDiscountAmount) "
+ " AND ((@OldStatus IS NULL AND [Status] IS NULL) OR [Status] = @OldStatus) "
+ " AND ((@OldbTestOrder IS NULL AND [bTestOrder] IS NULL) OR [bTestOrder] = @OldbTestOrder) "
+ "";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.CommandType = CommandType.Text;
deleteCommand.Parameters.AddWithValue("@OldOrderId", OrderHeader.OrderId);
deleteCommand.Parameters.AddWithValue("@OldUserId", OrderHeader.UserId);
deleteCommand.Parameters.AddWithValue("@OldOrderDate", OrderHeader.OrderDate);
if (OrderHeader.CustomerId.HasValue == true)
{
deleteCommand.Parameters.AddWithValue("@OldCustomerId", OrderHeader.CustomerId);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldCustomerId", DBNull.Value);
}
deleteCommand.Parameters.AddWithValue("@OldOrderTotal", OrderHeader.OrderTotal);
deleteCommand.Parameters.AddWithValue("@OldSalesTax", OrderHeader.SalesTax);
if (OrderHeader.SalesTaxCode.HasValue == true)
{
deleteCommand.Parameters.AddWithValue("@OldSalesTaxCode", OrderHeader.SalesTaxCode);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldSalesTaxCode", DBNull.Value);
}
deleteCommand.Parameters.AddWithValue("@OldShippingCharge", OrderHeader.ShippingCharge);
deleteCommand.Parameters.AddWithValue("@OldQbUpdated", OrderHeader.QbUpdated);
if (OrderHeader.SalesTaxAmt.HasValue == true)
{
deleteCommand.Parameters.AddWithValue("@OldSalesTaxAmt", OrderHeader.SalesTaxAmt);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldSalesTaxAmt", DBNull.Value);
}
if (OrderHeader.DiscountAmount.HasValue == true)
{
deleteCommand.Parameters.AddWithValue("@OldDiscountAmount", OrderHeader.DiscountAmount);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldDiscountAmount", DBNull.Value);
}
if (OrderHeader.Status.HasValue == true)
{
deleteCommand.Parameters.AddWithValue("@OldStatus", OrderHeader.Status);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldStatus", DBNull.Value);
}
if (OrderHeader.bTestOrder.HasValue == true)
{
deleteCommand.Parameters.AddWithValue("@OldbTestOrder", OrderHeader.bTestOrder);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldbTestOrder", DBNull.Value);
}
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static DataTable Search(string sField, string sCondition, string sValue)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement = "";
if (sCondition == "Contains")
{
selectStatement
= "SELECT "
+ " [OrderHeader].[OrderId] "
+ " ,[OrderHeader].[UserId] "
+ " ,[OrderHeader].[OrderDate] "
+ " ,[OrderHeader].[CustomerId] "
+ " ,[OrderHeader].[OrderTotal] "
+ " ,[OrderHeader].[SalesTax] "
+ " ,[OrderHeader].[SalesTaxCode] "
+ " ,[OrderHeader].[ShippingCharge] "
+ " ,[OrderHeader].[QbUpdated] "
+ " ,[OrderHeader].[SalesTaxAmt] "
+ " ,[OrderHeader].[DiscountAmount] "
+ " ,[OrderHeader].[Status] "
+ " ,[OrderHeader].[bTestOrder] "
+ "FROM "
+ " [OrderHeader] "
+ "INNER JOIN [OurCustomer] ON [OrderHeader].[UserId] = [OurCustomer].[UserId] "
+ "INNER JOIN [Customer] ON [OrderHeader].[CustomerId] = [Customer].[CustomerId] "
+ "WHERE "
+ " (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] LIKE '%' + LTRIM(RTRIM(@OrderId)) + '%') "
+ "AND (@UserId IS NULL OR @UserId = '' OR [OurCustomer].[UserId] LIKE '%' + LTRIM(RTRIM(@UserId)) + '%') "
+ "AND (@OrderDate IS NULL OR @OrderDate = '' OR [OrderHeader].[OrderDate] LIKE '%' + LTRIM(RTRIM(@OrderDate)) + '%') "
+ "AND (@CustomerId IS NULL OR @CustomerId = '' OR [Customer].[CustomerId] LIKE '%' + LTRIM(RTRIM(@CustomerId)) + '%') "
+ "AND (@OrderTotal IS NULL OR @OrderTotal = '' OR [OrderHeader].[OrderTotal] LIKE '%' + LTRIM(RTRIM(@OrderTotal)) + '%') "
+ "AND (@SalesTax IS NULL OR @SalesTax = '' OR [OrderHeader].[SalesTax] LIKE '%' + LTRIM(RTRIM(@SalesTax)) + '%') "
+ "AND (@SalesTaxCode IS NULL OR @SalesTaxCode = '' OR [OrderHeader].[SalesTaxCode] LIKE '%' + LTRIM(RTRIM(@SalesTaxCode)) + '%') "
+ "AND (@ShippingCharge IS NULL OR @ShippingCharge = '' OR [OrderHeader].[ShippingCharge] LIKE '%' + LTRIM(RTRIM(@ShippingCharge)) + '%') "
+ "AND (@QbUpdated IS NULL OR @QbUpdated = '' OR [OrderHeader].[QbUpdated] LIKE '%' + LTRIM(RTRIM(@QbUpdated)) + '%') "
+ "AND (@SalesTaxAmt IS NULL OR @SalesTaxAmt = '' OR [OrderHeader].[SalesTaxAmt] LIKE '%' + LTRIM(RTRIM(@SalesTaxAmt)) + '%') "
+ "AND (@DiscountAmount IS NULL OR @DiscountAmount = '' OR [OrderHeader].[DiscountAmount] LIKE '%' + LTRIM(RTRIM(@DiscountAmount)) + '%') "
+ "AND (@Status IS NULL OR @Status = '' OR [OrderHeader].[Status] LIKE '%' + LTRIM(RTRIM(@Status)) + '%') "
+ "AND (@bTestOrder IS NULL OR @bTestOrder = '' OR [OrderHeader].[bTestOrder] LIKE '%' + LTRIM(RTRIM(@bTestOrder)) + '%') "
+ "";
}
else if (sCondition == "Equals")
{
selectStatement
= "SELECT "
+ " [OrderHeader].[OrderId] "
+ " ,[OrderHeader].[UserId] "
+ " ,[OrderHeader].[OrderDate] "
+ " ,[OrderHeader].[CustomerId] "
+ " ,[OrderHeader].[OrderTotal] "
+ " ,[OrderHeader].[SalesTax] "
+ " ,[OrderHeader].[SalesTaxCode] "
+ " ,[OrderHeader].[ShippingCharge] "
+ " ,[OrderHeader].[QbUpdated] "
+ " ,[OrderHeader].[SalesTaxAmt] "
+ " ,[OrderHeader].[DiscountAmount] "
+ " ,[OrderHeader].[Status] "
+ " ,[OrderHeader].[bTestOrder] "
+ "FROM "
+ " [OrderHeader] "
+ "INNER JOIN [OurCustomer] ON [OrderHeader].[UserId] = [OurCustomer].[UserId] "
+ "INNER JOIN [Customer] ON [OrderHeader].[CustomerId] = [Customer].[CustomerId] "
+ "WHERE "
+ " (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] = LTRIM(RTRIM(@OrderId))) "
+ "AND (@UserId IS NULL OR @UserId = '' OR [OurCustomer].[UserId] = LTRIM(RTRIM(@UserId))) "
+ "AND (@OrderDate IS NULL OR @OrderDate = '' OR [OrderHeader].[OrderDate] = LTRIM(RTRIM(@OrderDate))) "
+ "AND (@CustomerId IS NULL OR @CustomerId = '' OR [Customer].[CustomerId] = LTRIM(RTRIM(@CustomerId))) "
+ "AND (@OrderTotal IS NULL OR @OrderTotal = '' OR [OrderHeader].[OrderTotal] = LTRIM(RTRIM(@OrderTotal))) "
+ "AND (@SalesTax IS NULL OR @SalesTax = '' OR [OrderHeader].[SalesTax] = LTRIM(RTRIM(@SalesTax))) "
+ "AND (@SalesTaxCode IS NULL OR @SalesTaxCode = '' OR [OrderHeader].[SalesTaxCode] = LTRIM(RTRIM(@SalesTaxCode))) "
+ "AND (@ShippingCharge IS NULL OR @ShippingCharge = '' OR [OrderHeader].[ShippingCharge] = LTRIM(RTRIM(@ShippingCharge))) "
+ "AND (@QbUpdated IS NULL OR @QbUpdated = '' OR [OrderHeader].[QbUpdated] = LTRIM(RTRIM(@QbUpdated))) "
+ "AND (@SalesTaxAmt IS NULL OR @SalesTaxAmt = '' OR [OrderHeader].[SalesTaxAmt] = LTRIM(RTRIM(@SalesTaxAmt))) "
+ "AND (@DiscountAmount IS NULL OR @DiscountAmount = '' OR [OrderHeader].[DiscountAmount] = LTRIM(RTRIM(@DiscountAmount))) "
+ "AND (@Status IS NULL OR @Status = '' OR [OrderHeader].[Status] = LTRIM(RTRIM(@Status))) "
+ "AND (@bTestOrder IS NULL OR @bTestOrder = '' OR [OrderHeader].[bTestOrder] = LTRIM(RTRIM(@bTestOrder))) "
+ "";
}
else if (sCondition == "Starts with...")
{
selectStatement
= "SELECT "
+ " [OrderHeader].[OrderId] "
+ " ,[OrderHeader].[UserId] "
+ " ,[OrderHeader].[OrderDate] "
+ " ,[OrderHeader].[CustomerId] "
+ " ,[OrderHeader].[OrderTotal] "
+ " ,[OrderHeader].[SalesTax] "
+ " ,[OrderHeader].[SalesTaxCode] "
+ " ,[OrderHeader].[ShippingCharge] "
+ " ,[OrderHeader].[QbUpdated] "
+ " ,[OrderHeader].[SalesTaxAmt] "
+ " ,[OrderHeader].[DiscountAmount] "
+ " ,[OrderHeader].[Status] "
+ " ,[OrderHeader].[bTestOrder] "
+ "FROM "
+ " [OrderHeader] "
+ "INNER JOIN [OurCustomer] ON [OrderHeader].[UserId] = [OurCustomer].[UserId] "
+ "INNER JOIN [Customer] ON [OrderHeader].[CustomerId] = [Customer].[CustomerId] "
+ "WHERE "
+ " (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] LIKE LTRIM(RTRIM(@OrderId)) + '%') "
+ "AND (@UserId IS NULL OR @UserId = '' OR [OurCustomer].[UserId] LIKE LTRIM(RTRIM(@UserId)) + '%') "
+ "AND (@OrderDate IS NULL OR @OrderDate = '' OR [OrderHeader].[OrderDate] LIKE LTRIM(RTRIM(@OrderDate)) + '%') "
+ "AND (@CustomerId IS NULL OR @CustomerId = '' OR [Customer].[CustomerId] LIKE LTRIM(RTRIM(@CustomerId)) + '%') "
+ "AND (@OrderTotal IS NULL OR @OrderTotal = '' OR [OrderHeader].[OrderTotal] LIKE LTRIM(RTRIM(@OrderTotal)) + '%') "
+ "AND (@SalesTax IS NULL OR @SalesTax = '' OR [OrderHeader].[SalesTax] LIKE LTRIM(RTRIM(@SalesTax)) + '%') "
+ "AND (@SalesTaxCode IS NULL OR @SalesTaxCode = '' OR [OrderHeader].[SalesTaxCode] LIKE LTRIM(RTRIM(@SalesTaxCode)) + '%') "
+ "AND (@ShippingCharge IS NULL OR @ShippingCharge = '' OR [OrderHeader].[ShippingCharge] LIKE LTRIM(RTRIM(@ShippingCharge)) + '%') "
+ "AND (@QbUpdated IS NULL OR @QbUpdated = '' OR [OrderHeader].[QbUpdated] LIKE LTRIM(RTRIM(@QbUpdated)) + '%') "
+ "AND (@SalesTaxAmt IS NULL OR @SalesTaxAmt = '' OR [OrderHeader].[SalesTaxAmt] LIKE LTRIM(RTRIM(@SalesTaxAmt)) + '%') "
+ "AND (@DiscountAmount IS NULL OR @DiscountAmount = '' OR [OrderHeader].[DiscountAmount] LIKE LTRIM(RTRIM(@DiscountAmount)) + '%') "
+ "AND (@Status IS NULL OR @Status = '' OR [OrderHeader].[Status] LIKE LTRIM(RTRIM(@Status)) + '%') "
+ "AND (@bTestOrder IS NULL OR @bTestOrder = '' OR [OrderHeader].[bTestOrder] LIKE LTRIM(RTRIM(@bTestOrder)) + '%') "
+ "";
}
else if (sCondition == "More than...")
{
selectStatement
= "SELECT "
+ " [OrderHeader].[OrderId] "
+ " ,[OrderHeader].[UserId] "
+ " ,[OrderHeader].[OrderDate] "
+ " ,[OrderHeader].[CustomerId] "
+ " ,[OrderHeader].[OrderTotal] "
+ " ,[OrderHeader].[SalesTax] "
+ " ,[OrderHeader].[SalesTaxCode] "
+ " ,[OrderHeader].[ShippingCharge] "
+ " ,[OrderHeader].[QbUpdated] "
+ " ,[OrderHeader].[SalesTaxAmt] "
+ " ,[OrderHeader].[DiscountAmount] "
+ " ,[OrderHeader].[Status] "
+ " ,[OrderHeader].[bTestOrder] "
+ "FROM "
+ " [OrderHeader] "
+ "INNER JOIN [OurCustomer] ON [OrderHeader].[UserId] = [OurCustomer].[UserId] "
+ "INNER JOIN [Customer] ON [OrderHeader].[CustomerId] = [Customer].[CustomerId] "
+ "WHERE "
+ " (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] > LTRIM(RTRIM(@OrderId))) "
+ "AND (@UserId IS NULL OR @UserId = '' OR [OurCustomer].[UserId] > LTRIM(RTRIM(@UserId))) "
+ "AND (@OrderDate IS NULL OR @OrderDate = '' OR [OrderHeader].[OrderDate] > LTRIM(RTRIM(@OrderDate))) "
+ "AND (@CustomerId IS NULL OR @CustomerId = '' OR [Customer].[CustomerId] > LTRIM(RTRIM(@CustomerId))) "
+ "AND (@OrderTotal IS NULL OR @OrderTotal = '' OR [OrderHeader].[OrderTotal] > LTRIM(RTRIM(@OrderTotal))) "
+ "AND (@SalesTax IS NULL OR @SalesTax = '' OR [OrderHeader].[SalesTax] > LTRIM(RTRIM(@SalesTax))) "
+ "AND (@SalesTaxCode IS NULL OR @SalesTaxCode = '' OR [OrderHeader].[SalesTaxCode] > LTRIM(RTRIM(@SalesTaxCode))) "
+ "AND (@ShippingCharge IS NULL OR @ShippingCharge = '' OR [OrderHeader].[ShippingCharge] > LTRIM(RTRIM(@ShippingCharge))) "
+ "AND (@QbUpdated IS NULL OR @QbUpdated = '' OR [OrderHeader].[QbUpdated] > LTRIM(RTRIM(@QbUpdated))) "
+ "AND (@SalesTaxAmt IS NULL OR @SalesTaxAmt = '' OR [OrderHeader].[SalesTaxAmt] > LTRIM(RTRIM(@SalesTaxAmt))) "
+ "AND (@DiscountAmount IS NULL OR @DiscountAmount = '' OR [OrderHeader].[DiscountAmount] > LTRIM(RTRIM(@DiscountAmount))) "
+ "AND (@Status IS NULL OR @Status = '' OR [OrderHeader].[Status] > LTRIM(RTRIM(@Status))) "
+ "AND (@bTestOrder IS NULL OR @bTestOrder = '' OR [OrderHeader].[bTestOrder] > LTRIM(RTRIM(@bTestOrder))) "
+ "";
}
else if (sCondition == "Less than...")
{
selectStatement
= "SELECT "
+ " [OrderHeader].[OrderId] "
+ " ,[OrderHeader].[UserId] "
+ " ,[OrderHeader].[OrderDate] "
+ " ,[OrderHeader].[CustomerId] "
+ " ,[OrderHeader].[OrderTotal] "
+ " ,[OrderHeader].[SalesTax] "
+ " ,[OrderHeader].[SalesTaxCode] "
+ " ,[OrderHeader].[ShippingCharge] "
+ " ,[OrderHeader].[QbUpdated] "
+ " ,[OrderHeader].[SalesTaxAmt] "
+ " ,[OrderHeader].[DiscountAmount] "
+ " ,[OrderHeader].[Status] "
+ " ,[OrderHeader].[bTestOrder] "
+ "FROM "
+ " [OrderHeader] "
+ "INNER JOIN [OurCustomer] ON [OrderHeader].[UserId] = [OurCustomer].[UserId] "
+ "INNER JOIN [Customer] ON [OrderHeader].[CustomerId] = [Customer].[CustomerId] "
+ "WHERE "
+ " (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] < LTRIM(RTRIM(@OrderId))) "
+ "AND (@UserId IS NULL OR @UserId = '' OR [OurCustomer].[UserId] < LTRIM(RTRIM(@UserId))) "
+ "AND (@OrderDate IS NULL OR @OrderDate = '' OR [OrderHeader].[OrderDate] < LTRIM(RTRIM(@OrderDate))) "
+ "AND (@CustomerId IS NULL OR @CustomerId = '' OR [Customer].[CustomerId] < LTRIM(RTRIM(@CustomerId))) "
+ "AND (@OrderTotal IS NULL OR @OrderTotal = '' OR [OrderHeader].[OrderTotal] < LTRIM(RTRIM(@OrderTotal))) "
+ "AND (@SalesTax IS NULL OR @SalesTax = '' OR [OrderHeader].[SalesTax] < LTRIM(RTRIM(@SalesTax))) "
+ "AND (@SalesTaxCode IS NULL OR @SalesTaxCode = '' OR [OrderHeader].[SalesTaxCode] < LTRIM(RTRIM(@SalesTaxCode))) "
+ "AND (@ShippingCharge IS NULL OR @ShippingCharge = '' OR [OrderHeader].[ShippingCharge] < LTRIM(RTRIM(@ShippingCharge))) "
+ "AND (@QbUpdated IS NULL OR @QbUpdated = '' OR [OrderHeader].[QbUpdated] < LTRIM(RTRIM(@QbUpdated))) "
+ "AND (@SalesTaxAmt IS NULL OR @SalesTaxAmt = '' OR [OrderHeader].[SalesTaxAmt] < LTRIM(RTRIM(@SalesTaxAmt))) "
+ "AND (@DiscountAmount IS NULL OR @DiscountAmount = '' OR [OrderHeader].[DiscountAmount] < LTRIM(RTRIM(@DiscountAmount))) "
+ "AND (@Status IS NULL OR @Status = '' OR [OrderHeader].[Status] < LTRIM(RTRIM(@Status))) "
+ "AND (@bTestOrder IS NULL OR @bTestOrder = '' OR [OrderHeader].[bTestOrder] < LTRIM(RTRIM(@bTestOrder))) "
+ "";
}
else if (sCondition == "Equal or more than...")
{
selectStatement
= "SELECT "
+ " [OrderHeader].[OrderId] "
+ " ,[OrderHeader].[UserId] "
+ " ,[OrderHeader].[OrderDate] "
+ " ,[OrderHeader].[CustomerId] "
+ " ,[OrderHeader].[OrderTotal] "
+ " ,[OrderHeader].[SalesTax] "
+ " ,[OrderHeader].[SalesTaxCode] "
+ " ,[OrderHeader].[ShippingCharge] "
+ " ,[OrderHeader].[QbUpdated] "
+ " ,[OrderHeader].[SalesTaxAmt] "
+ " ,[OrderHeader].[DiscountAmount] "
+ " ,[OrderHeader].[Status] "
+ " ,[OrderHeader].[bTestOrder] "
+ "FROM "
+ " [OrderHeader] "
+ "INNER JOIN [OurCustomer] ON [OrderHeader].[UserId] = [OurCustomer].[UserId] "
+ "INNER JOIN [Customer] ON [OrderHeader].[CustomerId] = [Customer].[CustomerId] "
+ "WHERE "
+ " (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] >= LTRIM(RTRIM(@OrderId))) "
+ "AND (@UserId IS NULL OR @UserId = '' OR [OurCustomer].[UserId] >= LTRIM(RTRIM(@UserId))) "
+ "AND (@OrderDate IS NULL OR @OrderDate = '' OR [OrderHeader].[OrderDate] >= LTRIM(RTRIM(@OrderDate))) "
+ "AND (@CustomerId IS NULL OR @CustomerId = '' OR [Customer].[CustomerId] >= LTRIM(RTRIM(@CustomerId))) "
+ "AND (@OrderTotal IS NULL OR @OrderTotal = '' OR [OrderHeader].[OrderTotal] >= LTRIM(RTRIM(@OrderTotal))) "
+ "AND (@SalesTax IS NULL OR @SalesTax = '' OR [OrderHeader].[SalesTax] >= LTRIM(RTRIM(@SalesTax))) "
+ "AND (@SalesTaxCode IS NULL OR @SalesTaxCode = '' OR [OrderHeader].[SalesTaxCode] >= LTRIM(RTRIM(@SalesTaxCode))) "
+ "AND (@ShippingCharge IS NULL OR @ShippingCharge = '' OR [OrderHeader].[ShippingCharge] >= LTRIM(RTRIM(@ShippingCharge))) "
+ "AND (@QbUpdated IS NULL OR @QbUpdated = '' OR [OrderHeader].[QbUpdated] >= LTRIM(RTRIM(@QbUpdated))) "
+ "AND (@SalesTaxAmt IS NULL OR @SalesTaxAmt = '' OR [OrderHeader].[SalesTaxAmt] >= LTRIM(RTRIM(@SalesTaxAmt))) "
+ "AND (@DiscountAmount IS NULL OR @DiscountAmount = '' OR [OrderHeader].[DiscountAmount] >= LTRIM(RTRIM(@DiscountAmount))) "
+ "AND (@Status IS NULL OR @Status = '' OR [OrderHeader].[Status] >= LTRIM(RTRIM(@Status))) "
+ "AND (@bTestOrder IS NULL OR @bTestOrder = '' OR [OrderHeader].[bTestOrder] >= LTRIM(RTRIM(@bTestOrder))) "
+ "";
}
else if (sCondition == "Equal or less than...")
{
selectStatement
= "SELECT "
+ " [OrderHeader].[OrderId] "
+ " ,[OrderHeader].[UserId] "
+ " ,[OrderHeader].[OrderDate] "
+ " ,[OrderHeader].[CustomerId] "
+ " ,[OrderHeader].[OrderTotal] "
+ " ,[OrderHeader].[SalesTax] "
+ " ,[OrderHeader].[SalesTaxCode] "
+ " ,[OrderHeader].[ShippingCharge] "
+ " ,[OrderHeader].[QbUpdated] "
+ " ,[OrderHeader].[SalesTaxAmt] "
+ " ,[OrderHeader].[DiscountAmount] "
+ " ,[OrderHeader].[Status] "
+ " ,[OrderHeader].[bTestOrder] "
+ "FROM "
+ " [OrderHeader] "
+ "INNER JOIN [OurCustomer] ON [OrderHeader].[UserId] = [OurCustomer].[UserId] "
+ "INNER JOIN [Customer] ON [OrderHeader].[CustomerId] = [Customer].[CustomerId] "
+ "WHERE "
+ " (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] <= LTRIM(RTRIM(@OrderId))) "
+ "AND (@UserId IS NULL OR @UserId = '' OR [OurCustomer].[UserId] <= LTRIM(RTRIM(@UserId))) "
+ "AND (@OrderDate IS NULL OR @OrderDate = '' OR [OrderHeader].[OrderDate] <= LTRIM(RTRIM(@OrderDate))) "
+ "AND (@CustomerId IS NULL OR @CustomerId = '' OR [Customer].[CustomerId] <= LTRIM(RTRIM(@CustomerId))) "
+ "AND (@OrderTotal IS NULL OR @OrderTotal = '' OR [OrderHeader].[OrderTotal] <= LTRIM(RTRIM(@OrderTotal))) "
+ "AND (@SalesTax IS NULL OR @SalesTax = '' OR [OrderHeader].[SalesTax] <= LTRIM(RTRIM(@SalesTax))) "
+ "AND (@SalesTaxCode IS NULL OR @SalesTaxCode = '' OR [OrderHeader].[SalesTaxCode] <= LTRIM(RTRIM(@SalesTaxCode))) "
+ "AND (@ShippingCharge IS NULL OR @ShippingCharge = '' OR [OrderHeader].[ShippingCharge] <= LTRIM(RTRIM(@ShippingCharge))) "
+ "AND (@QbUpdated IS NULL OR @QbUpdated = '' OR [OrderHeader].[QbUpdated] <= LTRIM(RTRIM(@QbUpdated))) "
+ "AND (@SalesTaxAmt IS NULL OR @SalesTaxAmt = '' OR [OrderHeader].[SalesTaxAmt] <= LTRIM(RTRIM(@SalesTaxAmt))) "
+ "AND (@DiscountAmount IS NULL OR @DiscountAmount = '' OR [OrderHeader].[DiscountAmount] <= LTRIM(RTRIM(@DiscountAmount))) "
+ "AND (@Status IS NULL OR @Status = '' OR [OrderHeader].[Status] <= LTRIM(RTRIM(@Status))) "
+ "AND (@bTestOrder IS NULL OR @bTestOrder = '' OR [OrderHeader].[bTestOrder] <= LTRIM(RTRIM(@bTestOrder))) "
+ "";
}
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
if (sField == "Order Id")
{
selectCommand.Parameters.AddWithValue("@OrderId", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@OrderId", DBNull.Value);
}
if (sField == "User Id")
{
selectCommand.Parameters.AddWithValue("@UserId", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@UserId", DBNull.Value);
}
if (sField == "Order Date")
{
selectCommand.Parameters.AddWithValue("@OrderDate", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@OrderDate", DBNull.Value);
}
if (sField == "Customer Id")
{
selectCommand.Parameters.AddWithValue("@CustomerId", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@CustomerId", DBNull.Value);
}
if (sField == "Order Total")
{
selectCommand.Parameters.AddWithValue("@OrderTotal", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@OrderTotal", DBNull.Value);
}
if (sField == "Sales Tax")
{
selectCommand.Parameters.AddWithValue("@SalesTax", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@SalesTax", DBNull.Value);
}
if (sField == "Sales Tax Code")
{
selectCommand.Parameters.AddWithValue("@SalesTaxCode", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@SalesTaxCode", DBNull.Value);
}
if (sField == "Shipping Charge")
{
selectCommand.Parameters.AddWithValue("@ShippingCharge", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@ShippingCharge", DBNull.Value);
}
if (sField == "Qb Updated")
{
selectCommand.Parameters.AddWithValue("@QbUpdated", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@QbUpdated", DBNull.Value);
}
if (sField == "Sales Tax Amt")
{
selectCommand.Parameters.AddWithValue("@SalesTaxAmt", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@SalesTaxAmt", DBNull.Value);
}
if (sField == "Discount Amount")
{
selectCommand.Parameters.AddWithValue("@DiscountAmount", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@DiscountAmount", DBNull.Value);
}
if (sField == "Status")
{
selectCommand.Parameters.AddWithValue("@Status", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@Status", DBNull.Value);
}
if (sField == "B Test Order")
{
selectCommand.Parameters.AddWithValue("@bTestOrder", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@bTestOrder", DBNull.Value);
}
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static bool Update(OrderDetail oldOrderDetail,
OrderDetail newOrderDetail)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string updateStatement
= "UPDATE "
+ " [OrderDetail] "
+ "SET "
+ " [OrderId] = @NewOrderId "
+ " ,[Quantity] = @NewQuantity "
+ " ,[CatalogItemId] = @NewCatalogItemId "
+ " ,[Price] = @NewPrice "
+ " ,[SpecialInstructions] = @NewSpecialInstructions "
+ " ,[DiscountPercent] = @NewDiscountPercent "
+ "WHERE "
+ " [OrderDetailId] = @OldOrderDetailId "
+ " AND [OrderId] = @OldOrderId "
+ " AND [Quantity] = @OldQuantity "
+ " AND [CatalogItemId] = @OldCatalogItemId "
+ " AND [Price] = @OldPrice "
+ " AND ((@OldSpecialInstructions IS NULL AND [SpecialInstructions] IS NULL) OR [SpecialInstructions] = @OldSpecialInstructions) "
+ " AND [DiscountPercent] = @OldDiscountPercent "
+ "";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.CommandType = CommandType.Text;
updateCommand.Parameters.AddWithValue("@NewOrderId", newOrderDetail.OrderId);
updateCommand.Parameters.AddWithValue("@NewQuantity", newOrderDetail.Quantity);
updateCommand.Parameters.AddWithValue("@NewCatalogItemId", newOrderDetail.CatalogItemId);
updateCommand.Parameters.AddWithValue("@NewPrice", newOrderDetail.Price);
if (newOrderDetail.SpecialInstructions != null)
{
updateCommand.Parameters.AddWithValue("@NewSpecialInstructions", newOrderDetail.SpecialInstructions);
}
else
{
updateCommand.Parameters.AddWithValue("@NewSpecialInstructions", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@NewDiscountPercent", newOrderDetail.DiscountPercent);
updateCommand.Parameters.AddWithValue("@OldOrderDetailId", oldOrderDetail.OrderDetailId);
updateCommand.Parameters.AddWithValue("@OldOrderId", oldOrderDetail.OrderId);
updateCommand.Parameters.AddWithValue("@OldQuantity", oldOrderDetail.Quantity);
updateCommand.Parameters.AddWithValue("@OldCatalogItemId", oldOrderDetail.CatalogItemId);
updateCommand.Parameters.AddWithValue("@OldPrice", oldOrderDetail.Price);
if (oldOrderDetail.SpecialInstructions != null)
{
updateCommand.Parameters.AddWithValue("@OldSpecialInstructions", oldOrderDetail.SpecialInstructions);
}
else
{
updateCommand.Parameters.AddWithValue("@OldSpecialInstructions", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@OldDiscountPercent", oldOrderDetail.DiscountPercent);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static OrderHeader Select_Record(OrderHeader OrderHeaderPara)
{
OrderHeader OrderHeader = new OrderHeader();
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement
= "SELECT "
+ " [OrderId] "
+ " ,[UserId] "
+ " ,[OrderDate] "
+ " ,[CustomerId] "
+ " ,[OrderTotal] "
+ " ,[SalesTax] "
+ " ,[SalesTaxCode] "
+ " ,[ShippingCharge] "
+ " ,[QbUpdated] "
+ " ,[SalesTaxAmt] "
+ " ,[DiscountAmount] "
+ " ,[Status] "
+ " ,[bTestOrder] "
+ "FROM "
+ " [OrderHeader] "
+ "WHERE "
+ " [OrderId] = @OrderId "
+ "";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
selectCommand.Parameters.AddWithValue("@OrderId", OrderHeaderPara.OrderId);
try
{
connection.Open();
SqlDataReader reader
= selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
OrderHeader.OrderId = System.Convert.ToInt32(reader["OrderId"]);
OrderHeader.UserId = System.Convert.ToInt32(reader["UserId"]);
OrderHeader.OrderDate = System.Convert.ToDateTime(reader["OrderDate"]);
OrderHeader.CustomerId = reader["CustomerId"] is DBNull ? null : (Int32?)reader["CustomerId"];
OrderHeader.OrderTotal = System.Convert.ToDecimal(reader["OrderTotal"]);
OrderHeader.SalesTax = System.Convert.ToDecimal(reader["SalesTax"]);
OrderHeader.SalesTaxCode = reader["SalesTaxCode"] is DBNull ? null : (Int32?)reader["SalesTaxCode"];
OrderHeader.ShippingCharge = System.Convert.ToDecimal(reader["ShippingCharge"]);
OrderHeader.QbUpdated = System.Convert.ToBoolean(reader["QbUpdated"]);
OrderHeader.SalesTaxAmt = reader["SalesTaxAmt"] is DBNull ? null : (Decimal?)reader["SalesTaxAmt"];
OrderHeader.DiscountAmount = reader["DiscountAmount"] is DBNull ? null : (Decimal?)reader["DiscountAmount"];
OrderHeader.Status = reader["Status"] is DBNull ? null : (Byte?)reader["Status"];
OrderHeader.bTestOrder = reader["bTestOrder"] is DBNull ? null : (Boolean?)reader["bTestOrder"];
}
else
{
OrderHeader = null;
}
reader.Close();
}
catch (SqlException)
{
return(OrderHeader);
}
finally
{
connection.Close();
}
return(OrderHeader);
}
public static bool Delete(CatalogItem CatalogItem)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string deleteStatement
= "DELETE FROM "
+ " [CatalogItem] "
+ "WHERE "
+ " [CatalogItemId] = @OldCatalogItemId "
+ " AND [Name] = @OldName "
+ " AND ((@OldDescription IS NULL AND [Description] IS NULL) OR [Description] = @OldDescription) "
+ " AND ((@OldSku IS NULL AND [Sku] IS NULL) OR [Sku] = @OldSku) "
+ " AND ((@OldQbItemId IS NULL AND [QbItemId] IS NULL) OR [QbItemId] = @OldQbItemId) "
+ "";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.CommandType = CommandType.Text;
deleteCommand.Parameters.AddWithValue("@OldCatalogItemId", CatalogItem.CatalogItemId);
deleteCommand.Parameters.AddWithValue("@OldName", CatalogItem.Name);
if (CatalogItem.Description != null)
{
deleteCommand.Parameters.AddWithValue("@OldDescription", CatalogItem.Description);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldDescription", DBNull.Value);
}
if (CatalogItem.Sku != null)
{
deleteCommand.Parameters.AddWithValue("@OldSku", CatalogItem.Sku);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldSku", DBNull.Value);
}
if (CatalogItem.QbItemId != null)
{
deleteCommand.Parameters.AddWithValue("@OldQbItemId", CatalogItem.QbItemId);
}
else
{
deleteCommand.Parameters.AddWithValue("@OldQbItemId", DBNull.Value);
}
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static bool Update(CatalogItem oldCatalogItem,
CatalogItem newCatalogItem)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string updateStatement
= "UPDATE "
+ " [CatalogItem] "
+ "SET "
+ " [Name] = @NewName "
+ " ,[Description] = @NewDescription "
+ " ,[Sku] = @NewSku "
+ " ,[QbItemId] = @NewQbItemId "
+ "WHERE "
+ " [CatalogItemId] = @OldCatalogItemId "
+ " AND [Name] = @OldName "
+ " AND ((@OldDescription IS NULL AND [Description] IS NULL) OR [Description] = @OldDescription) "
+ " AND ((@OldSku IS NULL AND [Sku] IS NULL) OR [Sku] = @OldSku) "
+ " AND ((@OldQbItemId IS NULL AND [QbItemId] IS NULL) OR [QbItemId] = @OldQbItemId) "
+ "";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.CommandType = CommandType.Text;
updateCommand.Parameters.AddWithValue("@NewName", newCatalogItem.Name);
if (newCatalogItem.Description != null)
{
updateCommand.Parameters.AddWithValue("@NewDescription", newCatalogItem.Description);
}
else
{
updateCommand.Parameters.AddWithValue("@NewDescription", DBNull.Value);
}
if (newCatalogItem.Sku != null)
{
updateCommand.Parameters.AddWithValue("@NewSku", newCatalogItem.Sku);
}
else
{
updateCommand.Parameters.AddWithValue("@NewSku", DBNull.Value);
}
if (newCatalogItem.QbItemId != null)
{
updateCommand.Parameters.AddWithValue("@NewQbItemId", newCatalogItem.QbItemId);
}
else
{
updateCommand.Parameters.AddWithValue("@NewQbItemId", DBNull.Value);
}
updateCommand.Parameters.AddWithValue("@OldCatalogItemId", oldCatalogItem.CatalogItemId);
updateCommand.Parameters.AddWithValue("@OldName", oldCatalogItem.Name);
if (oldCatalogItem.Description != null)
{
updateCommand.Parameters.AddWithValue("@OldDescription", oldCatalogItem.Description);
}
else
{
updateCommand.Parameters.AddWithValue("@OldDescription", DBNull.Value);
}
if (oldCatalogItem.Sku != null)
{
updateCommand.Parameters.AddWithValue("@OldSku", oldCatalogItem.Sku);
}
else
{
updateCommand.Parameters.AddWithValue("@OldSku", DBNull.Value);
}
if (oldCatalogItem.QbItemId != null)
{
updateCommand.Parameters.AddWithValue("@OldQbItemId", oldCatalogItem.QbItemId);
}
else
{
updateCommand.Parameters.AddWithValue("@OldQbItemId", DBNull.Value);
}
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static bool Add(OrderDetail OrderDetail)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string insertStatement
= "INSERT "
+ " [OrderDetail] "
+ " ( "
+ " [OrderId] "
+ " ,[Quantity] "
+ " ,[CatalogItemId] "
+ " ,[Price] "
+ " ,[SpecialInstructions] "
+ " ,[DiscountPercent] "
+ " ) "
+ "VALUES "
+ " ( "
+ " @OrderId "
+ " ,@Quantity "
+ " ,@CatalogItemId "
+ " ,@Price "
+ " ,@SpecialInstructions "
+ " ,@DiscountPercent "
+ " ) "
+ "";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.CommandType = CommandType.Text;
insertCommand.Parameters.AddWithValue("@OrderId", OrderDetail.OrderId);
insertCommand.Parameters.AddWithValue("@Quantity", OrderDetail.Quantity);
insertCommand.Parameters.AddWithValue("@CatalogItemId", OrderDetail.CatalogItemId);
insertCommand.Parameters.AddWithValue("@Price", OrderDetail.Price);
if (OrderDetail.SpecialInstructions != null)
{
insertCommand.Parameters.AddWithValue("@SpecialInstructions", OrderDetail.SpecialInstructions);
}
else
{
insertCommand.Parameters.AddWithValue("@SpecialInstructions", DBNull.Value);
}
insertCommand.Parameters.AddWithValue("@DiscountPercent", OrderDetail.DiscountPercent);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static DataTable Search(string sField, string sCondition, string sValue)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement = "";
if (sCondition == "Contains")
{
selectStatement
= "SELECT "
+ " [ItemOption].[ItemOptionId] "
+ " ,[ItemOption].[Description] "
+ "FROM "
+ " [ItemOption] "
+ "WHERE "
+ " (@ItemOptionId IS NULL OR @ItemOptionId = '' OR [ItemOption].[ItemOptionId] LIKE '%' + LTRIM(RTRIM(@ItemOptionId)) + '%') "
+ "AND (@Description IS NULL OR @Description = '' OR [ItemOption].[Description] LIKE '%' + LTRIM(RTRIM(@Description)) + '%') "
+ "";
}
else if (sCondition == "Equals")
{
selectStatement
= "SELECT "
+ " [ItemOption].[ItemOptionId] "
+ " ,[ItemOption].[Description] "
+ "FROM "
+ " [ItemOption] "
+ "WHERE "
+ " (@ItemOptionId IS NULL OR @ItemOptionId = '' OR [ItemOption].[ItemOptionId] = LTRIM(RTRIM(@ItemOptionId))) "
+ "AND (@Description IS NULL OR @Description = '' OR [ItemOption].[Description] = LTRIM(RTRIM(@Description))) "
+ "";
}
else if (sCondition == "Starts with...")
{
selectStatement
= "SELECT "
+ " [ItemOption].[ItemOptionId] "
+ " ,[ItemOption].[Description] "
+ "FROM "
+ " [ItemOption] "
+ "WHERE "
+ " (@ItemOptionId IS NULL OR @ItemOptionId = '' OR [ItemOption].[ItemOptionId] LIKE LTRIM(RTRIM(@ItemOptionId)) + '%') "
+ "AND (@Description IS NULL OR @Description = '' OR [ItemOption].[Description] LIKE LTRIM(RTRIM(@Description)) + '%') "
+ "";
}
else if (sCondition == "More than...")
{
selectStatement
= "SELECT "
+ " [ItemOption].[ItemOptionId] "
+ " ,[ItemOption].[Description] "
+ "FROM "
+ " [ItemOption] "
+ "WHERE "
+ " (@ItemOptionId IS NULL OR @ItemOptionId = '' OR [ItemOption].[ItemOptionId] > LTRIM(RTRIM(@ItemOptionId))) "
+ "AND (@Description IS NULL OR @Description = '' OR [ItemOption].[Description] > LTRIM(RTRIM(@Description))) "
+ "";
}
else if (sCondition == "Less than...")
{
selectStatement
= "SELECT "
+ " [ItemOption].[ItemOptionId] "
+ " ,[ItemOption].[Description] "
+ "FROM "
+ " [ItemOption] "
+ "WHERE "
+ " (@ItemOptionId IS NULL OR @ItemOptionId = '' OR [ItemOption].[ItemOptionId] < LTRIM(RTRIM(@ItemOptionId))) "
+ "AND (@Description IS NULL OR @Description = '' OR [ItemOption].[Description] < LTRIM(RTRIM(@Description))) "
+ "";
}
else if (sCondition == "Equal or more than...")
{
selectStatement
= "SELECT "
+ " [ItemOption].[ItemOptionId] "
+ " ,[ItemOption].[Description] "
+ "FROM "
+ " [ItemOption] "
+ "WHERE "
+ " (@ItemOptionId IS NULL OR @ItemOptionId = '' OR [ItemOption].[ItemOptionId] >= LTRIM(RTRIM(@ItemOptionId))) "
+ "AND (@Description IS NULL OR @Description = '' OR [ItemOption].[Description] >= LTRIM(RTRIM(@Description))) "
+ "";
}
else if (sCondition == "Equal or less than...")
{
selectStatement
= "SELECT "
+ " [ItemOption].[ItemOptionId] "
+ " ,[ItemOption].[Description] "
+ "FROM "
+ " [ItemOption] "
+ "WHERE "
+ " (@ItemOptionId IS NULL OR @ItemOptionId = '' OR [ItemOption].[ItemOptionId] <= LTRIM(RTRIM(@ItemOptionId))) "
+ "AND (@Description IS NULL OR @Description = '' OR [ItemOption].[Description] <= LTRIM(RTRIM(@Description))) "
+ "";
}
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
if (sField == "Item Option Id")
{
selectCommand.Parameters.AddWithValue("@ItemOptionId", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@ItemOptionId", DBNull.Value);
}
if (sField == "Description")
{
selectCommand.Parameters.AddWithValue("@Description", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@Description", DBNull.Value);
}
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
public static bool Add(OrderHeader OrderHeader)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string insertStatement
= "INSERT "
+ " [OrderHeader] "
+ " ( "
+ " [UserId] "
+ " ,[OrderDate] "
+ " ,[CustomerId] "
+ " ,[OrderTotal] "
+ " ,[SalesTax] "
+ " ,[SalesTaxCode] "
+ " ,[ShippingCharge] "
+ " ,[QbUpdated] "
+ " ,[SalesTaxAmt] "
+ " ,[DiscountAmount] "
+ " ,[Status] "
+ " ,[bTestOrder] "
+ " ) "
+ "VALUES "
+ " ( "
+ " @UserId "
+ " ,@OrderDate "
+ " ,@CustomerId "
+ " ,@OrderTotal "
+ " ,@SalesTax "
+ " ,@SalesTaxCode "
+ " ,@ShippingCharge "
+ " ,@QbUpdated "
+ " ,@SalesTaxAmt "
+ " ,@DiscountAmount "
+ " ,@Status "
+ " ,@bTestOrder "
+ " ) "
+ "";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.CommandType = CommandType.Text;
insertCommand.Parameters.AddWithValue("@UserId", OrderHeader.UserId);
insertCommand.Parameters.AddWithValue("@OrderDate", OrderHeader.OrderDate);
if (OrderHeader.CustomerId.HasValue == true)
{
insertCommand.Parameters.AddWithValue("@CustomerId", OrderHeader.CustomerId);
}
else
{
insertCommand.Parameters.AddWithValue("@CustomerId", DBNull.Value);
}
insertCommand.Parameters.AddWithValue("@OrderTotal", OrderHeader.OrderTotal);
insertCommand.Parameters.AddWithValue("@SalesTax", OrderHeader.SalesTax);
if (OrderHeader.SalesTaxCode.HasValue == true)
{
insertCommand.Parameters.AddWithValue("@SalesTaxCode", OrderHeader.SalesTaxCode);
}
else
{
insertCommand.Parameters.AddWithValue("@SalesTaxCode", DBNull.Value);
}
insertCommand.Parameters.AddWithValue("@ShippingCharge", OrderHeader.ShippingCharge);
insertCommand.Parameters.AddWithValue("@QbUpdated", OrderHeader.QbUpdated);
if (OrderHeader.SalesTaxAmt.HasValue == true)
{
insertCommand.Parameters.AddWithValue("@SalesTaxAmt", OrderHeader.SalesTaxAmt);
}
else
{
insertCommand.Parameters.AddWithValue("@SalesTaxAmt", DBNull.Value);
}
if (OrderHeader.DiscountAmount.HasValue == true)
{
insertCommand.Parameters.AddWithValue("@DiscountAmount", OrderHeader.DiscountAmount);
}
else
{
insertCommand.Parameters.AddWithValue("@DiscountAmount", DBNull.Value);
}
if (OrderHeader.Status.HasValue == true)
{
insertCommand.Parameters.AddWithValue("@Status", OrderHeader.Status);
}
else
{
insertCommand.Parameters.AddWithValue("@Status", DBNull.Value);
}
if (OrderHeader.bTestOrder.HasValue == true)
{
insertCommand.Parameters.AddWithValue("@bTestOrder", OrderHeader.bTestOrder);
}
else
{
insertCommand.Parameters.AddWithValue("@bTestOrder", DBNull.Value);
}
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static bool Add(CatalogItem CatalogItem)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string insertStatement
= "INSERT "
+ " [CatalogItem] "
+ " ( "
+ " [Name] "
+ " ,[Description] "
+ " ,[Sku] "
+ " ,[QbItemId] "
+ " ) "
+ "VALUES "
+ " ( "
+ " @Name "
+ " ,@Description "
+ " ,@Sku "
+ " ,@QbItemId "
+ " ) "
+ "";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.CommandType = CommandType.Text;
insertCommand.Parameters.AddWithValue("@Name", CatalogItem.Name);
if (CatalogItem.Description != null)
{
insertCommand.Parameters.AddWithValue("@Description", CatalogItem.Description);
}
else
{
insertCommand.Parameters.AddWithValue("@Description", DBNull.Value);
}
if (CatalogItem.Sku != null)
{
insertCommand.Parameters.AddWithValue("@Sku", CatalogItem.Sku);
}
else
{
insertCommand.Parameters.AddWithValue("@Sku", DBNull.Value);
}
if (CatalogItem.QbItemId != null)
{
insertCommand.Parameters.AddWithValue("@QbItemId", CatalogItem.QbItemId);
}
else
{
insertCommand.Parameters.AddWithValue("@QbItemId", DBNull.Value);
}
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException)
{
return(false);
}
finally
{
connection.Close();
}
}
public static DataTable Search(string sField, string sCondition, string sValue)
{
SqlConnection connection = OnlineSales2Data.GetConnection();
string selectStatement = "";
if (sCondition == "Contains")
{
selectStatement
= "SELECT "
+ " [OrderDetail].[OrderDetailId] "
+ " ,[OrderDetail].[OrderId] "
+ " ,[OrderDetail].[Quantity] "
+ " ,[OrderDetail].[CatalogItemId] "
+ " ,[OrderDetail].[Price] "
+ " ,[OrderDetail].[SpecialInstructions] "
+ " ,[OrderDetail].[DiscountPercent] "
+ "FROM "
+ " [OrderDetail] "
+ "INNER JOIN [OrderHeader] ON [OrderDetail].[OrderId] = [OrderHeader].[OrderId] "
+ "INNER JOIN [CatalogItem] ON [OrderDetail].[CatalogItemId] = [CatalogItem].[CatalogItemId] "
+ "WHERE "
+ " (@OrderDetailId IS NULL OR @OrderDetailId = '' OR [OrderDetail].[OrderDetailId] LIKE '%' + LTRIM(RTRIM(@OrderDetailId)) + '%') "
+ "AND (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] LIKE '%' + LTRIM(RTRIM(@OrderId)) + '%') "
+ "AND (@Quantity IS NULL OR @Quantity = '' OR [OrderDetail].[Quantity] LIKE '%' + LTRIM(RTRIM(@Quantity)) + '%') "
+ "AND (@CatalogItemId IS NULL OR @CatalogItemId = '' OR [CatalogItem].[CatalogItemId] LIKE '%' + LTRIM(RTRIM(@CatalogItemId)) + '%') "
+ "AND (@Price IS NULL OR @Price = '' OR [OrderDetail].[Price] LIKE '%' + LTRIM(RTRIM(@Price)) + '%') "
+ "AND (@SpecialInstructions IS NULL OR @SpecialInstructions = '' OR [OrderDetail].[SpecialInstructions] LIKE '%' + LTRIM(RTRIM(@SpecialInstructions)) + '%') "
+ "AND (@DiscountPercent IS NULL OR @DiscountPercent = '' OR [OrderDetail].[DiscountPercent] LIKE '%' + LTRIM(RTRIM(@DiscountPercent)) + '%') "
+ "";
}
else if (sCondition == "Equals")
{
selectStatement
= "SELECT "
+ " [OrderDetail].[OrderDetailId] "
+ " ,[OrderDetail].[OrderId] "
+ " ,[OrderDetail].[Quantity] "
+ " ,[OrderDetail].[CatalogItemId] "
+ " ,[OrderDetail].[Price] "
+ " ,[OrderDetail].[SpecialInstructions] "
+ " ,[OrderDetail].[DiscountPercent] "
+ "FROM "
+ " [OrderDetail] "
+ "INNER JOIN [OrderHeader] ON [OrderDetail].[OrderId] = [OrderHeader].[OrderId] "
+ "INNER JOIN [CatalogItem] ON [OrderDetail].[CatalogItemId] = [CatalogItem].[CatalogItemId] "
+ "WHERE "
+ " (@OrderDetailId IS NULL OR @OrderDetailId = '' OR [OrderDetail].[OrderDetailId] = LTRIM(RTRIM(@OrderDetailId))) "
+ "AND (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] = LTRIM(RTRIM(@OrderId))) "
+ "AND (@Quantity IS NULL OR @Quantity = '' OR [OrderDetail].[Quantity] = LTRIM(RTRIM(@Quantity))) "
+ "AND (@CatalogItemId IS NULL OR @CatalogItemId = '' OR [CatalogItem].[CatalogItemId] = LTRIM(RTRIM(@CatalogItemId))) "
+ "AND (@Price IS NULL OR @Price = '' OR [OrderDetail].[Price] = LTRIM(RTRIM(@Price))) "
+ "AND (@SpecialInstructions IS NULL OR @SpecialInstructions = '' OR [OrderDetail].[SpecialInstructions] = LTRIM(RTRIM(@SpecialInstructions))) "
+ "AND (@DiscountPercent IS NULL OR @DiscountPercent = '' OR [OrderDetail].[DiscountPercent] = LTRIM(RTRIM(@DiscountPercent))) "
+ "";
}
else if (sCondition == "Starts with...")
{
selectStatement
= "SELECT "
+ " [OrderDetail].[OrderDetailId] "
+ " ,[OrderDetail].[OrderId] "
+ " ,[OrderDetail].[Quantity] "
+ " ,[OrderDetail].[CatalogItemId] "
+ " ,[OrderDetail].[Price] "
+ " ,[OrderDetail].[SpecialInstructions] "
+ " ,[OrderDetail].[DiscountPercent] "
+ "FROM "
+ " [OrderDetail] "
+ "INNER JOIN [OrderHeader] ON [OrderDetail].[OrderId] = [OrderHeader].[OrderId] "
+ "INNER JOIN [CatalogItem] ON [OrderDetail].[CatalogItemId] = [CatalogItem].[CatalogItemId] "
+ "WHERE "
+ " (@OrderDetailId IS NULL OR @OrderDetailId = '' OR [OrderDetail].[OrderDetailId] LIKE LTRIM(RTRIM(@OrderDetailId)) + '%') "
+ "AND (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] LIKE LTRIM(RTRIM(@OrderId)) + '%') "
+ "AND (@Quantity IS NULL OR @Quantity = '' OR [OrderDetail].[Quantity] LIKE LTRIM(RTRIM(@Quantity)) + '%') "
+ "AND (@CatalogItemId IS NULL OR @CatalogItemId = '' OR [CatalogItem].[CatalogItemId] LIKE LTRIM(RTRIM(@CatalogItemId)) + '%') "
+ "AND (@Price IS NULL OR @Price = '' OR [OrderDetail].[Price] LIKE LTRIM(RTRIM(@Price)) + '%') "
+ "AND (@SpecialInstructions IS NULL OR @SpecialInstructions = '' OR [OrderDetail].[SpecialInstructions] LIKE LTRIM(RTRIM(@SpecialInstructions)) + '%') "
+ "AND (@DiscountPercent IS NULL OR @DiscountPercent = '' OR [OrderDetail].[DiscountPercent] LIKE LTRIM(RTRIM(@DiscountPercent)) + '%') "
+ "";
}
else if (sCondition == "More than...")
{
selectStatement
= "SELECT "
+ " [OrderDetail].[OrderDetailId] "
+ " ,[OrderDetail].[OrderId] "
+ " ,[OrderDetail].[Quantity] "
+ " ,[OrderDetail].[CatalogItemId] "
+ " ,[OrderDetail].[Price] "
+ " ,[OrderDetail].[SpecialInstructions] "
+ " ,[OrderDetail].[DiscountPercent] "
+ "FROM "
+ " [OrderDetail] "
+ "INNER JOIN [OrderHeader] ON [OrderDetail].[OrderId] = [OrderHeader].[OrderId] "
+ "INNER JOIN [CatalogItem] ON [OrderDetail].[CatalogItemId] = [CatalogItem].[CatalogItemId] "
+ "WHERE "
+ " (@OrderDetailId IS NULL OR @OrderDetailId = '' OR [OrderDetail].[OrderDetailId] > LTRIM(RTRIM(@OrderDetailId))) "
+ "AND (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] > LTRIM(RTRIM(@OrderId))) "
+ "AND (@Quantity IS NULL OR @Quantity = '' OR [OrderDetail].[Quantity] > LTRIM(RTRIM(@Quantity))) "
+ "AND (@CatalogItemId IS NULL OR @CatalogItemId = '' OR [CatalogItem].[CatalogItemId] > LTRIM(RTRIM(@CatalogItemId))) "
+ "AND (@Price IS NULL OR @Price = '' OR [OrderDetail].[Price] > LTRIM(RTRIM(@Price))) "
+ "AND (@SpecialInstructions IS NULL OR @SpecialInstructions = '' OR [OrderDetail].[SpecialInstructions] > LTRIM(RTRIM(@SpecialInstructions))) "
+ "AND (@DiscountPercent IS NULL OR @DiscountPercent = '' OR [OrderDetail].[DiscountPercent] > LTRIM(RTRIM(@DiscountPercent))) "
+ "";
}
else if (sCondition == "Less than...")
{
selectStatement
= "SELECT "
+ " [OrderDetail].[OrderDetailId] "
+ " ,[OrderDetail].[OrderId] "
+ " ,[OrderDetail].[Quantity] "
+ " ,[OrderDetail].[CatalogItemId] "
+ " ,[OrderDetail].[Price] "
+ " ,[OrderDetail].[SpecialInstructions] "
+ " ,[OrderDetail].[DiscountPercent] "
+ "FROM "
+ " [OrderDetail] "
+ "INNER JOIN [OrderHeader] ON [OrderDetail].[OrderId] = [OrderHeader].[OrderId] "
+ "INNER JOIN [CatalogItem] ON [OrderDetail].[CatalogItemId] = [CatalogItem].[CatalogItemId] "
+ "WHERE "
+ " (@OrderDetailId IS NULL OR @OrderDetailId = '' OR [OrderDetail].[OrderDetailId] < LTRIM(RTRIM(@OrderDetailId))) "
+ "AND (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] < LTRIM(RTRIM(@OrderId))) "
+ "AND (@Quantity IS NULL OR @Quantity = '' OR [OrderDetail].[Quantity] < LTRIM(RTRIM(@Quantity))) "
+ "AND (@CatalogItemId IS NULL OR @CatalogItemId = '' OR [CatalogItem].[CatalogItemId] < LTRIM(RTRIM(@CatalogItemId))) "
+ "AND (@Price IS NULL OR @Price = '' OR [OrderDetail].[Price] < LTRIM(RTRIM(@Price))) "
+ "AND (@SpecialInstructions IS NULL OR @SpecialInstructions = '' OR [OrderDetail].[SpecialInstructions] < LTRIM(RTRIM(@SpecialInstructions))) "
+ "AND (@DiscountPercent IS NULL OR @DiscountPercent = '' OR [OrderDetail].[DiscountPercent] < LTRIM(RTRIM(@DiscountPercent))) "
+ "";
}
else if (sCondition == "Equal or more than...")
{
selectStatement
= "SELECT "
+ " [OrderDetail].[OrderDetailId] "
+ " ,[OrderDetail].[OrderId] "
+ " ,[OrderDetail].[Quantity] "
+ " ,[OrderDetail].[CatalogItemId] "
+ " ,[OrderDetail].[Price] "
+ " ,[OrderDetail].[SpecialInstructions] "
+ " ,[OrderDetail].[DiscountPercent] "
+ "FROM "
+ " [OrderDetail] "
+ "INNER JOIN [OrderHeader] ON [OrderDetail].[OrderId] = [OrderHeader].[OrderId] "
+ "INNER JOIN [CatalogItem] ON [OrderDetail].[CatalogItemId] = [CatalogItem].[CatalogItemId] "
+ "WHERE "
+ " (@OrderDetailId IS NULL OR @OrderDetailId = '' OR [OrderDetail].[OrderDetailId] >= LTRIM(RTRIM(@OrderDetailId))) "
+ "AND (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] >= LTRIM(RTRIM(@OrderId))) "
+ "AND (@Quantity IS NULL OR @Quantity = '' OR [OrderDetail].[Quantity] >= LTRIM(RTRIM(@Quantity))) "
+ "AND (@CatalogItemId IS NULL OR @CatalogItemId = '' OR [CatalogItem].[CatalogItemId] >= LTRIM(RTRIM(@CatalogItemId))) "
+ "AND (@Price IS NULL OR @Price = '' OR [OrderDetail].[Price] >= LTRIM(RTRIM(@Price))) "
+ "AND (@SpecialInstructions IS NULL OR @SpecialInstructions = '' OR [OrderDetail].[SpecialInstructions] >= LTRIM(RTRIM(@SpecialInstructions))) "
+ "AND (@DiscountPercent IS NULL OR @DiscountPercent = '' OR [OrderDetail].[DiscountPercent] >= LTRIM(RTRIM(@DiscountPercent))) "
+ "";
}
else if (sCondition == "Equal or less than...")
{
selectStatement
= "SELECT "
+ " [OrderDetail].[OrderDetailId] "
+ " ,[OrderDetail].[OrderId] "
+ " ,[OrderDetail].[Quantity] "
+ " ,[OrderDetail].[CatalogItemId] "
+ " ,[OrderDetail].[Price] "
+ " ,[OrderDetail].[SpecialInstructions] "
+ " ,[OrderDetail].[DiscountPercent] "
+ "FROM "
+ " [OrderDetail] "
+ "INNER JOIN [OrderHeader] ON [OrderDetail].[OrderId] = [OrderHeader].[OrderId] "
+ "INNER JOIN [CatalogItem] ON [OrderDetail].[CatalogItemId] = [CatalogItem].[CatalogItemId] "
+ "WHERE "
+ " (@OrderDetailId IS NULL OR @OrderDetailId = '' OR [OrderDetail].[OrderDetailId] <= LTRIM(RTRIM(@OrderDetailId))) "
+ "AND (@OrderId IS NULL OR @OrderId = '' OR [OrderHeader].[OrderId] <= LTRIM(RTRIM(@OrderId))) "
+ "AND (@Quantity IS NULL OR @Quantity = '' OR [OrderDetail].[Quantity] <= LTRIM(RTRIM(@Quantity))) "
+ "AND (@CatalogItemId IS NULL OR @CatalogItemId = '' OR [CatalogItem].[CatalogItemId] <= LTRIM(RTRIM(@CatalogItemId))) "
+ "AND (@Price IS NULL OR @Price = '' OR [OrderDetail].[Price] <= LTRIM(RTRIM(@Price))) "
+ "AND (@SpecialInstructions IS NULL OR @SpecialInstructions = '' OR [OrderDetail].[SpecialInstructions] <= LTRIM(RTRIM(@SpecialInstructions))) "
+ "AND (@DiscountPercent IS NULL OR @DiscountPercent = '' OR [OrderDetail].[DiscountPercent] <= LTRIM(RTRIM(@DiscountPercent))) "
+ "";
}
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.CommandType = CommandType.Text;
if (sField == "Order Detail Id")
{
selectCommand.Parameters.AddWithValue("@OrderDetailId", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@OrderDetailId", DBNull.Value);
}
if (sField == "Order Id")
{
selectCommand.Parameters.AddWithValue("@OrderId", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@OrderId", DBNull.Value);
}
if (sField == "Quantity")
{
selectCommand.Parameters.AddWithValue("@Quantity", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@Quantity", DBNull.Value);
}
if (sField == "Catalog Item Id")
{
selectCommand.Parameters.AddWithValue("@CatalogItemId", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@CatalogItemId", DBNull.Value);
}
if (sField == "Price")
{
selectCommand.Parameters.AddWithValue("@Price", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@Price", DBNull.Value);
}
if (sField == "Special Instructions")
{
selectCommand.Parameters.AddWithValue("@SpecialInstructions", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@SpecialInstructions", DBNull.Value);
}
if (sField == "Discount Percent")
{
selectCommand.Parameters.AddWithValue("@DiscountPercent", sValue);
}
else
{
selectCommand.Parameters.AddWithValue("@DiscountPercent", DBNull.Value);
}
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (SqlException)
{
return(dt);
}
finally
{
connection.Close();
}
return(dt);
}
