C#+数据库

<connectionStrings>
    <add name="connStr" connectionString="server=.;database=StudentDB;uid=lyc;pwd=123456" providerName="System.Data.SqlClient"/>
  </connectionStrings>

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

namespace WinStudent.DAL
{
    /// <summary>
    /// 班级数据访问类
    /// </summary>
    public class ClassDAL
    {
        /// <summary>
        /// 判断该班级是否已存在
        /// </summary>
        /// <param name="className"></param>
        /// <param name="gradeId"></param>
        /// <returns></returns>
        public bool Exists(string className, int gradeId, int classId)
        {
            string sqlQ = "select count(1) from ClassInfos where ClassName=@ClassName and GradeId=@GradeId";
            if (classId > 0)
            {
                sqlQ += " and ClassId<>@ClassId";
            }
            SqlParameter[] parasQ =
            {
                new SqlParameter("@ClassName",className),
                new SqlParameter("@GradeId",gradeId),
                new SqlParameter("@ClassId",classId)
            };
            
            object o = DBHelper.ExecuteScalar(sqlQ, 1, parasQ);
            if (o == null && o == DBNull.Value)
                return false;
            else if ((int)o > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 添加班级信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddClass(ClassInfoModel model)
        {
            string sql = "insert into ClassInfos(ClassName,GradeId,Remark) " +
                         "values(@ClassName,@GradeId,@Remark)";

            SqlParameter[] paras =
            {
                new SqlParameter("@ClassName",model.ClassName),
                new SqlParameter("@GradeId",model.GradeId),
                new SqlParameter("@Remark",model.Remark)
            };
            return DBHelper.ExecuteNonQuery(sql, 1, paras) > 0;
        }


        /// <summary>
        /// 根据条件 查询班级列表
        /// </summary>
        /// <param name="gradeId"></param>
        /// <param name="cName"></param>
        /// <returns></returns>
        public List<ClassGradeModel> GetClasses(int gradeId, string cName)
        {
            string sql = "select ClassId,ClassName,c.GradeId,GradeName,Remark " +
                "from ClassInfos c inner join GradeInfos g " +
                "on c.GradeId=g.GradeId " +
                "where 1=1";
            if (gradeId > 0)
                sql += " and c.GradeId=@gradeId";
            if (!string.IsNullOrEmpty(cName))
                sql += " and ClassName like @cName";
            sql += " order by ClassId";

            SqlParameter[] paras =
            {
                new SqlParameter("@gradeId",gradeId),
                new SqlParameter("@cName","%"+cName+"%")
            };

            DataTable dtClass = DBHelper.GetDataTable(sql, 1, paras);
            //将DataTable转换为List
            return TableToList(dtClass);
        }

        /// <summary>
        /// 将DataTable转换为List
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private List<ClassGradeModel> TableToList(DataTable dt)
        {
            List<ClassGradeModel> list = new List<ClassGradeModel>();
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    ClassGradeModel model = new ClassGradeModel();
                    if (!string.IsNullOrEmpty(dr["ClassId"].ToString()))
                        model.ClassId = (int)dr["ClassId"];
                    model.ClassName = dr["ClassName"].ToString();
                    if (!string.IsNullOrEmpty(dr["GradeId"].ToString()))
                        model.GradeId = (int)dr["GradeId"];
                    model.GradeName = dr["GradeName"].ToString();
                    model.Remark = dr["Remark"].ToString();

                    list.Add(model);
                }
            }
            return list;
        }


        /// <summary>
        /// 获取班级列表 Dictionary（主要应用于 学生信息页面的下拉列表）
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetClassList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();

            string sql = "select ClassId,ClassName,GradeName from " + "ClassInfos c " +
                       "inner join GradeInfos g " +
                       "on c.GradeId=g.GradeId " +
                       "order by ClassId";
            DataTable dt_Class = DBHelper.GetDataTable(sql, 1);

            if (dt_Class != null && dt_Class.Rows.Count > 0)
            {
                foreach (DataRow dr in dt_Class.Rows)
                {
                    if (!string.IsNullOrEmpty(dr["ClassId"].ToString()))
                    {
                        int classId = (int)dr["ClassId"];
                        string className = dr["ClassName"].ToString();
                        string gradeName = dr["GradeName"].ToString();
                        list.Add(classId, gradeName + " " + className);
                    }

                }
            }
            return list;


        }

