internal void SetValueToEntity(string propName)
{
if (!string.IsNullOrEmpty(propName))
{
PropertyInfo pi = typeInfo.GetProperty(propName);
if (pi != null)
{
MDataCell cell = action.Data[propName];
if (cell != null && !cell.IsNull)
{
try
{
pi.SetValue(entity, cell.Value, null);
}
catch
{
}
}
}
}
else
{
action.Data.SetToEntity(entity);
}
}
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 void Form1_Load(object sender, EventArgs e)
{
MDataRow row = new MDataRow();
row.LoadFrom("{id:1,name:'hello'}");
MDataCell cell = row[0];
StringBuilder sb = new StringBuilder();
sb.AppendLine("ColumnName:" + cell.ColumnName);
sb.AppendLine("Struct.SqlType:" + cell.Struct.SqlType);
sb.AppendLine("IsNull:" + cell.IsNull);
sb.AppendLine("IsNullOrEmpty:" + cell.IsNullOrEmpty);
sb.AppendLine("State:" + cell.State + " (0:不可插入和更新；1：仅可以插入；2：可插入可更新)");
sb.AppendLine("Value:" + cell.Value);
sb.AppendLine("------------");
cell = row[1];
sb.AppendLine("ColumnName:" + cell.ColumnName);
sb.AppendLine("Struct.SqlType:" + cell.Struct.SqlType);
sb.AppendLine("Value:" + cell.Value);
cell.Struct.SqlType = SqlDbType.Int;//修改数据类型
sb.AppendLine("修改结构：Struct.SqlType:" + cell.Struct.SqlType);
sb.Append("FixValue():");
try
{ // AppConfig.Log.IsWriteLog = true;
Exception err;
if (!cell.FixValue(out err)) //修改该值
{
sb.AppendLine(err.Message);
}
}
catch (Exception er)
{
sb.AppendLine(er.Message);
}
sb.AppendLine("修正后Value:" + cell.Value);
rtxtText.Text = sb.ToString();
}
/// <summary>
/// 更新记录
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="t">实体对象</param>
/// <param name="where">条件</param>
/// <param name="updateColumns">指定要更新列</param>
/// <returns></returns>
public static bool Update <T>(T t, object where, params string[] updateColumns) where T : class
{
using (MAction action = GetMAction <T>())
{
action.Data.LoadFrom(t, BreakOp.Null);
if (updateColumns.Length > 0)
{
action.Data.SetState(0);
if (updateColumns.Length == 1)
{
updateColumns = updateColumns[0].Split(',');
}
foreach (string item in updateColumns)
{
MDataCell cell = action.Data[item];
if (cell != null)
{
cell.State = 2;
}
}
}
return(action.Update(where));
}
}
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)
{
_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>
/// 批量更新或插入。
/// </summary>
/// <param name="dt"></param>
/// <param name="excelRow"></param>
/// <returns></returns>
public static bool AcceptChanges(MDataTable dt, MDataRow excelRow, string objName = null)
{
if (excelRow == null)
{
MDataTable dtImportUnique = GridConfig.GetList(objName, GridConfig.SelectType.ImportUnique);
string[] names = null;
if (dtImportUnique != null && dtImportUnique.Rows.Count > 0)
{
names = new String[dtImportUnique.Rows.Count];
for (int i = 0; i < dtImportUnique.Rows.Count; i++)
{
names[i] = dtImportUnique.Rows[i].Get <string>("Field");
}
}
return(dt.AcceptChanges(AcceptOp.Auto, null, names));
}
bool result = true;
//获取相关配置
string[] tables = excelRow.Get <string>(Config_Excel.TableNames).Split(',');
MDataTable configTable = GetExcelInfo(excelRow.Get <string>(Config_Excel.ExcelID));
Dictionary <string, string> rowPrimaryValue = new Dictionary <string, string>(); //存档每个表每行的主键值。
Dictionary <string, string> wherePrimaryValue = new Dictionary <string, string>(); //存档where条件对应的主键值。
using (MAction action = new MAction(tables[0]))
{
action.SetAopOff();
action.BeginTransation();
AppConfig.Debug.OpenDebugInfo = false;
IExcelConfig excelConfigExtend = ExcelConfigFactory.GetExcelConfigExtend();
foreach (var table in tables)
{
GC.Collect();//后面的Fill查询代码循环上万次会增长太多内存，提前调用，能降低内存。
action.ResetTable(table);
for (int i = 0; i < dt.Rows.Count; i++)
{
action.Data.Clear();
var row = dt.Rows[i];
foreach (var cell in row) //遍历所有数据行
{
if (cell.Struct.TableName == table) //过滤出属于本表的字段。
{
string[] items = cell.ColumnName.Split('.');
string columnName = items[items.Length - 1];
action.Set(columnName, cell.Value);
}
}
#region 检测是否需要插入外键。
MDataTable foreignTable = configTable.FindAll("TableName='" + table + "' and IsForeignkey=1");
if (foreignTable != null)
{
foreach (var foreignRow in foreignTable.Rows)
{
string formatter = foreignRow.Get <string>("Formatter");
string fTableName = foreignRow.Get <string>("ForeignTable");
if (string.IsNullOrEmpty(formatter))
{
//获取主键外值键
string key = fTableName + i;
if (rowPrimaryValue.ContainsKey(key))
{
string value = rowPrimaryValue[key];
action.Set(foreignRow.Get <string>("Field"), value);
}
}
else // 从其它自定义列取值。
{
MDataCell cell = row[formatter];
cell = cell ?? row[fTableName + "." + formatter];
if (cell != null)
{
action.Set(foreignRow.Get <string>("Field"), cell.Value);
}
}
}
foreignTable = null;
}
#endregion
#region //获取唯一联合主键,检测是否重复
string where = string.Empty;
List <MDataRow> rowList = configTable.FindAll("TableName='" + table + "' and IsUnique=1");
if (rowList != null && rowList.Count > 0)
{
bool IsUniqueOr = excelRow.Get <bool>("IsUniqueOr");
List <MDataCell> cells = new List <MDataCell>();
string errText = string.Empty;
int errorCount = 0;
foreach (var item in rowList)
{
var cell = action.Data[item.Get <string>(Config_ExcelInfo.Field)];
if (cell != null)
{
if (cell.IsNullOrEmpty) // 唯一主键是必填写字段
{
errorCount++;
errText += "[第" + (i + 1) + "行数据]：" + cell.Struct.ColumnName + "[" + cell.Struct.Description + "]不允许为空！\r\n";
}
else
{
cells.Add(cell);
}
}
}
if (errorCount > 0)
{
if (!IsUniqueOr || errorCount == rowList.Count)
{
result = false;
dt.DynamicData = new Exception(errText);
goto err;
}
}
MDataCell[] item2s = cells.ToArray();
where = action.GetWhere(!IsUniqueOr, item2s);
item2s = null;
rowList = null;
}
if (!string.IsNullOrEmpty(where))
{
action.SetSelectColumns(action.Data.PrimaryCell.ColumnName);
if (action.Fill(where))//根据条件查出主键ID
{
string key = table + where;
if (wherePrimaryValue.ContainsKey(key))
{
rowPrimaryValue.Add(table + i, wherePrimaryValue[key]);//记录上一个主键值。
}
else
{
rowPrimaryValue.Add(table + i, action.Get <string>(action.Data.PrimaryCell.ColumnName));//记录上一个主键值。
}
if (action.Data.GetState() == 2)
{
ExcelResult eResult = excelConfigExtend.BeforeUpdate(action.Data, row);
if (eResult == ExcelResult.Ignore || (eResult == ExcelResult.Default && action.Update(where)))
{
continue;//已经存在了，更新，准备下一条。
}
else
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
else
{
continue;//已经存在了，同时没有可更新字段
}
}
else if (!string.IsNullOrEmpty(action.DebugInfo))//产生错误信息，发生异常
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
#endregion
if (action.Data.GetState() == 0)
{
continue;//没有可映射插入的列。
}
//插入前，调用函数（插入特殊主键等值）
string errMsg;
ExcelResult excelResult = excelConfigExtend.BeforeInsert(action.Data, row, out errMsg);
if (excelResult == ExcelResult.Ignore)
{
continue;
}
if (excelResult == ExcelResult.Error || !action.Insert(InsertOp.ID))
{
result = false;
action.RollBack();
if (string.IsNullOrEmpty(errMsg))
{
errMsg = "[第" + (i + 1) + "行数据]：" + action.DebugInfo;
}
dt.DynamicData = new Exception(errMsg);
excelConfigExtend.OnInsertError(errMsg, dt);
goto err;
}
//插入后事件（可以触发其它事件）
excelConfigExtend.AfterInsert(action.Data, row, i == dt.Rows.Count - 1);
string primaryKey = action.Get <string>(action.Data.PrimaryCell.ColumnName);
rowPrimaryValue.Add(table + i, primaryKey);//记录上一个主键值。
if (!wherePrimaryValue.ContainsKey(table + where))
{
wherePrimaryValue.Add(table + where, primaryKey);
}
}
}
err:
action.EndTransation();
excelConfigExtend.Dispose();
}
return(result);
}
public MDataProperty(MDataCell mdc, Attribute[] attrs)
: base(mdc.ColumnName, attrs)
{
cell = mdc;
}
/// <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);
}
internal MDataCell[] GetJoinPrimaryCell(MDataRow row)
{
MDataCell[] cells = null;
if (jointPrimaryIndex != null && jointPrimaryIndex.Count > 0)
{
cells = new MDataCell[jointPrimaryIndex.Count];
for (int i = 0; i < jointPrimaryIndex.Count; i++)
{
cells[i] = row[jointPrimaryIndex[i]];
}
}
else
{
cells = row.JointPrimaryCell.ToArray();
}
return cells;
}
/// <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());
}
