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