private void DoWritDataLog(LibTableObj[] tableObjs) { try { if (tableObjs != null) { DataTable dt = null; //string logid = null; string tablenm = null; string ID = string.Empty; string logtbnm = string.Empty; //LibDbParameter[] parameters = null; //List<string > sqlbuilder = new List<string>(); StringBuilder sqlbuilder = new StringBuilder(); //ColExtendedProperties colextprop = null; TableExtendedProperties tbextprop = null; ILibDBHelp dBHelp = new DBHelpFactory().GetDBHelp(ResFactory.ResManager.LogDBNm); foreach (LibTableObj tableObj in tableObjs) { dt = tableObj.DataTable; if (dt != null && dt.Rows != null) { tbextprop = Newtonsoft.Json.JsonConvert.DeserializeObject <TableExtendedProperties>(dt.ExtendedProperties[SysConstManage.ExtProp].ToString()); if (!tbextprop.Ignore) { continue; } foreach (DataRow dr in dt.Rows) { //logid = dr[SysConstManage.Sdp_LogId].ToString(); tablenm = dt.TableName; switch (dr.RowState) { case DataRowState.Added: sqlbuilder.Append(DoGetLogSqlStr(dr, tablenm, 1, dBHelp)); break; case DataRowState.Modified: sqlbuilder.Append(DoGetLogSqlStr(dr, tablenm, 2, dBHelp)); break; } } } } if (!string.IsNullOrEmpty(sqlbuilder.ToString())) { dBHelp.ExecuteNonQuery(sqlbuilder.ToString()); } } } catch (Exception ex) { this.ExceptionHelp.ThrowError(this, ex.Message, ex.StackTrace); } }
public DataTable[] InternalFillData(List <string> whereformat, object[] valus) { StringBuilder sql = new StringBuilder(); DataTable[] dts = { }; StringBuilder where = new StringBuilder(); //DataTable mdt = null; //Dictionary<int, int> dic = new Dictionary<int, int>(); //SDPCRL.DAL.COM.SQLBuilder sQLBuilder = new SDPCRL.DAL.COM.SQLBuilder(); foreach (var libdt in this.LibTables) { foreach (LibTableObj dtobj in libdt.Tables) { TableExtendedProperties tbextprop = this.JsonToObj <TableExtendedProperties>(dtobj.DataTable.ExtendedProperties[SysConstManage.ExtProp].ToString()); if (!tbextprop.Ignore) { continue; } Array.Resize(ref dts, dts.Length + 1); dts[dts.Length - 1] = new DataTable(dtobj.TableName); if (tbextprop.TableIndex == 0 || tbextprop.TableIndex != tbextprop.RelateTableIndex) { where.Clear(); foreach (string item in whereformat) { if (where.Length > 0) { where.Append(" And "); } where.AppendFormat("{0}.{1}", LibSysUtils.ToCharByTableIndex(tbextprop.TableIndex), item); } //sql.Append(this.SQLBuilder.GetSQL(dtobj.TableName, null, new WhereObject { WhereFormat=where.ToString (),Values=valus },false,false)); sql.Append(this.DSContext.GetSQL(dtobj.TableName, null, new WhereObject { WhereFormat = where.ToString(), Values = valus }, false, false)); sql.AppendLine(); } } } this.DataAccess.GetDatatTables(sql.ToString(), ref dts); return(dts); }
public void SaveChange(LibTableObj[] tableObjs, bool IsTrans = true) { if (tableObjs == null || tableObjs.Length == 0) { return; } try { if (IsTrans) { this.BeginTrans(); } DataTable dt = null; StringBuilder fields = null; StringBuilder cols = null; StringBuilder fieldtypes = null; StringBuilder fieldvalue = null; StringBuilder updateFields = null; StringBuilder updatefldtypes = null; StringBuilder updatefldval = null; StringBuilder updatewhere = null; string sql = string.Empty; ColExtendedProperties colextprop = null; TableExtendedProperties tbextprop = null; List <object> updatevalue = null; foreach (LibTableObj tableObj in tableObjs) { dt = tableObj.DataTable; tbextprop = Newtonsoft.Json.JsonConvert.DeserializeObject <TableExtendedProperties>(dt.ExtendedProperties[SysConstManage.ExtProp].ToString()); if (tbextprop == null) { //520:系统无法识别的表对象,请使用模型实例化表对象。 throw new LibExceptionBase(520); } if (!tbextprop.Ignore) { continue; } fields = new StringBuilder(); cols = new StringBuilder(); fieldtypes = new StringBuilder(); fieldvalue = new StringBuilder(); List <int> bytecols = new List <int>(); foreach (DataColumn col in dt.Columns) { colextprop = Newtonsoft.Json.JsonConvert.DeserializeObject <ColExtendedProperties>(col.ExtendedProperties[SysConstManage.ExtProp].ToString()); if (!colextprop.IsActive) { continue; } if (fields.Length > 0) { fields.Append(","); fieldtypes.Append(","); fieldvalue.Append(","); cols.Append(","); } fields.AppendFormat("@{0}", col.ColumnName); fieldtypes.AppendFormat("@{0} ", col.ColumnName); cols.AppendFormat("{0}", col.ColumnName); if (col.DataType == typeof(byte[])) { bytecols.Add(dt.Columns.IndexOf(col)); } SetColmnTypeAndValue(col, col.ColumnName, colextprop, fieldtypes, fieldvalue, dt.Columns.IndexOf(col)); } foreach (DataRow row in dt.Rows) { switch (row.RowState) { case DataRowState.Added: row[SysConstManage.Sdp_LogId] = LogHelp.GenerateLogId(); object[] vals = new object[row.ItemArray.Length]; if (bytecols.Count > 0) { for (int a = 0; a < row.ItemArray.Length; a++) { vals[a] = bytecols.Contains(a) && row.ItemArray[a] != DBNull.Value ? Convert.ToBase64String((byte[])row.ItemArray[a]) : row.ItemArray[a]; } } else { vals = row.ItemArray; } sql = string.Format(string.Format("EXEC sp_executesql N'insert into {0}({1}) values({2}) ',N'{3}',{4}", dt.TableName, cols.ToString(), fields.ToString(), fieldtypes.ToString(), fieldvalue.ToString() ), vals); break; case DataRowState.Modified: updateFields = new StringBuilder(); updatefldtypes = new StringBuilder(); updatefldval = new StringBuilder(); updatewhere = new StringBuilder(); updatevalue = new List <object>(); int index = 0; foreach (DataColumn c in dt.Columns) { colextprop = Newtonsoft.Json.JsonConvert.DeserializeObject <ColExtendedProperties>(c.ExtendedProperties[SysConstManage.ExtProp].ToString()); if (!colextprop.IsActive) { continue; } if (!LibSysUtils.Compare(row[c, DataRowVersion.Original], row[c, DataRowVersion.Current], false)) { if (updateFields.Length > 0) { updateFields.Append(","); } if (updatefldtypes.Length > 0) { updatefldtypes.Append(","); } if (updatefldval.Length > 0) { updatefldval.Append(","); } updateFields.AppendFormat("{0}=@{0}", c.ColumnName); updatefldtypes.AppendFormat("@{0} ", c.ColumnName); SetColmnTypeAndValue(c, c.ColumnName, colextprop, updatefldtypes, updatefldval, index); index++; if (c.DataType.Equals(typeof(byte[]))) { updatevalue.Add(Convert.ToBase64String((byte[])row[c, DataRowVersion.Current])); } else { updatevalue.Add(row[c, DataRowVersion.Current]); } } if (dt.PrimaryKey.Contains(c)) { if (updatewhere.Length > 0) { updatewhere.Append(" And "); } if (updatefldtypes.Length > 0) { updatefldtypes.Append(","); updatefldval.Append(","); } updatewhere.AppendFormat("{0}=@{1}", c.ColumnName, string.Format("{0}1", c.ColumnName)); updatefldtypes.AppendFormat("@{0} ", string.Format("{0}1", c.ColumnName)); SetColmnTypeAndValue(c, string.Format("{0}1", c.ColumnName), colextprop, updatefldtypes, updatefldval, index); index++; updatevalue.Add(row[c, DataRowVersion.Original]); } } if (updateFields.Length > 0) { sql = string.Format(string.Format("EXEC sp_executesql N'Update {0} Set {1} where {2}',N'{3}',{4}", dt.TableName, updateFields.ToString(), updatewhere.ToString(), updatefldtypes.ToString(), updatefldval.ToString() ), updatevalue.ToArray()); break; } continue; case DataRowState.Deleted: updatefldtypes = new StringBuilder(); updatefldval = new StringBuilder(); updatewhere = new StringBuilder(); updatevalue = new List <object>(); index = 0; foreach (DataColumn col in dt.PrimaryKey) { colextprop = Newtonsoft.Json.JsonConvert.DeserializeObject <ColExtendedProperties>(col.ExtendedProperties[SysConstManage.ExtProp].ToString()); if (updatewhere.Length > 0) { updatewhere.Append(" And "); } if (updatefldtypes.Length > 0) { updatefldtypes.Append(","); updatefldval.Append(","); } updatewhere.AppendFormat("{0}=@{1}", col.ColumnName, string.Format("{0}1", col.ColumnName)); updatefldtypes.AppendFormat("@{0} ", string.Format("{0}1", col.ColumnName)); SetColmnTypeAndValue(col, string.Format("{0}1", col.ColumnName), colextprop, updatefldtypes, updatefldval, index); index++; updatevalue.Add(row[col, DataRowVersion.Original]); } if (updatefldval.Length > 0) { sql = string.Format(string.Format("EXEC sp_executesql N'Delete from {0} where {1}',N'{2}',{3}", dt.TableName, updatewhere.ToString(), updatefldtypes.ToString(), updatefldval.ToString() ), updatevalue.ToArray()); break; } continue; //break; default: continue; //break; } this.ExecuteNonQuery(sql); } } if (IsTrans) { this.CommitTrans(); WriteDataLog(tableObjs); } } catch (Exception ex) { if (IsTrans) { this.RollbackTrans(); } throw ex; } }