使用封装查询

先定义Student(Models), 存入数据类型

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Dotnet
{
    public class Student
    {
        public int StudentId { get; set; }
        public string StudentName { get; set; }
        public string Gender { get; set; }
        public DateTime Birthday { get; set; }
        public decimal StudentIdNo { get; set; }
        public int Age { get; set; }
        public string PhoneNumber { get; set; }
        public string StudentAddress { get; set; }
        public int ClassId { get; set; }    


    }
}

写与SQL通讯的工具函数, 包括ExecuteScalar, ExecuteReader, ExecuteNonQuery

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace Dotnet
{

    public class SQLHelper
    {
        private static string connString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=sasa";

        /// <summary>
        /// 返回单一结果的查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object result = cmd.ExecuteScalar();
            conn.Close();
            return result;
        }
        /// <summary>
        /// 更新操作（insert update delete）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Update(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            return result;
        }
        /// <summary>
        /// 返回一个结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }
}

再写封装函数

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace Dotnet
{
    public class StudentService
    {
        //private static string connString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=sasa";

        //对象的解析

        public int AddStudent(Student objStudent)
        {
            string sql = "insert into Students (StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)";
            sql += " values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7})";
            sql = string.Format(sql, objStudent.StudentName, objStudent.Gender, objStudent.Birthday,
             objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber, objStudent.StudentAddress, objStudent.ClassId);
            return SQLHelper.Update(sql);
        }
        public int GetStuCountByClassId(string classId)
        {
            string sql = "select count(*) from Students where ClassId=" + classId;
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql));
        }

        //public SqlDataReader GetStudentById(string stuId)
        //{
        //    string sql = "select StudentName,Gender,Birthday,PhoneNumber,StudentAddress,StudentIdNo from Students where StudentId=" + stuId;
        //    return SQLHelper.GetReader(sql);
        //}

        public Student GetStudentById(string stuId)
        {
            //string sql = "select StudentName,Gender,Birthday,PhoneNumber,StudentAddress,StudentIdNo from Students where StudentId=" + stuId;
            string sql = "select StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId from Students where StudentId=" + stuId;
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            //读取并封装对象
            Student objStudent = null; //创建延时
            //Student objStudent = new Student();
            if (objReader.Read())
            {
                objStudent = new Student();
                objStudent.StudentName = objReader["StudentName"].ToString();
                objStudent.Gender = objReader["Gender"].ToString();
                objStudent.Birthday = Convert.ToDateTime(objReader["Birthday"]);
                objStudent.StudentIdNo = Convert.ToDecimal(objReader["StudentIdNo"]);
                objStudent.StudentAddress = objReader["StudentAddress"].ToString();
            }
            objReader.Close();
            return objStudent;
        }

        public List<Student> GetAllStudents()
        {
            string sql = "select StudentId,StudentName,Gender,Age,Birthday,PhoneNumber,StudentAddress,StudentIdNo from Students ";
            SqlDataReader objReader = SQLHelper.GetReader(sql);   //执行查询
            List<Student> stuList = new List<Student>();
            while (objReader.Read())
            {
                //使用对象初始化器初始化对象并封装到集合
                stuList.Add(new Student()
                {
                    Age = Convert.ToInt32(objReader["Age"]),
                    Birthday = Convert.ToDateTime(objReader["Birthday"]),
                    Gender = objReader["Gender"].ToString(),
                    PhoneNumber = objReader["PhoneNumber"].ToString(),
                    StudentAddress = objReader["StudentAddress"].ToString(),
                    StudentId = Convert.ToInt32(objReader["StudentId"]),
                    StudentIdNo = Convert.ToDecimal(objReader["StudentIdNo"]),
                    StudentName = objReader["StudentName"].ToString()
                });
            }
            objReader.Close();
            return stuList;
        }



    }
}

主程序中实现

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace Dotnet
{
    class Program
    {
        static void Main(string[] args)
        {


            【1】获取用户输入
            Student stu = new Student();
            Console.WriteLine("请输入学员姓名：");
            stu.StudentName = Console.ReadLine();
            Console.WriteLine("请输入学员性别：");
            stu.Gender = Console.ReadLine();
            Console.WriteLine("请输入学员出生日期：");
            stu.Birthday = Convert.ToDateTime(Console.ReadLine());
            Console.WriteLine("请输入学员身份证号：");
            stu.StudentIdNo = Convert.ToDecimal(Console.ReadLine());
            Console.WriteLine("请输入学员年龄：");
            stu.Age = Convert.ToInt16(Console.ReadLine());
            Console.WriteLine("请输入学员电话：");
            stu.PhoneNumber = Console.ReadLine(); ;
            Console.WriteLine("请输入学员地址：");
            stu.StudentAddress = Console.ReadLine(); ;
            Console.WriteLine("请输入学员班级编号：");
            stu.ClassId = Convert.ToInt16(Console.ReadLine());
            //【2】调用数据访问方法

            StudentService objStudent = new StudentService();
            //int result = objStudent.AddStudent(
            //    stu.StudentName, stu.Gender, stu.Birthday, 
            //    stu.StudentIdNo, stu.Age, stu.PhoneNumber, stu.StudentAddress, stu.ClassId);

            int result = objStudent.AddStudent(stu);
            Console.WriteLine(result);
            Console.ReadKey();

            StudentService stuS = new StudentService();
            Student stu = stuS.GetStudentById("120223198905062426");

            Console.WriteLine(stu.StudentName);
            Console.ReadKey();


            StudentService objStuService = new StudentService();
            SqlDataReader objReader = objStuService.GetStudentById("100001");
            if (objReader.Read())
            {
                Console.WriteLine(objReader["StudentName"] + "   " + objReader["Gender"]);
            }
            objReader.Close();
            Console.Read();


            StudentService objStuService = new StudentService();
            Student objStu = objStuService.GetStudentById("100001");
            Console.WriteLine(objStu.Birthday + objStu.StudentName);
            Console.ReadKey();


            StudentService objStuService = new StudentService();
            List<Student> list = objStuService.GetAllStudents();

            foreach (Student item in list)
            {
                Console.WriteLine(item.StudentName + " "+item.Age+" "+ item.StudentId);
            }

            Console.ReadKey();

        }
    }
}