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);
}
