经典数据访问层

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;
            }
        }

    }
}