C # Tiered Development MySchool

The MySchool of layered development

The implementation of the login function, verify the user name and password. Match from the database to see if there is any data that meets the requirements.

Code is written at the DAL layer, and the return value is a Boolean type. The method parameter is (Student entity class object), and the parameterized SqlParameter type is used to prevent SQL injection.

 1 public bool IsLogin (Student stu) 2 {3 BOOL flag = FALSE; 4 SqlConnection con = new Sqlcon Nection (SQLHELPER.STR); 5 6 String sql = "SELECT COUNT (1) from student where [email protected] and [email protected]"; 7//02 The SQL statement to the SQL Server to perform 8 SqlParameter para1 = new SqlParameter ("@StudentName", Stu. STUDENTNO); 9 SqlParameter para2 = new SqlParameter ("@LoginPwd", Stu. LOGINPWD), SqlCommand cmd = new SqlCommand (Sql,con), one-to-one CMD. Parameters.Add (PARA1); Parameters.Add (PARA2); try14 {n-con. Open (); int count = Convert.ToInt32 (cmd).                ExecuteScalar ()); if (Count > 0): {= True;20            }21}22 catch (Exception ex) @ ex;26}27 finally28 {con. CloSe ();}31 return flag;32} 

In the BLL layer to create the DAL layer of the object, call the DAL layer of the login method, return values and parameters and the DAL layer login method consistent, return Dal.login (Stu)

1 public  bool IsLogin (Student stu) 2        {3            return DAL. IsLogin (Stu); 4        }

The method of the BLL layer is called at the UI layer, and the entity class object is created at the UI layer, and the object is placed in the method of the called BLL layer after assigning a value to the property of the entity class object. Determine if the landing is successful!

1 STUDENTBLL BLL = new STUDENTBLL (); 2             Student stu = new Student (); 3             Stu. Studentno = Convert.ToInt32 (txtName.Text); 4             Stu. Loginpwd = Txtpwd.text; 5             BOOL flag = BLL. IsLogin (Stu); 6             if (flag) 7             {8                 MessageBox.Show ("Login Successful"), 9                 frmlist list = new Frmlist ();                 Show ();             }12             else13             {                 MessageBox.Show ("Login Failed");             

No.2 Show student Information

Writing method return values at the DAL layer is a generic collection with no method arguments, using the tool class (SqlHelper Class) to convert the DataTable type to generic

1 public  list<student> Select () 2        {3            SqlConnection con = new SqlConnection (SQLHELPER.STR); 4            String sql = "SELECT * from student"; 5            DataTable dt = sqlhelper.executedatatable (sql), 6            my_tool Tool = new My_tool (); 7            list<student> List = Tool. datatabletolist<student> (DT); 8            return list; 9        }

In the BLL layer the same way the DAL layer is written

1 public  list<student> Select () 2        {3            return DAL. Select (); 4        }

The initialization of the UI layer in the Select method () binds data to DataGridView!

1  private void Frmlist_load (object sender, EventArgs e) 2         {3             Initial (); 4         } 5         public  list< student> list = new list<student> (); 6 Public         void Initial () 7         {8             studentbll BLL = new Studentbll (); 9             list= BLL. Select ();             this.dgvlist.DataSource = list;11         }

No.3 Implement add student information

The INSERT statement inserts data with many parameters, so we can declare an array of type SqlParameters, save the Code, the method return value is still a bool type, and the method parameter is an object of type student. Attention! When adding parameters to the Command object

To use the AddRange () Method!

 1 public bool Insert (Student stu) 2 {3 BOOL flag = FALSE; 4 SqlConnection con = new Sqlcon Nection (SQLHELPER.STR); 5 String sql = "INSERT into student values (@LoginPwd, @StudentName, @Gender, @GradeId, @Phone, @Address, @Birthday, @E Mail, @MyTT) "; 6 sqlparameter[] Para = 7 {8 new SqlParameter ("@LoginPwd", Stu. LOGINPWD), 9 new SqlParameter ("@StudentName", Stu. Studentname), ten new SqlParameter ("@Gender", Stu. Gender), one-new SqlParameter ("@GradeId", Stu. Gradeid), New SqlParameter ("@Phone", Stu. Phone), New SqlParameter ("@Address", Stu. Address), New SqlParameter ("@Birthday", Stu. Birthday), New SqlParameter ("@Email", Stu. Email), New SqlParameter ("@MyTT", Stu. MYTT)};18 SqlCommand cmd = new SqlCommand (Sql,con);   Parameters.addrange (para); 20 21         try22 {% con. Open (); int count = Convert.ToInt32 (cmd).                ExecuteScalar ()); if (Count > 0) (+) (flag = true;28)            }29}31 catch (Exception ex) + ex;35}36 finally37 {# con. Close ();}40 return flag;41}

The method call is also implemented in the BLL layer!

1 public  bool Insert (Student stu) 2        {3            return DAL. Insert (Stu); 4        }

Call the BLL layer method at the UI layer, assign values to the properties of the student class, and determine if student information is added successfully!

 1 STUDENTBLL BLL = new STUDENTBLL (); 2 Student stu = new Student (); 3 4 stu. Loginpwd = Txtpwd.text; 5 Stu. Studentname = txtName.Text; 6 if (rbtnfemale.checked) 7 {8 Stu. Gender = "0"; 9}10 else if (rbtnman.checked) one {stu.                 Gender = "1";}14 if (cboGrade.SelectedItem.ToString () = = "S1") 15 {16 Stu.                 Gradeid = 0;17}18 Else if (cboGrade.SelectedItem.ToString () = = "S2") 19 {20 Stu. Gradeid = 1;21}22 stu. Phone = txtphone.text;23 Stu. Address = txtaddress.text;24 Stu. Birthday = dpbirthday.value;25 Stu. Email = txtemail.text;26 this.listnew.Add (stu); bool flag= BLL.    Insert (stu); frm.dgvlist.DataSource = new bindinglist<student> (this.listnew); 29         This. Close ();

No.4 implements a two-level linkage of the ComboBox, when one of the drop-down boxes is selected, the other drop-down box automatically matches and is queried by filter criteria!

Write a method to read all grade drop-down boxes at the gradedal level, with a return value of generic set, no method parameter

1 public  list<grade> getgradenew () 2        {3            list<grade> List = new List<grade> (); 4            using (SqlConnection con = new SqlConnection (SQLHELPER.STR)) 5            {6                String sql = "SELECT * from Grade"; 7                Sqlco Mmand cmd = new SqlCommand (Sql,con); 8                con. Open (); 9                using (SqlDataReader dr = cmd. ExecuteReader ()) Ten                {one while                    (Dr. Read ())                        Grade ga = new Grade ();                        Gradeid = Convert.ToInt32 (dr["Gradeid"]);                        Gradename = dr["Gradename"]. ToString ();                        ADD (GA);                    }19                }20            }22            return list;23        }

