/// <summary>
/// 設定命令參數的資料型別。
/// </summary>
/// <param name="parameter">命令參數。</param>
/// <param name="dbType">欄位資料型別。</param>
public override void SetParameterDbType(DbParameter parameter, EFieldDbType dbType)
{
SqlParameter oParameter;
oParameter = parameter as SqlParameter;
oParameter.SqlDbType = DatabaseFunc.ToSqlDbType(dbType);
}
/// <summary>
/// 建立 Delete 語法的資料庫命令。
/// </summary>
/// <param name="tableName">資料表名稱。</param>
public virtual DbCommand BuildDeleteCommand(string tableName)
{
IDbCommandHelper oHelper;
StringBuilder oBuffer;
GTableDefine oTableDefine;
GFieldDefine oKeyFieldDefine;
string sKeyField, sParameterSymbol;
string[] oKeyFields;
// 無資料表定義或不啟用資料庫命令，則傳回 Null
oTableDefine = this.GeGTableDefine(tableName);
if (BaseFunc.IsNull(oTableDefine))
{
return(null);
}
// 建立資料庫命令輔助類別
oHelper = DatabaseFunc.CreateDbCommandHelper(this.DatabaseType);
sParameterSymbol = oHelper.ParameterSymbol;
oBuffer = new StringBuilder();
oBuffer.AppendFormat("DELETE FROM {0} WITH ( ROWLOCK ) ", oTableDefine.DbTableName);
oBuffer.AppendLine("WHERE 1=1");
if (StrFunc.StrIsEmpty(oTableDefine.PrimaryKey))
{
// 主檔以 Sys_ID 為更新主鍵，明細檔以 Sys_RowID 為更新主鍵，其他則以 PrimaryKey 為更新主鍵
if (oTableDefine.Fields.Contains(SysFields.ID))
{
sKeyField = SysFields.ID;
}
else if (oTableDefine.Fields.Contains(SysFields.RowID))
{
sKeyField = SysFields.RowID;
}
else
{
throw new GException("{0} TableDefine 未包含 Sys_ID 或 Sys_RowID 欄位", tableName);
}
oKeyFieldDefine = oTableDefine.Fields[sKeyField];
oBuffer.AppendLine(StrFunc.StrFormat("AND {1}={0}{2}", sParameterSymbol, oKeyFieldDefine.DbFieldName, oKeyFieldDefine.FieldName));
oHelper.AddParameter(oKeyFieldDefine);
}
else
{
// 以自訂的 PrimaryKey 為準
oKeyFields = StrFunc.StrSplit(oTableDefine.PrimaryKey, ",");
foreach (string sFieldName in oKeyFields)
{
oKeyFieldDefine = oTableDefine.Fields[sFieldName];
oBuffer.AppendLine(StrFunc.StrFormat("AND {1}={0}{2}", sParameterSymbol, oKeyFieldDefine.DbFieldName, oKeyFieldDefine.FieldName));
oHelper.AddParameter(oKeyFieldDefine);
}
}
oHelper.SetCommandText(oBuffer.ToString());
return(oHelper.DbCommand);;
}
/// <summary>
/// 設定命令參數的資料型別。
/// </summary>
/// <param name="parameter">命令參數。</param>
/// <param name="dbType">欄位資料型別。</param>
public override void SetParameterDbType(DbParameter parameter, EFieldDbType dbType)
{
OracleParameter oParameter;
oParameter = parameter as OracleParameter;
oParameter.OracleType = DatabaseFunc.ToOracleType(dbType);
}
/// <summary>
/// 建立 Insert 語法的資料庫命令。
/// </summary>
/// <param name="tableName">資料表名稱。</param>
public virtual DbCommand BuildInsertCommand(string tableName)
{
IDbCommandHelper oHelper;
StringBuilder oBuffer;
GTableDefine oTableDefine;
string sSeparator;
// 無資料表定義或不啟用資料庫命令，則傳回 Null
oTableDefine = this.GeGTableDefine(tableName);
if (BaseFunc.IsNull(oTableDefine))
{
return(null);
}
// 建立資料庫命令輔助類別
oHelper = DatabaseFunc.CreateDbCommandHelper(this.DatabaseType);
oBuffer = new StringBuilder();
oBuffer.AppendFormat("INSERT INTO {0} ", oHelper.GetTableName(oTableDefine.DbTableName));
oBuffer.AppendLine();
// 處理 Insert 的欄位名稱
oBuffer.Append("(");
sSeparator = string.Empty;
foreach (GFieldDefine fieldDefine in oTableDefine.Fields)
{
//FieldType 為資料欄位(DataField)才做欄位處理
if (fieldDefine.FieldType == EFieldType.DataField)
{
oBuffer.Append(sSeparator + oHelper.GetFieldName(fieldDefine.DbFieldName));
sSeparator = ",";
}
}
oBuffer.AppendLine(")");
// 處理 Insert 的欄位值
oBuffer.AppendLine("VALUES");
oBuffer.Append("(");
sSeparator = string.Empty;
foreach (GFieldDefine fieldDefine in oTableDefine.Fields)
{
//FieldType 為資料欄位(DataField)才做欄位處理
if (fieldDefine.FieldType == EFieldType.DataField)
{
oBuffer.Append(sSeparator + oHelper.GetParameterName(fieldDefine.FieldName));
sSeparator = ",";
oHelper.AddParameter(fieldDefine); // 加入命令參數
}
}
oBuffer.AppendLine(")");
oHelper.SetCommandText(oBuffer.ToString());
return(oHelper.DbCommand);;
}
/// <summary>
/// 建立符合過濾條件的 Delete 語法的資料庫命令。
/// </summary>
/// <param name="tableName">資料表名稱。</param>
/// <param name="filterItems">資料過濾條件項目集合. </param>
public virtual DbCommand BuildDeleteCommand(string tableName, GFilterItemCollection filterItems)
{
IDbCommandHelper oHelper;
StringBuilder oBuffer;
GTableDefine oTableDefine;
GFilterBuilder oFilterBuilder;
GFilterInputArgs oFilterInputArgs;
string sFilter;
string sJoin;
// 無資料表定義或不啟用資料庫命令，則傳回 Null
oTableDefine = this.GeGTableDefine(tableName);
if (BaseFunc.IsNull(oTableDefine))
{
return(null);
}
// 建立資料庫命令輔助類別
oHelper = DatabaseFunc.CreateDbCommandHelper(this.DatabaseType);
oBuffer = new StringBuilder();
if (this.SessionInfo.DatabaseType == EDatabaseType.Oracle)
{
oBuffer.AppendLine(StrFunc.StrFormat("DELETE FROM {0} A ", oTableDefine.DbTableName));
}
else
{
oBuffer.AppendLine(StrFunc.StrFormat("DELETE FROM {0} FROM {0} A WITH ( ROWLOCK ) ", oTableDefine.DbTableName));
}
// 處理過濾條件
if (!BaseFunc.IsEmpty(filterItems))
{
oFilterInputArgs = new GFilterInputArgs(oHelper, this.ProgramDefine, tableName, null, filterItems, this.SessionInfo, true);
oFilterBuilder = new GFilterBuilder(oFilterInputArgs);
sFilter = oFilterBuilder.GetFilter(out sJoin);
if (StrFunc.StrIsNotEmpty(sFilter))
{
oBuffer.AppendLine("Where " + sFilter);
}
}
oHelper.SetCommandText(oBuffer.ToString());
return(oHelper.DbCommand);
}
/// <summary>
/// 建立 Select 語法的資料庫命令。
/// </summary>
/// <param name="tableName">資料表名稱。</param>
/// <param name="selectFields">要取得的欄位集合字串，以逗點分隔欄位名稱，空字串表示取得所有欄位。</param>
/// <param name="filterItems">資料過濾條件項目集合。</param>
/// <param name="userFilter">自訂過濾條件。</param>
/// <param name="isOrderBy">執行排序</param>
public virtual DbCommand BuildSelectCommand(string tableName, string selectFields,
GFilterItemCollection filterItems, string userFilter, bool isOrderBy = true)
{
IDbCommandHelper oHelper;
GTableJoinProvider oTableJoinProvider;
StringBuilder oBuffer;
StringBuilder oFilterBuffer;
IFilterBuilder oFilterBuilder;
GTableDefine oTableDefine;
GSortFieldCollection oSortFields;
GFilterInputArgs oFilterInputArgs;
string sCommandText;
string sFilter;
string sSort;
string sJoin;
// 無資料表定義則離開
oTableDefine = this.GeGTableDefine(tableName);
if (BaseFunc.IsNull(oTableDefine))
{
return(null);
}
// 建立資料庫命令輔助類別
oHelper = DatabaseFunc.CreateDbCommandHelper(this.DatabaseType);
// 取得排序欄位集合
oSortFields = GetSortFields(oTableDefine);
// 建置資料表關連資訊
oTableJoinProvider = new GTableJoinProvider();
oTableJoinProvider.Execute(this.ProgramDefine, tableName, selectFields, filterItems, oSortFields);
oBuffer = new StringBuilder();
// 產生 SELECT 欄位的語法
sCommandText = GetSelectFieldsCommandText(oHelper, oTableJoinProvider, tableName, selectFields);
oBuffer.AppendLine(sCommandText);
oFilterBuffer = new StringBuilder();
// 查詢過濾條件
oFilterInputArgs = new GFilterInputArgs(oHelper, this.ProgramDefine, tableName, oTableJoinProvider, filterItems, this.SessionInfo, true);
oFilterBuilder = new GFilterBuilder(oFilterInputArgs);
sFilter = oFilterBuilder.GetFilter(out sJoin);
AddFilter(oFilterBuffer, sFilter);
// 自訂過濾條件
AddFilter(oFilterBuffer, userFilter);
// 產生 FROM 及 JOIN 語法
sCommandText = GeGTableJoinCommandText(oHelper, oTableJoinProvider, oTableDefine);
oBuffer.Append(sCommandText);
if (StrFunc.StrIsNotEmpty(sJoin))
{
oBuffer.Append(sJoin);
}
// 加入過濾條件
if (oFilterBuffer.Length > 0)
{
oBuffer.AppendLine(" Where ");
oBuffer.AppendLine(oFilterBuffer.ToString());
}
// 加入排序語法
if (isOrderBy)
{
sSort = GetSortCommandText(oHelper, oTableJoinProvider, oTableDefine, oSortFields);
if (StrFunc.StrIsNotEmpty(sSort))
{
oBuffer.Append(sSort);
}
}
oHelper.SetCommandText(oBuffer.ToString());
return(oHelper.DbCommand);
}
/// <summary>
/// 建立 Update 語法的資料庫命令。
/// </summary>
/// <param name="tableName">資料表名稱。</param>
public virtual DbCommand BuildUpdateCommand(string tableName)
{
IDbCommandHelper oHelper;
StringBuilder oBuffer;
GTableDefine oTableDefine;
GFieldDefine oKeyFieldDefine;
DbParameter oParameter;
string sSeparator, sFormat;
string[] oKeyFields;
// 無資料表定義或不啟用資料庫命令，則傳回 Null
oTableDefine = this.GeGTableDefine(tableName);
if (BaseFunc.IsNull(oTableDefine))
{
return(null);
}
// 建立資料庫命令輔助類別
oHelper = DatabaseFunc.CreateDbCommandHelper(this.DatabaseType);
oBuffer = new StringBuilder();
oBuffer.AppendFormat("UPDATE {0} WITH ( ROWLOCK ) ", oHelper.GetTableName(oTableDefine.DbTableName));
oBuffer.AppendLine();
// 處理 Update 的欄位名稱與值
sSeparator = string.Empty;
oBuffer.AppendLine("SET");
foreach (GFieldDefine fieldDefine in oTableDefine.Fields)
{
// FieldType 為 DataField 才需做更新，但排除 SYS_CompanyID、SYS_ID、SYS_RowID、SYS_INSDAT、SYS_USR
if (fieldDefine.FieldType == EFieldType.DataField && !StrFunc.SameTextOr(fieldDefine.FieldName, SysFields.CompanyID, SysFields.ID, SysFields.RowID, SysFields.InsertDate, SysFields.InsertUser))
{
// 加入命令參數
oParameter = oHelper.AddParameter(fieldDefine);
sFormat = "{0}{1}={2}";
oBuffer.AppendFormat(sFormat, sSeparator, oHelper.GetFieldName(fieldDefine.DbFieldName), oParameter.ParameterName);
oBuffer.AppendLine();
sSeparator = ", ";
}
}
// 更新主鍵優先順序為 SYS_RowID、SYS_CompanyID+SYS_ID，CompanyID+SYS_ID、PrimaryKey
if (oTableDefine.Fields.Contains(SysFields.RowID))
{
oKeyFields = new string[] { SysFields.RowID }
}
;
else if (oTableDefine.Fields.Contains(SysFields.CompanyID) && oTableDefine.Fields.Contains(SysFields.ID))
{
oKeyFields = new string[] { SysFields.CompanyID, SysFields.ID }
}
;
else if (oTableDefine.Fields.Contains(SysFields.CommonCompanyID) && oTableDefine.Fields.Contains(SysFields.ID))
{
oKeyFields = new string[] { SysFields.CommonCompanyID, SysFields.ID }
}
;
else if (StrFunc.StrIsNotEmpty(oTableDefine.PrimaryKey))
{
oKeyFields = StrFunc.StrSplit(oTableDefine.PrimaryKey, ",");
}
else
{
throw new GException("{0} 未包含 Sys_ID 或 Sys_RowID 欄位，無法建立 Update Command", tableName);
}
// 產生更新的 Where 條件
oBuffer.AppendLine("WHERE 1=1");
foreach (string sFieldName in oKeyFields)
{
oKeyFieldDefine = oTableDefine.Fields[sFieldName];
oParameter = oHelper.AddOriginalParameter(oKeyFieldDefine);
sFormat = "AND {0}={1}";
oBuffer.AppendLine(StrFunc.StrFormat(sFormat, oHelper.GetFieldName(oKeyFieldDefine.DbFieldName), oParameter.ParameterName));
}
oHelper.SetCommandText(oBuffer.ToString());
return(oHelper.DbCommand);;
}
/// <summary>
/// 取得作用的資料表別名。
/// </summary>
private string GetActiveTableAlias()
{
this.ActiveTableAlias = DatabaseFunc.GetNextTableAlias(this.ActiveTableAlias);
return(this.ActiveTableAlias);
}
