WorldSkills: Review from the participant of the Olympiad
Hello, Habr!
Every programmer wants to pump his skills and every company wants to see qualified specialists, but how to achieve this? Olympiads come to the rescue, this article will be about participation in one of them.
Retreat
The article turned out to be voluminous, so all the code and most of the images are under the spoilers. Still testing the article submission format. If this format seems uncomfortable to you, please write about it. Also, all pictures are clickable.
About the contest
WorldSkills is a competition whose goal is to identify professionals in a specific field. The competition takes its roots with WorldSkills International (WSI), the international non-profit association.
')
Participation in the competition is free. The age of participants is from 18 to 28 years old, students in colleges or universities.
The competition at the university lasts 5 days: on the first day, the opening and verification of jobs, then three days of competition, on the final day - summing up and closing.
About competency selection and training
I learned about the Olympics by chance during one of the practices at the university in the 2nd year. I was invited to participate in the competence “Development of software solutions for business”. To solve problems, knowledge in C # or Java, working with a database, and as I learned during the first Android Olympics, were required. The opening of the Olympics was scheduled for early June, and the window was already the end of April. At that moment I knew absolutely nothing that was required.
My surprise knew no bounds when all potential participants were gathered for training. There were 7 people in the audience, 6 of whom were already third-year students, the seventh was me. Why was I so surprised? In the third year, students are taught a course on Databases, which lasts 2 semesters, which means that everyone had more than a year of practice. I didn’t want to refuse, so I asked the teachers for a book on DB, found a C # course on the Internet and began to prepare, periodically appearing in preparatory classes.
Two weeks before the start of the Olympiad, we learned that no more than three people can participate from our department. Passed the qualifying round. In 1.5 hours, we had to create a database in the MSSQL database management system using the ER-model, import data from an Excel file and show it in the application. In a word, I was allowed to participate in the Olympiad from our department.
Now about the competition
The first day of the competition or “C -1”
The long-awaited discovery took place on this day. All participants were gathered in the assembly hall, where they told about the history of WorldSkills, competencies and introduced all the experts with the participants.
After this ceremony, everyone went to their sites to check the equipment. A "dedication ritual" was held, in which we chose our jobs and signed for safety. At that time, I did not know what I could check in the software, so immediately after the draw I left to prepare for the next day.
The second day of the competition or the beginning of the competition
What was the Olympics?
For our competency, in 2 days (three for other competencies), for 6 hours, with breaks, we were required to write a client-server application in C # / Java with queries to the database. More precisely, to say "all that we have time to write," since the principle of the Olympiad is "do what you can and how you can."
According to experts, the criteria were several tens of A4 pages. Criteria are issued only after the end of the session and only to experts, so I can not say anything about them.
Let's move on to the events of the second day. For the first session it was required to implement the following:
- Create a database using a well-known ER-diagram
- Import data from Excel files to the database
- Create 4 screens for layouts in presentation
And for the second make up another five screens for layouts.
In short, all the tasks were solved, except for displaying pictures from the database and creating a custom list of elements.
A few screenshots:
The interface allows you to find players by name (even the first letter), season and team. By double-clicking on an image that has not been implemented, detailed information about the player is opened. Now I would implement it through DataGridViewImageColumn.
How I saved images
private void download_Click(object sender, EventArgs e) { SaveFileDialog saveFile = new SaveFileDialog(); saveFile.DefaultExt = ".jpg"; saveFile.AddExtension = true; // - / saveFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.CommonPictures); saveFile.Filter = "Bitmap files (*.bmp)|*.bmp|Image files (*.jpg;*.png)|*.jpg;*.png"; if (saveFile.ShowDialog() == DialogResult.OK) { if (!saveFile.FileName.Equals("")) { String[] arr = saveFile.FileName.Split('.'); // "\" String filepath = arr[0].Substring(0, arr[0].LastIndexOf('\\') + 1); for (int i = 1; i <= imageList1.Images.Count; i++) { Image image = Image.FromFile("..\\Pictures\\" + (i + offset) + ".jpg"); image.Save(filepath + i + "." + arr[1]); } } } } More screenshots, text and code
The interface is the main screen, allowing you to select the role of "User" or "Administrator", as well as watch the best moments from the matches. How to place the elements in the center could not find.
Interface for authorization of technical administrators and match organizers, allowing you to remember the last entered username and password. In my case, I wrote data to a file on the hard drive without encryption.
And this is how the unfinished screen looks, here I just threw components on the form according to the layout. Can you please tell me how to make such a custom list in C #?
The most difficult screen for me, since there was work with charts for which I was not preparing.
The interface is the main screen, allowing you to select the role of "User" or "Administrator", as well as watch the best moments from the matches. How to place the elements in the center could not find.
Image upload implementation
private int countImages() { sqlConnection = new SqlConnection(connectionString); using (sqlConnection) { sqlConnection.Open(); String sqlcomm = "SELECT Count(*) FROM [Pictures$]"; SqlCommand command = new SqlCommand(sqlcomm, sqlConnection); int result = (int)command.ExecuteScalar(); return result; } } private void loadPage() { sqlConnection = new SqlConnection(connectionString); using (sqlConnection) { sqlConnection.Open(); String sqlcomm = "SELECT [Img] , [CreateTime] FROM [Pictures] P" + " Order By P.CreateTime Desc" + " Offset @click Rows FETCH NEXT @svm ROWS ONLY"; SqlCommand cmd = new SqlCommand(sqlcomm, sqlConnection); cmd.Parameters.AddWithValue("@click", click); cmd.Parameters.AddWithValue("@svm", svm); dataAdapter.SelectCommand = cmd; DataSet data = new DataSet(); dataAdapter.Fill(data); imageList1.Images.Clear(); listView1.Clear(); for (int i = 0; i < data.Tables[0].Rows.Count; i++) { imageList1.Images.Add(Image.FromFile("..\\Pictures\\" + data.Tables[0].Rows[i].ItemArray[0].ToString())); } listView1.LargeImageList = imageList1; for (int i = 0; i < imageList1.Images.Count; i++) listView1.Items.Add("").ImageIndex = i; } } private void left_Click(object sender, EventArgs e) { if (click > 0) { if (!right.Enabled) { right.Enabled = true; } click -= svm; loadPage(); } else { left.Enabled = false; } } private void right_Click(object sender, EventArgs e) { if (click + svm < totalPhotos) { if (!left.Enabled) { left.Enabled = true; } click += svm; loadPage(); } else { right.Enabled = false; } } Interface for authorization of technical administrators and match organizers, allowing you to remember the last entered username and password. In my case, I wrote data to a file on the hard drive without encryption.
Checking the existence of the entered username and password in the database
If you know the best way to verify the data, please write in the comments.
private int checkData(string jobnumber, string password) { // SQL-Injection String sqlchecked = "if (Isnull((Select RoleId From Admin$ Where Jobnumber like(@Jobnumber) and Passwords like(@Password)) , 0) = 0)" + " Select 0 " + " else " + " Select RoleId From Admin$ Where Jobnumber like(@Jobnumber) and Passwords like(@Password)"; sqlConnection = new SqlConnection(connectionString); int result = 0; using (sqlConnection) { sqlConnection.Open(); SqlCommand command = new SqlCommand(sqlchecked, sqlConnection); // command.Parameters.AddWithValue("@Jobnumber", jobnumber); command.Parameters.AddWithValue("@Password", password); // result = (int)command.ExecuteScalar(); } return result; } If you know the best way to verify the data, please write in the comments.
And this is how the unfinished screen looks, here I just threw components on the form according to the layout. Can you please tell me how to make such a custom list in C #?
The most difficult screen for me, since there was work with charts for which I was not preparing.
The third day of the competition or an unexpected meeting with Android
The previous day could be considered a warm-up compared to the third. At 3 sessions, another 8 screens were to be credited. And at the final session there was a change of plans, and instead of presenting the developed product, we started making a simplified version for Android. Namely, a gallery with pictures uploaded from the database. Now it sounds easy, but at that moment I was glad that the session was given 15 minutes to access the Internet. In 3 hours, a GridView was created with elements in the form of ImageView, an array of id images was transferred to the Adapter, and the OnItemClickListener interface was redefined to create a new Activity with a picture.
Some more screenshots:
The interface for the administrator, allowing you to view information on the players. The most useless screen, in my opinion, since you can’t make changes.
|
| 
|My “perfect” gallery in 3 hours using the Internet and without programming skills for Android.
More images, text and code
Clicking on a season (right table) opens detailed information about all matches.
An interface for administrators that allows you to make changes to the list of commands and export data to Excel.
Code for creating a gallery when you do not know how to work with the database
class MainActivity : AppCompatActivity() { override fun onCreate(savedInstanceState: Bundle?) { super.onCreate(savedInstanceState) setContentView(R.layout.activity_main) setSupportActionBar(toolbar) // id var images = ArrayList<Int>() images.add(R.drawable.a1) //... images.add(R.drawable.a12) listView.adapter = CustomAdapter(images.toTypedArray()) listView.onItemClickListener = AdapterView.OnItemClickListener { _: AdapterView<*>, _: View, i: Int, _: Long -> var intent = Intent(this, FullscreenActivity::class.java) // id , intent.putExtra("id",images[i]) startActivity(intent) } } } class CustomAdapter internal constructor(var images: Array<Int>) : BaseAdapter() { override fun getCount(): Int { return images.size } // override fun getItem(position: Int): Int { return images[position] } // id override fun getItemId(position: Int): Long { return position.toLong() } // override fun getView(position: Int, convertView: View?, parent: ViewGroup): View { // var view: View? = convertView // , if (view == null) { val inflater = LayoutInflater.from(parent.context) view = inflater.inflate(R.layout.item, parent, false) } view!!.findViewById<ImageView>(R.id.imageView).setImageResource(images[position]) return view } } class FullscreenActivity : AppCompatActivity() { override fun onCreate(savedInstanceState: Bundle?) { super.onCreate(savedInstanceState) setContentView(R.layout.activity_fullscreen) // id var id = intent.getIntExtra("id",-1) imageView2.setImageResource(id) } } Clicking on a season (right table) opens detailed information about all matches.
An interface for administrators that allows you to make changes to the list of commands and export data to Excel.
How I exported data to Excel
private void exportExcel_Click(object sender, EventArgs e) { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel (*.xls)|*.xls|All files (*.*)|*.*"; saveFileDialog.DefaultExt = ".xls"; if (saveFileDialog.ShowDialog() != DialogResult.OK) return; if (saveFileDialog.FileName.Equals("")) return; DataTable dt = (DataTable)dataGridView2.DataSource; // StreamWriter wr = new StreamWriter(saveFileDialog.FileName); try { for (int i = 0; i < dt.Columns.Count; i++) { wr.Write(dt.Columns[i].ToString().ToUpper() + "\t"); } wr.WriteLine(); // Excel for (int i = 0; i < (dt.Rows.Count); i++) { for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Rows[i][j] != null) { wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t"); } else { wr.Write("\t"); } } // wr.WriteLine(); } // wr.Close(); } catch (Exception ex) { MessageBox.Show(this, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } Summarizing
At 12 o’clock, a closing ceremony was held, where all participants were awarded certificates, and medals with diplomas awaited winners and prize-winners. In our competence, I took 2nd place. Between the rewarding of competencies, students performed with songs and dances.
Conclusion
During the month of preparation, I mastered the DDL and DML SQL commands, which greatly simplified the work on DB pairs in the third year. The knowledge gained in C # and Windows Form leaves much to be desired, except for working with databases and the user interface, I did not have to work with anything.
This year I also took part in the WorldSkills Olympiad and took 1st place, but about this, the complicated tasks and the consequences of turning off the Internet on the sites in the next article.
Source: https://habr.com/ru/post/460819/
All Articles