增删查改最优化代码

Question

技术：ADO.NET+ASP.NET+三层结构
控件：GridView、DataList、Repeter均可

Answer 1

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace Dals
{
    public class DBHelper
    {
        public string connstr = ConfigurationManager.ConnectionStrings["db1"].ConnectionString;
        private SqlConnection _conn;
        public SqlConnection Conn
        {
            get
            {
                if (_conn == null)
                    _conn = new SqlConnection(connstr);
                return _conn;
            }
        }
        /// <summary>
        /// 封装增删改操作代码
        /// </summary>
        /// <param name="sql">要执行的SQL语句或者存储过程名称</param>
        /// <param name="type">命令类型</param>
        /// <param name="paras">参数数组</param>
        /// <returns></returns>
        public bool ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] paras)
        {
            int result = 0;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.CommandType = type;
                //判断是否存在参数
                if (paras != null && paras.Length > 0)
                    cmd.Parameters.AddRange(paras);                Conn.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch
            { }
            finally
            {
                Conn.Close();
            }
            return result > 0 ? true : false;
        }
        /// <summary>
        /// 返回单行单列
        /// </summary>
        /// <param name="sql">要执行的SQL语句或者存储过程名称</param>
        /// <param name="type">命令类型</param>
        /// <param name="paras">参数数组</param>
        /// <returns></returns>
        public object ExecuteScale(string sql, CommandType type, params SqlParameter[] paras)
        {
            object result = null;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.CommandType = type;
                //判断是否存在参数
                if (paras != null && paras.Length > 0)
                    cmd.Parameters.AddRange(paras);
                Conn.Open();
                result = cmd.ExecuteScalar();
            }
            catch { }
            finally
            {
                Conn.Close();
            }
            return result;
        }
        /// <summary>
        /// 返回DataReader，需要注意，获取完数据后，必须关闭DataReader对象
        /// </summary>
        /// <param name="sql">要执行的SQL语句或者存储过程名称</param>
        /// <param name="type">命令类型</param>
        /// <param name="paras">参数数组</param>
        /// <returns></returns>
        public SqlDataReader ExecuteDataReader(string sql, CommandType type, params SqlParameter[] paras)
        {
            SqlDataReader read = null;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.CommandType = type;
                //判断是否存在参数
                if (paras != null && paras.Length > 0)
                    cmd.Parameters.AddRange(paras);
                Conn.Open();
                //CommandBehavior.CloseConnection:关闭DataReader对象时，自动关闭相应的连接池对象
                read = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            { }
            return read;
        }
        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="sql">要执行的SQL语句或者存储过程名称</param>
        /// <param name="type">命令类型</param>
        /// <param name="paras">参数数组</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql, CommandType type, params SqlParameter[] paras)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.CommandType = type;
                //判断是否存在参数
                if (paras != null && paras.Length > 0)
                    cmd.Parameters.AddRange(paras);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
            }
            catch { }
            return ds;
        }
        /// <summary>
        /// 封装带事务的增删改操作代码
        /// </summary>
        /// <returns></returns>
        public bool ExecuteNonQuery(string sql, SqlConnection tranconn, SqlTransaction tran, CommandType type, params SqlParameter[] paras)
        {
            int result = 0;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, tranconn);
                cmd.Transaction = tran;
                cmd.CommandType = type;
                //判断是否存在参数
                if (paras != null && paras.Length > 0)
                    cmd.Parameters.AddRange(paras);                result = cmd.ExecuteNonQuery();
            }
            catch
            { }
            return result > 0 ? true : false;
        }
    }
}

Answer 2

楼主看这里http://www.51aspx.com/Search/%e4%b8%89%e5%b1%82从51aspx里面下载一个里面都会用到数据控件的增删改的。

Answer 3

积累下来的就是好的，用微软提供的吧PetShop