private IEnumerable <Model> ReaderBind <Model>(TabelAttribute tableAttr, DbDataReader reader) where Model : TableBase, new()
{
var result = new List <Model>();
while (reader.Read())
{
var model = new Model();
foreach (var column in tableAttr.Columns)
{
var vv = reader[column.Name];
if (vv is DBNull)
{
//column.SetValue(model, column.DefaultValue);
}
else
{
var method = column.Property.GetSetMethod();
var v2 = Special.ChangeType(vv, column.Property.PropertyType);
column.SetValue(model, v2);
}
}
result.Add(model);
}
reader.Close();
return(result);
}
private IEnumerable <Model> TableBind <Model>(TabelAttribute tableAttr, DataTable dataTable) where Model : TableBase, new()
{
var result = new List <Model>();
foreach (DataRow dr in dataTable.Rows)
{
var model = new Model();
foreach (var column in tableAttr.Columns)
{
var vv = dr[column.Name];
if (vv is DBNull)
{
//column.SetValue(model, column.DefaultValue);
}
else
{
object v2 = null;
//处理可空 类型 datetime?
if (column.Property.PropertyType.IsGenericType &&
column.Property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
v2 = Special.ChangeType(vv, column.Property.PropertyType.GetGenericArguments()[0]);
}
else
{
v2 = Special.ChangeType(vv, column.Property.PropertyType);
}
var method = column.Property.GetSetMethod();
column.SetValue(model, v2);
}
}
result.Add(model);
}
return(result);
}
private string SqlUpdate(TabelAttribute tableAttr, TableBase model)
{
StringBuilder sb = new StringBuilder();
sb.Append("update ");
sb.Append(tableAttr.Name);
sb.Append(" set ");
foreach (var a in tableAttr.Columns.Where(c => c.IsPrimary == false))
{
if (model.IsChangeColumn(a.Name))
{
sb.Append(a.Name);
sb.Append(" = ");
sb.Append(ParamPerFix);
sb.Append(a.Name);
sb.Append(", ");
}
}
sb.Remove(sb.Length - 2, 2);
sb.Append(" where ");
var pk = tableAttr.Columns.Single(c => c.IsPrimary).Name;
sb.Append(pk);
sb.Append(" = ");
sb.Append(ParamPerFix);
sb.Append(pk);
return(sb.ToString());
}
private string SqlDelete(TabelAttribute tableAttr)
{
StringBuilder sb = new StringBuilder();
sb.Append("delete from ");
sb.Append(tableAttr.Name);
sb.Append(" where ");
var pk = tableAttr.Columns.Single(c => c.IsPrimary).Name;
sb.Append(pk);
sb.Append(" = ");
sb.Append(ParamPerFix);
sb.Append(pk);
return(sb.ToString());
}
/// <summary>
///
/// </summary>
/// <param name="tableAttr"></param>
/// <param name="where">name = ? and age = ?</param>
/// <returns></returns>
public string SqlSelect(TabelAttribute tableAttr, string where, List <string> parNames, int skip, int take)
{
StringBuilder sb = new StringBuilder();
sb.Append("select ");
foreach (var a in tableAttr.Columns)
{
sb.Append(a.Name);
sb.Append(", ");
}
sb.Append("rowid ");
sb.Append("from ");
sb.Append(tableAttr.Name);
if (!string.IsNullOrEmpty(where))
{
sb.Append(" where ");
//分析条件字段的类型
string[] keywords = { " and ", " or ", " like " };
//var columnReg = new Regex(@" \w+ ");
//where = columnReg.Replace(where, delegate(Match match)
//{
// if (match.Groups[0].Value.ToLower().In(keywords))//是关键字
// {
// }
// return match.Groups[0].Value.ToLower();
//});
//替换参数
var parreg = new Regex(@"\?");
where = parreg.Replace(where, delegate(Match match)
{
var s = ParamPerFix + "par" + match.Index;
parNames.Add(s);
return(s);
});
sb.Append(where);
}
sb.Append(" limit " + skip + " " + take);
return(sb.ToString());
}
public string SqlSelect(TabelAttribute tableAttr, string where, List <string> parNames, int skip, int take)
{
StringBuilder sb = new StringBuilder();
sb.Append("select ");
foreach (var a in tableAttr.Columns)
{
sb.Append(a.Name);
sb.Append(", ");
}
sb.Append("rownum rn ");
sb.Append("from ");
sb.Append(tableAttr.Name);
if (!string.IsNullOrEmpty(where))
{
sb.Append(" where ");
//替换参数
var parreg = new Regex(@"\?");
where = parreg.Replace(where, delegate(Match match)
{
var s = ParamPerFix + "par" + match.Index;
parNames.Add(s);
return(s);
});
sb.Append(where);
}
sb.Insert(0, "with pager as ( ");
sb.Append(" ) select * from pager where rn > " + skip);
if (take > 0)
{
sb.Append(" and rn <= " + (skip + take));
}
//sb.Append(" limit " + skip + " " + take);
return(sb.ToString());
}
private string SqlInsert(TabelAttribute tableAttr)
{
StringBuilder sb = new StringBuilder();
sb.Append("insert into ");
sb.Append(tableAttr.Name);
sb.Append(" ( ");
foreach (var a in tableAttr.Columns)
{
sb.Append(a.Name);
sb.Append(", ");
}
sb.Remove(sb.Length - 2, 2);
sb.Append(" ) values ( ");
foreach (var a in tableAttr.Columns)
{
sb.Append(ParamPerFix);
sb.Append(a.Name);
sb.Append(", ");
}
sb.Remove(sb.Length - 2, 2);
sb.Append(" ) ");
return(sb.ToString());
}
public string SqlSelect(TabelAttribute tableAttr, string where, List <string> parNames, int skip, int take)
{
throw new NotImplementedException();
}