КАТЕГОРИИ: Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748) |
Исходный текст программы
// Main.cs using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using MySql.Data.MySqlClient; using Excel = Microsoft.Office.Interop.Excel; namespace Sklad { public partial class Main: Form { public Main() { InitializeComponent(); } public string Connect = "Database=as_product;Data Source=localhost;User=root;Password=pass;charset=cp1251;"; public int ID_red, N; public string FIO, Sklad; public void ShowData() { string CommandText = "Select Count(*) from sklad_tov st, product p where st.id_prod=p.id_prod and data_skl between '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' and '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' and name_prod like '%" + textBox1.Text + "%'";
MySqlConnection myConnection = new MySqlConnection(Connect); MySqlCommand myCommand = new MySqlCommand(CommandText, myConnection); myConnection.Open(); //Устанавливаем соединение с базой данных.
Int32 kol = Convert.ToInt32(myCommand.ExecuteScalar().ToString());; if (kol > 0) dataGridView1.RowCount = kol; else dataGridView1.RowCount = 1; for (int k = 0; k < kol; k++) dataGridView1[0, k].Value = ""; string TextCommand = "Select id_st, st.id_skl, name_kat,name_prod, Concat(fam, concat(' ', concat(imya,concat(' ',otch)))),kol_skl, data_skl,ed_izm "; TextCommand += " from Product p, kategoriya k, sotrudniki s, sklad_tov st where p.id_kat=k.id_kat and s.id_sotr=st.id_sotr and st.id_prod=p.id_prod "; TextCommand += " and data_skl between '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' and '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' "; TextCommand += " and name_prod like '%" + textBox1.Text + "%' order by name_kat, name_prod, data_skl"; MySqlCommand comm1 = new MySqlCommand(TextCommand, myConnection); MySqlDataReader myReader = comm1.ExecuteReader();
int i = 0; while (myReader.Read()) { for (int j = 0; j < 8; j++) { dataGridView1[j, i].Value = myReader.GetString(j); } i++; } myConnection.Close(); //Обязательно закрываем соединение! } private void Main_Load(object sender, EventArgs e) { dateTimePicker2.Value = DateTime.Today; dateTimePicker1.Value = DateTime.Today.AddDays(-30); ShowData(); if (FIO!= "admin") { сотрудникиToolStripMenuItem.Visible = false; редактироватьПоставкуToolStripMenuItem.Visible = false; удалитьПоставкуToolStripMenuItem.Visible = false; } } private void закзчикиToolStripMenuItem_Click(object sender, EventArgs e) { Zak frm = new Zak();
frm.ShowDialog(); } private void справочникПоставкиToolStripMenuItem_Click(object sender, EventArgs e) { Post frm = new Post(); frm.FIO = FIO; frm.Sklad = Sklad; frm.ShowDialog(); }
private void товарыToolStripMenuItem_Click(object sender, EventArgs e) { Prod frm = new Prod(); frm.ShowDialog(); } private void складыToolStripMenuItem_Click(object sender, EventArgs e) { Sklad frm = new Sklad(); frm.ShowDialog(); } private void сотрудникиToolStripMenuItem_Click(object sender, EventArgs e) { Sotr frm = new Sotr(); frm.ShowDialog(); } private void dateTimePicker1_ValueChanged(object sender, EventArgs e) { ShowData(); } private void dateTimePicker2_ValueChanged(object sender, EventArgs e) { ShowData(); } private void textBox1_TextChanged(object sender, EventArgs e) { ShowData(); } private void удалитьПоставкуToolStripMenuItem_Click(object sender, EventArgs e) { MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string DelId = Convert.ToString(dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value); string TextCommand = "Delete from sklad_tov where id_st =" + DelId; MySqlCommand myCommand = new MySqlCommand(TextCommand, myConnection); myCommand.ExecuteNonQuery(); myConnection.Close(); //Обязательно закрываем соединение! ShowData(); MessageBox.Show("Данные удалены", "Удаление записи", MessageBoxButtons.OK, MessageBoxIcon.Information); }
private void добавитьПоставкуToolStripMenuItem_Click(object sender, EventArgs e) { MainAdd frm = new MainAdd(); frm.button1.Visible = true; frm.button2.Visible = false; frm.comboBox3.Enabled = true; frm.comboBox1.Enabled = true; MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string CommandText = "SELECT min(C.ID_st+1) FROM sklad_tov C LEFT JOIN sklad_tov b ON C.ID_st+1 = b.ID_st where b.ID_st is null"; MySqlCommand myCommand = new MySqlCommand(CommandText, myConnection); frm.textBox1.Text = myCommand.ExecuteScalar().ToString(); myConnection.Close(); //Обязательно закрываем соединение! if (FIO!= "admin") { frm.comboBox3.Text = FIO; frm.comboBox3.Enabled = false; frm.comboBox4.Text = Sklad; frm.comboBox4.Enabled = false; frm.comboBox4_SelectedIndexChanged(sender, e); } frm.ShowDialog(); } private void редактироватьПоставкуToolStripMenuItem_Click(object sender, EventArgs e) { MainAdd frm = new MainAdd(); frm.button1.Visible = false; frm.button2.Visible = true; frm.ID_red = Convert.ToInt32(dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value); frm.textBox1.Text = Convert.ToString(dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value); frm.textBox2.Text = Convert.ToString(dataGridView1[5, dataGridView1.CurrentCell.RowIndex].Value); frm.textBox3.Text = Convert.ToString(dataGridView1[7, dataGridView1.CurrentCell.RowIndex].Value); frm.comboBox1.Enabled = false; frm.comboBox1.Text = Convert.ToString(dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value);
frm.comboBox2.Text = Convert.ToString(dataGridView1[3, dataGridView1.CurrentCell.RowIndex].Value); frm.comboBox3.Text = Convert.ToString(dataGridView1[4, dataGridView1.CurrentCell.RowIndex].Value); frm.comboBox3.Enabled = false; frm.comboBox4.Text = Convert.ToString(dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value); dateTimePicker1.Value = Convert.ToDateTime(dataGridView1[6, dataGridView1.CurrentCell.RowIndex].Value); frm.ShowDialog(); } private void Main_Activated(object sender, EventArgs e) { ShowData(); } private Excel.Application excel; private Excel.Worksheet excelworksheet; private Excel.Sheets excelsheets; Excel.Workbook book; private Excel.Range excelcells; private void отчетСписокСотрудниковToolStripMenuItem_Click(object sender, EventArgs e) { try { //работа с Excel excel = new Excel.Application(); } catch { MessageBox.Show("Отсутствует MS.Excel"); this.Close(); } excel.SheetsInNewWorkbook = 1; excel.Workbooks.Add(Type.Missing); book = excel.Workbooks[1]; excelworksheet = book.Worksheets[1]; excelworksheet.Name = "Список сотрудников"; excelcells = excelworksheet.get_Range("A1", "G1"); excelcells.Merge(Type.Missing); excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; excelcells.Font.FontStyle = 2; excelcells.EntireRow.Font.Size = 16; excelcells.Value2 = "Список сотрудников склада на " + DateTime.Today.ToString("dd-MM-yyyy"); excelworksheet.get_Range("A3", "A3").ColumnWidth = 15; excelworksheet.get_Range("B3", "B3").ColumnWidth = 20; excelworksheet.get_Range("C3", "C3").ColumnWidth = 15; excelworksheet.get_Range("D3", "D3").ColumnWidth = 15; excelworksheet.get_Range("E3", "E3").ColumnWidth = 20; excelworksheet.get_Range("F3", "F3").ColumnWidth = 20; excelworksheet.get_Range("G3", "G3").ColumnWidth = 15; excelcells = excelworksheet.get_Range("A3", Type.Missing); excelcells.Value2 = "Таб. номер"; excelcells = excelworksheet.get_Range("B3", Type.Missing); excelcells.Value2 = "Фамилия"; excelcells = excelworksheet.get_Range("C3", Type.Missing); excelcells.Value2 = "Имя"; excelcells = excelworksheet.get_Range("D3", Type.Missing); excelcells.Value2 = "Отчество"; excelcells = excelworksheet.get_Range("E3", Type.Missing); excelcells.Value2 = "Должность"; excelcells = excelworksheet.get_Range("F3", Type.Missing); excelcells.Value2 = "Разряд"; excelcells = excelworksheet.get_Range("G3", Type.Missing); excelcells.Value2 = "Склад"; excelcells = excelworksheet.get_Range("A3", "G3"); excelcells.EntireRow.Font.Bold = true; MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string TextCommand = "Select id_sotr, fam, imya, otch, dolzn, razr, id_skl "; TextCommand += " from sotrudniki "; TextCommand += " order by id_skl, fam, imya, otch, dolzn"; MySqlCommand comm1 = new MySqlCommand(TextCommand, myConnection); MySqlDataReader myReader = comm1.ExecuteReader(); int i = 0; while (myReader.Read()) { excelcells = excelworksheet.get_Range("A" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(0); excelcells = excelworksheet.get_Range("B" + (i + 4).ToString(), Type.Missing);
excelcells.Value2 = myReader.GetString(1); excelcells = excelworksheet.get_Range("C" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(2); excelcells = excelworksheet.get_Range("D" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(3); excelcells = excelworksheet.get_Range("E" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(4); excelcells = excelworksheet.get_Range("F" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(5); excelcells = excelworksheet.get_Range("G" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(6); i++; } myConnection.Close(); //Обязательно закрываем соединение! //заполнение данными excelcells = excelworksheet.get_Range("A3", "G" + (i + 3).ToString()); excelcells.Borders.ColorIndex = 5;
excel.Visible = true; } private void отчетСписокЗаказчиковToolStripMenuItem_Click(object sender, EventArgs e) { try { //работа с Excel excel = new Excel.Application(); } catch { MessageBox.Show("Отсутствует MS.Excel"); this.Close(); } excel.SheetsInNewWorkbook = 1; excel.Workbooks.Add(Type.Missing); book = excel.Workbooks[1]; excelworksheet = book.Worksheets[1]; excelworksheet.Name = "Список заказчиков"; excelcells = excelworksheet.get_Range("A1", "E1"); excelcells.Merge(Type.Missing); excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; excelcells.Font.FontStyle = 2; excelcells.EntireRow.Font.Size = 16; excelcells.Value2 = "Список заказчиков на " + DateTime.Today.ToString("dd-MM-yyyy"); excelworksheet.get_Range("A3", "A3").ColumnWidth = 15; excelworksheet.get_Range("B3", "B3").ColumnWidth = 20; excelworksheet.get_Range("C3", "C3").ColumnWidth = 15; excelworksheet.get_Range("D3", "D3").ColumnWidth = 15; excelworksheet.get_Range("E3", "E3").ColumnWidth = 30; excelcells = excelworksheet.get_Range("A3", Type.Missing); excelcells.Value2 = "Страна"; excelcells = excelworksheet.get_Range("B3", Type.Missing); excelcells.Value2 = "Заказчик"; excelcells = excelworksheet.get_Range("C3", Type.Missing); excelcells.Value2 = "Адрес"; excelcells = excelworksheet.get_Range("D3", Type.Missing); excelcells.Value2 = "Телефон"; excelcells = excelworksheet.get_Range("E3", Type.Missing); excelcells.Value2 = "Контакт. лицо"; excelcells = excelworksheet.get_Range("A3", "E3"); excelcells.EntireRow.Font.Bold = true; MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string TextCommand = "Select name_str, name_zak, adres_zak, tel_zak, kontact "; TextCommand += " from zakazchik, strana where strana.id_str=zakazchik.id_str "; TextCommand += " order by name_str, name_zak";
MySqlCommand comm1 = new MySqlCommand(TextCommand, myConnection); MySqlDataReader myReader = comm1.ExecuteReader(); int i = 0; while (myReader.Read()) { excelcells = excelworksheet.get_Range("A" + (i + 4).ToString(), Type.Missing);
excelcells.Value2 = myReader.GetString(0); excelcells = excelworksheet.get_Range("B" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(1); excelcells = excelworksheet.get_Range("C" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(2); excelcells = excelworksheet.get_Range("D" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(3); excelcells = excelworksheet.get_Range("E" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(4); i++; }
myConnection.Close(); //Обязательно закрываем соединение! //заполнение данными excelcells = excelworksheet.get_Range("A3", "E" + (i + 3).ToString()); excelcells.Borders.ColorIndex = 5;
excel.Visible = true; } private void отчетСписокПроизводимойПродукцииToolStripMenuItem_Click(object sender, EventArgs e) { try { //работа с Excel excel = new Excel.Application(); } catch { MessageBox.Show("Отсутствует MS.Excel"); this.Close(); } excel.SheetsInNewWorkbook = 1; excel.Workbooks.Add(Type.Missing); book = excel.Workbooks[1]; excelworksheet = book.Worksheets[1]; excelworksheet.Name = "Список продукции"; excelcells = excelworksheet.get_Range("A1", "C1"); excelcells.Merge(Type.Missing); excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; excelcells.Font.FontStyle = 2; excelcells.EntireRow.Font.Size = 16; excelcells.Value2 = "Список производимой продукции на " + DateTime.Today.ToString("dd-MM-yyyy"); excelworksheet.get_Range("A3", "A3").ColumnWidth = 30; excelworksheet.get_Range("B3", "B3").ColumnWidth = 20; excelworksheet.get_Range("C3", "C3").ColumnWidth = 30; excelcells = excelworksheet.get_Range("A3", Type.Missing); excelcells.Value2 = "Категория"; excelcells = excelworksheet.get_Range("B3", Type.Missing); excelcells.Value2 = "Номер"; excelcells = excelworksheet.get_Range("C3", Type.Missing); excelcells.Value2 = "Продукция"; excelcells = excelworksheet.get_Range("A3", "C3"); excelcells.EntireRow.Font.Bold = true; MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string TextCommand = "Select name_kat, id_prod, name_prod "; TextCommand += " from kategoriya, product where kategoriya.id_kat=product.id_kat "; TextCommand += " order by name_kat, name_prod";
MySqlCommand comm1 = new MySqlCommand(TextCommand, myConnection); MySqlDataReader myReader = comm1.ExecuteReader(); int i = 0; while (myReader.Read()) { excelcells = excelworksheet.get_Range("A" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(0); excelcells = excelworksheet.get_Range("B" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(1); excelcells = excelworksheet.get_Range("C" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = myReader.GetString(2); i++; } myConnection.Close(); //Обязательно закрываем соединение! //заполнение данными excelcells = excelworksheet.get_Range("A3", "C" + (i + 3).ToString()); excelcells.Borders.ColorIndex = 5;
excel.Visible = true; } private void отчетТоварыНаСкладеToolStripMenuItem_Click(object sender, EventArgs e) { try { //работа с Excel excel = new Excel.Application(); } catch { MessageBox.Show("Отсутствует MS.Excel"); this.Close(); } excel.SheetsInNewWorkbook = 1; excel.Workbooks.Add(Type.Missing); book = excel.Workbooks[1]; excelworksheet = book.Worksheets[1]; excelworksheet.Name = "Наличие товаров"; excelcells = excelworksheet.get_Range("A1", "E1"); excelcells.Merge(Type.Missing); excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; excelcells.Font.FontStyle = 2; excelcells.EntireRow.Font.Size = 16; excelcells.Value2 = "Наличие товаров на складе на " + DateTime.Today.ToString("dd-MM-yyyy"); excelworksheet.get_Range("A3", "A3").ColumnWidth = 15; excelworksheet.get_Range("B3", "B3").ColumnWidth = 20; excelworksheet.get_Range("C3", "C3").ColumnWidth = 15; excelworksheet.get_Range("D3", "D3").ColumnWidth = 15; excelworksheet.get_Range("E3", "E3").ColumnWidth = 15; excelcells = excelworksheet.get_Range("A3", Type.Missing); excelcells.Value2 = "Склад"; excelcells = excelworksheet.get_Range("B3", Type.Missing); excelcells.Value2 = "Продукция"; excelcells = excelworksheet.get_Range("C3", Type.Missing); excelcells.Value2 = "Количество на складе"; excelcells = excelworksheet.get_Range("D3", Type.Missing); excelcells.Value2 = "Ед.измерения"; excelcells = excelworksheet.get_Range("E3", Type.Missing); excelcells.Value2 = "Адрес склада"; excelcells = excelworksheet.get_Range("A3", "E3"); excelcells.EntireRow.Font.Bold = true; MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string TextCommand = "select distinct s.id_skl, name_prod, adres_skl from sklad s, sklad_tov st, product p where s.id_skl=st.id_skl and st.id_prod = p.id_prod ";
MySqlCommand comm1 = new MySqlCommand(TextCommand, myConnection); MySqlDataReader myReader = comm1.ExecuteReader(); string[] ID = new string[100]; string[] P = new string[100]; string[] Adres = new string[100];
int N = 0; while (myReader.Read()) { ID[N] = myReader.GetString(0); P[N] = myReader.GetString(1); Adres[N] = myReader.GetString(2); N++; } myConnection.Close();
MySqlConnection myConnection1 = new MySqlConnection(Connect); myConnection1.Open(); //Устанавливаем соединение с базой данных. int i = 0; for (int j = 0; j < N; j++) { excelcells = excelworksheet.get_Range("A" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = ID[j]; excelcells = excelworksheet.get_Range("B" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = P[j];
string CommandText1 = "select sum(kol_post) from postavka, product where product.id_prod=postavka.id_prod and name_prod ='" + P[j] + "' and id_skl = " + ID[j] + " group by id_skl, postavka.id_prod "; MySqlCommand myCommand1 = new MySqlCommand(CommandText1, myConnection1); Int32 kolpost = 0; if (myCommand1.ExecuteScalar()!= null) kolpost = Convert.ToInt32(myCommand1.ExecuteScalar().ToString()); string CommandText2 = "select sum(kol_skl) from sklad_tov st, product p where p.id_prod=st.id_prod and name_prod ='" + P[j] + "' and id_skl = " + ID[j] + " group by id_skl, st.id_prod "; MySqlCommand myCommand2 = new MySqlCommand(CommandText2, myConnection1); Int32 kolskl = 0; if (myCommand2.ExecuteScalar()!= null) kolskl = Convert.ToInt32(myCommand2.ExecuteScalar().ToString());
excelcells = excelworksheet.get_Range("C" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = Convert.ToString(kolskl - kolpost); excelcells = excelworksheet.get_Range("D" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = "тонн"; excelcells = excelworksheet.get_Range("E" + (i + 4).ToString(), Type.Missing); excelcells.Value2 = Adres[j]; i++; } myConnection1.Close(); //Обязательно закрываем соединение! //заполнение данными excelcells = excelworksheet.get_Range("A3", "E" + (i + 3).ToString()); excelcells.Borders.ColorIndex = 5; excel.Visible = true; } } } //ZakAdd.cs using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using MySql.Data.MySqlClient; namespace Sklad { public partial class ZakAdd: Form { public ZakAdd() { InitializeComponent(); } public string Connect = "Database=as_product;Data Source=localhost;User=root;Password=pass;charset=cp1251;"; public int ID_red; public void ShowStrana() { MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных.
string CommandText = "Select name_str from strana"; MySqlCommand myCommand = new MySqlCommand(CommandText, myConnection); MySqlDataReader myReader1 = myCommand.ExecuteReader(); comboBox1.Items.Clear(); while (myReader1.Read()) { comboBox1.Items.Add(myReader1.GetString(0)); }
myConnection.Close(); //Обязательно закрываем соединение! } private void ZakAdd_Load(object sender, EventArgs e) { if(button1.Visible)ShowStrana(); } private void button1_Click(object sender, EventArgs e) { if (comboBox1.Text == "" || textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "") MessageBox.Show("Необходимо заполнить все данные", "Добавление записи", MessageBoxButtons.OK, MessageBoxIcon.Information); else {MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string CommandText = "SELECT min(C.ID_zak+1) FROM zakazchik C LEFT JOIN zakazchik b ON C.ID_zak+1 = b.ID_zak where b.ID_zak is null"; MySqlCommand myCommand = new MySqlCommand(CommandText, myConnection); Int32 nom = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
string CommandText1 = "SELECT id_str from strana where name_str = '"+comboBox1.Text+"'"; MySqlCommand myCommand1 = new MySqlCommand(CommandText1, myConnection); Int32 nom1 = Convert.ToInt32(myCommand1.ExecuteScalar().ToString());
string TextCommand = "Insert into zakazchik (id_zak, id_str, name_zak, adres_zak, tel_zak, kontact) values(" + Convert.ToString(nom) + ","; TextCommand += Convert.ToString(nom1) + ",'"; TextCommand += textBox1.Text + "','"; TextCommand += textBox2.Text + "','"; TextCommand += textBox3.Text + "','"; TextCommand += textBox4.Text + "')"; myCommand = new MySqlCommand(TextCommand, myConnection); myCommand.ExecuteNonQuery(); MessageBox.Show("Данные добавлены", "Добавление записи", MessageBoxButtons.OK, MessageBoxIcon.Information); comboBox1.Text = ""; textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; myConnection.Close(); //Обязательно закрываем соединение! this.Close(); } } private void button2_Click(object sender, EventArgs e) { if (comboBox1.Text == "" || textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "") MessageBox.Show("Необходимо заполнить все данные", "Добавление записи", MessageBoxButtons.OK, MessageBoxIcon.Information); else { MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных.
string CommandText1 = "SELECT id_str from strana where name_str = '" + comboBox1.Text + "'"; MySqlCommand myCommand1 = new MySqlCommand(CommandText1, myConnection); Int32 nom1 = Convert.ToInt32(myCommand1.ExecuteScalar().ToString());
string TextCommand = "Update zakazchik Set id_str = "; TextCommand += Convert.ToString(nom1) + ",name_zak = '"; TextCommand += textBox1.Text + "', adres_zak = '"; TextCommand += textBox2.Text + "', tel_zak = '"; TextCommand += textBox3.Text + "',kontact = '"; TextCommand += textBox4.Text + "' where id_zak = " + Convert.ToString(ID_red); MySqlCommand myCommand = new MySqlCommand(TextCommand, myConnection); myCommand.ExecuteNonQuery(); MessageBox.Show("Данные изменены", "Изменение записи", MessageBoxButtons.OK, MessageBoxIcon.Information); comboBox1.Text = ""; textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; myConnection.Close(); //Обязательно закрываем соединение! this.Close(); } } }}
//Sotr.cs using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using MySql.Data.MySqlClient; namespace Sklad { public partial class Sotr: Form { public Sotr() { InitializeComponent(); } public string Connect = "Database=as_product;Data Source=localhost;User=root;Password=pass;charset=cp1251;"; public int ID_red; public void ShowSklad() { MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string CommandText = "Select id_skl from sklad"; MySqlCommand myCommand = new MySqlCommand(CommandText, myConnection); MySqlDataReader myReader1 = myCommand.ExecuteReader(); comboBox1.Items.Clear(); while (myReader1.Read()) { comboBox1.Items.Add(myReader1.GetString(0)); } myConnection.Close(); //Обязательно закрываем соединение! } private void Sotr_Load(object sender, EventArgs e) { string CommandText = "Select Count(*) from sotrudniki";
MySqlConnection myConnection = new MySqlConnection(Connect); MySqlCommand myCommand = new MySqlCommand(CommandText, myConnection); myConnection.Open(); //Устанавливаем соединение с базой данных. Int32 kol = Convert.ToInt32(myCommand.ExecuteScalar().ToString()); if (kol > 0) dataGridView1.RowCount = kol; else dataGridView1.RowCount = 1; for (int k = 0; k < kol; k++) dataGridView1[0, k].Value = ""; string TextCommand = "Select * "; TextCommand += " from sotrudniki "; MySqlCommand comm1 = new MySqlCommand(TextCommand, myConnection); MySqlDataReader myReader = comm1.ExecuteReader();
int i = 0; while (myReader.Read()) { for (int j = 0; j < 8; j++) {
dataGridView1[j, i].Value = myReader.GetString(j); } i++; } myConnection.Close(); //Обязательно закрываем соединение! ShowSklad(); this.button2.Enabled = true; } private void button1_Click(object sender, EventArgs e) { if (comboBox1.Text == "" || textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "" || textBox6.Text == "") MessageBox.Show("Необходимо заполнить все данные", "Добавление записи", MessageBoxButtons.OK, MessageBoxIcon.Information); else { MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string CommandText = "SELECT min(C.ID_sotr+1) FROM sotrudniki C LEFT JOIN sotrudniki b ON C.ID_sotr+1 = b.ID_sotr where b.ID_sotr is null"; MySqlCommand myCommand = new MySqlCommand(CommandText, myConnection);
Int32 nom = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
string TextCommand = "Insert into sotrudniki (id_sotr, fam, imya, otch, dolzn, razr, id_skl, passw) values(" + Convert.ToString(nom) + ",'"; TextCommand += textBox1.Text + "','"; TextCommand += textBox2.Text +"','"; TextCommand += textBox3.Text + "','"; TextCommand += textBox6.Text + "',"; TextCommand += textBox5.Text + ","; TextCommand += comboBox1.Text + ",'"; TextCommand += textBox4.Text + "')"; myCommand = new MySqlCommand(TextCommand, myConnection); myCommand.ExecuteNonQuery(); MessageBox.Show("Данные добавлены", "Добавление записи", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Sotr_Load(sender, e); comboBox1.Text = ""; textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; textBox5.Text = ""; textBox6.Text = ""; myConnection.Close(); //Обязательно закрываем соединение! } } private void редактироватьЗаписьToolStripMenuItem_Click(object sender, EventArgs e) { ID_red = Convert.ToInt32(dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value); textBox1.Text = Convert.ToString(dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value); textBox2.Text = Convert.ToString(dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value); textBox3.Text = Convert.ToString(dataGridView1[3, dataGridView1.CurrentCell.RowIndex].Value); textBox4.Text = Convert.ToString(dataGridView1[6, dataGridView1.CurrentCell.RowIndex].Value); textBox5.Text = Convert.ToString(dataGridView1[5, dataGridView1.CurrentCell.RowIndex].Value); textBox6.Text = Convert.ToString(dataGridView1[4, dataGridView1.CurrentCell.RowIndex].Value); comboBox1.Text = Convert.ToString(dataGridView1[7, dataGridView1.CurrentCell.RowIndex].Value); this.button2.Enabled = true; } private void button2_Click(object sender, EventArgs e) { if (comboBox1.Text == "" || textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "" || textBox6.Text == "") MessageBox.Show("Необходимо заполнить все данные", "Изменение записи", MessageBoxButtons.OK, MessageBoxIcon.Information); else { MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string TextCommand = "Update sotrudniki Set fam = '" + textBox1.Text + "', imya = '" + textBox2.Text; TextCommand += "', otch = '" + textBox3.Text; TextCommand += "', dolzn = '" + textBox6.Text; TextCommand += "', razr = " + textBox5.Text; TextCommand += ", passw = '" + textBox4.Text; TextCommand += "', id_skl = " + comboBox1.Text; TextCommand += " where id_sotr = " + ID_red; MySqlCommand myCommand = new MySqlCommand(TextCommand, myConnection); myCommand.ExecuteNonQuery(); MessageBox.Show("Данные изменены", "Изменение записи", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Sotr_Load(sender, e); comboBox1.Text = ""; textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; textBox5.Text = ""; textBox6.Text = ""; myConnection.Close(); //Обязательно закрываем соединение! } this.button2.Enabled = false; } private void удалитьЗаписьToolStripMenuItem_Click(object sender, EventArgs e) { MySqlConnection myConnection = new MySqlConnection(Connect); myConnection.Open(); //Устанавливаем соединение с базой данных. string DelId = Convert.ToString(dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value); string TextCommand = "Delete from sotrudniki where id_sotr =" + DelId; MySqlCommand myCommand = new MySqlCommand(TextCommand, myConnection); myCommand.ExecuteNonQuery(); myConnection.Close(); //Обязательно закрываем соединение! this.Sotr_Load(sender, e); MessageBox.Show("Данные удалены", "Удаление записи", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } ПРИЛОЖЕНИЕ Б
Дата добавления: 2015-08-31; Просмотров: 267; Нарушение авторских прав?; Мы поможем в написании вашей работы! Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет |