departinfoservice.cs

办公系统办公系统办公系统办公系统办公系统办公系统办公系统办公系统办公系统办公系统办公系统办公系统办公系统办公系统办公系统

//============================================================
// Producnt name:		BoBoARTS.CodeMad
// Version: 			1.0
// Coded by:			Shen Bo ([e-mail])
// Auto generated at: 	2008-9-18 16:21:28
//============================================================

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Office.Model;

namespace Office.DAL
{
	public static partial class DepartInfoService
	{
        /// <summary>
        /// Method: add department
        /// </summary>
        /// <param name="departInfo">DepartInfo</param>
        /// <returns>DepartInfo</returns>
        public static DepartInfo AddDepartInfo(DepartInfo departInfo)
		{
            string sql =
				"INSERT DepartInfo (DepartName, PrincipalUser, ConnectTelNo, ConnectMobileTelNo, Faxes, BranchId)" +
				"VALUES (@DepartName, @PrincipalUser, @ConnectTelNo, @ConnectMobileTelNo, @Faxes, @BranchId)";
				
			sql += " ; SELECT @@IDENTITY";

            try
            {
				SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@BranchId", departInfo.Branch.BranchId), //FK
					new SqlParameter("@PrincipalUser", departInfo.PrincipalUser.UserId), //FK
					new SqlParameter("@DepartName", departInfo.DepartName),
					new SqlParameter("@ConnectTelNo", departInfo.ConnectTelNo),
					new SqlParameter("@ConnectMobileTelNo", departInfo.ConnectMobileTelNo),
					new SqlParameter("@Faxes", departInfo.Faxes)
				};
				
                int newId = DBHelper.GetScalar(sql, para);
				return GetDepartInfoByDepartId(newId);
            }
            catch (Exception e)
            {
				Console.WriteLine(e.Message);
                throw e;
            }
		}
		/// <summary>
		/// Method:Delete department
		/// </summary>
        /// <param name="departInfo">DepartInfo</param>
        public static void DeleteDepartInfo(DepartInfo departInfo)
		{
			DeleteDepartInfoByDepartId( departInfo.DepartId );
		}
        /// <summary>
        /// Method:Delete department according to ID
        /// </summary>
        /// <param name="departId">departId</param>
        public static void DeleteDepartInfoByDepartId(int departId)
		{
            string sql = "DELETE DepartInfo WHERE DepartId = @DepartId";

            try
            {
				SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@DepartId", departId)
				};
			
                DBHelper.ExecuteCommand(sql, para);
            }
            catch (Exception e)
            {
				Console.WriteLine(e.Message);
				throw e;
            }
		}
		/// <summary>
		/// Method:Modification department
		/// </summary>
        /// <param name="departInfo">DepartInfo</param>
        public static void ModifyDepartInfo(DepartInfo departInfo)
        {
            string sql =
                "UPDATE DepartInfo " +
                "SET " +
	                "BranchId = @BranchId, " + //FK
	                "PrincipalUser = @PrincipalUser, " + //FK
	                "DepartName = @DepartName, " +
	                "ConnectTelNo = @ConnectTelNo, " +
	                "ConnectMobileTelNo = @ConnectMobileTelNo, " +
	                "Faxes = @Faxes " +
                "WHERE DepartId = @DepartId";


            try
            {
				SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@DepartId", departInfo.DepartId),
					new SqlParameter("@BranchId", departInfo.Branch.BranchId), //FK
					new SqlParameter("@PrincipalUser", departInfo.PrincipalUser.UserId), //FK
					new SqlParameter("@DepartName", departInfo.DepartName),
					new SqlParameter("@ConnectTelNo", departInfo.ConnectTelNo),
					new SqlParameter("@ConnectMobileTelNo", departInfo.ConnectMobileTelNo),
					new SqlParameter("@Faxes", departInfo.Faxes)
				};

				DBHelper.ExecuteCommand(sql, para);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
				throw e;
            }
        }		
        /// <summary>
        /// Method:Get all department
        /// </summary>
        /// <returns>IList<DepartInfo> </returns>
        public static IList<DepartInfo> GetAllDepartInfos()
        {
            string sqlAll = "SELECT * FROM DepartInfo";
			return GetDepartInfosBySql( sqlAll );
        }
        public static IList<DepartInfo> GetDepartInfosByBranchId(int BranchId)
        {
            string sqlAll = "SELECT * FROM DepartInfo WHERE BranchId="+BranchId;
            return GetDepartInfosBySql(sqlAll);
        }
		/// <summary>
		/// Method: Get department according to ID
        /// 
		/// </summary>
        /// <param name="departId">departId</param>
        /// <returns>DepartInfo</returns>
        public static DepartInfo GetDepartInfoByDepartId(int departId)
        {
            string sql = "SELECT * FROM DepartInfo WHERE DepartId = @DepartId";

			int branchId;
			string principalUser;

            try
            {
                SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@DepartId", departId));
                if (reader.Read())
                {
                    DepartInfo departInfo = new DepartInfo();

					departInfo.DepartId = (int)reader["DepartId"];
					departInfo.DepartName = (string)reader["DepartName"];
					departInfo.ConnectTelNo = (long)reader["ConnectTelNo"];
					departInfo.ConnectMobileTelNo = (long)reader["ConnectMobileTelNo"];
					departInfo.Faxes = (long)reader["Faxes"];
					branchId = (int)reader["BranchId"]; //FK
					principalUser = (string)reader["PrincipalUser"]; //FK

                    reader.Close();

					departInfo.Branch = BranchInfoService.GetBranchInfoByBranchId(branchId);
					departInfo.PrincipalUser = UserInfoService.GetUserInfoByUserId(principalUser);
					
                    return departInfo;
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }
        }
		
		
		
		/// <summary>
		/// Method:Get department according to safe T-SQL
		/// </summary>
        /// <param name="safeSql">safe T-SQL</param>
        /// <returns>IList<DepartInfo></returns>
        private static IList<DepartInfo> GetDepartInfosBySql( string safeSql )
        {
            List<DepartInfo> list = new List<DepartInfo>();

			try
			{
				DataTable table = DBHelper.GetDataSet( safeSql );
				
				foreach (DataRow row in table.Rows)
				{
					DepartInfo departInfo = new DepartInfo();
					
					departInfo.DepartId = (int)row["DepartId"];
					departInfo.DepartName = (string)row["DepartName"];
					departInfo.ConnectTelNo = (long)row["ConnectTelNo"];
					departInfo.ConnectMobileTelNo = (long)row["ConnectMobileTelNo"];
					departInfo.Faxes = (long)row["Faxes"];
					departInfo.Branch = BranchInfoService.GetBranchInfoByBranchId((int)row["BranchId"]); //FK
					departInfo.PrincipalUser = UserInfoService.GetUserInfoByUserId((string)row["PrincipalUser"]); //FK
	
					list.Add(departInfo);
				}
	
				return list;
			}
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }

        }
		/// <summary>
		/// Method: Get department according to safe T-SQL and parameter
		/// </summary>
        /// <param name="sql">safe T-SQL</param>
        /// <param name="values">SqlParameter</param>
        /// <returns>IList<DepartInfo></returns>
        private static IList<DepartInfo> GetDepartInfosBySql( string sql, params SqlParameter[] values )
        {
            List<DepartInfo> list = new List<DepartInfo>();

			try
			{
				DataTable table = DBHelper.GetDataSet( sql, values );
				
				foreach (DataRow row in table.Rows)
				{
					DepartInfo departInfo = new DepartInfo();
					
					departInfo.DepartId = (int)row["DepartId"];
					departInfo.DepartName = (string)row["DepartName"];
					departInfo.ConnectTelNo = (long)row["ConnectTelNo"];
					departInfo.ConnectMobileTelNo = (long)row["ConnectMobileTelNo"];
					departInfo.Faxes = (long)row["Faxes"];
					departInfo.Branch = BranchInfoService.GetBranchInfoByBranchId((int)row["BranchId"]); //FK
					departInfo.PrincipalUser = UserInfoService.GetUserInfoByUserId((string)row["PrincipalUser"]); //FK
	
					list.Add(departInfo);
				}
	
				return list;
			}
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }
			
        }
		
	}
}