public DataSet GetSmtpSettings()
    {
        DataSet dsSmtp = new DataSet();
        SqlConnection SqlCon = new SqlConnection(ConStr);
        try
        {
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = SqlCon;

            sqlCmd.CommandText = "sp_Get_SmtpSettings";
            sqlCmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
            sqlDa.Fill(dsSmtp);

        }
        catch (SqlException SqlEx)
        {
            objNLog.Error("SQLException : " + SqlEx.Message);
            throw new Exception("Exception re-Raised from DL with SQLError# " + SqlEx.Number + " while Inserting SMTP Settings.", SqlEx);
        }
        catch (Exception ex)
        {
            objNLog.Error("Exception : " + ex.Message);
            throw new Exception("**Error occured while Inserting SMTP Settings.", ex);
        }
        finally
        {
            SqlCon.Close();

        }

        return dsSmtp;
    }

        /// <summary>
        /// Returns a name for the CellBoundsVariable * ServiceDimensionForCellBoundsVar
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="name"></param>
        /// <param name="boundsVariableName"></param>
        /// <returns></returns>
        private static Tuple<string, string> EnsureAddAxisCellsNames(DataSet ds, string name, string boundsVariableName)
        {
            string boundsVarName = boundsVariableName;
            if (boundsVarName == null)
            {
                boundsVarName = string.Format("{0}_bnds", name);
                if (ds.Variables.Contains(boundsVarName))
                {
                    int tryNum = 1;
                    while (ds.Variables.Contains(string.Format("{0}_bnds{1}", boundsVarName, tryNum)))
                        tryNum++;
                    boundsVarName = string.Format("{0}_bnds{1}", boundsVarName, tryNum);
                }
            }

            if (ds.Variables.Contains(boundsVarName))
                throw new ArgumentException(string.Format("The dataset is already contains a variable with a name {0}. Please specyfy another one or omit it for automatic name choice", boundsVarName));

            string sndDim = "nv";
            if (ds.Dimensions.Contains(sndDim) && ds.Dimensions[sndDim].Length != 2)
            {
                int num = 1;
                while (ds.Dimensions.Contains(string.Format("{0}{1}", sndDim, num)))
                    num++;
                sndDim = string.Format("{0}{1}", sndDim, num);
            }
            return Tuple.Create(boundsVarName, sndDim);
        }

 public static DataSet GetAllINV_StoresWithRelation()
 {
     DataSet iNV_Stores = new DataSet();
     SqlINV_StoreProvider sqlINV_StoreProvider = new SqlINV_StoreProvider();
     iNV_Stores = sqlINV_StoreProvider.GetAllINV_Stores();
     return iNV_Stores;
 }

    public static string PublishActivity(int ClubId, string ActivityContent)
    {
        // 将新增活动存入数据库，并从数据库返回信息及数据
        string connString = System.Configuration.ConfigurationManager.ConnectionStrings["CZConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(connString);
        conn.Open();
        // 存储
        string PublishDate = DateTime.Now.ToString();
        string queryString1 = "Insert Into Activity Values (" + ClubId + ",N'" + ActivityContent + "','" + PublishDate + "')";
        SqlCommand cmd = new SqlCommand(queryString1, conn);
        cmd.ExecuteNonQuery();
        // 查询最后插入的数据，就是新的数据
        string queryString2 = "Select Top 1 * From Activity Where ClubId=" + ClubId + " Order By PublishDate Desc";
        cmd = new SqlCommand(queryString2, conn);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        int Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"].ToString());
        string Content = ds.Tables[0].Rows[0]["Content"].ToString();
        string Date = ds.Tables[0].Rows[0]["PublishDate"].ToString();

        conn.Close();

        // 通过判断前后时间知是否查入成功
        if (PublishDate == Date)
        {
            return "{status:1,id:" + Id + ",content:'" + Content + "',date:'" + Date + "'}";
        }
        else
        {
            return "{status:-1}";
        }
    }

 public static DataSet GetDropDownListAllHR_WorkingDaysShifting()
 {
     DataSet hR_WorkingDaysShiftings = new DataSet();
     SqlHR_WorkingDaysShiftingProvider sqlHR_WorkingDaysShiftingProvider = new SqlHR_WorkingDaysShiftingProvider();
     hR_WorkingDaysShiftings = sqlHR_WorkingDaysShiftingProvider.GetDropDownLisAllHR_WorkingDaysShifting();
     return hR_WorkingDaysShiftings;
 }

 //  To get 'SubMenu' record of 'Active' or 'Inactive' from database by stored procedure
 public DataTable LoadActiveSubMenu(bool IsActive,int LoggedInUser, string RetMsg)
 {
     SqlConnection Conn = new SqlConnection(ConnString);
     //  'uspGetSubMenuDetails' stored procedure is used to get specific records from SubMenu table
     SqlDataAdapter DAdapter = new SqlDataAdapter("uspGetSubMenuDetails", Conn);
     DAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
     DataSet DSet = new DataSet();
     try
     {
         DAdapter.SelectCommand.Parameters.AddWithValue("@IsActive", IsActive);
         DAdapter.SelectCommand.Parameters.AddWithValue("@LoggedInUser", LoggedInUser);
         DAdapter.SelectCommand.Parameters.AddWithValue("@RetMsg", RetMsg);
         DAdapter.Fill(DSet, "Masters.SubMenu");
         return DSet.Tables["Masters.SubMenu"];
     }
     catch
     {
         throw;
     }
     finally
     {
         DSet.Dispose();
         DAdapter.Dispose();
         Conn.Close();
         Conn.Dispose();
     }
 }

    protected void btnOK_Click(object sender, EventArgs e)
    {
        string ID;
        SqlConnection mycon = new SqlConnection(ConfigurationManager.AppSettings["conStr"]);
        mycon.Open();
        DataSet mydataset = new DataSet();
        SqlDataAdapter mydataadapter = new SqlDataAdapter("select * from tb_Blog where UserName='" + Session["UserName"] + "'", mycon);
        mydataadapter.Fill(mydataset, "tb_Blog");
        DataRowView rowview = mydataset.Tables["tb_Blog"].DefaultView[0];
        ID = rowview["BlogID"].ToString();

        string P_str_Com = "Insert into tb_Message(FriendName,Sex,HomePhone,MobilePhone,QQ,ICQ,Address,Birthday,Email,PostCode,BlogID,IP)"
            +" values ('"+this.txtName.Text+"','"+this.DropDownList1.SelectedValue+"','"+this.txtHphone.Text+"'"
        +",'"+this.txtMphone.Text+"','"+this.txtQQ.Text+"','"+this.txtICQ.Text+"','"+this.txtAddress.Text+"'"
        +",'"+this.txtBirthday.Text+"','"+this.txtEmail.Text+"','"+this.txtPostCode.Text+"','"+ID+"','"+Request.UserHostAddress+"')";
        SqlData da = new SqlData();
        if (!ValidateDate1(txtBirthday.Text) && !ValidateDate2(txtBirthday.Text) && !ValidateDate3(txtBirthday.Text))
        {
            Response.Write("<script language=javascript>alert('输入的日期格式有误！');location='javascript:history.go(-1)'</script>");
        }
        else
        {
            bool add = da.ExceSQL(P_str_Com);
            if (add == true)
            {
                Response.Write("<script language=javascript>alert('添加成功!');location='AddLinkMan.aspx'</script>");
            }
            else
            {
                Response.Write("<script language=javascript>alert('添加失败！');location='javascript:history.go(-1)'</script>");
            }
        }
    }

 //---------------------------------
 protected internal DataSet getProductById(int id)
 {
     try
         {
             Open();
             DataSet dataset = new DataSet();
             SqlCommand cmd = new SqlCommand();
             cmd.Connection = DataBase;
             cmd.CommandText = "Get_FeatProduct_By_ID";
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.Add("@id", SqlDbType.Int);
             //Setting values to Parameters.
             cmd.Parameters[0].Value = id;
             SqlDataAdapter adapter = new SqlDataAdapter();
             adapter.SelectCommand = cmd;
             adapter.Fill(dataset);
             cmd.Dispose();
             Close();
             return dataset;
         }
         catch (SqlException oSqlExp)
         {
             //Console.WriteLine("" + oSqlExp.Message);
             return null;
         }
         catch (Exception oEx)
         {
             //Console.WriteLine("" + oEx.Message);
             return null;
         }
 }

 public static DataSet GetAllHR_SalaryIncrementsWithRelation()
 {
     DataSet hR_SalaryIncrements = new DataSet();
     SqlHR_SalaryIncrementProvider sqlHR_SalaryIncrementProvider = new SqlHR_SalaryIncrementProvider();
     hR_SalaryIncrements = sqlHR_SalaryIncrementProvider.GetAllHR_SalaryIncrements();
     return hR_SalaryIncrements;
 }

 public static DataSet GetDropDownListAllSTD_ClassSubjectEmployee()
 {
     DataSet sTD_ClassSubjectEmployees = new DataSet();
     SqlSTD_ClassSubjectEmployeeProvider sqlSTD_ClassSubjectEmployeeProvider = new SqlSTD_ClassSubjectEmployeeProvider();
     sTD_ClassSubjectEmployees = sqlSTD_ClassSubjectEmployeeProvider.GetDropDownListAllSTD_ClassSubjectEmployee();
     return sTD_ClassSubjectEmployees;
 }

 public static DataSet GetSTD_ClassSubjectByClassSubjectID(int ClassSubjectID,bool isDataset)
 {
     DataSet sTD_ClassSubjectEmployee = new DataSet();
     SqlSTD_ClassSubjectEmployeeProvider sqlSTD_ClassSubjectEmployeeProvider = new SqlSTD_ClassSubjectEmployeeProvider();
     sTD_ClassSubjectEmployee = sqlSTD_ClassSubjectEmployeeProvider.GetSTD_ClassSubjectEmployeeByClassSubjectID(ClassSubjectID,isDataset);
     return sTD_ClassSubjectEmployee;
 }

    protected void btnsubmit_Click(object sender, EventArgs e)
    {
        BALLogin objloginbal = new BALLogin();
        DALLogin objlogindal = new DALLogin();

        objloginbal.UserName = Session["username"].ToString();
        objloginbal.Password = txtoldpassword.Text;

        DataSet ds = new DataSet();
        ds=objlogindal.ValidateLogin(objloginbal);

        if(ds.Tables[0].Rows.Count>0)
        {
            objloginbal.LoginId = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
            objloginbal.Password = txtnewpassword.Text;
            objlogindal.ChangePassword(objloginbal);

            Response.Write("<script>alert('Password Changed');</script>");
        }
        else
        {
            Response.Write("<script>alert('Invalid Login Details');</script>");
        
        }
    }

 public DataSet BindGrandChild(int nid)
 {
     SqlDataAdapter da = new SqlDataAdapter("select NEWS_ID, CHANNEL_ID , USERID, TIEUDE, NGAY, STT, CAP_ID from TINTUC01_ENG where MENU_SUB = 1 and NHOM = "+nid+" order by STT", con);
     DataSet ds = new DataSet();
     da.Fill(ds);
     return ds;
 }

 public DataSet BindChild(int stt)
 {
     SqlDataAdapter da = new SqlDataAdapter("select NEWS_ID, TIEUDE, USERID, CHANNEL_ID, MENU_SUB, NGAY, STT, CAP_ID from TINTUC01_ENG where MENU_SUB = 0 and CHANNEL_ID = "+stt+" order by STT", con);
     DataSet ds = new DataSet();
     da.Fill(ds);
     return ds;
 }

 public static DataSet GetAllLIB_SubCategoriesWithRelation()
 {
     DataSet lIB_SubCategories = new DataSet();
     SqlLIB_SubCategoryProvider sqlLIB_SubCategoryProvider = new SqlLIB_SubCategoryProvider();
     lIB_SubCategories = sqlLIB_SubCategoryProvider.GetAllLIB_SubCategories();
     return lIB_SubCategories;
 }

 public static DataSet GetDropDownListAllHR_SalaryIncrement()
 {
     DataSet hR_SalaryIncrements = new DataSet();
     SqlHR_SalaryIncrementProvider sqlHR_SalaryIncrementProvider = new SqlHR_SalaryIncrementProvider();
     hR_SalaryIncrements = sqlHR_SalaryIncrementProvider.GetDropDownListAllHR_SalaryIncrement();
     return hR_SalaryIncrements;
 }

 public static DataSet GetDropDownListAllLIB_SubCategory(int CategoryID)
 {
     DataSet lIB_SubCategories = new DataSet();
     SqlLIB_SubCategoryProvider sqlLIB_SubCategoryProvider = new SqlLIB_SubCategoryProvider();
     lIB_SubCategories = sqlLIB_SubCategoryProvider.GetDropDownLisAllLIB_SubCategory(CategoryID);
     return lIB_SubCategories;
 }

 private void Load_Friends_And_Contacts()
 {
     localhostWebService.WebService service = new localhostWebService.WebService();
     DataSet dataSet = new DataSet();
     dataSet = service.GetFriendsAndContacts(user);
     Session["DataSet"] = dataSet;
 }

    /// <summary>
    /// 生成手机密码：通过用户编号
    /// </summary>
    /// <param name="sUserNo">用户编号</param>
    /// <returns>已生成手机密码的用户的手机号</returns>
    public string GenerateSMP(string sUserNo)
    {
        Random r1 = new Random();
        int iRandomPassword;
        string sRandomPassword,SM_content,mobile_telephone,sOperCode;
        DataSet ds=new DataSet();

        //生成密码
        iRandomPassword = r1.Next(1000000);
        sRandomPassword = fu.PasswordEncode(iRandomPassword.ToString());
        ds = fu.GetOneUserByUserIndex(sUserNo);

        sOperCode = ds.Tables[0].Rows[0]["User_code"].ToString();
        mobile_telephone = ds.Tables[0].Rows[0]["mobile_telephone"].ToString();

        sSql = "UPDATE Frame_user SET SM_PWD='" + sRandomPassword + "',SMP_last_change_time=getdate()" +
                " WHERE User_code = '" + sOperCode + "'";
        sv.ExecuteSql(sSql);
        //发送密码

        string SMP_validity_period = ds.Tables[0].Rows[0]["SMP_validity_period"].ToString();
        SM_content = "欢迎您使用EBM系统！您新的手机密码是：" + iRandomPassword.ToString() + "有效时间为：" + SMP_validity_period + "小时。";
        SendSMContentByPhoneCode(mobile_telephone, SM_content);
        return mobile_telephone;
    }

    public static string[] GetCompletionList(string prefixText, int count)
    {
        //連線字串
        //string connStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename="
                     //+ System.Web.HttpContext.Current.Server.MapPath("~/App_Data/NorthwindChinese.mdf") + ";Integrated Security=True;User Instance=True";

        ArrayList array = new ArrayList();//儲存撈出來的字串集合

        //using (SqlConnection conn = new SqlConnection(connStr))
        using (SqlConnection conn = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {
            DataSet ds = new DataSet();
            string selectStr = @"SELECT Top (" + count + ") Account_numbers FROM Account_Order_M_View Where Account_numbers Like '" + prefixText + "%'";
            SqlDataAdapter da = new SqlDataAdapter(selectStr, conn);
            conn.Open();
            da.Fill(ds);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                array.Add(dr["Account_numbers"].ToString());
            }

        }

        return (string[])array.ToArray(typeof(string));
    }