departinfoservice.cs

will let you know the result once they replied will let you know the result once they replied

using System;
using System.Collections.Generic;
using System.Text;
using MyOffice.Models;
using MyOffice.DBUtility;
using System.Data;
using System.Data.SqlClient;

namespace MyOffice.DAL
{
    public class DepartInfoService
    {
        public DepartInfo GetDepartByID(int departID)
        {
            string sql = string.Format("select * from departinfo where departid={0}", departID);
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null))
            {
                if (reader.Read())
                {
                    DepartInfo depart = new DepartInfo();
                    depart.DepartID = reader.GetInt32(0);
                    depart.DepartName = reader.GetString(1);
                    depart.PrincipalUser = reader.GetString(2);
                    depart.ConnectTelNo = reader.GetInt64(3);
                    depart.ConnectMobileTelNo = reader.GetInt64(4);
                    depart.Faxes = reader.GetInt64(5);
                    depart.Branch = new BranchInfoService().GetBranchByID(reader.GetInt32(6));
                    return depart;
                }
                else
                {
                    return null;
                }
            }
        }

        public IList<DepartInfo> GetAllDepartInfo()
        {
            IList<DepartInfo> list = new List<DepartInfo>();
            string sql = string.Format(@"select * from departinfo");
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null))
            {
                while (reader.Read())
                {
                    DepartInfo depart = new DepartInfo();
                    depart.DepartID = reader.GetInt32(0);
                    depart.DepartName = reader.GetString(1);
                    depart.PrincipalUser = reader.GetString(2);
                    depart.ConnectTelNo = reader.GetInt64(3);
                    depart.ConnectMobileTelNo = reader.GetInt64(4);
                    depart.Faxes = reader.GetInt64(5);
                    depart.Branch = new BranchInfoService().GetBranchByID(reader.GetInt32(6));
                    list.Add(depart);
                }
            }
            return list;

        }

        public int AddNewDepartInfo(DepartInfo depart)
        {
            int flag = -1;
            string sql = string.Format(@"insert departinfo (departname,principaluser,connecttelno,connectmobileno,faxes,branchid)
                values(@departname,@principaluser,@connecttelno,@connectmobileno,@faxes,@branchid)");
            SqlParameter[] para = new SqlParameter[] 
            {
                new SqlParameter("@departname",depart.DepartName),
                new SqlParameter("@principaluser",depart.PrincipalUser),
                new SqlParameter("@connecttelno",depart.ConnectTelNo),
                new SqlParameter("@connectmobileno",depart.ConnectMobileTelNo),
                new SqlParameter("@faxes",depart.Faxes),
                new SqlParameter("@branchid",depart.Branch.BranchID)
            };
            flag = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, para);
            return flag;
        }

        public int ModifyDepart(DepartInfo depart)
        {
            int flag = -1;
            return flag;
        }

        public int DeleteDepartInfo(int departInfoID)
        {
            int flag = -1;
            string sql = string.Format(@"delete from departinfo where departinfoid={0}", departInfoID);
            flag = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null);
            return flag;
        }

        public IList<DepartInfo> GetDepartByBranchID(int branchID)
        {
            IList<DepartInfo> list = new List<DepartInfo>();
            string sql = string.Format(@"select * from departinfo where branchid={0}", branchID);
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null))
            {
                while (reader.Read())
                {
                    DepartInfo depart = new DepartInfo();
                    depart.DepartID = reader.GetInt32(0);
                    depart.DepartName = reader.GetString(1);
                    depart.PrincipalUser = reader.GetString(2);
                    depart.ConnectTelNo = reader.GetInt64(3);
                    depart.ConnectMobileTelNo = reader.GetInt64(4);
                    depart.Faxes = reader.GetInt64(5);
                    depart.Branch = new BranchInfoService().GetBranchByID(reader.GetInt32(6));
                    list.Add(depart);
                }
            }
            return list;
        }
    }
}