在第一篇《一步步实现自己的ORM（一）》里，我们用反射获取类名、属性和值，我们用这些信息开发了简单的INSERT方法，在上一篇文章里我们提到主键为什么没有设置成自增长类型，单单从属性里我们无法识别哪个是主键，今天我们用Attribute来标识列，关于Attribute，引用MSDN里描述
MADN的定义为：公共语言运行时允许添加类似关键字的描述声明，叫做attributes, 它对程序中的元素进行标注，如类型、字段、方法和属性等。Attributes和Microsoft .NET Framework文件的元数据（metadata）保存在一起，可以用来向运行时描述你的代码，或者在程序运行的时候影响应用程序的行为。 我们简单的总结为：定制特性attribute，本质上是一个类，其为目标元素提供关联附加信息，并在运行期以反射的方式来获取附加信息。
简单来说Attribute就是描述类、方法、属性参数等信息的。
可参考《浅析C#中的Attribute》
在这里我们定义2个Attribute，用来描述表和字段。
[AttributeUsage(AttributeTargets.Class)]classTableAttribute : Attribute
{///<summary>///表名///</summary>publicstringName {get;PRivateset; }publicTableAttribute(stringname)
{this.Name =name;
}
}
[AttributeUsage(AttributeTargets.Property)]classColumnAttribute : Attribute
{///<summary>///是否为数据库自动生成///</summary>publicboolIsGenerated {get;set; }///<summary>///列名///</summary>publicstringName {get;privateset; }publicColumnAttribute(stringname)
{this.Name =name;
}
}
View Code
修改后的实体类如下：
[Table("tb_Users")]publicclassUser
{
[Column("UserId",IsGenerated =true)]publicintUserId {get;set; }
[Column("Email")]publicstringEmail {get;set; }
[Column("CreatedTime")]publicDateTime CreatedTime {get;set; }
}
我们把表结构也修改下
CREATETABLE[dbo].[tb_Users]([UserId][int]NOTNULLidentity(1,1)PRIMARYKEY,[Email][nvarchar](100)NULL,[CreatedTime][datetime]NULL)
下面的问题，就是我们如何得到表名和字段名呢？我们还是采用反射来实现，先获取表名：
TableAttribute[] tableAttr = (TableAttribute[])typeof(User).GetCustomAttributes(typeof(TableAttribute),true);if(tableAttr.Length>0)
{
Console.WriteLine(tableAttr[0].Name);
}
运行结果
再获取列名，先查找property，然后找Attribute，代码如下：
varproperties =typeof(User).GetProperties();for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varcolumnAttrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute),true);if(columnAttrs.Length>0)
{
Console.WriteLine("字段名：{0}，是否为自动生成：{1}", columnAttrs[0].Name, columnAttrs[0].IsGenerated);
}
}
运行结果
再来修改INSERT 方法如下：
publicstaticintInsert(User user)
{vartype =typeof(User);
Dictionary<string,object> parameters =newDictionary<string,object>();varproperties =type.GetProperties();stringtableName =string.Empty;
TableAttribute[] tableAttrs= (TableAttribute[])typeof(User).GetCustomAttributes(typeof(TableAttribute),true);if(tableAttrs.Length >0)
{
tableName= tableAttrs[0].Name;
}else{
tableName=type.Name;
}/*将所有的列放到集合里*/List<ColumnAttribute> columns =newList<ColumnAttribute>();for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varattrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute),true);if(attrs.Length >0)
{
columns.Add(attrs[0]);
}
}
StringBuilder sql=newStringBuilder();
sql.Append("INSERT INTO [").Append(tableName).Append("](");intparamIndex =0;for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varattrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute),true);if(attrs.Length >0&& attrs[0].IsGenerated ==false)
{if(paramIndex >0)
sql.Append(",");
sql.Append(attrs[0].Name);
paramIndex++;
}
}
sql.Append(") VALUES (");
paramIndex=0;for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varattrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute),true);if(attrs.Length >0&& attrs[0].IsGenerated ==false)
{if(paramIndex >0)
sql.Append(",");
sql.Append("@p").Append(paramIndex);
parameters.Add("@p"+ paramIndex, pi.GetValue(user,null));
paramIndex++;
}
}
sql.Append(")");
Console.WriteLine(sql);
SqlConnection conn=newSqlConnection(connectionString);varcmd =conn.CreateCommand();
cmd.CommandText=sql.ToString();foreach(variteminparameters)
{varpa =cmd.CreateParameter();
pa.ParameterName=item.Key;
pa.Value= item.Value ??DBNull.Value;
cmd.Parameters.Add(pa);
}
conn.Open();returncmd.ExecuteNonQuery();
}
View Code
运行结果
我们再定义一个IdAttribute 类用来表示主键，它不需要任何属性
[AttributeUsage(AttributeTargets.Property)]classIdAttribute :ColumnAttribute
{publicIdAttribute(stringname) :base(name)
{
}
}
有了主键我们下面可以写UPDATE和DELETE 方法：
UPDATE：
publicstaticintUpdate(User user)
{vartype =typeof(User);
Dictionary<string,object> parameters =newDictionary<string,object>();varproperties =type.GetProperties();stringtableName =string.Empty;
TableAttribute[] tableAttrs= (TableAttribute[])typeof(User).GetCustomAttributes(typeof(TableAttribute),true);if(tableAttrs.Length >0)
{
tableName= tableAttrs[0].Name;
}else{
tableName=type.Name;
}
StringBuilder sql=newStringBuilder();
sql.Append("UPDATE [").Append(tableName).Append("] SET");intparamIndex =0;for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varidAttrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute),true);if(idAttrs.Length >0)//如果是主键 跳过continue;varcolumnAttrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute),true);if(columnAttrs.Length >0)
{if(paramIndex >0)
sql.Append(",");//字段 = @psql.Append(columnAttrs[0].Name).Append("=").Append("@p"+paramIndex);/*参数*/parameters.Add("@p"+ paramIndex, pi.GetValue(user,null));
paramIndex++;
}
}
sql.Append("WHERE");intkeyIndex =0;for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varidAttrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute),true);if(idAttrs.Length >0)
{if(keyIndex >0)//考虑到有多个主键sql.Append("AND");
sql.Append(idAttrs[0].Name).Append("=").Append("@p").Append(paramIndex);/*参数*/parameters.Add("@p"+ paramIndex, pi.GetValue(user,null));
paramIndex++;
keyIndex++;
}
}
Console.WriteLine(sql);
SqlConnection conn=newSqlConnection(connectionString);varcmd =conn.CreateCommand();
cmd.CommandText=sql.ToString();foreach(variteminparameters)
{varpa =cmd.CreateParameter();
pa.ParameterName=item.Key;
pa.Value= item.Value ??DBNull.Value;
cmd.Parameters.Add(pa);
}
conn.Open();returncmd.ExecuteNonQuery();
}
View Code
调用代码：
Update(newUser()
{
UserId=1,
Email="new@new.com",
CreatedTime=DateTime.Now
});
运行结果：
DELETE方法：
publicstaticintDeleteByKey(paramsobject[] values)
{vartype =typeof(User);
Dictionary<string,object> parameters =newDictionary<string,object>();varproperties =type.GetProperties();stringtableName =string.Empty;
TableAttribute[] tableAttrs= (TableAttribute[])typeof(User).GetCustomAttributes(typeof(TableAttribute),true);if(tableAttrs.Length >0)
{
tableName= tableAttrs[0].Name;
}else{
tableName=type.Name;
}/*将所有的列放到集合里*/List<IdAttribute> columns =newList<IdAttribute>();for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varattrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute),true);if(attrs.Length >0)
{
columns.Add(attrs[0]);
}
}if(columns.Count !=values.Length)thrownewArgumentException("参数个数和主键数不一致");
StringBuilder sql=newStringBuilder();
sql.Append("DELETE FROM [").Append(tableName).Append("]").Append("WHERE");for(inti =0; i < columns.Count; i++)
{if(i >0)//考虑到有多个主键sql.Append("AND");
sql.Append(columns[i].Name).Append("=").Append("@p").Append(i);/*参数*/parameters.Add("@p"+i, values[i]);
}
Console.WriteLine(sql);
SqlConnection conn=newSqlConnection(connectionString);varcmd =conn.CreateCommand();
cmd.CommandText=sql.ToString();foreach(variteminparameters)
{varpa =cmd.CreateParameter();
pa.ParameterName=item.Key;
pa.Value= item.Value ??DBNull.Value;
cmd.Parameters.Add(pa);
}
conn.Open();returncmd.ExecuteNonQuery();
}
View Code
调用代码：
DeleteByKey(1);
运行结果
最后我们把增删改方法放在一个泛型类里。
classEntityHelper
{privateconststringconnectionString ="";publicstaticintInsert<T>(T entity)
{vartype =typeof(T);
Dictionary<string,object> parameters =newDictionary<string,object>();varproperties =type.GetProperties();stringtableName =string.Empty;
TableAttribute[] tableAttrs= (TableAttribute[])type.GetCustomAttributes(typeof(TableAttribute),true);if(tableAttrs.Length >0)
{
tableName= tableAttrs[0].Name;
}else{
tableName=type.Name;
}/*将所有的列放到集合里*/List<ColumnAttribute> columns =newList<ColumnAttribute>();for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varattrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute),true);if(attrs.Length >0)
{
columns.Add(attrs[0]);
}
}
StringBuilder sql=newStringBuilder();
sql.Append("INSERT INTO [").Append(tableName).Append("](");intparamIndex =0;for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varattrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute),true);if(attrs.Length >0&& attrs[0].IsGenerated ==false)
{if(paramIndex >0)
sql.Append(",");
sql.Append(attrs[0].Name);
paramIndex++;
}
}
sql.Append(") VALUES (");
paramIndex=0;for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varattrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute),true);if(attrs.Length >0&& attrs[0].IsGenerated ==false)
{if(paramIndex >0)
sql.Append(",");
sql.Append("@p").Append(paramIndex);
parameters.Add("@p"+ paramIndex, pi.GetValue(entity,null));
paramIndex++;
}
}
sql.Append(")");
Console.WriteLine(sql);
SqlConnection conn=newSqlConnection(connectionString);varcmd =conn.CreateCommand();
cmd.CommandText=sql.ToString();foreach(variteminparameters)
{varpa =cmd.CreateParameter();
pa.ParameterName=item.Key;
pa.Value= item.Value ??DBNull.Value;
cmd.Parameters.Add(pa);
}
conn.Open();returncmd.ExecuteNonQuery();
}publicstaticintUpdate<T>(T entity)
{vartype =typeof(T);
Dictionary<string,object> parameters =newDictionary<string,object>();varproperties =type.GetProperties();stringtableName =string.Empty;
TableAttribute[] tableAttrs= (TableAttribute[])type.GetCustomAttributes(typeof(TableAttribute),true);if(tableAttrs.Length >0)
{
tableName= tableAttrs[0].Name;
}else{
tableName=type.Name;
}
StringBuilder sql=newStringBuilder();
sql.Append("UPDATE [").Append(tableName).Append("] SET");intparamIndex =0;for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varidAttrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute),true);if(idAttrs.Length >0)//如果是主键 跳过continue;varcolumnAttrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute),true);if(columnAttrs.Length >0)
{if(paramIndex >0)
sql.Append(",");//字段 = @psql.Append(columnAttrs[0].Name).Append("=").Append("@p"+paramIndex);/*参数*/parameters.Add("@p"+ paramIndex, pi.GetValue(entity,null));
paramIndex++;
}
}
sql.Append("WHERE");intkeyIndex =0;for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varidAttrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute),true);if(idAttrs.Length >0)
{if(keyIndex >0)//考虑到有多个主键sql.Append("AND");
sql.Append(idAttrs[0].Name).Append("=").Append("@p").Append(paramIndex);/*参数*/parameters.Add("@p"+ paramIndex, pi.GetValue(entity,null));
paramIndex++;
keyIndex++;
}
}
Console.WriteLine(sql);
SqlConnection conn=newSqlConnection(connectionString);varcmd =conn.CreateCommand();
cmd.CommandText=sql.ToString();foreach(variteminparameters)
{varpa =cmd.CreateParameter();
pa.ParameterName=item.Key;
pa.Value= item.Value ??DBNull.Value;
cmd.Parameters.Add(pa);
}
conn.Open();returncmd.ExecuteNonQuery();
}publicstaticintDeleteByKey<T>(paramsobject[] values)
{vartype =typeof(T);
Dictionary<string,object> parameters =newDictionary<string,object>();varproperties =type.GetProperties();stringtableName =string.Empty;
TableAttribute[] tableAttrs= (TableAttribute[])type.GetCustomAttributes(typeof(TableAttribute),true);if(tableAttrs.Length >0)
{
tableName= tableAttrs[0].Name;
}else{
tableName=type.Name;
}/*将所有的列放到集合里*/List<IdAttribute> columns =newList<IdAttribute>();for(inti =0; i < properties.Length; i++)
{varpi =properties[i];varattrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute),true);if(attrs.Length >0)
{
columns.Add(attrs[0]);
}
}if(columns.Count !=values.Length)thrownewArgumentException("参数个数和主键数不一致");
StringBuilder sql=newStringBuilder();
sql.Append("DELETE FROM [").Append(tableName).Append("]").Append("WHERE");for(inti =0; i < columns.Count; i++)
{if(i >0)//考虑到有多个主键sql.Append("AND");
sql.Append(columns[i].Name).Append("=").Append("@p").Append(i);/*参数*/parameters.Add("@p"+i, values[i]);
}
Console.WriteLine(sql);
SqlConnection conn=newSqlConnection(connectionString);varcmd =conn.CreateCommand();
cmd.CommandText=sql.ToString();foreach(variteminparameters)
{varpa =cmd.CreateParameter();
pa.ParameterName=item.Key;
pa.Value= item.Value ??DBNull.Value;
cmd.Parameters.Add(pa);
}
conn.Open();returncmd.ExecuteNonQuery();
}
}
View Code
大功告成，我们修改下调用代码
EntityHelper.Insert(newUser()
{
Email="abc@123.com",
CreatedTime=DateTime.Now
});
EntityHelper.Update(newUser()
{
UserId=1,
Email="new@new.com",
CreatedTime=DateTime.Now
});
EntityHelper.DeleteByKey<User>(1);