自己写的数据访问层（使用ini文件加密存储sql数据库登陆信息）

原创 于 2007-01-25 09:52:00 发布 · 1.5k 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql #加密 #数据库 #ini #存储 #exception

本文介绍了一个数据访问类的实现，该类用于执行数据库操作，如连接验证、SQL查询及事务处理，并通过3DES算法加密敏感信息，确保数据库交互的安全性。

数据访问类：

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Runtime.InteropServices;
using System.Text;
using System.Data.SqlClient;

 

/// <summary>
/// Base 的摘要说明
/// </summary>
public class Base
{
    //写入Ini文件API函数
    [DllImport("kernel32")]
    private static extern bool WritePrivateProfileString(string lpApplicationName, string lpKeyName, string lpString, string lpFileName);

    //读取Ini文件API函数
    [DllImport("kernel32")]
    public static extern bool GetPrivateProfileString(string lpApplicationName, string lpKeyName, string lpDefault, StringBuilder lpReturnedString, int nSize, string lpFileName);
   
    public string connectionstring="";
    public SqlConnection conn;
    Code code = new Code();
    public Base()
 {
        this.GetConnectionstring();
        conn = new SqlConnection(connectionstring);
 }
    //获取数据库连接字符串
    private void GetConnectionstring()
    {
        string file_name =HttpContext.Current.Server.MapPath("set.ini");
        string server_ip = "";
        string server_name = "";
        string server_pwd = "";
        StringBuilder sb = new StringBuilder(30);
        GetPrivateProfileString("ip", "server_ip", "", sb, sb.Capacity, file_name);
        server_ip =code.Decrypt3DES(sb.ToString().Trim());
        sb = new StringBuilder(30);
        GetPrivateProfileString("name", "server_name", "", sb, sb.Capacity, file_name);
        server_name =code.Decrypt3DES(sb.ToString().Trim());
        sb = new StringBuilder(30);
        GetPrivateProfileString("pwd", "server_pwd", "", sb, sb.Capacity, file_name);
        server_pwd =code.Decrypt3DES(sb.ToString().Trim());
        connectionstring = "server=" + server_ip + ";database=wsbm;user id=" + server_name + ";pwd=" + server_pwd + ";MAx pool size=100";
      
    }