Call this method in the BLL layer!

1 public  list<grade> getgradenew () 2        {3            return DAL. Getgradenew (); 4        }

Call this method in the UI layer, the Dispalymember property of the ComboBox is "Gradename", the ValueMember property is set to "Gradeid", the ComboBox's Dropdownheight property is set to 106, Avoid drop-down box redundancy

1 this.comboBox2.DropDownHeight = 106;2             This.comboBox1.DataSource = BLL. Getgradenew (); 3             this.comboBox1.ValueMember = "Gradeid"; 4             this.comboBox1.DisplayMember = "Gradename";

Write two methods in the DAL layer, the return value is generic, one method has a return value, the other is not, one method is to query all the accounts, and the other is to query the account according to the grade number

Public list<subject> Getsubjectname () {list<subject> List = new list<subject> (); using (SqlConnection con = new SqlConnection (sqlhelper.str)) {String sql = "SELECT * FROM Su               Bject ";               SqlCommand cmd = new SqlCommand (sql, con); Con.               Open (); using (SqlDataReader dr = cmd. ExecuteReader ()) {while (Dr.                       Read ()) {Subject sub = new Subject (); Sub.                       Subjectid = Convert.ToInt32 (dr["Subjectid"]); Sub. Subjectname = dr["Subjectname"].                       ToString (); Sub.                       Gradeid = Convert.ToInt32 (dr["Gradeid"]); Sub.                       Classhour = Convert.ToInt32 (dr["Classhour"]); List.                   ADD (sub);       }}} return list; } public list<subject> Getsubjectbygrade (int gradeid) {List<subject> list = new list<subject> ();  using (SqlConnection con = new SqlConnection (sqlhelper.str)) {String sql = "SELECT * FROM Subject               where gradeid= ' "+ Gradeid +" ' ";               SqlCommand cmd = new SqlCommand (sql, con); Con.               Open (); using (SqlDataReader dr = cmd. ExecuteReader ()) {while (Dr.                       Read ()) {Subject sub = new Subject (); Sub.                       Subjectid = Convert.ToInt32 (dr["Subjectid"]); Sub. Subjectname = dr["Subjectname"].                       ToString (); Sub.                       Gradeid = Convert.ToInt32 (dr["Gradeid"]); Sub.                       Classhour = Convert.ToInt32 (dr["Classhour"]); List.                   ADD (sub);       }}} return list; }

