public System.Data.IDbCommand GenerateSelectItemsSql <T>(object ob, PropertyInfo ParentInfo) { //初始化一个Command System.Data.SqlClient.SqlCommand command = null; var TargetType = typeof(T); var ParentType = ob.GetType(); ORMHelper helper = new ORMHelper(); command = new System.Data.SqlClient.SqlCommand(); String TableName = helper.GetTable(TargetType); var Properties = TargetType.GetProperties(); String Cols = ""; String WhereSql = ""; PropertyInfo ParentKey = null; String ColumnName = ""; var parentAttribute = ParentInfo.GetCustomAttributes(typeof(ParentRelationAttribute), true); if (parentAttribute.Length > 0) { ColumnName = (parentAttribute[0] as ParentRelationAttribute).ColumnName; // ParentKey = ParentType.GetProperty(parentInfo.PropertyName); } else { var columAttribute = ParentInfo.GetCustomAttributes(typeof(ColumnAttribute), true); if (columAttribute.Length > 0) { ColumnName = (columAttribute[0] as ColumnAttribute).ColumnName; } } ParentKey = new ORMHelper().GetKeyPropertyInfo(ParentInfo.PropertyType); if (ParentKey != null) { command.Parameters.Add(new SqlParameter("@" + ColumnName, ParentKey.GetValue(ob, null) ?? "")); WhereSql = "where [" + ColumnName + "]=@" + ColumnName ; } for (int i = 0; i < Properties.Length; i++) { if (Properties[i] != ParentInfo) { foreach (var Col in helper.GetColNameAttributes(Properties[i])) { String paramName = "@" + helper.GetColName(Col, Properties[i]); string ColName = helper.GetColName(Col, Properties[i]); if (Col.NoMap) { continue; } ColName = Col.ColumnName; if (String.IsNullOrEmpty(ColName)) { ColName = Properties[i].Name; } Cols += TableName + ".[" + ColName + "],"; // } } } } command.CommandText = "SELECT " + Cols.Substring(0, Cols.Length - 1) + " FROM " + TableName + WhereSql; //+ "(" + return(command); }
public System.Data.IDbCommand GenerateDynamicUpateSql <T>(T ob) { //初始化一个Command System.Data.SqlClient.SqlCommand command = null; var TargetType = typeof(T); var patternfactory = new MSSQLScriptSqlPatternFactory(); var helper = new ORMHelper(); command = new System.Data.SqlClient.SqlCommand(); String TableName = helper.GetTable(TargetType); var Properties = TargetType.GetProperties(); String Cols = ""; String update = "SELECT "; String WhereSql = ""; for (int i = 0; i < Properties.Length; i++) { var attribute = Properties[i].GetCustomAttributes(typeof(DynamicColumnAttribute), true); if (attribute.Length > 0) { var dynamicAttr = attribute[0] as DynamicColumnAttribute; var proerpty = TargetType.GetProperty(dynamicAttr.SourcePropertyName); if (proerpty != null) { var colattributes = helper.GetColNameAttributes(Properties[i]); if (colattributes.Count > 0) { if (colattributes[0].NoMap) { continue; } else { string ColName = helper.GetColName(colattributes[0], Properties[i]); String paramName = "@" + dynamicAttr.SourcePropertyName; ColName = TableName + ".[" + ColName + "]"; command.Parameters.Add(paramName, proerpty.GetValue(ob, new object[] {})); Cols += ColName + "=" + ColName + (dynamicAttr.Operation == DanymicOperationType.Add?"+":"-") + paramName + ","; } } } } else { var cols = helper.GetColNameAttributes(Properties[i]); if (cols.Count > 0) { foreach (var col in cols) { if (col.IsKey == true && String.IsNullOrEmpty(col.KeyGroupName)) { var colname = helper.GetColName(col, Properties[i]); var paramName = "@Pre" + colname; WhereSql = " WHERE " + TableName + ".[" + colname + "]=" + paramName; command.Parameters.Add(paramName, Properties[i].GetValue(ob, new object[] {})); } } } } } command.CommandText = "UPDATE " + TableName + " SET " + Cols.Substring(0, Cols.Length - 1) + WhereSql; //selectedCommand.Add(TargetType, command); return(command); }
/// <summary> /// /// </summary> /// <param name="ob"></param> /// <param name="cxt"></param> /// <param name="TargetType"></param> /// <returns></returns> /// <remarks> /// 2013.8.7 /// 将MSSQLScriptSqlPatternFactory改为可以得到列信息和名称信息 /// </remarks> private System.Data.IDbCommand GenerateCreateSql(object ob, IDBContext cxt, Type TargetType) { System.Data.SqlClient.SqlCommand command = null; var patternFactory = new MSSQLScriptSqlPatternFactory(); if (insertCommand.ContainsKey(TargetType) == false) { //得到表名 command = new System.Data.SqlClient.SqlCommand(); String TableName = helper.GetTable(TargetType); var Properties = TargetType.GetProperties(); String Cols = ""; String Values = ""; for (int i = 0; i < Properties.Length; i++) { if (Properties[i].PropertyType.IsArray) { continue; } var attribute = helper.GetColNameAttributes(Properties[i]); String paramName = "@" + Properties[i].Name; string ColName = ""; if (attribute.Count > 0) { for (int k = 0; k < attribute.Count; k++) { var Col = attribute[k]; if (Col.NoMap == true) { continue; } else if (Col.IsAutoGenerate == GenerationType.OnInSert) { var auto = Properties[i].GetCustomAttributes(typeof(SerialNoObject), true); if (auto.Length > 0) { var seriSetting = auto[0] as SerialNoObject; Properties[i].SetValue(ob, cxt.GetSerialNo(seriSetting.SerialPre, seriSetting.Len, seriSetting.DateFormateStr, ""), null); } else { continue; } } ColName = helper.GetColName(Col, Properties[i]); paramName = "@" + ColName; var valueObject = new ORMHelper().GetDbObject(Properties[i], ob, Col.PropertyName); if (valueObject is DateTime && valueObject != null && String.IsNullOrEmpty(Col.FormatStr) == false) { valueObject = ((DateTime)valueObject).ToString(Col.FormatStr); } Cols += "[" + ColName + "],"; Values += paramName + ","; command.Parameters.Add(new System.Data.SqlClient.SqlParameter(paramName, valueObject ?? "")); } } } command.CommandText = "INSERT " + TableName + "(" + Cols.Substring(0, Cols.Length - 1) + ") VALUES(" + Values.Substring(0, Values.Length - 1) + ")"; insertCommand.Add(TargetType, command); //return command; } command = (insertCommand[TargetType] as System.Data.SqlClient.SqlCommand).Clone(); SetValues(ob, command, TargetType, "", true, cxt); return(command); }