        /// <summary>
        /// 获取班级列表 List（主要应用于 学生列表页面的下拉列表）
        /// </summary>
        /// <returns></returns>
        public List<ClassInfoModel> GetClassListNew()
        {
            List<ClassInfoModel> list = new List<ClassInfoModel>();

            string sql = "select ClassId,ClassName,GradeName from ClassInfos c " +
                       "inner join GradeInfos g " +
                       "on c.GradeId=g.GradeId " +
                       "order by ClassId";
            DataTable dt_Class = DBHelper.GetDataTable(sql, 1);

            if (dt_Class != null && dt_Class.Rows.Count > 0)
            {
                foreach (DataRow dr in dt_Class.Rows)
                {
                    if (!string.IsNullOrEmpty(dr["ClassId"].ToString()))
                    {
                        ClassInfoModel model = new ClassInfoModel();
                        model.ClassId = (int)dr["ClassId"];
                        model.ClassName = dr["ClassName"].ToString() + " " + dr["GradeName"].ToString();
                        list.Add(model);
                    }

                }
            }
            return list;


        }
    }
}

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WinStudent.Helper;

namespace WinStudent.DAL
{
    /// <summary>
    /// 年级数据访问类
    /// </summary>
    public class GradeDAL
    {
        /// <summary>
        /// 获取所有年级信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetGrades()
        {
            string sql = "select GradeId,GradeName from GradeInfos";
            return DBHelper.GetDataTable(sql, 1);
        }
    }
}

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

namespace WinStudent.DAL
{
    /// <summary>
    /// 学生数据访问类
    /// </summary>
    public class StudentDAL
    {
        /// <summary>
        /// 判断学生是否已存在
        /// </summary>
        /// <param name="stuName"></param>
        /// <param name="phone"></param>
        /// <returns></returns>
        public bool Exists(string stuName,string phone)
        {
            string sql = "select count(1) from StudentInfos where StuName=@StuName and Phone=@Phone";
            SqlParameter[] paras =
            {
                new SqlParameter("@StuName",stuName),
                new SqlParameter("@phone",phone)
            };

            object o = DBHelper.ExecuteScalar(sql, 1, paras);
            if (o == null && o == DBNull.Value)
                return false;
            else if ((int)o > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 添加学生信息
        /// </summary>
        /// <param name="student"></param>
        /// <returns></returns>
        public bool AddStudent(StudentInfoModel student)
        {
            string sql = "insert into StudentInfos (StuName,ClassId,Sex,Phone) " +
                "values(@StuName,@ClassId,@Sex,@Phone)";
            SqlParameter[] paras =
            {
                new SqlParameter("@StuName",student.StuName),
                new SqlParameter("@ClassId",student.ClassId),
                new SqlParameter("@Sex",student.Sex),
                new SqlParameter("@Phone",student.Phone)
            };
            return DBHelper.ExecuteNonQuery(sql, 1, paras)>0;
        }

        public List<StudentClassModel> GetStudentList(int classId,string stuName)
        {
            string sql = "select StuId,StuName,Sex,c.ClassId,c.ClassName,g.GradeName,Phone " +
               "from StudentInfos s inner join ClassInfos c " +
               "on c.ClassId=s.ClassId " +
               "inner join GradeInfos g " +
               "on g.GradeId=c.GradeId " +
               "where 1=1";
            if (classId > 0)
                sql += " and c.ClassId=@ClassId";
            if (!string.IsNullOrEmpty(stuName))
                sql += " and StuName like @StuName";
            sql += " order by StuId";

            SqlParameter[] paras =
            {
                new SqlParameter("@ClassId",classId),
                new SqlParameter("@StuName","%"+stuName+"%")
            };

            DataTable dt_Students = DBHelper.GetDataTable(sql, 1, paras);

            return TableToList(dt_Students);

           
        }

        /// <summary>
        /// 将DataTable转换为List 
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private List<StudentClassModel> TableToList(DataTable dt )
        {
            List<StudentClassModel> list = new List<StudentClassModel>();
            if(dt!=null && dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if(!string.IsNullOrEmpty(dr["StuId"].ToString()))
                    {
                        StudentClassModel model = new StudentClassModel();
                        model.StuId = (int)dr["StuId"];
                        model.StuName = dr["StuName"].ToString();
                        model.ClassId = (int)dr["ClassId"];
                        model.ClassName =dr["GradeName"].ToString()+" "+ dr["ClassName"].ToString();
                        model.Sex = dr["Sex"].ToString();
                        model.Phone = dr["Phone"].ToString();
                        list.Add(model);
                    }
                }
            }
            return list;

        }
    }
}