一个简单的NET数据访问层操作类

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Text;

///<summary> ///数据访问层,数据库操作类  ///<summary>
public class CommonDatabase
{
    public static string ConnectionStrings = ConfigurationManager.ConnectionStrings["ConnectionStrings"].ConnectionString;
    ///<summary>
    ///执行带SqlParameter的sql语句,成功返回true,失败返回false 
    ///<summary>
    ///<paramnameparamname="sql">sql语句<param>
    ///<paramnameparamname="Param">SqlParameter[]参数列表<param>
    ///<returns><returns>
    public static int ExecuteNonQuery(string sql, params SqlParameter[] Param)
    //执行带SqlParameter的sql语句,返回影响的行数 
    {  //HttpContext.Current.Response.Write(sql+"<br>"); 
        using (SqlConnection conn = new SqlConnection(ConnectionStrings))
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlTransaction Tran = conn.BeginTransaction();
            //开始事务 
            SqlCommand cmd = new SqlCommand(sql, conn, Tran);
            if (Param != null)
                cmd.Parameters.AddRange(Param);
            try
            {
                int Result = cmd.ExecuteNonQuery();
                Tran.Commit();
                cmd.Parameters.Clear();
                return Result;
            }
            catch (Exception err)
            {
                HttpContext.Current.Response.Write(err.Message + "<br>" + err.StackTrace);
                Tran.Rollback();
                //回滚事务 
                return 0;
            }
        }
    }
    ///<summary>
    ///执行带SqlParameter的sql,返回DataReader 
    ///<summary>
    ///<paramnameparamname="sql">sql语句<param>
    ///<paramnameparamname="Param">SqlParameter[]参数列表<param>
    ///<returns>返回SqlDataReader<returns>
    public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] Param)
    //执行带SqlParameter的sql,返回DataReader 
    {
        SqlConnection conn = new SqlConnection(ConnectionStrings);
        SqlCommand cmd = new SqlCommand(sql, conn);
        if (Param != null)
            cmd.Parameters.AddRange(Param);
        if (conn.State != ConnectionState.Open)
            conn.Open();
        try
        {
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear(); return dr;
        }
        catch (Exception err)
        {
            conn.Close();
            HttpContext.Current.Response.Write(err.Message + "<br>" + err.StackTrace);
            return null;
        }
    }
    ///<summary>
    ///执行带SqlParameter的sql,返回DataTable
    ///<summary>
    ///<paramnameparamname="sql">SQL语句<param>
    ///<paramnameparamname="Param">SqlParameter[]参数列表<param>
    public static DataTable GetDataTable(string sql, params SqlParameter[] Param)
    //执行带SqlParameter的sql,返回DataTable
    {
        SqlConnection conn = new SqlConnection(ConnectionStrings);
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        if (Param != null)
            da.SelectCommand.Parameters.AddRange(Param);
        SqlCommandBuilder cb = new SqlCommandBuilder(da);
        da.Fill(dt);
        conn.Close();
        //HttpContext.Current.Response.Write(sql+"<br>");
        return dt;
    }
    ///<summary>
    ///执行带SqlParameter的sql,返回查询结果第一行第一列的数据 
    ///<summary>
    ///<paramnameparamname="sql">SQL语句<param>
    ///<paramnameparamname="Param">SqlParameter[]参数列表<param>
    ///<returns><returns>
    public static Object ExecuteScalar(string sql, params SqlParameter[] Param)
    //执行带SqlParameter的sql,返回查询结果第一行第一列的数据 
    {
        //HttpContext.Current.Response.Write(sql+"<br>");
        using (SqlConnection conn = new SqlConnection(ConnectionStrings))
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlTransaction Tran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand(sql, conn, Tran);
            if (Param != null)
                cmd.Parameters.AddRange(Param);
            try
            {
                object Result = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                Tran.Commit();
                return Result;
            }
            catch (Exception err)
            {
                HttpContext.Current.Response.Write(err.Message + "<br>" + err.StackTrace);
                Tran.Rollback();
                return null;
            }
        }
    }
    ///<summary>
    ///生成SqlParameter参数 
    ///<summary>
    ///<paramnameparamname="ParamName">参数名<param>
    ///<paramnameparamname="paramType">参数类型<param>
    ///<paramnameparamname="ParamValue">参数的值<param>
    public static SqlParameter GetParameter(string ParamName, SqlDbType paramType, Object ParamValue)
    //生成SqlParameter参数
    {
        SqlParameter param = new SqlParameter(ParamName, paramType);
        param.Value = ParamValue;
        return param;
    }
}

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值