/// <summary>
/// 查詢資料筆數
/// 只單純查詢筆數，不會將資料取出放到RecordList中。
/// </summary>
/// <param name="where">where條件式</param>
/// <returns>查詢到的資料筆數</returns>
public int SelectCount(AbstractWHEREStatement where)
{
if (CheckDataBase() == false)
{
return(0);
}
this.LastCommand = string.Format("SELECT Count(*) FROM {0} {1}",
TableName,
(where != null && where.ConditionCount > 0 ? "WHERE " + where.ToString(CurrentDataBase) : ""));
DataTable dt = CurrentDataBase.QueryCommand(LastCommand);
if (dt != null)
{
try
{
return(int.Parse(dt.Rows[0][0].ToString()));
}
catch (Exception exp)
{
this.LastErrorMsg = exp.Message;
return(0);
}
}
else
{
return(0);
}
}
public void EditEntry <T>(T SelectedItem)
{
try
{
var original = CurrentDataBase.GetDbSet <T>().Find((SelectedItem as dynamic).Id);
if (original != null)
{
MakeLogEntry(6, original, SelectedItem, "editing " + typeof(T).ToString());
CurrentDataBase.Entry(original).CurrentValues.SetValues(SelectedItem);
CurrentDataBase.SaveChanges();
}
}
catch (Exception e)
{
if (e is Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
{
MessageBox.Show("Editing entry must contain \"Id\" field!");
}
else
{
MessageBox.Show(e.Message);
}
}
}
public override bool Insert(OracleDAOTestRecord record, ref int effectedRows)
{
if (CheckDataBase() == false)
{
return(false);
}
this.LastCommand = "INSERT INTO " + TableName + " (ID,Name,ModifyDate) VALUES ("
+ CurrentDataBase.FieldToSQL(record.ID)
+ "," + CurrentDataBase.FieldToSQL(record.Name)
+ "," + CurrentDataBase.FieldToSQL(record.ModifyDate)
// 2012/3/16 Justin
// 好像不用下commit也可以!!
//+ ");"
//+ (CurrentDataBase.UseTransaction ? " COMMIT;" : "");
+ ")";
try
{
effectedRows = CurrentDataBase.ExecuteCommand(LastCommand);
return(true);
}
catch (Exception exp)
{
LastErrorMsg = exp.Message;
return(false);
}
}
/// <summary>
/// 找出每一个系的最高分，并且按系编号，学生编号升序排列
/// </summary>
/// <returns></returns>
public DataSet QueryStudentSores( )
{
//获取命令信息
CommandInfo cmdInfo = Mapper.GetCommandInfo("QueryStudentSores");
//执行查询
return(CurrentDataBase.ExecuteDataSet(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText, null));
//
} //End Function
}//End Function
/// <summary>
/// 查询所属系的学生信息
/// </summary>
/// <param name="DID"></param>
/// <returns></returns>
public DataSet GetStudent(Int32 DID)
{
//获取命令信息
CommandInfo cmdInfo = Mapper.GetCommandInfo("GetStudent");
//参数赋值，推荐使用该种方式；
cmdInfo.DataParameters[0].Value = DID;
//参数赋值，使用命名方式；
//cmdInfo.SetParameterValue("@DID", DID);
//执行查询
return(CurrentDataBase.ExecuteDataSet(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText, cmdInfo.DataParameters));
//
}//End Function
}//End Function
/// <summary>
/// 查询所属系的学生成绩
/// </summary>
/// <param name="Category"></param>
/// <returns></returns>
public DataSet GetStudentScore(String Category)
{
//获取命令信息
CommandInfo cmdInfo = Mapper.GetCommandInfo("GetStudentScore");
//参数赋值，推荐使用该种方式；
cmdInfo.DataParameters[0].Value = Category;
//参数赋值，使用命名方式；
//cmdInfo.SetParameterValue("@Category", Category);
//执行查询
return(CurrentDataBase.ExecuteDataSet(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText, cmdInfo.DataParameters));
//
}//End Function
}//End Function
/// <summary>
/// 查询学生的成绩，映射结果到DTO列表
/// </summary>
/// <param name="StuId"></param>
/// <returns></returns>
public List <SqlMapDemo.StudentScore> GetStudentScore2(Int32 StuId)
{
//获取命令信息
CommandInfo cmdInfo = Mapper.GetCommandInfo("GetStudentScore2");
//参数赋值，推荐使用该种方式；
cmdInfo.DataParameters[0].Value = StuId;
//参数赋值，使用命名方式；
//cmdInfo.SetParameterValue("@StuId", StuId);
//执行查询
return(MapObjectList <SqlMapDemo.StudentScore>(CurrentDataBase.ExecuteReader(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText, cmdInfo.DataParameters)));
//
}//End Function
/// <summary>
/// 执行sql获取List<T>
/// </summary>
/// <param name="sql"></param>
/// <param name="ps"></param>
/// <returns></returns>
public List <SODUser> GetListBySql(string sql, Dictionary <string, object> ps)
{
IDataParameter[] paras = new IDataParameter[ps.Count];
int i = 0;
foreach (string key in ps.Keys)
{
paras[i++] = CurrentDataBase.GetParameter(key, ps[key]);
}
var result = EntityQuery <SODUser> .QueryList(CurrentDataBase.ExecuteReader(CurrentDataBase.ConnectionString, System.Data.CommandType.Text, sql, paras));
return(result);
}
/// <summary>
/// 初始化DbContextProvider ,如果是SqlServer,Oracle之外的数据库，需要按照约定，提供XXXDbContext
/// <remarks>
/// 约定，根据 CurrentDataBase 所在的程序集，来确定 XXXDbContext 的位置
/// XXXDbContext的名字，XXX总是CurrentDataBase的类型名字，(Name,not full Name)
/// XXXDbContext 可以在不同的命名空间中
/// </remarks>
/// </summary>
private void InitContextProvider()
{
if (CurrentDataBase.CurrentDBMSType == Common.DBMSType.SqlServer)
{
provider = new SqlServerDbContext(CurrentDataBase);
}
else if (CurrentDataBase.CurrentDBMSType == Common.DBMSType.Oracle)
{
provider = new OracleDbContext(CurrentDataBase);
}
else if (CurrentDataBase.CurrentDBMSType == Common.DBMSType.Access)
{
var assembly = System.Reflection.Assembly.Load("PWMIS.Access.Extensions");
string typeName = "PWMIS.AccessExtensions.AccessDbContext";
var obj = assembly.CreateInstance(typeName, false,
System.Reflection.BindingFlags.Default, null, new object[] { CurrentDataBase }, null, null);
provider = obj as IDbContextProvider;
if (provider == null)
{
throw new Exception("类型 " + typeName + " 不是IDbContextProvider 的实例类型");
}
}
else
{
//约定，根据 CurrentDataBase 所在的程序集，来确定 XXXDbContext 的位置
//XXXDbContext的名字，XXX总是CurrentDataBase的类型名字，(Name,not full Name)
//XXXDbContext 可以在不同的命名空间中
var assembly = System.Reflection.Assembly.GetAssembly(CurrentDataBase.GetType());
string typeName = CurrentDataBase.GetType().Name + "DbContext";
foreach (Type t in assembly.GetTypes())
{
if (t.Name == typeName)
{
var obj = Activator.CreateInstance(t, CurrentDataBase);
provider = obj as IDbContextProvider;
if (provider == null)
{
throw new Exception("类型 " + typeName + " 不是IDbContextProvider 的实例类型");
}
break;
}
}
if (provider == null)
{
throw new Exception("未能在程序集 " + assembly.FullName + " 中找到约定的DbContext 类型： " + typeName);
}
}
}
}//End Function
/// <summary>
/// 增加学生
/// </summary>
/// <param name="Name"></param>
/// <param name="DeptId"></param>
/// <returns></returns>
public Int32 AddStudent(String Name, Int32 DeptId)
{
//获取命令信息
CommandInfo cmdInfo = Mapper.GetCommandInfo("InsertStudent");
//参数赋值，推荐使用该种方式；
cmdInfo.DataParameters[0].Value = Name;
cmdInfo.DataParameters[1].Value = DeptId;
//参数赋值，使用命名方式；
//cmdInfo.SetParameterValue("@Name", Name);
//cmdInfo.SetParameterValue("@DeptId", DeptId);
//执行查询
return(CurrentDataBase.ExecuteNonQuery(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText, cmdInfo.DataParameters));
//
} //End Function
/// <summary>
/// 以yield return的方式，每完成一筆MyRecord就傳回，不用等到全就產生好再傳回
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public IEnumerable <MyRecord> SelectIterator(AbstractWHEREStatement where)
{
LastErrorMsg = "";
RecordList.Clear();
if (CheckDataBase() == false)
{
yield break;
}
// 因為Oracle跟MSSQL在實作TOPN的方式不同，只好再改由底層的IDataBase來組指令了
this.LastCommand = CurrentDataBase.MakeSELECT("*", false, 0,
(where != null ? where.ToString(CurrentDataBase) : ""), TableName);
// 因為yield不能被包在try/catch中，只好…折開來寫
DataTable dt = null;
try
{
dt = CurrentDataBase.QueryCommand(LastCommand);
}
catch (Exception exp)
{
this.LastErrorMsg = exp.Message;
yield break;
}
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
MyRecord record = default(MyRecord);
try
{
// 衍生類別只要實作ConvertRecord()函式即可
record = ConvertRecord(row);
}
catch (Exception exp)
{
this.LastErrorMsg = exp.Message;
yield break;
}
yield return(record);
}
}
}
/// <summary>
/// 检查实体类对应的数据表是否在数据库中存在
/// </summary>
protected override void CheckTableExists <T>()
{
//创建表
if (CurrentDataBase.CurrentDBMSType == PWMIS.Common.DBMSType.SqlServer)
{
var entity = new T();
var dsScheme = CurrentDataBase.GetSchema("Tables", new string[] { null, null, null, "BASE TABLE" });
var rows = dsScheme.Select("table_name='" + entity.GetTableName() + "'");
if (rows.Length == 0)
{
EntityCommand ecmd = new EntityCommand(entity, CurrentDataBase);
string sql = ecmd.CreateTableCommand;
CurrentDataBase.ExecuteNonQuery(sql);
}
}
}
public void AddEntry <T>(T SelectedItem)
{
try
{
if (typeof(T) != typeof(Models.Log))
{
MakeLogEntry(5, null, SelectedItem, "adding " + typeof(T).ToString());
}
CurrentDataBase.GetDbSet <T>().Add(SelectedItem);
CurrentDataBase.SaveChanges();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
public virtual bool Delete(AbstractWHEREStatement where, ref int effectedRows)
{
effectedRows = 0;
if (CheckDataBase() == false)
{
return(false);
}
this.LastErrorMsg = "";
this.LastCommand = "DELETE FROM " + TableName +
(where != null && where.ConditionCount > 0 ? where.ToString(CurrentDataBase) : "")
+ (CurrentDataBase.UseTransaction ? "; commit;" : "");
effectedRows = CurrentDataBase.ExecuteCommand(this.LastCommand);
return(true);
}
/// <summary>
/// 查詢單一欄位的結果
/// 可指定是否要用DISTINCT、top N
/// </summary>
/// <typeparam name="T">要取出的欄位資料型別</typeparam>
/// <param name="field">欄位名稱</param>
/// <param name="isDistinct">是否要DISTINCT</param>
/// <param name="where">where條件式</param>
/// <param name="topN">是否取前N筆</param>
/// <param name="DataConvertFunc">資料轉換函式，如果將查詢出來的DataRow轉換成T型別</param>
/// <returns>查詢結果。如果有錯誤時，會傳回null</returns>
public T[] SelectFields <T>(string field, bool isDistinct, int topN, AbstractWHEREStatement where, Func <DataRow, T> DataConvertFunc)
{
if (CheckDataBase() == false)
{
return(new T[0]);
}
string strWhere = "";
if (where != null && where.ConditionCount > 0)
{
strWhere = where.ToString(CurrentDataBase);
}
// 因為Oracle跟MSSQL在實作TOPN的方式不同，只好再改由底層的IDataBase來組指令了
this.LastCommand = CurrentDataBase.MakeSELECT(field, isDistinct, topN, strWhere, TableName);
try
{
DataTable dt = CurrentDataBase.QueryCommand(LastCommand);
if (dt != null && dt.Rows.Count > 0)
{
T[] result = new T[dt.Rows.Count];
int index = 0;
foreach (DataRow row in dt.Rows)
{
//result[index++] = (T)row[0];
result[index++] = DataConvertFunc(row);
}
return(result);
}
else
{
return(null);
}
}
catch (Exception exp)
{
this.LastErrorMsg = exp.Message;
return(null);
}
}
public virtual int Select(AbstractWHEREStatement where)
{
LastErrorMsg = "";
RecordList.Clear();
if (CurrentDataBase == null)
{
LastErrorMsg = "沒有指定要使用的IDataBase";
return(0);
}
// 因為Oracle跟MSSQL在實作TOPN的方式不同，只好再改由底層的IDataBase來組指令了
this.LastCommand = CurrentDataBase.MakeSELECT("*", false, 0,
(where != null ? where.ToString(CurrentDataBase) : ""), TableName);
try
{
DataTable dt = CurrentDataBase.QueryCommand(LastCommand);
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
// 衍生類別只要實作ConvertRecord()函式即可
MyRecord record = ConvertRecord(row);
RecordList.Add(record);
}
}
}
catch (Exception exp)
{
this.LastErrorMsg = exp.Message;
}
return(RecordList.Count);
}
public override bool Update(OracleDAOTestRecord record, AbstractWHEREStatement where, ref int effectedRows)
{
if (CheckDataBase() == false)
{
return(false);
}
this.LastCommand = "UPDATE " + TableName + " SET "
+ "ID=" + CurrentDataBase.FieldToSQL(record.ID)
+ ",Name=" + CurrentDataBase.FieldToSQL(record.Name)
+ ",ModifyDate=" + CurrentDataBase.FieldToSQL(record.ModifyDate)
+ (where != null && where.ConditionCount > 0 ? " WHERE " + where.ToString(CurrentDataBase) : "");
try
{
effectedRows = CurrentDataBase.ExecuteCommand(LastCommand);
return(true);
}
catch (Exception exp)
{
LastErrorMsg = exp.Message;
return(false);
}
}
public void SetDataBase(CurrentDataBase Value)
{
DataBase = Value;
}
public MessageBoxResult DeleteEntry <T>(T SelectedItem)
{
if (SelectedItem == null)
{
return(MessageBoxResult.No);
}
try
{
System.Type type = typeof(T);
string name_of_deleting_entry = (SelectedItem as dynamic).Name;
if (type == typeof(Models.RouteScheduler))
{
DataConvertors.DayOfWeekConvertor dayOfWeekConvertor = new DataConvertors.DayOfWeekConvertor();
DateTime epoch = new DateTime(1970, 1, 1);
string time_scheduler = epoch.AddSeconds(System.Convert.ToDouble((SelectedItem as Models.RouteScheduler).StartTime)).ToLongTimeString();
name_of_deleting_entry = (string)dayOfWeekConvertor.Convert(
(SelectedItem as Models.RouteScheduler).DayOfWeek, null, null, null) + " " + time_scheduler;
}
var original = CurrentDataBase.GetDbSet <T>().Find((SelectedItem as dynamic).Id);
if (original != null)
{
MessageBoxResult answer = MessageBox.Show(@"Вы действительно хотите удалить """ + name_of_deleting_entry + @"""?", "Удаление", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (answer == MessageBoxResult.Yes)
{
try
{
original.DeletingDate = (Int32)(DateTime.Now.Subtract(new DateTime(1970, 1, 1))).TotalSeconds;
original.IsDeleted = 1;
MakeLogEntry(7, original, SelectedItem, "deleting " + typeof(T).ToString());
CurrentDataBase.Entry(original).CurrentValues.SetValues(original);
CurrentDataBase.SaveChanges();
CurrentDataBase.Entry(original).State = EntityState.Detached;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(MessageBoxResult.No);
}
}
else
{
return(MessageBoxResult.No);
}
}
}
catch (Exception e)
{
if (e is Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
{
MessageBox.Show("Deleting entry must contain \"Id\", \"DeletingDate\", \"IsDeleted\", \"Name\" fields!");
}
else
{
MessageBox.Show(e.Message);
}
return(MessageBoxResult.No);
}
return(MessageBoxResult.Yes);
}
