/// <summary>
/// 构造函数
/// </summary>
public BaseDAL()
{
Type t = typeof(TClass);
this.tableName = "[" + t.Name + "]";
object[] tableAtts = t.GetCustomAttributes(typeof(TableAttribute), true);
// 获取表名称
if (tableAtts.Length > 0)
{
this.tableName = "[" + ((TableAttribute)tableAtts[0]).Name.Replace("dbo.", "") + "]";
}
PropertyInfo[] propertyInfos = ModelPropertyInfoCache.Instance.GetProperties <TClass>();
// 获取实体的属性
foreach (PropertyInfo pi in propertyInfos)
{
object[] colAtts = pi.GetCustomAttributes(typeof(ColumnAttribute), true);
if (colAtts.Length == 0)
{
continue;
}
ColumnAttribute colAtt = colAtts[0] as ColumnAttribute;
ColumnDbType column = new ColumnDbType();
column.GetDbType(colAtt.DbType);
column.IsPrimaryKey = colAtt.IsPrimaryKey;
column.ColumnName = pi.Name;
colList.Add(column);
propList.Add(pi);
}
isLogicDelete = t.GetProperty("DelStatus") != null;
}
/// <summary>
/// 批量删除（只有一个主键的）
/// </summary>
/// <param name="keys"></param>
/// <returns></returns>
public int Delete(ICollection keys)
{
ColumnDbType cdt = colList.Where(c => c.IsPrimaryKey).Single();
List <SqlParameter> paras = new List <SqlParameter>();
StringBuilder inParas = new StringBuilder();
int i = 0;
foreach (object obj in keys)
{
SqlParameter para = new SqlParameter();
para.ParameterName = "@P" + i;
para.SqlDbType = cdt.SqlDbType;
para.Value = obj;
inParas.Append(para.ParameterName + ",");
paras.Add(para);
i++;
}
inParas.Remove(inParas.Length - 1, 1);
string strSql = "";
if (isLogicDelete)
{
strSql = string.Format("Update {0} Set DelStatus=1 Where {1} in ({2})", tableName, cdt.ColumnName, inParas);
}
else
{
strSql = string.Format("Delete From {0} Where {1} in ({2})", tableName, cdt.ColumnName, inParas);
}
return(NonQuery(strSql, paras.ToArray()));
}
/// <summary>
/// 单笔删除（只有一个主键的）
/// </summary>
/// <param name="keys"></param>
/// <returns></returns>
public int Delete(object key)
{
if (key is ICollection)
{
return(Delete(key as ICollection));
}
ColumnDbType cdt = colList.Where(c => c.IsPrimaryKey).Single();
List <SqlParameter> paras = new List <SqlParameter>();
StringBuilder inParas = new StringBuilder();
SqlParameter para = new SqlParameter();
para.ParameterName = "@" + cdt.ColumnName;
para.SqlDbType = cdt.SqlDbType;
para.Value = key;
paras.Add(para);
string strSql = "";
if (isLogicDelete)
{
strSql = string.Format("Update {0} Set DelStatus=1 Where {1}=@{1}", tableName, cdt.ColumnName);
}
else
{
strSql = string.Format("Delete From {0} Where {1}=@{1}", tableName, cdt.ColumnName);
}
return(NonQuery(strSql, paras.ToArray()));
}
/// <summary>
/// 根据多键实体判断是否存在记录
/// </summary>
/// <param name="keyProps"></param>
/// <returns></returns>
public virtual bool IsHas(Dictionary <string, object> keyProps)
{
List <ColumnDbType> cList = new List <ColumnDbType>();
foreach (string key in keyProps.Keys)
{
ColumnDbType cdt = null;
for (int i = 0; i < colList.Count; i++)
{
if (colList[i].ColumnName.ToLower() == key.ToLower())
{
cdt = colList[i];
break;
}
}
if (cdt == null)
{
throw new Exception(string.Format("表{0}未找到属性{1}", tableName, key));
}
cdt.Value = keyProps[key];
cList.Add(cdt);
}
StringBuilder whereList = new StringBuilder();
List <SqlParameter> paras = new List <SqlParameter>();
for (int i = 0; i < cList.Count; i++)
{
whereList.Append(" " + cList[i].ColumnName + "=@" + cList[i].ColumnName + " And");
SqlParameter para = new SqlParameter();
para.ParameterName = "@" + cList[i].ColumnName;
para.SqlDbType = cList[i].SqlDbType;
if (cList[i].Size > 0)
{
para.Size = cList[i].Size;
}
para.Value = cList[i].Value;
paras.Add(para);
}
whereList.Remove(whereList.Length - 4, 4);
string strSql = string.Format("Select 1 From {0} Where {1} {2}", tableName, isLogicDelete ? "DelStatus=0 And " : "", whereList);
using (DataAccess da = new DataAccess(ConnString))
{
SqlDataReader sdr = da.ExecuteReader(strSql, paras.ToArray());
return(sdr.HasRows);
}
}
private ColumnDbType GetColumnDbType(string paraName, string tableColumn)
{
Type t = typeof(TClass);
string columnName = "";
if (tableColumn == "")
{
t = typeof(TClass);
columnName = paraName;
}
else
{
string[] tabs = tableColumn.Split('.');
if (tabs.Length != 1 && tabs.Length != 2)
{
return(null);
}
if (tabs.Length == 2)
{
t = t.Assembly.GetType(typeNameSpace + "." + tabs[0], false, true);
columnName = tabs[1];
}
else
{
t = typeof(TClass);
columnName = tabs[0];
}
}
PropertyInfo pi = t.GetProperty(columnName);
if (pi == null)
{
return(null);
}
object[] colAtts = pi.GetCustomAttributes(typeof(ColumnAttribute), true);
if (colAtts.Length == 0)
{
return(null);
}
ColumnAttribute colAtt = colAtts[0] as ColumnAttribute;
ColumnDbType column = new ColumnDbType();
column.GetDbType(colAtt.DbType);
column.IsPrimaryKey = colAtt.IsPrimaryKey;
column.ColumnName = pi.Name;
return(column);
}
/// <summary>
///
/// </summary>
/// <param name="dbType"></param>
/// <param name="isJsonSerialize"></param>
public EntityFieldAttribute(bool isJsonSerialize, ColumnDbType dbType)
: this()
{
IsJsonSerialize = isJsonSerialize;
DbType = dbType;
}
private static IDataParameter CreateParameter(DbBaseProvider dbProvider, string columnName, ColumnDbType dbType, object value)
{
IDataParameter parameter;
switch (dbType)
{
case ColumnDbType.UniqueIdentifier:
parameter = dbProvider.CreateParameterByGuid(columnName, (Guid)value);
break;
case ColumnDbType.LongText:
parameter = dbProvider.CreateParameterByLongText(columnName, value);
break;
case ColumnDbType.Text:
parameter = dbProvider.CreateParameterByText(columnName, value);
break;
case ColumnDbType.LongBlob:
parameter = dbProvider.CreateParameterLongBlob(columnName, value);
break;
case ColumnDbType.Blob:
parameter = dbProvider.CreateParameterByBlob(columnName, value);
break;
default:
parameter = dbProvider.CreateParameter(columnName, value);
break;
}
return(parameter);
}
/// <summary>
///
/// </summary>
/// <param name="dbType"></param>
/// <param name="isJsonSerialize"></param>
public EntityFieldAttribute(bool isJsonSerialize, ColumnDbType dbType)
: this()
{
IsJsonSerialize = isJsonSerialize;
DbType = dbType;
}
/// <summary>
/// 分页读取数据
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">分页大小</param>
/// <param name="whereCollection">条件值，例如：key为UserId>=@UserId，值为20</param>
/// <param name="orderBy">排序字段</param>
/// <returns></returns>
public virtual PagedResult GetPaged(int pageIndex, int pageSize, Dictionary <string, object> whereCollection, string orderBy)
{
List <ColumnDbType> cList = new List <ColumnDbType>();
StringBuilder whereList = new StringBuilder();
List <SqlParameter> paras = new List <SqlParameter>();
if (whereCollection != null)
{
foreach (string key in whereCollection.Keys)
{
ColumnDbType cdt = null;
for (int i = 0; i < colList.Count; i++)
{
string colName = this.GetFieldName(key.Trim());
if (colList[i].ColumnName.ToLower() == colName.ToLower())
{
cdt = colList[i];
break;
}
}
if (cdt == null)
{
throw new Exception(string.Format("表{0}未找到属性{1}", tableName, key));
}
whereList.Append(" " + key + " = @" + key + " And");
SqlParameter para = new SqlParameter();
para.ParameterName = "@" + key;
para.SqlDbType = cdt.SqlDbType;
if (cdt.Size > 0)
{
para.Size = cdt.Size;
}
para.Value = whereCollection[key];
paras.Add(para);
}
}
if (whereList.Length > 0)
{
whereList.Remove(whereList.Length - 4, 4);
whereList.Insert(0, "Where ");
}
if (whereList.Length > 0)
{
if (isLogicDelete)
{
whereList.Append(" And DelStatus=0");
}
}
else
{
if (isLogicDelete)
{
whereList.Append("Where DelStatus=0");
}
}
string strSql = "";
if (pageIndex == 1)
{
strSql = string.Format("Select Count(1) From {1} {2};Select Top {0} * From {1} {2} Order By {3}", pageSize, tableName, whereList, orderBy);
}
else
{
int startRowNum = (pageIndex - 1) * pageSize;
StringBuilder innerSql = new StringBuilder();
innerSql.AppendFormat("Select ROW_NUMBER() OVER (Order By {0}) AS [ROW_NUMBER],* From {1} {2}", orderBy, tableName, whereList);
strSql = string.Format("Select Count(1) From {1} {2};Select * From ({0}) as [t1] Where [t1].[ROW_NUMBER] BETWEEN @CRMPageIndex + 1 AND @CRMPageIndex + @CRMPageSize ORDER BY [t1].[ROW_NUMBER]", innerSql, tableName, whereList);
SqlParameter para = new SqlParameter();
para.ParameterName = "@CRMPageIndex";
para.SqlDbType = SqlDbType.Int;
para.Size = 4;
para.Value = startRowNum;
paras.Add(para);
para = new SqlParameter();
para.ParameterName = "@CRMPageSize";
para.SqlDbType = SqlDbType.Int;
para.Size = 4;
para.Value = pageSize;
paras.Add(para);
}
DataSet ds = null;
using (DataAccess da = new DataAccess(this.ConnString))
{
ds = da.ExecuteDataSet(strSql, paras.ToArray());
}
if (ds != null && ds.Tables.Count == 2)
{
return(new PagedResult(ds.Tables[1], Convert.ToInt32(ds.Tables[0].Rows[0][0])));
}
return(new PagedResult());
}
/// <summary>
/// 查询多键实体
/// </summary>
/// <param name="keyProps"></param>
/// <returns></returns>
public virtual TClass Get(Dictionary <string, object> keyProps)
{
List <ColumnDbType> cList = new List <ColumnDbType>();
foreach (string key in keyProps.Keys)
{
ColumnDbType cdt = null;
for (int i = 0; i < colList.Count; i++)
{
if (colList[i].ColumnName.ToLower() == key.ToLower())
{
cdt = colList[i];
break;
}
}
if (cdt == null)
{
throw new Exception(string.Format("表{0}未找到属性{1}", tableName, key));
}
cdt.Value = keyProps[key];
cList.Add(cdt);
}
StringBuilder whereList = new StringBuilder();
List <SqlParameter> paras = new List <SqlParameter>();
for (int i = 0; i < cList.Count; i++)
{
whereList.Append(" " + cList[i].ColumnName + "=@" + cList[i].ColumnName + " And");
SqlParameter para = new SqlParameter();
para.ParameterName = "@" + cList[i].ColumnName;
para.SqlDbType = cList[i].SqlDbType;
if (cList[i].Size > 0)
{
para.Size = cList[i].Size;
}
para.Value = cList[i].Value;
paras.Add(para);
}
whereList.Remove(whereList.Length - 4, 4);
string strSql = string.Format("Select * From {0} Where {1} {2}", tableName, isLogicDelete ? "DelStatus=0 And " : "", whereList);
DataSet ds = null;
using (DataAccess da = new DataAccess(ConnString))
{
ds = da.ExecuteDataSet(strSql, paras.ToArray());
}
List <TClass> list = new List <TClass>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
list = EntityHelper.GetEntityListByDT <TClass>(ds.Tables[0], null);
}
if (list.Count > 1)
{
throw new Exception(string.Format("表{0}返回不只一条记录", tableName));
}
return(list.Count == 1 ? list[0] : null);
}