/// <summary> /// 删除若干条对象的数据 /// </summary> /// <param name="Objects">对象列表</param> /// <returns>删除的记录个数</returns> public int DeleteObjects(System.Collections.IEnumerable Objects) { if (Objects == null) { throw new ArgumentNullException("Objects"); } this.CheckBindInfo(Objects, true); this.CheckConnetion(); int RecordCount = 0; using (System.Data.IDbCommand cmd = myConnection.CreateCommand()) { foreach (object obj in Objects) { TableBindInfo table = this.GetBindInfo(obj.GetType()); // 拼凑SQL语句 System.Collections.ArrayList values = new System.Collections.ArrayList(); string strSQL = BuildCondition(obj, values); strSQL = "Delete From " + FixTableName(table.TableName) + " Where " + strSQL; // 设置SQL命令对象 cmd.Parameters.Clear(); cmd.CommandText = strSQL; foreach (object v in values) { System.Data.IDbDataParameter p = cmd.CreateParameter(); p.Value = v; cmd.Parameters.Add(p); } // 执行SQL,删除记录 RecordCount += cmd.ExecuteNonQuery(); } } return(RecordCount); }
/// <summary> /// 根据对象数值创建查询条件子SQL语句 /// </summary> /// <param name="obj">对象</param> /// <param name="values">SQL参数值列表</param> /// <returns>创建的SQL语句字符串</returns> private string BuildCondition(object obj, System.Collections.ArrayList values) { TableBindInfo table = this.GetBindInfo(obj.GetType()); // 拼凑查询条件SQL语句 System.Text.StringBuilder mySQL = new System.Text.StringBuilder(); foreach (FieldBindInfo field in table.Fields) { if (field.Attribute.Key) { object v = field.Property.GetValue(obj, null); if (v == null || DBNull.Value.Equals(v)) { throw new Exception("关键字段属性 " + field.Property.Name + " 未指定值"); } if (mySQL.Length > 0) { mySQL.Append(" And "); } mySQL.Append(FixFieldName(field.FieldName)); mySQL.Append(" = ? "); values.Add(field.ToDataBase(v)); } } //foreach if (mySQL.Length == 0) { throw new Exception("类型 " + obj.GetType().FullName + " 未能生成查询条件"); } return(mySQL.ToString()); }
/// <summary> /// 判断数据库中是否存在指定的对象 /// </summary> /// <param name="obj">对象</param> /// <returns>true:数据库中存在指定关键字的记录 false:数据库中不存在指定关键字的记录</returns> public bool Contains(object obj) { if (obj == null) { throw new ArgumentNullException("obj"); } this.CheckBindInfo(obj.GetType(), true); this.CheckConnetion(); TableBindInfo table = this.GetBindInfo(obj.GetType()); System.Collections.ArrayList values = new System.Collections.ArrayList(); string strSQL = this.BuildCondition(obj, values); bool result = false; if (strSQL != null) { using (System.Data.IDbCommand cmd = myConnection.CreateCommand()) { strSQL = "Select 1 from " + FixTableName(table.TableName) + " Where " + strSQL; cmd.CommandText = strSQL; foreach (object v in values) { System.Data.IDbDataParameter p = cmd.CreateParameter(); p.Value = v; cmd.Parameters.Add(p); } object v2 = cmd.ExecuteScalar(); if (v2 != null && DBNull.Value.Equals(v2) == false) { result = (Convert.ToInt32(v2) == 1); } } } return(false); }
/// <summary> /// 读取指定类型的所有的对象 /// </summary> /// <param name="ObjectType">记录对象类型</param> /// <returns>读取的记录对象数组</returns> public object[] ReadAllObjects(Type ObjectType) { if (ObjectType == null) { throw new ArgumentNullException("ObjectType"); } this.CheckBindInfo(ObjectType, false); TableBindInfo table = this.GetBindInfo(ObjectType); string strSQL = "Select " + table.FieldNameList + " From " + FixTableName(table.TableName); return(ReadObjects(strSQL, ObjectType, 0)); }
/// <summary> /// 更新多个对象 /// </summary> /// <param name="Objects">对象列表</param> /// <returns>更新修改的数据库记录个数</returns> public int UpdateObjects(System.Collections.IEnumerable Objects) { if (Objects == null) { throw new ArgumentNullException("Objects"); } this.CheckBindInfo(Objects, true); this.CheckConnetion(); int RecordCount = 0; using (System.Data.IDbCommand cmd = myConnection.CreateCommand()) { foreach (object obj in Objects) { TableBindInfo table = this.GetBindInfo(obj.GetType()); // 拼凑生成SQL更新语句 System.Collections.ArrayList values = new System.Collections.ArrayList(); System.Text.StringBuilder myStr = new System.Text.StringBuilder(); foreach (FieldBindInfo field in table.Fields) { object v = field.Property.GetValue(obj, null); if (myStr.Length > 0) { myStr.Append(" , " + System.Environment.NewLine); } myStr.Append(FixFieldName(field.FieldName) + " = ? "); values.Add(field.ToDataBase(v)); } myStr.Insert(0, "Update " + FixTableName(table.TableName) + " Set "); string strSQL = BuildCondition(obj, values); myStr.Append(" Where " + strSQL); strSQL = myStr.ToString(); // 设置SQL命令对象,填充参数 cmd.Parameters.Clear(); cmd.CommandText = strSQL; foreach (object v in values) { System.Data.IDbDataParameter p = cmd.CreateParameter(); cmd.Parameters.Add(p); p.Value = v; } RecordCount += cmd.ExecuteNonQuery(); } //foreach } //using return(RecordCount); }
/// <summary> /// 检查一个对象类型的数据库绑定状态,若检查不通过则抛出异常。 /// </summary> /// <param name="t">对象类型</param> /// <param name="CheckKey">是否检查定义了关键字段属性</param> private void CheckBindInfo(Type t, bool CheckKey) { TableBindInfo table = this.GetBindInfo(t); if (table == null) { throw new Exception("类型 " + t.FullName + " 未映射到数据库"); } if (CheckKey) { foreach (FieldBindInfo field in table.Fields) { if (field.Attribute.Key) { return; } } throw new Exception("类型 " + t.FullName + " 未定义关键字段"); } }
/// <summary> /// 从数据读取器中读取数据并填充到一个对象中 /// </summary> /// <param name="ObjInstance">对象实例</param> /// <param name="info">数据库绑定信息对象</param> /// <param name="reader">数据读取器</param> private int InnerReadValues(object ObjInstance, TableBindInfo info, System.Data.IDataReader reader) { // 检查参数 if (ObjInstance == null) { throw new ArgumentNullException("ObjectInstance"); } if (info == null) { throw new ArgumentNullException("info"); } if (reader == null) { throw new ArgumentNullException("reader"); } int FieldCount = 0; // 依次读取各个属性值 foreach (FieldBindInfo field in info.Fields) { // 绑定的属性是只读的 if (field.Property.CanWrite == false) { continue; } // 没有找到绑定的字段 if (field.FieldIndex < 0) { continue; } // 读取数据字段值 object v = reader.GetValue(field.FieldIndex); v = field.FromDataBase(v); // 设置对象属性值 field.Property.SetValue(ObjInstance, v, null); FieldCount++; } //foreach return(FieldCount); }
/// <summary> /// 获得指定类型的数据表映射信息对象 /// </summary> /// <param name="ObjectType">对象类型</param> /// <returns>获得的映射信息对象</returns> /// <remarks> /// 本函数内部使用了 myBufferedInfos 来缓存信息,提高性能。 /// </remarks> private TableBindInfo GetBindInfo(Type ObjectType) { if (ObjectType == null) { throw new ArgumentNullException("OjbectType"); } // 查找已缓存的映射信息对象 TableBindInfo info = ( TableBindInfo )myBufferedInfos[ObjectType]; if (info != null) { return(info); } // 若未找到则创建新的映射信息对象 BindTableAttribute ta = ( BindTableAttribute )System.Attribute.GetCustomAttribute( ObjectType, typeof(BindTableAttribute)); if (ta == null) { return(null); } TableBindInfo NewInfo = new TableBindInfo(); NewInfo.ObjectType = ObjectType; NewInfo.Attribute = ta; NewInfo.TableName = ta.Name; if (NewInfo.TableName == null || NewInfo.TableName.Trim().Length == 0) { // 若在特性中没有指明绑定的表名则使用默认的对象类型名称 NewInfo.TableName = ObjectType.Name; } System.Text.StringBuilder myFieldList = new System.Text.StringBuilder(); System.Collections.ArrayList fields = new System.Collections.ArrayList(); // 遍历所有的公开的实例属性来获得字段绑定信息 foreach (System.Reflection.PropertyInfo p in ObjectType.GetProperties( System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public )) { BindFieldAttribute fa = ( BindFieldAttribute )Attribute.GetCustomAttribute( p, typeof(BindFieldAttribute)); if (fa != null) { FieldBindInfo NewFieldInfo = new FieldBindInfo(); NewFieldInfo.Attribute = fa; NewFieldInfo.FieldName = fa.Name; if (NewFieldInfo.FieldName == null || NewFieldInfo.FieldName.Trim().Length == 0) { // 若在特性中没有指明绑定的字段名则使用默认的属性名称 NewFieldInfo.FieldName = p.Name; } if (myFieldList.Length > 0) { myFieldList.Append(","); } myFieldList.Append(FixFieldName(NewFieldInfo.FieldName)); NewFieldInfo.Property = p; NewFieldInfo.ValueType = p.PropertyType; NewFieldInfo.DefaultValue = GetDefaultValue(p); fields.Add(NewFieldInfo); } } NewInfo.Fields = ( FieldBindInfo[])fields.ToArray(typeof(FieldBindInfo)); NewInfo.FieldNameList = myFieldList.ToString(); // 缓存绑定信息对象 myBufferedInfos[ObjectType] = NewInfo; return(NewInfo); }
/// <summary> /// 将若干个对象插入到数据库中 /// </summary> /// <param name="Objects">对象列表</param> /// <param name="TableName">制定的数据表,若未指定则使用默认的数据表名</param> /// <returns>插入的数据库记录的个数</returns> public int InsertObjects(System.Collections.IEnumerable Objects, string TableName) { if (Objects == null) { throw new ArgumentNullException("Objects"); } this.CheckBindInfo(Objects, false); System.Collections.ArrayList list = new System.Collections.ArrayList(); foreach (object obj in Objects) { list.Add(obj); } if (list.Count == 0) { return(0); } this.CheckConnetion(); // 上一次执行的SQL语句 string strLastSQL = null; int InsertCount = 0; using (System.Data.IDbCommand cmd = myConnection.CreateCommand()) { foreach (object obj in list) { TableBindInfo table = this.GetBindInfo(obj.GetType()); string TableName2 = TableName; if (TableName2 == null || TableName.Trim().Length == 0) { TableName2 = table.TableName; } System.Collections.ArrayList values = new System.Collections.ArrayList(); // 拼凑SQL语句 System.Text.StringBuilder myStr = new System.Text.StringBuilder(); System.Text.StringBuilder myFields = new System.Text.StringBuilder(); foreach (FieldBindInfo field in table.Fields) { if (field.Property.CanRead == false) { throw new Exception("属性 " + field.Property.Name + " 是不可写的"); } object v = field.Property.GetValue(obj, null); if (v == null || DBNull.Value.Equals(v)) { continue; } values.Add(field.ToDataBase(v)); if (myStr.Length > 0) { myStr.Append(" , "); myFields.Append(" , "); } myStr.Append(" ? "); myFields.Append(FixFieldName(field.FieldName)); } //foreach myStr.Insert(0, "Insert Into " + FixTableName(TableName2) + " ( " + myFields.ToString() + " ) Values ( "); myStr.Append(" ) "); string strSQL = myStr.ToString(); if (strSQL != strLastSQL) { // 重新设置SQL命令对象 strLastSQL = strSQL; cmd.Parameters.Clear(); cmd.CommandText = strSQL; for (int iCount = 0; iCount < values.Count; iCount++) { cmd.Parameters.Add(cmd.CreateParameter()); } } // 填充SQL命令参数值 for (int iCount = 0; iCount < values.Count; iCount++) { ((System.Data.IDbDataParameter)cmd.Parameters[iCount]).Value = values[iCount]; } // 执行SQL命令向数据表新增记录 InsertCount += cmd.ExecuteNonQuery(); } //foreach } //using return(InsertCount); }
/// <summary> /// 使用指定的SQL查询语句查询数据库并读取多条数据库记录对象 /// </summary> /// <param name="strSQL">SQL查询语句</param> /// <param name="ObjectType">要读取的对象类型</param> /// <param name="MaxObjectCount">最多读取的对象个数</param> /// <returns>读取的对象组成的数组</returns> public object[] ReadObjects(string strSQL, Type ObjectType, int MaxObjectCount) { // 检查参数 if (strSQL == null) { throw new ArgumentNullException("strSQL"); } if (ObjectType == null) { throw new ArgumentNullException("ObjectType"); } // 检查数据库映射信息 this.CheckBindInfo(ObjectType, false); // 检查数据库连接 this.CheckConnetion(); // 创建SQL命令对象 using (System.Data.IDbCommand cmd = myConnection.CreateCommand()) { // 执行SQL查询,获得一个数据读取器 cmd.CommandText = strSQL; System.Data.IDataReader reader = cmd.ExecuteReader( MaxObjectCount == 1 ? System.Data.CommandBehavior.SingleRow : System.Data.CommandBehavior.SingleResult); System.Collections.ArrayList list = new System.Collections.ArrayList(); TableBindInfo table = this.GetBindInfo(ObjectType); lock ( table ) { // 设置字段序号,提高性能 foreach (FieldBindInfo field in table.Fields) { field.FieldIndex = -1; } for (int iCount = 0; iCount < reader.FieldCount; iCount++) { string name = reader.GetName(iCount); foreach (FieldBindInfo field in table.Fields) { if (EqualsFieldName(name, field.FieldName)) { field.FieldIndex = iCount; } } } while (reader.Read()) { // 根据对象类型创建对象实例 object obj = System.Activator.CreateInstance(ObjectType); // 读取对象属性值 if (InnerReadValues(obj, table, reader) > 0) { list.Add(obj); } if (MaxObjectCount > 0 || list.Count == MaxObjectCount) { break; } } //while } //lock reader.Close(); // 返回读取的对象数组 return(list.ToArray()); } //using }
/// <summary> /// �����ݶ�ȡ���ж�ȡ���ݲ���䵽һ�������� /// </summary> /// <param name="ObjInstance">����ʵ��</param> /// <param name="info">���ݿ����Ϣ����</param> /// <param name="reader">���ݶ�ȡ��</param> private int InnerReadValues( object ObjInstance , TableBindInfo info , System.Data.IDataReader reader ) { // ������ if( ObjInstance == null ) { throw new ArgumentNullException("ObjectInstance"); } if( info == null ) { throw new ArgumentNullException("info"); } if( reader == null ) { throw new ArgumentNullException("reader"); } int FieldCount = 0 ; // ���ζ�ȡ��������ֵ foreach( FieldBindInfo field in info.Fields ) { // ��������ֻ���� if( field.Property.CanWrite == false ) { continue ; } // û���ҵ����ֶ� if( field.FieldIndex < 0 ) { continue ; } // ��ȡ�����ֶ�ֵ object v = reader.GetValue( field.FieldIndex ); v = field.FromDataBase( v ); // ���ö�������ֵ field.Property.SetValue( ObjInstance , v , null ); FieldCount ++ ; }//foreach return FieldCount ; }
/// <summary> /// ���ָ�����͵����ݱ�ӳ����Ϣ���� /// </summary> /// <param name="ObjectType">��������</param> /// <returns>��õ�ӳ����Ϣ����</returns> /// <remarks> /// �������ڲ�ʹ���� myBufferedInfos ��������Ϣ��������ܡ� /// </remarks> private TableBindInfo GetBindInfo( Type ObjectType ) { if( ObjectType == null ) { throw new ArgumentNullException("OjbectType"); } // �����ѻ����ӳ����Ϣ���� TableBindInfo info = ( TableBindInfo ) myBufferedInfos[ ObjectType ] ; if( info != null ) { return info ; } // ��δ�ҵ����µ�ӳ����Ϣ���� BindTableAttribute ta = ( BindTableAttribute ) System.Attribute.GetCustomAttribute( ObjectType , typeof( BindTableAttribute )); if( ta == null ) { return null; } TableBindInfo NewInfo = new TableBindInfo(); NewInfo.ObjectType = ObjectType ; NewInfo.Attribute = ta ; NewInfo.TableName = ta.Name ; if( NewInfo.TableName == null || NewInfo.TableName.Trim().Length == 0 ) { // ����������û��ָ���ı�����ʹ��Ĭ�ϵĶ����������� NewInfo.TableName = ObjectType.Name ; } System.Text.StringBuilder myFieldList = new System.Text.StringBuilder(); System.Collections.ArrayList fields = new System.Collections.ArrayList(); // �������еĹ�����ʵ������������ֶΰ���Ϣ foreach( System.Reflection.PropertyInfo p in ObjectType.GetProperties( System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public )) { BindFieldAttribute fa = ( BindFieldAttribute ) Attribute.GetCustomAttribute( p , typeof( BindFieldAttribute )); if( fa != null ) { FieldBindInfo NewFieldInfo = new FieldBindInfo(); NewFieldInfo.Attribute = fa ; NewFieldInfo.FieldName = fa.Name ; if( NewFieldInfo.FieldName == null || NewFieldInfo.FieldName.Trim().Length == 0 ) { // ����������û��ָ�����ֶ�����ʹ��Ĭ�ϵ��������� NewFieldInfo.FieldName = p.Name ; } if( myFieldList.Length > 0 ) { myFieldList.Append(","); } myFieldList.Append( FixFieldName( NewFieldInfo.FieldName ) ) ; NewFieldInfo.Property = p ; NewFieldInfo.ValueType = p.PropertyType ; NewFieldInfo.DefaultValue = GetDefaultValue( p ); fields.Add( NewFieldInfo ); } } NewInfo.Fields = ( FieldBindInfo[]) fields.ToArray( typeof( FieldBindInfo )); NewInfo.FieldNameList = myFieldList.ToString(); // �������Ϣ���� myBufferedInfos[ ObjectType ] = NewInfo ; return NewInfo ; }