MemberCenter/Member/CoreEntity/DAL/CommonDAL.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using ZcPeng.PublicLibrary;

namespace CoreEntity.DAL
{
    public class CommonDAL
    {
        //解密
        public static string[] getPhoneDecrypt(string[] MemPhones)
        {
            DataTable dt = new DataTable();
            string result;
            List<SqlParameter> parameters = new List<SqlParameter>();

            StringBuilder sb = new StringBuilder();
            sb.Append("select ");
            int i = 0;
            foreach (string MemPhone in MemPhones) {
                if(MemPhone!=null)
                sb.Append("(Mall.dbo.DesDecryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e','" + MemPhone + "')) as phone"+i+" " );

                if (i != MemPhones.Length - 1)
                    sb.Append(", ");
                i++;
            }

            string[] phones = new string[MemPhones.Length];
            bool success = DataAccess.GetValues( sb.ToString(), ref dt, parameters.ToArray(), out result);
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];

                i = 0;
                foreach (string MemPhone in MemPhones)
                {
                    if (MemPhone != null)
                        phones[i] = Convert.ToString(dr["phone" + i]);
                    i++;
                }
            }
            return phones;
        }

        //加密
        public static string[] getPhoneEncrypt(string[] MemPhones)
        {
            DataTable dt = new DataTable();
            string result;
            List<SqlParameter> parameters = new List<SqlParameter>();

            StringBuilder sb = new StringBuilder();
            sb.Append("select ");
            int i = 0;
            foreach (string MemPhone in MemPhones)
            {
                if (MemPhone != null)
                    sb.Append("(Mall.dbo.DesEncryptFixKey('123456','123456e10adc3949ba59abbe56e057f20f883e','" + MemPhone + "')) as phone" + i + " ");

                if (i != MemPhones.Length - 1)
                    sb.Append(", ");
                i++;
            }

            string[] phones = new string[MemPhones.Length];
            bool success = DataAccess.GetValues(sb.ToString(), ref dt, parameters.ToArray(), out result);
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];

                i = 0;
                foreach (string MemPhone in MemPhones)
                {
                    if (MemPhone != null)
                        phones[i] = Convert.ToString(dr["phone" + i]);
                    i++;
                }
            }
            return phones;
        }


        public static DataTable getRegions(string regionCode)
        {
            DataTable dt = new DataTable();
            string result;
            List<SqlParameter> parameters = new List<SqlParameter>();

            StringBuilder sb = new StringBuilder();
            sb.Append(" select "+

        " case when dbo.VBARegexMatch(b.AreaCode, '[^0-9]*[1-9]{1,}$', 1) = 'True'  then b.AreaCode else null  end as MemUsualAreaCode," +

        " case when dbo.VBARegexMatch(b.AreaCode, '[^0-9]*[1-9]{1,}00$', 1) = 'True'  then b.AreaCode else b.ParentCode  end as MemUsualCityCode," +

        " case when dbo.VBARegexMatch(b.AreaCode, '[^0-9]*[1-9]{1,}0000$', 1) = 'True'  then b.AreaCode else  (case when dbo.VBARegexMatch(b1.AreaCode, '[^0-9]*[1-9]{1,}0000$', 1) = 'True' then b1.AreaCode else b1.ParentCode end) end as MemUsualProvinceCode " +

        " from Mall.dbo.AreaRegion b " +

        " left join Mall.dbo.AreaRegion b1 on b1.AreaCode = b.ParentCode " +
        //" --left join Mall.dbo.AreaRegion c on c.AreaCode = b1.ParentCode " +

        " where b.AreaCode = '"+ regionCode + "'");
            int i = 0; 
            bool success = DataAccess.GetValues(sb.ToString(), ref dt, parameters.ToArray(), out result); 
            return dt;
        }
    }
}