首页 新闻 搜索 专区 学院

关于 Hibernate 查询问题,求大牛解答,在线等,急啊,做不完不能放假了啊

0
悬赏园豆:50 [已关闭问题] 关闭于 2014-04-04 12:23
  1         /// <summary>
  2         /// 日志查询
  3         /// </summary>
  4         /// <param name="ModelName">模块名称</param>
  5         /// <param name="LogInfoType">日志类型</param>
  6         /// <param name="descrip">详情</param>
  7         /// <param name="user">操作用户</param>
  8         /// <param name="ip"></param>
  9         /// <param name="rversion">R版本</param>
 10         /// <param name="star">开始时间</param>
 11         /// <param name="end">结束时间</param>
 12         /// <param name="queryUser">当前查询用户ID</param>
 13         /// <param name="pageInfo">分页</param>
 14         /// <returns></returns>
 15         public IList<Log> GetLogListByUser(string ModelName, string LogInfoType, string descrip, string user, string ip, string rversion, DateTime? star, DateTime? end, string queryUser, ref PageInfo pageInfo)
 16         {
 17             string sql = @"select * from specMS_SpecPermission where userName=:userName";
 18             IList<SpecmsSpecPermission> list = CurrentSession.CreateSQLQuery(sql).AddEntity(typeof(SpecmsSpecPermission)).SetString("userName", queryUser).List<SpecmsSpecPermission>();
 19             if (list == null)
 20                 return null;
 21 
 22             if (list.Where(p => p.RCode == "1001").Count() > 0)
 23             {
 24                 return GetLogList(ModelName, LogInfoType, descrip, user, ip, rversion, star, end, null, queryUser, ref pageInfo);
 25             }
 26             else
 27             {
 28 
 29                 sql = string.Empty;
 30                 var list29 = list.Where(p => p.RCode == "29");
 31                 List<int> intDataSet = new List<int>();
 32                 foreach (var item in list29)
 33                     intDataSet.Add(item.DataSetid);
 34 
 35                 if (intDataSet.Count > 0)
 36                 {
 37                     sql = string.Format(@"select specMS_SpecDataIDSet.srcName from specMS_SpecPermission
 38                   left join specMS_SpecDataIDSet on specMS_SpecDataIDSet.dataSetID=
 39                   specMS_SpecPermission.dataSetID where specMS_SpecPermission.dataSetID in({0}) and
 40                   specMS_SpecDataIDSet.srcName is not null
 41                   and specMS_SpecDataIDSet.srcName<>'' group by specMS_SpecDataIDSet.srcName", string.Join(",", intDataSet));
 42                 }
 43 
 44                 var list41 = list.Where(p => p.RCode == "41");
 45                 if (list41.Count() > 0)
 46                 {
 47                     if (sql.Length > 0)
 48                         sql += " union all ";
 49 
 50                     sql += string.Format("select tempName from specMS_TemplateSpec where tempManager like '%{0}%'", queryUser);
 51                 }
 52 
 53                 List<string> result = new List<string>();
 54                 if (sql.Length > 0)
 55                     result = CurrentSession.CreateSQLQuery(sql).List<string>().ToList();
 56 
 57                 return GetLogList(ModelName, LogInfoType, descrip, user, ip, rversion, star, end, result, queryUser, ref pageInfo);
 58             }
 59         }
 60 
 61 
 62         /// <summary>
 63         /// 查询日志
 64         /// </summary>
 65         /// <param name="user">用户</param>
 66         /// <param name="type">日志类型</param>
 67         /// <param name="star">开始时间</param>
 68         /// <param name="end">结束时间</param>
 69         /// <param name="pageInfo">分页</param>
 70         /// <returns></returns>
 71         public IList<Log> GetLogList(string ModelName, string LogInfoType, string descrip, string user, string ip, string rversion, DateTime? star, DateTime? end, List<string> modelids, string queryUser, ref PageInfo pageInfo)
 72         {
 73             Dictionary<string, object> param = new Dictionary<string, object>();
 74             string sql = "select * from Log where ModelID<>'Service' and ModelID is not null ";
 75             if (!string.IsNullOrEmpty(ModelName))
 76             {
 77                 sql += "and ModelName in(:ModelName) ";
 78                 param.Add("ModelName", ModelName.Split(','));
 79             }
 80 
 81             if (!string.IsNullOrEmpty(LogInfoType))
 82             {
 83                 sql += "and LogType in(:LogType) ";
 84                 param.Add("LogType", LogInfoType.Split(','));
 85             }
 86 
 87             if (!string.IsNullOrEmpty(descrip))
 88             {
 89                 sql += "and (Content like :Descrip or Summary like :Descrip) ";
 90                 param.Add("Descrip", "%" + descrip + "%");
 91             }
 92 
 93             if (!string.IsNullOrEmpty(ip))
 94             {
 95                 sql += "and Ip like :Ip ";
 96                 param.Add("Ip", "%" + ip + "%");
 97             }
 98 
 99             if (!string.IsNullOrEmpty(rversion))
100             {
101                 sql += "and Modelid like :Modelid ";
102                 param.Add("Modelid", "%" + rversion + "%");
103             }
104 
105             if (modelids != null)
106             {
107                 if (modelids.Count > 0)
108                 {
109                     sql += "and (Modelid in (:inModelid) or UserID like :queryUser) ";
110                     param.Add("inModelid", modelids.ToArray());
111                     param.Add("queryUser", "%" + queryUser + "%");
112                 }
113                 else
114                 {
115                     sql += "and UserID=:UserID ";
116                     param.Add("UserID", queryUser);
117                 }
118             }
119 
120             if (star.HasValue)
121             {
122                 sql += "and LogTime >= :starTime ";
123                 param.Add("starTime", star.Value.ToString("yyyy-MM-dd 00:00:00"));
124             }
125 
126             if (end.HasValue)
127             {
128                 sql += "and LogTime < :endTime ";
129                 param.Add("endTime", end.Value.AddDays(1).ToString("yyyy-MM-dd 00:00:00"));
130             }
131 
132             if (!string.IsNullOrEmpty(user))
133             {
134                 IList<SyncEmployee> syEm = CurrentSession.CreateCriteria(typeof(SyncEmployee)).Add(Restrictions.In("Code", user.Split(','))).List<SyncEmployee>();
135 
136                 if (syEm != null && syEm.Count > 0)
137                 {
138                     List<string> str = new List<string>();
139                     foreach (var item in syEm)
140                         str.Add(item.Name + " " + item.Code);
141 
142                     sql += "and UserID in (:inUserID) ";
143                     param.Add("inUserID", str.ToArray());
144                 }
145             }
146 
147             //if (string.IsNullOrEmpty(user))
148             //{
149             //    string strWhere = sql.Substring(sql.IndexOf("where")) + " and UserID like :queryUser";
150             //    param.Add("queryUser", "%" + queryUser + "%");
151             //    sql += " union all select * from Log " + strWhere;
152             //}
153             //sql = "select * from(" + sql + ")A ";
154 
155             sql += "order by LogTime desc";
156 
157             var query = CurrentSession.CreateSQLQuery(sql).AddEntity(typeof(Log));
158             foreach (var item in param)
159             {
160                 if (item.Value is string)
161                     query.SetString(item.Key, item.Value.ToString());
162                 if (item.Value is Array)
163                     query.SetParameterList(item.Key, (Array)item.Value);
164             }
165 
166             pageInfo.Total = query.List().Count;
167             IList<Log> list = query.
168                SetFirstResult((pageInfo.PageNo - 1) * pageInfo.PageSize).
169                SetMaxResults(pageInfo.PageSize).
170                List<Log>();
171 
172             return list;
173         }

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 以上代码查询的时候会报错,错误信息:

{很可能由 IncludeExceptionDetailInFaults=true 创建的 ExceptionDetail,其值为:
NHibernate.Exceptions.GenericADOException: could not execute batch command.[SQL: SQL not available] ----> System.Data.SqlClient.SqlException: UPDATE 语句与 FOREIGN KEY 约束"FK_specMS_SpecPermission"冲突。该冲突发生于数据库"specMS_test",表"dbo.specMS_SpecDataIDSet", column 'dataSetID'。
语句已终止。
   在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   在 System.Data.SqlClient.SqlCommand.ExecuteBatchRPCCommand()
   在 System.Data.SqlClient.SqlCommandSet.ExecuteNonQuery()
   在 NHibernate.AdoNet.SqlClientSqlCommandSet.ExecuteNonQuery()
   在 NHibernate.AdoNet.SqlClientBatchingBatcher.DoExecuteBatch(IDbCommand ps)
   --- 内部 ExceptionDetail 堆栈跟踪结束 ---
   在 NHibernate.AdoNet.SqlClientBatchingBatcher.DoExecuteBatch(IDbCommand ps)
   在 NHibernate.AdoNet.AbstractBatcher.ExecuteBatchWithTiming(IDbCommand ps)
   在 NHibernate.AdoNet.AbstractBatcher.ExecuteBatch()
   在 NHibernate.Engine.ActionQueue.ExecuteActions(IList list)
   在 NHibernate.Engine.ActionQueue.ExecuteActions()
   在 NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)
   在 NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event)
   在 NHibernate.Impl.SessionImpl.Flush()
   在 NHibernate.Transaction.AdoTransaction.Commit()
   在 H3C.ProjectFrame.NHibernate.SessionManager.Commit() 位置 D:\workspace\H3C.ProjectFrame\V1.6\H3C.ProjectFrame\NHibernate\SessionManager.cs:行号 163
   在 H3C.ProjectFrame.NHibernate.WCF.NhSessionPerCallDispatchMessageInspector.BeforeSendReply(Message& reply, Object correlationState) 位置 D:\workspace\H3C.ProjectFrame\V1.6\H3C.ProjectFrame\NHibernate\WCF\NhSessionPerCallDispatchMessageInspector.cs:行号 24
   在 System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.BeforeSendReplyCore(MessageRpc& rpc, Exception& exception, Boolean& thereIsAnUnhandledException)}

 

什么原因呢,求解答

wangle1189的主页 wangle1189 | 初学一级 | 园豆:197
提问于:2014-04-04 10:33
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册