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