public override IEnumerable <T> GetPagerList <T>(string condition, string sort, int pageIndex, int pageSize, string filter = "*", DbParameter[] conditionParameters = null)
{
if (sort.IsNullOrEmpty())
{
throw new ArgumentNullException("sort"); //sort参数必填
}
var lstResult = new List <T>();
T tItem = default(T);
string tableName = AttrAssistant.GetTableName(typeof(T)); //表名
if (condition.IsNullOrEmpty())
{
condition = "1=1";
}
//起始记录
int startPosition = (pageIndex - 1) * pageSize + 1;
int offsetPosition = (pageIndex - 1) * pageSize;
//截止记录
int endPosition = pageIndex * pageSize;
var builder = new StringBuilder();
if (dbVersionNo >= 11)
{
//sqlserver2012及以上可用
builder.AppendFormat(@"select {0}
from {1} as {6}
where {2}
order by {3}
offset {4} rows fetch next {5} rows only",
filter, tableName, condition, sort, offsetPosition, pageSize, Utils.SinGooPagerAlias);
}
else
{
//sqlserver2005及以上可用
builder.AppendFormat(@"select {0}
from(select row_number() over(order by {3}) as rownum,*
from {1}
where {2}
) as {6}
where rownum between {4} and {5}
order by {3}", filter, tableName, condition, sort, startPosition, endPosition, Utils.SinGooPagerAlias);
}
var reader = GetDataReader(builder.ToString(), conditionParameters);
var refBuilder = ReflectionBuilder <T> .CreateBuilder(reader);
while (reader.Read())
{
tItem = refBuilder.Build(reader, dbProviderType);
lstResult.Add(tItem);
}
reader.Close();
return(lstResult);
}
public virtual async Task <bool> DeleteModelAsync <T>(T model) where T : class
{
var type = typeof(T);
string key = AttrAssistant.GetKey(type);
if (!key.IsNullOrEmpty())
{
object primaryKeyUniqueValue = RefProperty.GetPropertyValue(model, key);
return(await DeleteAsync <T>(primaryKeyUniqueValue));
}
return(false);
}
public override async Task <int> InsertModelAsync <T>(T model, string tableName = "")
{
var arrProperty = typeof(T).GetProperties();
var builderSQL = new StringBuilder();
var builderParams = new StringBuilder(" ( ");
var lstParams = new List <DbParameter>();
if (tableName.IsNullOrEmpty())
{
tableName = AttrAssistant.GetTableName(typeof(T));
}
foreach (PropertyInfo property in arrProperty)
{
//NotMapped 是自定义的字段，不属于表，所以要排除，Key默认是有值的，自增int或者GUID
if (AttrAssistant.IsKey(property) || AttrAssistant.IsNotMapped(property))
{
continue;
}
object obj = property.GetValue(model, null);
if (obj == null)
{
continue; //null无法加入到参数，跳过
}
//日期类型没有值时，默认0001-1-1报错：SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM
//因此设置新的默认值是 1900-1-1
if (property.PropertyType.Name.Equals("DateTime") && ((DateTime)obj).Equals(new DateTime(0001, 1, 1)))
{
obj = new DateTime(1900, 1, 1);
}
builderSQL.Append(property.Name + " , ");
builderParams.Append("@" + property.Name + " , ");
lstParams.Add(MakeParam("@" + property.Name, obj));
}
builderSQL.Remove(builderSQL.Length - 2, 2);
builderParams.Remove(builderParams.Length - 2, 2);
builderSQL.Append(" ) values ");
builderSQL.Append(builderParams.ToString() + " ) ");
builderSQL.Append(";select @@IDENTITY;"); //返回最新的ID
object objTemp = await GetObjectAsync(" insert into " + tableName + " ( " + builderSQL.ToString(), lstParams.ToArray());
return(objTemp.ToInt());
}
public virtual async Task <bool> DeleteAsync <T>(object keyValue)
{
if (keyValue.IsNullOrEmpty())
{
throw new ArgumentException("参数 keyValue 值不能为空");
}
var type = typeof(T);
string tableName = AttrAssistant.GetTableName(type);
string key = AttrAssistant.GetKey(type);
string sql = $" delete from {tableName} where {key}=@{key} ";
var parameters = new DbParameter[] {
MakeParam($"@{key}", keyValue)
};
return(await ExecSQLAsync(sql, parameters));
}
public override bool Delete <T>(object keyValue)
{
if (keyValue == null)
{
throw new ArgumentException("参数 keyValue 值不能为 null");
}
var type = typeof(T);
string tableName = AttrAssistant.GetTableName(type);
string key = AttrAssistant.GetKey(type);
string sql = $" delete from {tableName} where {key}=:{key} ";
var parameters = new DbParameter[] {
MakeParam($":{key}", keyValue)
};
return(ExecSQL(sql, parameters));
}
public override async Task <IEnumerable <T> > GetPagerListAsync <T>(string condition, string sort, int pageIndex, int pageSize, string filter = "*", DbParameter[] conditionParameters = null)
{
var lstResult = new List <T>();
T tItem = default(T);
string tableName = AttrAssistant.GetTableName(typeof(T)); //表名
if (string.IsNullOrEmpty(condition))
{
condition = "1=1";
}
//起始记录
int startPosition = (pageIndex - 1) * pageSize + 1;
//截止记录
int endPosition = pageIndex * pageSize;
StringBuilder builder = new StringBuilder();
builder.AppendFormat(@"select {0} from
(
select t.*,rownum as rowno from
(
select * from {1} {2} {3}
) t where rownum<={5}
) {6} where rowno>={4}",
filter, tableName,
condition.IsNullOrEmpty() ? "" : " where " + condition,
sort.IsNullOrEmpty() ? "" : " order by " + sort, startPosition, endPosition, Utils.SinGooPagerAlias);
var reader = await GetDataReaderAsync(builder.ToString(), conditionParameters);
var refBuilder = ReflectionBuilder <T> .CreateBuilder(reader, dbProviderType);
while (reader.Read())
{
tItem = refBuilder.Build(reader, dbProviderType);
lstResult.Add(tItem);
}
reader.Close();
return(lstResult);
}
public virtual async Task <bool> UpdateModelAsync <T>(T model) where T : class
{
var arrProperty = typeof(T).GetProperties();
var builderSQL = new StringBuilder();
var lstParams = new List <DbParameter>();
foreach (var property in arrProperty)
{
//关键字key 和 不属于表的属性NotMapped 不更新
if (AttrAssistant.IsKey(property) || AttrAssistant.IsNotMapped(property))
{
continue;
}
//没有提供数据的字段也不处理
object obj = property.GetValue(model, null);
if (obj == null)
{
continue;
}
builderSQL.AppendFormat("{0}=@{0} , ", property.Name);
lstParams.Add(MakeParam("@" + property.Name, obj));
}
builderSQL.Remove(builderSQL.Length - 2, 2);
string key = AttrAssistant.GetKey(typeof(T));
if (!key.IsNullOrEmpty())
{
//主键值
object primaryKeyUniqueValue = RefProperty.GetPropertyValue(model, key);
builderSQL.AppendFormat(" where {0}=@primaryKeyUniqueValue ", key);
lstParams.Add(MakeParam("@primaryKeyUniqueValue", primaryKeyUniqueValue));
}
return(await ExecSQLAsync(" update " + AttrAssistant.GetTableName(typeof(T)) + " set " + builderSQL.ToString(), lstParams.ToArray()));
}
public void BulkInsert <T>(DataTable dt) where T : class
{
BulkInsert(dt, AttrAssistant.GetTableName(typeof(T)));
}
public virtual async Task <int> GetCountAsync <T>(string condition = "", DbParameter[] conditionParameters = null) => await GetCountAsync(AttrAssistant.GetTableName(typeof(T)), condition);
public virtual int GetCount <T>(string condition = "", DbParameter[] conditionParameters = null) => GetCount(AttrAssistant.GetTableName(typeof(T)), condition);
public override async Task <int> InsertModelAsync <T>(T model, string tableName = "")
{
var dictSql = new Dictionary <string, (string, DbParameter[])>();
var arrProperty = typeof(T).GetProperties();
var builderSQL = new StringBuilder();
var builderParams = new StringBuilder(" ( ");
var lstParams = new List <DbParameter>();
bool isKeyAutoSEQ = false;
if (tableName.IsNullOrEmpty())
{
tableName = AttrAssistant.GetTableName(typeof(T));
}
foreach (PropertyInfo property in arrProperty)
{
//oracle没有自动增长的列，创建了序列也要主动赋值
if (AttrAssistant.IsKey(property) && property.PropertyType.Name == "Int32")
{
isKeyAutoSEQ = true;
builderSQL.Append(property.Name + " , ");
builderParams.AppendFormat("SEQ_{0}.nextval, ", tableName.ToUpper());
}
else
{
//NotMapped 是自定义的字段，不属于表，所以要排除，非自增Key不处理
if (AttrAssistant.IsNotMapped(property) || (AttrAssistant.IsKey(property) && property.PropertyType.Name != "Int32"))
{
continue;
}
object obj = property.GetValue(model, null);
if (obj == null)
{
continue; //null无法加入到参数，跳过
}
//日期类型没有值时，默认0001-1-1报错：SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM
//因此设置新的默认值是 1900-1-1
if (property.PropertyType.Name.Equals("DateTime") && ((DateTime)obj).Equals(new DateTime(0001, 1, 1)))
{
obj = new DateTime(1900, 1, 1);
}
builderSQL.Append(property.Name + " , ");
builderParams.Append(":" + property.Name + " , ");
lstParams.Add(MakeParam(":" + property.Name, obj));
}
}
builderSQL.Remove(builderSQL.Length - 2, 2);
builderParams.Remove(builderParams.Length - 2, 2);
builderSQL.Append(" ) values ");
builderSQL.Append(builderParams.ToString() + " ) ");
dictSql.Add("NOVALUE", (" insert into " + tableName + " ( " + builderSQL.ToString(), lstParams.ToArray()));
/*
* 先建立一个序列
* create sequence SEQ_SINGOO minvalue 1 maxvalue 9999999999 start with 1 increment by 1 nocache
* insert into cms_user(AutoID,UserName) VALUES(SEQ_SINGOO.nextval,'张三');
* select SEQ_SINGOO.currval from dual; //取值
*/
if (isKeyAutoSEQ)
{
dictSql.Add("REVALUE", ($"select SEQ_{tableName.ToUpper()}.currval from dual", null));
}
var result = await ExecOracleTransAsync(dictSql);
return((result != null && result.Count() == 2) ? result.ToList()[1] : 0);
}
