class SqlHelper
{
//通过ConfigurationManager可以读到App.config中的配置信息
private static string conStr = ConfigurationManager.ConnectionStrings["dbConStr"].ConnectionString;
/// <summary>
/// 将null转换成数据库中的DBNull
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static object ToDBValue(object value)
{
if (value == null)
value = DBNull.Value;
return value;
}
/// <summary>
/// 从数据库中获得DBNull数据转换成null
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static object FromDBValue(object value)
{
if (value == DBNull.Value)
value = null;
return value;
}
/// <summary>
/// 执行不查询sql语句
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <returns>执行完sql语句后,数据库受影响的行数</returns>
public static int ExecuteNonQuery(string sql,params SqlParameter[] parameters)
{
using (SqlConnection sqlCon = new SqlConnection(conStr))
{
sqlCon.Open();
using (SqlCommand sqlCmd = sqlCon.CreateCommand())
{
sqlCmd.CommandText = sql;
//foreach (SqlParameter param in parameters)
//{
// sqlCmd.Parameters.Add(param);
//}
sqlCmd.Parameters.AddRange(parameters);
return sqlCmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 执行查询
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <returns>返回查询结果中的第一行第一列</returns>
public static object ExecuteScaler(string sql,params SqlParameter[] parameters)
{
using (SqlConnection sqlCon = new SqlConnection(conStr))
{
sqlCon.Open();
using (SqlCommand sqlCmd = sqlCon.CreateCommand())
{
sqlCmd.CommandText = sql;
sqlCmd.Parameters.AddRange(parameters);
return sqlCmd.ExecuteScalar();
}
}
}
/// <summary>
/// 执行离线数据集查询
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <returns>返回离线数据集</returns>
public static DataTable ExecuteDataTable(string sql,params SqlParameter[] parameters)
{
using (SqlConnection sqlCon = new SqlConnection(conStr))
{
sqlCon.Open();
using (SqlCommand sqlCmd = sqlCon.CreateCommand())
{
sqlCmd.CommandText = sql;
sqlCmd.Parameters.AddRange(parameters);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);
DataSet dataSet = new DataSet();
sqlAdapter.Fill(dataSet);
return dataSet.Tables[0];
}
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProc"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteStoredProc(string storedProc,params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(conStr))
{
using (SqlCommand sqlCmd = new SqlCommand(storedProc, conn))
{
//sql命令为存储过程
sqlCmd.CommandType = CommandType.StoredProcedure;
//添加参数
sqlCmd.Parameters.AddRange(parameters);
//晚打开,早关闭
conn.Open();
return sqlCmd.ExecuteNonQuery();
}
}
}
}