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 BlankPage1 : Page
29
    {
30
        public BlankPage1()
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
                var select = "SELECT * FROM Marshuti";
46

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

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

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

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

85
        private async void UpdateTransportTable_Click(object sender, RoutedEventArgs e)
86
        {
87
            try {
88
                SqlConnection cnn;
89
                string conStr = ConnectionString.Constring;
90

91
                cnn = new SqlConnection(conStr);
92
                cnn.Open();
93
                Console.Write("OPENING DB CONNECTION!!! \n");
94
                var select = "UPDATE Marshuti SET Nachalni_Punkt=@a1,Konechni_Punkt=@a2,Voditel=@a3,Model_Avtobusa=@a4,Vremya_Proezda=@a5 WHERE Nomer_Marshuta=@a6";
95
                var commandBuilder = new SqlCommand(select, cnn);
96
                commandBuilder.Parameters.AddWithValue("a1", Editor2.Text);
97
                commandBuilder.Parameters.AddWithValue("a2", Editor3.Text);
98
                commandBuilder.Parameters.AddWithValue("a3", Editor4.Text);
99
                commandBuilder.Parameters.AddWithValue("a4", Editor5.Text);
100
                commandBuilder.Parameters.AddWithValue("a5", Editor6.Text);
101
                commandBuilder.Parameters.AddWithValue("a6", int.Parse(Editor1.Text));
102
                commandBuilder.ExecuteNonQuery();
103
                var selector = "SELECT * FROM Marshuti";
104

105
                var commandBuilders = new SqlCommand(selector, cnn);
106
                commandBuilders.ExecuteNonQuery();
107

108
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandBuilders);
109

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

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

128
                dataGrid1.ItemsSource = collection;
129
                Console.WriteLine("Connection established and the table updated!");
130
                cnn.Close();
131

132
            }
133
            catch (Exception ex)
134
            {
135
                var dialog = new ContentDialog()
136
                {
137
                    Title = "An Exception has occured!",
138
                    Content = ex.Message,
139
                    CloseButtonText = "Ok"
140
                };
141
                await dialog.ShowAsync();
142
            }
143
        }
144

145
        private async void ClickSendDb_Click(object sender, RoutedEventArgs e)
146
        {
147
            try
148
            {
149
                string NomeMarshuta = Editor1.Text;
150
                string NachalnPunkt = Editor2.Text;
151
                string KonechnPunkt = Editor3.Text;
152
                string Vodit = Editor4.Text;
153
                string Modelvtobusa = Editor5.Text;
154
                string VremProezda = Editor6.Text;
155
                string query = "INSERT INTO Marshuti(Nomer_Marshuta, Nachalni_Punkt, Konechni_Punkt, Voditel,Model_Avtobusa,Vremya_Proezda) " +
156
                   "Values('" + NomeMarshuta + "', '" + NachalnPunkt + "', '" + KonechnPunkt + "', '" + Vodit + "','" + Modelvtobusa + "', '" + VremProezda + "')";
157
                SqlConnection cnn;
158
                string conStr = ConnectionString.Constring;
159

160
                cnn = new SqlConnection(conStr);
161
                cnn.Open();
162
                SqlCommand command = new SqlCommand(query, cnn);
163
                command.ExecuteNonQuery();
164
                Console.WriteLine("Data inserted!\n Closing connection ");
165
                var selector = "SELECT * FROM Marshuti";
166

167
                var commandBuilders = new SqlCommand(selector, cnn);
168
                commandBuilders.ExecuteNonQuery();
169

170
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandBuilders);
171

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

184
                var collection = new ObservableCollection<object>();
185
                foreach (DataRow row in ds.Rows)
186
                {
187
                    collection.Add(row.ItemArray);
188
                }
189

190
                dataGrid1.ItemsSource = collection;
191
                cnn.Close();
192
                Console.WriteLine("Connection has been closed , database ready for next operation!");
193
            }
194
            catch (Exception ex)
195
            {
196
                var dialog = new ContentDialog()
197
                {
198
                    Title = "An Exception has occured!",
199
                    Content = ex.Message,
200
                    CloseButtonText = "Ok"
201
                };
202
                await dialog.ShowAsync();
203
            }
204
        }
205
    }
206
}
207