/// <summary> /// 设置一个命令 /// </summary> /// <param name="ob"></param> /// <param name="command"></param> /// <param name="TagetType"></param> /// <param name="cxt"></param> public void SetValues(object ob, System.Data.SqlClient.SqlCommand command, Type TagetType, String ParamPre = "", bool GernerateSerialNo = false, IDBContext cxt = null) { var Propertitest = TagetType.GetProperties(); var factory = new MSSQLScriptSqlPatternFactory(); foreach (var property in Propertitest) { foreach (var colattr in helper.GetColNameAttributes(property)) { if (colattr.NoMap) { continue; } var paramName = "@" + ParamPre + helper.GetColName(colattr, property); object propertyob = new ORMHelper().GetDbObject(property, ob, colattr.PropertyName) ?? ""; if (command.Parameters.Contains(paramName)) { if (GernerateSerialNo && cxt != null && String.IsNullOrEmpty( //(command.Parameters[paramName].Value ?? "" (new ORMHelper().GetDbObject(property, ob, colattr.PropertyName) ?? "" ).ToString() ) == true ) { var auto = property.GetCustomAttributes(typeof(SerialNoObject), true); if (auto.Length > 0) { var seriSetting = auto[0] as SerialNoObject; command.Parameters[paramName].Value = cxt.GetSerialNo(seriSetting.SerialPre, seriSetting.Len, seriSetting.DateFormateStr, ""); property.SetValue(ob, command.Parameters[paramName].Value, null); } else { command.Parameters[paramName].Value = propertyob; } //else if(String.IsNullOrEmpty( (command.Parameters[paramName].Value ?? "").ToString())==false) //command.Parameters[paramName].Value = new ORMHelper().GetDbObject(property, ob, colattr.PropertyName) ?? ""; } else { var insertDb = new ORMHelper().GetDbObject(property, ob, colattr.PropertyName); if (insertDb is DateTime && insertDb != null && String.IsNullOrEmpty(colattr.FormatStr) == false) { insertDb = ((DateTime)insertDb).ToString(colattr.FormatStr); } command.Parameters[paramName].Value = insertDb ?? ""; } } } } }
/// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <param name="ob"></param> /// <returns></returns> /// <remarks> /// 2013.8.7</remarks> public System.Data.IDbCommand GenerateSaveSql <T>(T ob) { if (!updateCommand.ContainsKey(typeof(T))) { //初始化一个Command System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(); var TargetType = typeof(T); //得到表名 var factory = new MSSQLScriptSqlPatternFactory();//.GetTable(TargetType); var table = helper.GetTableAttribute(TargetType); String TableName = helper.GetTable(TargetType); var Properties = TargetType.GetProperties(); String Cols = ""; for (int i = 0; i < Properties.Length; i++) { var attribute = helper.GetColNameAttributes(Properties[i]); string ColName = ""; if (attribute.Count > 0) { for (int j = 0; j < attribute.Count; j++) { var Col = (ColumnAttribute)attribute[j]; if (Col.IsAutoGenerate != GenerationType.Never || Col.NoMap == true || Col.IsIdentify) { continue; //如果自动生成 } else { ColName = helper.GetColName(Col, Properties[i]); } String paramName = "@" + ColName; // ColName = factory.GetColName(Col, Properties[i]); var dbValue = new ORMHelper().GetDbObject(Properties[i], ob, Col.PropertyName); // if (dbValue != null) // { Cols += "[" + ColName + "]=" + paramName + ","; command.Parameters.Add(new System.Data.SqlClient.SqlParameter(paramName, dbValue ?? "")); } } } var parity = new MSSQLScriptSqlPatternFactory().GetExistSql <T>(Properties, ob, true, "Pre"); command.Parameters.AddRange(parity.Params.ToArray()); command.CommandText = "Update " + TableName + " SET " + Cols.Substring(0, Cols.Length - 1) + " WHERE " + parity.SqlScript; updateCommand.Add(typeof(T), command); } var upatecommand = (updateCommand[typeof(T)] as SqlCommand).Clone(); SetValues <T>(ob, upatecommand); SetValues <T>(ob, upatecommand, "Pre"); // + " SET " + Cols.Substring(0, Cols.Length - 1) + " WHERE " + parity.SqlScript; return(upatecommand); }
/// <summary> /// 生成一条记录是否在数据库中存在 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="ob"></param> /// <returns></returns> public System.Data.IDbCommand GenerateExitsSql <T>(T ob) { var TargetType = typeof(T); String TableName = helper.GetTable(TargetType); var Properties = TargetType.GetProperties(); MSSQLScriptSqlPatternFactory factory = new MSSQLScriptSqlPatternFactory(); System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(); String sql = "Select * From " + TableName + " Where "; var parity = factory.GetExistSql <T>(Properties, ob, false); command.Parameters.AddRange(parity.Params.ToArray()); command.CommandText = sql + parity.SqlScript; return(command); }
/// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> /// <remarks> /// 2016.4.16 /// 加了一个简单的限定,对加密的东西不做处理。。 /// 有待完善 /// </remarks> public System.Data.IDbCommand GenerateSelectSql <T>() { //初始化一个Command System.Data.SqlClient.SqlCommand command = null; var TargetType = typeof(T); var patternfactory = new MSSQLScriptSqlPatternFactory(); if (selectedCommand.ContainsKey(TargetType) == false) { command = new System.Data.SqlClient.SqlCommand(); String TableName = helper.GetTable(TargetType); var Properties = TargetType.GetProperties(); String Cols = ""; String SelectedSql = "SELECT "; for (int i = 0; i < Properties.Length; i++) { var attributes = helper.GetColNameAttributes(Properties[i]); foreach (var attribute in attributes.Where(p => p.EncrpytType == EncryptType.NoEncrpty)) { if (attribute.NoMap) { continue; } string ColName = helper.GetColName(attribute, Properties[i]); String paramName = "@" + ColName; Cols += TableName + ".[" + ColName + "],"; } } command.CommandText = "SELECT " + Cols.Substring(0, Cols.Length - 1) + " FROM " + TableName; selectedCommand.Add(TargetType, command); return(command); } else { return(selectedCommand[TargetType] as System.Data.IDbCommand); } }
public System.Data.IDbCommand GenerateDeleteSql <T>(T ob) { var TargetType = typeof(T); if (deleteCommand.ContainsKey(TargetType) == false) { String TableName = helper.GetTable(TargetType); var Properties = TargetType.GetProperties(); MSSQLScriptSqlPatternFactory factory = new MSSQLScriptSqlPatternFactory(); System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(); String sql = "DELETE " + TableName + " Where "; var parity = factory.GetExistSql <T>(Properties, ob); command.Parameters.AddRange(parity.Params.ToArray()); command.CommandText = sql + parity.SqlScript; deleteCommand.Add(TargetType, command); } var deletedcommand = (deleteCommand[TargetType] as SqlCommand).Clone(); SetValues <T>(ob, deletedcommand); return(deletedcommand); }
private void SetObject(object ob, DataRow Row, PropertyInfo[] properties, bool GetDetail = false) { MSSQL.MSSQLScriptSqlPatternFactory patternFactory = new MSSQL.MSSQLScriptSqlPatternFactory(); if (ob is Soway.Data.IObjectFromDataBase) { (ob as Soway.Data.IObjectFromDataBase).Row = Row; } ORMHelper helper = new ORMHelper(); for (int j = 0; j < properties.Length; j++) { try { var attribute = helper.GetColNameAttributes(properties[j]); object propertyob = new object(); var isBO = new ORMHelper().IsBusinessType(properties[j].PropertyType); var propertyType = properties[j].PropertyType; for (int i = 0; i < attribute.Count; i++) { var attr = attribute[i]; if (attr.NoMap) { continue; } String ColName = helper.GetColName(attr, properties[j]); if (String.IsNullOrEmpty(ColName)) { continue; } if (Row.Table.Columns.Contains(ColName)) { var dbObject = Row[ColName]; if ((dbObject is DBNull)) { continue; } if (propertyType == typeof(DateTime) && String.IsNullOrEmpty(attr.FormatStr) == false && dbObject != null) { dbObject = DateTime.ParseExact(dbObject.ToString(), attr.FormatStr, null); } if (attribute.Count == 1) { if (isBO) { if (GetDetail) { if (!(dbObject is DBNull)) { properties[j].SetValue(ob, DBContext.GetMethod.MakeGenericMethod( new Type[] { properties[j].PropertyType, properties[j].PropertyType }).Invoke( this, new object[] { Row[ColName] }), new object[] { }); } } else { propertyob = propertyType.Assembly.CreateInstance(propertyType.FullName); properties[j].SetValue(ob, propertyob , new object[] { }); PropertyInfo key; if (String.IsNullOrEmpty(attr.PropertyName)) { key = new ORMHelper().GetKeyPropertyInfo(propertyType); } else { key = propertyType.GetProperty(attr.PropertyName); } if (!(dbObject is DBNull)) { key.SetValue(propertyob, dbObject, new object[] { }); } } } else if (String.IsNullOrEmpty(attr.PropertyName)) { if (!(dbObject is DBNull)) { properties[j].SetValue(ob, dbObject is DBNull ? null : dbObject, new object[] { }); } } else if (GetDetail && isBO) { if (!(dbObject is DBNull)) { properties[j].SetValue(ob, DBContext.GetMethod.MakeGenericMethod(new Type[] { properties[j].PropertyType, properties[j].PropertyType }).Invoke( new Soway.DB.DBContext(), new object[] { Row[ColName] }), new object[] { }); } } } else { if (i == 0)//{ { propertyob = propertyType.Assembly.CreateInstance(propertyType.FullName); properties[j].SetValue(ob, propertyob, new object[] { }); } if (String.IsNullOrEmpty(attr.PropertyName) == false) { if (!(dbObject is DBNull)) { if (propertyType.GetProperty(attr.PropertyName).PropertyType == typeof(DateTime) && String.IsNullOrEmpty(attr.FormatStr) == false && dbObject != null) { dbObject = DateTime.ParseExact(dbObject.ToString(), attr.FormatStr, null); } propertyType.GetProperty(attr.PropertyName).SetValue(propertyob, dbObject, new object[] { }); } } // } } } } } catch (Exception ex) { } } }
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); }
public System.Data.IDbCommand GenerateSeletedAutoGernerateSql <T>(T ob) { //初始化一个Command System.Data.SqlClient.SqlCommand command = null; bool IsAuto = false; var TargetType = typeof(T); if (SeletctAutoGernertateCommands.ContainsKey(TargetType) == false) { //得到表名 command = new System.Data.SqlClient.SqlCommand(); var factory = new MSSQLScriptSqlPatternFactory(); String TableName = helper.GetTable(TargetType); var Properties = TargetType.GetProperties(); String Cols = null; String Values = ""; String SqlInsert = "SELECT " + TableName + "(" + Cols + ") VALUES("; String Condition = null; for (int i = 0; i < Properties.Length; i++) { if (Properties[i].PropertyType.IsArray) { continue; } var attributes = helper.GetColNameAttributes(Properties[i]);//.GetCustomAttributes(typeof(ColumnAttribute), e); foreach (var attr in attributes) { if (attr.NoMap) { continue; } String paramName = "@" + helper.GetColName(attr, Properties[i]); string ColName = helper.GetColName(attr, Properties[i]); bool IsSelected = false; IsSelected = attr.IsAutoGenerate != GenerationType.Never; if (IsSelected) { if (Properties[i].GetCustomAttributes(typeof(SerialNoObject), true).Length == 0) { if (Cols != null) { Cols += ","; } Cols += ColName; IsAuto = true; } } else { if (Condition != null) { Condition += " AND "; } Condition += "(" + ColName + "=" + paramName + " OR " + paramName + " IS NULL )"; command.Parameters.Add(new System.Data.SqlClient.SqlParameter(paramName, Properties[i].GetValue(ob, null) ?? "")); } } } if (IsAuto) { command.CommandText = "SELECT " + Cols + " FROM " + TableName + " WHERE " + Condition; } else { command = null; } SeletctAutoGernertateCommands.Add(TargetType, command); } else { if (SeletctAutoGernertateCommands[TargetType] == null) { return(null); } command = (SeletctAutoGernertateCommands[TargetType] as System.Data.SqlClient.SqlCommand).Clone(); } if (command != null) { SetValues <T>(ob, command); } return(command); }
public System.Data.IDbCommand GenerateGetNextKey <T>(object key) { //初始化一个Command System.Data.SqlClient.SqlCommand command = null; var TargetType = typeof(T); bool HaveKey = false; command = new System.Data.SqlClient.SqlCommand(); var factory = new MSSQLScriptSqlPatternFactory(); String TableName = helper.GetTable(TargetType); var Properties = TargetType.GetProperties(); String Cols = ""; String SelectedSql = "SELECT "; String WhereSql = " WHERE "; for (int i = 0; i < Properties.Length; i++) { var attribute = helper.GetColNameAttributes(Properties[i]); for (int aI = 0; aI < attribute.Count; aI++) { var Col = attribute[aI]; var ColName = helper.GetColName(Col, Properties[i]); String paramName = "@" + ColName; if (Col.NoMap) { continue; } if (String.IsNullOrEmpty(ColName)) { continue; } if (Col.IsKey && String.IsNullOrEmpty(Col.KeyGroupName)) { if (HaveKey == false) { HaveKey = true; } else { throw new Exception("没有默认的主键!"); } command.Parameters.Add(new SqlParameter(paramName, Properties[i].GetValue(key, new object[] { }))); WhereSql += "[" + ColName + "]>" + paramName + " Order by " + ColName + " ASC"; Cols = TableName + ".[" + ColName + "],"; break; } Cols += TableName + ".[" + ColName + "],"; } if (HaveKey) { break; } } command.CommandText = "SELECT Top 1 " + Cols.Substring(0, Cols.Length - 1) + " FROM " + TableName + WhereSql;//+ "(" + Cols.Substring(0, Cols.Length - 1) + ") VALUES(" + Values.Substring(0, Values.Length - 1) + ")"; // selectedCommand.Add(TargetType, command); return(command); }