unitdao.cs.svn-base

医院医德医风管理系统 B/S架构

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Collections.Generic;
using System.Data.Sql;
using System.Data.SqlClient;
/// <summary>
/// UnitDAO 的摘要说明
/// </summary>
/// 

public class UnitDAO
{
    public UnitDAO()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    private static string constr = ConfigurationManager.ConnectionStrings["dahuaConnectionString"].ConnectionString;

    public static List<Unit> GetUnitList(int unitId)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            List<Unit> list = new List<Unit>();
            string sql = "Select * from Unit where ParentUnitId=@ParentUnitId";
            SqlCommand command = new SqlCommand(sql, connection);
            command.Parameters.Add(new SqlParameter("@ParentUnitId", unitId));
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Unit unit = new Unit();
                unit.UnitId = (int)reader["Unitid"];
                unit.UnitNumber=(int)reader["UnitNumber"];
                unit.UnitName = reader["UnitName"].ToString();
                unit.ParentUnitId = (int)reader["ParentUnitId"];
                list.Add(unit);
            }
            return list;
        }



    }   
    public static List<Unit> GetParentUnitList(int parentUnitId)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            List<Unit> list = new List<Unit>();
            string sql = "Select * from Unit where ParentUnitId=@ParentUnitId or ParentUnitId=-1";
            SqlCommand command = new SqlCommand(sql, connection);
            command.Parameters.Add(new SqlParameter("@ParentUnitId", parentUnitId));
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Unit unit = new Unit();
                unit.UnitId = (int)reader["Unitid"];
                unit.UnitName = reader["UnitName"].ToString();
                unit.UnitNumber = (int)reader["UnitNumber"];

                list.Add(unit);  


            }
            return list;
        }
    }
    public static List<Unit> GetAllUnitList()
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            List<Unit> list = new List<Unit>();
            string sql = "Select * from Unit where ParentUnitId!=-1";
            SqlCommand command = new SqlCommand(sql, connection);

            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Unit unit = new Unit();
                unit.UnitId = (int)reader["Unitid"];
                unit.UnitNumber = (int)reader["UnitNumber"];
                unit.UnitName = reader["UnitName"].ToString();
                unit.ParentUnitId = (int)reader["ParentUnitId"];
                list.Add(unit);
            }
            return list;
        }
    }
    public static List<Unit> GetAllUnitListA()
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            List<Unit> list = new List<Unit>();
            string sql = "Select * from Unit where ParentUnitId!=-1";
            SqlCommand command = new SqlCommand(sql, connection);

            SqlDataReader reader = command.ExecuteReader();
            Unit u = new Unit();
            u.UnitId = 0;
            u.UnitName = "所有部门";
            list.Add(u);
            Unit uu = new Unit();
            uu.UnitId = -1;
            uu.UnitName = "集体记录";
            list.Add(uu);
            while (reader.Read())
            {
                Unit unit = new Unit();
                unit.UnitId = (int)reader["Unitid"];                
                unit.UnitName = reader["UnitName"].ToString();
                unit.ParentUnitId = (int)reader["ParentUnitId"];
                list.Add(unit);
            }
            
             return list;
        }
    }
    public static List<Unit> GetAllUnitListB()
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            List<Unit> list = new List<Unit>();
            string sql = "Select * from Unit where ParentUnitId!=-1";
            SqlCommand command = new SqlCommand(sql, connection);

            SqlDataReader reader = command.ExecuteReader();
            Unit u = new Unit();
            u.UnitId = 0;
            u.UnitName = "所有部门";
            list.Add(u);

            while (reader.Read())
            {
                Unit unit = new Unit();
                unit.UnitId = (int)reader["Unitid"];
                unit.UnitName = reader["UnitName"].ToString();
                unit.ParentUnitId = (int)reader["ParentUnitId"];
                list.Add(unit);
            }

            return list;
        }
    }
    public static Unit GetUnitById(int unitId)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            Unit unit = new Unit();
            string sql = "Select * from Unit where UnitId=@UnitId";
            SqlCommand command = new SqlCommand(sql, connection);
            command.Parameters.Add(new SqlParameter("@UnitId", unitId));
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                unit.UnitId = (int)reader["Unitid"];
                unit.UnitNumber=(int)reader["UnitNumber"];
                unit.UnitName = reader["UnitName"].ToString();
                unit.ParentUnitId = (int)reader["ParentUnitId"];
             }
            return unit;
        }
    }
    public static bool DelUnit(int unitId)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            string sql = "delete from Unit where UnitId=@UnitId1";
            string sql1 = "delete from Unit where ParentUnitId=@UnitId2";
            string sql2 = "delete from Personnel where UnitId=@UnitId3";
            SqlTransaction sqlTransaction = connection.BeginTransaction();
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.Transaction = sqlTransaction;
            int i = -1, j=-1,k = -1;
            try
            {
                command.CommandText = sql;
                command.Parameters.Add(new SqlParameter("@UnitId1", unitId));
                i=command.ExecuteNonQuery();

                command.CommandText = sql1;
                command.Parameters.Add(new SqlParameter("@UnitId2", unitId));
                i = command.ExecuteNonQuery();

                command.CommandText = sql2;
                command.Parameters.Clear();
                command.Parameters.Add(new SqlParameter("@UnitId3", unitId));
                j=command.ExecuteNonQuery();
                sqlTransaction.Commit();

            }
            catch
            {
                sqlTransaction.Rollback();
            }
            finally
            {
                connection.Close();
            }
            if (i >= 0 && j >= 0&&k>=0) return true;
            else return false;
        }
    }
    public static bool AddUnit(Unit unit)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            string sql = "insert into Unit(UnitNumber,UnitName,ParentUnitId) values(@UnitNumber,@UnitName,@ParentUnitId)";
            SqlCommand command = new SqlCommand(sql, connection);
            command.Parameters.Add(new SqlParameter("@UnitNumber", unit.UnitNumber));
            command.Parameters.Add(new SqlParameter("@UnitName", unit.UnitName));
            command.Parameters.Add(new SqlParameter("@ParentUnitId", unit.ParentUnitId.ToString()));
            int i = command.ExecuteNonQuery();
            connection.Close();
            if (i > 0) return true; else return false;
        }

    }
    public static bool UpdateUnit(Unit unit)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            string sql = "update Unit set UnitNumber=@UnitNumber,UnitName=@UnitName,ParentUnitId=@ParentUnitId where UnitId=@UnitId";
            SqlCommand command = new SqlCommand(sql, connection);

            command.Parameters.Add(new SqlParameter("@UnitNumber", unit.UnitNumber));
            command.Parameters.Add(new SqlParameter("@UnitName", unit.UnitName));
            command.Parameters.Add(new SqlParameter("@ParentUnitId", unit.ParentUnitId.ToString()));
            command.Parameters.Add(new SqlParameter("@UnitId", unit.UnitId));
            int i = command.ExecuteNonQuery();
            connection.Close();
            if (i > 0) return true; else return false;
        }
    }
}