7、调用数据库（DA、Data）

//新建App_Code文件夹
//新建studentDA文件
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ConnSqlForm.App_Code
{
    public class StudentDA
    {
        private SqlConnection Conn;
        private SqlCommand Cmd;

        public StudentDA()
        {
            Conn = new SqlConnection("server=.;database=school;user=sa;pwd=123");
            Cmd = Conn.CreateCommand();
        }

        public int InsertData(StudentData data)
        {
            Cmd.CommandText = "insert into student values(@xuehao,@name,@sex,@birthday)";
            Cmd.Parameters.Clear();
            Cmd.Parameters.Add("@xuehao",data.Xuehao);
            Cmd.Parameters.Add("@name", data.Name);
            Cmd.Parameters.Add("@sex", data.Sex);
            Cmd.Parameters.Add("@birthday", data.Birthday);

            Conn.Open();
            int count = Cmd.ExecuteNonQuery();
            Conn.Close();
            Cmd.Dispose();
            return count;
        }

        public int UpdateData(StudentData data)
        {
            Cmd.CommandText = "update student set name=@name,sex=@sex,birthday=@birthday where xuehao=@xuehao";
            Cmd.Parameters.Clear();
            Cmd.Parameters.Add("@xuehao", data.Xuehao);
            Cmd.Parameters.Add("@name", data.Name);
            Cmd.Parameters.Add("@sex", data.Sex);
            Cmd.Parameters.Add("@birthday", data.Birthday);

            Conn.Open();
            int count = Cmd.ExecuteNonQuery();
            Conn.Close();
            Cmd.Dispose();
            return count;
        }

        public int DeleteData(StudentData data)
        {
            Cmd.CommandText = "delete from student where xuehao=@xuehao";
            Cmd.Parameters.Clear();
            Cmd.Parameters.Add("@xuehao", data.Xuehao);
            Conn.Open();
            int count = Cmd.ExecuteNonQuery();
            Conn.Close();
            Cmd.Dispose();
            return count;
        }
        public int DeleteData(string  xuehao)
        {
            Cmd.CommandText = "delete from student where xuehao=@xuehao";
            Cmd.Parameters.Clear();
            Cmd.Parameters.Add("@xuehao", xuehao);
            Conn.Open();
            int count = Cmd.ExecuteNonQuery();
            Conn.Close();
            Cmd.Dispose();
            return count;
        }


        public DataTable selectStudent()
        {
            DataTable dt = new DataTable();
            Cmd = Conn.CreateCommand();
            Cmd.CommandText = "select *From student";
            Conn.Open();
            SqlDataReader dr = Cmd.ExecuteReader();
            for (int i = 0; i < dr.FieldCount; i++)
            {
                dt.Columns.Add(dr.GetName(i));
            }

            while (dr.Read())
            {
                DataRow row = dt.NewRow();
                row["xuehao"] = dr["xuehao"];
                row["name"] = dr["name"];
                row["sex"] = dr["sex"];
                row["birthday"] = dr["birthday"];
                dt.Rows.Add(row);
            }
            Conn.Close();
            return dt;
        }

        public List<StudentData> selectListStudent()
        {
            List<StudentData> list = new List<StudentData>();
            Cmd = Conn.CreateCommand();
            Cmd.CommandText = "select *From student";
            Conn.Open();
            SqlDataReader dr = Cmd.ExecuteReader();
            while (dr.Read())
            {
                StudentData sdata = new StudentData();
                sdata.Xuehao = dr["xuehao"].ToString();
                sdata.Name = dr["name"].ToString();
                sdata.Sex = dr["sex"].ToString();
                sdata.Birthday = DateTime.Parse(dr["birthday"].ToString());
                list.Add(sdata);
            }
            Conn.Close();
            return list;
        }

        public StudentData selectStudentbyid(string xuehao)
        {
            StudentData sdata = null;
            Cmd = Conn.CreateCommand();
            Cmd.CommandText = "select *From student where xuehao="+xuehao;
            Conn.Open();
            SqlDataReader dr = Cmd.ExecuteReader();

            if (dr.Read())
            {
                sdata = new StudentData();
                sdata.Xuehao = dr["xuehao"].ToString();
                sdata.Name = dr["name"].ToString();
                sdata.Sex = dr["sex"].ToString();
                sdata.Birthday = DateTime.Parse(dr["birthday"].ToString());
            }
            Conn.Close();
            return sdata;
        }

        public DataTable selectStudent(string name)
        {
            DataTable dt = new DataTable();
            Cmd = Conn.CreateCommand();
            Cmd.CommandText = "select *From student where name like ‘%@name%‘";
            Cmd.Parameters.Clear();
            Cmd.Parameters.Add("@name",name);
            Conn.Open();
            SqlDataReader dr = Cmd.ExecuteReader();
            for (int i = 0; i < dr.FieldCount; i++)
            {
                dt.Columns.Add(dr.GetName(i));
            }

            while (dr.Read())
            {
                DataRow row = dt.NewRow();
                row["xuehao"] = dr["xuehao"];
                row["name"] = dr["name"];
                row["sex"] = dr["sex"];
                row["birthday"] = dr["birthday"];
                dt.Rows.Add(row);
            }
            Conn.Close();
            return dt;
        }
    }
}
//新建studentData文件
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConnSqlForm.App_Code
{
    public class StudentData
    {
        private string xuehao;

        public string Xuehao
        {
            get { return xuehao; }
            set { xuehao = value; }
        }
        private string name;

        public string Name
        {
            get { return name; }
            set { name = value; }
        }
        private string sex;

        public string Sex
        {
            get { return sex; }
            set { sex = value; }
        }
        private DateTime birthday;

        public DateTime Birthday
        {
            get { return birthday; }
            set { birthday = value; }
        }
    }
}

//调用App_Code新增数据
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ConnSqlForm.App_Code;

namespace ConnSqlForm
{
    public partial class Form5 : Form
    {
        public Form5()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            StudentData sdata = new StudentData();
            sdata.Xuehao = txtxuehao.Text;
            sdata.Name = txtname.Text;
            if (rbtnan.Checked)
                sdata.Sex = "男";
            else
                sdata.Sex = "女";
            sdata.Birthday = dateTimePicker1.Value;

            new StudentDA().InsertData(sdata);
        }
    }
}

//调用App_Code修改数据库
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ConnSqlForm.App_Code;

namespace ConnSqlForm
{
    public partial class Form6 : Form
    {
        public Form6()
        {
            InitializeComponent();
        }
        private StudentData SData;
        public Form6(string xuehao)
        {
            InitializeComponent();
            SData = new StudentDA().selectStudentbyid(xuehao);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            StudentData data = new StudentData();
            data.Xuehao = txtxuehao.Text;
            data.Name = txtname.Text;
            if (rbtnan.Checked)
                data.Sex = "男";
            else
                data.Sex = "女";
            data.Birthday = dateTimePicker1.Value;

            new StudentDA().UpdateData(data);
        }

        private void Form6_Load(object sender, EventArgs e)
        {
            txtname.Text = SData.Name;
            txtxuehao.Text = SData.Xuehao;
            if (SData.Sex == "男")
                rbtnan.Checked = true;
            else
                rbtnv.Checked = true;
            dateTimePicker1.Value = SData.Birthday;
        }
    }
}