本文通過C#特性和反射功能,實現了數據存儲和視圖模型之間的一個自定義轉換,實現了存儲和呈現的分離,從設計模式上避免了在面向對象的開發環境中使用SQL語句直接繞過對象繼承系統直接操作存儲結構的做法,值得同行借鑒.
所支持的視圖轉換包括:從數據存儲ID到NAME的一個轉換,從枚舉值到枚舉名稱的一個轉換,從存儲對象模型到UI對象模型的一個轉換.
建議將存儲對象定義直接從數據庫定義生成(利用存儲代碼生成器),然後在UI生成器上配置對應的視圖呈現特性,最後完成UI的自動生成工作.
有意者可聯系作者本人,共同完成開源項目構建工作.
//特性定義部分:
/// <summary>
/// 呈現特性定義類
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
public class PresentAttribute:System.Attribute
{
protected bool _present;
protected int _index;
protected string _caption;
protected Type _reference;
protected string _getMethod;
protected bool _isInstanceMethod;
public PresentAttribute(bool present,string caption,int index,Type reference,string getMethod,bool isInstanceMethod)
{
_present = present;
_index = index;
_caption = caption;
_reference = reference;
_getMethod = getMethod;
_isInstanceMethod = isInstanceMethod;
}
public PresentAttribute(bool present,string caption,Type reference,string getMethod):this(present,caption,0,reference,getMethod,false)
{
}
public PresentAttribute(bool present,string caption,Type reference,string getMethod,bool isInstanceMethod):this(present,caption,0,reference,getMethod,isInstanceMethod)
{
}
public PresentAttribute(bool present,string caption,Type reference):this(present,caption,0,reference,null,false)
{
}
/// <summary>
///
/// </summary>
/// <param name="present">是否顯示</param>
/// <param name="caption">顯示標題</param>
/// <param name="index">顯示索引(從0開始)</param>
public PresentAttribute(bool present,string caption,int index):this(present,caption,index,null,null,false)
{
}
public PresentAttribute(bool present,string caption):this(present,caption,0,null,null,false)
{
}
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>
/// 引用類型:通常是對另一個DO類型的引用
/// </summary>
public Type Reference{get{return this._reference;}}
/// <summary>
/// 獲取方法:通常是一個通過id獲取name的方法
/// </summary>
public string GetMethod{get{return this._getMethod;}}
/// <summary>
/// 實例方法:返回false代表一個靜態方法
/// </summary>
public bool IsInstanceMethod{get{return this._isInstanceMethod;}}
}
//反射邏輯部分:
/// <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)
{
if(presents[0].Reference == null)
{
///將新行中指定列的值設置為當前屬性的值
newRow[presents[0].Caption] = p.GetValue(o,null).ToString();
}
///是一個引用類型,需要利用反射進行轉換
else
{
///獲取當前屬性的值
object needTransVal = p.GetValue(o,null);
///獲取當前屬性的引用類型
Type refType = presents[0].Reference;
if(refType.IsEnum)
{
///調用當前屬性的getMethod方法所聲明的方法,調用該方法以獲取目標值,以完成諸如從枚舉值到枚舉名稱的一個轉換
object targetVal = null;
targetVal = EnumUtil.GetEnumName(refType,needTransVal);
if(targetVal != null)
///將新行中指定列的值設置為轉換後的值
newRow[presents[0].Caption] = targetVal.ToString();
else
newRow[presents[0].Caption] = DBNull.Value;
}
else
{
///創建一個引用類型的對象
object refObj = refType.Assembly.CreateInstance(refType.FullName);
///調用當前屬性的getMethod方法所聲明的方法,調用該方法以獲取目標值,以完成諸如從id到name的一個轉換
object targetVal = null;
if(presents[0].IsInstanceMethod)
{
targetVal = refType.InvokeMember(presents[0].GetMethod,BindingFlags.Public|BindingFlags.Instance|BindingFlags.InvokeMethod,null,refObj,new object[]{needTransVal});
}
else
{
targetVal = refType.InvokeMember(presents[0].GetMethod,BindingFlags.Public|BindingFlags.Static|BindingFlags.InvokeMethod,null,null,new object[]{needTransVal});
}
if(targetVal != null)
///將新行中指定列的值設置為轉換後的值
newRow[presents[0].Caption] = targetVal.ToString();
else
newRow[presents[0].Caption] = DBNull.Value;
}
}
}
}
curTable.Rows.Add(newRow);
}
return curTable;
}
}
/// <summary>
/// EnumUtil 的摘要說明。
/// </summary>
public class EnumUtil
{
/// <summary>
/// 完成指定枚舉的從名稱到值的轉換
/// </summary>
/// <param name="curEnum">指定枚舉</param>
/// <param name="curName">名稱</param>
/// <returns>值</returns>
public static int GetEnumValue(Type curEnum,object curName)
{
int value = int.MinValue;
if(curName != null)
{
string[] names = Enum.GetNames(curEnum);
int[] values = (int[])Enum.GetValues(curEnum);
for(int i = 0; i < names.Length; i ++)
{
if(names[i] == curName.ToString())
{
value = values[i];
break;
}
}
}
return value;
}
/// <summary>
/// 完成指定枚舉的從值到名稱的轉換
/// </summary>
/// <param name="curEnum">指定枚舉</param>
/// <param name="curValue">值</param>
/// <returns>名稱</returns>
public static string GetEnumName(Type curEnum,object curValue)
{
return Enum.GetName(curEnum,curValue);
}
}
//數據定義部分
/// <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 string _fire_time;
protected string _create_time;
protected int _create_user_id;
protected string _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 string Fire_Time{get{return this._fire_time;}set{this._fire_time = value;}}
[PresentAttribute(true,"創建時間")]
public string Create_Time{get{return this._create_time;}set{this._create_time = value;}}
//此處定義為從一個數據對象ID到NAME的轉換
[PresentAttribute(true,"創建用戶",typeof(INS_DADP_USER),"GetUserName",false)]
public int Create_User_Id{get{return this._create_user_id;}set{this._create_user_id = value;}}
[PresentAttribute(true,"修改時間")]
public string Modi_Time{get{return this._modi_time;}set{this._modi_time = value;}}
//此處定義為從一個數據對象ID到NAME的轉換
[PresentAttribute(true,"修改用戶",typeof(INS_DADP_USER),"GetUserName",false)]
public int Modi_User_Id{get{return this._modi_user_id;}set{this._modi_user_id = value;}}
//此處定義為從一個枚舉對象值到名稱的轉換
[PresentAttribute(true,"啟用狀態",typeof(SchemeState))]
public int State{get{return this._state;}set{this._state = value;}}
public static string GetSchemeName(int id)
{
INS_DADP_SCHEME curScheme = Get(id);
if(curScheme == null)return null;
else
return curScheme.Name;
}
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.GetString(8);
if(!sqlReader.IsDBNull(9))
dataObj.Create_Time = sqlReader.GetString(9);
if(!sqlReader.IsDBNull(10))
dataObj.Create_User_Id = sqlReader.GetInt32(10);
if(!sqlReader.IsDBNull(11))
dataObj.Modi_Time = sqlReader.GetString(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.Length == 0 ? null: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.GetString(8);
if(!sqlReader.IsDBNull(9))
dataObj.Create_Time = sqlReader.GetString(9);
if(!sqlReader.IsDBNull(10))
dataObj.Create_User_Id = sqlReader.GetInt32(10);
if(!sqlReader.IsDBNull(11))
dataObj.Modi_Time = sqlReader.GetString(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.VarChar;
parameters[7].Size = 50;
parameters[7].Value = curObj.Fire_Time;
parameters[8] = new OleDbParameter();
parameters[8].ParameterName ="@Create_Time";
parameters[8].OleDbType = OleDbType.VarChar;
parameters[8].Size = 50;
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.VarChar;
parameters[10].Size = 50;
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.VarChar;
parameters[7].Size = 50;
parameters[7].Value = curObj.Fire_Time;
parameters[8] = new OleDbParameter();
parameters[8].ParameterName ="@Create_Time";
parameters[8].OleDbType = OleDbType.VarChar;
parameters[8].Size = 50;
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.VarChar;
parameters[10].Size = 50;
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";r /> 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.VarChar;
parameters[7].Size = 50;
parameters[7].Value = curObj.Fire_Time;
parameters[8] = new OleDbParameter();
parameters[8].ParameterName ="@Create_Time";
parameters[8].OleDbType = OleDbType.VarChar;
parameters[8].Size = 50;
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.VarChar;
parameters[10].Size = 50;
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.VarChar;
parameters[7].Size =50;
parameters[7].Value = curObj.Fire_Time;
parameters[8] = new OleDbParameter();
parameters[8].ParameterName ="@Create_Time";
parameters[8].OleDbType = OleDbType.VarChar;
parameters[8].Size = 50;
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.VarChar;
parameters[10].Size = 50;
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_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 = curObj.Id;
#endregion
OleDbHelper.ExecuteNonQuery(sSql,parameters);
}
public static void Delete(INS_DADP_SCHEME curObj,OleDbTransaction curTrans)
{
const string sSql = "delete 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 = 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);
}
myConn.Open();
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();
}
public static object SelectMaxIdInTrans(string table,OleDbTransaction curTrans)
{
OleDbCommand myCmd =curTrans.Connection.CreateCommand();
myCmd.Transaction = curTrans;
myCmd.CommandType = CommandType.Text;
myCmd.CommandText = "select max(id) from " + table;
return myCmd.ExecuteScalar();
}
}
//引用定義部分:
public enum SchemeState
{
Open = 1,
Close = 2
}
/// <summary>
/// 用戶信息存儲對象
/// </summary>
public class INS_DADP_USER
{
protected int _id;
protected string _user_name;
protected string _user_pwd;
protected string _login_time;
[PresentAttribute()]
public int Id{get{return this._id;}set{this._id = value;}}
[PresentAttribute()]
public string User_Name{get{return this._user_name;}set{this._user_name = value;}}
[PresentAttribute()]
public string User_Pwd{get{return this._user_pwd;}set{this._user_pwd = value;}}
[PresentAttribute()]
public string Login_Time{get{return this._login_time;}set{this._login_time = value;}}
public static string GetUserName(int id)
{
INS_DADP_USER curUser = INS_DADP_USER.Get(id);
if(curUser == null)return null;
else
return curUser.User_Name;
}
public static INS_DADP_USER Get(int id)
{
const string sSql = "select ID, User_Name, User_Pwd,Login_Time from INS_DADP_USER 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_USER 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_USER();
if(!sqlReader.IsDBNull(0))
dataObj.Id = sqlReader.GetInt32(0);
if(!sqlReader.IsDBNull(1))
dataObj.User_Name = sqlReader.GetString(1);
if(!sqlReader.IsDBNull(2))
dataObj.User_Pwd = sqlReader.GetString(2);
if(!sqlReader.IsDBNull(3))
dataObj.Login_Time = sqlReader.GetString(3);
objArray.Add(dataObj);
}
sqlReader.Close();
OleDbHelper.PushConnection(connection);
#endregion
INS_DADP_USER[] obJS = new INS_DADP_USER[objArray.Count];
objArray.CopyTo(0,objs,0,obJS.Length);
return objs.Length == 0 ? null:obJS[0];
}
}
//界面調用部分
private void FrmSchemeVIEw_Load(object sender, System.EventArgs e)
{
//加載數據
DataBind();
//加載組件
ComponentBind();
}
protected void DataBind()
{
INS_DADP_SCHEME[] schemes = INS_DADP_SCHEME.List();
dgSchemes.DataSource = DataGridUtil.PrepareDataTable(schemes,new string[]{"方案ID","啟用狀態","方案名稱","方案描述","創建時間","創建用戶","修改時間","修改用戶"});
}
