SQLHelper类常用操作
[ 本帖最后由 feiibao2020 于 2020-3-16 19:48 编辑 ]\n\nusing System;using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
/// <summary>
///通用数据访问类
/// </summary>
class SQLHelper
{
private static readonly string connString =
ConfigurationManager.ConnectionStrings["connString"].ToString();
/// <summary>
/// 执行增、删、改（insert/update/delete）
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Update(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
int result = cmd.ExecuteNonQuery();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行单一结果查询（select）
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
object result = cmd.ExecuteScalar();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行多结果查询（select）
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
SqlDataReader objReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
return objReader;
}
catch (Exception ex)
{
conn.Close();
throw ex;
}
}
/// <summary>
/// 执行返回数据集的查询
/// </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)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static DataSet GetDataSet(string sql,SqlParameter[] param)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(param);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 启用事务执行多条SQL语句
/// </summary>
/// <param name="sqlList">SQL语句列表</param>
/// <returns></returns>
public static 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 itemSql in sqlList)//循环提交SQL语句
{
cmd.CommandText = itemSql;
cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit();//提交事务(同时自动清除事务)
return true;
}
catch (Exception ex)
{
if (cmd.Transaction != null)
cmd.Transaction.Rollback();//回滚事务(同时自动清除事务)
throw new Exception("调用事务方法UpdateByTran(List<string> sqlList)时出现错误：" + ex.Message);
}
finally
{
if (cmd.Transaction != null)
cmd.Transaction = null;
conn.Close();
}
}
/// <summary>
/// 获取服务器的时间
/// </summary>
/// <returns></returns>
public static DateTime GetServerTime()
{
return Convert.ToDateTime(GetSingleResult("select getdate()"));
}
}
}
页:
[1]