studentdal.cs

连接数据库的基本操作,可以作为入门的学习参考

using System;
using System.Data;
using System.Data.SqlClient;
using Model.Dxs;
using IDAL.Dxs;
using log4net;

namespace SQLServerDAL.Dxs
{
	/// <summary>
	/// StudentDAL 的摘要说明。
	/// </summary>
	public class StudentDAL:IStudent
	{
		private const string SQL_INSERT_STUDENT ="p_dxs_insert_student";
		private const string SQL_UPDATE_STUDENT  ="p_dxs_update_student";
		private const string SQL_DELETE_STUDENT  ="p_dxs_delete_student";

		#region IStudent 成员

		/// <summary>
		/// 得到所有学生信息
		/// </summary>
		/// <returns></returns>
		public DataTable GetAll()
		{
			// TODO:  添加 StudentDAL.GetAll 实现
			DataTable dt=null;
			string sqlstr="p_dxs_get_student_all";
			using(SqlConnection conn=new SqlConnection(SQLHelper.CONN_STRING))
			{
				conn.Open();
				try
				{
					dt=SQLHelper.ExecuteDataTable(conn,CommandType.StoredProcedure,sqlstr,null);
				}
				catch(Exception e)
				{
					ILog log=LogManager.GetLogger("");
					log.Debug(e.ToString());
					return null;
				}
			}
			return dt;
		}

		/// <summary>
		/// 更新学生信息
		/// </summary>
		/// <param name="studentInfo"></param>
		/// <returns></returns>
		public bool UpdateStudentInfo(Student studentInfo)
		{
			// TODO:  添加 StudentDAL.UpdateStudentInfo 实现
			bool flag=false;
			SqlParameter[] parameters=GetUpdateStudentParameters();
			parameters[0].Value=studentInfo.ID;
			parameters[1].Value=studentInfo.Name;
			parameters[2].Value=studentInfo.Age;
			parameters[3].Value=studentInfo.Height;

			using(SqlConnection conn=new SqlConnection(SQLHelper.CONN_STRING))
			{
				conn.Open();
				try
				{
					SQLHelper.ExecuteNonQuery(conn,CommandType.StoredProcedure,SQL_UPDATE_STUDENT,parameters);
					flag=true;
				}
				catch(Exception e)
				{
					ILog log=LogManager.GetLogger("");
					log.Debug(e.ToString());
					return false;
				}
			}
			return flag;
		}

		/// <summary>
		/// 插入学生信息
		/// </summary>
		/// <param name="studentInfo"></param>
		/// <returns></returns>
		public bool InsertStudentInfo(Student studentInfo)
		{
			// TODO:  添加 StudentDAL.InsertStudentInfo 实现
			bool flag=false;
			SqlParameter[] parameters=GetInsertStudentParameters();
			parameters[0].Value=studentInfo.Name;
			parameters[1].Value=studentInfo.Age;
			parameters[2].Value=studentInfo.Height;

			using(SqlConnection conn=new SqlConnection(SQLHelper.CONN_STRING))
			{
				conn.Open();
				try
				{
					SQLHelper.ExecuteNonQuery(conn,CommandType.StoredProcedure,SQL_INSERT_STUDENT,parameters);
					flag=true;
				}
				catch(Exception e)
				{
					ILog log=LogManager.GetLogger("");
					log.Debug(e.ToString());
					return false;
				}
			}
			return flag;
		}

		/// <summary>
		/// 通过学生编号得到学生信息
		/// </summary>
		/// <param name="studentId"></param>
		/// <returns></returns>
		public Student GetStudentInfoById(int studentId)
		{
			// TODO:  添加 StudentDAL.GetStudentInfoById 实现
			Student student =null;
			SqlParameter[] parameters=new SqlParameter[]
				{
					new SqlParameter("@id",SqlDbType.Int)
				};
			string sqlstr="p_dxs_get_student_by_id";
			parameters[0].Value=studentId;
			try
			{
				using(SqlDataReader dr=SQLHelper.ExecuteReader(SQLHelper.CONN_STRING,CommandType.StoredProcedure,sqlstr,parameters))
				{
					if(dr.Read())
					{
						student=new Student();
						student.Name=dr.IsDBNull(1)?null:dr["name"].ToString();
						student.Age=dr.IsDBNull(2)?null:dr["age"].ToString();
						student.Height=dr.IsDBNull(3)?null:dr["height"].ToString();
					}
				}
			}
			catch(Exception e)
			{
				ILog log=LogManager.GetLogger("");
				log.Debug(e.ToString());
				return null;
			}
			return student;
		}

		/// <summary>
		/// 通过学生编号删除学生信息
		/// </summary>
		/// <param name="studentId"></param>
		/// <returns></returns>
		public bool DeleteStudentInfo(Student studentInfo)
		{
			// TODO:  添加 StudentDAL.DeleteStudentInfo 实现
			bool flag=false;
			SqlParameter[] parameters=new SqlParameter[]
				{
					new SqlParameter("@id",SqlDbType.Int)
				};
			parameters[0].Value=studentInfo.ID;
			using(SqlConnection conn=new SqlConnection(SQLHelper.CONN_STRING))
			{
				try
				{
					SQLHelper.ExecuteNonQuery(conn,CommandType.StoredProcedure,SQL_DELETE_STUDENT,parameters);
					flag=true;
				}
				catch(Exception e)
				{
					ILog log=LogManager.GetLogger("");
					log.Debug(e.ToString());
					return false;
				}
			}
			return flag;
		}

		#endregion

		/// <summary>
		/// 获取学生参数
		/// </summary>
		/// <returns></returns>
		public SqlParameter[] GetInsertStudentParameters()
		{
			SqlParameter [] parameters=SQLHelper.GetCachedParameters(SQL_INSERT_STUDENT);
			if(parameters==null)
			{
				parameters=new SqlParameter[]{
												 new SqlParameter("@name",SqlDbType.VarChar,50),
												 new SqlParameter("@age",SqlDbType.Int),
												 new SqlParameter("@height",SqlDbType.Float),
				};
				SQLHelper.CacheParameters(SQL_INSERT_STUDENT,parameters);
			}
			return parameters;
		}

		/// <summary>
		/// 获取更新学生参数
		/// </summary>
		/// <returns></returns>
		public SqlParameter[] GetUpdateStudentParameters()
		{
			SqlParameter [] parameters=SQLHelper.GetCachedParameters(SQL_UPDATE_STUDENT);
			if(parameters==null)
			{
				parameters=new SqlParameter[]{
												 new SqlParameter("@id",SqlDbType.Int),
												 new SqlParameter("@name",SqlDbType.VarChar,50),
												 new SqlParameter("@age",SqlDbType.Int),
												 new SqlParameter("@height",SqlDbType.Float),
				};
				SQLHelper.CacheParameters(SQL_UPDATE_STUDENT,parameters);
			}
			return parameters;
		}
	}
}