/// <summary>
/// 用拼接SQL的方式，
/// 把当前表以及其使用到的所有外键表链接起来，
/// 拼接成一条SQL子查询，供分页的存储过程使用
/// </summary>
private String PreparedTable(T_Role entity, bool isFuzzySearch, bool isSignleResult)
{
String sql = getFKColumnsSQL("[gdce_task].dbo.T_Role", T_Role.DBColumns, isFuzzySearch, isSignleResult);
if (entity == null)
{
return(sql);
}
if (entity.id != null)
{
sql += " and [gdce_task].dbo.T_Role.id = " + entity.id;
}
if (entity.name != null)
{
sql += String.Format(" and [gdce_task].dbo.T_Role.name = '{0}' ", AntiInjection(entity.name));
}
if (entity.descr != null)
{
sql += String.Format(" and [gdce_task].dbo.T_Role.descr = '{0}' ", AntiInjection(entity.descr));
}
return(AntiInjection(sql));
}
public int Add(T_Role entity)
{
AntiSQLInjection(entity);
StringBuilder sql = new StringBuilder();
sql.Append(" exec sp_executesql N'");
sql.Append(" INSERT INTO [gdce_task].dbo.[T_Role] ( ");
sql.Append(" [name],");
sql.Append(" [descr],");
sql.Remove(sql.Length - 1, 1);
sql.Append(" ) values ( ");
sql.Append(entity.name != null ? "@name," : "DEFAULT,");
sql.Append(entity.descr != null ? "@descr," : "DEFAULT,");
sql.Remove(sql.Length - 1, 1);
sql.Append(" );select case when @@identity is not null then @@identity else 1 end as id;', N'");
sql.Append(entity.name != null ? "@name nvarchar(max)," : "");
sql.Append(entity.descr != null ? "@descr nvarchar(max)," : "");
sql.Remove(sql.Length - 1, 1);
sql.Append("', ");
sql.Append(entity.name != null ? "@name=N'" + entity.name + "'," : "");
sql.Append(entity.descr != null ? "@descr=N'" + entity.descr + "'," : "");
sql.Remove(sql.Length - 1, 1);
return(Convert.ToInt32(DBHelper.ExecuteDataTable(sql.ToString()).Rows[0][0]));
}
/// <summary>
/// 以实体类作为查询条件获取符合条件的数据集，NULL值不作为查询条件
/// </summary>
public List <T_Role> Search(T_Role entity, int pageNum, int pageSize, String criteria, T_Role.Columns OrderBy, AscDesc AscDesc, out int totalRecords)
{
String sql = "";
//如果没有任何查询条件，则不开启模糊查询列以减少SQL消耗
if (String.IsNullOrWhiteSpace(criteria))
{
sql = this.PreparedTable(entity, false, false);
}
else
{
sql = this.PreparedTable(entity, true, false);
}
String tbl = "'(" + sql + ") as t'";
List <SqlParameter> pars = new List <SqlParameter>();
String crit = getSearchCriterias(criteria);
String order = OrderBy.ToString() + " " + AscDesc.ToString();
//有待改进，没危险的int数据用了参数插入，有风险的字符串反而直接组装SQL，虽然做了简单的去单引号处理
sql = String.Format("exec sp_paging {0},@pageNum,@pageSize,'{1}','{2}'", tbl, crit, order);
pars.Add(new SqlParameter("@pageNum", pageNum));
pars.Add(new SqlParameter("@pageSize", pageSize));
DataTable dt = DBHelper.ExecuteDataTable(sql, pars);
totalRecords = 0;
if (dt.Rows.Count > 0)
{
totalRecords = (int)dt.Rows[0]["TotalRecord"];
}
return(DtToList(dt));
}
public T_Role GetById(Int32 id)
{
T_Role entity = new T_Role()
{
id = id
};
return(GetByModel(entity, false));
}
//处理本类的所有属性，把String类型里面的单引号处理一下
public void AntiSQLInjection(T_Role entity)
{
Type t = entity.GetType();
foreach (System.Reflection.PropertyInfo p in t.GetProperties())
{
if (p.GetValue(entity, null) is String)
{
String s = AntiInjection(p.GetValue(entity, null).ToString());
p.SetValue(entity, s, null);
}
}
}
public T_Role GetByModel(T_Role entity, bool isLazy)
{
T_Role result = GetByModel(entity);
if (result == null)//如果传入的实体为空（或者根据ID找不到）,则需要new一个空对象
{
result = new T_Role();
}
if (!isLazy)
{
}
return(result);
}
public List <T_Role> DtToList(DataTable dt)
{
List <T_Role> entities = new List <T_Role>();
foreach (DataRow dr in dt.Rows)
{
T_Role entity = new T_Role(dr, "");
try
{
}
catch (Exception ex) { }
entities.Add(entity);
}
return(entities);
}
public T_Role GetByModel(T_Role entity)
{
String sql = String.Format("exec sp_executesql N'{0}'", PreparedTable(entity, false, true));
DataTable dt = DBHelper.ExecuteDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
entity = new T_Role(dt.Rows[0], "");
return(entity);
}
else
{
return(null);
}
}
public int Update(T_Role entity)
{
AntiSQLInjection(entity);
StringBuilder sql = new StringBuilder();
sql.Append(" exec sp_executesql N'");
sql.Append(" UPDATE [gdce_task].dbo.[T_Role] SET ");
sql.Append(" [name] = @name,");
sql.Append(" [descr] = @descr,");
sql.Remove(sql.Length - 1, 1);
sql.Append(" WHERE [id] = ''" + entity.id + "'' ', N'");
sql.Append("@name nvarchar(max),");
sql.Append("@descr nvarchar(max),");
sql.Remove(sql.Length - 1, 1);
sql.Append("', ");
sql.Append("@name=" + (entity.name != null ? "N'" + entity.name + "'," : "NULL,"));
sql.Append("@descr=" + (entity.descr != null ? "N'" + entity.descr + "'," : "NULL,"));
sql.Remove(sql.Length - 1, 1);
return(DBHelper.ExecuteNonQuery(sql.ToString()));
}
/// <summary>
/// 以实体类作为查询条件获取符合条件的数据集，NULL值不作为查询条件
/// </summary>
public List <T_Role> GetAllByModel(T_Role entity)
{
String sql = String.Format("exec sp_executesql N'{0}'", PreparedTable(entity, false, false));
return(DtToList(DBHelper.ExecuteDataTable(sql)));
}