mgpk-politex-zadania

1
using Microsoft.Toolkit.Uwp.UI.Controls;
2
using System;
3
using System.Collections.Generic;
4
using System.Collections.ObjectModel;
5
using System.Configuration;
6
using System.Data;
7
using System.Data.SqlClient;
8
using System.IO;
9
using System.Linq;
10
using System.Runtime.InteropServices.WindowsRuntime;
11
using Windows.Foundation;
12
using Windows.Foundation.Collections;
13
using Windows.UI.Xaml;
14
using Windows.UI.Xaml.Controls;
15
using Windows.UI.Xaml.Controls.Primitives;
16
using Windows.UI.Xaml.Data;
17
using Windows.UI.Xaml.Input;
18
using Windows.UI.Xaml.Media;
19
using Windows.UI.Xaml.Navigation;
20

21
// The Blank Page item template is documented at https://go.microsoft.com/fwlink/?LinkId=234238
22

23
namespace UWPKursovayaAvtobus
24
{
25
    /// <summary>
26
    /// An empty page that can be used on its own or navigated to within a Frame.
27
    /// </summary>
28
    public sealed partial class Nav_Obslugers : Page
29
    {
30
        public Nav_Obslugers()
31
        {
32
            this.InitializeComponent();
33
        }
34

35
        private async void dataGrid1_Loaded(object sender, RoutedEventArgs e)
36
        {
37
            SqlConnection cnn;
38
            string conStr = ConnectionString.Constring;
39

40
            cnn = new SqlConnection(conStr);
41
            cnn.Open();
42
            Console.Write("OPENING DB CONNECTION!!! \n");
43
            Console.Write("[MENU] Connect db clicked - test \n");
44
            try
45
            {
46
                var select = "SELECT * FROM Maintenance";
47

48
                var commandBuilder = new SqlCommand(select, cnn);
49
                commandBuilder.ExecuteNonQuery();
50

51
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandBuilder);
52
                DataTable ds = new DataTable("Avtobusx");
53
                dataAdapter.Fill(ds);
54
                dataGrid1.Columns.Clear();
55
                for (int i = 0; i < ds.Columns.Count; i++)
56
                {
57
                    dataGrid1.Columns.Add(new DataGridTextColumn()
58
                    {
59
                        Header = ds.Columns[i].ColumnName,
60
                        Binding = new Binding { Path = new PropertyPath("[" + i.ToString() + "]") }
61
                    });
62
                }
63

64
                var collection = new ObservableCollection<object>();
65
                foreach (DataRow row in ds.Rows)
66
                {
67
                    collection.Add(row.ItemArray);
68
                }
69

70
                dataGrid1.ItemsSource = collection;
71
                Console.WriteLine("Connection established and the datagrid filled!");
72
                cnn.Close();
73
            }
74
            catch (Exception ex)
75
            {
76
                var dialog = new ContentDialog()
77
                {
78
                    Title = "An Exception has occured!",
79
                    Content = ex.Message,
80
                    CloseButtonText = "Ok"
81
                };
82
                await dialog.ShowAsync();
83
            }
84

85
            
86
        }
87

88
        private async void ClickSendDb_Click(object sender, RoutedEventArgs e)
89
        { 
90
            try {
91
                string Num = AvEditor1.Text;
92
                string ModAvtobus = AvEditor2.Text;
93
                string DatObslug = AvEditor3.Text;
94
                string Engineer = AvEditor4.Text;
95
                string ErAvto = AvEditor5.Text;
96
                string DatSlegObslug = AvEditor6.Text;
97
                string GodDorog = AvEditor7.Text;
98
                string query = "INSERT INTO Maintenance(Num, Model_Avtobusa, Data_Poslednego_Obsluzhivania, Ingener_Obsluzhivania,Problemi_Avtobusa,Data_Sledueschego_Obsluzivania,Goden_K_Doroge) " +
99
                   "Values('" + Num + "', '" + ModAvtobus + "', '" + DatObslug + "', '" + Engineer + "','" + ErAvto + "','" + DatSlegObslug + "','" + GodDorog + "')";
100
                SqlConnection cnn;
101
                string conStr = ConnectionString.Constring;
102

103
                cnn = new SqlConnection(conStr);
104
                cnn.Open();
105
                SqlCommand command = new SqlCommand(query, cnn);
106
                command.ExecuteNonQuery();
107
                Console.WriteLine("Data inserted!\n Updating grid");
108
                var select = "SELECT * FROM Maintenance";
109
                var commandBuilder = new SqlCommand(select, cnn);
110
                commandBuilder.ExecuteNonQuery();
111

112
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandBuilder);
113
                DataTable ds = new DataTable("Avtobusx");
114
                dataAdapter.Fill(ds);
115
                dataGrid1.Columns.Clear();
116
                for (int i = 0; i < ds.Columns.Count; i++)
117
                {
118
                    dataGrid1.Columns.Add(new DataGridTextColumn()
119
                    {
120
                        Header = ds.Columns[i].ColumnName,
121
                        Binding = new Binding { Path = new PropertyPath("[" + i.ToString() + "]") }
122
                    });
123
                }
124

125
                var collection = new ObservableCollection<object>();
126
                foreach (DataRow row in ds.Rows)
127
                {
128
                    collection.Add(row.ItemArray);
129
                }
130

131
                dataGrid1.ItemsSource = collection;
132
                Console.WriteLine("Connection established and the datagrid filled!");
133
                cnn.Close();
134

135
                Console.WriteLine("Connection has been closed , database ready for next operation!");
136

137
            }
138
            catch (Exception ex)
139
            {
140
                var dialog = new ContentDialog()
141
                {
142
                    Title = "An Exception has occured!",
143
                    Content = ex.Message,
144
                    CloseButtonText = "Ok"
145
                };
146
                await dialog.ShowAsync();
147
            }
148

149
        }
