数据库应用系统 第二章上机1-4

 private void button1_Click(object sender, EventArgs e)
        {
            if (InsertStudent())
            {
                MessageBox.Show("新增成功","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
            }
            else {
                MessageBox.Show("新增失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            BinGrade();
        }
        public string st = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";
        public bool BinGrade()
        {

//创建数据连接
            第二章上机.DBHelper s = new 第二章上机.DBHelper();
            try
            {//查询年级SQL
                string sql = "SELECT * FROM [MySchool].[dbo].[Grade]";
                //打开数据库
                s.OpenConnection();
                //查询结果
                SqlCommand command = new SqlCommand(sql, s.Connection);
                SqlDataReader myReader = command.ExecuteReader();
                //写默认匡“请选择”
                this.comboBox1.Items.Add("请选择");
                this.comboBox1.SelectedIndex = 0;
                //获取grade表中的记录
                while (myReader.Read())
                {//获取年级名称
                    string gradeName = myReader["GradeName"].ToString();
                    //将年级名称添加到组合框中
                    comboBox1.Items.Add(gradeName);
                    //comboBox1.Items.Add(gradeName);
                }
                myReader.Close();
                return true;
            }
            catch (Exception e)
            {

                MessageBox.Show(e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }
            finally
            {
                s.CloseConnection();
            }

        }

        //public bool CheckInput()
        //{  if (this.textBox2.Text.Trim().Equals(string.Empty))
        //    {
        //        MessageBox.Show("请输入密码：", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        this.textBox2.Focus();
        //        return false;
        //    }
        //   else if (this.textBox4.Text.Trim().Equals(string.Empty))
        //    {
        //        MessageBox.Show("请输入姓名：", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        this.textBox4.Focus();
        //        return false;

        //    }
        //else if (this.panel1.Text.Trim().Equals(string.Empty))
        //    {
        //        MessageBox.Show("请输入性别：", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        this.panel1.Focus();
        //        return false;
        //    }
        //    else if (this.comboBox1.Text.Trim().Equals(string.Empty))
        //    {
        //        MessageBox.Show("请输入年级：", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        this.comboBox1.Focus();
        //        return false;
        //    }
        //else if (this.textBox7.Text.Trim().Equals(string.Empty))
        //{
        //    MessageBox.Show("请输入Email：", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //    this.textBox7.Focus();
        //    return false;
        //}
        //    else
        //    {
        //        return true;
        //    }
        //}

        public bool InsertStudent()
        {
            bool success = false;
            string id = this.textBox1.Text.Trim();
            string pwd = this.textBox2.Text.Trim();
            string name = this.textBox4.Text.Trim();
            int gender = 0;
            if (this.radioButton1.Checked)
            {

                gender = 0;
            }
            else
            {
                gender = 1;
            }
            int genderId = (int)gender;
            string grade = this.comboBox1.Text.Trim();

            string phone = this.textBox6.Text.Trim();

            string address = this.textBox8.Text.Trim();

            DateTime date = this.dateTimePicker1.Value;

            string email = this.textBox7.Text.Trim();

            string birthday = string.Format("{0}-{1}-{2}", date.Year, date.Month, date.Day);
            DBHelper s = new DBHelper();
            try
            {
                string sqlName = String.Format("select gradeId from grade where gradeName='{0}'", grade);

                s.OpenConnection();  

                SqlCommand command = new SqlCommand(sqlName, s.Connection); 

                int gradeId = (int)command.ExecuteScalar();
                StringBuilder sql = new StringBuilder();

                sql.AppendLine("INSERT INTO [Student] ([StudentNo],[LoginPwd],[StudentName],[Sex],[GradeId],[Phone],[Address],[BornDate],[Email])");
                sql.AppendFormat("VALUES ('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}')",id, pwd, name, genderId, gradeId, phone, address, birthday,email);
               //创建command对象
                MessageBox.Show(sql.ToString());
                command = new SqlCommand(sql.ToString(),s.Connection);
                int result = command.ExecuteNonQuery();
                if (result == 1)
                {
                    
                    //string sqlNo = "SELECT @@IDENTITY FROM [Student]";  null
                    //command.CommandText = sqlNo;
                    //int studentNo = Convert.ToInt32(command.ExecuteScalar());
                    //this.textBox1.Text = studentNo.ToString();
                    //MessageBox.Show("添加成功！");
                    success = true;
                    return success;
                }

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
              
                success = false;
            }
            finally
            {
                s.CloseConnection();
            }

            return success;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.textBox1.Text = "";
            this.textBox2.Text = "";
            this.textBox3.Text = "";
            this.textBox4.Text = "";
            this.textBox6.Text = "";
            this.textBox7.Text = "";
            this.textBox8.Text = "";
            this.comboBox1.Text = "";
           this. radioButton2.Text = "";
           this.radioButton1.Text = "";
            this.dateTimePicker1.Text = "";

        }