[Gibberish code pitfall series] ZJUT database large-scale experiment, pitfall zjut

[Gibberish code pitfall series] ZJUT database large-scale experiment, pitfall zjut
Student Management System (5) Data Binding

Student Management System (1) project establishment

Student Management System (2) Project Planning

Student Management System (3) database creation and login

Student Management System (4) Build Main Interface

 

It was only a day later, but I don't remember what I did before (by mistake.

After the main interface is completed, the page is edited. I chose TabControl for the main interface, so I can write the elements of each interface in different TabItem.

Write Data Binding first, and then talk about the UI part. To bind a table, use the simplest table Building as an example.

First, open SSMS and create several stored procedures.

USE EducationManagerGOCREATE PROCEDURE GetBuildingsASBEGIN    SELECT * FROM Building    ORDER BY 2, 1;ENDGOCREATE PROCEDURE AddBuilding(@bldgName nvarchar(6), @roomNum int)ASBEGIN    INSERT INTO Building    VALUES    ( @bldgName, @roomNum);ENDGOCREATE PROCEDURE DeleteBuilding(@bldgName nvarchar(6), @roomNum int)ASBEGIN    DELETE FROM Building    WHERE BldgName = @bldgName AND            RoomNum = @roomNum;ENDGO

Obtain, add, and delete rows respectively.

Return to VS. In the EducationManagerClass project, create a folder named Models and add a class Building in it.

 1     public class Building 2     { 3         public string BldgName { get; set; } 4  5         public int RoomNum { get; set; } 6  7         public Building(string bldgName, int roomNum) 8         { 9             BldgName = bldgName;10             RoomNum = roomNum;11         }12 13         public override string ToString()14         {15             return BldgName + " " + RoomNum;16         }17     }

Note that you must override the ToString () method. Otherwise, no data is displayed. This class is the model class of the table Building in the rectification project.

Then open the AppDbConnection class. Because all the attributes in the class must be set as parameters each time these stored procedures are used, I also wrote a help method SetSqlParamters, to add all the attributes of the model class to the SqlCommand parameter. The specific explanation of this method can be seen in the episode (1) of the series of spammers ).

1 // automatically sets the parameter value through reflection 2 private void SetSqlParameters <T> (SqlCommand cmd, T model) 3 where T: class 4 {5 foreach (PropertyInfo prop in 6 model. getType (). getProperties 7 (BindingFlags. instance | BindingFlags. public) 8 {9 cmd. parameters. addWithValue (10 "@" + prop. name, prop. getValue (model, null); 11} 12}

Then, all the methods corresponding to these three stored procedures use the Asynchronous Method to execute SQL operations. However, a problem occurs here: when using the OpenAsync method, I suddenly don't know where to jump, and the UI will crash, but no exception is reported ...... I am very grateful to you for telling me what I know. The number of rows returned by the Add and Delete methods is affected. Although it is useless, I still return the results. If there is a returned value, the results will be returned.

