.NET三层架构 脚本
数据访问层(DAL)
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
public static class DBHelper
{
private static SqlConnection connection;
public static SqlConnection Connection //连接数据库，并打开连接
{
get
{
string connectionString = "server=.\\sql2005;database=TeachSys;uid=sa;pwd=sa2005";
if (connection == null)
{
connection = new SqlConnection(connectionString);
Connection.Open();
}
return connection;
}
}
public static int ExecuteCommand(string safeSql) //执行存储过程，返回受影响行数
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = safeSql;
int result = cmd.ExecuteNonQuery();
return result;
}
public static int ExecuteCommand(string safeSql, params SqlParameter[] values) //执行存储过程，返回受影响行数
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = safeSql;
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
return result;
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="safeSql"></param>
/// <param name="values"></param>
/// <returns></returns>
///
public static int ExecuteCommandWithSQL(string salfsql) //执行SQL语句，并返回受影响的行数
{
SqlCommand cmd = new SqlCommand(salfsql, Connection);
cmd.CommandType = CommandType.Text;
cmd.CommandText = salfsql;
int result = cmd.ExecuteNonQuery();
return result;
}
public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = safeSql;
int result = (int)cmd.ExecuteScalar();
return result;
}
public static int GetScalar(string safeSql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = safeSql;
cmd.Parameters.AddRange(values);
int result = (int)cmd.ExecuteScalar();
return result;
}
public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = safeSql;
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static SqlDataReader GetReader(string safeSql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = safeSql;
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static SqlDataReader GetRead(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandText = safeSql;
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = safeSql;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
public static DataTable GetDataSet(string safeSql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = safeSql;
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
public static int GetScalarStudent(string safeSql)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = safeSql;
SqlParameter renshu = cmd.CreateParameter();
renshu.ParameterName = "@stuRenShu";
renshu.DbType = DbType.Int32;
renshu.Direction = ParameterDirection.Output;
cmd.Parameters.Add(renshu);
cmd.ExecuteScalar();
return (int)cmd.Parameters["@stuRenShu"].Value;
}
}
}
表示层 （USL）
using System;
using System.Collections.Generic;
using System.Text;
namespace Models
{
#region 老师类
public class Teacher
{
int teaNum; //老师的序号
string teaName; //姓名
string teaXingZhi; //老师工作性质
string teaTeChang; //老师专业特长
string teaSex; //性别
string teaPhone; //老师手机
string teaTelephone; //家庭电话
string teaEmail; //邮箱
string teaNation; //民族
string teaOverSchool; //毕业院校
DateTime teaOverTime; //毕业时间
string teaSpeciality; //专业
string teaXueLi; //学历
string teaXueWei; //学位
string teaZhiCheng; //职称
string teaID; //身份证号
DateTime teaRuZhiTime; //入职时间
public int TeaNum
{
set { this.teaNum = value; }
get { return this.teaNum; }
}
public string TeaName
{
set { this.teaName = value; }
get { return this.teaName; }
}
public string TeaXingZhi
{
set { this.teaXingZhi = value; }
get { return this.teaXingZhi; }
}
public string TeaTeChang
{
set { this.teaTeChang = value; }
get { return this.teaTeChang; }
}
public string TeaSex
{
set { this.teaSex = value; }
get { return this.teaSex; }
}
public string TeaPhone
{
set { this.teaPhone = value; }
get { return this.teaPhone; }
}
public string TeaTelephone
{
set { this.teaTelephone = value; }
get { return this.teaTelephone; }
}
public string TeaEmail
{
set { this.teaEmail = value; }
get { return this.teaEmail; }
}
public string TeaNation
{
set { this.teaNation = value; }
get { return this.teaNation; }
}
public string TeaOverSchool
{
set { this.teaOverSchool = value; }
get { return this.teaOverSchool; }
}
public DateTime TeaOverTime
{
set { this.teaOverTime = value; }
get { return this.teaOverTime; }
}
public string TeaSpeciality
{
set { this.teaSpeciality = value; }
get { return this.teaSpeciality; }
}
public string TeaXueLi
{
set { this.teaXueLi = value; }
get { return this.teaXueLi; }
}
public string TeaXueWei
{
set { this.teaXueWei = value; }
get { return this.teaXueWei; }
}
public string TeaZhiCheng
{
set { this.teaZhiCheng = value; }
get { return this.teaZhiCheng; }
}
public string TeaID
{
set { this.teaID = value; }
get { return this.teaID; }
}
public DateTime TeaRuZhiTime
{
set { this.teaRuZhiTime = value; }
get { return this.teaRuZhiTime; }
}
}
｝
#endregion
业务逻辑层（BLL）
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace BLL
{
using DAL;
using Models;
public static class Manager
{
#region 教师模块
public static DataTable GetTeacherInfo()
{
return DBHelps.GetDataSet("GetTeacherInfo");
}
/// <summary>
/// 返回老师表头
/// </summary>
/// <returns></returns>
public static DataTable GetTeacherInfoHead()
{
return DBHelps.GetDataSet("GetTeacherInfoHeads");
}
public static bool InTeacher(Teacher teacher)
{
string sqlTeacher = "InTeacher";
bool isSuccess = false;
SqlParameter[] teaPara = new SqlParameter[]
{
new SqlParameter("@TeaName",teacher.TeaName),
new SqlParameter("@TeaXingZhi",teacher.TeaXingZhi),
new SqlParameter("@TeaTeChang",teacher.TeaTeChang),
new SqlParameter("@TeaSex",teacher.TeaSex),
new SqlParameter("@TeaPhone",teacher.TeaPhone),
new SqlParameter("@TeaTelephone",teacher.TeaTelephone),
new SqlParameter("@TeaEmail",teacher.TeaEmail),
new SqlParameter("@TeaNation",teacher.TeaNation),
new SqlParameter("@TeaOverSchool",teacher.TeaOverSchool),
new SqlParameter("@TeaOverTime",teacher.TeaOverTime),
new SqlParameter("@TeaSpeciality",teacher.TeaSpeciality),
new SqlParameter("@TeaXueLi",teacher.TeaXueLi),
new SqlParameter("@TeaXueWei",teacher.TeaXueWei),
new SqlParameter("@TeaZhiCheng",teacher.TeaZhiCheng),
new SqlParameter("@TeaID",teacher.TeaID),
new SqlParameter("@TeaRuZhiTime",teacher.TeaRuZhiTime),
};
int result = DBHelps.ExecuteCommand(sqlTeacher, teaPara);
if (result > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
return isSuccess;
}
/// <summary>
/// 批量添加老师信息
/// </summary>
/// <param name="teachers"></param>
/// <returns></returns>
public static bool InTeachers(Teacher[] teachers)
{
int count = teachers.Length;
bool isSucess = false;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < count; i++)
{
string tempt = string.Format("insert into Teacher values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')", teachers[i].TeaName, teachers[i].TeaXingZhi, teachers[i].TeaTeChang, teachers[i].TeaSex, teachers[i].TeaPhone, teachers[i].TeaTelephone, teachers[i].TeaEmail, teachers[i].TeaNation, teachers[i].TeaOverSchool, teachers[i].TeaOverTime, teachers[i].TeaSpeciality, teachers[i].TeaXueLi, teachers[i].TeaXueWei, teachers[i].TeaZhiCheng, teachers[i].TeaID, teachers[i].TeaRuZhiTime);
sb.Append(tempt);
sb.Append(" ");
}
string strSql = sb.ToString();
int resultRows = DBHelps.ExecuteCommandWithSQL(strSql);
if (count == resultRows)
{
isSucess = true;
}
else
{
isSucess = false;
}
return isSucess;
}
//返回教师所带的班级
public static DataTable GetTeacherClass(int TeaID)
{
SqlParameter[] tcPara = new SqlParameter[]
{
new SqlParameter("@teaNum",TeaID)
};
return DBHelps.GetDataSet("GetTeaClass", tcPara);
}
//删除教师信息
public static bool DelTeacher(int teaNum)
{
bool isDel = false;
SqlParameter[] teapara = new SqlParameter[]
{
new SqlParameter("@teaNum",teaNum)
};
int delRows = DBHelps.ExecuteCommand("DelTeacher", teapara);
if (teapara.Length == delRows)
{
isDel = true;
}
else
{
isDel = false;
}
return isDel;
}
public static bool UpTeacher(Teacher[] teachers)
{
int count = teachers.Length;
bool isSucess = false;
StringBuilder sqlSb = new StringBuilder();
for (int i = 0; i < count; i++)
{
string tempSql = String.Format("update teacher set TeaName='{0}',TeaXingZhi='{1}',TeaTeChang='{2}',TeaSex='{3}',TeaPhone='{4}',TeaTelephone='{5}',TeaEmail='{6}',TeaNation='{7}',TeaOverSchool='{8}',TeaOverTime='{9}',TeaSpeciality='{10}',TeaXueLi='{11}',TeaXueWei='{12}',TeaZhiCheng='{13}',TeaID='{14}',TeaRuZhiTime='{15}' where TeaNum = {16}", teachers[i].TeaName, teachers[i].TeaXingZhi, teachers[i].TeaTeChang, teachers[i].TeaSex, teachers[i].TeaPhone, teachers[i].TeaTelephone, teachers[i].TeaEmail, teachers[i].TeaNation, teachers[i].TeaOverSchool, teachers[i].TeaOverTime, teachers[i].TeaSpeciality, teachers[i].TeaXueLi, teachers[i].TeaXueWei, teachers[i].TeaZhiCheng, teachers[i].TeaID, teachers[i].TeaRuZhiTime, teachers[i].TeaNum);
sqlSb.Append(tempSql);
sqlSb.Append(" ");
}
string strSql = sqlSb.ToString();
int resultRows = DBHelps.ExecuteCommandWithSQL(strSql);
if (count == resultRows)
{
isSucess = true;
}
else
{
isSucess = false;
}
return isSucess;
}
public static string GetTName(int teaNum)
{
SqlParameter[] teapara = new SqlParameter[]
{
new SqlParameter("@teaNum",teaNum)
};
return DBHelps.ExecuteCommand("GetTName", teapara).ToString();
}
#endregion
