經典資料訪問層

using System;using System.Collections.Generic;using System.Data;using System.Text;using System.Text.RegularExpressions;using System.Data.SqlClient;using System.Configuration;using System.Net;using System.IO;namespace Common{    /// <summary>    /// 資料庫訪問類    /// </summary>    public class MsSql    {        //public static string ConnectionString = @"server=" + taobao.DBdata_server + ";Uid="+taobao.DBdata_user +";Pwd="+taobao .DBdata_password +";Database="+taobao .DBdata_name +"";        public static string ConnectionString = @"server=" + BLL.datahelp.DBdata_server + ";Uid=" + BLL.datahelp.DBdata_user + ";Pwd=" + BLL.datahelp.DBdata_password + ";Database=" + BLL.datahelp.DBdata_name + "";        public static string ConnectionString2 = @"";        /// <summary>        /// 查詢（注意使用完需關閉dr）        /// </summary>        /// <param name="sql">查詢語句</param>        /// <returns>SqlDataReader</returns>        public static SqlDataReader ExecuteReader(string sql, Type t)        {            SqlConnection conn = new SqlConnection(ConnectionString);            try            {                conn.Open();                SqlCommand cmd = new SqlCommand(sql, conn);                return cmd.ExecuteReader(CommandBehavior.CloseConnection);            }            catch (SystemException exception)            {                if (conn.State == ConnectionState.Open)                {                    conn.Close();                }                if (sql.Length > 50)                {                    sql = sql.Substring(0, 50) + "...";                }                Log.Write(t, "ExecuteReader(" + sql + ")：" + exception.Message);                return null;            }        }        /// <summary>        /// 添加、更新、刪除        /// </summary>        /// <param name="sql"></param>        /// <returns></returns>        public static int ExecuteNonQuery(string sql, Type t)        {            if (sql.Trim().Length == 0)            {                return 0;            }            using (SqlConnection conn = new SqlConnection(ConnectionString))            {                conn.Open();                SqlCommand cmd = new SqlCommand(sql, conn);                try                {                    cmd.CommandType = CommandType.Text;                    int i = cmd.ExecuteNonQuery();                    return i;                }                catch (SystemException exception)                {                    if (sql.Length > 50)                    {                        sql = sql.Substring(0, 50) + "...";                    }                    Log.Write(t, "ExecuteNonQuery(" + sql + ")：" + exception.Message);                    return -1;                }                finally                {                    if (conn.State.Equals(ConnectionState.Open))                    {                        conn.Close();                    }                }            }        }        /// <summary>        /// 執行查詢，並返回查詢多返回的結果集中第一行的第一列。忽略其他列或行。        /// </summary>        /// <param name="sql">查詢語句</param>        /// <returns>結果集中第一行的第一列</returns>        public static string ExecuteScalar(string sql)        {            using (SqlConnection conn = new SqlConnection(ConnectionString))            {                try                {                    conn.Open();                    SqlCommand cmd = new SqlCommand(sql, conn);                    string s = cmd.ExecuteScalar().ToString();                    return s;                }                catch                {                    return "";                }                finally                {                    if (conn.State.Equals(ConnectionState.Open))                    {                        conn.Close();                    }                }            }        }        /// <summary>        /// 根據查詢語句從資料庫檢索資料        /// </summary>        /// <param name="strSelect">查詢語句</param>        /// <param name="SqlConn">資料庫連接</param>        /// <returns>有資料則返回DataSet資料集（類似於ASP中的RecordSet），否則返回null</returns>        public static DataSet ExecuteDataSet(string sql, Type t)        {            using (SqlConnection conn = new SqlConnection(ConnectionString))            {                try                {                    conn.Open();                    SqlCommand cmd = new SqlCommand(sql, conn);                    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();                    mySqlDataAdapter.SelectCommand = cmd;                    DataSet myDS = new DataSet();                    mySqlDataAdapter.Fill(myDS);                    return myDS;                }                catch (SystemException exception)                {                    if (sql.Length > 50)                    {                        sql = sql.Substring(0, 50) + "...";                    }                    Log.Write(t, "ExecuteDataSet(" + sql + ")：" + exception.Message);                    return null;                }                finally                {                    if (conn.State.Equals(ConnectionState.Open))                    {                        conn.Close();                    }                }            }        }        public static SqlDataAdapter ExecuteSqlDataAdapter(string sql, Type t)        {            using (SqlConnection conn = new SqlConnection(ConnectionString))            {                try                {                    conn.Open();                    SqlCommand cmd = new SqlCommand(sql, conn);                    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();                    mySqlDataAdapter.SelectCommand = cmd;                    return mySqlDataAdapter;                }                catch (SystemException exception)                {                    if (sql.Length > 50)                    {                        sql = sql.Substring(0, 50) + "...";                    }                    Log.Write(t, "SqlDataAdapter(" + sql + ")：" + exception.Message);                    return null;                }                finally                {                    if (conn.State.Equals(ConnectionState.Open))                    {                        conn.Close();                    }                }            }        }        public static SqlDataReader ExecuteStoredProcWithReader(string spName, SqlParameter[] parameters, Type t)        {            try            {                SqlConnection conn = new SqlConnection(ConnectionString);                conn.Open();                SqlCommand cmd = new SqlCommand(spName, conn);                cmd.CommandType = CommandType.StoredProcedure;                if (parameters != null)                {                    for (int i = 0; i < parameters.Length; i++)                    {                        cmd.Parameters.Add(parameters[i]);                    }                }                return cmd.ExecuteReader(CommandBehavior.CloseConnection);            }            catch (SqlException exception)            {                Log.Write(t, "ExecuteStoredProcWithReader(" + spName + ")：" + exception.Message);                throw new SystemException(exception.Message);            }        }        public static int ExecuteStoredProcNonQuery(string spName, SqlParameter[] parameters, Type t)        {            using (SqlConnection conn = new SqlConnection(ConnectionString))            {                try                {                    conn.Open();                    SqlCommand cmd = new SqlCommand(spName, conn);                    cmd.CommandType = CommandType.StoredProcedure;                    if (parameters != null)                    {                        for (int i = 0; i < parameters.Length; i++)                        {                            cmd.Parameters.Add(parameters[i]);                        }                    }                    int num2 = cmd.ExecuteNonQuery();                    return num2;                }                catch (SqlException exception)                {                    Log.Write(t, "ExecuteStoredProcNonQuery(" + spName + ")：" + exception.Message);                    return -1;                }                finally                {                    if (conn.State.Equals(ConnectionState.Open))                    {                        conn.Close();                    }                }            }        }                public static DataSet ExecuteStoredProcWithDataSet(string spName, ref SqlParameter[] parameters, Type t)        {            using (SqlConnection conn = new SqlConnection(ConnectionString))            {                try                {                    conn.Open();                    SqlDataAdapter da = new SqlDataAdapter();                    DataSet ds = new DataSet();                    SqlCommand cmd = new SqlCommand(spName, conn);                    cmd.CommandType = CommandType.StoredProcedure;                    if (parameters != null)                    {                        for (int i = 0; i < parameters.Length; i++)                        {                            cmd.Parameters.Add(parameters[i]);                        }                    }                    da.SelectCommand = cmd;                    da.Fill(ds);                    return ds;                }                catch (SqlException exception)                {                    Log.Write(t, "ExecuteStoredProcWithDataSet(" + spName + ")：" + exception.Message);                    return null;                    //throw new SystemException(exception.Message);                }                finally                {                    if (conn.State.Equals(ConnectionState.Open))                    {                        conn.Close();                    }                }            }        }        public static SqlDataReader  get_tablecolunm(string talblename)        {            try            {                SqlConnection conn = new SqlConnection(ConnectionString);                conn.Open();                string str_sql = "Select Name from syscolumns where ID=OBJECT_ID(N'" + talblename + "') Order By ColID";                SqlCommand com = new SqlCommand(str_sql, conn);                SqlDataReader dr = com.ExecuteReader();                return dr;            }            catch (SqlException exception)            {                Log.Write("ExecuteStoredProcWithReader：" + exception.Message);                throw new SystemException(exception.Message);                return null;            }        }        /// <summary>        /// 檢證安全字元        /// </summary>        /// <param name="value"></param>        /// <returns></returns>        public static  string SqlSafe(string value)        {            value = value.Replace("'", "''");            // value = value.Replace("%", "'%");            return value;        }    }}

 

 

 

using System;using System.IO;namespace Common{    public class Log    {        private static object lockWrite = new object();        public static void Write(Type t, Exception e)        {            Write(t, e, null);        }        public static void Write(Type t, string sLog)        {            try            {                Write(t, null, sLog);            }            catch            { }        }        public static void Write(string sLog)        {            Write(null, null, sLog);        }        public static void Write(Exception e)        {            Write(null, e, null);        }        private static void Write(Type t, Exception e, string sLog)        {            lock (lockWrite)            {                string LogDirectory = AppDomain.CurrentDomain.BaseDirectory + @"\App_Log\";                if (!Directory.Exists(LogDirectory))                {                    Directory.CreateDirectory(LogDirectory);                }                string LogFile = LogDirectory + DateTime.Now.Date.ToString("yyyy-MM-dd") + ".log";                string strLog = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "|";                if (t != null)                {                    strLog += t.FullName;                }                if (e != null)                {                    strLog += e.Message;                }                if (sLog != null)                {                    strLog += sLog;                }                try                {                    StreamWriter writer = new StreamWriter(new FileStream(LogFile, FileMode.Append, FileAccess.Write), System.Text.Encoding.GetEncoding("GBK"));                    writer.WriteLine(strLog);                    writer.Close();                }                catch (SystemException ex)                {                    Write("Log.cs(66)," + ex.Message);                }            }        }    }}

 

 

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Security.Cryptography;using System.IO;using System.Security;namespace BLL{   public  class Security_cs    {        public class EncryptAndDecrypt        {            //預設密鑰向量            private static byte[] Keys = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };            /**/            /// <summary>            /// DES加密字串            /// </summary>            /// <param name="encryptString">待加密的字串</param>            /// <param name="encryptKey">加密金鑰,要求為8位</param>            /// <returns>加密成功返回加密後的字串，失敗返回源串</returns>            public static string EncryptDES(string encryptString, string encryptKey)            {                try                {                    byte[] rgbKey = Encoding.UTF8.GetBytes(encryptKey.Substring(0, 8));                    byte[] rgbIV = Keys;                    byte[] inputByteArray = Encoding.UTF8.GetBytes(encryptString);                    DESCryptoServiceProvider dCSP = new DESCryptoServiceProvider();                    MemoryStream mStream = new MemoryStream();                    CryptoStream cStream = new CryptoStream(mStream, dCSP.CreateEncryptor(rgbKey, rgbIV), CryptoStreamMode.Write);                    cStream.Write(inputByteArray, 0, inputByteArray.Length);                    cStream.FlushFinalBlock();                    return Convert.ToBase64String(mStream.ToArray());                }                catch                {                    return encryptString;                }            }            /**/            /// <summary>            /// DES解密字串            /// </summary>            /// <param name="decryptString">待解密的字串</param>            /// <param name="decryptKey">解密密鑰,要求為8位,和加密金鑰相同</param>            /// <returns>解密成功返回解密後的字串，失敗返源串</returns>            public static string DecryptDES(string decryptString, string decryptKey)            {                try                {                    byte[] rgbKey = Encoding.UTF8.GetBytes(decryptKey);                    byte[] rgbIV = Keys;                    byte[] inputByteArray = Convert.FromBase64String(decryptString);                    DESCryptoServiceProvider DCSP = new DESCryptoServiceProvider();                    MemoryStream mStream = new MemoryStream();                    CryptoStream cStream = new CryptoStream(mStream, DCSP.CreateDecryptor(rgbKey, rgbIV), CryptoStreamMode.Write);                    cStream.Write(inputByteArray, 0, inputByteArray.Length);                    cStream.FlushFinalBlock();                    return Encoding.UTF8.GetString(mStream.ToArray());                }                catch                {                    return decryptString;                }            }            public static string to_md5(string str, int code)            {                string strmd5 = "";                if (code == 16) //16位MD5加密（取32位加密的9~25字元）                 {                    strmd5 = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(str, "MD5").ToLower().Substring(8, 16);                }                if (code == 32) //32位加密                 {                    strmd5 = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(str, "MD5").ToLower();                }                return strmd5;            }        }    }}