我在工作中使用的自己实现的超简易ORM工具
我在7月份到了家新公司,要维护一个在原来.net1.1上的系统。我进去一看代码就傻了眼了,满屏的SQL语句,看得脑袋嗡嗡的.但是苦于.net1.1,又不敢大动直接上其他的orm..顺便 Castle Activerecord 的.net1.1版还真用不来,于是一怒之下,决定自己动手,自己搞一个,遂自己瞎糊弄了一个,在项目中用了,感觉良好,嘿嘿,先不多说,把代码统统拷上来:
//wxy create at 2008-8-31
//自己实现了一个简易的ORM工具,方便数据库操作
//关键是我实在不想写那些
//Insert,Update语句了
using System;
using System.Collections;
namespace WXYLiteORM
{
using System.Data;
using System.Data.SqlClient;
#region 数据表属性
/// <summary>
/// 指明类对应的表
/// </summary>
[AttributeUsage(AttributeTargets.Class, AllowMultiple=false),
Serializable()]
public class DataBaseTableAttribute:Attribute
{
private string _table;
private string _database=string.Empty ;
private string _owner=string.Empty ;
/// <summary>
/// 数据表名
/// </summary>
public string Table
{
get{return _table;}
set{_table=value;}
}
/// <summary>
/// 数据库所有者
/// </summary>
public string Owner
{
get{return _owner;}
set{_owner=value;}
}
/// <summary>
/// 所在数据库
/// </summary>
public string DataBase
{
get{return _database ;}
set{_database=value;}
}
/// <summary>
///
/// </summary>
/// <param name="table">数据表名</param>
public DataBaseTableAttribute(string table)
{
_table=table;
}
public DataBaseTableAttribute(string table,string database):this(table)
{
_database=database;
}
public DataBaseTableAttribute(string table,string database,string owner):this(table,database )
{
_owner=owner;
}
/// <summary>
///
/// </summary>
public DataBaseTableAttribute()
{}
}
#endregion
#region 数据字段属性
/// <summary>
/// 指定数据表字段
/// </summary>
[AttributeUsage(AttributeTargets.Property , AllowMultiple=false),
Serializable()]
public class TableFieldAttribute:Attribute
{
private string _column;
private bool _hasSetSqlType=false;
private bool _update=true;
private bool _insert=true;
/// <summary>
/// 是否在update时更新
/// </summary>
public bool UpDate
{
get{return _update;}
set{_update=value;}
}
/// <summary>
/// 是否在insert时插入
/// </summary>
public bool Insert
{
get{return _insert ;}
set{_insert=value;}
}
/// <summary>
/// 是否已显示设置Sql类型
/// </summary>
public bool HasSetSqlType
{
get{ return _hasSetSqlType ;}
}
/// <summary>
/// 对应的字段名
/// </summary>
public string Column
{
get{return _column;}
set{_column=value;}
}
private SqlDbType _dbType ;
/// <summary>
/// 对应的Sql字段类型
/// </summary>
public SqlDbType DbType
{
get{ return _dbType ;}
set{ _hasSetSqlType=true ;_dbType=value;}
}
/// <summary>
/// 构造TableFieldAttribute的新实例
/// </summary>
public TableFieldAttribute()
{
}
/// <summary>
///
/// </summary>
/// <param name="column">对应字段名称</param>
public TableFieldAttribute(string column)
{
_column=column;
}
/// <summary>
///
/// </summary>
/// <param name="dbtype">对应字段类型</param>
public TableFieldAttribute(SqlDbType dbtype)
{
_hasSetSqlType=true;
_dbType=dbtype;
}
/// <summary>
///
/// </summary>
/// <param name="column">对应字段名称</param>
/// <param name="dbtype">对应字段类型</param>
public TableFieldAttribute(string column,SqlDbType dbtype):this(column)
{
_hasSetSqlType=true;
_dbType=dbtype;
}
}
#endregion
#region 主键相关属性
/// <summary>
/// 定义主键可能的策略
/// </summary>
[Serializable]
public enum PrimaryKeyType
{
/// <summary>
///
/// 自动编号
/// </summary>
Identity,
/// <summary>
/// 建立一个GUID
/// </summary>
Guid,
/// <summary>
/// 生成一个可以排序的Guid
/// </summary>
GuidComb,
/// <summary>
/// 主键值由用户指定
/// </summary>
Assigned
}
/// <summary>
/// 主键属性
/// </summary>
[AttributeUsage(AttributeTargets.Property, AllowMultiple=false), Serializable]
public class PrimaryKeyAttribute:Attribute
{
private PrimaryKeyType _generator = PrimaryKeyType.GuidComb ;
private string _column;
private SqlDbType _dbtype;
private bool _hasSetSqlType=false;
/// <summary>
/// 是否已显示设置Sql类型
/// </summary>
public bool HasSetSqlType
{
get{ return _hasSetSqlType ;}
}
/// <summary>
/// 字段名
/// </summary>
public string Column
{
get { return _column; }
set { _column = value; }
}
/// <summary>
/// 主键生成策略
/// </summary>
public PrimaryKeyType Generator
{
get { return _generator; }
set { _generator = value; }
}
/// <summary>
/// 字段类型
/// </summary>
public SqlDbType DbType
{
get{return _dbtype;}
set{ _hasSetSqlType=true; _dbtype=value;}
}
/// <summary>
/// 初始化PrimaryKeyAttribute的实例
/// </summary>
/// <param name="generator">主键生成器</param>
public PrimaryKeyAttribute(PrimaryKeyType generator)
{
this._generator = generator;
}
/// <summary>
/// 初始化PrimaryKeyAttribute的实例
/// </summary>
/// <param name="generator">主键生成器</param>
/// <param name="column">字段名</param>
public PrimaryKeyAttribute(PrimaryKeyType generator, String column) : this(generator)
{
this._column = column;
}
/// <summary>
/// 初始化PrimaryKeyAttribute的实例
/// </summary>
/// <param name="column">字段名</param>
public PrimaryKeyAttribute(string column)
{
this._column = column;
}
/// <summary>
/// 初始化PrimaryKeyAttribute的实例
/// </summary>
/// <param name="dbtype">字段类型</param>
public PrimaryKeyAttribute(SqlDbType dbtype)
{
_hasSetSqlType=true;
_dbtype=dbtype;
}
/// <summary>
/// 初始化PrimaryKeyAttribute的实例
/// </summary>
/// <param name="generator">主键生成器</param>
/// <param name="column">字段名</param>
/// <param name="dbtype">字段类型</param>
public PrimaryKeyAttribute(PrimaryKeyType generator, String column,
SqlDbType dbtype):this(generator,column )
{
_hasSetSqlType=true;
_dbtype=dbtype;
}
}
#endregion
#region 数据库连接
#region 连接参数
/// <summary>
/// 连接设置
/// </summary>
public class WXYLiteORMConnSetting
{
private static string _conn;
/// <summary>
/// 连接字符串
/// </summary>
public static string ConnString
{
get{return _conn;}
set{_conn=value;}
}
private static int _timeout;
/// <summary>
/// 命令超时时间
/// </summary>
public static int CommandTimeOut
{
get{return _timeout;}
set{_timeout =value;}
}
}
#endregion
#region 数据库连接会话周期
/// <summary>
/// 数据连接接口
/// </summary>
public interface ISessionScope:IDisposable
{
/// <summary>
/// 返回一个新的SqlCommand
/// </summary>
/// <returns></returns>
SqlCommand GetSqlCommand();
}
/// <summary>
/// 数据库连接栈
/// </summary>
internal class SessionScopeStack
{
/* 在web情况下,不宜使用static变量,因为会串.所以,改为使用item,
* 保证每个request有一个栈 */
// [ThreadStatic()]
// private static Stack _stack=null;
/******************/
//[ThreadStatic()]
private static object lockobj=new object();
/// <summary>
/// 返回当前连接栈
/// </summary>
/// <returns></returns>
public static Stack Current()
{
lock(lockobj )
{
// cs 的情况,使用threadstatic()的变量,bs需使用item
// if(_stack==null)
// _stack=Stack.Synchronized(new Stack());
Stack _stack=null;
if( !System.Web.HttpContext.Current
.Items.Contains("wxyliteormstack") )
{
_stack=Stack.Synchronized(new Stack());
System.Web.HttpContext.Current
.Items.Add("wxyliteormstack",_stack );
}
else
{
_stack=(Stack)System.Web.HttpContext.Current
.Items["wxyliteormstack"];
}
return _stack;
}
}
}
#region 会话基类
/// <summary>
/// 基础数据库连接会话
/// </summary>
public class SessionScope:ISessionScope
{
protected SqlConnection _conn;
/// <summary>
/// 初始化一个新的连接实例
/// </summary>
public SessionScope()
{
if(WXYLiteORMConnSetting.ConnString==null)
{
throw new System.Exception("连接字符串未初始化");
}
_conn=new SqlConnection(WXYLiteORMConnSetting.ConnString );
//_conn.Open();
SessionScopeStack.Current().Push(this);
}
#region ISessionScope 成员
/// <summary>
/// 返回SqlCommand
/// </summary>
/// <returns></returns>
public virtual SqlCommand GetSqlCommand()
{
if(_conn.State==ConnectionState.Broken )
{
_conn.Close();_conn.Open();
}
else if(_conn.State==ConnectionState.Closed )
{
_conn.Open();
}
SqlCommand cmd=_conn.CreateCommand();
cmd.CommandTimeout=WXYLiteORMConnSetting.CommandTimeOut;
return cmd ;
}
#endregion
#region IDisposable 成员
public virtual void Dispose()
{
// TODO: 添加 SessionScope.Dispose 实现
//_conn.Close();
SessionScopeStack.Current().Pop();
_conn.Dispose();
}
#endregion
}
#endregion
#region 不指定会话时,生成的默认会话
/// <summary>
/// 此会话的特点是会自动
/// 关闭数据库连接。
/// </summary>
public class DefaultSessionScope:SessionScope
{
public override SqlCommand GetSqlCommand()
{
SqlCommand cmd= base.GetSqlCommand ();
cmd.Disposed+=new EventHandler(cmd_Disposed);
return cmd;
}
private void cmd_Disposed(object sender, EventArgs e)
{
((SqlCommand)sender ).Connection.Close();
}
}
#endregion
#region 事务支持的会话
/// <summary>
/// 此会话支持事务
/// </summary>
public class TransactionSessionScope:SessionScope
{
private SqlTransaction tran;
//private bool autocommit;
#region 构造函数
public TransactionSessionScope()
{
base._conn.Open();
tran=base._conn.BeginTransaction();
}
// public TransactionSessionScope(bool isautocommit):this()
// {
// autocommit=isautocommit;
// }
public TransactionSessionScope(IsolationLevel isolation )
{
base._conn.Open();
tran=base._conn.BeginTransaction(isolation);
}
// public TransactionSessionScope(IsolationLevel isolation,bool isautocommit ):this(isolation)
// {
// autocommit=isautocommit;
// }
#endregion
public override SqlCommand GetSqlCommand()
{
SqlCommand cmd= base.GetSqlCommand ();
cmd.Transaction=tran;
return cmd;
}
private bool hascommitorrollback;
public void Commit()
{
tran.Commit();
hascommitorrollback=true;
}
public void Rollback()
{
tran.Rollback();
hascommitorrollback=true;
}
public override void Dispose()
{
tran.Dispose();
base.Dispose ();
}
}
#endregion
#endregion
#endregion
#region Comb操作
/// <summary>
/// 可排序Guid操作类
/// </summary>
public class GuidComb
{
//================================================================
///<summary>
/// 返回 GUID 用于数据库操作,特定的时间代码可以提高检索效率
/// </summary>
/// <returns>COMB (GUID 与时间混合型) 类型 GUID 数据</returns>
public static Guid NewComb()
{
byte[] guidArray = System.Guid.NewGuid().ToByteArray();
DateTime baseDate = new DateTime(1900,1,1);
DateTime now = DateTime.Now;
// Get the days and milliseconds which will be used to build the byte string
TimeSpan days = new TimeSpan(now.Ticks - baseDate.Ticks);
TimeSpan msecs = new TimeSpan(now.Ticks - (new DateTime(now.Year, now.Month, now.Day).Ticks));
// Convert to a byte array
// Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333
byte[] daysArray = BitConverter.GetBytes(days.Days);
byte[] msecsArray = BitConverter.GetBytes((long)(msecs.TotalMilliseconds/3.333333));
// Reverse the bytes to match SQL Servers ordering
Array.Reverse(daysArray);
Array.Reverse(msecsArray);
// Copy the bytes into the guid
Array.Copy(daysArray, daysArray.Length - 2, guidArray, guidArray.Length - 6, 2);
Array.Copy(msecsArray, msecsArray.Length - 4, guidArray, guidArray.Length - 4, 4);
return new System.Guid(guidArray);
}
//================================================================
/// <summary>
/// 从 SQL SERVER 返回的 GUID 中生成时间信息
/// </summary>
/// <param name="guid">包含时间信息的 COMB </param>
/// <returns>时间</returns>
public static DateTime GetDateFromComb(System.Guid guid)
{
DateTime baseDate = new DateTime(1900,1,1);
byte[] daysArray = new byte[4];
byte[] msecsArray = new byte[4];
byte[] guidArray = guid.ToByteArray();
// Copy the date parts of the guid to the respective byte arrays.
Array.Copy(guidArray, guidArray.Length - 6, daysArray, 2, 2);
Array.Copy(guidArray, guidArray.Length - 4, msecsArray, 0, 4);
// Reverse the arrays to put them into the appropriate order
Array.Reverse(daysArray);
Array.Reverse(msecsArray);
// Convert the bytes to ints
int days = BitConverter.ToInt32(daysArray, 0);
int msecs = BitConverter.ToInt32(msecsArray, 0);
DateTime date = baseDate.AddDays(days);
date = date.AddMilliseconds(msecs * 3.333333);
return date;
}
}
#endregion
/// <summary>
/// ORM仲裁者,提供Save,Find,Create,
/// Update,等操作的方法
/// </summary>
public class WXYLiteORMMediater
{
#region 获得SessionScope
private static ISessionScope GetSession()
{
if( SessionScopeStack.Current().Count==0 )
{
new DefaultSessionScope();
//throw new System.Exception("没有数据连接!");
}
return (ISessionScope)SessionScopeStack.Current().Peek();
}
#endregion
#region 查找类方法
/// <summary>
/// 通过主键查找实例.
/// </summary>
/// <param name="id">主键</param>
/// <param name="targetType">目标类型</param>
/// <returns></returns>
public static object Find(object id,Type targetType)
{
TableMapping map= InitTypeMapping(targetType);
string sql=CreateSelectSql(targetType);
sql+="\n WHERE ["+ map.PrimaryKey.Column +"]=@pk";
ISessionScope s=GetSession();
using( SqlCommand cmd= s.GetSqlCommand() )
{
cmd.CommandText=sql;
cmd.Parameters.Add("@pk", map.PrimaryKey.DbType).Value=
ConvertDataToDbType(map.PrimaryKey.DbType, id);
try
{
using( SqlDataReader rd=cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if(!rd.HasRows)
return null;
object result=BindDataReader(targetType,rd)[0];
rd.Close();
return result;
}
}
catch(System.Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 通过Sql语句,生成对象数组。
/// 注意,所有字段必须都包括在内。
/// </summary>
/// <param name="targetType">目标类型</param>
/// <param name="sql">sql语句</param>
/// <returns>目标类型的数组</returns>
public static object[] Query(Type targetType,string sql)
{
ISessionScope s=GetSession();
using (SqlCommand cmd=s.GetSqlCommand())
{
cmd.CommandText=sql;
try
{
using( SqlDataReader rd=cmd.ExecuteReader())
{
object[] result= BindDataReader(targetType ,rd);
rd.Close();
return result;
}
}
catch(System.Exception ex)
{
throw ex;
}
}
}
#endregion
#region 保存实例
/// <summary>
/// 将新实例保存到数据库,
/// 同时设置此实例的主键值.
/// </summary>
/// <param name="daobj">要保存的实例</param>
public static void Create( object daobj)
{
TableMapping map=InitTypeMapping( daobj.GetType() );
//Console.WriteLine(CreateInsertSql( daobj.GetType() ));
ISessionScope s=GetSession();
using(SqlCommand cmd=s.GetSqlCommand())
{
cmd.CommandText=CreateInsertSql( daobj.GetType() );
/*这里判断主键如何赋值*/
if(map.PkCreater ==PrimaryKeyType.Assigned)
{
cmd.Parameters.Add(
"@" +map.PrimaryKey.Column ,map.PrimaryKey.DbType).Value=
ConvertDataToDbType(map.PrimaryKey.DbType,
daobj.GetType().GetProperty(map.PrimaryKey.PropertyName).GetValue(
daobj,null));
}
else if(map.PkCreater ==PrimaryKeyType.Guid)
{
Guid pk=Guid.NewGuid();
cmd.Parameters.Add(
"@" +map.PrimaryKey.Column ,map.PrimaryKey.DbType).Value=
ConvertDataToDbType(map.PrimaryKey.DbType,
pk ) ;
daobj.GetType().GetProperty(map.PrimaryKey.PropertyName ).SetValue(
daobj,
ConvertType(
pk,daobj.GetType().GetProperty(map.PrimaryKey.PropertyName ).PropertyType
)
,null
);
}
else if(map.PkCreater ==PrimaryKeyType.GuidComb )
{
Guid pk=GuidComb.NewComb();
cmd.Parameters.Add(
"@" +map.PrimaryKey.Column ,map.PrimaryKey.DbType).Value=
ConvertDataToDbType(map.PrimaryKey.DbType,
pk ) ;
daobj.GetType().GetProperty(map.PrimaryKey.PropertyName ).SetValue(
daobj,
ConvertType(
pk,daobj.GetType().GetProperty(map.PrimaryKey.PropertyName ).PropertyType
)
,null
);
}
//********开始参数赋值**********
foreach(FieldMapping fm in map.Columns.Values )
{
if(fm.Insert)
{
cmd.Parameters.Add(
"@" +fm.Column ,fm.DbType).Value=
ConvertDataToDbType(fm.DbType,
daobj.GetType().GetProperty(fm.PropertyName).GetValue(
daobj,null));
}
}
try
{
if(map.PkCreater!=PrimaryKeyType.Identity )
{
cmd.ExecuteNonQuery();
}
else
{
long id=Convert.ToInt64( cmd.ExecuteScalar());
/**给主键赋值为刚才生成的Id值**/
daobj.GetType().GetProperty(map.PrimaryKey.PropertyName ).SetValue(
daobj,
ConvertType(
id,daobj.GetType().GetProperty(map.PrimaryKey.PropertyName ).PropertyType
)
,null
);
}
}
catch(System.Exception ex)
{
//Console.WriteLine(ex.ToString());
throw ex;
}
}
}
#endregion
#region 更新实例
/// <summary>
/// 更新数据库中的实例
/// </summary>
/// <param name="daobj">要更新的实例</param>
public static void UpDate(object daobj)
{
TableMapping map=InitTypeMapping(daobj.GetType());
//Console.WriteLine( CreateUpDateSql( daobj.GetType() ) );
string sql=CreateUpDateSql(daobj.GetType());
ISessionScope s=GetSession();
using(SqlCommand cmd=s.GetSqlCommand())
{
cmd.CommandText=sql;
foreach( FieldMapping fm in map.Columns.Values )
{
if(fm.Update)
{
SqlParameter para=new SqlParameter("@"+fm.Column,fm.DbType );
para.Value=
ConvertDataToDbType( fm.DbType ,
daobj.GetType().GetProperty(fm.PropertyName).GetValue
(daobj,null)
);
cmd.Parameters.Add(para);
}
}
/* 给 where 加条件 */
cmd.Parameters.Add("@"+map.PrimaryKey.Column ,map.PrimaryKey.DbType ).Value=
ConvertDataToDbType(
map.PrimaryKey.DbType,
daobj.GetType().GetProperty( map.PrimaryKey.PropertyName).GetValue(
daobj,null
)
)
;
cmd.ExecuteNonQuery();
}
}
#endregion
#region 从数据库中刷新实例
/// <summary>
/// 从数据库中刷新实例
/// </summary>
/// <param name="daobj">要刷新的实例</param>
public static void Refresh( object daobj)
{
TableMapping map=InitTypeMapping(daobj.GetType());
object pk=daobj.GetType().GetProperty( map.PrimaryKey.PropertyName ).GetValue( daobj,null);
object res=Find(pk,daobj.GetType() );
Type optype=daobj.GetType();
/*******将值拷贝给daobj*************/
foreach( FieldMapping fm in map.Columns.Values )
{
optype.GetProperty(fm.PropertyName).SetValue(
daobj,
optype.GetProperty(fm.PropertyName).GetValue(res,null),
null
);
}
}
#endregion
#region 从数据库中删除实例
/// <summary>
/// 从数据库中删除实例
/// </summary>
/// <param name="daobj"></param>
public static void Delete(object daobj)
{
TableMapping map=InitTypeMapping(daobj.GetType());
string sql=CreateDelSql(daobj.GetType());
ISessionScope s= GetSession();
using(SqlCommand cmd=s.GetSqlCommand() )
{
cmd.CommandText=sql;
/* 给 where 加条件 */
cmd.Parameters.Add("@pk" ,map.PrimaryKey.DbType ).Value=
ConvertDataToDbType(
map.PrimaryKey.DbType,
daobj.GetType().GetProperty( map.PrimaryKey.PropertyName).GetValue(
daobj,null
)
)
;
cmd.ExecuteNonQuery();
}
}
#endregion
#region "私有方法"
#region 数据操作
/// <summary>
/// 从SqlDataReader的数据生成目标类型
/// </summary>
/// <param name="targetType"></param>
/// <param name="rd"></param>
/// <returns></returns>
private static object[] BindDataReader(Type targetType,SqlDataReader rd)
{
//object result=CreateObjInstance(targetType );
ArrayList objs=new ArrayList();
TableMapping map= InitTypeMapping(targetType);
while(rd.Read())
{
//targetType.GetProperty("Itest").SetValue( result,Convert.ToInt16( rd["Itest"]) ,null );
object obj=CreateObjInstance(targetType);
//主键赋值
targetType.GetProperty(map.PrimaryKey.PropertyName).SetValue
(
obj,ConvertType( rd[map.PrimaryKey.Column],
targetType.GetProperty(map.PrimaryKey.PropertyName).PropertyType ) ,null );
foreach(FieldMapping fm in map.Columns.Values )
{
targetType.GetProperty( fm.PropertyName ).SetValue
(
obj,ConvertType(rd[fm.Column],targetType.GetProperty(fm.PropertyName).PropertyType),null);
}
objs.Add(obj);
}
return (object[])objs.ToArray(targetType );
}
/// <summary>
/// 建立实体类的一个实例
/// </summary>
/// <param name="targetType">目标类型</param>
/// <returns></returns>
private static object CreateObjInstance(Type targetType)
{
object result;
System.Reflection.ConstructorInfo constructor=targetType.GetConstructor(new Type[0]);
result=constructor.Invoke(new object[0]);
return result;
}
#endregion
#region 建立sql语句
#region 建立select 部分,不包括where
private static string CreateSelectSql(Type targetType)
{
TableMapping map= InitTypeMapping(targetType);
System.Text.StringBuilder sql=new System.Text.StringBuilder();
sql.Append("SELECT \n[" + map.PrimaryKey.Column + "],");
foreach( FieldMapping f in map.Columns.Values )
{
sql.Append("\n[" +f.Column + "],");
}
sql.Remove(sql.Length-1,1);
sql.Append(" \nFROM "+ map.TableName +" " );
return sql.ToString();
}
#endregion
#region 建立insert 语句
private static string CreateInsertSql(Type sourceType)
{
TableMapping map=InitTypeMapping(sourceType );
System.Text.StringBuilder sql=new System.Text.StringBuilder();
sql.Append("INSERT INTO " + map.TableName + "\n");
sql.Append("( ");
/*这里判断主键是否是自动编号还是手工指定的还是由框架提供*/
if(map.PkCreater !=PrimaryKeyType.Identity )
{
sql.Append("\n[" + map.PrimaryKey.Column + "],");
}
//***********
foreach(FieldMapping fm in map.Columns.Values )
{
if(fm.Insert)
{
sql.Append("\n["+ fm.Column +"],");
}
}
sql.Remove(sql.Length-1,1);
sql.Append(") VALUES ( ");
/*这里判断主键是否是自动编号还是手工指定的还是由框架提供*/
if(map.PkCreater !=PrimaryKeyType.Identity )
{
sql.Append("\n@" + map.PrimaryKey.Column + ",");
}
//***********
foreach(FieldMapping fm in map.Columns.Values )
{
if(fm.Insert)
{
sql.Append("\n@"+ fm.Column +",");
}
}
sql.Remove(sql.Length-1,1);
sql.Append(") ");
//如果主键自动编号,则加上查询最新值的代码
if(map.PkCreater ==PrimaryKeyType.Identity )
{
sql.Append("\n SELECT SCOPE_IDENTITY() ");
}
return sql.ToString();
}
#endregion
#region 建立update语句
private static string CreateUpDateSql(Type sourceType)
{
TableMapping map=InitTypeMapping(sourceType );
System.Text.StringBuilder sql=new System.Text.StringBuilder();
sql.Append("UPDATE "+ map.TableName +"\n");
sql.Append("SET ");
foreach( FieldMapping fm in map.Columns.Values )
{
if(fm.Update )
{
sql.AppendFormat("\n[{0}]=@{0},", fm.Column );
}
}
sql.Remove(sql.Length-1,1);
sql.AppendFormat("\n WHERE [{0}]=@{0}",map.PrimaryKey.Column);
return sql.ToString();
}
#endregion
#region 建立delete语句
private static string CreateDelSql(Type sourceType)
{
TableMapping map=InitTypeMapping(sourceType );
string sql=string.Format("DELETE FROM {0} \n WHERE [{1}]=@pk" ,map.TableName ,map.PrimaryKey.Column );
return sql;
}
#region
#endregion
#region "获得对象关系映射表"
private static Hashtable cachemapping=new Hashtable();
/// <summary>
/// 获得对象关系映射表
/// </summary>
/// <param name="st"></param>
/// <returns></returns>
private static TableMapping InitTypeMapping(Type st)
{
if(! cachemapping.Contains(st.FullName ))
{
TableMapping map=new TableMapping();
#region "查找表名"
// 1,查找表名
DataBaseTableAttribute dbt=(DataBaseTableAttribute)
Attribute.GetCustomAttribute(st,typeof(DataBaseTableAttribute));
if(dbt==null)
{
throw new Exception.NoTableException(st.FullName );
}
else if(dbt.Table==null || dbt.Table=="")
{
map.TableName= "["+ st.Name+"]";
}
else
{
map.TableName="["+dbt.Table+"]";
}
if(dbt.Owner!="" || dbt.DataBase !="")
{
string db= dbt.DataBase !=""? "["+ dbt.DataBase +"].":"";
string ow="";
if(db !="")
{
ow=dbt.Owner !=""? "[" + dbt.Owner + "].":".";
}
else
{
ow=dbt.Owner !=""? "[" + dbt.Owner + "].":"";
}
map.TableName=db+ow+map.TableName;
}
#endregion
#region 查找主键
System.Reflection.PropertyInfo[] props=
st.GetProperties();
foreach( System.Reflection.PropertyInfo p in props )
{
PrimaryKeyAttribute pk=(PrimaryKeyAttribute)
Attribute.GetCustomAttribute(p,typeof(PrimaryKeyAttribute));
if(pk !=null)
{
if(map.PrimaryKey.Column !=null && map.PrimaryKey.Column.Trim() !="" )
{
throw new Exception.ToManyPkException(st.FullName );
}
else
{
map.PkCreater=pk.Generator ;
map.PrimaryKey.PropertyName=p.Name ;
if(pk.HasSetSqlType )
{
map.PrimaryKey.DbType=pk.DbType;
}
else
{
map.PrimaryKey.DbType=ToSqlDbType(p.PropertyType ) ;
}
if(pk.Column==null || pk.Column.Trim()=="")
{
map.PrimaryKey.Column=p.Name;
}
else
{
map.PrimaryKey.Column=pk.Column;
}
}
}
}
if(map.PrimaryKey.Column ==null || map.PrimaryKey.Column.Trim() =="" )
{
throw new Exception.NoPkException(st.FullName );
}
#endregion
#region 查找字段
foreach( System.Reflection.PropertyInfo p in props )
{
TableFieldAttribute f=(TableFieldAttribute)
Attribute.GetCustomAttribute(p,typeof(TableFieldAttribute));
if(f!=null)
{
FieldMapping fd=new FieldMapping();
if(! f.HasSetSqlType)
{
fd.DbType=ToSqlDbType(p.PropertyType);
}
else
{
fd.DbType=f.DbType;
}
if(f.Column ==null || f.Column.Trim()=="" )
{
fd.Column=p.Name ;
}
else
{
fd.Column=f.Column;
}
fd.Update=f.UpDate ;
fd.Insert=f.Insert ;
fd.PropertyName=p.Name;
map.Columns.Add( p.Name ,fd );
}
}
#endregion
cachemapping.Add( st.FullName ,map );
return map;
}
else
{
return (TableMapping)cachemapping[st.FullName ];
}
}
#endregion
#region 转换类型
/// <summary>
/// 将类型转换为DbType
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
private static SqlDbType ToSqlDbType(Type s)
{
if(s.Equals(typeof(String))){return SqlDbType.NVarChar; }
if(s.Equals(typeof(Int32))){return SqlDbType.Int;}
if(s.Equals(typeof(Int16))){return SqlDbType.SmallInt;}
if(s.Equals(typeof(Int64))){return SqlDbType.BigInt;}
if(s.Equals(typeof(Decimal))){return SqlDbType.Decimal;}
if(s.Equals(typeof(Single))){return SqlDbType.Real;}
if(s.Equals(typeof(Double))){return SqlDbType.Float;}
if(s.Equals(typeof(Byte))){return SqlDbType.TinyInt;}
if(s.Equals(typeof(SByte))){return SqlDbType.TinyInt ;}
if(s.Equals(typeof(UInt16))){return SqlDbType.Int;}
if(s.Equals(typeof(UInt32))){return SqlDbType.Int;}
if(s.Equals(typeof(UInt64))){return SqlDbType.BigInt;}
if(s.Equals(typeof(Char))){return SqlDbType.NChar;}
if(s.Equals(typeof(Boolean))){return SqlDbType.Bit;}
if(s.Equals(typeof(DateTime))){return SqlDbType.DateTime;}
if(s.Equals(typeof(Guid))){return SqlDbType.UniqueIdentifier;}
if(s.Equals(typeof(Byte[]))){return SqlDbType.VarBinary;}
if(s.Equals(typeof(Object))){return SqlDbType.Variant;}
throw new Exception.CanNotMappingSqlTypeException(s.FullName );
}
/// <summary>
/// 将数据库中取得的类型转型成的属性的类型
/// </summary>
/// <param name="val"></param>
/// <param name="totype"></param>
/// <returns></returns>
private static object ConvertType(object val,Type totype )
{
//Console.WriteLine (val.GetType().Name );
if (totype.Equals(typeof(Guid)) && !( val is Guid ) )
{
if( val is DBNull )
return null;
else
return new Guid(val.ToString());
}
if(val is DBNull )
{
return null;
}
else
{
if( val is Guid && totype.Equals(typeof(string)) )
{
return val.ToString();
}
else
{
return Convert.ChangeType(val,totype);
}
}
}
/// <summary>
/// 将值转换为数据库兼容的类型
/// 特别处理Guid的转换
/// 日期类型的转换
/// 日期不能小于sql的最小范围。否则视为Null处理
/// </summary>
/// <param name="dbtype"></param>
/// <param name="v"></param>
/// <returns></returns>
private static object ConvertDataToDbType(SqlDbType dbtype,object v)
{
if (v==null)
{
return DBNull.Value;
}
if( v is DateTime )
{
if( DateTime.Compare( (DateTime)v,System.Data.SqlTypes.SqlDateTime.MinValue.Value )<0 )
{
return DBNull.Value ;
}
}
if(dbtype==SqlDbType.UniqueIdentifier && !( v is Guid ) )
{
return new Guid(v.ToString());
}
if(dbtype !=SqlDbType.UniqueIdentifier && (v is Guid ))
{
return v.ToString();
}
return v;
}
#endregion
#endregion
#endregion
#endregion
#region 表映射关系内部类
class TableMapping
{
public string TableName;
public FieldMapping PrimaryKey;
public PrimaryKeyType PkCreater;
private Hashtable _columns;
public Hashtable Columns
{
get{return _columns;}
}
public TableMapping()
{
PrimaryKey=new FieldMapping();
_columns=new Hashtable();
}
}
class FieldMapping
{
public string PropertyName;
public string Column;
public SqlDbType DbType;
public bool Update;
public bool Insert;
}
#endregion
}
/// <summary>
/// 提供Insert,Update,Delete
/// 数据库操作的ORM基类
/// </summary>
[Serializable()]
public abstract class WXYLiteORMBase
{
/// <summary>
/// 将新实例保存到数据库
/// </summary>
public void Create()
{
WXYLiteORMMediater.Create(this );
}
/// <summary>
/// 更新实例
/// </summary>
public void UpDate()
{
WXYLiteORMMediater.UpDate( this);
}
/// <summary>
/// 刷新实例
/// </summary>
public void Refresh()
{
WXYLiteORMMediater.Refresh( this);
}
/// <summary>
/// 删除实例
/// </summary>
public void Delete()
{
WXYLiteORMMediater.Delete(this);
}
}
}
namespace WXYLiteORM.Exception
{
/// <summary>
/// 没有指定表名
/// </summary>
public class NoTableException:System.Exception
{
private string _source;
/// <summary>
/// 此实例没有标记为要使用ORM
/// </summary>
/// <param name="source"></param>
public NoTableException(string source)
{
_source=source;
}
public override string Message
{
get
{
return _source+"没有指定对应表" ;
}
}
}
/// <summary>
/// 指定了太多的主键
/// </summary>
public class ToManyPkException:System.Exception
{
private string _source;
/// <summary>
/// 指定了太多的主键
/// </summary>
/// <param name="source"></param>
public ToManyPkException(string source)
{
_source=source;
}
public override string Message
{
get
{
return _source+"指定了多个主键。" ;
}
}
}
/// <summary>
/// 没有指定主键
/// </summary>
public class NoPkException:System.Exception
{
private string _source;
/// <summary>
/// 没有指定主键
/// </summary>
/// <param name="source"></param>
public NoPkException(string source)
{
_source=source;
}
public override string Message
{
get
{
return _source+"没有指定主键。" ;
}
}
}
/// <summary>
/// 不能映射的类型
/// </summary>
public class CanNotMappingSqlTypeException:System.Exception
{
private string _source;
/// <summary>
/// 不能将属性映射成数据库字段
/// </summary>
/// <param name="source"></param>
public CanNotMappingSqlTypeException(string source)
{
_source=source;
}
public override string Message
{
get
{
return _source+"无法转换为Sql的类型。" ;
}
}
}
/// <summary>
/// 没有初始化连接字符串
/// </summary>
public class NoInitConnException:System.Exception
{
public override string Message
{
get
{
return "没有初始化连接字符串";
}
}
}
}
以上就是全部代码,直接编译就可用 .
功能非常简单,只支持SqlServer, 和单张表操作,其他一律不考虑,复合主键也不考虑(反正这个项目里也用不着其他东西) 主键提供了4种生成机制,guid,comb,自增,和指定。 其实基本的思路非常简单,就是定义了三个 attribute, 一个指示是一个表,一个指示是主键,一个指示字段,顺便指定字段的类型。然后搞了个基类,凡是继承此基类的自动就可以save,update,delete. 顺带把事务支持也加进去了,这样基本上算是可以用了,运行至现在情况良好。反正这个不算什么机密,于是厚着脸皮就发上来了。
当然光有这还不够,实体类的生成也很麻烦,但是我这有位大哥管建立数据库,而且他还会给字段加注释,那最好,于是我就写了个存储过程自动生成实体类
Code
set nocount on
set statistics time off
declare @tablename nvarchar(100)
declare @field nvarchar(100)
declare @type nvarchar(100)
declare @summary nvarchar(100)
declare @ttype nvarchar(100)
declare @null bit
declare @def nvarchar(100)
set @tablename='tblgiftsendbackgoodsdetailbase'
declare @dh nvarchar(100)
declare cur cursor for
SELECT
表名=case when a.colorder=1 then d.name else '' end,
字段名=a.name,
类型=b.name, 允许空=case when a.isnullable=1 then 1 else 0 end,
默认值=isnull(e.text,''),
字段说明=isnull(convert(nvarchar(1000),g.[value]),'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
--left join sys.extended_properties g on a.colid=minor_id and g.class=1 and g.major_id=a.id
where d.name=@tablename
order by a.id,a.colorder
open cur
print '[DataBaseTable("'+@tablename+'")]'
print 'public class '+@tablename
print '{'
fetch next from cur into @tablename,@field,@type,@null,@def,@summary
while @@fetch_status=0
begin
set @ttype=case @type when 'nvarchar ' then 'string'
when 'money ' then 'decimal '
when 'uniqueidentifier ' then 'Guid'
when 'datetime ' then 'DateTime '
when 'decimal ' then 'decimal '
when 'numeric ' then 'decimal '
when 'tinyint ' then 'Int16'
when 'smallint ' then 'Int16 '
when 'int ' then 'int '
when 'smalldatetime ' then 'DateTime '
when 'real ' then 'decimal '
when 'float ' then 'float '
when 'ntext ' then 'string'
when 'bit ' then 'bool'
when 'smallmoney ' then 'decimal '
when 'bigint ' then 'long'
when 'varbinary ' then 'Byte[]'
when 'varchar' then 'string'
when 'char ' then 'string'
when 'nchar ' then 'string'
when 'image ' then 'Byte[]'
end
set @dh=''
if @def !=''
begin
set @def=substring(@def,2,len(@def)-2)
set @def=replace(@def,'''','"')
end
if @ttype='DateTime ' and @def !='getdate()'
set @def='"'+replace(@def,'"','')+'"'
if @def='getdate()'
set @def='DateTime.Now'
if @null=0
begin
if @def !=''
begin
set @dh=case @ttype when 'string' then '='+@def
when 'DateTime ' then '=Convert.ToDateTime('+@def+')'
else ''
end
end
end
if @ttype='Guid' and @null=1
set @ttype='string'
print 'private '+ @ttype + ' _'+@field+@dh+';'
print '///<summary>'
print '///'+replace(replace(@summary,'<',' '),'>','')
print '///</summary>'
print '[TableField("'+@field+'")]'
print 'public '+@ttype+' '+@field
print '{'
print ' get{ return '+ ' _'+@field+';}'
print ' set{'+ ' _'+@field +'=value ;}'
print '}'
fetch next from cur into @tablename,@field,@type,@null,@def,@summary
end
close cur
deallocate cur
print '}'
最后当然是使用拉,使用非常简单,先声明一个事务空间,然后实例化一个实体类,然后调用Save,就一切OK了
using (TransactionSessionScope t=new TransactionSessionScope())
{
//****建立主表
TreasureGiftOrderBase o=new TreasureGiftOrderBase();
o.ordersource="2";
o.billNumber=createbillno();
o.successOrderid=ViewState["recordid"].ToString();
o.Create();
t.Commit();
}
由于是.net1.1,没有泛型,所以在查询时取得实例遇到了不少麻烦,我最后的办法是在查询的时候,传个类型参数进去,然后在ArrayList.ToArray()的时候强制转型,也算是可以凑合。
CustomerBase customer= (CustomerBase)WXYLiteORMMediater.Find(
ViewState["CustomerId"].ToString(), typeof(CustomerBase) );
实现了这个东西,自我感觉还不错,也算是自己一个小提高啊,呵呵
在实现这个的时候,主要遇到了这么几个问题:Castle ActiveRecord 的 SessionScope 只要声明了一个,以后的数据连接就自动都会使用它,我一直没搞明白他是怎么做到的,这里我也想实现同样的功能,于是看了他的代码,(没看懂-_-)但是大致上知道了他是在System.Web.HttpContext.Current 里放了一个栈,每次要取的时候就去读栈里顶上的那个SessionScope.于是我也依葫芦画瓢弄了个,还成,挺象的.
不用拼Insert和Update的感觉真是不错,效率可是高多了。