/// <summary>
/// ����condition��value���ֵ���£�condition�����в�Ϊnull���ֶζ�����Ϊ��ѯ����
/// ��value�е����в�Ϊnull���ֶν��ᱻ����
/// ֧��ģ����ѯ
/// </summary>
/// <param name="value">��Ҫ���µ�ֵ</param>
/// <param name="condition">����ʱ������</param>
/// <returns>Ӱ��ļ�¼��</returns>
public virtual int UpdateWithModel(Model value, Model condition)
{
this.setSystemField(value);
ParmCollection conditionParms = this.Table.PrepareUpdateParms(value, this.IsLikeMode, condition);
return(this.Internal_DataHelper.ExecuteNonQuery(string.Format(conditionParms.UpdateSql, conditionParms.WhereSql), conditionParms));
}
/// <summary>
/// ����condition��value���ֵ���£�condition�����в�Ϊnull���ֶζ�����Ϊ��ѯ����
/// ��value�е����в�Ϊnull���ֶν��ᱻ����
/// ֧��ģ����ѯ
/// </summary>
/// <param name="value">��Ҫ���µ�ֵ</param>
/// <param name="condition">����ʱ������</param>
/// <returns>���º��ֵ����</returns>
public virtual IEnumerable <OutputModel> UpdateWithModelAndReturn(Model value, Model condition)
{
this.setSystemField(value);
ParmCollection conditionParms = this.Table.PrepareUpdateParms(value, this.IsLikeMode, condition);
return(this.List(string.Format(conditionParms.UpdateSql, conditionParms.WhereSql), conditionParms).ToList());
}
/// <summary>
/// 根据具体Model对象生成Update参数及子句
/// </summary>
/// <param name="value">更新Model对象，用于生成Update部分，属性为null值的不作为生成条件</param>
/// <param name="condition">条件Model对象，用于生成Where部分，属性为null值的不作为生成条件</param>
/// <param name="isLikeMode">是否模糊查询</param>
/// <returns>Update参数及子句</returns>
public virtual ParmCollection PrepareUpdateParms(object value, bool isLikeMode = false, object condition = null)
{
ParmCollection temp = new ParmCollection();
string update = string.Empty;
foreach (IDataParameter p in this.Parameters)
{
foreach (DBFieldInfoAttribute f in this.Fields)
{
if (f.ColumnName == p.SourceColumn && f.Field.GetValue(value) != null && f.SqlDbType != SqlDbType.Timestamp)
{
p.Value = f.Field.GetValue(value);
update += string.Format("{0}={1}U, ", f.ColumnNameFix, f.ParameterName);
IDataParameter pp = this.CloneParameter(p);
pp.ParameterName = f.ParameterName + "U";
temp.Add(pp);
break;
}
}
}
if (update.Length == 0)
{
return(temp);
}
ParmCollection where = this.PrepareConditionParms(condition, isLikeMode);
update = string.Format("UPDATE {0} SET {1} OUTPUT INSERTED.* {2}", this.TableName, update.Substring(0, update.Length - 2), "{0}");
where.UpdateSql = update;
where.AddRange(temp);
return(where);
}
/// <summary>
/// 根据具体Model对象获取非自动增量的参数集合
/// </summary>
/// <param name="o">Model对象</param>
/// <returns>非自动增量的参数集合</returns>
public virtual ParmCollection PrepareNotIdentityParms(object o)
{
ParmCollection temp = new ParmCollection();
foreach (IDataParameter p in this.Parameters)
{
foreach (DBFieldInfoAttribute f in this.Fields)
{
if (!f.IsIdentity && f.ColumnName == p.SourceColumn)
{
object v = f.Field.GetValue(o);
if (f.IsKey && v == null)
{
v = Guid.NewGuid();
}
if (f.DefaultValue != null)
{
if (f.DefaultValue.GetType().IsEnum)
{
f.DefaultValue = (int)f.DefaultValue;
}
}
p.Value = v == null ? (f.DefaultValue == null ? DBNull.Value : f.DefaultValue) : v;
p.DbType = v == null ? (f.DefaultValue == null ? p.DbType : DbType.String) : p.DbType;
temp.Add(p);
break;
}
}
}
return(temp);
}
/// <summary>
/// ���������ֶ�ֵ���£�model��������κ�ֵ��������Ϊ��������
/// ��model�г��Զ������ֶ���������в�Ϊnull���ֶν��ᱻ����
/// </summary>
/// <param name="model">Ҫ���µ�ֵ</param>
/// <returns>Ӱ��ļ�¼��</returns>
public virtual OutputModel UpdateWithIdentityAndReturn(Model model)
{
this.setSystemField(model);
ParmCollection conditionParms = this.Table.PrepareUpdateParms(model);
conditionParms.AddRange(this.Table.PrepareAllParms(model));
return(this.Select(string.Format(conditionParms.UpdateSql, this.Table.IdentityParm.WhereSql), conditionParms));
}
/// <summary>
/// ����model��ֵ��ѯ��model��ÿһ����null�ֶζ�����Ϊ��ѯ����
/// ������ѯ���м�¼����modelΪnull����
/// ֧��ģ����ѯ
/// </summary>
/// <param name="model">��Ϊ��ѯ������model</param>
/// <returns>���ϲ�ѯ���������ݱ�</returns>
public virtual DataTable ListTable(Model model)
{
ParmCollection parms = this.Table.PrepareConditionParms(model, this.IsLikeMode);
DataTable dt = ListTable(
string.Format(this.Table.Select, parms.WhereSql, this.OrderBy)
, parms);
return(dt);
}
/// <summary>
/// ��model��ij��˱�ʶΪIdentity,Timestamp֮�������ֵ�������ݿ�
/// </summary>
/// <param name="model">��Ҫ�������ݿ��model</param>
/// <returns>Ӱ��ļ�¼��</returns>
public virtual int Insert(Model model)
{
model.GmtCreateUser = this.Operater;
model.GmtCreateDate = DateTime.Now;
model.GmtUpdateUser = this.Operater;
model.GmtUpdateDate = DateTime.Now;
ParmCollection parms = this.Table.PrepareNotIdentityParms(model);
return(Internal_DataHelper.ExecuteNonQuery(this.Table.Insert, parms));
}
/// <summary>
/// ����model��ֵ��Ϊ��ѯ����,model��ÿһ����null�ֶζ�����Ϊ��ѯ������
/// ��ȡModel��Ӧ�����ݿ���ڷ��������ļ�¼���ϼ�
/// ֧��ģ����ѯ
/// </summary>
/// <param name="model">��������model</param>
/// <returns>��¼���ϼ�</returns>
public virtual int GetCount(Model model)
{
ParmCollection parms = this.Table.PrepareConditionParms(model, this.IsLikeMode);
object o = Internal_DataHelper.ExecuteScalar(CommandType.Text, string.Format("SELECT COUNT(*) FROM {0} {1}", this.Table.TableName, parms.WhereSql), parms);
if (o != null)
{
return(int.Parse(o.ToString()));
}
return(0);
}
/// <summary>
/// ��model��ij��˱�ʶΪIdentity,Timestamp֮�������ֵ�������ݿ�
/// </summary>
/// <param name="model">��Ҫ�������ݿ��model</param>
/// <returns>�����Ľ��</returns>
public virtual OutputModel InsertAndReturn(Model model)
{
model.GmtCreateUser = this.Operater;
model.GmtCreateDate = DateTime.Now;
model.GmtUpdateUser = this.Operater;
model.GmtUpdateDate = DateTime.Now;
ParmCollection parms = this.Table.PrepareNotIdentityParms(model);
using (IDataReader rdr = Internal_DataHelper.ExecuteReader(this.Table.Insert, parms))
{
return(rdr.Read() ? (OutputModel)this.Table.ReadDataReader(rdr, new OutputModel()) : null);
}
}
/// <summary>
/// ���������ֶ�ֵ���£�model��������κ�ֵ��������Ϊ��������
/// ��model�г��Զ������ֶ���������в�Ϊnull���ֶν��ᱻ����
/// </summary>
/// <param name="models">Ҫ���µ�ֵ����</param>
/// <returns>Ӱ��ļ�¼��</returns>
public virtual int UpdateWithIdentity(params Model[] models)
{
int returnCount = 0;
foreach (var model in models)
{
this.setSystemField(model);
ParmCollection conditionParms = this.Table.PrepareUpdateParms(model);
conditionParms.AddRange(this.Table.PrepareAllParms(model));
returnCount += this.Internal_DataHelper.ExecuteNonQuery(string.Format(conditionParms.UpdateSql, this.Table.IdentityParm.WhereSql), conditionParms);
}
return(returnCount);
}
/// <summary>
/// ���������ֶ�ֵ���£�model��������κ�ֵ��������Ϊ��������
/// ��model�����г��Զ������ֶ���������в�Ϊnull���ֶν��ᱻ����
/// </summary>
/// <param name="models">Ҫ���µ�ֵ����</param>
/// <returns>���º�Ľ��</returns>
public virtual IEnumerable <OutputModel> UpdateWithIdentityAndReturn(Model[] models)
{
var resultList = new List <OutputModel>();
foreach (var model in models)
{
this.setSystemField(model);
ParmCollection conditionParms = this.Table.PrepareUpdateParms(model);
conditionParms.AddRange(this.Table.PrepareAllParms(model));
var result = this.Select(string.Format(conditionParms.UpdateSql, this.Table.IdentityParm.WhereSql), conditionParms);
resultList.Add(result);
}
return(resultList);
}
public virtual PageModel <OutputModel> ListPage(Model model)
{
bool flag = typeof(BasePageModel).IsAssignableFrom(model.GetType());
PageModel <OutputModel> pageModel = new PageModel <OutputModel>();
ParmCollection parmCollection = base.Table.PrepareConditionParms(model, base.IsLikeMode);
string requirSQL = string.Format(base.Table.Select, parmCollection.WhereSql, string.Empty);
PageInfo pageInfo = new PageInfo(requirSQL, base.OrderBy, model.PageSize ?? 10, model.PageNO ?? 1);
pageModel.ListData = List(pageInfo.PageSQL, parmCollection);
pageModel.PageNO = (model.PageNO ?? 1);
pageModel.PageSize = (model.PageSize ?? 10);
using (IDataReader dataReader = base.Internal_DataHelper.ExecuteReader(pageInfo.PageCountSQL, parmCollection))
{
pageModel.DataCount = (dataReader.Read() ? dataReader.GetValue <int>("PAGECOUNT") : 0);
}
return(pageModel);
}
/// <summary>
/// 根据model的值作为查询条件,model里每一个非null字段都会作为查询条件
/// 获取分页的数据
/// 支持模糊查询
/// </summary>
/// <param name="model">作为条件的model</param>
/// <returns>符合查询条件的分页数据集合</returns>
public virtual PageModel <OutputModel> ListPage(Model model)
{
bool isIMyInterface = typeof(BasePageModel).IsAssignableFrom(model.GetType());
var result = new PageModel <OutputModel>();
ParmCollection parms = this.Table.PrepareConditionParms(model, this.IsLikeMode);
var requirSql = string.Format(this.Table.Select, parms.WhereSql, string.Empty);
PageInfo pageInfo = new PageInfo(requirSql, this.OrderBy, model.PageSize ?? 10, model.PageNO ?? 1);
result.ListData = List(pageInfo.PageSQL, parms);
result.PageNO = model.PageNO ?? 1;
result.PageSize = model.PageSize ?? 10;
using (IDataReader rdr = Internal_DataHelper.ExecuteReader(pageInfo.PageCountSQL, parms))
{
result.DataCount = rdr.Read() ? rdr.GetValue <int>("PAGECOUNT") : 0;
}
return(result);
}
/// <summary>
/// 根据Model数据表属性，预生成Select，Update，Delete，Insert语句主干
/// </summary>
/// <param name="table">Model数据表属性</param>
/// <param name="dataHelper">数据库连接对象</param>
/// <param name="t">Model类型</param>
internal void Initial(ModelAttribute table, IDbDataHelper dataHelper, Type t)
{
string selectColumns = string.Empty;
string updateColumns = string.Empty;
string insertColumns = string.Empty;
string identityColumn = string.Empty;
string insertselectColumns = string.Empty;
string keysColumns = string.Empty;
this.m_parameters = new Collection <IDataParameter>();
foreach (DBFieldInfoAttribute f in table)
{
if (f.Field == null)
{
FieldInfo field = t.GetField(f.FieldName);
if (field == null)
{
throw new MyModel.MyException(string.Format("Model[{0}]中没有字段名为[{1}]的private或protected的字段。", t, f.FieldName));
}
f.Field = field;
}
f.ParameterPrefix = dataHelper.ParameterPrefix;
f.Prefix = dataHelper.Prefix;
f.Suffix = dataHelper.Suffix;
IDataParameter parm = dataHelper.Parameter(f.ParameterName, dataHelper.DbType(f), f.ColumnName);
this.m_parameters.Add(parm);
// 自增字段
if (f.IsIdentity)
{
this.m_identityParm = new ParmCollection(parm);
this.m_identityParm.WhereSql = string.Format(" WHERE {0}={1} ", f.ColumnNameFix, f.ParameterName);
identityColumn = f.ColumnNameFix;
}
else
{
if (f.SqlDbType != SqlDbType.Timestamp)
{
updateColumns += string.Format("{0}={1}, ", f.ColumnNameFix, f.ParameterName);
insertColumns += string.Format("{0}, ", f.ParameterName);
insertselectColumns += string.Format("{0}, ", f.ColumnNameFix);
}
selectColumns += string.Format("{0}, ", f.ColumnNameFix);
}
if (f.IsKey)
{
this.m_keysParms.Add(parm);
keysColumns += string.Format("{0}={1} AND ", f.ColumnNameFix, f.ParameterName);
}
if (f.IsMainKey)
{
this.m_treeMainKeyColName = f.ColumnName;
}
if (f.IsParentKey)
{
this.m_treeParentKeyColName = f.ColumnName;
}
}
if (identityColumn == string.Empty && selectColumns == string.Empty)
{
throw new MyModel.MyException(t.ToString() + " 类中没有一个Field应用DbFieldInfoAttribute属性。");
}
string select = selectColumns.Substring(0, selectColumns.Length - 2);
string insertselect = insertselectColumns.Substring(0, insertselectColumns.Length - 2);
this.m_fields = table;
this.m_modelName = t.ToString();
this.m_tableName = table.AttrTable.TableName;
this.m_select = string.Format("SELECT {0}{1} FROM {2} {3} {4}", identityColumn == string.Empty ? string.Empty : (identityColumn + ", "), select, "{0}", "{1}", "{2}");
this.m_update = string.Format("UPDATE {0} SET {1} {2}", "{0}", updateColumns.Substring(0, updateColumns.Length - 2), "{1}");
this.m_insert = string.Format("INSERT INTO {0}({1}) OUTPUT INSERTED.* VALUES({2})", "{0}", insertselect, insertColumns.Substring(0, insertColumns.Length - 2));
this.m_insertCol = string.Format("INSERT INTO {0}({1}) VALUES({2})", "{0}", insertselect, "{1}");
this.m_keysParms.WhereSql = keysColumns.Length > 0 ? string.Format(" WHERE {0}", keysColumns.Substring(0, keysColumns.Length - 4)) : string.Empty;
}
/// <summary>
/// ����model��ֵ��Ϊ��ѯ����,model��ÿһ����null�ֶζ�����Ϊ��ѯ����
/// ���ط��������ĵ�һ��model
/// ֧��ģ����ѯ
/// </summary>
/// <param name="model">��������model</param>
/// <returns>���ϲ�ѯ�����ĵ�һ��model</returns>
public virtual OutputModel SelectWithModel(Model model)
{
ParmCollection parms = this.Table.PrepareConditionParms(model, this.IsLikeMode);
return(this.Select(string.Format(this.Table.Select, parms.WhereSql, this.OrderBy), parms));
}
/// <summary>
/// ���Զ������ֶ�Ϊ��ѯ��������ȡ��������model
/// </summary>
/// <param name="identity">��Ϊ�������Զ�����</param>
/// <returns>��������model</returns>
public virtual OutputModel SelectWithIdentity(long identity)
{
ParmCollection parms = this.Table.PrepareIdentityParm(identity);
return(this.Select(string.Format(this.Table.Select, parms.WhereSql, this.OrderBy), parms));
}
/// <summary>
/// 根据具体Model对象生成Where参数及子句
/// 对象中属性为null值的不作为生成条件
/// </summary>
/// <param name="o">Model对象</param>
/// <param name="or">条件连接是否使用or，true为使用or，false为使用and</param>
/// <param name="isLikeMode">是否模糊查询</param>
/// <returns>Where参数及子句</returns>
public virtual ParmCollection PrepareSearchParms(object o, bool or, bool isLikeMode)
{
ParmCollection temp = new ParmCollection();
if (o == null)
{
return(temp);
}
string where = string.Empty;
foreach (IDataParameter p in this.Parameters)
{
foreach (DBFieldInfoAttribute f in this.Fields)
{
object val = f.Field.GetValue(o);
if (f.ColumnName == p.SourceColumn && val != null && val.ToString() != string.Empty)
{
if (f.Field.FieldType == typeof(byte[]))
{//字段为byte[0]时不做查询条件
var byteVal = (byte[])val;
if (byteVal.Length == 0)
{
break;
}
}
string link;
if (f.Field.FieldType == typeof(string) && isLikeMode)
{
val = val.ToString().Replace("[", "[[]").Replace("%", "[%]").Replace("^", "[^]").Replace("_", "[_]");
link = "LIKE";
if (f.LikeEqual == EnumLikeMode.NoLike)
{
link = "=";
}
if (f.LikeEqual == EnumLikeMode.AllLike)
{
val = "%" + val + "%";
}
if (f.LikeEqual == EnumLikeMode.BackwardLike)
{
val = val + "%";
}
if (f.LikeEqual == EnumLikeMode.ForwardLike)
{
val = "%" + val;
}
}
else
{
val = f.Field.GetValue(o);
link = "=";
}
p.Value = val;
where += string.Format("{0} {1} {2} {3} ", f.ColumnNameFix, link, f.ParameterName, or ? "OR" : "AND");
temp.Add(p);
break;
}
}
}
if (where.Length > 0)
{
where = string.Format("{0}{1}", " WHERE ", where.Substring(0, where.Length - 5));
temp.WhereSql = where;
}
return(temp);
}
/// <summary>
/// �������ֶ�Ϊ��ѯ������model�еķ������ֶξ�����Ϊ��ѯ��������ȡ��������model
/// </summary>
/// <param name="model">��������model</param>
/// <returns>��������model</returns>
public virtual OutputModel SelectWithKeys(Model model)
{
ParmCollection parms = this.Table.PrepareKeysParms(model);
return(this.Select(string.Format(this.Table.Select, parms.WhereSql, this.OrderBy), parms));
}
/// <summary>
/// ����model��ֵ��Ϊ������ѯ������model��ÿһ����null�ֶζ�����Ϊ��ѯ����
/// ������ѯ���м�¼����modelΪnull����
/// ֧��ģ����ѯ
/// </summary>
/// <param name="model">��������model</param>
/// <returns>���ϲ�ѯ���������ݼ���</returns>
public virtual IEnumerable <OutputModel> List(Model model)
{
ParmCollection parms = this.Table.PrepareConditionParms(model, this.IsLikeMode);
return(List(string.Format(this.Table.Select, parms.WhereSql, this.OrderBy), parms));
}
