/// <summary>
/// The Method that get called on clicking Export To Excel image button
/// </summary>
/// <param name="sender">Event Source</param>
/// <param name="eventArgument">Event Data</param>
protected void ImageButtonXLS_Click(object sender, ImageClickEventArgs eventArgument)
{
actionButton = "Export";
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=Products.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter sw = new System.IO.StringWriter(CultureInfo.InvariantCulture);
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(sw);
GetProducts();
TableProducts.BorderWidth = 1;
TableProducts.RenderControl(htmlWrite);
Response.Write(sw.ToString());
Response.End();
}
//загрузка данных записей из таблиц бд при открытии формы
private void ProductsAdmin_Load(object sender, EventArgs e)
{
// подключение к базе sql сервер
using (SqlConnection conn = new SqlConnection(@"Data Source=DESKTOP-9IUFQMM\SQLEXPRESS;Initial Catalog=PateticoPractic;Integrated Security=True"))
{
string query = "SELECT p.id_product, p.product_name, p.description, p.price, p.weight, p.id_parameter, pr.parameter FROM Products AS p INNER JOIN Parameters as pr ON pr.id_parameter = p.id_parameter ";
try
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
SqlDataReader reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
conn.Close();
TableProducts.DataSource = dt;
TableProducts.Update();
TableProducts.Columns[0].Width = 50;
TableProducts.Columns[1].Width = 140;
TableProducts.Columns[2].Width = 580;
TableProducts.Columns[3].Width = 60;
TableProducts.Columns[4].Width = 60;
TableProducts.Columns[6].Width = 90;
TableProducts.Columns[0].HeaderText = "Номер товара";
TableProducts.Columns[1].HeaderText = "Название товара";
TableProducts.Columns[2].HeaderText = "Описание товара";
TableProducts.Columns[3].HeaderText = "Цена товара";
TableProducts.Columns[4].HeaderText = "Вес (г)";
TableProducts.Columns[6].HeaderText = "Категория товара";
TableProducts.Columns[5].Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
}
//осуществление поиска в списке по наименованию товара
private void roundBtnSearch_Click(object sender, EventArgs e)
{
// подключение к базе sql сервер
using (SqlConnection conn = new SqlConnection(@"Data Source=DESKTOP-9IUFQMM\SQLEXPRESS;Initial Catalog=PateticoPractic;Integrated Security=True"))
{
try
{
conn.Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT p.id_product, p.product_name, p.description, p.price, p.weight, pr.parameter FROM Products AS p INNER JOIN Parameters as pr ON pr.id_parameter = p.id_parameter WHERE p.product_name like '%" + txtForName.Text + "%'", conn);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
TableProducts.DataSource = dataTable;
TableProducts.Refresh();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
}
public static IEnumerable <TableProducts> GetProducts()
{
Stream xmlStream = typeof(TableFeatures).GetTypeInfo().Assembly.GetManifestResourceStream("SampleBrowser.Samples.PDF.Assets.Products.xml");
XmlReader reader = XmlReader.Create(xmlStream);
List <TableProducts> collection = new List <TableProducts>();
while (reader.Read())
{
if (reader.IsStartElement())
{
switch (reader.Name)
{
case "Products":
TableProducts product = new TableProducts();
while (reader.Read())
{
if (reader.IsStartElement())
{
switch (reader.Name)
{
case "Image1":
reader.Read();
product.Image1 = reader.Value;
break;
case "Description1":
reader.Read();
product.Description1 = reader.Value;
break;
case "Image2":
reader.Read();
product.Image2 = reader.Value;
break;
case "Description2":
reader.Read();
product.Description2 = reader.Value;
break;
case "Description3":
reader.Read();
product.Description3 = reader.Value;
break;
case "Image3":
reader.Read();
product.Image3 = reader.Value;
break;
}
}
else if (reader.NodeType == XmlNodeType.EndElement && reader.Name == "Products")
{
collection.Add(product);
break;
}
}
break;
}
}
}
return(collection.AsEnumerable());
}
//удаление данных выбранной записи из таблицы бд
private void roundBtnDelete_Click(object sender, EventArgs e)
{
if (TableProducts.SelectedRows.Count == 1)
{
string message = $"Вы действительно хотите удалить данную запись?\nОтменить это действие будет невозможно!";
string caption = "Удаление записи";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
result = MessageBox.Show(message, caption, buttons);
if (result == DialogResult.Yes)
{
// подключение к базе sql сервер
using (SqlConnection conn = new SqlConnection(@"Data Source=DESKTOP-9IUFQMM\SQLEXPRESS;Initial Catalog=PateticoPractic;Integrated Security=True"))
{
try
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM [Products] WHERE id_product = @ID";
cmd.Parameters.AddWithValue(@"ID", ID);
cmd.ExecuteScalar();
TableProducts.Refresh();
MessageBox.Show("Запись удалена!");
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT p.id_product, p.product_name, p.description, p.price, p.weight, p.id_parameter, pr.parameter FROM Products AS p INNER JOIN Parameters as pr ON pr.id_parameter = p.id_parameter", conn);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
TableProducts.DataSource = dataTable;
TableProducts.Update();
TableProducts.Columns[0].Width = 50;
TableProducts.Columns[1].Width = 100;
TableProducts.Columns[2].Width = 400;
TableProducts.Columns[3].Width = 70;
TableProducts.Columns[4].Width = 50;
TableProducts.Columns[6].Width = 70;
TableProducts.Columns[0].HeaderText = "Номер товара";
TableProducts.Columns[1].HeaderText = "Название товара";
TableProducts.Columns[2].HeaderText = "Описание товара";
TableProducts.Columns[3].HeaderText = "Цена товара";
TableProducts.Columns[4].HeaderText = "Вес (г)";
TableProducts.Columns[6].HeaderText = "Категория товара";
TableProducts.Columns[5].Visible = false;
TableProducts.Refresh();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
}
}
else
{
MessageBox.Show("Запись не удалена!");
}
}
else
{
if (TableProducts.SelectedRows.Count == 0)
{
MessageBox.Show("Вы не выбрали строку для удаления!");
}
else
{
MessageBox.Show("Выберите ТОЛЬКО ОДНУ строку для удаления!");
}
}
}
