本文利用C#反射和特性定义功能,实现了从数据存储到存储对象的DO映射,在存储对象级别通过自定义特性定义(存储对象的哪些属性需要显示在界面上以及该属性的显示标题和显示索引定义)实现存储对象以数据列表形式在System.Windows.FormsDataGrid中进行显示的功能,具有一定的借鉴意义.
第一部分:自定义特性定义
/// <summary>
/// 呈现特性定义类
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
public class PresentAttribute:System.Attribute
{
protected bool _present;
protected int _index;
protected string _caption;
/// <summary>
///
/// </summary>
/// <param name="present">是否显示</param>
/// <param name="caption">显示标题</param>
/// <param name="index">显示索引(从0开始)</param>
public PresentAttribute(bool present,string caption,int index)
{
_present = present;
_index = index;
_caption = caption;
}
public PresentAttribute(bool present,string caption):this(present,caption,0)
{
}
public PresentAttribute():this(false,null,0)
{
}
/// <summary>
/// 是否显示
/// </summary>
public bool Present{get{return _present;}}
/// <summary>
/// 显示索引(从0开始)
/// </summary>
public int Index{get{return _index;}}
/// <summary>
/// 显示标题
/// </summary>
public string Caption{get{return _caption;}}
}
第二部分:存储对象映射
/// <summary>
/// 方案信息存储对象
/// </summary>
public class INS_DADP_SCHEME
{
protected int _id;
protected string _name;
protected string _summary;
protected int _datasource_type;
protected int _datasource_id;
protected int _dataobjective_id;
protected int _strategy_type;
protected string _fire_dates;
protected DateTime _fire_time;
protected DateTime _create_time;
protected int _create_user_id;
protected DateTime _modi_time;
protected int _modi_user_id;
protected int _state;
[PresentAttribute(true,"方案ID")]
public int Id{get{return this._id;}set{this._id = value;}}
[PresentAttribute(true,"方案名称")]
public string Name{get{return this._name;}set{this._name = value;}}
[PresentAttribute(true,"方案描述")]
public string Summary{get{return this._summary;}set{this._summary = value;}}
[PresentAttribute()]
public int DataSource_Type{get{return this._datasource_type;}set{this._datasource_type = value;}}
[PresentAttribute()]
public int DataSource_Id{get{return this._datasource_id;}set{this._datasource_id = value;}}
[PresentAttribute()]
public int DataObjective_Id{get{return this._dataobjective_id;}set{this._dataobjective_id = value;}}
[PresentAttribute()]
public int Strategy_Type{get{return this._strategy_type;}set{this._strategy_type = value;}}
[PresentAttribute()]
public string Fire_Dates{get{return this._fire_dates;}set{this._fire_dates = value;}}
[PresentAttribute()]
public DateTime Fire_Time{get{return this._fire_time;}set{this._fire_time = value;}}
[PresentAttribute(true,"创建时间")]
public DateTime Create_Time{get{return this._create_time;}set{this._create_time = value;}}
[PresentAttribute(true,"创建用户")]
public int Create_User_Id{get{return this._create_user_id;}set{this._create_user_id = value;}}
[PresentAttribute(true,"修改时间")]
public DateTime Modi_Time{get{return this._modi_time;}set{this._modi_time = value;}}
[PresentAttribute(true,"修改用户")]
public int Modi_User_Id{get{return this._modi_user_id;}set{this._modi_user_id = value;}}
[PresentAttribute(true,"启用状态")]
public int State{get{return this._state;}set{this._state = value;}}
public static INS_DADP_SCHEME Get(int id)
{
const string sSql = "select ID, Name, Summary,DataSource_Type,DataSource_ID,DataObjective_ID,Strategy_Type,Fire_Dates,Fire_Time,Create_Time,Create_User_ID,Modi_Time,Modi_User_ID,State from INS_DADP_SCHEME where ID = @ID ";
#region Parameters Block
OleDbParameter[] parameters = new OleDbParameter[1];
parameters[0] = new OleDbParameter();
parameters[0].ParameterName ="@ID";
parameters[0].Size = 10;
parameters[0].OleDbType = OleDbType.Integer;
parameters[0].Value = id;
#endregion
#region Push Block
INS_DADP_SCHEME dataObj = null;
ArrayList objArray = new ArrayList();
OleDbHelper.ConnectionString = Configuration.ConnnectionString;
OleDbConnection connection = OleDbHelper.PopConnection();
OleDbDataReader sqlReader = OleDbHelper.ExecuteReader(sSql,parameters,connection);
while (sqlReader.Read())
{
dataObj = new INS_DADP_SCHEME();
if(!sqlReader.IsDBNull(0))
dataObj.Id = sqlReader.GetInt32(0);
if(!sqlReader.IsDBNull(1))
dataObj.Name = sqlReader.GetString(1);
if(!sqlReader.IsDBNull(2))
dataObj.Summary = sqlReader.GetString(2);
if(!sqlReader.IsDBNull(3))
dataObj.DataSource_Type = sqlReader.GetInt32(3);
if(!sqlReader.IsDBNull(4))
dataObj.DataSource_Id = sqlReader.GetInt32(4);
if(!sqlReader.IsDBNull(5))
dataObj.DataObjective_Id = sqlReader.GetInt32(5);
if(!sqlReader.IsDBNull(6))
dataObj.Strategy_Type = sqlReader.GetInt32(6);
if(!sqlReader.IsDBNull(7))
dataObj.Fire_Dates = sqlReader.GetString(7);
if(!sqlReader.IsDBNull(8))
dataObj.Fire_Time = sqlReader.GetDateTime(8);
if(!sqlReader.IsDBNull(9))
dataObj.Create_Time = sqlReader.GetDateTime(9);
if(!sqlReader.IsDBNull(10))
dataObj.Create_User_Id = sqlReader.GetInt32(10);
if(!sqlReader.IsDBNull(11))
dataObj.Modi_Time = sqlReader.GetDateTime(11);
if(!sqlReader.IsDBNull(12))
dataObj.Modi_User_Id = sqlReader.GetInt32(12);
if(!sqlReader.IsDBNull(13))
dataObj.State = sqlReader.GetInt32(13);
objArray.Add(dataObj);
}
sqlReader.Close();
OleDbHelper.PushConnection(connection);
#endregion
INS_DADP_SCHEME[] objs = new INS_DADP_SCHEME[objArray.Count];
objArray.CopyTo(0,objs,0,objs.Length);
return objs[0];
}
public static INS_DADP_SCHEME[] List()
{
const string sSql = "select ID, Name, Summary,DataSource_Type,DataSource_ID,DataObjective_ID,Strategy_Type,Fire_Dates,Fire_Time,Create_Time,Create_User_ID,Modi_Time,Modi_User_ID,State from INS_DADP_SCHEME ";
#region Parameters Block
OleDbParameter[] parameters = new OleDbParameter[0];
#endregion
#region Push Block
INS_DADP_SCHEME dataObj = null;
ArrayList objArray = new ArrayList();
OleDbHelper.ConnectionString = Configuration.ConnnectionString;
OleDbConnection connection = OleDbHelper.PopConnection();
OleDbDataReader sqlReader = OleDbHelper.ExecuteReader(sSql,parameters,connection);
while (sqlReader.Read())
{
dataObj = new INS_DADP_SCHEME();
if(!sqlReader.IsDBNull(0))
dataObj.Id = sqlReader.GetInt32(0);
if(!sqlReader.IsDBNull(1))
dataObj.Name = sqlReader.GetString(1);
if(!sqlReader.IsDBNull(2))
dataObj.Summary = sqlReader.GetString(2);
if(!sqlReader.IsDBNull(3))
dataObj.DataSource_Type = sqlReader.GetInt32(3);
if(!sqlReader.IsDBNull(4))
dataObj.DataSource_Id = sqlReader.GetInt32(4);
if(!sqlReader.IsDBNull(5))
dataObj.DataObjective_Id = sqlReader.GetInt32(5);
if(!sqlReader.IsDBNull(6))
dataObj.Strategy_Type = sqlReader.GetInt32(6);
if(!sqlReader.IsDBNull(7))
dataObj.Fire_Dates = sqlReader.GetString(7);
if(!sqlReader.IsDBNull(8))
dataObj.Fire_Time = sqlReader.GetDateTime(8);
if(!sqlReader.IsDBNull(9))
dataObj.Create_Time = sqlReader.GetDateTime(9);
if(!sqlReader.IsDBNull(10))
dataObj.Create_User_Id = sqlReader.GetInt32(10);
if(!sqlReader.IsDBNull(11))
dataObj.Modi_Time = sqlReader.GetDateTime(11);
if(!sqlReader.IsDBNull(12))
dataObj.Modi_User_Id = sqlReader.GetInt32(12);
if(!sqlReader.IsDBNull(13))
dataObj.State = sqlReader.GetInt32(13);
objArray.Add(dataObj);
}
sqlReader.Close();
OleDbHelper.PushConnection(connection);
#endregion
INS_DADP_SCHEME[] objs = new INS_DADP_SCHEME[objArray.Count];
objArray.CopyTo(0,objs,0,objs.Length);
return objs;
}
public static void Save(INS_DADP_SCHEME curObj)
{
const string sSql = "insert into INS_DADP_SCHEME( Name, Summary,DataSource_Type,DataSource_ID,DataObjective_ID,Strategy_Type,Fire_Dates,Fire_Time,Create_Time,Create_User_ID,Modi_Time,Modi_User_ID,State) values (@Name, @Summary,@DataSource_Type,@DataSource_ID,@DataObjective_ID,@Strategy_Type,@Fire_Dates,@Fire_Time,@Create_Time,@Create_User_ID,@Modi_Time,@Modi_User_ID,@State);";
#region Parameters Block
OleDbParameter[] parameters = new OleDbParameter[13];
parameters[0] = new OleDbParameter();
parameters[0].ParameterName ="@Name";
parameters[0].OleDbType = OleDbType.VarChar;
parameters[0].Size = 500;
parameters[0].Value = curObj.Name;
parameters[1] = new OleDbParameter();
parameters[1].ParameterName ="@Summary";
parameters[1].OleDbType = OleDbType.VarChar;
parameters[1].Size = 500;
parameters[1].Value = curObj.Summary;
parameters[2] = new OleDbParameter();
parameters[2].ParameterName ="@DataSource_Type";
parameters[2].OleDbType = OleDbType.Integer;
parameters[2].Value = curObj.DataSource_Type;
parameters[3] = new OleDbParameter();
parameters[3].ParameterName ="@DataSource_ID";
parameters[3].OleDbType = OleDbType.Integer;
parameters[3].Value = curObj.DataSource_Id;
parameters[4] = new OleDbParameter();
parameters[4].ParameterName ="@DataObjective_ID";
parameters[4].OleDbType = OleDbType.Integer;
parameters[4].Value = curObj.DataObjective_Id;
parameters[5] = new OleDbParameter();
parameters[5].ParameterName ="@Strategy_Type";
parameters[5].OleDbType = OleDbType.Integer;
parameters[5].Value = curObj.Strategy_Type;
parameters[6] = new OleDbParameter();
parameters[6].ParameterName ="@Fire_Dates";
parameters[6].OleDbType = OleDbType.VarChar;
parameters[6].Size = 500;
parameters[6].Value = curObj.Fire_Dates;
parameters[7] = new OleDbParameter();
parameters[7].ParameterName ="@Fire_Time";
parameters[7].OleDbType = OleDbType.DBTimeStamp;
parameters[7].Value = curObj.Fire_Time;
parameters[8] = new OleDbParameter();
parameters[8].ParameterName ="@Create_Time";
parameters[8].OleDbType = OleDbType.DBTimeStamp;
parameters[8].Value = curObj.Create_Time;
parameters[9] = new OleDbParameter();
parameters[9].ParameterName ="@Create_User_ID";
parameters[9].OleDbType = OleDbType.Integer;
parameters[9].Value = curObj.Create_User_Id;
parameters[10] = new OleDbParameter();
parameters[10].ParameterName ="@Modi_Time";
parameters[10].OleDbType = OleDbType.DBTimeStamp;
parameters[10].Value = curObj.Modi_Time;
parameters[11] = new OleDbParameter();
parameters[11].ParameterName ="@Modi_User_ID";
parameters[11].OleDbType = OleDbType.Integer;
parameters[11].Value = curObj.Modi_User_Id;
parameters[12] = new OleDbParameter();
parameters[12].ParameterName ="@State";
parameters[12].OleDbType = OleDbType.Integer;
parameters[12].Value = curObj.State;
#endregion
OleDbHelper.ConnectionString = Configuration.ConnnectionString;
OleDbHelper.ExecuteNonQuery(sSql,parameters);
}
public static void Save(INS_DADP_SCHEME curObj,OleDbTransaction curTrans)
{
const string sSql = "insert into INS_DADP_SCHEME( Name, Summary,DataSource_Type,DataSource_ID,DataObjective_ID,Strategy_Type,Fire_Dates,Fire_Time,Create_Time,Create_User_ID,Modi_Time,Modi_User_ID,State) values (@Name, @Summary,@DataSource_Type,@DataSource_ID,@DataObjective_ID,@Strategy_Type,@Fire_Dates,@Fire_Time,@Create_Time,@Create_User_ID,@Modi_Time,@Modi_User_ID,@State);";
#region Parameters Block
OleDbParameter[] parameters = new OleDbParameter[13];
parameters[0] = new OleDbParameter();
parameters[0].ParameterName ="@Name";
parameters[0].OleDbType = OleDbType.VarChar;
parameters[0].Size = 500;
parameters[0].Value = curObj.Name;
parameters[1] = new OleDbParameter();
parameters[1].ParameterName ="@Summary";
parameters[1].OleDbType = OleDbType.VarChar;
parameters[1].Size = 500;
parameters[1].Value = curObj.Summary;
parameters[2] = new OleDbParameter();
parameters[2].ParameterName ="@DataSource_Type";
parameters[2].OleDbType = OleDbType.Integer;
parameters[2].Value = curObj.DataSource_Type;
parameters[3] = new OleDbParameter();
parameters[3].ParameterName ="@DataSource_ID";
parameters[3].OleDbType = OleDbType.Integer;
parameters[3].Value = curObj.DataSource_Id;
parameters[4] = new OleDbParameter();
parameters[4].ParameterName ="@DataObjective_ID";
parameters[4].OleDbType = OleDbType.Integer;
parameters[4].Value = curObj.DataObjective_Id;
parameters[5] = new OleDbParameter();
parameters[5].ParameterName ="@Strategy_Type";
parameters[5].OleDbType = OleDbType.Integer;
parameters[5].Value = curObj.Strategy_Type;
parameters[6] = new OleDbParameter();
parameters[6].ParameterName ="@Fire_Dates";
parameters[6].OleDbType = OleDbType.VarChar;
parameters[6].Size = 500;
parameters[6].Value = curObj.Fire_Dates;
parameters[7] = new OleDbParameter();
parameters[7].ParameterName ="@Fire_Time";
parameters[7].OleDbType = OleDbType.DBTimeStamp;
parameters[7].Value = curObj.Fire_Time;
parameters[8] = new OleDbParameter();
parameters[8].ParameterName ="@Create_Time";
parameters[8].OleDbType = OleDbType.DBTimeStamp;
parameters[8].Value = curObj.Create_Time;
parameters[9] = new OleDbParameter();
parameters[9].ParameterName ="@Create_User_ID";
parameters[9].OleDbType = OleDbType.Integer;
parameters[9].Value = curObj.Create_User_Id;
parameters[10] = new OleDbParameter();
parameters[10].ParameterName ="@Modi_Time";
parameters[10].OleDbType = OleDbType.DBTimeStamp;
parameters[10].Value = curObj.Modi_Time;
parameters[11] = new OleDbParameter();
parameters[11].ParameterName ="@Modi_User_ID";
parameters[11].OleDbType = OleDbType.Integer;
parameters[11].Value = curObj.Modi_User_Id;
parameters[12] = new OleDbParameter();
parameters[12].ParameterName ="@State";
parameters[12].OleDbType = OleDbType.Integer;
parameters[12].Value = curObj.State;
#endregion
OleDbHelper.ConnectionString = Configuration.ConnnectionString;
OleDbHelper.ExecuteNonQuery(sSql,parameters,curTrans);
}
public static void Update(INS_DADP_SCHEME curObj)
{
const string sSql = "update INS_DADP_SCHEME set Name = @Name, Summary = @Summary,DataSource_Type = @DataSource_Type,DataSource_ID = @DataSource_ID,DataObjective_ID = @DataObjective_ID,Strategy_Type = @Strategy_Type,Fire_Dates = @Fire_Dates,Fire_Time =@Fire_Time,Create_Time = @Create_Time,Create_User_ID = @Create_User_ID,Modi_Time = @Modi_Time,Modi_User_ID = @Modi_User_ID,State = @State where ID = @ID ;";
#region Parameters Block
OleDbParameter[] parameters = new OleDbParameter[14];
parameters[0] = new OleDbParameter();
parameters[0].ParameterName ="@Name";
parameters[0].OleDbType = OleDbType.VarChar;
parameters[0].Size = 500;
parameters[0].Value = curObj.Name;
parameters[1] = new OleDbParameter();
parameters[1].ParameterName ="@Summary";
parameters[1].OleDbType = OleDbType.VarChar;
parameters[1].Size = 500;
parameters[1].Value = curObj.Summary;
parameters[2] = new OleDbParameter();
parameters[2].ParameterName ="@DataSource_Type";
parameters[2].OleDbType = OleDbType.Integer;
parameters[2].Value = curObj.DataSource_Type;
parameters[3] = new OleDbParameter();
parameters[3].ParameterName ="@DataSource_ID";
parameters[3].OleDbType = OleDbType.Integer;
parameters[3].Value = curObj.DataSource_Id;
parameters[4] = new OleDbParameter();
parameters[4].ParameterName ="@DataObjective_ID";
parameters[4].OleDbType = OleDbType.Integer;
parameters[4].Value = curObj.DataObjective_Id;
parameters[5] = new OleDbParameter();
parameters[5].ParameterName ="@Strategy_Type";
parameters[5].OleDbType = OleDbType.Integer;
parameters[5].Value = curObj.Strategy_Type;
parameters[6] = new OleDbParameter();
parameters[6].ParameterName ="@Fire_Dates";
parameters[6].OleDbType = OleDbType.VarChar;
parameters[6].Size = 500;
parameters[6].Value = curObj.Fire_Dates;
parameters[7] = new OleDbParameter();
parameters[7].ParameterName ="@Fire_Time";
parameters[7].OleDbType = OleDbType.DBTimeStamp;
parameters[7].Value = curObj.Fire_Time;
parameters[8] = new OleDbParameter();
parameters[8].ParameterName ="@Create_Time";
parameters[8].OleDbType = OleDbType.DBTimeStamp;
parameters[8].Value = curObj.Create_Time;
parameters[9] = new OleDbParameter();
parameters[9].ParameterName ="@Create_User_ID";
parameters[9].OleDbType = OleDbType.Integer;
parameters[9].Value = curObj.Create_User_Id;
parameters[10] = new OleDbParameter();
parameters[10].ParameterName ="@Modi_Time";
parameters[10].OleDbType = OleDbType.DBTimeStamp;
parameters[10].Value = curObj.Modi_Time;
parameters[11] = new OleDbParameter();
parameters[11].ParameterName ="@Modi_User_ID";
parameters[11].OleDbType = OleDbType.Integer;
parameters[11].Value = curObj.Modi_User_Id;
parameters[12] = new OleDbParameter();
parameters[12].ParameterName ="@State";
parameters[12].OleDbType = OleDbType.Integer;
parameters[12].Value = curObj.State;
parameters[13] = new OleDbParameter();
parameters[13].ParameterName ="@ID";
parameters[13].OleDbType = OleDbType.Integer;
parameters[13].Value = curObj.Id;
#endregion
OleDbHelper.ConnectionString = Configuration.ConnnectionString;
OleDbHelper.ExecuteNonQuery(sSql,parameters);
}
public static void Update(INS_DADP_SCHEME curObj,OleDbTransaction curTrans)
{
const string sSql = "update INS_DADP_SCHEME set Name = @Name, Summary = @Summary,DataSource_Type = @DataSource_Type,DataSource_ID = @DataSource_ID,DataObjective_ID = @DataObjective_ID,Strategy_Type = @Strategy_Type,Fire_Dates = @Fire_Dates,Fire_Time =@Fire_Time,Create_Time = @Create_Time,Create_User_ID = @Create_User_ID,Modi_Time = @Modi_Time,Modi_User_ID = @Modi_User_ID,State = @State where ID = @ID ;";
#region Parameters Block
OleDbParameter[] parameters = new OleDbParameter[14];
parameters[0] = new OleDbParameter();
parameters[0].ParameterName ="@Name";
parameters[0].OleDbType = OleDbType.VarChar;
parameters[0].Size = 500;
parameters[0].Value = curObj.Name;
parameters[1] = new OleDbParameter();
parameters[1].ParameterName ="@Summary";
parameters[1].OleDbType = OleDbType.VarChar;
parameters[1].Size = 500;
parameters[1].Value = curObj.Summary;
parameters[2] = new OleDbParameter();
parameters[2].ParameterName ="@DataSource_Type";
parameters[2].OleDbType = OleDbType.Integer;
parameters[2].Value = curObj.DataSource_Type;
parameters[3] = new OleDbParameter();
parameters[3].ParameterName ="@DataSource_ID";
parameters[3].OleDbType = OleDbType.Integer;
parameters[3].Value = curObj.DataSource_Id;
parameters[4] = new OleDbParameter();
parameters[4].ParameterName ="@DataObjective_ID";
parameters[4].OleDbType = OleDbType.Integer;
parameters[4].Value = curObj.DataObjective_Id;
parameters[5] = new OleDbParameter();
parameters[5].ParameterName ="@Strategy_Type";
parameters[5].OleDbType = OleDbType.Integer;
parameters[5].Value = curObj.Strategy_Type;
parameters[6] = new OleDbParameter();
parameters[6].ParameterName ="@Fire_Dates";
parameters[6].OleDbType = OleDbType.VarChar;
parameters[6].Size = 500;
parameters[6].Value = curObj.Fire_Dates;
parameters[7] = new OleDbParameter();
parameters[7].ParameterName ="@Fire_Time";
parameters[7].OleDbType = OleDbType.DBTimeStamp;
parameters[7].Value = curObj.Fire_Time;
parameters[8] = new OleDbParameter();
parameters[8].ParameterName ="@Create_Time";
parameters[8].OleDbType = OleDbType.DBTimeStamp;
parameters[8].Value = curObj.Create_Time;
parameters[9] = new OleDbParameter();
parameters[9].ParameterName ="@Create_User_ID";
parameters[9].OleDbType = OleDbType.Integer;
parameters[9].Value = curObj.Create_User_Id;
parameters[10] = new OleDbParameter();
parameters[10].ParameterName ="@Modi_Time";
parameters[10].OleDbType = OleDbType.DBTimeStamp;
parameters[10].Value = curObj.Modi_Time;
parameters[11] = new OleDbParameter();
parameters[11].ParameterName ="@Modi_User_ID";
parameters[11].OleDbType = OleDbType.Integer;
parameters[11].Value = curObj.Modi_User_Id;
parameters[12] = new OleDbParameter();
parameters[12].ParameterName ="@State";
parameters[12].OleDbType = OleDbType.Integer;
parameters[12].Value = curObj.State;
parameters[13] = new OleDbParameter();
parameters[13].ParameterName ="@ID";
parameters[13].OleDbType = OleDbType.Integer;
parameters[13].Value = curObj.Id;
#endregion
OleDbHelper.ConnectionString = Configuration.ConnnectionString;
OleDbHelper.ExecuteNonQuery(sSql,parameters,curTrans);
}
public static void Delete(INS_DADP_SCHEME curObj)
{
const string sSql = "delete from INS_DADP_PARAM where ID = @ID ;";
#region Parameters Block
OleDbParameter[] parameters = new OleDbParameter[1];
parameters[0] = new OleDbParameter();
parameters[0].ParameterName ="@ID";
parameters[0].Size = 10;
parameters[0].OleDbType = OleDbType.Integer;
parameters[0].Value = curObj.Id;
#endregion
OleDbHelper.ExecuteNonQuery(sSql,parameters);
}
public static void Delete(INS_DADP_SCHEME curObj,OleDbTransaction curTrans)
{
const string sSql = "delete from INS_DADP_PARAM where ID = @ID ;";
#region Parameters Block
OleDbParameter[] parameters = new OleDbParameter[1];
parameters[0] = new OleDbParameter();
parameters[0].ParameterName ="@ID";
parameters[0].Size = 10;
parameters[0].OleDbType = OleDbType.Integer;
parameters[0].Value = curObj.Id;
#endregion
OleDbHelper.ExecuteNonQuery(sSql,parameters,curTrans);
}
}
/// <summary>
/// OleDb数据库操作助手
/// </summary>
public class OleDbHelper
{
protected static string connectionString = null;
protected static OleDbParameter param = null;
public static string ConnectionString
{
set
{
connectionString = value;
}
get
{
return connectionString;
}
}
public static OleDbConnection PopConnection()
{
if(connectionString == null)
{
throw new System.ArgumentNullException("连接字符串未设置");
}
OleDbConnection myConn = new OleDbConnection(connectionString);
myConn.Open();
return myConn;
}
public static void PushConnection(OleDbConnection connection)
{
if(connection.State.Equals(ConnectionState.Open))
connection.Close();
}
public static void ExecuteNonQuery(string strSql)
{
if(connectionString == null)
{
throw new System.ArgumentNullException("连接字符串未设置");
}
OleDbConnection myConn = new OleDbConnection(connectionString);
OleDbCommand myCmd = new OleDbCommand(strSql,myConn);
myConn.Open();
myCmd.ExecuteNonQuery();
myConn.Close();
}
public static DataTable ExecuteQuery(string strSql)
{
DataTable myTable = new DataTable();
if(connectionString == null)
{
throw new System.ArgumentNullException("连接字符串未设置");
}
OleDbConnection myConn = new OleDbConnection(connectionString);
OleDbDataAdapter myAdp = new OleDbDataAdapter(strSql,myConn);
myAdp.Fill(myTable);
return myTable;
}
public static void ExecuteNonQuery(string strSql,OleDbParameter[] myParams)
{
if(connectionString == null)
{
throw new System.ArgumentNullException("连接字符串未设置");
}
OleDbConnection myConn = new OleDbConnection(connectionString);
OleDbCommand myCmd = new OleDbCommand(strSql,myConn);
foreach( OleDbParameter p in myParams )
{
param = new OleDbParameter();
param.ParameterName = p.ParameterName;
param.DbType = p.DbType;
param.Direction = p.Direction;
param.Size = p.Size;
param.Value = p.Value;
myCmd.Parameters.Add(param);
}
myCmd.ExecuteNonQuery();
myConn.Close();
}
public static DataTable ExecuteQuery(string strSql,OleDbParameter[] myParams)
{
DataTable myTable = new DataTable();
if(connectionString == null)
{
throw new System.ArgumentNullException("连接字符串未设置");
}
OleDbConnection myConn = new OleDbConnection(connectionString);
OleDbDataAdapter myAdp = new OleDbDataAdapter();
myAdp.SelectCommand = new OleDbCommand();
myAdp.SelectCommand.Connection = myConn;
myAdp.SelectCommand.CommandText = strSql;
foreach( OleDbParameter p in myParams )
{
param = new OleDbParameter();
param.ParameterName = p.ParameterName;
param.DbType = p.DbType;
param.Direction = p.Direction;
param.Size = p.Size;
param.Value = p.Value;
myAdp.SelectCommand.Parameters.Add(param);
}
myAdp.Fill(myTable);
return myTable;
}
public static void ExecuteNonQuery(string strSql,OleDbTransaction curTrans)
{
OleDbCommand myCmd = curTrans.Connection.CreateCommand();
myCmd.CommandType = CommandType.Text;
myCmd.CommandText = strSql;
myCmd.Transaction = curTrans;
myCmd.ExecuteNonQuery();
}
public static void ExecuteNonQuery(string strSql,OleDbParameter[] myParams,OleDbTransaction curTrans)
{
OleDbCommand myCmd = curTrans.Connection.CreateCommand();
myCmd.CommandType = CommandType.Text;
myCmd.CommandText = strSql;
myCmd.Transaction = curTrans;
foreach( OleDbParameter p in myParams )
{
param = new OleDbParameter();
param.ParameterName = p.ParameterName;
param.DbType = p.DbType;
param.Direction = p.Direction;
param.Size = p.Size;
param.Value = p.Value;
myCmd.Parameters.Add(param);
}
myCmd.ExecuteNonQuery();
}
public static OleDbDataReader ExecuteReader(string strSql,OleDbParameter[] myParams,OleDbConnection connection)
{
OleDbCommand myCmd = connection.CreateCommand();
myCmd.CommandType = CommandType.Text;
myCmd.CommandText = strSql;
foreach( OleDbParameter p in myParams )
{
param = new OleDbParameter();
param.ParameterName = p.ParameterName;
param.DbType = p.DbType;
param.Direction = p.Direction;
param.Size = p.Size;
param.Value = p.Value;
myCmd.Parameters.Add(param);
}
return myCmd.ExecuteReader();
}
}
第三部分:反射操作逻辑定义
/// <summary>
/// GridUtil 的摘要说明。
/// </summary>
public class DataGridUtil
{
/// <summary>
/// 为指定的DataTable添加列名集合
/// </summary>
/// <param name="curTable">目标DataTable</param>
/// <param name="columns">列集合</param>
/// <returns>已成功添加列集合的DataTable</returns>
protected static DataTable PrepareTableColumn(DataTable curTable,params string[] columns)
{
curTable.Columns.Clear();
foreach(string caption in columns)
{
DataColumn column = new DataColumn();
column.ColumnName = caption;
column.DataType = typeof(string);
curTable.Columns.Add(column);
}
return curTable;
}
/// <summary>
/// 为指定的DataTable添加列名集合
/// </summary>
/// <param name="curTable">目标DataTable</param>
/// <param name="curObj">已绑定PresentAttribute特性的对象实例</param>
/// <returns>已成功添加列集合的DataTable</returns>
protected static DataTable PrepareTableColumn(DataTable curTable,object curObj)
{
curTable.Columns.Clear();
///获取所有属性
PropertyInfo[] propers = curObj.GetType().GetProperties();
foreach(PropertyInfo p in propers)
{
///获取该属性上对应的PresentAttribute特性值
PresentAttribute[] presents = (PresentAttribute[])p.GetCustomAttributes(typeof(PresentAttribute),false);
///如果该属性的PresentAttribute特性值说明该属性需要进行显示
if(presents[0].Present)
{
///为目标数据表添加列信息
DataColumn column = new DataColumn();
column.ColumnName = presents[0].Caption;
column.DataType = p.GetType();
curTable.Columns.Add(column);
}
}
return curTable;
}
/// <summary>
/// 为已绑定PresentAttribute特性的对象实例数组自动生成一个DataTable
/// </summary>
/// <param name="objects">已绑定PresentAttribute特性的对象实例数组</param>
/// <param name="columns">默认的列名定义集合(该集合仅在objects对象实例数组Length == 0时被使用)</param>
/// <returns>已成功添加列集合且已自动生成对应数据行的DataTable</returns>
public static DataTable PrepareDataTable(object[] objects, string[] columns)
{
DataTable curTable = null;
///为空则为目标数据表添加一个默认的列显示
if(objects.Length == 0)
{
curTable = PrepareTableColumn(new DataTable(),columns);
return curTable;
}
///声明列结构
curTable = PrepareTableColumn(new DataTable(),objects[0]);
foreach(object o in objects)
{
///获取所有属性
PropertyInfo[] propers = o.GetType().GetProperties();
///创建一个新行
DataRow newRow = curTable.NewRow();
///对每一个属性进行遍历
foreach(PropertyInfo p in propers)
{
///获取该属性上对应的PresentAttribute特性值
PresentAttribute[] presents = (PresentAttribute[])p.GetCustomAttributes(typeof(PresentAttribute),false);
///如果该属性的PresentAttribute特性值说明该属性需要进行显示
if(presents[0].Present)
///将新行中指定列的值设置为当前属性的值
newRow[presents[0].Caption] = p.GetValue(o,null).ToString();
}
curTable.Rows.Add(newRow);
}
return curTable;
}
}
第四部分: UI界面数据获取及显示
private System.Windows.Forms.DataGrid dgSchemes;
private void FrmSchemeView_Load(object sender, System.EventArgs e)
{
INS_DADP_SCHEME[] schemes = Scheme.List();
dgSchemes.DataSource = DataGridUtil.PrepareDataTable(schemes,new string[]{"方案ID","启用状态","方案名称","方案描述","创建时间","创建用户","修改时间","修改用户"});
}