最近在做项目时,频繁的使用增删改,把大量时间花费在写Sql语句和参数上,就想能不能写一个通用的方法省略掉写sql 和参数。想了一段时间,想到.net里面有Attitude类,可以自定义属性,这样就可以根据自定义属性获取sql字段和参数名,然后构造出sql语句和sqlParamertes ,要想根据不同表的增删改,可以用泛型实现。
首先定义一个继承自Attribute的自定义类:Entry包括 数据库列名、参数名、参数长度、参数类型、是否包括改字段属性。
代码如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public
class
Entry:Attribute
{
private string columnName;
/// <summary>
/// 列名
/// </summary>
public string ColumnName
{
get { return columnName; }
set { columnName = value; }
}
/// <summary>
/// 参数名
/// </summary>
private string paramName;
public string ParamName
{
get { return paramName; }
set { paramName = value; }
}
/// <summary>
/// 参数类型
/// </summary>
private SqlDbType paramType;
public SqlDbType ParamType
{
get { return paramType; }
set { paramType = value; }
}
/// <summary>
/// 参数大小
/// </summary>
private int paramLength;
public int ParamLength
{
get { return paramLength; }
set { paramLength = value; }
}
/// <summary>
/// 是否加入实体
/// </summary>
private bool show = true ;
public bool Show
{
get { return show; }
set { show = value; }
}
public Entry() { }
public Entry( string colName, SqlDbType paramType)
{
this .columnName = colName;
this .paramType = paramType;
}
public Entry( string colName, string paramName, SqlDbType paramType)
{
this .columnName = colName;
this .paramName = paramName;
this .paramType = paramType;
}
public Entry( string colName, string paramName, SqlDbType paramType, int paramLength)
{
this .columnName = colName;
this .paramName = paramName;
this .paramType = paramType;
this .ParamLength = paramLength;
}
public Entry( string colName, string paramName, SqlDbType paramType, int paramLength, bool show)
{
this .columnName = colName;
this .paramName = paramName;
this .paramType = paramType;
this .ParamLength = paramLength;
this .show = show;
}
}
{
private string columnName;
/// <summary>
/// 列名
/// </summary>
public string ColumnName
{
get { return columnName; }
set { columnName = value; }
}
/// <summary>
/// 参数名
/// </summary>
private string paramName;
public string ParamName
{
get { return paramName; }
set { paramName = value; }
}
/// <summary>
/// 参数类型
/// </summary>
private SqlDbType paramType;
public SqlDbType ParamType
{
get { return paramType; }
set { paramType = value; }
}
/// <summary>
/// 参数大小
/// </summary>
private int paramLength;
public int ParamLength
{
get { return paramLength; }
set { paramLength = value; }
}
/// <summary>
/// 是否加入实体
/// </summary>
private bool show = true ;
public bool Show
{
get { return show; }
set { show = value; }
}
public Entry() { }
public Entry( string colName, SqlDbType paramType)
{
this .columnName = colName;
this .paramType = paramType;
}
public Entry( string colName, string paramName, SqlDbType paramType)
{
this .columnName = colName;
this .paramName = paramName;
this .paramType = paramType;
}
public Entry( string colName, string paramName, SqlDbType paramType, int paramLength)
{
this .columnName = colName;
this .paramName = paramName;
this .paramType = paramType;
this .ParamLength = paramLength;
}
public Entry( string colName, string paramName, SqlDbType paramType, int paramLength, bool show)
{
this .columnName = colName;
this .paramName = paramName;
this .paramType = paramType;
this .ParamLength = paramLength;
this .show = show;
}
}
接下来就是根据自定类动态构造增加数据的方法。要想获取自定义属性,可以采用反射机制获取字段名,参数名,参数长度等信息。
下面四泛型实现的添加数据的Add方法
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public
static
void
Add
<
T
>
(T entity,
string
tableName)
where
T :
class
,
new
()
{
Type type = typeof (T);
List < string > fields = new List < string > ();
List < string > paramNames = new List < string > ();
PropertyInfo[] propertys = type.GetProperties();
List < SqlParameter > parameters = new List < SqlParameter > ();
SqlParameter param = null ;
foreach (PropertyInfo info in propertys) // Folder所有属性
{
object [] attributes = info.GetCustomAttributes( true );
foreach ( object attribute in attributes)
{
if (attribute is Entry)
{
Entry entry = attribute as Entry;
fields.Add(entry.ColumnName);
paramNames.Add(entry.ParamName);
param = new SqlParameter(entry.ParamName, entry.ParamType, entry.ParamLength);
param.Value = info.GetValue (entity, null );
parameters.Add(param);
}
}
}
StringBuilder sBuilder = new StringBuilder();
string fieldSql = string .Empty;
string paramSql = string .Empty;
sBuilder.Append( " Insert into " );
sBuilder.Append(tableName);
sBuilder.Append( " ( " );
foreach ( string field in fields)
{
fieldSql += field + " , " ;
}
fieldSql = fieldSql.Substring( 0 , fieldSql.LastIndexOf( ' , ' ));
sBuilder.Append(fieldSql);
sBuilder.Append( " ) values ( " );
foreach ( string pN in paramNames)
{
paramSql += " @ " + pN + " , " ;
}
paramSql = paramSql.Substring( 0 , paramSql.LastIndexOf( ' , ' ));
sBuilder.Append(paramSql);
sBuilder.Append( " ) " );
ExecuteSql(sBuilder.ToString(), parameters.ToArray ());
}
{
Type type = typeof (T);
List < string > fields = new List < string > ();
List < string > paramNames = new List < string > ();
PropertyInfo[] propertys = type.GetProperties();
List < SqlParameter > parameters = new List < SqlParameter > ();
SqlParameter param = null ;
foreach (PropertyInfo info in propertys) // Folder所有属性
{
object [] attributes = info.GetCustomAttributes( true );
foreach ( object attribute in attributes)
{
if (attribute is Entry)
{
Entry entry = attribute as Entry;
fields.Add(entry.ColumnName);
paramNames.Add(entry.ParamName);
param = new SqlParameter(entry.ParamName, entry.ParamType, entry.ParamLength);
param.Value = info.GetValue (entity, null );
parameters.Add(param);
}
}
}
StringBuilder sBuilder = new StringBuilder();
string fieldSql = string .Empty;
string paramSql = string .Empty;
sBuilder.Append( " Insert into " );
sBuilder.Append(tableName);
sBuilder.Append( " ( " );
foreach ( string field in fields)
{
fieldSql += field + " , " ;
}
fieldSql = fieldSql.Substring( 0 , fieldSql.LastIndexOf( ' , ' ));
sBuilder.Append(fieldSql);
sBuilder.Append( " ) values ( " );
foreach ( string pN in paramNames)
{
paramSql += " @ " + pN + " , " ;
}
paramSql = paramSql.Substring( 0 , paramSql.LastIndexOf( ' , ' ));
sBuilder.Append(paramSql);
sBuilder.Append( " ) " );
ExecuteSql(sBuilder.ToString(), parameters.ToArray ());
}
下面是一个具体例子:根据自定义属性配置参数名、参数类型等基本信息
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public class Folder
{
/// <summary>
/// Folder Id Guid
/// </summary>
[Entry( " Id " , " Id " ,SqlDbType.NVarChar, 32 )]
public string Id { set ; get ; }
/// <summary>
/// Folder Name
/// </summary>
[Entry( " Name " , " Name " , SqlDbType.NVarChar, 32 )]
public string Name { set ; get ; }
/// <summary>
/// Folder Parent Id
/// </summary>
[Entry( " ParentId " , " ParentId " , SqlDbType.NVarChar, 32 )]
public string ParentId { set ; get ; }
/// <summary>
///
/// </summary>
[Entry( " UserId " , " UserId " , SqlDbType.NVarChar, 32 )]
public int UserId { set ; get ; }
/// <summary>
/// Pengding type: add/update/delete
/// </summary>
public string Type { set ; get ; }
[Entry( " CreateTime " , " CreateTime " , SqlDbType.DateTime, 32 )]
public DateTime CreateTime { set ; get ; }
}
同理,Delete、Update可以写出一个通用的方法来。不过,Update可能有点麻烦,很多时候只更新个别字段,这个时候就 不能把所有字段都更新。最近工作比较忙,这个问题以后有时间再去研究。