    //判断数据库连接是否可用
    public bool IsDataServer()
    {
        try
        {
            conn.Open();
            conn.Close();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if(conn.State==ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }

    //执行select语句
    public DataSet ExecSelect(string sql, string table)
    {
        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        try
        {
            ds.Clear();
            if (table != "")
            {
                adap.Fill(ds, table);
                return ds;
            }
            else
            {
                adap.Fill(ds);
                return ds;
            }
        }
        catch (SqlException ex)
        {
            throw (new Exception(ex.Message));
        }
        finally
        {
            ds.Dispose();
            adap.Dispose();
            conn.Close();
        }
    }

    //执行insert,delete,update语句
    public bool ExecTransact(string str_sql)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(str_sql, conn);
        try
        {
            int i = cmd.ExecuteNonQuery();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (SqlException ex)
        {
            throw (new Exception(ex.Message));
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
    }

    //判断是记录否存在，返回布尔
    public bool ExecIsRecord(string str_sql)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(str_sql, conn);
        try
        {
            SqlDataReader read = cmd.ExecuteReader();
            if (read.HasRows)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (SqlException ex)
        {
            throw (new Exception(ex.Message));
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
    }

    //使用事务处理,删除考试级别
    public bool Del_ksjb(string sql1, string sql2,string sql3)
    {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        SqlTransaction trans;
        trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        cmd.Connection = conn;
        cmd.Transaction = trans;
        try
        {
            cmd.CommandText = sql1;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql2;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql3;
            cmd.ExecuteNonQuery();
            trans.Commit();
            return true;
        }
        catch (Exception e)
        {
            try
            {
                trans.Rollback();//回滚事务
            }
            catch (System.Data.SqlClient.SqlException ex)//回滚失败的处理
            {
                if (trans.Connection != null)
                {
                    throw (new Exception("一个异常： " + ex.GetType() +
                        "在执行事务回滚操作时发生"));

                }
            }
            throw (new Exception(e.Message));
            return false;
        }
        finally
        {
            conn.Close();
        }
    }
    //使用事务处理,删除考试专业
    public bool Del_kszy(string sql1, string sql2)
    {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        SqlTransaction trans;
        trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        cmd.Connection = conn;
        cmd.Transaction = trans;
        try
        {
            cmd.CommandText = sql1;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql2;
            cmd.ExecuteNonQuery();          
            trans.Commit();
            return true;
        }
        catch (Exception e)
        {
            try
            {
                trans.Rollback();//回滚事务
            }
            catch (System.Data.SqlClient.SqlException ex)//回滚失败的处理
            {
                if (trans.Connection != null)
                {
                    throw (new Exception("一个异常： " + ex.GetType() +
                        "在执行事务回滚操作时发生"));

                }
            }
            throw (new Exception(e.Message));
            return false;
        }
        finally
        {
            conn.Close();
        }
    }

    //使用事务处理,删除考试内容
    public bool Del_ksnr(string sql1, string sql2,string sql3,string sql4,string sql5,string sql6)
    {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        SqlTransaction trans;
        trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        cmd.Connection = conn;
        cmd.Transaction = trans;
        try
        {
            cmd.CommandText = sql1;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql2;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql3;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql4;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql5;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql6;
            cmd.ExecuteNonQuery();
            trans.Commit();
            return true;
        }
        catch (Exception e)
        {
            try
            {
                trans.Rollback();//回滚事务
            }
            catch (System.Data.SqlClient.SqlException ex)//回滚失败的处理
            {
                if (trans.Connection != null)
                {
                    throw (new Exception("一个异常： " + ex.GetType() +
                        "在执行事务回滚操作时发生"));

                }
            }
            throw (new Exception(e.Message));
            return false;
        }
        finally
        {
            conn.Close();
        }
    }

    //使用事务处理，同时执行3条语句
    public bool ExecTransact_3(string sql1, string sql2,string sql3)
    {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        SqlTransaction trans;
        trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        cmd.Connection = conn;
        cmd.Transaction = trans;
        try
        {
            cmd.CommandText = sql1;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql2;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql3;
            cmd.ExecuteNonQuery();
            trans.Commit();
            return true;
        }
        catch (Exception e)
        {
            try
            {
                trans.Rollback();//回滚事务
            }
            catch (System.Data.SqlClient.SqlException ex)//回滚失败的处理
            {
                if (trans.Connection != null)
                {
                    throw (new Exception("一个异常： " + ex.GetType() +
                        "在执行事务回滚操作时发生"));

                }
            }
            throw (new Exception(e.Message));
            return false;
        }
        finally
        {
            conn.Close();
        }
    }

    //使用事务处理，同时执行2条语句
    public bool ExecTransact_2(string sql1, string sql2)
    {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        SqlTransaction trans;
        trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        cmd.Connection = conn;
        cmd.Transaction = trans;
        try
        {
            cmd.CommandText = sql1;
            cmd.ExecuteNonQuery();
            cmd.CommandText = sql2;
            cmd.ExecuteNonQuery();          
            trans.Commit();
            return true;
        }
        catch (Exception e)
        {
            try
            {
                trans.Rollback();//回滚事务
            }
            catch (System.Data.SqlClient.SqlException ex)//回滚失败的处理
            {
                if (trans.Connection != null)
                {
                    throw (new Exception("一个异常： " + ex.GetType() +
                        "在执行事务回滚操作时发生"));

                }
            }
            throw (new Exception(e.Message));
            return false;
        }
        finally
        {
            conn.Close();
        }
    }
}
 

 

加密类：

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Security.Cryptography;//加密
using System.Text;

/// <summary>
/// Code 的摘要说明
/// </summary>
public class Code
{
 public Code()
 {
  //
  // TODO: 在此处添加构造函数逻辑
  //
 }
    //3des加密
    public string Encrypt3DES(string strString)
    {
        string strKey = "malong";
        TripleDESCryptoServiceProvider DES = new TripleDESCryptoServiceProvider();
        MD5CryptoServiceProvider hashMD5 = new MD5CryptoServiceProvider();
       
        DES.Key = hashMD5.ComputeHash(Encoding.ASCII.GetBytes(strKey));
        DES.Mode = CipherMode.ECB;

        ICryptoTransform DESEncrypt = DES.CreateEncryptor();

        byte[] Buffer = Encoding.ASCII.GetBytes(strString);
        return Convert.ToBase64String(DESEncrypt.TransformFinalBlock(Buffer, 0, Buffer.Length));
    }
    //解密
    public string Decrypt3DES(string strString)
    {
        string strKey = "malong";
        TripleDESCryptoServiceProvider DES = new TripleDESCryptoServiceProvider();
        MD5CryptoServiceProvider hashMD5 = new MD5CryptoServiceProvider();

        DES.Key = hashMD5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(strKey));
        DES.Mode = CipherMode.ECB;
        ICryptoTransform DESDecrypt = DES.CreateDecryptor();
        string result = "";
        try
        {
            byte[] Buffer = Convert.FromBase64String(strString);
            result = ASCIIEncoding.ASCII.GetString(DESDecrypt.TransformFinalBlock(Buffer, 0, Buffer.Length));
        }
        catch (System.Exception e)
        {
           throw (new System.Exception("null", e));
        }
        return result;
    }
    //md5加密
    public string EncryptMD5(string str)
    {
        MD5CryptoServiceProvider hashMD5 = new MD5CryptoServiceProvider();
        string s=ASCIIEncoding.ASCII.GetString(hashMD5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(str)));
        return s;
    }  

}

 

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值