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_Sotrudniks : Page
29
    {
30
        public Nav_Sotrudniks()
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 Employees";
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
                for (int i = 0; i < ds.Columns.Count; i++)
54
                {
55
                    dataGrid1.Columns.Add(new DataGridTextColumn()
56
                    {
57
                        Header = ds.Columns[i].ColumnName,
58
                        Binding = new Binding { Path = new PropertyPath("[" + i.ToString() + "]") }
59
                    });
60
                }
61

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

68
                dataGrid1.ItemsSource = collection;
69
                Console.WriteLine("Connection established and the datagrid filled!");
70
                cnn.Close();
71

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

86
        private async  void Button_Click(object sender, RoutedEventArgs e)
87
        {
88
            try {
89
                string Num = Exed1.Text;
90
                string EmpName = Exed2.Text;
91
                string EmpsName = Exed3.Text;
92
                string Emppat = Exed4.Text;
93
                string EmpES = Exed5.Text;
94
                string Emptitle = Exed6.Text;
95
                string Empint = Exed7.Text;
96
                string query = "INSERT INTO Employees(Num, Surname, Name, Patronym,Employed_Since,Job,Internship) " +
97
                   "Values('" + Num + "', '" + EmpName + "', '" + EmpsName + "', '" + Emppat + "','" + EmpES + "','" + Emptitle + "','" + Empint + "')";
98
                SqlConnection cnn;
99
                string conStr = ConnectionString.Constring;
100

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

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

111
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandBuilder);
112
                DataTable ds = new DataTable("Avtobusx");
113
                dataAdapter.Fill(ds);
114
                dataGrid1.Columns.Clear();
115

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
                cnn.Close();
133
                Console.WriteLine("Connection has been closed , database ready for next operation!");
134

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

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

155
                cnn = new SqlConnection(conStr);
156
                cnn.Open();
157
                Console.Write("OPENING DB CONNECTION!!! \n");
158
                var select = "UPDATE Employees SET Surname=@a1,Name=@a2,Patronym=@a3,Employed_Since=@a4,Job=@a5,Internship=@a6 WHERE Num=@a7";
159
                var commandBuilder = new SqlCommand(select, cnn);
160
                commandBuilder.Parameters.AddWithValue("a1", Exed2.Text);
161
                commandBuilder.Parameters.AddWithValue("a2", Exed3.Text);
162
                commandBuilder.Parameters.AddWithValue("a3", Exed4.Text);
163
                commandBuilder.Parameters.AddWithValue("a4", Exed5.Text);
164
                commandBuilder.Parameters.AddWithValue("a5", Exed6.Text);
165
                commandBuilder.Parameters.AddWithValue("a6", Exed7.Text);
166
                commandBuilder.Parameters.AddWithValue("a7", int.Parse(Exed1.Text));
167
                commandBuilder.ExecuteNonQuery();
168
                Console.WriteLine("Connection established and the table updated!");
169
                var selector = "SELECT * FROM Employees";
170

171
                var commandBuildersd = new SqlCommand(selector, cnn);
172
                commandBuilder.ExecuteNonQuery();
173

174
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandBuildersd);
175
                DataTable ds = new DataTable("Avtobusx");
176
                dataAdapter.Fill(ds);
177
                dataGrid1.Columns.Clear();
178

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
                cnn.Close();
196
            }
197
            catch (Exception ex)
198
            {
199
                var dialog = new ContentDialog()
200
                {
201
                    Title = "An Exception has occured!",
202
                    Content = ex.Message,
203
                    CloseButtonText = "Ok"
204
                };
205
                await dialog.ShowAsync();
206
            }
207
        }
208
    }
209
}
210