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