public static DataSet GetCurrentSize(int productId)
{
DataSet lresultDataSet = new DataSet();
// DataTable dataTable = new DataTable();
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lCommandString = @"SELECT ID,Name,Country,CreationDate,Code,Description FROM Manufacture";
// Создаем и настраиваем экземпляр класса SqlDataAdapter
SqlDataAdapter adapter = new SqlDataAdapter(lCommandString, lConnection);
try
{
// lconnection.Open();
adapter.Fill(lresultDataSet, "Manufacture");
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lresultDataSet);
}
public static string GetProductTitleImage(int pProductId)
{
string lresult = string.Empty;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
SqlCommand lCommand = new SqlCommand("GetProductTitleImage", lConnection);
lCommand.CommandType = System.Data.CommandType.StoredProcedure;
lCommand.Parameters.Add(new SqlParameter(@"ProductId", SqlDbType.Int));
lCommand.Parameters[@"ProductId"].Value = pProductId;
try
{
lConnection.Open();
lresult = (string)lCommand.ExecuteScalar();
// lresult = (int)lcommand.Parameters[@"NewProductId"].Value;
}
catch (Exception ex) { }
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lresult);
}
/// <summary>
/// insert new company to dataBase
/// </summary>
/// <param name="pId">id</param>
/// <param name="pCompanyName">Company Name</param>
/// <param name="pCountry">Country for Manufacture</param>
/// <param name="pCode">internal manufacturer code</param>
/// <param name="pCreateDate">date of the founding of the company</param>
/// <param name="pDecription">Text description of the company</param>
/// <returns>successful making changes</returns>
public static bool CreateNewManufacture(string pCompanyName, string pCountry, string pCode, DateTime pCreateDate, string pDecription)
{
bool lResult = false;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lCommandText = @"INSERT INTO Manufacture
(Name
,Country
,CreationDate
,Code
,Description)
VALUES
(@Name
,@Country
,@CreationDate
,@Code
,@Description)";
SqlCommand lCommand = new SqlCommand();
lCommand.CommandText = lCommandText;
lCommand.Connection = lConnection;
lCommand.Parameters.Add(new SqlParameter(@"Name", SqlDbType.NVarChar, 250));
lCommand.Parameters[@"Name"].Value = pCompanyName;
lCommand.Parameters.Add(new SqlParameter(@"Country", SqlDbType.NVarChar, 250));
lCommand.Parameters[@"Country"].Value = pCountry;
lCommand.Parameters.Add(new SqlParameter(@"CreationDate", SqlDbType.SmallDateTime));
lCommand.Parameters[@"CreationDate"].Value = pCreateDate;
lCommand.Parameters.Add(new SqlParameter(@"Code", SqlDbType.NVarChar, 50));
lCommand.Parameters[@"Code"].Value = pCode;
lCommand.Parameters.Add(new SqlParameter(@"Description", SqlDbType.NVarChar, 400));
lCommand.Parameters[@"Description"].Value = pCompanyName;
try
{
lConnection.Open();
lCommand.ExecuteNonQuery();
lResult = true;
}
catch { }
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lResult);
}
public static DataSet GetTypes()
{
DataSet dataSet = new DataSet();
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lCommandString = @"SELECT ID,TypeItem FROM TypeItem";
// Создаем и настраиваем экземпляр класса SqlDataAdapter
SqlDataAdapter adapter = new SqlDataAdapter(lCommandString, lConnection);
try
{
adapter.Fill(dataSet);
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(dataSet);
}
public static DataSet GetCountSize(int pProductId)
{
DataSet ldataSet = new DataSet();
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
SqlCommand lCommand = new SqlCommand();
string lCommandText = @"select Size.ID, Size.Size, ProductSize.ProductCount
FROM ProductSize INNER JOIN
Size ON ProductSize.SizeID = Size.ID
WHERE(ProductSize.ProductId = @ProductID)";
lCommand.CommandText = lCommandText;
lCommand.Connection = lConnection;
SqlParameter lparametr = new SqlParameter(@"ProductID", SqlDbType.Int);
lparametr.Value = pProductId;
//lCommand.Parameters.Add(new SqlParameter);
//lCommand.Parameters[@"ProductID"].Value = pProductId;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = lCommand;
//.CommandText = @"SELECT Size.*
/* FROM ProductSize INNER JOIN
* Size ON ProductSize.SizeID = Size.ID
* WHERE(ProductSize.ProductId = @ProductID)";
*/
//lCommandText;
adapter.SelectCommand.Parameters.Add(lparametr);
try
{
adapter.Fill(ldataSet);
}
catch (SqlException exc)
{
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(ldataSet);
}
/// <summary>
/// a list of all the Size
/// </summary>
/// <returns>data set</returns>
public static DataSet GetSeason()
{
DataSet dataSet = new DataSet();
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lCommandString = @"SELECT ID,Size FROM Size";
SqlDataAdapter adapter = new SqlDataAdapter(lCommandString, lConnection);
try
{
adapter.Fill(dataSet, "Size");
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(dataSet);
}
/// <summary>
/// Remove the selected season
/// </summary>
/// <param name="pSeasonId">id selected records</param>
/// <returns>successful changes</returns>
public static bool DeleteSeasonal(int pSeasonId)
{
bool lResult = false;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lSqlCommandstring = @"DELETE FROM SeasonalType WHERE ID = @ID";
SqlCommand lCommand = new SqlCommand(lSqlCommandstring, lConnection);
lCommand.Parameters.AddWithValue("@ID", pSeasonId);
try
{
lConnection.Open();
lCommand.ExecuteNonQuery();
lResult = true;
}
catch (SqlException exc)
{
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lResult);
}
/// <summary>
/// inser new color value to db
/// </summary>
/// <param name="pColor">color name</param>
/// <returns></returns>
public static bool AddType(string pTypeName)
{
bool result = false;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lSqlCommandstring = @"INSERT INTO TypeItem VALUES(@TypeName)";
SqlCommand lCommand = new SqlCommand(lSqlCommandstring, lConnection);
lCommand.Parameters.AddWithValue("@TypeName", pTypeName);
try
{
lConnection.Open();
lCommand.ExecuteNonQuery();
result = true;
}
catch (SqlException exc)
{
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(result);
}
public static decimal ComputeProductsValue(int productId)
{
decimal lresult = 0;
SqlConnection lconnection = new SqlConnection();
lconnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lcommandText = "select Cost From product where id = @ProductId";
SqlCommand lcommand = new SqlCommand(lcommandText, lconnection);
lcommand.Parameters.Add(new SqlParameter(@"ProductId", SqlDbType.Int));
lcommand.Parameters[@"ProductId"].Value = productId;
try
{
lconnection.Open();
lresult = (decimal)lcommand.ExecuteScalar();
// lresult = (int)lcommand.Parameters[@"NewProductId"].Value;
}
catch (Exception ex)
{
}
finally
{
if (lconnection.State == ConnectionState.Open)
{
lconnection.Close();
}
}
return(lresult);
}
/// <summary>
/// delete colors from db
/// </summary>
/// <param name="pColor">id color for deleting</param>
/// <returns></returns>
public static int DeleteType(int ptypeId)
{
int lResult = 0;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lSqlCommandstring = @"DELETE FROM TypeItem WHERE ID = @ID";
SqlCommand lCommand = new SqlCommand(lSqlCommandstring, lConnection);
lCommand.Parameters.AddWithValue("@ID", ptypeId);
try
{
lConnection.Open();
lCommand.ExecuteNonQuery();
lResult = 0;
}
catch (SqlException exc)
{
lResult = 2;
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lResult);
}
public static int InsertImage(int pProductId, string pImageUrl, string pImagePreviewPath, string pCaption,
bool pIsTitle, string pImageName, int pSize)
{
int lresult;
lresult = 0;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
SqlCommand lcommand = new SqlCommand("InsertImage", lConnection);
lcommand.CommandType = CommandType.StoredProcedure;
lcommand.Parameters.Add(new SqlParameter(@"ImageUrl", SqlDbType.NVarChar, 255));
lcommand.Parameters[@"ImageUrl"].Value = pImageUrl;
lcommand.Parameters.Add(new SqlParameter(@"imagePreviewPath", SqlDbType.NVarChar, 255));
lcommand.Parameters[@"imagePreviewPath"].Value = pImagePreviewPath;
lcommand.Parameters.Add(new SqlParameter(@"CreationDate", SqlDbType.SmallDateTime));
lcommand.Parameters[@"CreationDate"].Value = DateTime.Now;
lcommand.Parameters.Add(new SqlParameter(@"Caption", SqlDbType.NVarChar, 255));
lcommand.Parameters[@"Caption"].Value = pCaption;
lcommand.Parameters.Add(new SqlParameter(@"istitle", SqlDbType.Bit));
lcommand.Parameters[@"istitle"].Value = pIsTitle;
lcommand.Parameters.Add(new SqlParameter(@"imageName", SqlDbType.NVarChar, 255));
lcommand.Parameters[@"imageName"].Value = pImageName;
lcommand.Parameters.Add(new SqlParameter(@"productId", SqlDbType.Int));
lcommand.Parameters[@"productId"].Value = pProductId;
lcommand.Parameters.Add(new SqlParameter(@"NewImageId", SqlDbType.Int));
lcommand.Parameters[@"NewImageId"].Direction = ParameterDirection.Output;
try
{
lConnection.Open();
lcommand.ExecuteNonQuery();
lresult = (int)lcommand.Parameters[@"NewImageId"].Value;
}
catch (Exception ex) { }
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lresult);
}
/// <summary>
/// обновление записи в БД
/// </summary>
/// <param name="pId"></param>
/// <param name="pMaterial"></param>
/// <returns></returns>
public static int UpdateMaterial(int pId, string pMaterial)
{
int lResult = 0;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lCommandText = @"UPDATE MaterialType
SET MaterialType = @MaterialType
WHERE ID = @ID";
SqlCommand lCommand = new SqlCommand();
lCommand.CommandText = lCommandText;
lCommand.Connection = lConnection;
lCommand.Parameters.Add(new SqlParameter(@"MaterialType", SqlDbType.NVarChar, 250));
lCommand.Parameters[@"MaterialType"].Value = pMaterial;
lCommand.Parameters.Add(new SqlParameter(@"ID", SqlDbType.Int));
lCommand.Parameters[@"ID"].Value = pId;
try
{
lConnection.Open();
lCommand.ExecuteNonQuery();
lResult = 0;
}
catch (SqlException exc)
{
lResult = 1;
}
catch (Exception ex)
{
lResult = 2;
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lResult);
}
/// <summary>
/// update record in database
/// </summary>
/// <param name="pId">id</param>
/// <param name="pSeasonalName">name</param>
/// <returns>successful changes</returns>
public static bool UpdateSize(int pId, string pSize)
{
bool lResult = false;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lCommandText = @"UPDATE Size
SET Size = @Size
WHERE ID = @ID";
SqlCommand lCommand = new SqlCommand();
lCommand.CommandText = lCommandText;
lCommand.Connection = lConnection;
lCommand.Parameters.Add(new SqlParameter(@"Size", SqlDbType.NVarChar, 250));
lCommand.Parameters[@"Size"].Value = pSize;
lCommand.Parameters.Add(new SqlParameter(@"ID", SqlDbType.Int));
lCommand.Parameters[@"ID"].Value = pId;
try
{
lConnection.Open();
lCommand.ExecuteNonQuery();
lResult = true;
}
catch (SqlException exc)
{
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lResult);
}
/// <summary>
/// add new rebview for product to data base
/// </summary>
/// <param name="pProductId"></param>
/// <param name="puserName"></param>
/// <param name="ptext"></param>
public static bool SetReviewForProduct(int pProductId, string puserName, string ptext)
{
//@userName nvarchar(255),@Text nvarChar(max), @productID int
bool lresult = false;
SqlConnection lconnection = new SqlConnection();
lconnection.ConnectionString = ShoeStoreDB.GetConnectionString();
SqlCommand lcommand = new SqlCommand("CreateReview", lconnection);
lcommand.CommandType = System.Data.CommandType.StoredProcedure;
lcommand.Parameters.Add(new SqlParameter(@"ProductId", SqlDbType.Int));
lcommand.Parameters[@"ProductId"].Value = pProductId;
lcommand.Parameters.Add(new SqlParameter(@"Text", SqlDbType.NText));
lcommand.Parameters[@"Text"].Value = ptext;
lcommand.Parameters.Add(new SqlParameter(@"userName", SqlDbType.NVarChar, 255));
lcommand.Parameters[@"userName"].Value = puserName;
try
{
lconnection.Open();
lcommand.ExecuteNonQuery();
lresult = true;
// lresult = (int)lcommand.Parameters[@"NewProductId"].Value;
}
catch (Exception ex)
{
}
finally
{
if (lconnection.State == ConnectionState.Open)
{
lconnection.Close();
}
}
return(lresult);
}
/// <summary>
////добавление
/// </summary>
/// <param name="pProductId"></param>
/// <returns></returns>
public static bool SetProductsSize(int pProductId, int psizeid, int pProductCount)
{
bool lresult = false;
//SetProductsSize
SqlConnection lconnection = new SqlConnection();
lconnection.ConnectionString = ShoeStoreDB.GetConnectionString();
SqlCommand lcommand = new SqlCommand("SetProductsSize", lconnection);
lcommand.CommandType = System.Data.CommandType.StoredProcedure;
lcommand.Parameters.Add(new SqlParameter(@"productid", SqlDbType.Int));
lcommand.Parameters[@"productid"].Value = pProductId;
lcommand.Parameters.Add(new SqlParameter(@"sizeId", SqlDbType.Int));
lcommand.Parameters[@"sizeId"].Value = psizeid;;
lcommand.Parameters.Add(new SqlParameter(@"count", SqlDbType.Int));
lcommand.Parameters[@"count"].Value = pProductCount;;
try
{
lconnection.Open();
lcommand.ExecuteNonQuery();
lresult = true;
}
catch (SqlException exc)
{
}
catch (Exception ex)
{
}
finally
{
if (lconnection.State == ConnectionState.Open)
{
lconnection.Close();
}
}
return(lresult);
}
/// <summary>
/// receiving data according to the selected size
/// </summary>
/// <param name="pProductId"></param>
/// <param name="psize"></param>
/// <returns></returns>
public static DataSet GetProductBySize(int pProductId, int psize)
{
DataSet ldataSet = new DataSet();
SqlConnection lconnection = new SqlConnection();
lconnection.ConnectionString = ShoeStoreDB.GetConnectionString();
SqlCommand lcommand = new SqlCommand("GetProductsInfoById", lconnection);
lcommand.CommandType = System.Data.CommandType.StoredProcedure;
lcommand.Parameters.Add(new SqlParameter(@"ProductId", SqlDbType.Int));
lcommand.Parameters[@"ProductId"].Value = pProductId;
lcommand.Parameters.Add(new SqlParameter(@"Size", SqlDbType.Int));
lcommand.Parameters[@"Size"].Value = psize;
SqlDataAdapter ladapter = new SqlDataAdapter(lcommand);
try
{
//lconnection.Open();
ladapter.Fill(ldataSet);
}
catch (SqlException ex)
{
}
catch (Exception ex)
{
}
finally
{
if (lconnection.State == ConnectionState.Open)
{
lconnection.Close();
}
}
return(ldataSet);
}
public static DataSet GetProductValueToCart(int productId)
{
DataSet ldataSet = new DataSet();
SqlConnection lconnection = new SqlConnection();
lconnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lcommandText = @"SELECT Product.Name, Manufacture.Name AS ManufactyreName, Manufacture.Country,Product.Cost
FROM Product INNER JOIN Manufacture ON Product.ManufacturedId = Manufacture.ID
WHERE Product.ID = @ProductId";
SqlCommand lcommand = new SqlCommand(lcommandText, lconnection);
lcommand.Parameters.Add(new SqlParameter(@"ProductId", SqlDbType.Int));
lcommand.Parameters[@"ProductId"].Value = productId;
SqlDataAdapter ladapter = new SqlDataAdapter(lcommand);
try
{
//lconnection.Open();
ladapter.Fill(ldataSet);
}
catch (SqlException ex)
{
}
catch (Exception ex)
{
}
finally
{
if (lconnection.State == ConnectionState.Open)
{
lconnection.Close();
}
}
return(ldataSet);
}
public static int GetProductCount(int pProductId, int psizeId)
{
int lresult = 0;
SqlConnection lconnection = new SqlConnection();
lconnection.ConnectionString = ShoeStoreDB.GetConnectionString();
SqlCommand lcommand = new SqlCommand("GetProductCount", lconnection);
lcommand.CommandType = System.Data.CommandType.StoredProcedure;
lcommand.Parameters.Add(new SqlParameter(@"ProductId", SqlDbType.Int));
lcommand.Parameters[@"ProductId"].Value = pProductId;
lcommand.Parameters.Add(new SqlParameter(@"SizeID", SqlDbType.Int));
lcommand.Parameters[@"SizeID"].Value = psizeId;;
try
{
lconnection.Open();
lresult = (int)lcommand.ExecuteScalar();
}
catch (SqlException exc)
{
}
catch (Exception ex)
{
}
finally
{
if (lconnection.State == ConnectionState.Open)
{
lconnection.Close();
}
}
return(lresult);
}
/// <summary>
/// delete colors from db
/// </summary>
/// <param name="pColor">id color for deleting</param>
/// <returns></returns>
public static bool EditType(int pColorId, string ptype)
{
bool lResult = false;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lSqlCommandstring = @"UPDATE TypeItem SET TypeItem = @TypeItem WHERE ID=@ID";
SqlCommand lCommand = new SqlCommand(lSqlCommandstring, lConnection);
lCommand.Parameters.Add(new SqlParameter(@"ID", SqlDbType.Int));
lCommand.Parameters[@"ID"].Value = pColorId;
lCommand.Parameters.Add(new SqlParameter(@"TypeItem", SqlDbType.NVarChar, 255));
lCommand.Parameters[@"TypeItem"].Value = ptype;
try
{
lConnection.Open();
lCommand.ExecuteNonQuery();
lResult = true;
}
catch (SqlException exc)
{
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lResult);
}
/// <summary>
/// создание новой записи
/// </summary>
/// <param name="pMaterialName">название материала</param>
public static bool CreateNewMaterial(string pMaterialName)
{
bool lResult = false;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lCommandText = @"INSERT INTO MaterialType( MaterialType )
VALUES ( @MaterialType )";
SqlCommand lCommand = new SqlCommand();
lCommand.CommandText = lCommandText;
lCommand.Connection = lConnection;
lCommand.Parameters.Add(new SqlParameter(@"MaterialType", SqlDbType.NVarChar, 250));
lCommand.Parameters[@"MaterialType"].Value = pMaterialName;
try
{
lConnection.Open();
lCommand.ExecuteNonQuery();
lResult = true;
}
catch { }
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lResult);
}
/// <summary>
///
/// </summary>
/// <param name="pProductId"></param>
/// <returns></returns>
public static DataSet GetSizeForCurrentProduct(int pProductId)
{
DataSet dataSet = new DataSet();
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lCommandString = @"SELECT Size.ID, Size.Size
FROM ProductSize INNER JOIN
Size ON ProductSize.SizeID = Size.ID
AND ProductSize.ProductID = @Productid";
SqlCommand lCommand = new SqlCommand(lCommandString, lConnection);
lCommand.Parameters.Add(new SqlParameter(@"Productid", SqlDbType.Int));
lCommand.Parameters[@"Productid"].Value = pProductId;
SqlDataAdapter adapter = new SqlDataAdapter(lCommand);
try
{
adapter.Fill(dataSet);
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(dataSet);
}
public static bool DeleteProduct(int productId)
{
bool lresult = false;
SqlConnection lconnection = new SqlConnection();
lconnection.ConnectionString = ShoeStoreDB.GetConnectionString();
SqlCommand lcommand = new SqlCommand("deleteProduct", lconnection);
lcommand.CommandType = System.Data.CommandType.StoredProcedure;
lcommand.Parameters.Add(new SqlParameter(@"ProductId", SqlDbType.Int));
lcommand.Parameters[@"ProductId"].Value = productId;
try
{
lconnection.Open();
lcommand.ExecuteNonQuery();
lresult = true;
}
catch (SqlException exc)
{
}
catch (Exception ex)
{
}
finally
{
if (lconnection.State == ConnectionState.Open)
{
lconnection.Close();
}
}
return(lresult);
}
/// <summary>
/// обновление текущего значения для указанного производителя
/// </summary>
/// <param name="pId">id</param>
/// <param name="pCompanyName">Company Name</param>
/// <param name="pCountry">Country for Manufacture</param>
/// <param name="pCode">internal manufacturer code</param>
/// <param name="pCreateDate">date of the founding of the company</param>
/// <param name="pDecription">Text description of the company</param>
/// <returns>successful making changes</returns>
public static bool UpdateManufacture(int pId, string pCompanyName, string pCountry, string pCode, DateTime pCreateDate, string pDecription)
{
bool lResult = false;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
string lCommandText = @"UPDATE Manufacture
SET Name = @Name
,Country = @Country
,CreationDate = @CreationDate
,Code = @Code
,Description = @Description
WHERE ID = @ID";
SqlCommand lCommand = new SqlCommand();
lCommand.CommandText = lCommandText;
lCommand.Connection = lConnection;
lCommand.Parameters.Add(new SqlParameter(@"Name", SqlDbType.NVarChar, 250));
lCommand.Parameters[@"Name"].Value = pCompanyName;
lCommand.Parameters.Add(new SqlParameter(@"Country", SqlDbType.NVarChar, 250));
lCommand.Parameters[@"Country"].Value = pCountry;
lCommand.Parameters.Add(new SqlParameter(@"CreationDate", SqlDbType.SmallDateTime));
lCommand.Parameters[@"CreationDate"].Value = pCreateDate;
lCommand.Parameters.Add(new SqlParameter(@"Code", SqlDbType.NVarChar, 50));
lCommand.Parameters[@"Code"].Value = pCode;
lCommand.Parameters.Add(new SqlParameter(@"Description", SqlDbType.NVarChar, 400));
lCommand.Parameters[@"Description"].Value = pCompanyName;
lCommand.Parameters.Add(new SqlParameter(@"ID", SqlDbType.Int));
lCommand.Parameters[@"ID"].Value = pId;
try
{
lConnection.Open();
lCommand.ExecuteNonQuery();
lResult = true;
}
catch (SqlException exc)
{
}
catch (Exception ex)
{
}
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lResult);
}
public static int CreateProduct(int pColorId, int pSizeId, int pSexId, int pMaterialTypeId,
int pManufactureId, int pSeasonId, int pTypeId, string pName, decimal pCost, string pCode,
string pDescription)
{
int lresult = 0;
SqlConnection lConnection = new SqlConnection();
lConnection.ConnectionString = ShoeStoreDB.GetConnectionString();
SqlCommand lCommand = new SqlCommand("CreateProduct", lConnection);
lCommand.CommandType = System.Data.CommandType.StoredProcedure;
lCommand.Parameters.Add(new SqlParameter(@"Name", SqlDbType.NVarChar, 255));
lCommand.Parameters[@"Name"].Value = pName;
lCommand.Parameters.Add(new SqlParameter(@"ColorId", SqlDbType.Int));
lCommand.Parameters[@"ColorId"].Value = pColorId;
lCommand.Parameters.Add(new SqlParameter(@"SizeId", SqlDbType.Int));
lCommand.Parameters[@"SizeId"].Value = pSizeId;
lCommand.Parameters.Add(new SqlParameter(@"SexId", SqlDbType.Int));
lCommand.Parameters[@"SexId"].Value = pSexId;
lCommand.Parameters.Add(new SqlParameter(@"TypeId", SqlDbType.Int));
lCommand.Parameters[@"TypeId"].Value = pTypeId;
lCommand.Parameters.Add(new SqlParameter(@"MaterialTypeID", SqlDbType.Int));
lCommand.Parameters[@"MaterialTypeID"].Value = pMaterialTypeId;
lCommand.Parameters.Add(new SqlParameter(@"ManufactureID", SqlDbType.Int));
lCommand.Parameters[@"ManufactureID"].Value = pManufactureId;
lCommand.Parameters.Add(new SqlParameter(@"SeasonId", SqlDbType.Int));
lCommand.Parameters[@"SeasonId"].Value = pSeasonId;
lCommand.Parameters.Add(new SqlParameter(@"cost", SqlDbType.Decimal));
lCommand.Parameters[@"cost"].Value = pCost;
lCommand.Parameters.Add(new SqlParameter(@"DescriptionText", SqlDbType.NText));
lCommand.Parameters[@"DescriptionText"].Value = pDescription;
lCommand.Parameters.Add(new SqlParameter(@"Code", SqlDbType.NVarChar, 255));
lCommand.Parameters[@"Code"].Value = pCode;
//lCommand.Parameters.Add(new SqlParameter(@"CountItem", SqlDbType.Int));
//lCommand.Parameters[@"CountItem"].Value = pcount;
lCommand.Parameters.Add(new SqlParameter(@"NewProductId", SqlDbType.Int));
lCommand.Parameters[@"NewProductId"].Direction = ParameterDirection.Output;
try
{
lConnection.Open();
lCommand.ExecuteNonQuery();
lresult = (int)lCommand.Parameters[@"NewProductId"].Value;
}
catch (Exception ex) { }
finally
{
if (lConnection.State == ConnectionState.Open)
{
lConnection.Close();
}
}
return(lresult);
}
/// <summary>
/// updated product information
/// </summary>
/// <param name="pProduxtId">product id </param>
/// <param name="pname">name</param>
/// <param name="pcode">inner code, articul</param>
/// <param name="pmanufactureId">manufacture id </param>
/// <param name="pcolorId">color id </param>
/// <param name="pmaterialId">matrerial id</param>
/// <param name="psizeId">size id</param>
/// <param name="psexId">size id</param>
/// <param name="pseasonId">season id</param>
/// <param name="typeId">current type id</param>
/// <param name="pdescriptionId">products description</param>
/// <param name="pCost">value of goods</param>
/// <returns></returns>
public static bool UpdateProduct(int pProductId, string pname, string pcode, int pmanufactureId,
int pcolorId, int pmaterialId, int psizeId, int psexId, int pseasonId, int typeId, string pdescription,
decimal pCost)
{
bool lresult = false;
SqlConnection lconnection = new SqlConnection();
lconnection.ConnectionString = ShoeStoreDB.GetConnectionString();
SqlCommand lCommand = new SqlCommand("[UpdateProductById]", lconnection);
lCommand.CommandType = System.Data.CommandType.StoredProcedure;
lCommand.Parameters.Add(new SqlParameter(@"name", SqlDbType.NVarChar, 255));
lCommand.Parameters[@"name"].Value = pname;
lCommand.Parameters.Add(new SqlParameter(@"Code", SqlDbType.NVarChar, 255));
lCommand.Parameters[@"Code"].Value = pcode;
lCommand.Parameters.Add(new SqlParameter(@"manufactureID", SqlDbType.Int));
lCommand.Parameters[@"manufactureID"].Value = pmanufactureId;
lCommand.Parameters.Add(new SqlParameter(@"ColorID", SqlDbType.Int));
lCommand.Parameters[@"ColorID"].Value = pcolorId;
lCommand.Parameters.Add(new SqlParameter(@"MaterialID", SqlDbType.Int));
lCommand.Parameters[@"MaterialID"].Value = pmaterialId;
lCommand.Parameters.Add(new SqlParameter(@"SizeID", SqlDbType.Int));
lCommand.Parameters[@"SizeID"].Value = psizeId;
lCommand.Parameters.Add(new SqlParameter(@"SexID", SqlDbType.Int));
lCommand.Parameters[@"SexID"].Value = psexId;
lCommand.Parameters.Add(new SqlParameter(@"SesonID", SqlDbType.Int));
lCommand.Parameters[@"SesonID"].Value = pseasonId;
lCommand.Parameters.Add(new SqlParameter(@"TypeID", SqlDbType.Int));
lCommand.Parameters[@"TypeID"].Value = typeId;
lCommand.Parameters.Add(new SqlParameter(@"Desctiption", SqlDbType.NText));
lCommand.Parameters[@"Desctiption"].Value = pdescription;
lCommand.Parameters.Add(new SqlParameter(@"Cost", SqlDbType.Decimal));
lCommand.Parameters[@"Cost"].Value = pCost;
//lCommand.Parameters.Add(new SqlParameter(@"Count", SqlDbType.Int));
//lCommand.Parameters[@"Count"].Value = typeId;
lCommand.Parameters.Add(new SqlParameter(@"ProductID", SqlDbType.Int));
lCommand.Parameters[@"ProductID"].Value = pProductId;
try
{
lconnection.Open();
lCommand.ExecuteNonQuery();
lresult = true;
}
catch (Exception ex) { }
finally
{
if (lconnection.State == ConnectionState.Open)
{
lconnection.Close();
}
}
return(lresult);
}