private string GetInsertCommand(out DBParameterCollection Parameter) { StringBuilder SQL = new StringBuilder(); string ssql = "INSERT INTO REM_Master_Project ("; string extColumn = ""; string extParameter = ""; Parameter = new DBParameterCollection(); foreach (PropertyInfo ProInfo in _REM_Master_Project.GetType().GetProperties()) { if ((bool)_REM_Master_Project.GetType().GetField("Edit" + ProInfo.Name).GetValue(_REM_Master_Project) && ProInfo.Name != "Id") { object Value = ProInfo.GetValue(_REM_Master_Project, null); if (Value == null) { Value = DBNull.Value; } if (extColumn != "") { extColumn += ","; } extColumn += ProInfo.Name; if (extParameter != "") { extParameter += ","; } extParameter += "@" + ProInfo.Name; string PropType = ProInfo.PropertyType.Name; if (PropType.IndexOf("Nullable") >= 0) { PropType = ProInfo.PropertyType.GetGenericArguments()[0].Name; } Parameter.Add(new DBParameter("@" + ProInfo.Name, Value, (DbType)Enum.Parse(typeof(DbType), PropType, true))); } } if (extColumn == "") { return(""); } ssql += extColumn + ") VALUES(" + extParameter + ")"; string GetIdentityComm = string.Empty; if (_DBHelper.Provider == "System.Data.SqlClient") { GetIdentityComm = "SELECT @@IDENTITY"; } else if (_DBHelper.Provider == "MySql.Data.MySqlClient") { GetIdentityComm = "SELECT LAST_INSERT_ID()"; } return(ssql + ";" + GetIdentityComm); }
private string GetInsertCommand(DBHelper DBHelp, out DBParameterCollection Parameter) { List <string> IdentityInsertKey_List = this.GetIdentityInsertKey(); Type MyType = _SAP_Interface_Log.GetType(); Type BaseType = MyType.BaseType; Type TableType = typeof(System.Object) == BaseType ? MyType : BaseType; Parameter = new DBParameterCollection(); List <string> Field_List = new List <string>(); foreach (PropertyInfo ProInfo in _SAP_Interface_Log.GetType().GetProperties()) { FieldInfo FieldInfo = _SAP_Interface_Log.GetType().GetField("Edit" + ProInfo.Name); if (FieldInfo != null && (bool)FieldInfo.GetValue(_SAP_Interface_Log) && IdentityInsertKey_List.IndexOf(ProInfo.Name) < 0) { Field_List.Add(ProInfo.Name); object Value = ProInfo.GetValue(_SAP_Interface_Log, null); if (Value == null) { Value = DBNull.Value; } string PropType = ProInfo.PropertyType.IsGenericType && ProInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable <>)) ? Nullable.GetUnderlyingType(ProInfo.PropertyType).Name : ProInfo.PropertyType.Name; Parameter.Add(new DBParameter("@" + ProInfo.Name, Value, (DbType)Enum.Parse(typeof(DbType), PropType, true))); } } string SQL = "INSERT INTO " + TableType.Name + " (" + string.Join(",", Field_List.Select(x => x)) + ")"; SQL += " VALUES(" + string.Join(",", Field_List.Select(x => "@" + x)) + ")"; if (Field_List.Count == 0) { return(""); } string GetIdentityComm = string.Empty; if (DBHelp.Provider == "System.Data.SqlClient") { GetIdentityComm = " SELECT @@IDENTITY"; } else if (DBHelp.Provider == "MySql.Data.MySqlClient") { GetIdentityComm = " SELECT LAST_INSERT_ID()"; } return(SQL + ";" + GetIdentityComm); }
private string GetOverLimitErrorMsg(DBHelper DBHelp, IDbTransaction DbTransaction) { try { Type MyType = _SAP_Interface_Log.GetType(); Type BaseType = MyType.BaseType; Type TableType = typeof(System.Object) == BaseType ? MyType : BaseType; System.Text.StringBuilder OverLimitMsg = new System.Text.StringBuilder(); System.Text.StringBuilder SQL = new System.Text.StringBuilder(); SQL.AppendLine("SELECT"); SQL.AppendLine(" c.name AS ColumnName"); SQL.AppendLine(" , t.name AS TypeName"); SQL.AppendLine(" , c.max_length AS MaxLength"); SQL.AppendLine("FROM sys.columns c"); SQL.AppendLine("INNER JOIN sys.objects tb ON c.object_id = tb.object_id"); SQL.AppendLine("INNER JOIN sys.types t ON c.user_type_id = t.user_type_id"); SQL.AppendLine("WHERE tb.name = '" + TableType.Name + "'"); System.Data.DataTable DT = DBHelp.ExecuteDataTable(SQL.ToString(), DbTransaction); foreach (PropertyInfo ProInfo in _SAP_Interface_Log.GetType().GetProperties()) { FieldInfo FieldInfo = _SAP_Interface_Log.GetType().GetField("Edit" + ProInfo.Name); if (FieldInfo != null && (bool)FieldInfo.GetValue(_SAP_Interface_Log)) { DataRow[] DR_List = DT.Select("ColumnName = '" + ProInfo.Name + "' AND TypeName LIKE '*char*' AND MaxLength <> -1"); DataRow DR = DR_List.FirstOrDefault(); if (DR != null) { int MaxLength = Convert.ToInt32(DR["MaxLength"]) / 2; object Value = ProInfo.GetValue(_SAP_Interface_Log, null); string ValueStr = Value == null ? "" : Value.ToString(); if (ValueStr.Length > MaxLength) { OverLimitMsg.AppendFormat("String length longer then prescribed on {1} property.({2}>{3}){0}", Environment.NewLine, ProInfo.Name, ValueStr.Length, MaxLength); } } } } return(OverLimitMsg.ToString()); } catch (Exception) { return(""); } }
/// <summary> /// 将集合类转换成DataTable /// </summary> /// <param name="list">集合</param> /// <returns></returns> public static DataTable ToDataTable(IList list) { DataTable result = new DataTable(); if (list.Count > 0) { PropertyInfo[] propertys = list[0].GetType().GetProperties(); foreach (PropertyInfo ProInfo in propertys) { string Value = ProInfo.Name.ToString(); object PropertyType = Convert.ChangeType(Value, typeof(String)); // ProInfo.PropertyType; if (ProInfo.PropertyType.IsGenericType) { //泛型Nullable<> Type genericTypeDefinition = ProInfo.PropertyType.GetGenericTypeDefinition(); if (genericTypeDefinition == typeof(Nullable <>)) { PropertyType = Convert.ChangeType(Value, typeof(String)); //PropertyType = ProInfo.PropertyType.GetGenericArguments()[0]; //PropertyType = Convert.ChangeType(value, Nullable.GetUnderlyingType(typeof(String))); } } result.Columns.Add(Value, PropertyType.GetType()); } for (int i = 0; i < list.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo ProInfo in propertys) { object obj = ProInfo.GetValue(list[i], null); tempList.Add(obj); } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } } return(result); }
private string GetInsertCommand(out DBParameterCollection Parameter) { StringBuilder SQL = new StringBuilder(); string ssql = "INSERT INTO AP_Master_Item_Unit ("; string extColumn = ""; string extParameter = ""; Parameter = new DBParameterCollection(); foreach (PropertyInfo ProInfo in _AP_Master_Item_Unit.GetType().GetProperties()) { if ((bool)_AP_Master_Item_Unit.GetType().GetField("Edit" + ProInfo.Name).GetValue(_AP_Master_Item_Unit)) { object Value = ProInfo.GetValue(_AP_Master_Item_Unit, null); if (Value == null) { Value = DBNull.Value; } if (extColumn != "") { extColumn += ","; } extColumn += ProInfo.Name; if (extParameter != "") { extParameter += ","; } extParameter += "@" + ProInfo.Name; string PropType = ProInfo.PropertyType.Name; if (PropType.IndexOf("Nullable") >= 0) { PropType = ProInfo.PropertyType.GetGenericArguments()[0].Name; } Parameter.Add(new DBParameter("@" + ProInfo.Name, Value, (DbType)Enum.Parse(typeof(DbType), PropType, true))); } } if (extColumn == "") { return(""); } ssql += extColumn + ") VALUES(" + extParameter + ")"; return(ssql); }
private string GetUpdateCommand(out DBParameterCollection Parameter) { List <string> PrimaryKey_List = this.GetPrimaryKey(); Type MyType = _SAP_Interface_Log.GetType(); Type BaseType = MyType.BaseType; Type TableType = typeof(System.Object) == BaseType ? MyType : BaseType; Parameter = new DBParameterCollection(); List <string> Field_List = new List <string>(); foreach (PropertyInfo ProInfo in _SAP_Interface_Log.GetType().GetProperties()) { FieldInfo FieldInfo = _SAP_Interface_Log.GetType().GetField("Edit" + ProInfo.Name); if (FieldInfo != null && (bool)FieldInfo.GetValue(_SAP_Interface_Log) && PrimaryKey_List.IndexOf(ProInfo.Name) < 0) { Field_List.Add(ProInfo.Name); object Value = ProInfo.GetValue(_SAP_Interface_Log, null); if (Value == null) { Value = DBNull.Value; } string PropType = ProInfo.PropertyType.IsGenericType && ProInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable <>)) ? Nullable.GetUnderlyingType(ProInfo.PropertyType).Name : ProInfo.PropertyType.Name; Parameter.Add(new DBParameter("@" + ProInfo.Name, Value, (DbType)Enum.Parse(typeof(DbType), PropType, true))); } if (PrimaryKey_List.IndexOf(ProInfo.Name) >= 0) { Parameter.Add(new DBParameter("@" + ProInfo.Name, ProInfo.GetValue(_SAP_Interface_Log, null))); } } string SQL = "UPDATE " + TableType.Name + " SET " + string.Join(",", Field_List.Select(x => x + "=@" + x)); SQL += " WHERE " + string.Join(" AND ", PrimaryKey_List.Select(x => x + "=@" + x)); if (Field_List.Count == 0) { return(""); } return(SQL); }
private string GetUpdateCommand(out DBParameterCollection Parameter) { Parameter = new DBParameterCollection(); string ssql = "UPDATE REM_Master_Project SET "; string extSql = ""; foreach (PropertyInfo ProInfo in _REM_Master_Project.GetType().GetProperties()) { if ((bool)_REM_Master_Project.GetType().GetField("Edit" + ProInfo.Name).GetValue(_REM_Master_Project) && ProInfo.Name != "Id") { object Value = ProInfo.GetValue(_REM_Master_Project, null); if (Value == null) { Value = DBNull.Value; } if (extSql != "") { extSql += ","; } extSql += ProInfo.Name + "=@" + ProInfo.Name; string PropType = ProInfo.PropertyType.Name; if (PropType.IndexOf("Nullable") >= 0) { PropType = ProInfo.PropertyType.GetGenericArguments()[0].Name; } Parameter.Add(new DBParameter("@" + ProInfo.Name, Value, (DbType)Enum.Parse(typeof(DbType), PropType, true))); } if (ProInfo.Name == "Id") { Parameter.Add(new DBParameter("@" + ProInfo.Name, ProInfo.GetValue(_REM_Master_Project, null))); } } ssql += extSql; ssql += " WHERE Id=@Id"; if (extSql == "") { return(""); } return(ssql); }
private string GetInsertCommand(DBHelper DBHelp, out DBParameterCollection Parameter) { List <string> IdentityInsertKey_List = this.GetIdentityInsertKey(); Type MyType = _JournalEntries_Lines.GetType(); Type BaseType = MyType.BaseType; Type TableType = typeof(System.Object) == BaseType ? MyType : BaseType; Parameter = new DBParameterCollection(); List <string> Field_List = new List <string>(); foreach (PropertyInfo ProInfo in _JournalEntries_Lines.GetType().GetProperties()) { FieldInfo FieldInfo = _JournalEntries_Lines.GetType().GetField("Edit" + ProInfo.Name); if (FieldInfo != null && (bool)FieldInfo.GetValue(_JournalEntries_Lines) && IdentityInsertKey_List.IndexOf(ProInfo.Name) < 0) { Field_List.Add(ProInfo.Name); object Value = ProInfo.GetValue(_JournalEntries_Lines, null); if (Value == null) { Value = DBNull.Value; } string PropType = ProInfo.PropertyType.IsGenericType && ProInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable <>)) ? Nullable.GetUnderlyingType(ProInfo.PropertyType).Name : ProInfo.PropertyType.Name; Parameter.Add(new DBParameter("@" + ProInfo.Name, Value, (DbType)Enum.Parse(typeof(DbType), PropType, true))); } } string SQL = "INSERT INTO " + TableType.Name + " (" + string.Join(",", Field_List.Select(x => x)) + ")"; SQL += " VALUES(" + string.Join(",", Field_List.Select(x => "@" + x)) + ")"; if (Field_List.Count == 0) { return(""); } return(SQL); }