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_salers : Page
29
    {
30
        public Nav_salers()
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 Prodazhi";
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
        private async void ClickSendDb_Click(object sender, RoutedEventArgs e)
88
        {
89
            try {
90
                string Num = Edit1.Text;
91
                string SaleDat = Edit2.Text;
92
                string NomAvtobus = Edit3.Text;
93
                string Punkt = Edit4.Text;
94
                string Stoimost = Edit5.Text;
95
                string query = "INSERT INTO Prodazhi(Num, Sale_Date, Nomer_Avtobusa, Punkt_Posadki,Stoimost) " +
96
                   "Values('" + Num + "', '" + SaleDat + "', '" + NomAvtobus + "', '" + Punkt + "','" + Stoimost + "')";
97
                SqlConnection cnn;
98
                string conStr = ConnectionString.Constring;
99

100
                cnn = new SqlConnection(conStr);
101
                cnn.Open();
102
                SqlCommand command = new SqlCommand(query, cnn);
103
                command.ExecuteNonQuery();
104
                Console.WriteLine("Data inserted!\n Closing connection ");
105
                var select = "SELECT * FROM Prodazhi";
106

107
                var commandBuilder = new SqlCommand(select, cnn);
108
                commandBuilder.ExecuteNonQuery();
109

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

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

129
                dataGrid1.ItemsSource = collection;
130
                Console.WriteLine("Connection established and the datagrid filled!");
131
                cnn.Close();
132
                Console.WriteLine("Connection has been closed , database ready for next operation!");
133

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

146
        }
147

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

154
                cnn = new SqlConnection(conStrs);
155
                cnn.Open();
156
                Console.Write("OPENING DB CONNECTION!!! \n");
157
                var querier = "UPDATE Prodazhi SET Sale_Date=@a1,Nomer_Avtobusa=@a2,Punkt_Posadki=@a3,Stoimost=@a4 WHERE Num=@a5";
158
                var commandBuilder = new SqlCommand(querier, cnn);
159
                commandBuilder.Parameters.AddWithValue("a1", Edit2.Text);
160
                commandBuilder.Parameters.AddWithValue("a2", int.Parse(Edit3.Text));
161
                commandBuilder.Parameters.AddWithValue("a3", Edit4.Text);
162
                commandBuilder.Parameters.AddWithValue("a4", Edit5.Text);
163
                commandBuilder.Parameters.AddWithValue("a5", int.Parse(Edit1.Text));
164
                commandBuilder.ExecuteNonQuery();
165
                Console.WriteLine("Connection established and the table updated!");
166
                var select = "SELECT * FROM Prodazhi";
167

168
                var commandBuilders = new SqlCommand(select, cnn);
169
                commandBuilders.ExecuteNonQuery();
170

171
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandBuilders);
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
                Console.WriteLine("Connection established and the datagrid filled!");
192
                cnn.Close();
193

194
            }
195
            catch (Exception ex)
196
            {
197
                var dialog = new ContentDialog()
198
                {
199
                    Title = "An Exception has occured!",
200
                    Content = ex.Message,
201
                    CloseButtonText = "Ok"
202
                };
203
                await dialog.ShowAsync();
204
            }
205
        }
206
    }
207
}
208