110,535
社区成员
发帖
与我相关
我的任务
分享
#region 把SQLDataReader转成Class一条
private static T ConvertOneClass<T>(Type tp, SqlDataReader sdr)
{
try
{
T t = System.Activator.CreateInstance<T>();
for (int i = 0; i < sdr.FieldCount; i++)
{
if (sdr.IsDBNull(i))
{
continue;
}
string ColName = sdr.GetName(i);
object ColValue = sdr.GetValue(i);
if (ColValue == null)
{
continue;
}
PropertyInfo pi = tp.GetProperty(ColName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
if (pi != null)
{
if (pi.CanWrite)
{
if (pi.PropertyType == typeof(Boolean))
{
if (ColValue.ToString() == "0" || ColValue.ToString() == "1")
{
ColValue = ColValue.ToString() == "0" ? false : true;
}
}
try
{
pi.SetValue(t, ColValue, null);
}
catch
{
throw new Exception(ColName + ":转换类型错误,可能原因数据库与实体类型不匹配!");
}
}
}
}
return t;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region SqlDataReader转成Class
public static T SDRConvertClass<T>(SqlDataReader sdr)
{
try
{
Type tp = typeof(T);
if (sdr.Read())
{
return ConvertOneClass<T>(tp, sdr);
}
else
{
return default(T);
}
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
/// <summary>
/// 根据数据行和类型初始化实例的方法
/// 请注意这里的命名规范,主键表的主键为"Id",外键表的外键为外键表名+"Id"
/// 如果不同,则会引发SqlException
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public object Intitial(DataRow dr, Type type)
{
///初始化所需要的属性,包括:
///
///实例化的引用,此处为t
///实例化对象的属性数组,此处引用为prs
object t = Assembly.Load(type.Assembly.FullName).CreateInstance(type.Namespace + "." + type.Name);
PropertyInfo[] prs = t.GetType().GetProperties();
///循环为实例对象的属性赋值
for (int i = 0; i < prs.Length; i++)
{
///基元类型,基元类型的可空扩展类型,string,datetime,decimal类型可以直接获取属性值
if (t.GetType().GetProperty(prs[i].Name).PropertyType.IsPrimitive
|| t.GetType().GetProperty(prs[i].Name).PropertyType.Name == "String"
|| t.GetType().GetProperty(prs[i].Name).PropertyType.Name == "DateTime"
|| t.GetType().GetProperty(prs[i].Name).PropertyType.Name == "Decimal"
|| t.GetType().GetProperty(prs[i].Name).PropertyType.Name.Contains("Nullable"))
{
t.GetType().GetProperty(prs[i].Name).SetValue(t, dr[prs[i].Name], null);
}
///其他类型则重新编写Sql语句查询其外键表,实例化对象后赋值
else
{
///拼写Sql语句
SqlStrB.Clear();
SqlStrB.AppendFormat("select * from [{0}] where Id=@id", prs[i].Name);
///初始化参数数组
SqlParameter[] para = new SqlParameter[1];
para[0] = new SqlParameter("@id", dr[prs[i].Name + "Id"]);
///查询所需的外键数据
DataRow row = new DBHelper().Search(SqlStrB.ToString(), para).Rows[0];
///为相应的属性实例化并赋值
if (row != null)
{
t.GetType().GetProperty(prs[i].Name).SetValue(t, Intitial(row, prs[i].PropertyType), null);
}
///未获取时引发异常,异常信息为"数据检索失败,请检查Sql语句及参数!"
else
{
throw new Exception("数据检索失败,请检查Sql语句及参数!");
}
}
}
return t;
}
/// <summary>
/// 获取实例的单一属性值
/// </summary>
/// <param name="model">model为该实例</param>
/// <param name="pro">pro为要获取值的属性</param>
/// <returns></returns>
protected dynamic GetProValue(object model, PropertyInfo pro)
{
///若属性类型为.net的基元类型或String类型、DateTime类型与Decimal类型,则直接返回属性值
if (pro.PropertyType.IsPrimitive
|| pro.PropertyType.Name == "String"
|| pro.PropertyType.Name == "DateTime"
|| pro.PropertyType.Name == "Decimal")
{
return model.GetType().GetProperty(pro.Name).GetValue(model, null);
}
///否则创建相应实例,用递归给实例属性赋值并返回实例
else
{
///获取属性类型的程序集
Assembly ass = pro.PropertyType.Assembly;
///获取属性类型的命名空间
string nameSpace = pro.PropertyType.Namespace;
///获取属性类型的类名
string className = pro.PropertyType.Name;
///创建该类实例
dynamic result = Assembly.Load(ass.FullName)
.CreateInstance(nameSpace + "." + className);
///获取实例的属性数组
PropertyInfo[] prs = result.GetType().GetProperties();
///用递归循环给实例的属性赋值
for (int i = 0; i < result.GetType().GetProperties().Length; i++)
{
result.GetType().GetProperty(prs[i].Name).SetValue(result, GetProValue(model.GetType().GetProperty(result.GetType().Name).GetValue(model, null), prs[i]), null);
}
///返回实例
return result;
}
}
注意你的实体类里外键属性命名必须是外键表+id..当然如果你有其他规范 你就自己改吧...
<SQL Id="UpdateExam">
UPDATE dbo.Exam_$TableName$ SET
E_Year = #Exam.E_Year#,
E_Area = #Exam.E_Area#,
E_Grade = #Exam.E_Grade#,
E_Diff = #Exam.E_Diff#,
E_Kind = #Exam.E_Kind#,
E_QuestionTypes = #Exam.E_QuestionTypes#,
E_FromPaper = #Exam.E_FromPaper#,
E_Unit = #Exam.E_Unit#,
E_Owner = #Exam.E_Owner#,
E_ExtInfo = #Exam.E_ExtInfo#,
E_State = #Exam.E_State#,
E_FileIndex = #Exam.E_FileIndex#,
E_IsObjective = #Exam.E_IsObjective#,
E_IsObjectiveAnswer = #Exam.E_IsObjectiveAnswer#
WHERE E_GUID = #Exam.E_GUID#
</SQL>
<SQL Id="SaveMyExam">
INSERT INTO dbo.MyCollectExam
(
E_UserGuid ,
E_OriginGUID ,
E_SubjectGUID ,
E_CollectType
)
SELECT * FROM(
<LOOP Name="list" LinkStr="UNION ALL">
Select
'$UserGuid$' E_UserGuid,
#list[].OriginGUID# E_OriginGUID,
'$SubjectGUID$' E_SubjectGUID,
'$MyExamType$' E_CollectType
</LOOP>
)a
WHERE NOT EXISTS(SELECT 1 FROM dbo.MyCollectExam b
WHERE a.E_UserGuid=b.E_UserGuid
AND a.E_CollectType=b.E_CollectType
AND a.E_SubjectGUID=b.E_SubjectGUID
AND a.E_OriginGUID=b.E_OriginGUID)
</SQL>
public UserLog GetUserLogById(int Id)
{
UserLog log = new UserLog();
string sql = "select * from LogTable where Id=@Id";
SqlParameter param = new SqlParameter("@Id", Id);
SqlDataReader dr = SqlHelper.ExecutReader(sql, param);
if (dr.Read())
{
log.Id = Convert.ToInt32(dr["Id"]);
log.UserName = dr["UserName"].ToString();
log.UserType = dr["UserType"].ToString();
log.Operation = dr["Operation"].ToString();
log.CreateDateTime = dr["CreateDateTime"].ToString();
}
dr.Close();
return log;
}