//SQL命令 获取删除命令
public static SqlCommand GetDeleteCommand(IBaseEntity info)
{
if (info == null)
{
return(null);
}
Type tp = info.GetType();
string tbName;
var attrs = tp.GetCustomAttributes(typeof(EntityTableNameAttribute), false);
if (attrs.Length > 0)
{
tbName = ((EntityTableNameAttribute)attrs[0]).TableName;
}
else
{
return(null);
}
var comm = new SqlCommand {
CommandType = CommandType.Text
};
var sb = new StringBuilder();
var memberAccessor = new DynamicMethodMemberAccessor();
sb.AppendFormat("DELETE FROM [{0}] WHERE ", tbName);
foreach (PropertyInfo prop in tp.GetProperties())
{
var eca = Attribute.GetCustomAttribute(prop, typeof(EntityColumnAttribute)) as EntityColumnAttribute;
if (eca != null && eca.Key)
{
string paramName = string.Format("@{0}", prop.Name);
var param = new SqlParameter(paramName, eca.DataType)
{
Value = memberAccessor.GetValue(info, prop.Name)
};
comm.Parameters.Add(param);
sb.AppendFormat("[{0}] = {1}", eca.Field, paramName);
break;
}
}
comm.CommandText = sb.ToString();
return(comm);
}
//从数据读取器获取实例
public static void GetEntityFromDataReader(SqlDataReader dr, ref IBaseEntity entity, bool getForeigns, bool getChldren)
{
var memberAccessor = new DynamicMethodMemberAccessor();
var keyName = "Id";
foreach (PropertyInfo property in entity.GetType().GetProperties())
{
var mde = Attribute.GetCustomAttribute(property, typeof(EntityColumnAttribute)) as EntityColumnAttribute;
if (mde != null)
{
if (mde.Key)
{
keyName = property.Name;
}
if (getForeigns && mde.Foreign && !string.IsNullOrEmpty(mde.ForeignKey))
{
var fEntity = Activator.CreateInstance(property.PropertyType) as IBaseEntity;
if (fEntity != null)
{
GetSingleEntity(memberAccessor.GetValue(entity, mde.ForeignKey), ref fEntity);
memberAccessor.SetValue(entity, property.Name, fEntity);
}
}
else if (getChldren && mde.Children && !string.IsNullOrEmpty(mde.ChildKey))
{
var children = Activator.CreateInstance(property.PropertyType) as IList;
SearchByField(mde.ChildKey, memberAccessor.GetValue(entity, keyName), ref children);
memberAccessor.SetValue(entity, property.Name, children);
}
else
{
if (!string.IsNullOrEmpty(mde.Field) && !(dr[mde.Field] is DBNull))
{
memberAccessor.SetValue(entity, property.Name, dr[mde.Field]);
}
}
}
}
}
//SQL命令 获取更新命令
public static SqlCommand GetUpdateCommand(IBaseEntity info)
{
if (info == null)
{
return(null);
}
Type tp = info.GetType();
string tbName;
var attrs = tp.GetCustomAttributes(typeof(EntityTableNameAttribute), false);
if (attrs.Length > 0)
{
tbName = ((EntityTableNameAttribute)attrs[0]).TableName;
}
else
{
return(null);
}
var comm = new SqlCommand {
CommandType = CommandType.Text
};
var sb = new StringBuilder();
var sbW = new StringBuilder();
var memberAccessor = new DynamicMethodMemberAccessor();
sb.AppendFormat("UPDATE [{0}] SET ", tbName);
sbW.Append(" WHERE 1 = 2");
foreach (PropertyInfo prop in tp.GetProperties())
{
var eca = Attribute.GetCustomAttribute(prop, typeof(EntityColumnAttribute)) as EntityColumnAttribute;
if (eca != null && !eca.Foreign && !eca.Children)
{
string paramName = string.Format("@{0}", prop.Name);
var param = new SqlParameter(paramName, eca.DataType)
{
Value = memberAccessor.GetValue(info, prop.Name)
};
if (param.Value == null && (eca.DataType == DbType.String || eca.DataType == DbType.AnsiString))
{
param.Value = DBNull.Value;
}
comm.Parameters.Add(param);
if (eca.Key || eca.Unique)
{
sbW.AppendFormat(" OR [{0}] = {1}", eca.Field, paramName);
}
else
{
sb.AppendFormat("[{0}] = {1},", eca.Field, paramName);
}
}
}
sb.Remove(sb.Length - 1, 1).Append(sbW);
comm.CommandText = sb.ToString();
return(comm);
}
//SQL命令 获取插入命令
public static SqlCommand GetInsertCommand(IBaseEntity info)
{
if (info == null)
{
return(null);
}
Type tp = info.GetType();
string tbName;
var attrs = tp.GetCustomAttributes(typeof(EntityTableNameAttribute), false);
if (attrs.Length > 0)
{
tbName = ((EntityTableNameAttribute)attrs[0]).TableName;
}
else
{
return(null);
}
var comm = new SqlCommand {
CommandType = CommandType.Text
};
var sb = new StringBuilder();
var sbV = new StringBuilder();
var memberAccessor = new DynamicMethodMemberAccessor();
sb.AppendFormat("INSERT INTO [{0}] (", tbName);
sbV.Append(" VALUES (");
foreach (PropertyInfo prop in tp.GetProperties())
{
var eca = Attribute.GetCustomAttribute(prop, typeof(EntityColumnAttribute)) as EntityColumnAttribute;
if (eca != null && !eca.Key && !eca.Foreign && !eca.Children)
{
sb.AppendFormat("[{0}],", eca.Field);
string paramName = string.Format("@{0}", prop.Name);
sbV.AppendFormat("{0},", paramName);
var param = new SqlParameter(paramName, eca.DataType);
param.Value = memberAccessor.GetValue(info, prop.Name);
if (param.Value == null && (eca.DataType == DbType.String || eca.DataType == DbType.AnsiString))
{
param.Value = DBNull.Value;
}
comm.Parameters.Add(param);
}
}
sbV.Remove(sbV.Length - 1, 1).Append(");SELECT @ScopeIdentity = SCOPE_IDENTITY();");
var idParam = new SqlParameter("@ScopeIdentity", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
comm.Parameters.Add(idParam);
sb.Remove(sb.Length - 1, 1).Append(")").Append(sbV);
comm.CommandText = sb.ToString();
return(comm);
}