?using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
/// <summary>
/// 基礎(chǔ)配置類
/// </summary>
//該源碼下載自www.51aspx.com(５１ａｓｐｘ．ｃｏｍ)

public class BasicSet
{
    //將參數(shù)名和T-SQL語(yǔ)句設(shè)置成常量，方便以后維護(hù)。
    private const string PARM_AREA_NAME = "@areaname";
    private const string SQL_INSERT_AREA = "INSERT INTO area VALUES(@areaname)";

    private const string PARM_AREA_ID = "@areaid";
    private const string PARM_CITY_NAME = "@cityname";
    private const string SQL_INSERT_CITY = "INSERT INTO city VALUES(@areaid,@cityname)";
    private const string SQL_SELECT_AREAID = "SELECT areaid FROM area WHERE areaname=@areaname";

    private const string PARM_STATE_NAME = "@statename";
    private const string SQL_INSERT_STATE = "INSERT INTO userstate VALUES(@statename)";

    private const string PARM_GRADE_NAME = "@gradename";
    private const string SQL_INSERT_GRADE = "INSERT INTO usergrade VALUES(@gradename)";

    private const string PARM_TYPE_NAME = "@typename";
    private const string SQL_INSERT_TYPE = "INSERT INTO usertype VALUES(@typename)";
    private const string PARM_DEPART_NAME = "@departname";
    private const string SQL_INSERT_DEPART = "INSERT INTO department VALUES(@departname)";



