private string ToDbType(EFieldDbType dbType, int maxLength)
{
switch (dbType)
{
case EFieldDbType.Boolean:
return("[bit]");
case EFieldDbType.GUID:
return("[uniqueidentifier]");
case EFieldDbType.Currency:
return("[money]");
case EFieldDbType.Double:
return("[float]");
case EFieldDbType.DateTime:
return("[datetime]");
case EFieldDbType.Integer:
return("[int]");
case EFieldDbType.String:
return($"[nvarchar]({maxLength})");
case EFieldDbType.Text:
case EFieldDbType.Binary:
default:
return("[nvarchar](MAX)");
}
}
/// <summary>
/// 建立欄位並加入資料表中。
/// </summary>
/// <param name="fieldName">欄位名稱。</param>
/// <param name="caption">欄位標題。</param>
/// <param name="fieldDbType">欄位資料型別。</param>
/// <param name="defaultValue">預設值。</param>
public DataColumn AddColumn(string fieldName, string caption, EFieldDbType fieldDbType, object defaultValue)
{
Type oDataType;
oDataType = DbTypeConverter.ToType(fieldDbType);
return(AddColumn(fieldName, caption, oDataType, defaultValue));
}
/// <summary>
/// 依欄位資料型別取得預設值(字串預設為空字串、數值預設為 0，布林值預設為 false，其他為 DBNull.Value)。
/// </summary>
/// <param name="dbType">欄位資料型別。</param>
public static object GetDefaultValue(EFieldDbType dbType)
{
switch (dbType)
{
case EFieldDbType.String:
case EFieldDbType.Text:
return(string.Empty);
case EFieldDbType.Boolean:
return(false);
case EFieldDbType.Integer:
case EFieldDbType.Double:
case EFieldDbType.Currency:
return(0);
case EFieldDbType.DateTime:
return(DateTime.Today);
case EFieldDbType.GUID:
return(Guid.Empty);
default:
return(DBNull.Value);
}
}
/// <summary>
/// 將 EFieldDbType 轉型為 DbType 型別。
/// </summary>
/// <param name="fieldDbType">欄位資料型別。</param>
public static DbType ToDbType(EFieldDbType fieldDbType)
{
switch (fieldDbType)
{
case EFieldDbType.String:
return(DbType.String);
case EFieldDbType.Text:
return(DbType.String);
case EFieldDbType.Boolean:
return(DbType.Boolean);
case EFieldDbType.Integer:
return(DbType.Int32);
case EFieldDbType.Double:
return(DbType.Double);
case EFieldDbType.Currency:
return(DbType.Currency);
case EFieldDbType.DateTime:
return(DbType.DateTime);
case EFieldDbType.GUID:
return(DbType.Guid);
case EFieldDbType.Binary:
return(DbType.Binary);
default:
throw new GException("{0} can't convert to DbType", fieldDbType);
}
}
/// <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>
/// 將 EFieldDbType 轉型為 System.Type。
/// </summary>
/// <param name="fieldDbType">欄位資料型別。</param>
public static Type ToType(EFieldDbType fieldDbType)
{
switch (fieldDbType)
{
case EFieldDbType.String:
return(typeof(string));
case EFieldDbType.Text:
return(typeof(string));
case EFieldDbType.Boolean:
return(typeof(bool));
case EFieldDbType.Integer:
return(typeof(int));
case EFieldDbType.Double:
return(typeof(double));
case EFieldDbType.Currency:
return(typeof(decimal));
case EFieldDbType.DateTime:
return(typeof(DateTime));
case EFieldDbType.GUID:
return(typeof(Guid));
case EFieldDbType.Binary:
return(typeof(byte[]));
default:
throw new GException("{0} can't convert to System.Type", fieldDbType);
}
}
/// <summary>
/// 轉換 FieldDbType 為 SqlDbType。
/// </summary>
/// <param name="fieldDbType">FieldType列舉。</param>
public static SqlDbType ToSqlDbType(EFieldDbType fieldDbType)
{
switch (fieldDbType)
{
case EFieldDbType.String: return(SqlDbType.NVarChar);
case EFieldDbType.Text: return(SqlDbType.NVarChar);
case EFieldDbType.Boolean: return(SqlDbType.Bit);
case EFieldDbType.Integer: return(SqlDbType.Int);
case EFieldDbType.Currency: return(SqlDbType.Money);
case EFieldDbType.Double: return(SqlDbType.Float);
case EFieldDbType.DateTime: return(SqlDbType.DateTime);
case EFieldDbType.GUID: return(SqlDbType.UniqueIdentifier);
case EFieldDbType.Binary: return(SqlDbType.Binary);
default:
throw new GException("{0} can't convert to SqlDbType", fieldDbType.ToString());
}
}
/// <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>
/// 新增命令參數。
/// </summary>
/// <param name="name">參數名稱。</param>
/// <param name="dbType">資料型別。</param>
/// <param name="value">參數值。</param>
/// <param name="direction">設定參數是否為只能輸入、只能輸出、雙向 (Bidirectional) 或預存程序 (Stored Procedure) 傳回值參數。</param>
public DbParameter AddParameter(string name, EFieldDbType dbType, object value, ParameterDirection direction)
{
DbParameter oParameter;
oParameter = this.AddParameter(name, dbType, value);
oParameter.Direction = direction;
return(oParameter);
}
/// <summary>
/// 建立欄位並加入資料表。
/// </summary>
/// <param name="fieldName">欄位名稱。</param>
/// <param name="fieldDbType">欄位資料型別。</param>
public DataColumn AddColumn(string fieldName, EFieldDbType fieldDbType)
{
Type oDataType;
oDataType = DbTypeConverter.ToType(fieldDbType);
object oDefaultValue = DataFunc.GetDefaultValue(oDataType);
return(this.AddColumn(fieldName, oDataType, oDefaultValue));
}
/// <summary>
/// 新增命令參數。
/// </summary>
/// <param name="name">參數名稱。</param>
/// <param name="dbType">資料型別。</param>
/// <param name="value">參數值。</param>
/// <param name="size">資料最大長度。</param>
public DbParameter AddParameter(string name, EFieldDbType dbType, object value, int size)
{
DbParameter oParameter;
oParameter = this.AddParameter(name, dbType);
oParameter.Value = GetParameterValue(dbType, value);
oParameter.Size = size;
return(oParameter);
}
/// <summary>
/// 取得轉換後的參數值。
/// </summary>
/// <param name="dbType">欄位資料型別。</param>
/// <param name="value">參數值。</param>
public override object GetParameterValue(EFieldDbType dbType, object value)
{
if (dbType == EFieldDbType.GUID)
{
return(value.ToString());
}
else
{
return(base.GetParameterValue(dbType, value));
}
}
/// <summary>
/// 將傳入值轉型為指定欄位資料型別。
/// </summary>
/// <param name="fieldDbType">欄位資料型別。</param>
/// <param name="value">傳入值。</param>
/// <param name="defaultValue">無法正確轉型的預設值。</param>
public static object CFieldValue(EFieldDbType fieldDbType, object value, object defaultValue)
{
try
{
return(CFieldValue(fieldDbType, value));
}
catch
{
return(defaultValue);
}
}
/// <summary>
/// 新增命令參數。
/// </summary>
/// <param name="name">參數名稱。</param>
/// <param name="dbType">資料型別。</param>
public DbParameter AddParameter(string name, EFieldDbType dbType)
{
DbParameter oParameter;
oParameter = CreateDbParameter();
oParameter.ParameterName = GetParameterName(name);
// 設定命令參數的資料型別
this.SetParameterDbType(oParameter, dbType);
this.DbCommand.Parameters.Add(oParameter);
return(oParameter);
}
/// <summary>
/// 取得轉換後的參數值。
/// </summary>
/// <param name="dbType">欄位資料型別。</param>
/// <param name="value">參數值。</param>
public virtual object GetParameterValue(EFieldDbType dbType, object value)
{
if (dbType == EFieldDbType.DateTime)
{
if (BaseFunc.IsEmpty(BaseFunc.CDateTime(value)))
{
return(DBNull.Value);
}
else
{
return(value);
}
}
else
{
return(value);
}
}
/// <summary>
/// 取得篩選值。
/// </summary>
/// <param name="dbType">欄位資料型別。</param>
/// <param name="value">輸入值。</param>
private string GetFilterValue(EFieldDbType dbType, object value)
{
if (dbType == EFieldDbType.DateTime)
{
if (BaseFunc.IsNullOrDBNull(value))
{
return(string.Empty);
}
else
{
return(BaseFunc.CDateTime(value).ToString("yyyy/MM/dd"));
}
}
else
{
return(BaseFunc.CStr(value));
}
}
/// <summary>
/// 依欄位資料型別設定欄位值
/// </summary>
/// <param name="dbType"></param>
/// <param name="fieldValue"></param>
/// <returns></returns>
public static object SetFieldValue(EFieldDbType dbType, object fieldValue)
{
object result = null;
switch (dbType)
{
case EFieldDbType.String:
case EFieldDbType.Text:
result = BaseFunc.CStr(fieldValue);
break;
case EFieldDbType.Boolean:
result = BaseFunc.CBool(fieldValue);
break;
case EFieldDbType.Integer:
result = BaseFunc.CInt(fieldValue);
break;
case EFieldDbType.Double:
result = BaseFunc.CDouble(fieldValue);
break;
case EFieldDbType.Currency:
result = BaseFunc.CDecimal(fieldValue);
break;
case EFieldDbType.DateTime:
result = BaseFunc.CDateTime(fieldValue);
break;
case EFieldDbType.GUID:
result = Guid.Parse(fieldValue.ToString());
break;
default:
result = fieldValue;
break;
}
return(result);
}
/// <summary>
/// 將傳入值轉型為指定欄位資料型別。
/// </summary>
/// <param name="fieldDbType">欄位資料型別。</param>
/// <param name="value">傳入值。</param>
public static object CFieldValue(EFieldDbType fieldDbType, object value)
{
switch (fieldDbType)
{
case EFieldDbType.String:
return(BaseFunc.CStr(value).Trim()); // 去除左右空白
case EFieldDbType.Text:
return(BaseFunc.CStr(value).Trim()); // 去除左右空白
case EFieldDbType.Boolean:
return(BaseFunc.CBool(value));
case EFieldDbType.Integer:
return(BaseFunc.CInt(value));
case EFieldDbType.Double:
return(BaseFunc.CDouble(value));
case EFieldDbType.Currency:
return(BaseFunc.CDecimal(value));
case EFieldDbType.DateTime:
if (BaseFunc.IsDBNull(value))
{
return(value);
}
else
{
return(BaseFunc.CDateTime(value));
}
case EFieldDbType.GUID:
return(BaseFunc.CGuid(value));
default:
return(value);
}
}
/// <summary>
/// 轉換 FieldDbType 為 OracleTypem。
/// </summary>
/// <param name="fieldDbType">FieldType列舉。</param>
public static OracleType ToOracleType(EFieldDbType fieldDbType)
{
switch (fieldDbType)
{
case EFieldDbType.String: return(OracleType.NChar);
case EFieldDbType.Text: return(OracleType.NVarChar);
case EFieldDbType.Boolean: return(OracleType.Char);
case EFieldDbType.Integer: return(OracleType.Int32);
case EFieldDbType.Double: return(OracleType.Double);
case EFieldDbType.DateTime: return(OracleType.DateTime);
case EFieldDbType.Currency: return(OracleType.Number);
case EFieldDbType.GUID: return(OracleType.NVarChar);
default:
throw new GException("{0} can't convert to OracleType", fieldDbType.ToString());
}
}
/// <summary>
/// 新增命令參數。
/// </summary>
/// <param name="name">參數名稱。</param>
/// <param name="dbType">資料型別。</param>
/// <param name="value">參數值。</param>
public DbParameter AddParameter(string name, EFieldDbType dbType, object value)
{
return(AddParameter(name, dbType, value, 0));
}
/// <summary>
/// 設定命令參數的資料型別。
/// </summary>
/// <param name="parameter">命令參數。</param>
/// <param name="dbType">欄位資料型別。</param>
public virtual void SetParameterDbType(DbParameter parameter, EFieldDbType dbType)
{
parameter.DbType = DbTypeConverter.ToDbType(dbType);
}
