SqlHelper初次重构

最新推荐文章于 2024-10-12 20:02:20 发布

_Ben仔

最新推荐文章于 2024-10-12 20:02:20 发布

阅读量434

点赞数

文章标签： string sql cmd null exception server

本文链接：https://blog.csdn.net/heruibin/article/details/6512691

版权

没有重构的代码

/*
*创建人：CK
*创建时间：2010-11-25
*/
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
///SqlHelper 数据操作类
/// </summary>
public class SqlHelper
{
    public SqlHelper()
    {

    }

    public int ExecuteNonQuery(string sql)
    {
        string connStr = "server=localhost;database=newssystem;uid=sa;pwd=123";
        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();
        int res = cmd.ExecuteNonQuery();
        conn.Close();
        return res;
    }

    public DataTable ExecuteQuery(string sql)
    {
        string connStr = "server=localhost;database=newssystem;uid=sa;pwd=heruibin";
        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();
        DataTable dt = new DataTable();
        SqlDataReader sdr = cmd.ExecuteReader();
        dt.Load(sdr);
        sdr.Close();
        conn.Close();
        return dt;
    }
}

重构的代码

/// <summary>
///SqlHelper 数据操作类
/// </summary>
public class SqlHelper
{
    private SqlConnection conn = null;
    private SqlCommand cmd = null;
    private SqlDataReader sdr = null;
    private DataTable dt = null;

    public SqlHelper()
    {
        string connStr =ConfigurationManager.ConnectionStrings["connStr"].ConnectionString ;//把字符串从WEB配置文件调出
        conn = new SqlConnection(connStr);
    }

    private SqlConnection Getconn() //打开连接
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        return conn;
    }

    public int ExecuteNonQuery(string sql) //用try...catch重构
    {
        int res;
        try
        {
            cmd = new SqlCommand(sql, Getconn());
            res = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {

            throw ex;
        }
        finally
        {
            conn.Close();
        }
        return res;
    }

    public DataTable ExecuteQuery(string sql)   //用using重构
    {
        dt = new DataTable();
        cmd = new SqlCommand(sql, Getconn());
        using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) //自动释放资源
        {
            dt.Load(sdr);
        }
        return dt;
    }
}

最近看牛腩爷的视频，在此来做做笔记~~~~~~