分享我的数据库访问帮助类EasyDBUtility.dll C#

  /// <summary>
  /// 数据库连接对象
  /// </summary>
  public SqlConnection Conn
  {
      get { return _conn; }
  }
  /// <summary>
  /// 执行SQL命令对象
  /// </summary>
  public SqlCommand Cmd
  {
      get { return _cmd; }
      set { _cmd = value; }
  }
  /// <summary>
  /// 事务
  /// </summary>
  public SqlTransaction Trans
  {
      get { return _trans; }
      set { _trans = value; }
  }

<connectionStrings>
    <add name="SqlConnectionString" connectionString="数据库连接字符串"/>
</connectionStrings>

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool Sex { get; set; }
    public int NationId{get;set;}
    public string NationName{get;set;}
}

int result=SimpleDBHelper<SqlHelper>.ExecuteNonQuery("insert into [User] values(‘事理‘,1)");

SqlHelper helper = new SqlHelper();
helper.CreateCommand("insert into [User] values(@Name,@Sex)");
helper.AddParameter("@Name",name);
helper.AddParameter("@Sex",sex);
helper.ExecuteNonQuery();

public bool Add(UserData data)
{
    SqlHelper helper = new SqlHelper();
    helper.CreateCommand("insert into [User] values(@Name,@Sex)");
    //增删改查都可以用，前提是Sql语句中的参数化变量名称必须和对象中的属性名称相同，不区分大小写
    //方法中有缓存对象参数，再次调用将从缓存中读取，提高根据对象自动参数化效率，参考Pet Shop 5.0中参数缓存
    helper.AddParameter(data);
    helper.ExecuteNonQuery();
}

SqlHelper helper = new SqlHelper();
helper.CreateCommand("insert into [User] values(‘事理‘,1)");
helper.ExecuteNonQuery();

List<User> list = SimpleDBHelper<SqlHelper>.ExecuteReader<User>("select * from [User]");

public IList<User> Get(UserData data)
{
    //不写连接字符串，在web.config中必须配置SqlConnectionString
    SqlHelper helper = new SqlHelper();
    helper.CreateCommand("select * from [User] where Name=@Name and Id=@Id");
    helper.AddParameter(data);
    //采用Emit绑定DataReader中数据到对象，缓存后效率高过硬编码，不用再写繁复的写读取DataReader数据到对象的代码
    List<User> list = helper.ExecuteReader<User>();
    return list;
}
public IList<User> Get(UserData data)
{
    //如果要连接多个数据库，直接指定不同的连接字符串即可
    SqlHelper helper = new SqlHelper(System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnectionString2"].ConnectionString);
    helper.CreateCommand("select * from [User] where Name=@Name");
    helper.AddParameter(data);
    List<User> list = helper.ExecuteReader<User>();
    return list;
}

SQLHelper helper = new SQLHelper();
helper.AutoClose = false;//设置在执行完sql语句后不自动关闭数据库连接
helper.CreateCommand("select * from [User]");
DataReader dr = helper.ExecuteReader();
//var list = helper.ExecuteReader<User>();
List<User> list = new List<User>();
while (dr.Read())
{
    User data=new User();
    data.Id=dr.GetInt("Id");
    datat.Name=dr.GetString("Name");
    data.Sex=dr.GetBoolean("Sex");
    data.NationId=dr.GetInt("NationId");
    //使用CommandText方法
    helper.CommandText("select NationName from Nation where NationId="+data.NationId);
    object obj=helper.ExecuteScalar();
    data.NationName=obj!=null?obj.ToString():string.Empty;
    list.Add(data);    
}
helper.Close();//关闭数据库

string sql="select content from News where Id=12;select Author from Author where NewId=12;";
SqlHelper helper = new SqlHelper();
helper.CreateCommand(sql);
List<List<string>> list = helper.ExecuteMultipleReader<string>();
StringBuilder content = new StringBuilder();
foreach (List<string> item in list)
{
    foreach (string data in item)
    {
        content.Append(data);
    }
}

public static List<ClassName> GetAll<ClassName>(DataPage dataPage)
{
    SqlHelper helper = new SqlHelper();
    helper.CreateStoredProcedureCommand("[proc_DataPagination]");
    helper.AddParameter("@Table", dataPage.TableName);
    helper.AddParameter("@Fields", dataPage.Fields);
    helper.AddParameter("@Where", dataPage.Where);
    helper.AddParameter("@OrderBy", dataPage.OrderBy);
    helper.AddParameter("@CurrentPage", dataPage.CurrentPage);
    helper.AddParameter("@PageSize", dataPage.PageSize);
    helper.AddParameter("@GetCount", 0);
    helper.AddParameter("@Count", SqlDbType.Int, 0, null, ParameterDirection.Output);//输出参数
    List<ClassName> list = helper.ExecuteReader<ClassName>();
    dataPage.RecordSum = Convert.ToInt32(helper.GetValue("@Count"));
    return list;
}

SqlHelper helper = new SqlHelper();
helper.BeginTransaction();
helper.CreateCommand("delete from [user] where Id=@Id");            
for (int i = 0; i < ids.Length; i++)
{
    if (i == 0)
    {
        helper.AddParameter("@Id", ids[i]);
        helper.Cmd.Prepare();
    }
    else 
    {
        helper.Cmd.Parameters[0].Value = ids[i];
    }
    int result= helper.ExecuteNonQuery();
    if (result <= 0)
    {
        helper.RollBack();
        break;
    }
}
//事务提交
helper.Commit();

TransactionOptions op = new TransactionOptions();
//op.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;  //提交读
op.IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead;  //提交读
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, op))
{
    try
    {
        SimpleDBHelper<SqlHelper>.ExecuteNonQuery("insert into [User] values(‘事理‘,1)");
        SqlHelper helper2 = new SqlHelper(System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnectionString2"].ConnectionString);
        //helper2.CreateCommand("insert into [User] values(‘事理2‘,1)");//成功数据
        helper2.CreateCommand("insert into [User] values(事理2,1)");//错误插入，如果此处添加失败，并且上面表也没有数据，则说明分布式事务成功
        helper2.ExecuteNonQuery();
        scope.Complete(); //提交
    }
    catch
    {
    }                
}
MessageBox.Show("完毕");