DBHelper
/// <summary>
/// 根据sql 返回受影响的行数(增删改)
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteSql(string sql)
{
int rs = 0;
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
rs = cmd.ExecuteNonQuery();
return rs;
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
}
/// <summary>
/// 根据sql语句和参数执行sql
/// </summary>
/// <param name="sql"></param>
/// <param name="parmas"></param>
/// <returns></returns>
public static int ExecuteSql(string sql, SqlParameter[] param)
{
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(param);
return cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
/// <summary>
/// 根据sql 返回datatable
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable QueryBySql(string sql)
{
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
return ds.Tables[0];
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
/// <summary>
/// 根据sql 返回datatable
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable QueryBySql(string sql, SqlParameter[] param)
{
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(param);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
return ds.Tables[0];
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
/// <summary>
/// 根据sql语句返回查询结果的首行首列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
}
/// <summary>
/// 根据sql语句返回查询结果的首行首列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, SqlParameter[] param)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(param);
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
}
/// <summary>
/// 返回datareader
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql)
{
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
/// <summary>
/// 返回datareader
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql, SqlParameter[] param)
{
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(param);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}