private string GetWhereFromObj(object whereObj) { if (whereObj == null) { return(string.Empty); } else if (whereObj is String || (whereObj is ValueType && !(whereObj is Enum))) { return(Convert.ToString(whereObj)); } else if (whereObj is IField) { return(SqlFormat.GetIFieldSql(whereObj)); } MDataCell cell = null; if (whereObj is Enum) { cell = _action.Data[(int)whereObj]; } else if (whereObj is MDataCell) { cell = whereObj as MDataCell; } else { string propName = MBindUI.GetID(whereObj); if (!string.IsNullOrEmpty(propName)) { _action.UI.Get(whereObj, null, null); cell = _action.Data[propName]; } } string where = string.Empty; if (cell != null) { #region 从单元格里取值。 if (cell.IsNullOrEmpty) { isCanDo = false; _action.dalHelper.recordsAffected = -2; _action.dalHelper.debugInfo.Append(AppConst.HR + "error : " + cell.ColumnName + " can't be null" + AppConst.BR); return("1=2 and " + cell.ColumnName + " is null"); } switch (_action.dalHelper.dalType) { case DalType.Txt: case DalType.Xml: switch (DataType.GetGroup(cell.Struct.SqlType)) { case 1: case 3: where = cell.ColumnName + "=" + cell.Value; break; default: where = cell.ColumnName + "='" + cell.Value + "'"; break; } break; default: where = cell.ColumnName + "=" + _action.dalHelper.Pre + cell.ColumnName; _action.dalHelper.AddParameters(cell.ColumnName, cell.Value, DataType.GetDbType(cell.Struct.ValueType), cell.Struct.MaxSize, System.Data.ParameterDirection.Input); break; } #endregion } return(where); }
/// <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> /// <returns>结果如:insert into tableName(id,Name,Value) values(@id,@Name,@Value)</returns> internal string GetInsertSql() { isCanDo = false; StringBuilder _TempSql = new StringBuilder(); StringBuilder _TempSql2 = new StringBuilder(); _TempSql.Append("insert into " + TableName + "("); _TempSql2.Append(") Values("); MDataCell primaryCell = _action.Data[_action.Data.Columns.FirstPrimary.ColumnName]; int groupID = DataType.GetGroup(primaryCell.Struct.SqlType); string defaultValue = Convert.ToString(primaryCell.Struct.DefaultValue); if (primaryCell.IsNullOrEmpty && (groupID == 4 || (groupID == 0 && (primaryCell.Struct.MaxSize <= 0 || primaryCell.Struct.MaxSize >= 36) && (defaultValue == "" || defaultValue == "newid" || defaultValue == SqlValue.Guid))))//guid类型 { primaryCell.Value = Guid.NewGuid(); } MDataCell cell = null; for (int i = 0; i < _action.Data.Count; i++) { cell = _action.Data[i]; if (cell.Struct.IsAutoIncrement && !_action.AllowInsertID) { continue;//跳过自增列。 } if (cell.IsNull && !cell.Struct.IsCanNull && cell.Struct.DefaultValue == null) { string err = AppConst.HR + string.Format("error : {0} {1} can't be insert null", TableName, cell.ColumnName) + AppConst.BR; Log.Write(err, LogType.DataBase); _action.dalHelper.DebugInfo.Append(err); _action.dalHelper.RecordsAffected = -2; isCanDo = false; break; } if (cell.State > 0) { _TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + ","); _TempSql2.Append(_action.dalHelper.Pre + cell.ColumnName + ","); 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); isCanDo = true; } } switch (_action.dalHelper.DataBaseType) { case DataBaseType.Oracle: if (!_action.AllowInsertID && DataType.GetGroup(primaryCell.Struct.SqlType) == 1) { _TempSql.Append(primaryCell.ColumnName + ","); _TempSql2.Append(AutoID + ".nextval,"); } break; } string sql = _TempSql.ToString().TrimEnd(',') + _TempSql2.ToString().TrimEnd(',') + ")"; switch (_action.dalHelper.DataBaseType) { case DataBaseType.PostgreSQL: if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && groupID == 1) { string key = Convert.ToString(primaryCell.Struct.DefaultValue); if (!string.IsNullOrEmpty(key)) { key = key.Replace("nextval", "currval"); sql = sql + "; select " + key + " as OutPutValue"; } } else if (!primaryCell.IsNullOrEmpty) { sql += string.Format("; select '{0}' as OutPutValue", primaryCell.Value); } break; case DataBaseType.MsSql: case DataBaseType.Sybase: if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && groupID == 1) { if (_action.dalHelper.DataBaseType == DataBaseType.Sybase) { sql = sql + " select @@idENTITY as OutPutValue"; } else if (_action.dalHelper.DataBaseType == DataBaseType.MsSql) { sql += " select cast(scope_identity() as bigint) as OutPutValue"; //改成bigint避免转换数据溢出 } } else if (!primaryCell.IsNullOrEmpty) { sql += string.Format(" select '{0}' as OutPutValue", primaryCell.Value); } if (_action.AllowInsertID && !_action.dalHelper.IsOpenTrans && primaryCell.Struct.IsAutoIncrement) //非批量操作时 { sql = "set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " on " + sql + " set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " off"; } break; //if (!(Parent.AllowInsertID && !primaryCell.IsNull)) // 对于自行插入id的,跳过,主操作会自动返回id。 //{ // sql += ((groupID == 1 && (primaryCell.IsNull || primaryCell.ToString() == "0")) ? " select cast(scope_identity() as int) as OutPutValue" : string.Format(" select '{0}' as OutPutValue", primaryCell.Value)); //} //case DalType.Oracle: // sql += string.Format("BEGIN;select {0}.currval from dual; END;", Autoid); // break; } return(sql); }
/// <summary> /// 返回插入的字符串 /// </summary> /// <returns>结果如:insert into tableName(ID,Name,Value) values(@ID,@Name,@Value)</returns> internal string GetInsertSql() { isCanDo = false; StringBuilder _TempSql = new StringBuilder(); StringBuilder _TempSql2 = new StringBuilder(); _TempSql.Append("insert into " + TableName + "("); _TempSql2.Append(") Values("); MDataCell primaryCell = _action.Data[_action.Data.Columns.FirstPrimary.ColumnName]; int groupID = DataType.GetGroup(primaryCell.Struct.SqlType); string defaultValue = Convert.ToString(primaryCell.Struct.DefaultValue); if (primaryCell.IsNullOrEmpty && (groupID == 4 || (groupID == 0 && (primaryCell.Struct.MaxSize <= 0 || primaryCell.Struct.MaxSize >= 36) && (defaultValue == "" || defaultValue == "newid" || defaultValue == SqlValue.Guid))))//guid类型 { primaryCell.Value = Guid.NewGuid(); } MDataCell cell = null; for (int i = 0; i < _action.Data.Count; i++) { cell = _action.Data[i]; if (cell.Struct.IsAutoIncrement && !_action.AllowInsertID) { continue;//跳过自增列。 } if (cell.IsNull && !cell.Struct.IsCanNull && cell.Struct.DefaultValue == null) { _action.dalHelper.debugInfo.Append(AppConst.HR + "error : " + cell.ColumnName + " can't be null" + AppConst.BR); _action.dalHelper.recordsAffected = -2; isCanDo = false; break; } if (cell.cellValue.State > 0) { _TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DalType) + ","); _TempSql2.Append(_action.dalHelper.Pre + cell.ColumnName + ","); 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 = " "; } _action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input); isCanDo = true; } } switch (_action.dalHelper.dalType) { case DalType.Oracle: if (!_action.AllowInsertID && DataType.GetGroup(primaryCell.Struct.SqlType) == 1) { _TempSql.Append(primaryCell.ColumnName + ","); _TempSql2.Append(AutoID + ".nextval,"); } break; } string sql = _TempSql.ToString().TrimEnd(',') + _TempSql2.ToString().TrimEnd(',') + ")"; switch (_action.dalHelper.dalType) { case DalType.MsSql: case DalType.Sybase: if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && groupID == 1) { if (_action.dalHelper.dalType == DalType.Sybase) { sql = sql + " select @@IDENTITY as OutPutValue"; } else { sql += " select cast(scope_Identity() as int) as OutPutValue"; } } else if (!primaryCell.IsNullOrEmpty) { sql += string.Format(" select '{0}' as OutPutValue", primaryCell.Value); } if (_action.AllowInsertID && !_action.dalHelper.isOpenTrans && primaryCell.Struct.IsAutoIncrement) //非批量操作时 { sql = "set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.dalType) + " on " + sql + " set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.dalType) + " off"; } break; //if (!(Parent.AllowInsertID && !primaryCell.IsNull)) // 对于自行插入ID的,跳过,主操作会自动返回ID。 //{ // sql += ((groupID == 1 && (primaryCell.IsNull || primaryCell.ToString() == "0")) ? " select cast(scope_Identity() as int) as OutPutValue" : string.Format(" select '{0}' as OutPutValue", primaryCell.Value)); //} //case DalType.Oracle: // sql += string.Format("BEGIN;select {0}.currval from dual; END;", AutoID); // break; } return(sql); }
/// <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()); }