MSSQL.Command.Handler 1.4.5

License file
MIT License

Copyright (c) 2025 MSSQL.Command.Handler

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

Resources
---------
- [Official Documentation](https://mssqlcommandhandler.mssqlcommandhandler.com/documentation)
- [GitHub Repository](https://github.com/mssqlcommandhandler/MSSQL.Command.Handler)
- [Issue Tracker](https://github.com/mssqlcommandhandler/MSSQL.Command.Handler/issues)
- [Contribution Guidelines](https://github.com/mssqlcommandhandler/MSSQL.Command.Handler/blob/main/CONTRIBUTING.md)
- [Changelog](https://github.com/mssqlcommandhandler/MSSQL.Command.Handler/blob/main/CHANGELOG.md)
- [Releases](https://github.com/mssqlcommandhandler/MSSQL.Command.Handler/releases)
- [Support](https://mssqlcommandhandler.mssqlcommandhandler.com/support)

















using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApp1
{
    public class Partner
    {
		public int Id { get; set; }
		public string Name { get; set; }
		public string Director { get; set; }
		public string DirectorPhone { get; set; }
		public int Rating { get; set; }
		public string Type { get; set; }
		public string Address { get; set; }
		public string Email { get; set; }
		public int DiscountPercentage { get; set; }
		public string INN { get; set; }
	}
}



--------------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
	public partial class UserControl1 : UserControl
	{
		public Partner Partner { get; private set; }
		public event EventHandler<Partner> PartnerSelected;
		public event EventHandler<Partner> PartnerHistoryRequested;

		private string connectionString;
		public UserControl1(Partner partner, string connectionString)
		{
			InitializeComponent();
			this.connectionString = connectionString;
			Partner = partner;
			// Привязка данных к контролам
			nameLabel.Text = partner.Type + " | " + partner.Name;
			directorLabel.Text = $"Директор: {partner.Director}";
			phoneLabel.Text = $"+7 {partner.DirectorPhone}";
			ratingLabel.Text = $"Рейтинг: {partner.Rating}";
			partner.DiscountPercentage = CalculateDiscount(partner.Id);
			discountLabel.Text = $"{partner.DiscountPercentage}%";
			
		}
		private int CalculateDiscount(int partnerId)
		{
			decimal totalSales = GetTotalSalesByPartner(partnerId);

			if (totalSales < 10_000) return 0;
			else if (totalSales < 50_000) return 5;
			else if (totalSales < 300_000) return 10;
			else return 15;
		}

		private decimal GetTotalSalesByPartner(int partnerId)
		{
			decimal total = 0;

			try
			{
				using (var conn = new SqlConnection(connectionString))
				{
					conn.Open();
					string query = "SELECT SUM(PRODUCT_COUNT) FROM SALE WHERE PARTNER = @partnerId";
					using (var cmd = new SqlCommand(query, conn))
					{
						cmd.Parameters.AddWithValue("@partnerId", partnerId);
						object result = cmd.ExecuteScalar();
						if (result != DBNull.Value && decimal.TryParse(result.ToString(), out var value))
							total = value;
					}
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show($"Ошибка при расчете продаж: {ex.Message}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}

			return total;
		}

		private void btnEdit_Click(object sender, EventArgs e)
		{
			PartnerSelected?.Invoke(this, Partner);
		}

		private void btnHistory_Click(object sender, EventArgs e)
		{
			PartnerHistoryRequested?.Invoke(this, Partner);
		}
	}
}

--------------------


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        private string connectionString = @"Data Source=SOUEIWINHOMEPC\SQLEXPRESS;Initial Catalog=SonyaDemo;Integrated Security=True;Encrypt=False";

        public Form1()
        {
            InitializeComponent();
            LoadPartners();
        }

        private void LoadPartners()
        {
			flowLayoutPanel1.Controls.Clear();

			string query = @"SELECT 
                    p.PARTNER_ID, 
                    p.PARTNER_NAME, 
                    p.DIRECTOR, 
                    p.DIRECTOR_PHONE, 
                    p.PARTNER_RATING, 
                    p.PARTNER_LEGAL_ADDRESS, 
                    p.DIRECTOR_MAIL, 
                    p.PARTNER_INN, 
                    pt.PARTNER_TYPE_NAME
                 FROM PARTNER p
                 JOIN PARTNER_TYPE pt ON p.PARTNER_TYPE = pt.PARTNER_TYPE_ID";

			try
			{
				using (var conn = new SqlConnection(connectionString))
				{
					conn.Open();
					using (var cmd = new SqlCommand(query, conn))
					{
						using (var reader = cmd.ExecuteReader())
						{
							while (reader.Read())
							{
								var partner = new Partner
								{
									Id = Convert.ToInt32(reader["PARTNER_ID"]),
									Name = reader["PARTNER_NAME"].ToString(),
									Director = reader["DIRECTOR"].ToString(),
									DirectorPhone = reader["DIRECTOR_PHONE"].ToString(),
									Rating = Convert.ToInt32(reader["PARTNER_RATING"]),
									Address = reader["PARTNER_LEGAL_ADDRESS"].ToString(),
									Email = reader["DIRECTOR_MAIL"].ToString(),
									INN = reader["PARTNER_INN"].ToString(),
									Type = reader["PARTNER_TYPE_NAME"].ToString()
								};

								var card = new UserControl1(partner, connectionString);
								card.PartnerSelected += (sender, p) =>
								{
									var form = new Form2(connectionString, p);
									if (form.ShowDialog() == DialogResult.OK)
										LoadPartners();
								};
								card.PartnerHistoryRequested += (sender, p) =>
								{
									var historyForm = new Form3(connectionString, p.Id, p.Name);
									historyForm.Show();
									
								};

								flowLayoutPanel1.Controls.Add(card);
							}
						}
					}
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show("Ошибка загрузки данных: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}


        
		private void btnAdd_Click(object sender, EventArgs e)
		{
			var form = new Form2(connectionString);
			if (form.ShowDialog() == DialogResult.OK)
				LoadPartners();
		}

		private void btnCalculateMaterial_Click(object sender, EventArgs e)
		{
			var calcForm = new Form4();
			calcForm.Show();
		}
	}
}

-----------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
using System.Xml.Linq;

namespace WindowsFormsApp1
{
    public partial class Form2 : Form
    {
        private string connectionString;
        private Partner currentPartner;
        private bool isEditMode = false;

        public Form2(string connString, Partner partner = null)
        {
			InitializeComponent();
			connectionString = connString;
			currentPartner = partner;
			isEditMode = partner != null;
			LoadPartnerTypes();
			LoadData();
		}
		private void LoadData()
		{
			if (currentPartner != null)
			{
				tbName.Text = currentPartner.Name;
				tbDirector.Text = currentPartner.Director;
				tbPhone.Text = currentPartner.DirectorPhone;
				tbMail.Text = currentPartner.Email;
				tbAddress.Text = currentPartner.Address;
				tbRating.Text = currentPartner.Rating.ToString();
				tbINN.Text = currentPartner.INN;
				cmbType.SelectedItem = currentPartner.Type;
			}
		}


		private void LoadPartnerTypes()
		{
			try
			{
				using (var conn = new SqlConnection(connectionString))
				{
					conn.Open();
					var cmd = new SqlCommand("SELECT PARTNER_TYPE_NAME FROM PARTNER_TYPE", conn);
					var reader = cmd.ExecuteReader();
					while (reader.Read())
						cmbType.Items.Add(reader["PARTNER_TYPE_NAME"].ToString());
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show("Ошибка загрузки типов: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}
		private bool ValidateInputs()
        {
			if (string.IsNullOrWhiteSpace(tbName.Text))
			{
				MessageBox.Show("Введите наименование.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
				return false;
			}
			if (cmbType.SelectedItem == null)
			{
				MessageBox.Show("Выберите тип партнера.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
				return false;
			}
			if (!int.TryParse(tbRating.Text, out int rating) || rating < 0)
			{
				MessageBox.Show("Рейтинг должен быть неотрицательным числом.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
				return false;
			}
			return true;
		}

		private void btnCancel_Click(object sender, EventArgs e)
		{
			Close();
		}

		private void btnSave_Click(object sender, EventArgs e)
		{
			if (!ValidateInputs()) return;

			try
			{
				using (var conn = new SqlConnection(connectionString))
				{
					conn.Open();
					string query;
					SqlCommand cmd;
					if (isEditMode)
					{
						query = @"UPDATE PARTNER SET 
                PARTNER_NAME = @name, 
                DIRECTOR = @director, 
                DIRECTOR_PHONE = @phone, 
                PARTNER_RATING = @rating, 
                PARTNER_TYPE = (SELECT PARTNER_TYPE_ID FROM PARTNER_TYPE WHERE PARTNER_TYPE_NAME = @type),
                PARTNER_LEGAL_ADDRESS = @address, 
                DIRECTOR_MAIL = @email,
                PARTNER_INN = @inn
              WHERE PARTNER_ID = @id";
						cmd = new SqlCommand(query, conn);
						cmd.Parameters.AddWithValue("@id", currentPartner.Id);
					}
					else
					{
						query = @"INSERT INTO PARTNER 
                (PARTNER_NAME, DIRECTOR, DIRECTOR_PHONE, PARTNER_RATING,
                 PARTNER_TYPE, PARTNER_LEGAL_ADDRESS, DIRECTOR_MAIL, PARTNER_INN)
              VALUES 
                (@name, @director, @phone, @rating,
                 (SELECT PARTNER_TYPE_ID FROM PARTNER_TYPE WHERE PARTNER_TYPE_NAME = @type),
                 @address, @email, @inn)";
						cmd = new SqlCommand(query, conn);
					}

					cmd.Parameters.AddWithValue("@name", tbName.Text);
					cmd.Parameters.AddWithValue("@director", tbDirector.Text);
					cmd.Parameters.AddWithValue("@phone", tbPhone.Text);
					cmd.Parameters.AddWithValue("@rating", int.Parse(tbRating.Text));
					cmd.Parameters.AddWithValue("@type", cmbType.SelectedItem.ToString());
					cmd.Parameters.AddWithValue("@address", tbAddress.Text);
					cmd.Parameters.AddWithValue("@email", tbMail.Text);
					cmd.Parameters.AddWithValue("@inn", tbINN.Text);

					cmd.ExecuteNonQuery();
					DialogResult = DialogResult.OK;
					Close();
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show("Ошибка сохранения: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}
	}
}

--------------------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public partial class Form3 : Form
    {
        private string connectionString;
        private int partnerId;
		private string partnerName;
		public Form3(string connString, int partnerId, string partnerName)
        {
			InitializeComponent();
			this.connectionString = connString;
			this.partnerId = partnerId;
			this.partnerName = partnerName;
			label2.Text = $"{partnerName}";
			Load += (s, e) => LoadSalesHistory();
		}
        private void LoadSalesHistory()
        {
			try
			{
				using (var conn = new SqlConnection(connectionString))
				{
					conn.Open();

					string query = @"
                        SELECT 
                            p.PRODUCT_NAME AS [Наименование продукции],
                            s.PRODUCT_COUNT AS [Количество],
                            s.SALE_DATE AS [Дата продажи]
                        FROM SALE s
                        JOIN PRODUCT p ON s.PRODUCT_ARTICLE = p.PRODUCT_ARTICLE
                        WHERE s.PARTNER = @partnerId";

					using (var cmd = new SqlCommand(query, conn))
					{
						cmd.Parameters.AddWithValue("@partnerId", partnerId);

						using (var adapter = new SqlDataAdapter(cmd))
						{
							var table = new DataTable();
							adapter.Fill(table);
							dataGridView1.DataSource = table;
						}
					}
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show("Ошибка загрузки истории: " + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}

    }
}

-------------------------------------------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
	public partial class Form4 : Form
	{
        private static string connectionString = @"Data Source=SOUEIWINHOMEPC\SQLEXPRESS;Initial Catalog=SonyaDemo;Integrated Security=True;Encrypt=False";

		public Form4()
		{
			InitializeComponent();
			LoadProductTypes();
			LoadMaterialTypes();
		}
		private void LoadProductTypes()
		{
			try
			{
				using (var conn = new SqlConnection(connectionString))
				{
					conn.Open();
					var cmd = new SqlCommand("SELECT PRODUCT_TYPE_ID, PRODUCT_TYPE_NAME FROM PRODUCT_TYPE", conn);
					var reader = cmd.ExecuteReader();
					while (reader.Read())
					{
						cmbProductType.Items.Add(new ProductTypeInfo
						{
							Id = Convert.ToInt32(reader["PRODUCT_TYPE_ID"]),
							Name = reader["PRODUCT_TYPE_NAME"].ToString()
						});
					}
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show($"Ошибка загрузки типов продукции: {ex.Message}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}
		private void LoadMaterialTypes()
		{
			try
			{
				using (var conn = new SqlConnection(connectionString))
				{
					conn.Open();
					var cmd = new SqlCommand("SELECT MATERIAL_TYPE_ID, MATERIAL_TYPE_NAME FROM MATERIAL_TYPE", conn);
					var reader = cmd.ExecuteReader();
					while (reader.Read())
					{
						cmbMaterialType.Items.Add(new MaterialTypeInfo
						{
							Id = Convert.ToInt32(reader["MATERIAL_TYPE_ID"]),
							Name = reader["MATERIAL_TYPE_NAME"].ToString()
						});
					}
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show($"Ошибка загрузки типов материалов: {ex.Message}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}

		private void btnCalculate_Click(object sender, EventArgs e)
		{
			try
			{
				if (cmbProductType.SelectedItem == null || cmbMaterialType.SelectedItem == null ||
					!int.TryParse(tbProductCount.Text, out int productCount) ||
					!decimal.TryParse(tbParam1.Text, out decimal param1) ||
					!decimal.TryParse(tbParam2.Text, out decimal param2))
				{
					MessageBox.Show("Введите корректные данные.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
					return;
				}

				var productType = (ProductTypeInfo)cmbProductType.SelectedItem;
				var materialType = (MaterialTypeInfo)cmbMaterialType.SelectedItem;

				int result = CalculateMaterial(productType.Id, materialType.Id, productCount, param1, param2);

				if (result == -1)
				{
					MessageBox.Show("Некорректные данные или типы.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
				}
				else
				{
					lblResult.Text = $"{result} шт.";
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show($"Ошибка расчета: {ex.Message}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}
		public static int CalculateMaterial(int productTypeId, int materialTypeId, int productCount, decimal param1, decimal param2)
		{
			try
			{
				using (var conn = new SqlConnection(connectionString))
				{
					conn.Open();

					// Получаем коэффициент типа продукции
					string queryCoeff = "SELECT PRODUCT_TYPE_COEFFICIENT FROM PRODUCT_TYPE WHERE PRODUCT_TYPE_ID = @id";
					var cmdCoeff = new SqlCommand(queryCoeff, conn);
					cmdCoeff.Parameters.AddWithValue("@id", productTypeId);
					var resultCoeff = cmdCoeff.ExecuteScalar();
					if (resultCoeff == null || !(resultCoeff is decimal coeff))
						return -1;

					// Получаем процент брака материала
					string queryDefect = "SELECT PERCENTAGE_DEFECTIVE_MATERIAL FROM MATERIAL_TYPE WHERE MATERIAL_TYPE_ID = @id";
					var cmdDefect = new SqlCommand(queryDefect, conn);
					cmdDefect.Parameters.AddWithValue("@id", materialTypeId);
					var resultDefect = cmdDefect.ExecuteScalar();
					if (resultDefect == null || !(resultDefect is decimal defect))
						return -1;

					// Проверка положительности параметров
					if (param1 <= 0 || param2 <= 0 || productCount <= 0)
						return -1;

					// Расчет
					decimal baseMaterial = param1 * param2 * coeff * productCount;
					decimal materialWithDefect = baseMaterial * (1 + defect);
					return (int)Math.Ceiling(materialWithDefect);
				}
			}
			catch
			{
				return -1;
			}
		}
		public class ProductTypeInfo
		{
			public int Id { get; set; }
			public string Name { get; set; }
			public override string ToString() => Name;
		}

		public class MaterialTypeInfo
		{
			public int Id { get; set; }
			public string Name { get; set; }
			public override string ToString() => Name;
		}
	}


}
MSSQL.Command.Handler 1.4.5 License Info

MSSQL.Command.Handler 1.4.5
