ParameterSyntax(string name, MetaId metaId, DbParam param, string front, string back, bool displayValue)
{
Name = name;
MetaId = metaId;
_param = param;
_front = front;
_back = back;
_displayValue = displayValue;
}
public List <DbParam> ToParameterList()
{
List <DbParam> ret = new List <DbParam>(this.Count);
foreach (object dicVal in this._dbParams.Values)
{
DbParam dbParam = dicVal as DbParam;
if (dbParam != null)
{
ret.Add(dbParam);
}
else
{
List <DbParam> dbParamList = dicVal as List <DbParam>;
for (int i = 0; i < dbParamList.Count; i++)
{
ret.Add(dbParamList[i]);
}
}
}
return(ret);
}
public DataSet GetUserObject(AuthUser objAuthUser)
{
DbParam[] param = new DbParam[]
{
new DbParam(objAuthUser.MobileNo, typeof(System.String), "_mobile"),
new DbParam(objAuthUser.Password, typeof(System.String), "_password"),
new DbParam(objAuthUser.SchoolTenentId, typeof(System.String), "_schooltenentId"),
new DbParam(objAuthUser.IsFaculty, typeof(System.Boolean), "_isFaculty")
};
DataSet ds = db.GetDataset("sp_MobileUserMaster_Detail", param);
if (ds != null && ds.Tables.Count == 3)
{
ds.Tables[0].TableName = "LoginUser";
ds.Tables[1].TableName = "Student";
ds.Tables[2].TableName = "Notification";
}
else
{
ds = null;
}
return(ds);
}
public IEnumerable <OrderDetailForRedisRF> GetOrderDetailListByOrderNumber(string OrderNumber, string CustomerID)
{
IEnumerable <OrderDetailForRedisRF> list = new List <OrderDetailForRedisRF>();
using (SqlConnection conn = new SqlConnection(BaseAccessor._dataBase.ConnectionString))
{
try
{
DbParam[] param = new DbParam[] {
new DbParam("@OrderNumber", DbType.String, OrderNumber, ParameterDirection.Input),
new DbParam("@CustomerID", DbType.String, CustomerID, ParameterDirection.Input)
};
DataSet ds = ExecuteDataSet("Proc_WMS_RF_GetOrderDetailListByOrderNumber", param);
list = ds.Tables[0].ConvertToEntityCollection <OrderDetailForRedisRF>();
return(list);
}
catch (Exception ex)
{
list = new List <OrderDetailForRedisRF>();
return(list);
}
}
}
public string GetExamDetail(int Year)
{
if (Year == 0)
{
Year = userDetail.AccedemicStartYear;
}
DbParam[] param = new DbParam[]
{
new DbParam(userDetail.schooltenentId, typeof(System.String), "_tenentId"),
new DbParam(Year, typeof(System.Int32), "_year")
};
DataSet ds = db.GetDataset("sp_ExamDetails_SelFilter", param);
if (ds != null && ds.Tables.Count > 0)
{
Result = JsonConvert.SerializeObject(ds);
}
else
{
Result = JsonConvert.SerializeObject(new DataTable());
}
return(Result);
}
public string ExamDataInsertion(Examdetails ObjExamdetails)
{
DbParam[] param = new DbParam[]
{
new DbParam(null, typeof(System.String), "_ExamDetailId"),
new DbParam(userDetail.schooltenentId, typeof(System.String), "_SchooltenentId"),
new DbParam(ObjExamdetails.ExamDescriptionId, typeof(System.String), "_ExamDescriptionId"),
new DbParam(ObjExamdetails.Class, typeof(System.String), "_Class"),
new DbParam(ObjExamdetails.SubjectId, typeof(System.String), "_SubjectId"),
new DbParam(ObjExamdetails.ExamDate, typeof(System.DateTime), "_ExamDate"),
new DbParam(ObjExamdetails.StartTime, typeof(System.String), "_Starttime"),
new DbParam(ObjExamdetails.Duration, typeof(System.Int64), "_Duration"),
new DbParam(userDetail.AccedemicStartYear, typeof(System.Int32), "_AcademicYearFrom"),
new DbParam(userDetail.AccedemicStartYear + 1, typeof(System.Int32), "_AcademicYearTo"),
new DbParam(userDetail.UserId, typeof(System.String), "_Adminid")
};
Result = db.ExecuteNonQuery("sp_examdetails_InsUpd", param, true);
if (Result == null || Result == "")
{
Result = "";
}
return(Result);
}
public string StudenPaymentDetailService(string PayeeUid)
{
string Result = null;
DbParam[] param = new DbParam[]
{
new DbParam(PayeeUid, typeof(System.String), "_PayeeUid"),
new DbParam(userDetail.schooltenentId, typeof(System.String), "_TenentId"),
new DbParam(userDetail.AccedemicStartYear, typeof(System.Int32), "_ForYear"),
new DbParam(1, typeof(System.Int32), "_UserCode")
};
DataSet ds = db.GetDataset("sp_StudentPaymentDetail_Sel", param);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
Result = JsonConvert.SerializeObject(ds);
}
else
{
Result = JsonConvert.SerializeObject("No record found");
}
return(Result);
}
public (DataSet, string) GetLoginUserObject(AuthUser authUser)
{
string Token = null;
string ProcessingData = null;
DbParam[] param = new DbParam[]
{
new DbParam(authUser.UserId, typeof(System.String), "_mobile"),
new DbParam(authUser.Password, typeof(System.String), "_password")
};
DataSet ds = db.GetDataset("sp_GetLoginUser_Detail", param, true, ref ProcessingData);
if (ds != null && ds.Tables.Count > 0)
{
this.autoMapper = new TableAutoMapper();
UserDetail userDetail = this.autoMapper.AutoMapToObject <UserDetail>(ds.Tables[0]);
if (userDetail != null)
{
authUser.Role = Policies.Admin;
Token = GenerateToken(authUser);
beanContext.AddNewSession(string.Empty, "userdetail", userDetail, Token);
}
}
if (ds.Tables.Count == 8)
{
ds.Tables[0].TableName = "CurrentUser";
ds.Tables[1].TableName = "Classes";
ds.Tables[2].TableName = "TotalCount";
ds.Tables[3].TableName = "Menu";
ds.Tables[4].TableName = "Subject";
ds.Tables[5].TableName = "Roles";
ds.Tables[6].TableName = "ColumnMapping";
ds.Tables[7].TableName = "StateNCity";
}
return(ds, Token);
}
/// <summary>
/// Implementation of IDbParameterAssembler.SetupParameter
/// </summary>
/// <param name="parameter"></param>
/// <param name="param"></param>
public override void SetupParameter(IDbDataParameter parameter, DbParam param)
{
base.SetupParameter(parameter, param);
if (parameter is NpgsqlParameter)
{
//For PostgreSQL json
NpgsqlParameter pgsqlParameter = (NpgsqlParameter)parameter;
DbType jsonDbType = DbTypeConsts.NpgJson;
if (param.DbType == jsonDbType)
{
parameter.DbType = DbType.String;
pgsqlParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json;
}
}
else if (parameter is OracleParameter)
{
//For Oracle Clob/NClob
OracleParameter oracleParameter = (OracleParameter)parameter;
/* 针对 oracle 长文本做处理 */
string value = oracleParameter.Value as string;
if (value != null && value.Length > 2000)
{
if (param.DbType == DbType.String || param.DbType == DbType.StringFixedLength)
{
oracleParameter.OracleDbType = OracleDbType.NClob;
}
else if (param.DbType == DbType.AnsiString || param.DbType == DbType.AnsiStringFixedLength)
{
oracleParameter.OracleDbType = OracleDbType.Clob;
}
}
}
}
/// <summary>
/// 放弃待办项
/// </summary>
/// <param name="todobh"></param>
/// <returns></returns>
public IActionResult Abandon(string todobh)
{
Result result;
try
{
using (DatabaseHelper helper = DatabaseHelper.CreateByConnName("ZNoteDB"))
{
// 获取所有子项
string userbh = TokenHelper.GetUserBH();
var todobhLst = _GetAllSubTodoBH(todobh);
todobhLst.Insert(0, todobh);
string inSQL;
IEnumerable <DbParam> inParams = DbParam.GetDbParams_WhereIn(out inSQL, "todobh", todobhLst.ToArray());
string sql = $"update d_todo set state='放弃',abandontime=sysdate() where userbh=@userbh and {inSQL}";
List <DbParam> DbParams = new List <DbParam>()
{
new DbParam("userbh", userbh)
};
DbParams.AddRange(inParams);
if (helper.ExecuteNonQuery(sql, DbParams.ToArray()) > 0)
{
result = Result.Success;
}
else
{
result = ResultCode.DB_UpdateFail.GetResult();
}
}
}
catch (Exception ex)
{
Logger.log("Todo.Abandon", ex);
result = Result.CreateDefeat(ex.Message);
}
return(Json(result));
}
public string SaveSettlementBySettlementNumber(SettlementSearchCondition wc)
{
string Mess = string.Empty;
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
try
{
//string[] qtysum = wc.ActulQtyargs;
//if (qtysum.Length > 0)
//{
string str = string.Empty;
str = " and SettlementNumber='" + wc.SettlementNumber.Trim() + "' ";
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, str, ParameterDirection.Input)
};
DataTable dt = this.ExecuteDataSet("Proc_WMS_GetSettlementBySettlementNumber", dbParams).Tables[1];
//if (dt.Rows.Count == qtysum.Length)
//{
for (int i = 0; i < dt.Rows.Count; i++)
{
DbParam[] dbUpdate = new DbParam[] {
//new DbParam("@ActualQty", DbType.String,qtysum[i].ToString(),ParameterDirection.Input),
new DbParam("@ID", DbType.Int32, int.Parse(dt.Rows[i]["ID"].ToString()), ParameterDirection.Input)
};
this.ExecuteScalar("Proc_WMS_SaveSettlementBySettlementNumber", dbUpdate);
}
//}
//}
return("更新成功");
}
catch (Exception)
{
return("更新失败");
}
}
public IEnumerable <OrderDetailInfo> GetOrderDetailList2(string ReceiptNumber, string customerid, string warehousename)
{
IEnumerable <OrderDetailInfo> list = new List <OrderDetailInfo>();
using (SqlConnection conn = new SqlConnection(BaseAccessor._dataBase.ConnectionString))
{
try
{
DbParam[] param = new DbParam[] {
new DbParam("@OrderNumber", DbType.String, ReceiptNumber, ParameterDirection.Input),
new DbParam("@CustomerID", DbType.String, customerid, ParameterDirection.Input),
new DbParam("@WarehouseName", DbType.String, warehousename, ParameterDirection.Input)
};
DataSet ds = ExecuteDataSet("Proc_WMS_RF_GetOrderDetailList2", param);
list = ds.Tables[0].ConvertToEntityCollection <OrderDetailInfo>();
return(list);
}
catch (Exception ex)
{
list = new List <OrderDetailInfo>();
return(list);
}
}
}
//导出
public DataSet ExportSettlementBySettlementNumber_b(SettlementSearchCondition wc)
{
string Mess = string.Empty;
GetSettlementByConditionResponse response = new GetSettlementByConditionResponse();
try
{
//保存结算主子表
DataTable dt = new DataTable();
#region 查询条件
string sb = string.Empty;
if (!string.IsNullOrEmpty(wc.CustomerID.ToString()))
{
sb += " and c.CustomerID='" + wc.CustomerID.ToString().Trim() + "' ";
}
if (!string.IsNullOrEmpty(wc.WarehouseID.ToString()))
{
sb += " and c.WarehouseID='" + wc.WarehouseID.ToString().Trim() + "' ";
}
if (!string.IsNullOrEmpty(wc.SettlementNumber))
{
sb += " and c.SettlementNumber='" + wc.SettlementNumber.Trim() + "' ";
}
#endregion
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sb, ParameterDirection.Input)
};
DataSet ds = this.ExecuteDataSet("Proc_WMS_GetSettlementBySettlementNumber_Export", dbParams);
return(ds);
}
catch (Exception Ex)
{
return(null);
}
}
public string SaveSchoolCalendarChangesService(SchoolCalendar ObjSchoolCalendar)
{
ServiceResult ObjServiceResult = validateModalService.ValidateModalFieldsService(typeof(SchoolCalendar), ObjSchoolCalendar);
if (ObjServiceResult.IsValidModal)
{
DbParam[] param = new DbParam[]
{
new DbParam(ObjSchoolCalendar.SchoolCalendarId, typeof(System.String), "_SchoolEventUid"),
new DbParam(userDetail.schooltenentId, typeof(System.String), "_tenentUid"),
new DbParam(ObjSchoolCalendar.Title, typeof(System.String), "_Title"),
new DbParam(ObjSchoolCalendar.Description, typeof(System.String), "_Description"),
new DbParam(ObjSchoolCalendar.StartDate, typeof(System.DateTime), "_StartDate"),
new DbParam(ObjSchoolCalendar.EndDate, typeof(System.DateTime), "_EndDate"),
new DbParam(ObjSchoolCalendar.StartTime, typeof(System.String), "_StartTime"),
new DbParam(ObjSchoolCalendar.EndTime, typeof(System.String), "_EndTime"),
new DbParam(ObjSchoolCalendar.IsFullDayEvent, typeof(System.Boolean), "_IsFullDayEvent"),
new DbParam(ObjSchoolCalendar.BindUrl, typeof(System.String), "_BindUrl"),
new DbParam(userDetail.UserId, typeof(System.String), "_adminid")
};
OutParam = db.ExecuteNonQuery("sp_schoolevents_InsUpd", param, true);
}
return(OutParam);
}
public IEnumerable <StoresByGetReceipt> GetReceipt(GetReceiptbyCondition Request, int pageIndex, int pageSize, out int rowCount)
{
string WhereSql = "";
// string WhereSqlRRTime = "";
if (Request != null)
{
WhereSql = SKUSearchCondition(Request);
//WhereSqlRRTime = SKUSearchConditionRRTime(Request);
}
int tempRowCount = 0;
DbParam[] dbParams = new DbParam[] {
new DbParam("@WhereSql", DbType.String, WhereSql, ParameterDirection.Input),
// new DbParam("@WhereSqlRRTime", DbType.String, WhereSql, ParameterDirection.Input),
new DbParam("@PageIndex", DbType.Int32, pageIndex, ParameterDirection.Input),
new DbParam("@PageSize", DbType.Int32, pageSize, ParameterDirection.Input),
new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output)
};
DataTable dt = this.ExecuteDataTable("[Proc_WMS_GetReceipt]", dbParams);
rowCount = (int)dbParams[3].Value;
return(dt.ConvertToEntityCollection <StoresByGetReceipt>());
}
public GetInventoryBySearchConditionResponse GetInventoryBySearchConditionGroup(InventorySearchCondition SearchCondition, int PageIndex, int PageSize, out int RowCount)
{
GetInventoryBySearchConditionResponse response = new GetInventoryBySearchConditionResponse();
string sqlWhere = this.GenGetInventory(SearchCondition);
string sqlOrderByType = this.OrderByType(SearchCondition);
string sqlOrderByType2 = this.OrderByType2(SearchCondition);
int tempRowCount = 0;
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input),
new DbParam("@OrderByType", DbType.String, sqlOrderByType, ParameterDirection.Input),
new DbParam("@OrderByType2", DbType.String, sqlOrderByType2, ParameterDirection.Input),
new DbParam("@PageIndex", DbType.Int32, PageIndex, ParameterDirection.Input),
new DbParam("@PageSize", DbType.Int32, PageSize, ParameterDirection.Input),
new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output)
};
DataSet ds = this.ExecuteDataSet("Proc_WMS_GetInventoryBySearchConditionGroup", dbParams);
RowCount = (int)dbParams[5].Value;
//return ds.Tables[0].ConvertToEntityCollection<Inventorys>();
response.InventoryCollection = ds.Tables[0].ConvertToEntityCollection <Inventorys>();
response.InventoryCollection2 = ds.Tables[1].ConvertToEntityCollection <Inventorys>();
return(response);
}
public string GetAllAnnouncementService(FetchAnnouncement fetchAnnouncement)
{
string ResultSet = null;
DbParam[] param = new DbParam[]
{
new DbParam(fetchAnnouncement.searchModal.SearchString, typeof(System.String), "_searchString"),
new DbParam(fetchAnnouncement.searchModal.SortBy, typeof(System.String), "_sortBy"),
new DbParam(fetchAnnouncement.searchModal.PageIndex, typeof(System.Int32), "_pageIndex"),
new DbParam(fetchAnnouncement.searchModal.PageSize, typeof(System.Int32), "_pageSize"),
new DbParam(fetchAnnouncement.StudentUid, typeof(System.String), "_studentUid"),
new DbParam(fetchAnnouncement.ClassDetailUid, typeof(System.String), "_classDetailUid"),
new DbParam(this.userDetail.schooltenentId, typeof(System.String), "_tenentId")
};
DataSet ds = db.GetDataset("sp_Announcement_SelFilter", param);
if (ds != null && ds.Tables.Count > 0)
{
ds = UiMappedColumn.BuildColumn <Announcement>(ds);
ResultSet = JsonConvert.SerializeObject(ds);
}
return(ResultSet);
}
public Clients GetClientDetailByUid(string ExistingClientUid)
{
Clients ClientResult = null;
String ResultSet = null;
ClientResult = new Clients();
DbParam[] param = new DbParam[]
{
new DbParam(ExistingClientUid, typeof(System.String), "_clientUid"),
new DbParam(userDetail.schooltenentId, typeof(System.String), "_tenentUid")
};
DataSet ds = db.GetDataset("sp_Client_ByUid", param);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
ClientResult = userDefineMapping.ConvertToObject(ds.Tables[0], ClientResult.GetType(), out ResultSet) as Clients;
if (ResultSet != "100")
{
return(null);
}
}
return(ClientResult);
}
public int GetTotal(string startDate, string endDate)
{
string query = @"SELECT count(*) as Total
FROM " + nameData + @".sms_packages_distribute
WHERE
STR_TO_DATE(CONCAT(MonthStart, '/', YearStart), '%m/%Y') >= STR_TO_DATE(@StartDate, '%m/%Y') AND
STR_TO_DATE(CONCAT(MonthEnd, '/', YearEnd), '%m/%Y') <= STR_TO_DATE(@EndDate, '%m/%Y')";
List <DbParam> param = new List <DbParam>();
param.Add(DbParam.Create("@StartDate", startDate));
param.Add(DbParam.Create("@EndDate", endDate));
var reader = _context.Session.ExecuteReader(query, param);
int total = 0;
while (reader.Read())
{
total = Convert.ToInt32(reader["Total"]);
}
reader.Close();
return(total);
}
public override string GetQueryWithFilter(string commandText, IList <DbField> filterFields, string filter, out DbParam [] pars)
{
SelectBuilder sBuilder = new SelectBuilder(fieldsTable, commandText);
string [] filterKeys = filter.Split(new [] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
List <DbParam> p = new List <DbParam> ();
foreach (string key in filterKeys)
{
StringBuilder condition = new StringBuilder();
DbParam par = GetFilterParam(p, key);
p.Add(par);
foreach (DbField field in filterFields)
{
SelectColumnInfo colInfo = sBuilder.SelectClause [field];
if (colInfo == null)
{
throw new ArgumentException(string.Format("Cannot search for field {0} as it is not populated from the database", field));
}
if (condition.Length > 0)
{
condition.Append(" OR ");
}
condition.AppendFormat(GetFilterCondition(colInfo.SourceExpression, par));
}
sBuilder.WhereClause.Add(new KeyValuePair <string, ConditionCombineLogic> (string.Format("({0})", condition), ConditionCombineLogic.And));
}
pars = p.ToArray();
return(sBuilder.ToString());
}
public override object Insert <TEntity>(Expression <Func <TEntity> > content, string table)
{
PublicHelper.CheckNull(content);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
if (typeDescriptor.PrimaryKeys.Count > 1)
{
/* 对于多主键的实体，暂时不支持调用这个方法进行插入 */
throw new NotSupportedException(string.Format("Can not call this method because entity '{0}' has multiple keys.", typeDescriptor.Definition.Type.FullName));
}
PropertyDescriptor keyPropertyDescriptor = typeDescriptor.PrimaryKeys.FirstOrDefault();
Dictionary <MemberInfo, Expression> insertColumns = InitMemberExtractor.Extract(content);
DbTable explicitDbTable = null;
if (table != null)
{
explicitDbTable = new DbTable(table, typeDescriptor.Table.Schema);
}
DefaultExpressionParser expressionParser = typeDescriptor.GetExpressionParser(explicitDbTable);
DbInsertExpression insertExp = new DbInsertExpression(explicitDbTable ?? typeDescriptor.Table);
object keyVal = null;
foreach (var kv in insertColumns)
{
MemberInfo key = kv.Key;
PropertyDescriptor propertyDescriptor = typeDescriptor.TryGetPropertyDescriptor(key);
if (propertyDescriptor == null)
{
throw new ChloeException(string.Format("The member '{0}' does not map any column.", key.Name));
}
if (propertyDescriptor.IsAutoIncrement)
{
throw new ChloeException(string.Format("Could not insert value into the auto increment column '{0}'.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("Can not insert value into the column '{0}', because it's mapping member has define a sequence.", propertyDescriptor.Column.Name));
}
if (propertyDescriptor.IsPrimaryKey)
{
object val = ExpressionEvaluator.Evaluate(kv.Value);
if (val == null)
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", propertyDescriptor.Property.Name));
}
else
{
keyVal = val;
insertExp.InsertColumns.Add(propertyDescriptor.Column, DbExpression.Parameter(keyVal, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType));
continue;
}
}
insertExp.InsertColumns.Add(propertyDescriptor.Column, expressionParser.Parse(kv.Value));
}
foreach (PropertyDescriptor propertyDescriptor in typeDescriptor.PropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement && propertyDescriptor.IsPrimaryKey)
{
insertExp.Returns.Add(propertyDescriptor.Column);
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor);
insertExp.InsertColumns.Add(propertyDescriptor.Column, getNextValueForSequenceExp);
if (propertyDescriptor.IsPrimaryKey)
{
insertExp.Returns.Add(propertyDescriptor.Column);
}
continue;
}
}
if (keyPropertyDescriptor != null)
{
//主键为空并且主键又不是自增列
if (keyVal == null && !keyPropertyDescriptor.IsAutoIncrement && !keyPropertyDescriptor.HasSequence())
{
throw new ChloeException(string.Format("The primary key '{0}' could not be null.", keyPropertyDescriptor.Property.Name));
}
}
List <DbParam> parameters;
this.ExecuteNonQuery(insertExp, out parameters);
if (keyPropertyDescriptor != null && (keyPropertyDescriptor.IsAutoIncrement || keyPropertyDescriptor.HasSequence()))
{
string outputColumnName = Utils.GenOutputColumnParameterName(keyPropertyDescriptor.Column.Name);
DbParam outputParam = parameters.Where(a => a.Direction == ParamDirection.Output && a.Name == outputColumnName).First();
keyVal = PublicHelper.ConvertObjType(outputParam.Value, keyPropertyDescriptor.PropertyType);
}
return(keyVal); /* It will return null if an entity does not define primary key. */
}
public static void ExecuteCommandText()
{
List <User> users = context.SqlQuery <User>("select * from Users where Age > @age", DbParam.Create("@age", 12)).ToList();
int rowsAffected = context.Session.ExecuteNonQuery("update Users set name=@name where Id = 1", DbParam.Create("@name", "Chloe"));
/*
* 执行存储过程:
* User user = context.SqlQuery<User>("Proc_GetUser", CommandType.StoredProcedure, DbParam.Create("@id", 1)).FirstOrDefault();
* rowsAffected = context.Session.ExecuteNonQuery("Proc_UpdateUserName", CommandType.StoredProcedure, DbParam.Create("@name", "Chloe"));
*/
ConsoleHelper.WriteLineAndReadKey();
}
public override void InsertRange <TEntity>(List <TEntity> entities, bool keepIdentity = false)
{
/*
* 将 entities 分批插入数据库
* 每批生成 insert into TableName(...) values(...),(...)...
* 该方法相对循环一条一条插入，速度提升 2/3 这样
*/
Utils.CheckNull(entities);
if (entities.Count == 0)
{
return;
}
int maxParameters = 2100;
int batchSize = 50; /* 每批实体大小，此值通过测试得出相对插入速度比较快的一个值 */
TypeDescriptor typeDescriptor = TypeDescriptor.GetDescriptor(typeof(TEntity));
var e = typeDescriptor.MappingMemberDescriptors.Select(a => a.Value);
if (keepIdentity == false)
{
e = e.Where(a => a.IsAutoIncrement == false);
}
List <MappingMemberDescriptor> mappingMemberDescriptors = e.ToList();
int maxDbParamsCount = maxParameters - mappingMemberDescriptors.Count; /* 控制一个 sql 的参数个数 */
string sqlTemplate = AppendInsertRangeSqlTemplate(typeDescriptor, mappingMemberDescriptors);
Action insertAction = () =>
{
int batchCount = 0;
List <DbParam> dbParams = new List <DbParam>();
StringBuilder sqlBuilder = new StringBuilder();
for (int i = 0; i < entities.Count; i++)
{
var entity = entities[i];
if (batchCount > 0)
{
sqlBuilder.Append(",");
}
sqlBuilder.Append("(");
for (int j = 0; j < mappingMemberDescriptors.Count; j++)
{
if (j > 0)
{
sqlBuilder.Append(",");
}
MappingMemberDescriptor mappingMemberDescriptor = mappingMemberDescriptors[j];
object val = mappingMemberDescriptor.GetValue(entity);
if (val == null)
{
sqlBuilder.Append("NULL");
continue;
}
Type valType = val.GetType();
if (valType.IsEnum)
{
val = Convert.ChangeType(val, Enum.GetUnderlyingType(valType));
valType = val.GetType();
}
if (ToStringableNumericTypes.ContainsKey(valType))
{
sqlBuilder.Append(val.ToString());
continue;
}
if (val is bool)
{
if ((bool)val == true)
{
sqlBuilder.AppendFormat("1");
}
else
{
sqlBuilder.AppendFormat("0");
}
continue;
}
else if (val is double)
{
double v = (double)val;
if (v >= long.MinValue && v <= long.MaxValue)
{
sqlBuilder.Append(((long)v).ToString());
continue;
}
}
else if (val is float)
{
float v = (float)val;
if (v >= long.MinValue && v <= long.MaxValue)
{
sqlBuilder.Append(((long)v).ToString());
continue;
}
}
else if (val is decimal)
{
decimal v = (decimal)val;
if (v >= long.MinValue && v <= long.MaxValue)
{
sqlBuilder.Append(((long)v).ToString());
continue;
}
}
string paramName = UtilConstants.ParameterNamePrefix + dbParams.Count.ToString();
DbParam dbParam = new DbParam(paramName, val)
{
DbType = mappingMemberDescriptor.Column.DbType
};
dbParams.Add(dbParam);
sqlBuilder.Append(paramName);
}
sqlBuilder.Append(")");
batchCount++;
if ((batchCount >= 20 && dbParams.Count >= 120 /*参数个数太多也会影响速度*/) || dbParams.Count >= maxDbParamsCount || batchCount >= batchSize || (i + 1) == entities.Count)
{
sqlBuilder.Insert(0, sqlTemplate);
string sql = sqlBuilder.ToString();
this.Session.ExecuteNonQuery(sql, dbParams.ToArray());
sqlBuilder.Clear();
dbParams.Clear();
batchCount = 0;
}
}
};
Action fAction = () =>
{
bool shouldTurnOff_IDENTITY_INSERT = false;
string tableName = null;
if (keepIdentity == true)
{
tableName = AppendTableName(typeDescriptor.Table);
this.Session.ExecuteNonQuery(string.Format("SET IDENTITY_INSERT {0} ON ", tableName));
shouldTurnOff_IDENTITY_INSERT = true;
}
insertAction();
if (shouldTurnOff_IDENTITY_INSERT == true)
{
this.Session.ExecuteNonQuery(string.Format("SET IDENTITY_INSERT {0} OFF ", tableName));
}
};
if (this.Session.IsInTransaction)
{
fAction();
}
else
{
/* 因为分批插入，所以需要开启事务保证数据一致性 */
this.Session.BeginTransaction();
try
{
fAction();
this.Session.CommitTransaction();
}
catch
{
if (this.Session.IsInTransaction)
{
this.Session.RollbackTransaction();
}
throw;
}
}
}
public override DbExpression Visit(DbParameterExpression exp)
{
object paramValue = exp.Value;
Type paramType = exp.Type;
if (paramType.IsEnum())
{
paramType = Enum.GetUnderlyingType(paramType);
if (paramValue != null)
{
paramValue = Convert.ChangeType(paramValue, paramType);
}
}
if (paramValue == null)
{
paramValue = DBNull.Value;
}
DbParam p;
if (paramValue == DBNull.Value)
{
p = this._parameters.Where(a => Utils.AreEqual(a.Value, paramValue) && a.Type == paramType).FirstOrDefault();
}
else
{
p = this._parameters.Where(a => Utils.AreEqual(a.Value, paramValue)).FirstOrDefault();
}
if (p != null)
{
this._sqlBuilder.Append(p.Name);
return(exp);
}
string paramName = GenParameterName(this._parameters.Count);
p = DbParam.Create(paramName, paramValue, paramType);
if (paramValue.GetType() == UtilConstants.TypeOfString)
{
if (exp.DbType == DbType.AnsiStringFixedLength || exp.DbType == DbType.StringFixedLength)
{
p.Size = ((string)paramValue).Length;
}
else if (((string)paramValue).Length <= 4000)
{
p.Size = 4000;
}
}
if (exp.DbType != null)
{
p.DbType = exp.DbType;
}
this._parameters.Add(p);
this._sqlBuilder.Append(paramName);
return(exp);
}
protected virtual string GetFilterCondition(string source, DbParam parameter)
{
return(string.Format("({0} LIKE {1})",
source, GetConcatStatement("'%'", fieldsTable.GetParameterName(parameter.ParameterName), "'%'")));
}
public virtual IDbDataParameter CreateDataParameter(IDbCommand cmd, DbParam param)
{
IDbDataParameter parameter = cmd.CreateParameter();
parameter.ParameterName = param.Name;
Type parameterType = null;
if (param.Value == null || param.Value == DBNull.Value)
{
parameter.Value = DBNull.Value;
parameterType = param.Type ?? typeof(object);
}
else
{
parameterType = param.Value.GetType();
if (parameterType.IsEnum)
{
parameterType = Enum.GetUnderlyingType(parameterType);
parameter.Value = Convert.ChangeType(param.Value, parameterType);
}
else
{
parameter.Value = param.Value;
}
}
if (param.Precision != null)
{
parameter.Precision = param.Precision.Value;
}
if (param.Scale != null)
{
parameter.Scale = param.Scale.Value;
}
if (param.Size != null)
{
parameter.Size = param.Size.Value;
}
if (param.DbType != null)
{
parameter.DbType = param.DbType.Value;
}
else
{
parameter.DbType = this.DbType;
}
const int defaultSizeOfStringOutputParameter = 4000;/* 当一个 string 类型输出参数未显示指定 Size 时使用的默认大小。如果有需要更大或者该值不足以满足需求，需显示指定 DbParam.Size 值 */
if (param.Direction == ParamDirection.Input)
{
parameter.Direction = ParameterDirection.Input;
}
else if (param.Direction == ParamDirection.Output)
{
parameter.Direction = ParameterDirection.Output;
param.Value = null;
if (param.Size == null && param.Type == UtilConstants.TypeOfString)
{
parameter.Size = defaultSizeOfStringOutputParameter;
}
}
else if (param.Direction == ParamDirection.InputOutput)
{
parameter.Direction = ParameterDirection.InputOutput;
if (param.Size == null && param.Type == UtilConstants.TypeOfString)
{
parameter.Size = defaultSizeOfStringOutputParameter;
}
}
else
{
throw new NotSupportedException(string.Format("ParamDirection '{0}' is not supported.", param.Direction));
}
return(parameter);
}
public override TEntity Insert <TEntity>(TEntity entity, string table)
{
PublicHelper.CheckNull(entity);
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
DbTable dbTable = PublicHelper.CreateDbTable(typeDescriptor, table);
List <PrimitivePropertyDescriptor> outputColumns = new List <PrimitivePropertyDescriptor>();
Dictionary <PrimitivePropertyDescriptor, DbExpression> insertColumns = new Dictionary <PrimitivePropertyDescriptor, DbExpression>();
foreach (PrimitivePropertyDescriptor propertyDescriptor in typeDescriptor.PrimitivePropertyDescriptors)
{
if (propertyDescriptor.IsAutoIncrement)
{
outputColumns.Add(propertyDescriptor);
continue;
}
if (propertyDescriptor.HasSequence())
{
DbMethodCallExpression getNextValueForSequenceExp = PublicHelper.MakeNextValueForSequenceDbExpression(propertyDescriptor, dbTable.Schema);
insertColumns.Add(propertyDescriptor, getNextValueForSequenceExp);
outputColumns.Add(propertyDescriptor);
continue;
}
object val = propertyDescriptor.GetValue(entity);
PublicHelper.NotNullCheck(propertyDescriptor, val);
DbExpression valExp = DbExpression.Parameter(val, propertyDescriptor.PropertyType, propertyDescriptor.Column.DbType);
insertColumns.Add(propertyDescriptor, valExp);
}
DbInsertExpression e = new DbInsertExpression(dbTable);
foreach (var kv in insertColumns)
{
e.InsertColumns.Add(kv.Key.Column, kv.Value);
}
e.Returns.AddRange(outputColumns.Select(a => a.Column));
List <DbParam> parameters;
this.ExecuteNonQuery(e, out parameters);
List <DbParam> outputParams = parameters.Where(a => a.Direction == ParamDirection.Output).ToList();
for (int i = 0; i < outputColumns.Count; i++)
{
PrimitivePropertyDescriptor propertyDescriptor = outputColumns[i];
string putputColumnName = Utils.GenOutputColumnParameterName(propertyDescriptor.Column.Name);
DbParam outputParam = outputParams.Where(a => a.Name == putputColumnName).First();
var outputValue = PublicHelper.ConvertObjectType(outputParam.Value, propertyDescriptor.PropertyType);
outputColumns[i].SetValue(entity, outputValue);
}
return(entity);
}
public override DbExpression Visit(DbParameterExpression exp)
{
object paramValue = exp.Value;
Type paramType = exp.Type.GetUnderlyingType();
if (paramType.IsEnum)
{
paramType = Enum.GetUnderlyingType(paramType);
if (paramValue != null)
{
paramValue = Convert.ChangeType(paramValue, paramType);
}
}
else if (paramType == PublicConstants.TypeOfBoolean)
{
paramType = PublicConstants.TypeOfInt32;
if (paramValue != null)
{
paramValue = (bool)paramValue ? Boxed_1 : Boxed_0;
}
if (exp.DbType == null || exp.DbType == DbType.Boolean)
{
exp.DbType = DbType.Int32;
}
}
if (paramValue == null)
{
paramValue = DBNull.Value;
}
DbParam p = this._parameters.Find(paramValue, paramType, exp.DbType);
if (p != null)
{
this.SqlBuilder.Append(p.Name);
return(exp);
}
string paramName = GenParameterName(this._parameters.Count);
p = DbParam.Create(paramName, paramValue, paramType);
if (paramValue.GetType() == PublicConstants.TypeOfString)
{
if (exp.DbType == DbType.AnsiStringFixedLength || exp.DbType == DbType.StringFixedLength)
{
p.Size = ((string)paramValue).Length;
}
else if (((string)paramValue).Length <= 4000)
{
p.Size = 4000;
}
}
if (exp.DbType != null)
{
p.DbType = exp.DbType;
}
this._parameters.Add(p);
this.SqlBuilder.Append(paramName);
return(exp);
}
internal ParameterSyntax(object value)
{
Name = null;
MetaId = null;
_param = new DbParam() {Value = value };
}
public override void InsertRange <TEntity>(List <TEntity> entities, string table)
{
/*
* 将 entities 分批插入数据库
* 每批生成 insert into TableName(...) select ... from dual union all select ... from dual...
* 对于 oracle，貌似速度提升不了...- -
* #期待各码友的优化建议#
*/
PublicHelper.CheckNull(entities);
if (entities.Count == 0)
{
return;
}
int maxParameters = 1000;
int batchSize = 40; /* 每批实体大小，此值通过测试得出相对插入速度比较快的一个值 */
TypeDescriptor typeDescriptor = EntityTypeContainer.GetDescriptor(typeof(TEntity));
List <PropertyDescriptor> mappingPropertyDescriptors = typeDescriptor.PropertyDescriptors.Where(a => a.IsAutoIncrement == false).ToList();
int maxDbParamsCount = maxParameters - mappingPropertyDescriptors.Count; /* 控制一个 sql 的参数个数 */
DbTable dbTable = string.IsNullOrEmpty(table) ? typeDescriptor.Table : new DbTable(table, typeDescriptor.Table.Schema);
string sqlTemplate = AppendInsertRangeSqlTemplate(dbTable, mappingPropertyDescriptors);
Action insertAction = () =>
{
int batchCount = 0;
List <DbParam> dbParams = new List <DbParam>();
StringBuilder sqlBuilder = new StringBuilder();
for (int i = 0; i < entities.Count; i++)
{
var entity = entities[i];
if (batchCount > 0)
{
sqlBuilder.Append(" UNION ALL ");
}
sqlBuilder.Append("SELECT ");
for (int j = 0; j < mappingPropertyDescriptors.Count; j++)
{
if (j > 0)
{
sqlBuilder.Append(",");
}
PropertyDescriptor mappingPropertyDescriptor = mappingPropertyDescriptors[j];
object val = mappingPropertyDescriptor.GetValue(entity);
if (val == null)
{
sqlBuilder.Append("NULL");
sqlBuilder.Append(" C").Append(j.ToString());
continue;
}
Type valType = val.GetType();
if (valType.IsEnum)
{
val = Convert.ChangeType(val, Enum.GetUnderlyingType(valType));
valType = val.GetType();
}
if (Utils.IsToStringableNumericType(valType))
{
sqlBuilder.Append(val.ToString());
}
else if (val is bool)
{
if ((bool)val == true)
{
sqlBuilder.AppendFormat("1");
}
else
{
sqlBuilder.AppendFormat("0");
}
}
else
{
string paramName = UtilConstants.ParameterNamePrefix + dbParams.Count.ToString();
DbParam dbParam = new DbParam(paramName, val)
{
DbType = mappingPropertyDescriptor.Column.DbType
};
dbParams.Add(dbParam);
sqlBuilder.Append(paramName);
}
sqlBuilder.Append(" C").Append(j.ToString());
}
sqlBuilder.Append(" FROM DUAL");
batchCount++;
if ((batchCount >= 20 && dbParams.Count >= 400 /*参数个数太多也会影响速度*/) || dbParams.Count >= maxDbParamsCount || batchCount >= batchSize || (i + 1) == entities.Count)
{
sqlBuilder.Insert(0, sqlTemplate);
sqlBuilder.Append(") T");
string sql = sqlBuilder.ToString();
this.Session.ExecuteNonQuery(sql, dbParams.ToArray());
sqlBuilder.Clear();
dbParams.Clear();
batchCount = 0;
}
}
};
Action fAction = insertAction;
if (this.Session.IsInTransaction)
{
fAction();
}
else
{
/* 因为分批插入，所以需要开启事务保证数据一致性 */
this.Session.BeginTransaction();
try
{
fAction();
this.Session.CommitTransaction();
}
catch
{
if (this.Session.IsInTransaction)
{
this.Session.RollbackTransaction();
}
throw;
}
}
}
static void ChloeSqlQueryTest(int takeCount, int loops)
{
using (MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString))
{
for (int i = 0; i < loops; i++)
{
int id = 0;
var list = context.SqlQuery <TestEntity>(string.Format("select top {0} * from TestEntity where Id>@Id", takeCount.ToString()), DbParam.Create("@Id", id)).ToList();
}
}
}
public DbCommandFactor(IObjectActivator objectActivator, string commandText, DbParam[] parameters)
{
this.ObjectActivator = objectActivator;
this.CommandText = commandText;
this.Parameters = parameters;
}
protected virtual IDbCommand PrepareCommand(string cmdText, DbParam[] parameters, CommandType cmdType, out List <OutputParameter> outputParameters)
{
outputParameters = null;
IDbCommand cmd = this.DbConnection.CreateCommand();
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
cmd.CommandTimeout = this.CommandTimeout;
if (this.IsInTransaction)
{
cmd.Transaction = this.DbTransaction;
}
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
{
DbParam param = parameters[i];
if (param == null)
{
continue;
}
if (param.ExplicitParameter != null)/* 如果存在创建好了的 IDbDataParameter，则直接用它。同时也忽视了 DbParam 的其他属性 */
{
cmd.Parameters.Add(param.ExplicitParameter);
continue;
}
Type parameterType;
if (param.Value == null || param.Value == DBNull.Value)
{
parameterType = param.Type ?? typeof(object);
}
else
{
parameterType = param.Value.GetType();
if (parameterType.IsEnum)
{
parameterType = Enum.GetUnderlyingType(parameterType);
}
}
IDbDataParameter parameter = cmd.CreateParameter();
Infrastructure.MappingType mappingType = MappingTypeSystem.GetMappingType(parameterType);
mappingType.DbParameterAssembler.SetupParameter(parameter, param);
cmd.Parameters.Add(parameter);
OutputParameter outputParameter = null;
if (param.Direction == ParamDirection.Output || param.Direction == ParamDirection.InputOutput)
{
outputParameter = new OutputParameter(param, parameter);
if (outputParameters == null)
{
outputParameters = new List <OutputParameter>();
}
outputParameters.Add(outputParameter);
}
}
}
return(cmd);
}
internal ParameterSyntax(string name = null, MetaId metaId = null, DbParam param = null)
{
Name = name;
MetaId = metaId;
_param = param;
}
