/// <summary>
/// Sql数据库兼容和Sql注入处理
/// </summary>
public static string Compatible(object where, DataBaseType dalType, bool isFilterInjection)
{
string text = GetIFieldSql(where);
if (isFilterInjection)
{
text = SqlInjection.Filter(text, dalType);
}
text = SqlCompatible.Format(text, dalType);
return(RemoveWhereOneEqualsOne(text));
}
/// <summary>
/// 返回不包括Where条件的字符串
/// </summary>
/// <returns>结果如:Update tableName set Name=@Name,Value=@Value</returns>
internal string GetUpdateSql(object whereObj)
{
isCanDo = false;
StringBuilder _TempSql = new StringBuilder();
_TempSql.Append("Update " + TableName + " set ");
if (!string.IsNullOrEmpty(updateExpression))
{
_TempSql.Append(SqlCompatible.Format(updateExpression, _action.DalType) + ",");
updateExpression = null;//取完值后清除值。
isCanDo = true;
}
string editTime = GetEditTimeSql();//内部判断该字段没有值才会更新。
if (!string.IsNullOrEmpty(editTime))
{
_TempSql.Append(editTime);//自带尾,号
}
MDataCell cell = null;
for (int i = 0; i < _action.Data.Count; i++)
{
cell = _action.Data[i];
if (cell.Struct.IsPrimaryKey || cell.Struct.IsAutoIncrement)
{
continue;//跳过自增或主键列。
}
if (cell.cellValue.State > 1 && (cell.Struct.IsCanNull || !cell.IsNull))
{
if (cell.Struct.SqlType == SqlDbType.Timestamp && (_action.DalType == DalType.MsSql || _action.DalType == DalType.Sybase))
{
//更新时间戳不允许更新。
continue;
}
object value = cell.Value;
DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DalType);
if (_action.DalType == DalType.Oracle && dbType == DbType.String && cell.strValue == "" && !cell.Struct.IsCanNull)
{
value = " ";//Oracle not null 字段，不允许设置空值。
}
_action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DalType) + "=" + _action.dalHelper.Pre + cell.ColumnName + ",");
isCanDo = true;
}
}
if (!isCanDo)
{
_action.dalHelper.debugInfo.Append(AppConst.HR + "Tip : Can not find the data can be updated!");
}
//switch (_action.dalHelper.dalType)
//{
// case DalType.Oracle:
// case DalType.SQLite:
// _TempSql = _TempSql.Replace("[", "").Replace("]", "");
// break;
// case DalType.MySql:
// _TempSql = _TempSql.Replace("[", "`").Replace("]", "`");
// break;
//}
_TempSql = _TempSql.Remove(_TempSql.Length - 1, 1);
_TempSql.Append(" where " + FormatWhere(whereObj));
return(_TempSql.ToString());
}
/// <summary>
/// 将各数据库默认值格式化成标准值，将标准值还原成各数据库默认值
/// </summary>
/// <param name="flag">[0:转成标准值],[1:转成各数据库值],[2:转成各数据库值并补充字符串前后缀]</param>
/// <param name="sqlDbType">该列的值</param>
/// <returns></returns>
public static string FormatDefaultValue(DataBaseType dalType, object value, int flag, SqlDbType sqlDbType)
{
string defaultValue = Convert.ToString(value).Trim().TrimEnd('\n');//oracle会自带\n结尾
if (dalType != DataBaseType.Access)
{
defaultValue = defaultValue.Replace("GenGUID()", string.Empty);
}
if (defaultValue.Length == 0)
{
return(null);
}
int groupID = DataType.GetGroup(sqlDbType);
if (flag == 0)
{
#region 转标准值
if (groupID == 2)//日期的标准值
{
return(SqlValue.GetDate);
}
else if (groupID == 4)
{
return(SqlValue.Guid);
}
switch (dalType)
{
case DataBaseType.MySql: //用转\' \"，所以不用替换。
defaultValue = defaultValue.Replace("\\\"", "\"").Replace("\\\'", "\'");
break;
case DataBaseType.Access:
case DataBaseType.SQLite:
defaultValue = defaultValue.Replace("\"\"", "≮");
break;
default:
defaultValue = defaultValue.Replace("''", "≯");
break;
}
switch (defaultValue.ToLower().Trim('(', ')'))
{
case "newid":
case "guid":
case "sys_guid":
case "genguid":
case "uuid":
return(SqlValue.Guid);
}
#endregion
}
else
{
if (defaultValue == SqlValue.Guid)
{
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.Oracle:
case DataBaseType.Sybase:
case DataBaseType.PostgreSQL:
return(SqlCompatible.FormatGUID(defaultValue, dalType));
default:
return("");
}
}
}
switch (dalType)
{
case DataBaseType.Access:
if (flag == 0)
{
if (defaultValue[0] == '"' && defaultValue[defaultValue.Length - 1] == '"')
{
defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
}
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "Now()").Replace("\"", "\"\"");
if (groupID == 0)
{
defaultValue = "\"" + defaultValue + "\"";
}
}
break;
case DataBaseType.MsSql:
case DataBaseType.Sybase:
if (flag == 0)
{
if (defaultValue.StartsWith("(") && defaultValue.EndsWith(")")) //避免 (newid()) 被去掉()
{
defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
}
if (defaultValue.StartsWith("N'"))
{
defaultValue = defaultValue.TrimStart('N');
}
defaultValue = defaultValue.Trim('\''); //'(', ')',
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "getdate()").Replace("'", "''");
if (groupID == 0)
{
defaultValue = "(N'" + defaultValue + "')";
}
}
break;
case DataBaseType.Oracle:
if (flag == 0)
{
defaultValue = defaultValue.Trim('\'');
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "sysdate").Replace("'", "''");
if (groupID == 0)
{
defaultValue = "'" + defaultValue + "'";
}
}
break;
case DataBaseType.MySql:
if (flag == 0)
{
defaultValue = defaultValue.Replace("b'0", "0").Replace("b'1", "1").Trim(' ', '\'');
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "CURRENT_TIMESTAMP").Replace("'", "\\'").Replace("\"", "\\\"");
if (groupID == 0)
{
defaultValue = "\"" + defaultValue + "\"";
}
}
break;
case DataBaseType.SQLite:
if (flag == 0)
{
defaultValue = defaultValue.Trim('"');
if (groupID > 0) //兼容一些不规范的写法。像数字型的加了引号 '0'
{
defaultValue = defaultValue.Trim('\'');
}
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "CURRENT_TIMESTAMP").Replace("\"", "\"\"");
if (groupID == 0)
{
defaultValue = "\"" + defaultValue + "\"";
}
}
break;
case DataBaseType.PostgreSQL:
if (flag == 0)
{
defaultValue = defaultValue.Trim('"');
if (groupID == 0)
{
defaultValue = Regex.Split(defaultValue, "::", RegexOptions.IgnoreCase)[0];
}
if (groupID > 0) //兼容一些不规范的写法。像数字型的加了引号 '0'
{
defaultValue = defaultValue.Trim('\'');
}
}
else
{
defaultValue = defaultValue.Replace(SqlValue.GetDate, "now()").Replace("\"", "\"\"");
if (groupID == 0)
{
defaultValue = Regex.Split(defaultValue, "::", RegexOptions.IgnoreCase)[0];
defaultValue = "'" + defaultValue.Trim('\'') + "'";
}
else if (groupID == 3) // bool
{
defaultValue = defaultValue.Replace("1", "true").Replace("0", "false");
}
}
break;
}
if (flag == 0)
{
return(defaultValue.Replace("≮", "\"").Replace("≯", "'"));
}
return(defaultValue);
}
/// <summary>
/// 返回不包括Where条件的字符串
/// </summary>
/// <returns>结果如:Update tableName set Name=@Name,Value=@Value</returns>
internal string GetUpdateSql(object whereObj)
{
isCanDo = false;
StringBuilder _TempSql = new StringBuilder();
_TempSql.Append("Update " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " set ");
if (!string.IsNullOrEmpty(updateExpression))
{
_TempSql.Append(SqlCompatible.Format(updateExpression, _action.DataBaseType) + ",");
updateExpression = null;//取完值后清除值。
isCanDo = true;
}
string editTime = GetEditTimeSql();//内部判断该字段没有值才会更新。
if (!string.IsNullOrEmpty(editTime))
{
_TempSql.Append(editTime);//自带尾,号
}
MDataCell cell = null;
for (int i = 0; i < _action.Data.Count; i++)
{
cell = _action.Data[i];
if (cell.Struct.IsPrimaryKey || cell.Struct.IsAutoIncrement)
{
continue;//跳过自增或主键列。
}
if (cell.State > 1 && (cell.Struct.IsCanNull || !cell.IsNull))
{
if (cell.Struct.SqlType == SqlDbType.Timestamp && (_action.DataBaseType == DataBaseType.MsSql || _action.DataBaseType == DataBaseType.Sybase))
{
//更新时间戳不允许更新。
continue;
}
if (_action.DataBaseType == DataBaseType.MsSql && cell.Struct.SqlTypeName != null && cell.Struct.SqlTypeName.EndsWith("hierarchyId"))
{
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + "=HierarchyID::Parse('" + cell.StringValue + "')" + ",");
}
else
{
if (_action.DataBaseType == DataBaseType.FoxPro)
{
string value = "\"" + cell.StringValue + "\",";
if (cell.Struct.SqlType == SqlDbType.DateTime)
{
value = "{^" + cell.StringValue + "},";
}
//不支持参数化
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + "=" + value);
}
else
{
object value = cell.Value;
DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DataBaseType);
if (dbType == DbType.String && cell.StringValue == "")
{
if (_action.DataBaseType == DataBaseType.Oracle && !cell.Struct.IsCanNull)
{
value = " ";//Oracle not null 字段，不允许设置空值。
}
if (_action.DataBaseType == DataBaseType.MySql && cell.Struct.MaxSize == 36)
{
value = DBNull.Value;//MySql 的char36 会当成guid处理，不能为空，只能为null。
}
}
_action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + "=" + _action.dalHelper.Pre + cell.ColumnName + ",");
}
}
isCanDo = true;
}
}
if (!isCanDo)
{
string err = AppConst.HR + "warn : " + TableName + " can't find the data can be updated!";
Log.Write(err, LogType.Warn);
_action.dalHelper.DebugInfo.Append(err);
}
//switch (_action.dalHelper.dalType)
//{
// case DalType.Oracle:
// case DalType.SQLite:
// _TempSql = _TempSql.Replace("[", "").Replace("]", "");
// break;
// case DalType.MySql:
// _TempSql = _TempSql.Replace("[", "`").Replace("]", "`");
// break;
//}
_TempSql = _TempSql.Remove(_TempSql.Length - 1, 1);
_TempSql.Append(" where " + FormatWhere(whereObj));
return(_TempSql.ToString());
}
