?using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// 員工實體方法
/// </summary>
public class EmployeeDA
{
    //定義常量表示字段名稱或SQL語句。
    private const string SQL_INSERT_EMPLOYEE = "INSERT INTO employeeinfo VALUES(@departid,@name,@phone, @mail,@birthday,@sex,@note)";
    private const string PARM_EMPLOYEE_NAME = "@name";
    private const string PARM_EMPLOYEE_PHONE = "@phone";
    private const string PARM_EMPLOYEE_MAIL = "@mail";
    private const string PARM_EMPLOYEE_BIRTHDAY = "@birthday";
    private const string PARM_EMPLOYEE_SEX = "@sex";
    private const string PARM_EMPLOYEE_NOTE = "@note";
    private const string PARM_DEPART_NAME = "@depart";
    private const string PARM_DEPART_ID = "@departid";
    private const string SQL_SELECT_DEPARTNAME = "SELECT departid FROM department WHERE departname=@depart";

	public EmployeeDA()
	{
	}
    /// <summary>
    /// 添加員工資料
    /// </summary>
    /// <param name="user">員工資料實體</param>
    /// <returns>添加是否成功</returns>
    public bool InsertEmployee(EmployeeEntity employee)
    {
        StringBuilder strSQL = new StringBuilder();
        SqlParameter[] employeeParms = GetParameters();
        SqlCommand cmd = new SqlCommand();

        // 依次給實體參數(shù)賦值
        employeeParms[1].Value = employee.Name;
        employeeParms[2].Value = employee.Phone;
        employeeParms[3].Value = employee.Mail;
        employeeParms[4].Value = employee.Birthday;
        employeeParms[5].Value = employee.Sex;
        employeeParms[6].Value = employee.Note;
        //獲取部門的ID
        int departid = GetDepartID(employee.Depart);
        employeeParms[0].Value = departid;

        //遍歷所有參數(shù)，并將參數(shù)添加到SqlCommand命令中
        foreach (SqlParameter parm in employeeParms)
            cmd.Parameters.Add(parm);

        //獲取數(shù)據(jù)庫的連接字符串
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            strSQL.Append(SQL_INSERT_EMPLOYEE);
            //打開數(shù)據(jù)庫連接，執(zhí)行命令
            conn.Open();
            //設(shè)置Sqlcommand命令的屬性
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSQL.ToString();
            //執(zhí)行添加的SqlCommand命令
            int val = cmd.ExecuteNonQuery();
            //清空SqlCommand命令中的參數(shù)
            cmd.Parameters.Clear();
            //判斷是否添加成功，注意返回的是添加是否成功，不是影響的行數(shù)
            if (val > 0)
                return true;
            else
                return false;
        }
    }
    #region 私有方法
    /// <summary>
    /// 獲取部門ID的方法
    /// </summary>
    /// <param name="cityname">部門名稱</param>
    /// <returns>該部門的ID</returns>
    private int GetDepartID(string departname)
    {
        int departid = 0;

        //創(chuàng)建新參數(shù)并給參數(shù)賦值，用來指定單位名稱
        SqlParameter parm = new SqlParameter(PARM_DEPART_NAME, SqlDbType.NVarChar, 20);
        parm.Value = departname;

        //調(diào)用SqlHelper訪問組件的方法返回第一行第一列的值
        departid = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_DEPARTNAME, parm);
        return departid;
    }
    private static SqlParameter[] GetParameters()
    {
        //將SQL_INSERT_EMPLOYEE做為哈希表緩存的鍵值
        SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_EMPLOYEE);

        //首先判斷緩存是否已經(jīng)存在
        if (parms == null)
        {
            //緩存不存在的情況下，新建參數(shù)列表
            parms = new SqlParameter[] {
                    new SqlParameter(PARM_DEPART_ID, SqlDbType.Int),
                    new SqlParameter(PARM_EMPLOYEE_NAME, SqlDbType.NVarChar,20),
                    new SqlParameter(PARM_EMPLOYEE_PHONE, SqlDbType.NVarChar, 20),
                    new SqlParameter(PARM_EMPLOYEE_MAIL, SqlDbType.NVarChar, 20),
                    new SqlParameter(PARM_EMPLOYEE_BIRTHDAY, SqlDbType.DateTime),
                    new SqlParameter(PARM_EMPLOYEE_SEX, SqlDbType.NVarChar,2),
                    new SqlParameter(PARM_EMPLOYEE_NOTE, SqlDbType.NVarChar,50) };

            //將新建的參數(shù)列表添加到哈希表中緩存起來
            SqlHelper.CacheParameters(SQL_INSERT_EMPLOYEE, parms);
        }
        //返回參數(shù)數(shù)組
        return parms;
    }
    #endregion
}