/// <summary>
/// 查询单一结果
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static object GetSingleResult(string sql, SqlParameter[] param)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(param);
try
{
conn.Open();
return(cmd.ExecuteScalar());
}
catch (Exception ex)
{
string errorInfo = "GetSingleResult(string sql, SqlParameter[] param)发生异常：" + ex.Message;
LogUnitity.WriteLog("DBError.log", errorInfo);
throw new Exception(errorInfo);
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行增删改操作
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int Update(string sql, SqlParameter[] param)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(param);
try
{
conn.Open();
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
string errorInfo = "Update(string sql, SqlParameter[] param)发生异常：" + ex.Message;
LogUnitity.WriteLog("DBError.log", errorInfo);
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行增删改操作
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int UpdateByProc(string procName, SqlParameter[] param)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(procName, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
try
{
conn.Open();
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
string errorInfo = "UpdateByProc(string procName, SqlParameter[] param)发生异常：" + ex.Message;
LogUnitity.WriteLog("DBError.log", errorInfo);
throw new Exception(errorInfo);
}
finally
{
conn.Close();
}
}
/// <summary>
/// 启用事务，执行多条sql语句
/// </summary>
/// <param name="sqlList"></param>
/// <returns></returns>
public bool UpdateByTran(List <string> sqlList)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
try
{
conn.Open();
cmd.Transaction = conn.BeginTransaction();
foreach (string sql in sqlList)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit();
return(true);
}
catch (Exception ex)
{
if (cmd.Transaction != null)
{
cmd.Transaction.Rollback();
}
string errorInfo = "UpdateByTran(List<string> sqlList)发生异常：" + ex.Message;
LogUnitity.WriteLog("DBError.log", errorInfo);
throw new Exception(errorInfo);
}
finally
{
if (cmd.Transaction != null)
{
cmd.Transaction = null;
}
conn.Close();
}
}
/// <summary>
/// 执行查询，返回dataset数据集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
return(ds);
}
catch (Exception ex)
{
string errorInfo = "GetDataSet(string sql)发生异常：" + ex.Message;
LogUnitity.WriteLog("DBError.log", errorInfo);
throw new Exception(errorInfo);
}
finally
{
conn.Close();
}
}
