internal void FixValue() { object value = CellValue.SourceValue; DataGroupType group = DataType.GetGroup(_CellStruct.SqlType); if (_CellStruct.SqlType != SqlDbType.Variant) { value = ChangeValue(value, _CellStruct.ValueType, group); if (value == null) { return; } } if (isAllowChangeState) { if (CellValue.Value == null || CellValue.Value == DBNull.Value) { CellValue.State = 2; } else if (CellValue.Value.Equals(value) || (group != DataGroupType.Object && CellValue.Value.ToString() == StringValue))//对象的比较值,用==号则比例引用地址。 { CellValue.State = 1; } else { CellValue.State = 2; } } CellValue.Value = value; isNewValue = false; isAllowChangeState = false;//恢复可设置状态。 }
public bool Insert(bool isOpenTrans) { MDataCell cell = _Row.PrimaryCell; if (IsCanDoInsertCheck((cell.IsNullOrEmpty || cell.Struct.IsAutoIncrement || cell.Struct.IsPrimaryKey) ? 1 : 0)) { //判断是否需要增加自增加id if (!cell.Struct.IsCanNull && (cell.Struct.IsAutoIncrement || cell.Struct.IsPrimaryKey)) { #region 给主键赋值 DataGroupType group = DataType.GetGroup(cell.Struct.SqlType); string existWhere = cell.ColumnName + (group == DataGroupType.Number ? "={0}" : "='{0}'"); if (cell.IsNull || cell.State == 0 || cell.StringValue == "0" || Exists(string.Format(existWhere, cell.Value)))//这里检测存在,避免id重复 { switch (group) { case DataGroupType.Number: cell.Value = NextID; break; case DataGroupType.Guid: cell.Value = Guid.NewGuid(); break; case DataGroupType.Text: cell.Value = Guid.NewGuid().ToString(); break; default: return((bool)Error.Throw("first column value can't be null")); } } if (group == DataGroupType.Number || group == DataGroupType.Guid) //再检测是否已存在 { if (!isOpenTrans && Exists(string.Format(existWhere, cell.Value))) //事务时,由于自动补id,避开检测,提升性能 { Error.Throw("first column value must be unique:(" + cell.ColumnName + ":" + cell.Value + ")"); } else if (group == DataGroupType.Number) { maxID = (int)cell.Value; } } #endregion } CheckFileChanged(true); _Row.SetState(0);//状态重置,避免重复使用插入! MDataRow newRow = Table.NewRow(true); newRow.LoadFrom(_Row); _insertRows.Add(newRow);//插入引用,内存表有数据,还没写文章! needToSaveState = needToSaveState > 1 ? 2 : 1; return(true); } return(false); }
private List <DataGroup> getGroupsList(DataGroupType groupType) { using (zcrlDbContext = new DAL.ZcrlContext()) { List <DataGroup> groupsList = (from tg in zcrlDbContext.PortalDataGroups where (tg.RelatedGroup == groupType) select tg).ToList(); return(groupsList); } }
private bool checkUserAccess(DataGroupType dgt, out string redirectActionName, out string editedEntityName) { redirectActionName = null; editedEntityName = null; switch (dgt) { case DataGroupType.UserDepartment: { if (!User.IsInRole("Administrators")) { return(false); } redirectActionName = "UserGroups"; editedEntityName = "Группа користувачів"; break; } case DataGroupType.TenderGroup: { if (!User.IsInRole("Administrators") && !User.IsInRole("TenderGroup")) { return(false); } redirectActionName = "TenderGroups"; editedEntityName = "Тендерна категорія"; break; } case DataGroupType.ArticleGroup: { if (!User.IsInRole("Administrators") && !User.IsInRole("Editors") && !User.IsInRole("Doctors")) { return(false); } redirectActionName = "ArticleGroups"; editedEntityName = "Категорія статей"; break; } } return(true); }
/// <summary> /// 值的数据类型转换。 /// </summary> /// <param name="value">要被转换的值</param> /// <param name="convertionType">要转换成哪种类型</param> /// <param name="group">数据库类型的组号</param> /// <returns></returns> internal object ChangeValue(object value, Type convertionType, DataGroupType group) { //值不为null try { switch (group) { case DataGroupType.Text: if (_CellStruct.SqlType == SqlDbType.Time) //time类型的特殊处理。 { string[] items = StringValue.Split(' '); if (items.Length > 1) { CellValue.StringValue = items[1]; } } value = StringValue; break; default: value = ConvertTool.ChangeType(value, convertionType); //if (convertionType.Name.EndsWith("[]")) //{ // value = Convert.FromBase64String(StringValue); // CellValue.StringValue = "System.Byte[]"; //} //else //{ //} break; } } catch (Exception err) { CellValue.Value = null; CellValue.IsNull = true; CellValue.StringValue = null; isNewValue = false; string msg = string.Format("【MDataCell.ChangeValue】ChangeType Error:ColumnName【{0}】({1}) , Value:【{2}】\r\n", _CellStruct.ColumnName, _CellStruct.ValueType.FullName, StringValue); Log.Write(msg, LogType.Error); return(null); } return(value); }
/// <summary> /// Mysql Bit 类型不允许条件带引号 (字段='0' 不可以) /// </summary> /// <param name="where"></param> /// <param name="mdc"></param> /// <returns></returns> internal static string FormatMySqlBit(string where, MDataColumn mdc) { if (where.Contains("'0'")) { foreach (MCellStruct item in mdc) { DataGroupType group = DataType.GetGroup(item.SqlType); if (group == DataGroupType.Number || group == DataGroupType.Bool)//视图模式里取到的bit是bigint,所以数字一并处理 { if (where.IndexOf(item.ColumnName, StringComparison.OrdinalIgnoreCase) > -1) { string pattern = @"\s?" + item.ColumnName + @"\s*=\s*'0'"; where = Regex.Replace(where, pattern, " " + item.ColumnName + "=0", RegexOptions.IgnoreCase); } } } } return(where); }
private static string GetKey(MCellStruct column, DataBaseType dalType, ref List <MCellStruct> primaryKeyList, string version) { string key = SqlFormat.Keyword(column.ColumnName, dalType); //列名。 DataGroupType group = DataType.GetGroup(column.SqlType); //数据库类型。 bool isAutoOrPKey = column.IsPrimaryKey || column.IsAutoIncrement; //是否主键或自增列。 if (dalType != DataBaseType.Access || !isAutoOrPKey || !column.IsAutoIncrement) { SqlDbType sdt = column.SqlType; if (sdt == SqlDbType.DateTime && dalType == DataBaseType.MySql && Convert.ToString(column.DefaultValue) == SqlValue.GetDate) { sdt = SqlDbType.Timestamp; } key += " " + DataType.GetDataType(column, dalType, version); } if (isAutoOrPKey) { if (column.IsAutoIncrement) { if (primaryKeyList.Count == 0 || (!column.IsPrimaryKey && dalType == DataBaseType.MySql))//MySql 的自增必须是主键. { column.IsPrimaryKey = true; primaryKeyList.Insert(0, column); } } switch (dalType) { case DataBaseType.Access: if (column.IsAutoIncrement) { key += " autoincrement(1,1)"; } else // 主键。 { if (group == DataGroupType.Guid) //主键又是GUID { key += " default GenGUID()"; } } break; case DataBaseType.MsSql: if (column.IsAutoIncrement) { key += " IDENTITY(1,1)"; } else { if (group == DataGroupType.Guid) //主键又是GUID { key += " Default (newid())"; } } break; case DataBaseType.Oracle: if (Convert.ToString(column.DefaultValue) == SqlValue.Guid) //主键又是GUID { key += " Default (SYS_GUID())"; } break; case DataBaseType.Sybase: if (column.IsAutoIncrement) { key += " IDENTITY"; } else { if (group == DataGroupType.Guid) //主键又是GUID { key += " Default (newid())"; } } break; case DataBaseType.MySql: if (column.IsAutoIncrement) { key += " AUTO_INCREMENT"; if (!column.IsPrimaryKey) { primaryKeyList.Add(column); } } break; case DataBaseType.SQLite: //sqlite的AUTOINCREMENT不能写在primarykey前, if (column.IsAutoIncrement) { key += " PRIMARY KEY AUTOINCREMENT"; primaryKeyList.Clear(); //如果有自增加,只允许存在这一个主键。 } break; case DataBaseType.PostgreSQL: if (column.IsAutoIncrement && key.EndsWith("int")) { key = key.Substring(0, key.Length - 3) + "serial"; } break; case DataBaseType.DB2: if (column.IsAutoIncrement) { key += " GENERATED ALWAYS AS IDENTITY"; } break; } key += " NOT NULL"; } else { string defaultValue = string.Empty; if (Convert.ToString(column.DefaultValue).Length > 0 && group != DataGroupType.Object)//默认值只能是基础类型有。 { if (dalType == DataBaseType.MySql) { if ((group == 0 && (column.MaxSize < 1 || column.MaxSize > 8000)) || (group == DataGroupType.Date && key.Contains("datetime"))) //只能对TIMESTAMP类型的赋默认值。 { goto er; } } defaultValue = SqlFormat.FormatDefaultValue(dalType, column.DefaultValue, 1, column.SqlType); if (!string.IsNullOrEmpty(defaultValue)) { if (dalType == DataBaseType.MySql) { defaultValue = defaultValue.Trim('(', ')'); } key += " Default " + defaultValue; } } er: if (dalType != DataBaseType.Access) { if (dalType == DataBaseType.Sybase && column.SqlType == SqlDbType.Bit) { if (string.IsNullOrEmpty(defaultValue)) { key += " Default 0"; } key += " NOT NULL";//Sybase bit 不允许为Null } else { if (dalType == DataBaseType.DB2 && column.IsCanNull) { } //db2 不用null else { key += column.IsCanNull ? " NULL" : " NOT NULL"; } } } } if (!string.IsNullOrEmpty(column.Description)) { switch (dalType) { case DataBaseType.MySql: key += string.Format(" COMMENT '{0}'", column.Description.Replace("'", "''")); break; } } return(key + ","); }
private string getTitleForPage(DataGroupType type, CrudMode mode) { string result = null; switch (type) { case DataGroupType.ArticleGroup: { switch (mode) { case CrudMode.Add: { result = "Створити нову категорію статей"; break; } case CrudMode.Edit: { result = "Редагувати категорію"; break; } } break; } case DataGroupType.TenderGroup: { switch (mode) { case CrudMode.Add: { result = "Створити нову тендерну категорію"; break; } case CrudMode.Edit: { result = "Редагувати категорію"; break; } } break; } case DataGroupType.UserDepartment: { switch (mode) { case CrudMode.Add: { result = "Створити нову группу користувачів"; break; } case CrudMode.Edit: { result = "Редагувати группу користувачів"; break; } } break; } } return(result); }
/// <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); } DataGroupType group = DataType.GetGroup(sqlDbType); if (flag == 0) { #region 转标准值 if (group == DataGroupType.Date)//日期的标准值 { return(SqlValue.GetDate); } else if (group == DataGroupType.Guid) { 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 (group == 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 (group == 0) { defaultValue = "(N'" + defaultValue + "')"; } } break; case DataBaseType.Oracle: if (flag == 0) { defaultValue = defaultValue.Trim('\''); } else { defaultValue = defaultValue.Replace(SqlValue.GetDate, "sysdate").Replace("'", "''"); if (group == 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 (group == 0) { defaultValue = "\"" + defaultValue + "\""; } } break; case DataBaseType.SQLite: if (flag == 0) { defaultValue = defaultValue.Trim('"'); if (group > 0) //兼容一些不规范的写法。像数字型的加了引号 '0' { defaultValue = defaultValue.Trim('\''); } } else { defaultValue = defaultValue.Replace(SqlValue.GetDate, "CURRENT_TIMESTAMP").Replace("\"", "\"\""); if (group == 0) { defaultValue = "\"" + defaultValue + "\""; } } break; case DataBaseType.PostgreSQL: if (flag == 0) { defaultValue = defaultValue.Trim('"'); if (group == 0) { defaultValue = Regex.Split(defaultValue, "::", RegexOptions.IgnoreCase)[0]; } if (group != DataGroupType.Text) //兼容一些不规范的写法。像数字型的加了引号 '0' { defaultValue = defaultValue.Trim('\''); } } else { defaultValue = defaultValue.Replace(SqlValue.GetDate, "now()").Replace("\"", "\"\""); if (group == 0) { defaultValue = Regex.Split(defaultValue, "::", RegexOptions.IgnoreCase)[0]; defaultValue = "'" + defaultValue.Trim('\'') + "'"; } else if (group == DataGroupType.Bool) // bool { defaultValue = defaultValue.Replace("1", "true").Replace("0", "false"); } } break; case DataBaseType.DB2: if (flag == 0) { defaultValue = defaultValue.Trim(' ', '\''); if (group == 0) { defaultValue = "'" + defaultValue.Trim('\'') + "'"; } } else { defaultValue = defaultValue.Replace(SqlValue.GetDate, "CURRENT TIMESTAMP"); } break; } if (flag == 0) { return(defaultValue.Replace("≮", "\"").Replace("≯", "'")); } return(defaultValue); }
/// <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 " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + "("); _TempSql2.Append(" Values("); string endChar = ")"; MDataCell primaryCell = _action.Data[_action.Data.Columns.FirstPrimary.ColumnName]; DataGroupType group = DataType.GetGroup(primaryCell.Struct.SqlType); string defaultValue = Convert.ToString(primaryCell.Struct.DefaultValue); if (primaryCell.IsNullOrEmpty && (group == DataGroupType.Guid || (group == 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) + ","); if (_action.DataBaseType == DataBaseType.MsSql && cell.Struct.SqlTypeName != null && cell.Struct.SqlTypeName.EndsWith("hierarchyId")) { _TempSql2.Append("HierarchyID::Parse('" + cell.StringValue + "')"); } else { if (_action.DataBaseType == DataBaseType.FoxPro) { string value = "\"" + cell.StringValue + "\","; if (cell.Struct.SqlType == SqlDbType.DateTime) { value = "{^" + cell.StringValue + "},"; } //不支持参数化 _TempSql2.Append(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。 } } _TempSql2.Append(_action.dalHelper.Pre + cell.ColumnName + ","); _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) == DataGroupType.Number) { _TempSql.Append(primaryCell.ColumnName + ","); _TempSql2.Append(AutoID + ".nextval,"); } break; case DataBaseType.PostgreSQL: if (_action.AllowInsertID && primaryCell.Struct.IsAutoIncrement && !primaryCell.IsNullOrEmpty) { endChar = ") OVERRIDING SYSTEM VALUE "; } break; } string sql = _TempSql.ToString().TrimEnd(',') + endChar + _TempSql2.ToString().TrimEnd(',') + ")"; switch (_action.dalHelper.DataBaseType) { case DataBaseType.PostgreSQL: if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && group == DataGroupType.Number) { string key = Convert.ToString(primaryCell.Struct.DefaultValue); if (!string.IsNullOrEmpty(key)) { key = key.Replace("nextval", "currval"); //nextval('sequence_name'::regclass);||nextval('"sequence_name"'::regclass); string[] items = key.Split('\''); key = items[0] + "'" + SqlFormat.Keyword(items[1].Trim('\"'), DataBaseType.PostgreSQL) + "'" + items[2]; sql = sql + "; select " + key + " as OutPutValue"; } else { sql = sql + " RETURNING " + SqlFormat.Keyword(primaryCell.ColumnName, DataBaseType.PostgreSQL); } } else if (!primaryCell.IsNullOrEmpty) { sql += string.Format("; select '{0}' as OutPutValue", SqlFormat.Keyword(primaryCell.StringValue, DataBaseType.PostgreSQL)); } break; case DataBaseType.MsSql: case DataBaseType.Sybase: if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && group == DataGroupType.Number) { 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); }