Calling methods in the BLL layer

1  subjectdal dal = new Subjectdal (), 2 public        list<subject> Getsubjectname () 3        {4            return DAL. Getsubjectname (); 5        } 6 public        list<subject> getsubjectbygrade (int gradeid) 7        {8            return DAL. Getsubjectbygrade (Gradeid); 9        }10 Public        list<subject> Getsubjectbyid (int subjectid) One        {            return DAL. Getsubjectbyid (Subjectid);        

Write code at the UI layer, note that if the order of the other events is triggered before the form Load event, the BOOL type variable is used to adjust the order, the value of flag is ture after the Load event is executed, if true, other events are executed, the Account drop-down box is added to a select, Use the generic Insert method to add it to the position labeled 0!

1  private void ComboBox1_SelectedIndexChanged (object sender, EventArgs e) 2         {3             if (flag) 4             {5             int n Um = Convert.ToInt32 (this.comboBox1.SelectedValue); 6             7             List < Subject > list= Sub. Getsubjectbygrade (num); 8             Subject ject = new Subject (); 9             ject. Subjectid = -1;10             ject. Subjectname = "Please select";             list. Insert (0, ject);             this.comboBox2.ValueMember = "Subjectid";             this.comboBox2.DisplayMember = " Subjectname ";             this.comboBox2.DataSource = list;15             }16         }

In the DAL layer to write methods, as a condition to query student performance, based on query conditions to set method parameters and SQL statements, to achieve a variety of fuzzy query under the conditions

1 Public DataTable GetList (string name, int id) 2        {3            using (SqlConnection con = new SqlConnection (SQLHELPER.STR)) 4            {5                String sql = "Select Studentname,subjectname,studentresult,examdate from Student,result,subject,grade where Subject.gradeid=grade. Gradeid and Result.studentno=student.studentno and Result.subjectid=subject.subjectid "; 6                if (id! =-1) 7                {8                    sql + = "and subject.subjectid= ' + ID +" ' "; 9                }10                if (!string. IsNullOrEmpty (name)) {+ + + +                    studentname= ' + name + "'";                }14                SqlDataAdapter da = new Sqld Ataadapter (sql, con); the                DataSet ds = new DataSet ();                Fill (ds, "SS"), and                return DS. tables["ss"];18            }19        }

Call this method in the BLL layer!

1 public    DataTable GetList (string name, int id) 2        {3            return DAL. GetList (name, id); 4        }

Call this method at the UI layer, assign a value to the method parameter, assign a value to the property of the entity class

1  private void Button1_Click (object sender, EventArgs e) 2         {3             string name = textbox1.text;4             int Subjectid = Convert.ToInt32 (Combobox2.selectedvalue); 5             DataTable dt = Re. GetList (name, Subjectid); 6             This.dataGridView1.DataSource = dt;7         }

C # Tiered Development MySchool