public static bool Update(Products oldProducts, Products newProducts) { SqlConnection connection = TutorialsteamData.GetConnection(); string updateStatement = "UPDATE " + " [Products] " + "SET " + " [Name] = @NewName " + " ,[Quantity] = @NewQuantity " + " ,[BoxSize] = @NewBoxSize " + " ,[Price] = @NewPrice " + "WHERE " + " [ProductId] = @OldProductId " + " AND [Name] = @OldName " + " AND [Quantity] = @OldQuantity " + " AND [BoxSize] = @OldBoxSize " + " AND [Price] = @OldPrice " + ""; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.CommandType = CommandType.Text; updateCommand.Parameters.AddWithValue("@NewName", newProducts.Name); updateCommand.Parameters.AddWithValue("@NewQuantity", newProducts.Quantity); updateCommand.Parameters.AddWithValue("@NewBoxSize", newProducts.BoxSize); updateCommand.Parameters.AddWithValue("@NewPrice", newProducts.Price); updateCommand.Parameters.AddWithValue("@OldProductId", oldProducts.ProductId); updateCommand.Parameters.AddWithValue("@OldName", oldProducts.Name); updateCommand.Parameters.AddWithValue("@OldQuantity", oldProducts.Quantity); updateCommand.Parameters.AddWithValue("@OldBoxSize", oldProducts.BoxSize); updateCommand.Parameters.AddWithValue("@OldPrice", oldProducts.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 bool Add(Products Products) { SqlConnection connection = TutorialsteamData.GetConnection(); string insertStatement = "INSERT " + " [Products] " + " ( " + " [Name] " + " ,[Quantity] " + " ,[BoxSize] " + " ,[Price] " + " ) " + "VALUES " + " ( " + " @Name " + " ,@Quantity " + " ,@BoxSize " + " ,@Price " + " ) " + ""; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.CommandType = CommandType.Text; insertCommand.Parameters.AddWithValue("@Name", Products.Name); insertCommand.Parameters.AddWithValue("@Quantity", Products.Quantity); insertCommand.Parameters.AddWithValue("@BoxSize", Products.BoxSize); insertCommand.Parameters.AddWithValue("@Price", Products.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 Products Select_Record(Products productsPara) { Products products = new Products(); SqlConnection connection = TutorialsteamData.GetConnection(); string selectStatement = "SELECT " + " [ProductId] " + " ,[Name] " + " ,[Quantity] " + " ,[BoxSize] " + " ,[Price] " + "FROM " + " [Products] " + "WHERE " + " [ProductId] = @ProductId " + ""; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.CommandType = CommandType.Text; selectCommand.Parameters.AddWithValue("@ProductId", productsPara.ProductId); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { products.ProductId = System.Convert.ToInt32(reader["ProductId"]); products.Name = System.Convert.ToString(reader["Name"]); products.Quantity = System.Convert.ToInt32(reader["Quantity"]); products.BoxSize = System.Convert.ToInt32(reader["BoxSize"]); products.Price = System.Convert.ToDecimal(reader["Price"]); } else { products = null; } reader.Close(); } catch (SqlException) { return(products); } finally { connection.Close(); } return(products); }
public static bool Delete(Products Products) { SqlConnection connection = TutorialsteamData.GetConnection(); string deleteStatement = "DELETE FROM " + " [Products] " + "WHERE " + " [ProductId] = @OldProductId " + " AND [Name] = @OldName " + " AND [Quantity] = @OldQuantity " + " AND [BoxSize] = @OldBoxSize " + " AND [Price] = @OldPrice " + ""; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.CommandType = CommandType.Text; deleteCommand.Parameters.AddWithValue("@OldProductId", Products.ProductId); deleteCommand.Parameters.AddWithValue("@OldName", Products.Name); deleteCommand.Parameters.AddWithValue("@OldQuantity", Products.Quantity); deleteCommand.Parameters.AddWithValue("@OldBoxSize", Products.BoxSize); deleteCommand.Parameters.AddWithValue("@OldPrice", Products.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 DataTable SelectAll() { SqlConnection connection = TutorialsteamData.GetConnection(); string selectStatement = "SELECT " + " [Products].[ProductId] " + " ,[Products].[Name] " + " ,[Products].[Quantity] " + " ,[Products].[BoxSize] " + " ,[Products].[Price] " + "FROM " + " [Products] " + ""; 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 DataTable Search(string sField, string sCondition, string sValue) { SqlConnection connection = TutorialsteamData.GetConnection(); string selectStatement = ""; if (sCondition == "Contains") { selectStatement = "SELECT " + " [Products].[ProductId] " + " ,[Products].[Name] " + " ,[Products].[Quantity] " + " ,[Products].[BoxSize] " + " ,[Products].[Price] " + "FROM " + " [Products] " + "WHERE " + " (@ProductId IS NULL OR @ProductId = '' OR [Products].[ProductId] LIKE '%' + LTRIM(RTRIM(@ProductId)) + '%') " + "AND (@Name IS NULL OR @Name = '' OR [Products].[Name] LIKE '%' + LTRIM(RTRIM(@Name)) + '%') " + "AND (@Quantity IS NULL OR @Quantity = '' OR [Products].[Quantity] LIKE '%' + LTRIM(RTRIM(@Quantity)) + '%') " + "AND (@BoxSize IS NULL OR @BoxSize = '' OR [Products].[BoxSize] LIKE '%' + LTRIM(RTRIM(@BoxSize)) + '%') " + "AND (@Price IS NULL OR @Price = '' OR [Products].[Price] LIKE '%' + LTRIM(RTRIM(@Price)) + '%') " + ""; } else if (sCondition == "Equals") { selectStatement = "SELECT " + " [Products].[ProductId] " + " ,[Products].[Name] " + " ,[Products].[Quantity] " + " ,[Products].[BoxSize] " + " ,[Products].[Price] " + "FROM " + " [Products] " + "WHERE " + " (@ProductId IS NULL OR @ProductId = '' OR [Products].[ProductId] = LTRIM(RTRIM(@ProductId))) " + "AND (@Name IS NULL OR @Name = '' OR [Products].[Name] = LTRIM(RTRIM(@Name))) " + "AND (@Quantity IS NULL OR @Quantity = '' OR [Products].[Quantity] = LTRIM(RTRIM(@Quantity))) " + "AND (@BoxSize IS NULL OR @BoxSize = '' OR [Products].[BoxSize] = LTRIM(RTRIM(@BoxSize))) " + "AND (@Price IS NULL OR @Price = '' OR [Products].[Price] = LTRIM(RTRIM(@Price))) " + ""; } else if (sCondition == "Starts with...") { selectStatement = "SELECT " + " [Products].[ProductId] " + " ,[Products].[Name] " + " ,[Products].[Quantity] " + " ,[Products].[BoxSize] " + " ,[Products].[Price] " + "FROM " + " [Products] " + "WHERE " + " (@ProductId IS NULL OR @ProductId = '' OR [Products].[ProductId] LIKE LTRIM(RTRIM(@ProductId)) + '%') " + "AND (@Name IS NULL OR @Name = '' OR [Products].[Name] LIKE LTRIM(RTRIM(@Name)) + '%') " + "AND (@Quantity IS NULL OR @Quantity = '' OR [Products].[Quantity] LIKE LTRIM(RTRIM(@Quantity)) + '%') " + "AND (@BoxSize IS NULL OR @BoxSize = '' OR [Products].[BoxSize] LIKE LTRIM(RTRIM(@BoxSize)) + '%') " + "AND (@Price IS NULL OR @Price = '' OR [Products].[Price] LIKE LTRIM(RTRIM(@Price)) + '%') " + ""; } else if (sCondition == "More than...") { selectStatement = "SELECT " + " [Products].[ProductId] " + " ,[Products].[Name] " + " ,[Products].[Quantity] " + " ,[Products].[BoxSize] " + " ,[Products].[Price] " + "FROM " + " [Products] " + "WHERE " + " (@ProductId IS NULL OR @ProductId = '' OR [Products].[ProductId] > LTRIM(RTRIM(@ProductId))) " + "AND (@Name IS NULL OR @Name = '' OR [Products].[Name] > LTRIM(RTRIM(@Name))) " + "AND (@Quantity IS NULL OR @Quantity = '' OR [Products].[Quantity] > LTRIM(RTRIM(@Quantity))) " + "AND (@BoxSize IS NULL OR @BoxSize = '' OR [Products].[BoxSize] > LTRIM(RTRIM(@BoxSize))) " + "AND (@Price IS NULL OR @Price = '' OR [Products].[Price] > LTRIM(RTRIM(@Price))) " + ""; } else if (sCondition == "Less than...") { selectStatement = "SELECT " + " [Products].[ProductId] " + " ,[Products].[Name] " + " ,[Products].[Quantity] " + " ,[Products].[BoxSize] " + " ,[Products].[Price] " + "FROM " + " [Products] " + "WHERE " + " (@ProductId IS NULL OR @ProductId = '' OR [Products].[ProductId] < LTRIM(RTRIM(@ProductId))) " + "AND (@Name IS NULL OR @Name = '' OR [Products].[Name] < LTRIM(RTRIM(@Name))) " + "AND (@Quantity IS NULL OR @Quantity = '' OR [Products].[Quantity] < LTRIM(RTRIM(@Quantity))) " + "AND (@BoxSize IS NULL OR @BoxSize = '' OR [Products].[BoxSize] < LTRIM(RTRIM(@BoxSize))) " + "AND (@Price IS NULL OR @Price = '' OR [Products].[Price] < LTRIM(RTRIM(@Price))) " + ""; } else if (sCondition == "Equal or more than...") { selectStatement = "SELECT " + " [Products].[ProductId] " + " ,[Products].[Name] " + " ,[Products].[Quantity] " + " ,[Products].[BoxSize] " + " ,[Products].[Price] " + "FROM " + " [Products] " + "WHERE " + " (@ProductId IS NULL OR @ProductId = '' OR [Products].[ProductId] >= LTRIM(RTRIM(@ProductId))) " + "AND (@Name IS NULL OR @Name = '' OR [Products].[Name] >= LTRIM(RTRIM(@Name))) " + "AND (@Quantity IS NULL OR @Quantity = '' OR [Products].[Quantity] >= LTRIM(RTRIM(@Quantity))) " + "AND (@BoxSize IS NULL OR @BoxSize = '' OR [Products].[BoxSize] >= LTRIM(RTRIM(@BoxSize))) " + "AND (@Price IS NULL OR @Price = '' OR [Products].[Price] >= LTRIM(RTRIM(@Price))) " + ""; } else if (sCondition == "Equal or less than...") { selectStatement = "SELECT " + " [Products].[ProductId] " + " ,[Products].[Name] " + " ,[Products].[Quantity] " + " ,[Products].[BoxSize] " + " ,[Products].[Price] " + "FROM " + " [Products] " + "WHERE " + " (@ProductId IS NULL OR @ProductId = '' OR [Products].[ProductId] <= LTRIM(RTRIM(@ProductId))) " + "AND (@Name IS NULL OR @Name = '' OR [Products].[Name] <= LTRIM(RTRIM(@Name))) " + "AND (@Quantity IS NULL OR @Quantity = '' OR [Products].[Quantity] <= LTRIM(RTRIM(@Quantity))) " + "AND (@BoxSize IS NULL OR @BoxSize = '' OR [Products].[BoxSize] <= LTRIM(RTRIM(@BoxSize))) " + "AND (@Price IS NULL OR @Price = '' OR [Products].[Price] <= LTRIM(RTRIM(@Price))) " + ""; } SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.CommandType = CommandType.Text; if (sField == "Product Id") { selectCommand.Parameters.AddWithValue("@ProductId", sValue); } else { selectCommand.Parameters.AddWithValue("@ProductId", DBNull.Value); } if (sField == "Name") { selectCommand.Parameters.AddWithValue("@Name", sValue); } else { selectCommand.Parameters.AddWithValue("@Name", DBNull.Value); } if (sField == "Quantity") { selectCommand.Parameters.AddWithValue("@Quantity", sValue); } else { selectCommand.Parameters.AddWithValue("@Quantity", DBNull.Value); } if (sField == "Box Size") { selectCommand.Parameters.AddWithValue("@BoxSize", sValue); } else { selectCommand.Parameters.AddWithValue("@BoxSize", DBNull.Value); } if (sField == "Price") { selectCommand.Parameters.AddWithValue("@Price", sValue); } else { selectCommand.Parameters.AddWithValue("@Price", 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); }