1 public async Task <ICollection <Building> GetBuildings () 2 {3 using (SqlConnection con = new SqlConnection (AppConnectionString) 4 {5 SqlCommand cmd = new SqlCommand ("GetBuildings ", con); 6 cmd. commandType = CommandType. storedProcedure; 7 ObservableCollection <Building> buildings = new ObservableCollection <Building> (); 8 9 // even if an exception is thrown because it is in the using statement block, con will also be killed, without manual Close () or Dispose () 10 con. open (); 11 SqlDataReader reader = await cmd. executeReaderAsync (); 12 while (reader. read () 13 {14 Building building = new Building15 (16 (string) reader ["BldgName"], 17 (int) reader ["RoomNum"] 18); 19 buildings. add (building); 20} 21 reader. close (); 22 23 return buildings; 24} 25} 26 27 public async Task <int> AddBuilding (Building building) 28 {29 using (SqlConnection con = new SqlConnection (AppConnectionString )) 30 {31 SqlCommand cmd = new SqlCommand ("AddBuilding", con); 32 cmd. commandType = CommandType. storedProcedure; 33 SetSqlParameters (cmd, building); 34 35 con. open (); 36 var lines = await cmd. executeNonQueryAsync (); 37 return lines; 38} 39} 40 41 public async Task <int> DeleteBuilding (Building building) 42 {43 using (SqlConnection con = new SqlConnection (AppConnectionString )) 44 {45 SqlCommand cmd = new SqlCommand ("DeleteBuilding", con); 46 cmd. commandType = CommandType. storedProcedure; 47 SetSqlParameters (cmd, building); 48 49 con. open (); 50 var lines = await cmd. executeNonQueryAsync (); 51 return lines; 52} 53}

At this point, the methods for database operations have been completed, which is also very simple, and there seems to be nothing to explain.

 

Then go to the UI section.

First, we need to be able to view all the data. At the same time, it is unfriendly to simply use ListView to list all the classrooms, so we need to use TreeView; we also need an operation area with buttons and input boxes to operate data.

Select the Classroom Management tab, add a Grid on it, and divide the Grid into the left and right parts. Put TreeView on the left and operation area on the right.

　　

Separate the creation and details to ensure that they do not interfere with each other. In addition, you must add the TreeView template to the xaml Code to ensure that the data can be correctly displayed by group.

 1     <Window.Resources> 2         <HierarchicalDataTemplate x:Key="tvit" ItemsSource="{Binding}"> 3             <TextBlock Text="{Binding Key}"/> 4             <HierarchicalDataTemplate.ItemTemplate> 5                 <DataTemplate> 6                     <TextBlock Text="{Binding}"/> 7                 </DataTemplate> 8             </HierarchicalDataTemplate.ItemTemplate> 9         </HierarchicalDataTemplate>10     </Window.Resources>11 12     <!--Other code-->13 14     <TreeView Name="buildingTree" Grid.Column="0"15             ItemTemplate="{StaticResource ResourceKey=tvit}" >16     </TreeView>  

Also, the two TextBox boxes in the details box need to bind data and set the Binding Path to the attribute name corresponding to the Building class to automatically display the corresponding information.

<Label Content = "Building Name" HorizontalAlignment = "Left" Width = "70" Height = "25" verticalignment = "Top" Margin = "0, 38, 0, 0 "/> <TextBox Name =" bldgNameTextBox "Margin =" 75, 38, "Text =" {Binding Path = BldgName} "Height =" 25 "verticalignment =" Top "> </TextBox> <Label Content =" Classroom no. "Margin =, 0, 0 "HorizontalAlignment =" Left "Width =" 70 "Height =" 28 "verticalignment =" Top "/> <TextBox Name =" roomNumTextBox "Margin =" 75,68, 10, 0 "Text =" {Binding Path = RoomNum} "Height =" 28 "verticalignment =" Top "> </TextBox>

 

Enter the background code.

Before writing, if you use VS 2015, you can add using static EductionManagerUI at the beginning. in this way, you can directly use the AppDbConnection object in the App class without adding the App..

Data needs to be loaded when the program starts, and the methods for loading data are asynchronous. The async keyword cannot be added before the constructor. Therefore, you need to write a separate method for initializing data. The return value of this method must be async void rather than Task. Otherwise, a warning will appear in the called place (in the constructor. Because the return value is void, exceptions must be captured in this method. At the same time, a site is also required to store the information obtained from the database for later operations and processing.

1 // store the Building property 2 private ICollection <Building> Buildings {get; set;} 3 4 /// <summary> 5 // initialize the data, async void is used, so catch 6 // </summary> 7 private async void InitData () 8 {9 try10 {11 Buildings = await AppDbconnection must be used. getBuildings (); 12 // Other initialization... 13} 14 catch15 {16 MessageBox. show ("Data initialization exception"); 17 Application. current. shutdown (); 18} 19 buildingTree. itemsSource = Buildings. groupBy (B => B. bldgName); 20}

Note that the ItemsSource value of the TreeView should be the result of grouping Buildings so that it can be correctly displayed hierarchically.

 

Then begin to write the event of the UI element.

There are several main events: select the SelectedItemChanged event of the TreeView to display the corresponding information when the selected row changes; Click events of the add or delete button, the two are respectively calling the corresponding methods in AppDbConnection and updating the UI. There is also a Click event of the reset button, which is to clear the values in the details box after clicking.

In addition, async void is used in the incident, not the Task. This is not an option. I also want to be a good guy, but the judge will not let me escape.

First, let's take a look at the TreeView event. when updating the value, we must first judge whether the selected element is a child element. If yes, it will be displayed. Otherwise, we will pretend to look around for the scenery.

1 private void buildingTree_SelectedItemChanged (object sender, RoutedPropertyChangedEventArgs <Object> e) 2 {3 if (buildingTree. selectedValue. getType () = typeof (Building) 4 {5 var building = (Building) buildingTree. selectedItem; 6 try 7 {8 gridBuildingDetails. dataContext = building; 9} 10 catch11 {12 MessageBox. show ("A strange error occurred"); 13} 14} 15}

The exception catch part can be changed to the output log, so I am too lazy to write it, So I directly use MessageBox (......

The events for adding and deleting buttons are similar, but the data sources are different. After modification, you can use the two methods to return the affected rows to determine whether the operation is successful, however, this is not necessary, so I am too lazy. Haha, at the same time, it is best to re-obtain the data in the database and reset the ItemsSource OF THE TreeView to refresh the interface. There should be a way to implement the update without refreshing, but it will not be done if it feels a little troublesome. The idea is to change the Buildings type to the post-grouping type, in this case, it is a little troublesome to update and delete anything. The reset button event is automatically cleared only by setting the bound information to null.

1 private async void bldgAddButton_Click (object sender, RoutedEventArgs e) 2 {3 int roomNum; 4 if (int. tryParse (newRoomNumTextBox. text, out roomNum) 5 {6 var building = new Building (newBldgNameTextBox. text, roomNum); 7 try 8 {9 await AppDbconnection. addBuilding (building); 10 Buildings = await AppDbconnection. getBuildings (); 11 buildingTree. itemsSource = Buildings. groupBy (B => B. bldgName); 12} 13 catch14 {15 MessageBox. show ("failed to add"); 16} 17} 18 else19 {20 MessageBox. show ("Incorrect classroom number"); 21} 22} 23 24 private async void bldgDelButton_Click (object sender, RoutedEventArgs e) 25 {26 var building = (Building) gridBuildingDetails. dataContext; 27 if (building = null) 28 return; 29 30 try31 {32 await AppDbconnection. deleteBuilding (building); 33 Buildings. remove (building); 34 buildingTree. itemsSource = Buildings. groupBy (B => B. bldgName); 35 gridBuildingDetails. dataContext = null; 36} 37 catch38 {39 MessageBox. show ("failed to delete"); 40} 41} 42 43 private void bldgResetButton_Click (object sender, RoutedEventArgs e) 44 {45 gridBuildingDetails. dataContext = null; 46}

Here, the catch can also be added with log output. I personally recommend this, rather than a message box (serious face) like this.

Yes, you can!

To be honest, if you press the keyboard so loud at night, you are afraid of being cut by roommates.

Well, Git: Github \ LzxHahaha \ EducationManager