/// <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);
}
}
/// <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);
}
}
}
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 abstract bool Update(MyRecord record, AbstractWHEREStatement where, ref int effectedRows);
/// <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>
/// <returns>查詢結果。如果有錯誤時，會傳回null</returns>
public T[] SelectFields <T>(string field, bool isDistinct, int topN, AbstractWHEREStatement where)
{
return(SelectFields <T>(field, isDistinct, topN, where,
ConvertFirstColumn <T>));
}
