// 按ID检索记录 // parameter: // searchItem SearchItem对象,包括检索信息 // isConnected 连接对象的delegate // resultSet 结果集对象,存放命中记录 // return: // -1 出错 // 0 成功 // 线:不安全 private int SearchByID(SearchItem searchItem, Delegate_isConnected isConnected, DpResultSet resultSet, out string strError) { strError = ""; Debug.Assert(searchItem != null, "SearchByID()调用错误,searchItem参数值不能为null。"); Debug.Assert(isConnected != null, "SearchByID()调用错误,isConnected参数值不能为null。"); Debug.Assert(resultSet != null, "SearchByID()调用错误,resultSet参数值不能为null。"); SqlConnection connection = new SqlConnection(this.m_strConnString); connection.Open(); try { List<SqlParameter> aSqlParameter = new List<SqlParameter>(); string strWhere = ""; if (searchItem.Match == "left" || searchItem.Match == "") { strWhere = " WHERE id LIKE @id and id like N'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' "; SqlParameter temp = new SqlParameter("@id", SqlDbType.NVarChar); temp.Value = searchItem.Word + "%"; aSqlParameter.Add(temp); } else if (searchItem.Match == "middle") { strWhere = " WHERE id LIKE @id and id like N'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' "; SqlParameter temp = new SqlParameter("@id", SqlDbType.NVarChar); temp.Value = "%" + searchItem.Word + "%"; aSqlParameter.Add(temp); } else if (searchItem.Match == "right") { strWhere = " WHERE id LIKE @id and id like N'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' "; SqlParameter temp = new SqlParameter("@id", SqlDbType.NVarChar); temp.Value = "%" + searchItem.Word; aSqlParameter.Add(temp); } else if (searchItem.Match == "exact") { if (searchItem.DataType == "string") searchItem.Word = DbPath.GetID10(searchItem.Word); if (searchItem.Relation == "draw") { int nPosition; nPosition = searchItem.Word.IndexOf("-"); if (nPosition >= 0) { string strStartID; string strEndID; StringUtil.SplitRange(searchItem.Word, out strStartID, out strEndID); strStartID = DbPath.GetID10(strStartID); strEndID = DbPath.GetID10(strEndID); strWhere = " WHERE @idMin <=id and id<= @idMax and id like N'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' "; SqlParameter temp = new SqlParameter("@idMin", SqlDbType.NVarChar); temp.Value = strStartID; aSqlParameter.Add(temp); temp = new SqlParameter("@idMax", SqlDbType.NVarChar); temp.Value = strEndID; aSqlParameter.Add(temp); } else { string strOperator; string strRealText; StringUtil.GetPartCondition(searchItem.Word, out strOperator, out strRealText); strRealText = DbPath.GetID10(strRealText); strWhere = " WHERE id " + strOperator + " @id and id like N'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' "; SqlParameter temp = new SqlParameter("@id", SqlDbType.NVarChar); temp.Value = strRealText; aSqlParameter.Add(temp); } } else { searchItem.Word = DbPath.GetID10(searchItem.Word); strWhere = " WHERE id " + searchItem.Relation + " @id and id like N'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' "; SqlParameter temp = new SqlParameter("@id", SqlDbType.NVarChar); temp.Value = searchItem.Word; aSqlParameter.Add(temp); } } string strTop = ""; if (searchItem.MaxCount != -1) // 只命中指定的条数 strTop = " TOP " + Convert.ToString(searchItem.MaxCount) + " "; string strOrderBy = ""; if (searchItem.IdOrder != "") strOrderBy = "ORDER BY id " + searchItem.IdOrder + " "; string strCommand = "use " + this.m_strSqlDbName + " SELECT " + " DISTINCT " + strTop + " id " + " FROM records " + strWhere + " " + strOrderBy + "\n"; strCommand += " use master " + "\n"; SqlCommand command = new SqlCommand(strCommand, connection); command.CommandTimeout = 20 * 60; // 把检索时间变大 foreach (SqlParameter sqlParameter in aSqlParameter) { command.Parameters.Add(sqlParameter); } DatabaseCommandTask task = new DatabaseCommandTask(command); try { Thread t1 = new Thread(new ThreadStart(task.ThreadMain)); t1.Start(); bool bRet; while (true) { if (isConnected != null) //只是不再检索了 { if (isConnected() == false) { strError = "用户中断"; return -1; } } bRet = task.m_event.WaitOne(100, false); //millisecondsTimeout if (bRet == true) break; } if (task.bError == true) { strError = task.ErrorString; return -1; } if (task.DataReader == null) return 0; if (task.DataReader.HasRows == false) { return 0; } int nLoopCount = 0; while (task.DataReader.Read()) { if (nLoopCount % 10000 == 0) { if (isConnected != null) { if (isConnected() == false) { strError = "用户中断"; return -1; } } } string strID = ((string)task.DataReader[0]); if (strID.Length != 10) { strError = "结果集中出现了长度不是10位的记录号,不正常"; return -1; } string strId = this.FullID + "/" + strID; //记录路径格式:库ID/记录号 resultSet.Add(new DpRecord(strId)); nLoopCount++; Thread.Sleep(0); } } finally { if (task != null && task.DataReader != null) task.DataReader.Close(); } } catch (SqlException sqlEx) { if (sqlEx.Errors is SqlErrorCollection) strError = "数据库'" + this.GetCaption("zh") + "'尚未初始化。"; else strError = sqlEx.Message; return -1; } catch (Exception ex) { strError = ex.Message; return -1; } finally // 连接 { connection.Close(); } return 0; }
// 检索 // parameters: // searchItem SearchItem对象,存放检索词等信息 // isConnected 连接对象 // resultSet 结果集对象,存放命中记录 // strLang 语言版本, // return: // -1 出错 // 0 成功 internal override int SearchByUnion(SearchItem searchItem, Delegate_isConnected isConnected, DpResultSet resultSet, int nWarningLevel, out string strError, out string strWarning) { strError = ""; strWarning = ""; //**********对数据库加读锁************** m_lock.AcquireReaderLock(m_nTimeOut); #if DEBUG_LOCK_SQLDATABASE this.container.WriteDebugInfo("SearchByUnion(),对'" + this.GetCaption("zh-cn") + "'数据库加读锁。"); #endif try { bool bHasID; List<TableInfo> aTableInfo = null; int nRet = this.TableNames2aTableInfo(searchItem.TargetTables, out bHasID, out aTableInfo, out strError); if (nRet == -1) return -1; if (bHasID == true) { nRet = SearchByID(searchItem, isConnected, resultSet, out strError); if (nRet == -1) return -1; } // 对sql库来说,通过ID检索后,记录已排序,去重 if (aTableInfo == null || aTableInfo.Count == 0) return 0; string strCommand = ""; // Sql命令参数数组 List<SqlParameter> aSqlParameter = new List<SqlParameter>(); string strSelectKeystring = ""; if (searchItem.KeyOrder != "") { if (aTableInfo.Count > 1) strSelectKeystring = ",keystring"; } // 循环每一个检索途径 for (int i = 0; i < aTableInfo.Count; i++) { TableInfo tableInfo = aTableInfo[i]; // 参数名的后缀 string strPostfix = Convert.ToString(i); string strConditionAboutKey = ""; try { nRet = GetKeyCondition( searchItem, tableInfo.nodeConvertQueryString, tableInfo.nodeConvertQueryNumber, strPostfix, ref aSqlParameter, out strConditionAboutKey, out strError); if (nRet == -1) return -1; } catch (NoMatchException ex) { strWarning = ex.Message; strError = strWarning; return -1; } // 如果限制了一个最大数,则按每个途径都是这个最大数算 string strTop = ""; if (searchItem.MaxCount != -1) //限制的最大数 strTop = " TOP " + Convert.ToString(searchItem.MaxCount) + " "; string strWhere = ""; if (strConditionAboutKey != "") strWhere = " WHERE " + strConditionAboutKey; string strOneCommand = ""; if (i == 0)// 第一个表 { strOneCommand = "use " + this.m_strSqlDbName + " " + " SELECT " + " DISTINCT " + strTop + " idstring" + strSelectKeystring + " " + " FROM " + tableInfo.SqlTableName + " " + strWhere; } else { strOneCommand = " union SELECT " + " DISTINCT " + strTop + " idstring" + strSelectKeystring + " " //DISTINCT 去重 + " FROM " + tableInfo.SqlTableName + " " + strWhere; } strCommand += strOneCommand; } string strOrderBy = ""; if (searchItem.OrderBy != "") strOrderBy = "ORDER BY " + searchItem.OrderBy + " "; strCommand += strOrderBy; strCommand += " use master " + "\n"; if (aSqlParameter == null) { strError = "一个参数也没是不可能的情况"; return -1; } SqlCommand command = null; SqlConnection connection = new SqlConnection(this.m_strConnString); connection.Open(); try { command = new SqlCommand(strCommand, connection); foreach (SqlParameter sqlParameter in aSqlParameter) { command.Parameters.Add(sqlParameter); } command.CommandTimeout = 20 * 60; // 把检索时间变大 // 调新线程处理 DatabaseCommandTask task = new DatabaseCommandTask(command); try { if (task == null) { strError = "test为null"; return -1; } Thread t1 = new Thread(new ThreadStart(task.ThreadMain)); t1.Start(); bool bRet; while (true) { if (isConnected != null) { if (isConnected() == false) { strError = "用户中断"; return -1; } } bRet = task.m_event.WaitOne(100, false); //1/10秒看一次 if (bRet == true) break; } if (task.DataReader == null || task.DataReader.HasRows == false) { return 0; } int nGetedCount = 0; while (task.DataReader.Read()) { if (isConnected != null && (nGetedCount % 10000) == 0) { if (isConnected() == false) { strError = "用户中断"; return -1; } } string strId = this.FullID + "/" + (string)task.DataReader[0]; // 记录格式为:库id/记录号 resultSet.Add(new DpRecord(strId)); nGetedCount++; // 超过最大数了 if (searchItem.MaxCount != -1 && nGetedCount >= searchItem.MaxCount) break; Thread.Sleep(0); } } finally { if (task.DataReader != null) task.DataReader.Close(); } } catch (SqlException sqlEx) { if (sqlEx.Errors is SqlErrorCollection) strError = "数据库'" + this.GetCaption("zh") + "'尚未初始化。"; else strError = sqlEx.Message; return -1; } catch (Exception ex) { strError = ex.Message; return -1; } finally // 连接 { connection.Close(); } } catch (Exception ex) { strError = ex.Message; return -1; } finally { //*****************对数据库解读锁*************** m_lock.ReleaseReaderLock(); #if DEBUG_LOCK_SQLDATABASE this.container.WriteDebugInfo("SearchByUnion(),对'" + this.GetCaption("zh-cn") + "'数据库解读锁。"); #endif } return 0; }