· 5 years ago · Mar 19, 2020, 04:24 PM
1using System;
2using System.Collections.Generic;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Linq;
7using System.Text;
8using System.Threading.Tasks;
9using System.Windows.Forms;
10
11using System.Data.SqlClient;
12using System.IO;
13
14namespace EntitiyFrameworkTest
15{
16 /// <summary>
17 /// Aufgrund des plötzlichen Projektwechsels im letzten Semester hatte ich keine Gelegenheit, das EntityFramework zu
18 /// verwenden. Da ich jedoch zu mehreren Gelegenheiten mit Datenbanken arbeite, und das EntityFramework, sofern ich es
19 /// meistere, mir diese Arbeit erheblich einfacher machen würde, werde ich die aktuelle Aufgabenstellung als Gelegenheit
20 /// verwenden, mir Kenntnisse darüber anzueignen und sie zu vertiefen.
21 ///
22 /// Dieses Programm fungiert als kleiner Datenbankmanager, es ist möglich die Daten von verschiedenen Personen zu betrachten,
23 /// zu ändern und Haustiere hinzuzufügen. Zu beginn wird eine Datenbank erstellt und befüllt, die Abfragen
24 /// erfolgen mithilfe von LINQ to entities, die Aktualisierungen mithilfe des EntityFrameworks
25 /// </summary>
26 public partial class Form1 : Form
27 {
28 #region Variables
29 SqlConnection con;
30 SqlCommand cmd;
31 efEntities db;
32 int addedAnimalCount;
33 #endregion
34
35 #region Constructors
36 public Form1()
37 {
38 InitializeComponent();
39 db = new efEntities();
40 addedAnimalCount = 0;
41 }
42 #endregion
43
44 #region Event-Methods
45
46 /// <summary>
47 /// Startup-Method, creates database if not exists,
48 /// Fills the ListBox with names of database,
49 /// Makes Details-GroupBox invisible
50 /// </summary>
51 /// <param name="sender"></param>
52 /// <param name="e"></param>
53 private void Form1_Load(object sender, EventArgs e)
54 {
55 CreateAndFillDatabase("ef");
56 RefreshPersonList();
57 gbMain.Visible = false;
58 btnSave.Enabled = false;
59 }
60
61 /// <summary>
62 /// Save Changes made to the selected persons data
63 /// </summary>
64 /// <param name="sender"></param>
65 /// <param name="e"></param>
66 private void btnSave_Click(object sender, EventArgs e)
67 {
68 //Handle Person
69 var person = (from p in db.Person
70 where p.PersonID == lstPerson.SelectedIndex
71 select p).Single();
72
73 person.FirstName = txtFName.Text;
74 person.Surname = txtSName.Text;
75 person.FavNumber = int.Parse(txtFavNum.Text);
76
77 //Handle Animal
78 if (addedAnimalCount > 0)
79 {
80 int lastID = (from a in db.Animal
81 select a.ID).Max();
82
83 for(int i = 0; i < addedAnimalCount; i++)
84 {
85 db.Animal.Add(new Animal()
86 {
87 ID = lastID + 1,
88 Name = lstPets.Items[lstPets.Items.Count - addedAnimalCount + i].ToString(),
89 PersonID = lstPerson.SelectedIndex
90 });
91
92 lastID++;
93 }
94 }
95
96 db.SaveChanges();
97 addedAnimalCount = 0;
98 gbMain.Visible = false;
99 btnSave.Enabled = false;
100 RefreshPersonList();
101 }
102
103 /// <summary>
104 /// Adds item from TextBox to ListBox
105 /// </summary>
106 /// <param name="sender"></param>
107 /// <param name="e"></param>
108 private void btnAddPet_Click(object sender, EventArgs e)
109 {
110 if (!string.IsNullOrEmpty(txtAddPet.Text))
111 {
112 lstPets.Items.Add(txtAddPet.Text);
113 txtAddPet.Text = "";
114 addedAnimalCount++;
115 StuffChanged(sender, e);
116 }
117 }
118
119 /// <summary>
120 /// Displays GroupBox with all of a Persons information
121 /// Stores currentAnimalCount
122 /// </summary>
123 /// <param name="sender"></param>
124 /// <param name="e"></param>
125 private void lstPerson_SelectedIndexChanged(object sender, EventArgs e)
126 {
127 if (lstPerson.SelectedIndex != -1)
128 {
129 string sName = "";
130 string fName = "";
131 int favNum = 0;
132 List<string> pets = new List<string>();
133
134 fName = (from s in db.Person
135 where s.PersonID == lstPerson.SelectedIndex
136 select s.FirstName).Single();
137
138 sName = (from s in db.Person
139 where s.PersonID == lstPerson.SelectedIndex
140 select s.Surname).Single();
141
142 favNum = (from s in db.Person
143 where s.PersonID == lstPerson.SelectedIndex
144 select s.FavNumber).Single();
145
146 pets = (from p in db.Person
147 join a in db.Animal
148 on p.PersonID equals a.PersonID
149 where p.PersonID == lstPerson.SelectedIndex
150 select a.Name).ToList();
151
152 gbMain.Text = "Data of " + fName + ":";
153 txtFName.Text = fName;
154 txtSName.Text = sName;
155 txtFavNum.Text = favNum.ToString();
156
157 lstPets.Items.Clear();
158 for (int i = 0; i < pets.Count; i++)
159 lstPets.Items.Add(pets[i]);
160
161 btnSave.Enabled = false;
162 gbMain.Visible = true;
163 }
164 }
165 #endregion
166
167 #region Private Methods
168
169 /// <summary>
170 /// Creates the passed database if it doesn't already exist
171 /// </summary>
172 /// <param name="databaseName"></param>
173 private void CreateAndFillDatabase(string databaseName)
174 {
175 con = new SqlConnection("server=(localdb)\\MSSQLLocalDB; integrated security=sspi;");
176 cmd = new SqlCommand("select * from sys.databases;", con);
177
178 try
179 {
180 con.Open();
181 SqlDataReader reader = cmd.ExecuteReader();
182
183 if (reader.HasRows)
184 while (reader.Read())
185 if (reader.GetString(0).Equals(databaseName))
186 return;
187
188 con.Close();
189 }
190 catch (Exception ex)
191 {
192 MessageBox.Show(ex.ToString());
193 }
194
195 cmd = new SqlCommand("create database " + databaseName + ";", con);
196
197 try
198 {
199 con.Open();
200 cmd.ExecuteNonQuery();
201 con.Close();
202 }
203 catch (Exception ex)
204 {
205 MessageBox.Show(ex.ToString());
206 }
207
208 con.ConnectionString += " database=" + databaseName + ";";
209 cmd = new SqlCommand(File.ReadAllText("create tables.txt") + File.ReadAllText("insert intos.txt"), con);
210
211 try
212 {
213 con.Open();
214 cmd.ExecuteNonQuery();
215 con.Close();
216 }
217 catch (Exception ex)
218 {
219 MessageBox.Show(ex.ToString());
220 }
221 }
222
223 /// <summary>
224 /// Refresh the ListBox containing persons
225 /// </summary>
226 private void RefreshPersonList()
227 {
228 lstPerson.Items.Clear();
229 var dataset = (from p in db.Person
230 select new
231 {
232 p.FirstName,
233 p.Surname
234 }).ToList();
235
236 for (int i = 0; i < dataset.Count; i++)
237 lstPerson.Items.Add(dataset[i].FirstName + " " + dataset[i].Surname);
238 }
239
240 /// <summary>
241 /// Template event-method to be assigned by the designer
242 /// </summary>
243 /// <param name="sender"></param>
244 /// <param name="e"></param>
245 private void StuffChanged(object sender, EventArgs e)
246 {
247 btnSave.Enabled = true;
248 }
249 #endregion
250 }
251}
252
253/*
254Create tables lt. Textfile
255create table Person(
256PersonID int NOT NULL,
257FirstName VarChar(50),
258Surname VarChar(50),
259FavNumber int NOT NULL,
260PRIMARY KEY (PersonID));
261
262create table Animal(
263ID int NOT NULL,
264Name VarChar(50),
265PersonID int NOT NULL,
266PRIMARY KEY(ID),
267FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
268);
269
270
271Insert intos lt. Textfile
272insert into Person values(0, 'Geralt', 'von Rivia', 884);
273insert into Person values(1, 'Joe', 'Chill', 13);
274insert into Person values(2, 'Hella', 'Groß', 1);
275insert into Person values(3, 'Franz', 'Gruber', 47);
276insert into Person values(4, 'Theodor', 'Brise', 3);
277insert into Person values(5, 'Gregor', 'Haderer', 24);
278insert into Person values(6, 'Felix', 'Baumgartner', 8);
279insert into Person values(7, 'Laser', 'Johnson', 7);
280insert into Person values(8, 'Boy', 'Finch', 13);
281insert into Person values(9, 'Trevor', 'Hartmann', 2);
282
283insert into Animal values(0, 'Susi', 0);
284insert into Animal values(1, 'Sancho', 2);
285insert into Animal values(2, 'Gina', 1);
286insert into Animal values(3, 'Schnurry', 9);
287insert into Animal values(4, 'Zucchini', 2);
288*/
289
290using System;
291using System.Collections.Generic;
292using System.Linq;
293using System.Threading.Tasks;
294using System.Windows.Forms;
295
296namespace EntitiyFrameworkTest
297{
298 static class Program
299 {
300 [STAThread]
301 static void Main()
302 {
303 Application.EnableVisualStyles();
304 Application.SetCompatibleTextRenderingDefault(false);
305 Application.Run(new Form1());
306 }
307 }
308}