150

151
        private async void UpdateTable_Click(object sender, RoutedEventArgs e)
152
        {
153
            try {
154
                SqlConnection cnn;
155
                string conStrs = ConnectionString.Constring;
156

157
                cnn = new SqlConnection(conStrs);
158
                cnn.Open();
159
                Console.Write("OPENING DB CONNECTION!!! \n");
160
                var querier = "UPDATE Maintenance SET Model_Avtobusa=@a1,Data_Poslednego_Obsluzhivania=@a2,Ingener_Obsluzhivania=@a3,Problemi_Avtobusa=@a4,Data_Sledueschego_Obsluzivania=@a5,Goden_K_Doroge=@a6 WHERE Num=@a7";
161
                var commandBuilders = new SqlCommand(querier, cnn);
162
                commandBuilders.Parameters.AddWithValue("a1", AvEditor2.Text);
163
                commandBuilders.Parameters.AddWithValue("a2", AvEditor3.Text);
164
                commandBuilders.Parameters.AddWithValue("a3", AvEditor4.Text);
165
                commandBuilders.Parameters.AddWithValue("a4", AvEditor5.Text);
166
                commandBuilders.Parameters.AddWithValue("a5", AvEditor6.Text);
167
                commandBuilders.Parameters.AddWithValue("a6", AvEditor7.Text);
168
                commandBuilders.Parameters.AddWithValue("a7", int.Parse(AvEditor1.Text));
169
                commandBuilders.ExecuteNonQuery();
170
                Console.WriteLine("Connection established and the table updated!");
171
                var select = "SELECT * FROM Maintenance";
172
                var commandBuilder = new SqlCommand(select, cnn);
173
                commandBuilder.ExecuteNonQuery();
174

175
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandBuilder);
176
                DataTable ds = new DataTable("Avtobusx");
177
                dataAdapter.Fill(ds);
178
                dataGrid1.Columns.Clear();
179
                for (int i = 0; i < ds.Columns.Count; i++)
180
                {
181
                    dataGrid1.Columns.Add(new DataGridTextColumn()
182
                    {
183
                        Header = ds.Columns[i].ColumnName,
184
                        Binding = new Binding { Path = new PropertyPath("[" + i.ToString() + "]") }
185
                    });
186
                }
187

188
                var collection = new ObservableCollection<object>();
189
                foreach (DataRow row in ds.Rows)
190
                {
191
                    collection.Add(row.ItemArray);
192
                }
193

194
                dataGrid1.ItemsSource = collection;
195
                Console.WriteLine("Connection established and the datagrid filled!");
196
                cnn.Close();
197
                Console.WriteLine("Connection has been closed , database ready for next operation!");
198

199
            }
200
            catch (Exception ex)
201
            {
202
                var dialog = new ContentDialog()
203
                {
204
                    Title = "An Exception has occured!",
205
                    Content = ex.Message,
206
                    CloseButtonText = "Ok"
207
                };
208
                await dialog.ShowAsync();
209
            }
210
        }
211
    }
212
}
213