	public BasicSet()
	{
	}
    /// <summary>
    /// 添加區(qū)域方法
    /// </summary>
    /// <param name="areaname">區(qū)域名稱</param>
    /// <returns>是否成功</returns>
    public bool AddArea(string areaname)
    {
        //初始化輸入?yún)?shù)并賦值
        SqlParameter parm = new SqlParameter(PARM_AREA_NAME, SqlDbType.NVarChar, 20);
        parm.Value = areaname;
        //將參數(shù)添加到SQL命令中
        SqlCommand cmd = new SqlCommand();
        cmd.Parameters.Add(parm);
        //初始化字符串連接
        StringBuilder strSQL = new StringBuilder();
        //初始化數(shù)據(jù)庫(kù)連接
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            //添加連接語(yǔ)句
            strSQL.Append(SQL_INSERT_AREA);
            //打開數(shù)據(jù)庫(kù)連接
            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;
        }
    }
    /// <summary>
    /// 為區(qū)域添加城市的方法
    /// </summary>
    /// <param name="areaname">區(qū)域名稱</param>
    /// <param name="cityname">城市名稱</param>
    /// <returns>是否添加成功</returns>
    public bool AddCity(string areaname,string cityname)
    { 

        //因?yàn)槌鞘斜碇写娣诺氖菂^(qū)域的ID，所以要先獲取區(qū)域ID。
        int areaid = GetAreaID(areaname);
        //初始化輸入?yún)?shù)并賦值
        SqlParameter [] parm = new SqlParameter[]{new SqlParameter(PARM_AREA_ID, SqlDbType.Int),new SqlParameter(PARM_CITY_NAME, SqlDbType.NVarChar,20)};
        parm[0].Value = areaid;
        parm[1].Value = cityname;
        //將參數(shù)添加到SQL命令中
        SqlCommand cmd = new SqlCommand();
        //遍歷所有參數(shù)，并將參數(shù)添加到SqlCommand命令中
        foreach (SqlParameter parm1 in parm)
            cmd.Parameters.Add(parm1);

        //初始化字符串連接
        StringBuilder strSQL = new StringBuilder();
        //初始化數(shù)據(jù)庫(kù)連接
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            //添加連接語(yǔ)句
            strSQL.Append(SQL_INSERT_CITY);
            //打開數(shù)據(jù)庫(kù)連接
            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;
        }


    }
    /// <summary>
    /// 根據(jù)區(qū)域名稱獲取ID的內(nèi)部方法
    /// </summary>
    /// <param name="areaname">區(qū)域名稱</param>
    /// <returns>返回區(qū)域ID</returns>
    private int GetAreaID(string areaname)
    {
        //初始化輸入?yún)?shù)并賦值
        SqlParameter parm = new SqlParameter(PARM_AREA_NAME, SqlDbType.NVarChar, 20);
        parm.Value = areaname;

        //調(diào)用SqlHelper訪問(wèn)組件的方法返回第一行第一列的值
        string tmpstr = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_AREAID, parm).ToString();
        //將返回值進(jìn)行類型轉(zhuǎn)換
        int areaid = int.Parse(tmpstr);
        //返回區(qū)域ID。
        return areaid;
    }
    /// <summary>
    /// 添加客戶等級(jí)方法
    /// </summary>
    /// <param name="areaname">等級(jí)名稱</param>
    /// <returns>是否成功</returns>
    public bool AddGrade(string gradename)
    {
        //初始化輸入?yún)?shù)并賦值
        SqlParameter parm = new SqlParameter(PARM_GRADE_NAME, SqlDbType.NVarChar, 20);
        parm.Value = gradename;
        //將參數(shù)添加到SQL命令中
        SqlCommand cmd = new SqlCommand();
        cmd.Parameters.Add(parm);
        //初始化字符串連接
        StringBuilder strSQL = new StringBuilder();
        //初始化數(shù)據(jù)庫(kù)連接
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            //添加連接語(yǔ)句
            strSQL.Append(SQL_INSERT_GRADE);
            //打開數(shù)據(jù)庫(kù)連接
            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;
        }
    }
    /// <summary>
    /// 添加客戶狀態(tài)方法
    /// </summary>
    /// <param name="areaname">狀態(tài)名稱</param>
    /// <returns>是否成功</returns>
    public bool AddState(string statename)
    {
        //初始化輸入?yún)?shù)并賦值
        SqlParameter parm = new SqlParameter(PARM_STATE_NAME, SqlDbType.NVarChar, 20);
        parm.Value = statename;
        //將參數(shù)添加到SQL命令中
        SqlCommand cmd = new SqlCommand();
        cmd.Parameters.Add(parm);
        //初始化字符串連接
        StringBuilder strSQL = new StringBuilder();
        //初始化數(shù)據(jù)庫(kù)連接
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            //添加連接語(yǔ)句
            strSQL.Append(SQL_INSERT_STATE);
            //打開數(shù)據(jù)庫(kù)連接
            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;
        }
    }
    /// <summary>
    /// 添加客戶類型方法
    /// </summary>
    /// <param name="areaname">類型名稱</param>
    /// <returns>是否成功</returns>
    public bool AddType(string typename)
    {
        //初始化輸入?yún)?shù)并賦值
        SqlParameter parm = new SqlParameter(PARM_TYPE_NAME, SqlDbType.NVarChar, 20);
        parm.Value = typename;
        //將參數(shù)添加到SQL命令中
        SqlCommand cmd = new SqlCommand();
        cmd.Parameters.Add(parm);
        //初始化字符串連接
        StringBuilder strSQL = new StringBuilder();
        //初始化數(shù)據(jù)庫(kù)連接
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            //添加連接語(yǔ)句
            strSQL.Append(SQL_INSERT_TYPE);
            //打開數(shù)據(jù)庫(kù)連接
            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;
        }
    }
    /// <summary>
    /// 添加部門方法
    /// </summary>
    /// <param name="areaname">類型名稱</param>
    /// <returns>是否成功</returns>
    public bool AddDepart(string departname)
    {
        //初始化輸入?yún)?shù)并賦值
        SqlParameter parm = new SqlParameter(PARM_DEPART_NAME, SqlDbType.NVarChar, 20);
        parm.Value = departname;
        //將參數(shù)添加到SQL命令中
        SqlCommand cmd = new SqlCommand();
        cmd.Parameters.Add(parm);
        //初始化字符串連接
        StringBuilder strSQL = new StringBuilder();
        //初始化數(shù)據(jù)庫(kù)連接
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            //添加連接語(yǔ)句
            strSQL.Append(SQL_INSERT_DEPART);
            //打開數(shù)據(jù)庫(kù)連接
            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;
        }
    }
}