jKPA57Sp

1using System;
2using System.Collections.Generic;
3using System.Drawing;
4using System.Windows.Forms;
5using MySql.Data;
6using MySql.Data.MySqlClient;
7using System.Data;
8
9namespace SharpDevelopTest
10{
11    /// <summary>
12    /// Description of MainForm.
13    /// </summary>
14    public partial class MainForm : Form
15    {
16        //SHOW GLOBAL VARIABLES LIKE 'char%';
17        public MainForm()
18        {
19            //
20            // The InitializeComponent() call is required for Windows Forms designer support.
21            //
22            InitializeComponent();
23            
24            //
25            // TODO: Add constructor code after the InitializeComponent() call.
26            //
27            
28            string connStr = "server=localhost;user=root;";
29            using (var conn = new MySqlConnection(connStr))
30            using (var cmd = conn.CreateCommand())
31            {
32                conn.Open();
33                cmd.CommandText = "CREATE DATABASE IF NOT EXISTS `Monparnas`;";
34                cmd.ExecuteNonQuery();
35            }
36
37            connStr = "server=localhost;user=root;database=Monparnas;";
38            using (var conn = new MySqlConnection(connStr))
39            using (var cmd = conn.CreateCommand())
40            {
41                conn.Open();
42                cmd.CommandText = "CREATE TABLE IF NOT EXISTS `poezda` (" +
43                  "`nomer` int NOT NULL," +
44                  "`type` varchar(50) NOT NULL default 'skoroj'," +
45                  "`stOtpravlenia` varchar(50) NOT NULL default ''," +
46                  "`vrOtpravlenia` date NOT NULL default '1000-01-01'," +
47                  "`stPribitija` varchar(50)," +
48                  "`kolVagonov` int," +
49                  "`nachPoezda` varchar(50)," +
50                  "`cena` int," +
51                  "`status` varchar(20)," +
52                  "PRIMARY KEY  (`nomer`));";
53                cmd.ExecuteNonQuery();
54            }
55            
56            SelectData();
57        }
58        
59        void ButtonAddClick(object sender, EventArgs e)
60        {
61            string connStr = "server=localhost;user=root;database=Monparnas;";
62            using (var conn = new MySqlConnection(connStr))
63            using (var cmd = conn.CreateCommand())
64            {
65                conn.Open();
66                cmd.CommandText = "INSERT INTO poezda VALUES(@nomer,@type,@stOtpravlenia,@vrOtpravlenia,@stPribitija,@kolVagonov,@nachPoezda,@cena,@status);";
67                cmd.Parameters.Add(new MySqlParameter("@nomer", tbnumber.Text));
68                cmd.Parameters.Add(new MySqlParameter("@type", cbtype.SelectedItem));
69                cmd.Parameters.Add(new MySqlParameter("@stOtpravlenia", tbstanotpr.Text));
70                cmd.Parameters.Add(new MySqlParameter("@vrOtpravlenia", tpvropr.Value.ToString("yyyy-MM-dd")));
71                cmd.Parameters.Add(new MySqlParameter("@stPribitija", tbstprib.Text));
72                cmd.Parameters.Add(new MySqlParameter("@kolVagonov", nudvagonov.Value.ToString()));
73                cmd.Parameters.Add(new MySqlParameter("@nachPoezda", tbnachp.Text));
74                cmd.Parameters.Add(new MySqlParameter("@cena", textBox2.Text));
75                cmd.Parameters.Add(new MySqlParameter("@status", comboBox2.SelectedItem));
76                cmd.ExecuteNonQuery();
77                SelectData();
78                MessageBox.Show("Запись добавленна!");
79            }
80        }
81        
82        
83        void ButtonDeleteClick(object sender, EventArgs e)
84        {
85            if(dataGridViewInfo.SelectedRows.Count > 0)
86            {
87                string connStr = "server=localhost;user=root;database=Monparnas;";
88                using (var conn = new MySqlConnection(connStr))
89                using (var cmd = conn.CreateCommand())
90                {
91                    conn.Open();
92                    cmd.CommandText = "DELETE FROM poezda WHERE nomer=@num";
93                    cmd.Parameters.Add(new MySqlParameter("@num", dataGridViewInfo.SelectedRows[0].Cells[0].Value.ToString()));
94                    cmd.ExecuteNonQuery();
95                    
96                    ((DataTable)dataGridViewInfo.DataSource).Rows.RemoveAt(dataGridViewInfo.SelectedRows[0].Index);
97                }
98            }
99            else
100            {
101                MessageBox.Show("Выберите строку для удаления");
102            }
103        }
104        
105        private void SelectData()
106        {
107            string connStr = "server=localhost;user=root;database=Monparnas;";
108            using (var conn = new MySqlConnection(connStr))
109            using (var cmd = conn.CreateCommand())
110            {
111                conn.Open();
112                cmd.CommandText = "SELECT * FROM poezda";
113                MySqlDataReader dr = cmd.ExecuteReader();
114                DataTable dt = new DataTable();
115                dt.Load(dr);
116                dataGridViewInfo.DataSource = dt;
117            }
118        }
119        
120        void DataGridViewInfoSelectionChanged(object sender, EventArgs e)
121        {
122
123        }
124
125        private void button1_Click(object sender, EventArgs e)
126        {
127            string dopopcii = "";
128            if (checkBox1.Checked)
129            {
130                dopopcii = dopopcii+" AND cena>"+textBox3.Text+" AND cena < "+textBox4.Text;
131            }
132            if (checkBox2.Checked)
133            {
134                dopopcii = dopopcii + "AND status = '" + comboBox3.SelectedItem + "'";
135            }
136            string stanzia = textBox1.Text;
137            if (comboBox1.SelectedIndex == 0)
138            {
139                string connStr = "server=localhost;user=root;database=Monparnas;";
140                using (var conn = new MySqlConnection(connStr))
141                using (var cmd = conn.CreateCommand())
142                {
143                    conn.Open();
144                    cmd.CommandText = "SELECT * FROM poezda WHERE stOtpravlenia = '" + stanzia + "'" + dopopcii + " ORDER BY vrOtpravlenia;";
145                    MySqlDataReader dr = cmd.ExecuteReader();
146                    DataTable dt = new DataTable();
147                    dt.Load(dr);
148                    dataGridViewInfo.DataSource = dt;
149                }
150            }
151            else
152            {
153                string connStr = "server=localhost;user=root;database=Monparnas;";
154                using (var conn = new MySqlConnection(connStr))
155                using (var cmd = conn.CreateCommand())
156                {
157                    conn.Open();
158                    cmd.CommandText = "SELECT * FROM poezda WHERE stPribitija = '" + stanzia + "';";
159                    MySqlDataReader dr = cmd.ExecuteReader();
160                    DataTable dt = new DataTable();
161                    dt.Load(dr);
162                    dataGridViewInfo.DataSource = dt;
163                }
164            }
165        }
166
167        private void button2_Click(object sender, EventArgs e)
168        {
169            SelectData();
170        }
171    }
172}