public static T SingleRow <T>(this IDbConnection conn, int pk_Int = -1, string pk_uuid = null, string sqlCondition = null) where T : new()
{
T propertyItem = new T();
FisherSchema schema = FisherUtil.ParseSchema(propertyItem.GetType());
FisherField fisherField = schema.Fields.Find(t => t.IsPrimaryKey == true);
string pkFieldName = fisherField.Name; // 主键名称
if (fisherField == null) // 检查主键是否定义
{
throw new PKIsNull(FisherMessage.PkIsNull.AddParams(schema.SchemaName));
}
string sqlString = "";
if (pk_Int > 0)
{
if (fisherField.SqlDbType.Equals(SqlDbType.Int) == false)
{
throw new IllegalType(FisherMessage.IllegalType.AddParams("Int", fisherField.Name));
}
sqlString = string.Format(" where {0}={1}", pkFieldName, pk_Int);
}
else if (string.IsNullOrEmpty(pk_uuid) == false)
{
if (fisherField.SqlDbType.Equals(SqlDbType.VarChar) == false && fisherField.SqlDbType.Equals(SqlDbType.NVarChar) == false)
{
throw new IllegalType(FisherMessage.IllegalType.AddParams("VarChar", fisherField.Name));
}
sqlString = string.Format(" where {0}=\"{1}\"", pkFieldName, pk_uuid);
}
else if (string.IsNullOrEmpty(sqlCondition) == false)
{
sqlString = sqlCondition;
}
FisherResult <T> result = Select <T>(conn, sqlString);
if (result.Result.Count > 0)
{
return(result.Result[0]);
}
else
{
return(propertyItem);
}
}
/// <summary>
/// 解析对象vo==>LjkSchema类
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
internal static FisherSchema ParseSchema(Type type)
{
FisherSchema schema = new FisherSchema();
schema.SchemaName = type.Name;
schema.MethodInfos = type.GetMethods().ToList();
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo propertyInfo in propertyInfos)
{
Attribute[] flags = Attribute.GetCustomAttributes(propertyInfo);
if (flags.Length > 0)
{
FisherField dapperFlag = flags[0] as FisherField;
schema.Fields.Add(dapperFlag);
}
}
return(schema);
}
public static T SignleRow <T>(int id, params string[] queryFields) where T : new()
{
T item = new T();
FisherSchema schema = FisherUtil.ParseSchema(item.GetType());
FisherField pkField = schema.Fields.Find(t => t.KEY_SEQ > 0 || t.SqlDbType == SqlDbType.UniqueIdentifier);
if (pkField == null)
{
throw new PKIsNull(FisherMessage.PkIsNull.Message);
}
string sqlWhere = string.Format(" where {0}={1}", pkField.Name, id);
FisherResult <T> result = Query <T>(sqlWhere: sqlWhere, sqlOrderBy: null, pageSize: -1, pageIndex: -1, queryFields: queryFields);
if (result.Result != null && result.Result.Count > 0)
{
item = result.Result[0];
}
return(item);
}
public static FisherResult <T> Select <T>(this IDbConnection conn, string sqlCondition = null, string orderby = null, int pageSize = -1, int pageIndex = -1, params string[] selectFields) where T : new()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
T propertyItem = new T();
string commandText = "";
string countText = "";
// 是否启用分页，pageIndex，pageSize需同时满足 > 0
bool pageEnabled = (pageIndex > 0 && pageSize > 0) ? true : false;
FisherResult <T> result = new FisherResult <T>();
result.PageSize = pageSize;
result.PageIndex = pageIndex;
FisherSchema schema = FisherUtil.ParseSchema(propertyItem.GetType());
string pkFieldName = schema.Fields.Find(t => t.IsPrimaryKey == true).Name; // 主键名称
if (string.IsNullOrEmpty(pkFieldName)) // 检查主键是否定义
{
result.Success = Result.Exception;
result.Exception = new PKIsNull(schema.SchemaName);
}
else
{
/* **********************************************
* 检查用户是否指定查询字段
* 如果指定，则按照用户指定的字段执行查询
* 如果未指定，则默认查询所有字段
* **********************************************
*/
if (selectFields != null)
{
foreach (string field in selectFields) // 依次检查用户指定的字段是否在表中存在。
{
FisherField fisherField = schema.Fields.Find(t => t.Name.Equals(field));
if (fisherField == null) // 用户指定了非法的字段，也就是指定了不是表中的字段。
{
result.Success = Result.Exception;
result.Exception = new IllegalField(FisherMessage.IllegalField.AddParams(field, schema.SchemaName));
break;
}
fisherField.QueryOption = QueryOption.Include; // 标记为Include
}
}
commandText += string.Format("select {0} from [{1}]", schema.GetIncludeSQLFields, schema.SchemaName);
/* ************************************************
* 控制查询条件，如果用户指定了查询条件，则以用户的查询条件为准
* 在分页的情况下：
* - 如果用户未指定排序方式，则默认按照主键（PK）排序
* - 如果用户指定了排序条件，则以用户指定的排序方式为准
* 不分页的情况，以用户实际order by字段为准
*/
commandText += " " + sqlCondition; // Where条件，普通查询增加条件
if (pageEnabled)
{
// 分页统计记录总数，包括分页查询条件
countText = string.Format("select count({0}) from {1} {2}", pkFieldName, schema.SchemaName, sqlCondition);
if (string.IsNullOrEmpty(orderby)) // throw new PaginationNeedOrderBy("分页必须同时指定[Order By xxx] 参数");
{
commandText += " order by " + pkFieldName;
}
else
{
commandText += " " + orderby;
}
commandText += string.Format(" offset {0} rows fetch next {1} rows only", (pageIndex - 1) * pageSize, pageSize);
// 统计分页记录总数
IDbCommand countCmd = conn.CreateCommand();
countCmd.CommandText = countText;
var totalRecord = countCmd.ExecuteScalar();
result.TotalRecord = FisherUtil.ParseInt(totalRecord);
}
else
{
commandText += " " + orderby;
}
try {
IDbCommand command = conn.CreateCommand();
command.CommandText = commandText;
result.CommondText = commandText;
List <string> includeFields = schema.Fields.Where(t => t.QueryOption.Equals(QueryOption.Include)).Select(t => t.Name).ToList();
if (includeFields == null || includeFields.Count <= 0)
{
includeFields = schema.Fields.Where(t => t.QueryOption.Equals(QueryOption.Exclude) == false).Select(t => t.Name).ToList();
}
IDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
T newItem = new T();
foreach (string temp in includeFields)
{
var getDBValue = dataReader[temp];
if (getDBValue == DBNull.Value) // 忽略DBNull值
{
continue;
}
MethodInfo setMethod = schema.MethodInfos.Find(t => t.Name.Equals("set_" + temp, StringComparison.CurrentCultureIgnoreCase));
setMethod.Invoke(newItem, new object[] { getDBValue });
}
result.Result.Add(newItem);
}
dataReader.Close();
dataReader = null;
} catch (Exception excDBCommand) {
result.Success = Result.Exception;
result.Exception = excDBCommand;
}
}
return(result);
}
/// <summary>
/// 执行Insert或者Update操作，FisherCore.Save<T>(T item){ ... }
/// <para>系统会根据item是否包括pk主键值来判断操作类型：</para>
/// <para> - 包括pk，执行Update操作</para>
/// <para> - 不包括pk，执行Insert操作，因为pk为数据库自动生成（支持int自增类型，及UUID自动生成类型主键）</para>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="conn"></param>
/// <param name="vo"></param>
/// <returns></returns>
internal static FisherResult Save <T>(this IDbConnection conn, T vo)
{
FisherResult result = new FisherResult();
FisherSchema schema = FisherUtil.ParseSchema(vo.GetType());
FisherField pkField = null;
/* **************************************************************************
* 构造Insert into [xxx] values(xxx,xxx,xxx,...);
* 也就是构造最原始的sql代码
* 重点：
* - 采用临时表的方式实现，并且通过该临时表获得新插入的“主键值”
* 逻辑：
* - 首先判断是否是主键，跳过主键不更新
* - 然后构造values(...)内容
*/
string tmpDeclareField = "";
string sqlFieldList = "";
string sqlValueList = "";
string outputField = "";
bool isFirstItem = true;
foreach (FisherField fisherField in schema.Fields)
{
/* ****************************************
* 自增int类型，及UUID类型主键，跳过
* 注意：
* - int类型需设置标识自动增长
* - varchar类型，需将字段设置为uniqueidentifier类型，且默认值为：(newid())
* - 这样就由数据库“自动产生”主键唯一标识
*/
if (fisherField.KEY_SEQ > 0 || fisherField.SqlDbType == SqlDbType.UniqueIdentifier)
{
pkField = fisherField;
tmpDeclareField += string.Format("[{0}] {1}", fisherField.Name, Enum.GetName(typeof(SqlDbType), fisherField.SqlDbType));
outputField = string.Format("OUTPUT INSERTED.[{0}] INTO @tmp_tbl", pkField.Name);
continue;
}
MethodInfo method = schema.MethodInfos.Find(t => t.Name.Equals("get_" + fisherField.Name, StringComparison.CurrentCultureIgnoreCase));
/* *****************************************************
* 检查是否是必填字段，如果数据为必填，则不允许为空
* 然后根据不同的数据类型，构造values字符串，如果字段类型为varchar，则需要增加'<xxx>'表示
*/
if (isFirstItem == false)
{
sqlFieldList += ",";
sqlValueList += ",";
}
sqlFieldList += string.Format("[{0}]", fisherField.Name);
var getMethodValue = method.Invoke(vo, null);
if (getMethodValue == null)
{
if (fisherField.CanotDBNull == true)
{
throw new FieldNotAllowNull(FisherMessage.FieldNotAllowNull.AddParams(fisherField.Name));
}
else
{
sqlValueList += "null";
}
}
else
{
switch (fisherField.SqlDbType)
{
case SqlDbType.Bit:
sqlValueList += FisherUtil.ParseBoolToBit(getMethodValue);
break;
case SqlDbType.Int:
case SqlDbType.BigInt:
case SqlDbType.Float:
case SqlDbType.Real:
case SqlDbType.SmallInt:
case SqlDbType.TinyInt:
sqlValueList += getMethodValue;
break;
default:
sqlValueList += string.Format("'{0}'", getMethodValue);
break;
}
}
isFirstItem = false;
}
/* ***********************************************************************
* 构造真实的insert语句，格式：
* - insert into [schema]([filedlist...])
* OUTPUT INSERTED.[uuid] INTO @tmp_tbl
* values([values...]);
* */
string realDeclare = string.Format("DECLARE @tmp_tbl TABLE({0});", tmpDeclareField);
string realSQL = string.Format("insert into [{0}]({1}) {2} values({3});", schema.SchemaName, sqlFieldList, outputField, sqlValueList);
string realIdentity = "SELECT * FROM @tmp_tbl;";
result.CommondText = realSQL;
if (result.Exception == null)
{
try {
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
IDbCommand command = conn.CreateCommand();
command.CommandText = realDeclare + realSQL + realIdentity; //"DECLARE @tmp_tbl TABLE([uuid] uniqueidentifier, [code] nvarchar(50));INSERT INTO[dbo].[aaa1] ([code]) OUTPUT INSERTED.[uuid], INSERTED.[code] INTO @tmp_tbl VALUES(N'a1');SELECT* FROM @tmp_tbl";
var resultDeclare = command.ExecuteScalar();
if (pkField.SqlDbType == SqlDbType.Int)
{
result.Pk_Id = FisherUtil.ParseInt(resultDeclare);
}
else
{
result.Pk_UUID = FisherUtil.IngoreNull(resultDeclare);
}
result.Success = Result.True;
} catch (Exception excCommand) {
result.Success = Result.Exception;
result.Exception = excCommand;
}
}
return(result);
}
