|
using Dapper;
|
using Dapper.Contrib.Extensions;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Text;
|
|
namespace IStation.DataProvider
|
{
|
/// <summary>
|
/// 数据库访问基类
|
/// </summary>
|
public partial class BaseMsSqlDAL<T> where T : class, new()
|
{
|
/// <summary>
|
/// 构造函数
|
/// </summary>
|
public BaseMsSqlDAL()
|
{
|
this.TableName = EntityHelper.GetTableName<T>();
|
this.PrimaryKey = EntityHelper.GetPrimaryKey<T>();
|
}
|
|
/// <summary>
|
/// 对象的表名
|
/// </summary>
|
public virtual string TableName { get; set; }
|
|
/// <summary>
|
/// 对象主键属性名称
|
/// </summary>
|
public virtual string PrimaryKey { get; set; }
|
|
/// <summary>
|
/// 数据库连接
|
/// </summary>
|
protected virtual IDbConnection Connection
|
{
|
get
|
{
|
var connection = new SqlConnection(ConfigHelper.Db4Scada);
|
connection.Open();
|
return connection;
|
}
|
}
|
|
#region Insert
|
|
/// <summary>
|
/// 插入指定对象到数据库中
|
/// </summary>
|
/// <param name="entity">指定的对象</param>
|
/// <returns></returns>
|
public virtual long Insert(T entity)
|
{
|
using (IDbConnection dbConnection = Connection)
|
{
|
return dbConnection.Insert(entity);
|
}
|
}
|
|
/// <summary>
|
/// 插入指定对象集合到数据库中
|
/// </summary>
|
/// <param name="list">指定的对象集合</param>
|
/// <returns></returns>
|
public virtual bool Inserts(IEnumerable<T> list)
|
{
|
bool result = false;
|
using (IDbConnection dbConnection = Connection)
|
using (IDbTransaction trans = dbConnection.BeginTransaction())
|
{
|
result = dbConnection.Insert(list, trans) > 0;
|
trans.Commit();
|
}
|
return result;
|
}
|
|
#endregion
|
|
#region Update
|
|
/// <summary>
|
/// 更新对象属性到数据库中
|
/// </summary>
|
/// <param name="entity">指定的对象</param>
|
/// <returns></returns>
|
public virtual bool Update(T entity)
|
{
|
bool result = false;
|
using (IDbConnection dbConnection = Connection)
|
{
|
result = dbConnection.Update(entity);
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 更新指定对象集合到数据库中
|
/// </summary>
|
/// <param name="list">指定的对象集合</param>
|
/// <returns></returns>
|
public virtual bool Updates(IEnumerable<T> list)
|
{
|
bool result = false;
|
using (IDbConnection dbConnection = Connection)
|
using (IDbTransaction trans = dbConnection.BeginTransaction())
|
{
|
result = dbConnection.Update(list, trans);
|
trans.Commit();
|
}
|
return result;
|
}
|
|
#endregion
|
|
#region Delete
|
|
/// <summary>
|
/// 从数据库中删除指定对象
|
/// </summary>
|
/// <param name="entity">指定的对象</param>
|
/// <returns></returns>
|
public virtual bool Delete(T entity)
|
{
|
bool result = false;
|
using (IDbConnection dbConnection = Connection)
|
{
|
result = dbConnection.Delete(entity);
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 从数据库中删除指定对象集合
|
/// </summary>
|
/// <param name="list">指定的对象集合</param>
|
/// <returns></returns>
|
public virtual bool Deletes(IEnumerable<T> list)
|
{
|
bool result = false;
|
using (IDbConnection dbConnection = Connection)
|
using (IDbTransaction trans = dbConnection.BeginTransaction())
|
{
|
result = dbConnection.Delete(list, trans);
|
trans.Commit();
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 根据指定对象的ID,从数据库中删除指定对象
|
/// </summary>
|
/// <param name="id">对象的ID</param>
|
/// <returns></returns>
|
public virtual bool DeleteByID(long id)
|
{
|
bool result = false;
|
using (IDbConnection dbConnection = Connection)
|
{
|
string strSql = string.Format("DELETE FROM {0} WHERE {1} = @id", TableName, PrimaryKey);
|
var parameters = new DynamicParameters();
|
parameters.Add("@id", id);
|
|
result = dbConnection.Execute(strSql, parameters) > 0;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 通过id列表删除
|
/// </summary>
|
/// <param name="ids"></param>
|
/// <returns></returns>
|
public virtual bool DeleteByIds(IEnumerable<long> ids)
|
{
|
using (IDbConnection dbConnection = Connection)
|
{
|
var strSql = string.Format("DELETE FROM {0} WHERE {1} IN @ids", TableName, PrimaryKey);
|
var parameters = new DynamicParameters();
|
parameters.Add("@ids", ids);
|
var result = dbConnection.Execute(strSql, parameters) > 0 ? true : false;
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// 从数据库中删除所有对象
|
/// </summary>
|
/// <returns></returns>
|
public virtual bool DeleteAll()
|
{
|
bool result = false;
|
using (IDbConnection dbConnection = Connection)
|
{
|
result = dbConnection.DeleteAll<T>();
|
}
|
return result;
|
}
|
|
#endregion
|
|
#region Query
|
/// <summary>
|
/// 返回数据库所有的对象集合
|
/// </summary>
|
/// <returns></returns>
|
public virtual IEnumerable<T> GetAll()
|
{
|
using (IDbConnection dbConnection = Connection)
|
{
|
return dbConnection.GetAll<T>();
|
}
|
}
|
|
/// <summary>
|
/// 查询数据库,返回指定ID的对象
|
/// </summary>
|
/// <param name="id">主键的值</param>
|
/// <returns></returns>
|
public virtual T GetByID(long id)
|
{
|
using (IDbConnection dbConnection = Connection)
|
{
|
return dbConnection.Get<T>(id);
|
}
|
}
|
|
|
/// <summary>
|
/// 通拓id列表获取
|
/// </summary>
|
/// <returns></returns>
|
public virtual IEnumerable<T> GetByIds(IEnumerable<long> ids)
|
{
|
using (IDbConnection dbConnection = Connection)
|
{
|
var strSql = string.Format("SELECT * FROM {0} WHERE {1} IN @ids", TableName, PrimaryKey);
|
var parameters = new DynamicParameters();
|
parameters.Add("@ids", ids);
|
return dbConnection.Query<T>(strSql, parameters);
|
}
|
}
|
#endregion
|
|
|
}
|
}
|
