/// <summary> /// 根据实体类内部的属性字段名称,获取对应的数据库字段长度 /// </summary> /// <param name="fieldName">属性字段名称</param> /// <param name="entity">要访问的实体类对象</param> /// <returns></returns> public SimplyField GetPropertyFieldSize(string fieldName, EntityBase entity) { if (entity == null) { entity = Activator.CreateInstance(this.entityType) as EntityBase; } return(entity.GetStringFieldSize(fieldName)); }
/// <summary> /// 为实体类的一个属性创建对应的数据库表的列的脚本 /// </summary> /// <param name="db"></param> /// <param name="entity"></param> /// <param name="field"></param> /// <returns></returns> public string CreateTableColumnScript(AdoHelper db, EntityBase entity, string field) { Type t = this.GetPropertyType(field); object defaultValue = null; if (t == typeof(string)) { defaultValue = ""; } else { defaultValue = Activator.CreateInstance(t); } IDataParameter para = db.GetParameter(field, defaultValue); //需要再获取参数长度 string temp = "[" + field + "] " + db.GetNativeDbTypeName(para); if (t == typeof(string)) { temp = temp + "(" + entity.GetStringFieldSize(field) + ")"; } //identity(1,1) primary key if (entity.PrimaryKeys.Contains(field)) { temp = temp + " PRIMARY KEY"; } if (field == entity.IdentityName) { if (db.CurrentDBMSType == PWMIS.Common.DBMSType.SqlServer || db.CurrentDBMSType == PWMIS.Common.DBMSType.SqlServerCe) { temp = temp + " IDENTITY(1,1)"; } else if (db.CurrentDBMSType == PWMIS.Common.DBMSType.Access && entity.PrimaryKeys.Contains(field)) { temp = "[" + field + "] " + " autoincrement PRIMARY KEY "; } else { if (db.CurrentDBMSType == PWMIS.Common.DBMSType.SQLite) { temp = temp + " autoincrement"; } } } return(db.GetPreparedSQL(temp)); }
/// <summary> /// 将字典形式的参数数据,转换成数据库格式的参数素组 /// </summary> /// <param name="dictPara"></param> /// <returns></returns> protected internal static IDataParameter[] GetParameters(Dictionary <string, TableNameField> dictPara, AdoHelper db) { if (dictPara == null) { return(null); } IDataParameter[] paras = new IDataParameter[dictPara.Count]; int index = 0; foreach (string key in dictPara.Keys) { object Value = dictPara[key]; if (Value is IDataParameter) { paras[index] = (IDataParameter)Value; } else { string paraName = key.StartsWith("@")?key.Substring(1):key; //参数名无需加上 ParameterChar //if (!key.StartsWith(db.GetParameterChar)) // paraName = db.GetParameterChar + key.Substring(1); var tnf = dictPara[key]; paras[index] = db.GetParameter(paraName, tnf.FieldValue); //为字符串类型的参数指定长度 edit at 2012.4.23 //增加判断tnf.Name!=null,这可能是因为使用了自定义查询的SqlMap的OQL,感谢网友 吉林-stdbool 发现此问题 if (tnf.Name != null && paras[index].Value != null && paras[index].Value.GetType() == typeof(string)) { //增加字符串长度的检查,如果值得长度大于定义的长度,抛出异常提示 2014.10.21 int size = EntityBase.GetStringFieldSize(tnf.Name, tnf.Field); int length = paras[index].Value.ToString().Length; if (length > size) { throw new NotSupportedException("当前实体类映射的字段" + paraName + " 长度没有定义或者长度小于了当前实际值的长度:" + length + ",请在实体类定义里面使用 setProperty 的重载方法指定合适的字段长度。"); } ((IDbDataParameter)paras[index]).Size = size; } } index++; } return(paras); }
/// <summary> /// 为实体类的一个属性创建对应的数据库表的列的脚本 /// </summary> /// <param name="db"></param> /// <param name="entity"></param> /// <param name="field"></param> /// <returns></returns> public string CreateTableColumnScript(AdoHelper db, EntityBase entity, string field) { Type t = this.GetPropertyType(field); object defaultValue = null; if (t == typeof(string)) { defaultValue = ""; } else if (t == typeof(byte[])) { defaultValue = new byte[1];//这里只是构造默认值,不需要实际的长度 } else { defaultValue = Activator.CreateInstance(t); } IDataParameter para = db.GetParameter(field, defaultValue); //需要再获取参数长度 string temp = ""; if (t == typeof(string)) { SimplyField sf = entity.GetStringFieldSize(field); int length = sf.FieldLength; if (length == 0) //实体类未定义属性字段的长度 { string fieldType = "text"; if (db is SqlServer) //此处要求SqlServer 2005以上,SqlServer2000 不支持 { fieldType = "varchar(max)"; } if (db.CurrentDBMSType == DBMSType.SqlServerCe) { fieldType = "ntext"; } temp = temp + "[" + field + "] " + fieldType; } else if (length > 0) { //并不是所有数据库都支持nvarchar,有关数据库字符串类型的详细支持,请看 http://www.cnblogs.com/hantianwei/p/3152517.html string fieldType = string.Empty; if (sf.FieldDbType == DbType.String) { fieldType = "nvarchar"; } else if (sf.FieldDbType == DbType.AnsiString) { fieldType = "varchar"; } else if (sf.FieldDbType == DbType.AnsiStringFixedLength) { fieldType = "char"; } else if (sf.FieldDbType == DbType.StringFixedLength) { fieldType = "nchar"; } else if (db.CurrentDBMSType == DBMSType.SqlServer || db.CurrentDBMSType == DBMSType.Oracle || db.CurrentDBMSType == DBMSType.SqlServerCe) { fieldType = "nvarchar"; } else { fieldType = "varchar"; } temp = temp + "[" + field + "] " + fieldType + "(" + length + ")"; } else if (length < 0) { temp = temp + "[" + field + "] varchar" + "(" + length + ")"; } } else if (t == typeof(byte[])) //感谢CSDN网友 ccliushou 发现此问题,原贴:http://bbs.csdn.net/topics/391967495 { int length = entity.GetStringFieldSize(field).FieldLength; temp = temp + "[" + field + "] " + db.GetNativeDbTypeName(para); if (length == 0) { temp = temp + "(max)"; } else { temp = temp + "(" + length + ")"; } } else { temp = temp + "[" + field + "] " + db.GetNativeDbTypeName(para); } if (field == entity.IdentityName) { if (db.CurrentDBMSType == PWMIS.Common.DBMSType.SqlServer || db.CurrentDBMSType == PWMIS.Common.DBMSType.SqlServerCe) { temp = temp + " IDENTITY(1,1)"; } else if (db.CurrentDBMSType == PWMIS.Common.DBMSType.Access) { temp = temp.Replace("Integer", " autoincrement"); } else if (db.CurrentDBMSType == PWMIS.Common.DBMSType.SQLite) { temp = temp + " autoincrement"; } else if (db.CurrentDBMSType == PWMIS.Common.DBMSType.MySql) { temp = temp + " AUTO_INCREMENT"; } else if (db.CurrentDBMSType == PWMIS.Common.DBMSType.PostgreSQL) { temp = temp + " DEFAULT nextval('" + entity.TableName + "_" + entity.IdentityName + "_" + "seq'::regclass) NOT NULL"; } else { //Oracle 采用序列和触发器,这里不处理 } } //identity(1,1) primary key //Access 要求主键申明必须在自增之后,否则语法错误 if (entity.PrimaryKeys.Contains(field)) { temp = temp + " PRIMARY KEY"; } return(db.GetPreparedSQL(temp)); }
/// <summary> /// 根据OQL查询数据获得DataReader。如果指定single=真,将执行优化的查询以获取单条记录 /// </summary> /// <param name="oql">OQL表达式</param> /// <param name="db">当前数据库访问对象</param> /// <param name="factEntityType">实体类类型</param> /// <param name="single">是否只查询一条记录</param> /// <returns>DataReader</returns> public static IDataReader ExecuteDataReader(OQL oql, AdoHelper db, Type factEntityType, bool single) { string sql = ""; Dictionary <string, object> Parameters = null; //处理用户查询映射的实体类 if (oql.EntityMap == PWMIS.Common.EntityMapType.SqlMap) { if (CommonUtil.CacheEntityMapSql == null) { CommonUtil.CacheEntityMapSql = new Dictionary <string, string>(); } if (!CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table)) { string tempView = GetMapSql(factEntityType); CommonUtil.CacheEntityMapSql.Add(oql.sql_table, tempView); } sql = oql.GetMapSQL(CommonUtil.CacheEntityMapSql[oql.sql_table]); //如果用户本身没有初始化参数对象,则这里声明一个 edit at 2012.11.16 Parameters = oql.InitParameters ?? new Dictionary <string, object>(); if (oql.Parameters != null && oql.Parameters.Count > 0) { foreach (string name in oql.Parameters.Keys) { Parameters.Add(name, oql.Parameters[name]); } } } else if (oql.EntityMap == PWMIS.Common.EntityMapType.StoredProcedure) { string script = ""; if (CommonUtil.CacheEntityMapSql == null) { CommonUtil.CacheEntityMapSql = new Dictionary <string, string>(); } //获取SQL-MAP脚本 if (CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table)) { script = CommonUtil.CacheEntityMapSql[oql.sql_table]; } else { script = GetMapSql(factEntityType); CommonUtil.CacheEntityMapSql.Add(oql.sql_table, script); } //对SQL-MAP格式的参数进行解析 SqlMap.SqlMapper mapper = new PWMIS.DataMap.SqlMap.SqlMapper(); mapper.DataBase = db; //解析存储过程名称 sql = mapper.FindWords(mapper.GetScriptInfo(script), 0, 255); //由于是存储过程,需要特殊处理,调用 FindWords方法 //解析参数 IDataParameter[] paras = mapper.GetParameters(script); if (oql.InitParameters != null && oql.InitParameters.Count > 0) { try { foreach (IDataParameter para in paras) { string key = para.ParameterName.TrimStart(db.GetParameterChar.ToCharArray()); para.Value = oql.InitParameters[key]; } } catch (KeyNotFoundException exKey) { throw new KeyNotFoundException("'存储过程实体类'的初始化参数中没有找到指定的参数名,请检查参数定义和设置。", exKey); } catch (Exception ex) { throw ex; } } else { if (paras.Length > 0) { throw new Exception("当前'存储过程实体类'需要提供初始化参数,请设置OQL对象的InitParameters属性"); } } return(db.ExecuteDataReader(sql, CommandType.StoredProcedure, paras)); } else { //edit at 2013-2-25 处理非SQLSERVER的参数名前缀问题 if (db is SqlServer) { sql = oql.ToString(); } else { sql = oql.ToString().Replace("@", db.GetParameterChar); } Parameters = oql.Parameters; } //处理实体类分页 2010.6.20 if (oql.PageEnable && !single) { switch (db.CurrentDBMSType) { case PWMIS.Common.DBMSType.Access: case PWMIS.Common.DBMSType.SqlServer: case PWMIS.Common.DBMSType.SqlServerCe: //如果含有Order By 子句,则不能使用主键分页 if (oql.HaveJoinOpt || sql.IndexOf("order by", StringComparison.OrdinalIgnoreCase) > 0) { sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.SqlServer, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); } else { if (oql.PageOrderDesc) { sql = PWMIS.Common.SQLPage.GetDescPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); } else { sql = PWMIS.Common.SQLPage.GetAscPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); } } break; //case PWMIS.Common.DBMSType.Oracle: // sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.Oracle, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); // break ; //case PWMIS.Common.DBMSType.MySql: //case PWMIS.Common.DBMSType.SQLite: // sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.MySql, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); // break; //case PWMIS.Common.DBMSType.PostgreSQL: // sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.PostgreSQL, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); // break; default: sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(db.CurrentDBMSType, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); break; } } IDataReader reader = null; if (Parameters != null && Parameters.Count > 0) { int fieldCount = Parameters.Count; IDataParameter[] paras = new IDataParameter[fieldCount]; int index = 0; foreach (string name in Parameters.Keys) { paras[index] = db.GetParameter(name, Parameters[name]); //为字符串类型的参数指定长度 edit at 2012.4.23 if (paras[index].Value != null && paras[index].Value.GetType() == typeof(string)) { string field = FindFieldNameInSql(sql, name, db.GetParameterChar); ((IDbDataParameter)paras[index]).Size = EntityBase.GetStringFieldSize(oql.sql_table, field); } index++; } if (single) { reader = db.ExecuteDataReaderWithSingleRow(sql, paras); } else { reader = db.ExecuteDataReader(sql, CommandType.Text, paras); } } else { if (single) { reader = db.ExecuteDataReaderWithSingleRow(sql); } else { reader = db.ExecuteDataReader(sql); } } return(reader); }