studentdao.cs

改程序能够查询课程表

using System; 
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Collections;
namespace StudentTest
{
    public class StudentDAO
    {
        OleDbConnection ConDB;
        OleDbCommand  command;
        OleDbDataReader reader=null ;
      
        public StudentDAO()
        {
            ConDB = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db2.mdb");
        }
        public IList<Student>  FindAll()
        {

            //ArrayList arrStudent = new ArrayList();
            //Student student =(Student ) arrStudent[0];
            String SQL;
            SQL = "select * from Student";
            List<Student> entities = new List<Student>();
            command = new OleDbCommand(SQL, ConDB);
            try{
            command.Connection.Open();
            reader =command.ExecuteReader();
            Fill (reader,entities );
           }
            finally
            {
				if (reader != null){
					reader.Close();
				}
				command.Connection.Close();
			}
			return entities;
        }

        public Student FindByPK(string SNO)
        {
            String SQL;
            SQL = "select * from Student  where SNO=@SNO";
          
            List<Student> entities = new List<Student>();
            command = new OleDbCommand(SQL, ConDB);
            command.Parameters.Add(new OleDbParameter("@SNO", SNO));
            try
            {
                command.Connection.Open();
                reader = command.ExecuteReader();
                Fill(reader, entities);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                command.Connection.Close();
            }
            if (entities.Count > 0)
            {
                return entities[0];
            }
            else
            {
                return null;
            }
        }


        public IList<Student> FindByCustomWhere(string customWhere)
        {
            String SQL;
          
            SQL = "select * from Student where "+ customWhere ;
            List<Student> entities = new List<Student>();
            command = new OleDbCommand(SQL, ConDB);
            try
            {
                command.Connection.Open();
                reader = command.ExecuteReader();
                Fill(reader, entities);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                command.Connection.Close();
            }
            return entities;
        }
        public Boolean Insert(Student entity)
        {
            String SQL;
            bool retval = false;
                SQL = "insert into Student ([Address],[Age],[SName],[SNO]) values (@Address,@Age,@SName,@SNO)";
                command = new OleDbCommand(SQL, ConDB);

                command.Parameters.Add(new OleDbParameter("@Address", entity.Address));
                command.Parameters.Add(new OleDbParameter("@Age", entity.Age));
              
                command.Parameters.Add(new OleDbParameter("@SName", entity.SName ));
                command.Parameters.Add(new OleDbParameter("@SNO", entity.SNO));
            
              
                try
                {
                    command.Connection.Open();
                    int affectCount = command.ExecuteNonQuery();
                    if (affectCount > 0)
                    {
                       
                            retval = true;
                       
                    }
                }
                finally
                {
                    command.Connection.Close();
                }
                return retval;
        }
        public Boolean DeleteByPK(string SNO)
        {
            bool retval = false;
            String SQL;
            SQL = "delete from Student where SNO=@SNO";
            command = new OleDbCommand(SQL, ConDB);
            command.Parameters.Add(new OleDbParameter("@SNO", SNO));
         
            try
            {
                command.Connection.Open();
                int affectCount = command.ExecuteNonQuery();
                if (affectCount > 0)
                {
                    retval = true;
                }
            }
            finally
            {
                command.Connection.Close();
            }
            return retval;
        }
        public Boolean Update(Student entity)
        {
            bool retval = false;
            String SQL;
            SQL = "update  Student  set  [Address]=@Address,[Age]=@Age,[SName]=@SName ,[Sex]=@Sex where SNO=@SNO";
            command = new OleDbCommand(SQL, ConDB);
            command.Connection.Open();
            command.Parameters.Add("@Address", entity.Address);
            command.Parameters.Add("@Age", entity.Age);
            command.Parameters.Add("@SName", entity.SName);
            command.Parameters.Add("@Sex", entity.Sex);
            command.Parameters.Add("@SNO", entity.SNO);

            try
            {
                int affectCount = command.ExecuteNonQuery();
                if (affectCount > 0)
                {
                    retval = true;
                }
            }
            finally
            {
                command.Connection.Close();
            }
            return retval;
        }


        public  void DeepLoad(Student  entity, bool needDeepLoadProperty)
        {
            DictionaryCodeDAO dao = new DictionaryCodeDAO();
            DictionaryCode sexSource = dao.FindByNameAndCode("sex", entity.Sex.Trim());
            entity.SexSource = sexSource;

        }

        #region DeepLoad IList<BitUseData>
       
        public  void DeepLoad(IList<Student > entities, bool needDeepLoadProperty)
        {
            foreach (Student  entity in entities)
            {
                DeepLoad(entity, needDeepLoadProperty);
            }
        }
        #endregion
        private  void Fill(OleDbDataReader reader,IList<Student> entities){
			while (reader.Read()){
				Student  entity = new Student ();
				if (!reader.IsDBNull(reader.GetOrdinal("SNO"))){
					entity.SNO = (string )reader["SNO"];
				}
				if (!reader.IsDBNull(reader.GetOrdinal("SName"))){
					entity.SName  = (string)reader["SName"];
				}
			
				if (!reader.IsDBNull(reader.GetOrdinal("Age"))){
					entity.Age  = (int)reader["Age"];
				}
				if (!reader.IsDBNull(reader.GetOrdinal("Address"))){
					entity.Address  = (string )reader["Address"];
				}

                if (!reader.IsDBNull(reader.GetOrdinal("Sex")))
                {
                    entity.Sex = (string)reader["Sex"];
                }
				
				entities.Add(entity);
			}
		}
		

    }
}