/// 重建索引
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="table">要创建索引的表</param>
public static void RebuildIndex(string connStr, string table)
{
string sql = string.Format("exec sp_rebuild '{0}',5000,1", table);
ExecuteNonQueryIndex(sql, CommandType.StoredProcedure, connStr);
}
/// <summary>
/// 执行索引同步
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="table">要同步的表</param>
public static void SynchronizeIndex(string connStr, string table)
{
//string sql = string.Format("exec sp_synchronizetable '{0}',5000,2", table);
//ExecuteNonQueryIndex(sql, CommandType.StoredProcedure, connStr);
TableSynchronization _TableSync;
TableSynchronization.OptimizeOption option = TableSynchronization.OptimizeOption.Minimum;
int step = 5000;
HubbleConnection conn = new HubbleConnection(connStr);
conn.Open();
_TableSync = new TableSynchronization(conn, table, step, option);
bool con = _TableSync.Synchronize();
//同步完成后优化索引
OptimizeIndex(connStr, table);
}
/// <summary>
/// 优化索引
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="table">要优化的表</param>
public static void OptimizeIndex(string connStr, string table)
{
string sql = string.Format("exec sp_optimizetable '{0}',1", table);
ExecuteNonQueryIndex(sql, CommandType.StoredProcedure, connStr);
}
/// <summary>
/// 插入索引
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="table">要插入索引的表</param>
/// <param name="values">要插入的值</param>
public static void InsertIndex(string connStr, string table,string values)
{
string columnsStr = GetTableColumnsStr(connStr, table);
string sql = string.Format("insert into {0} ({1}) values ({2})", table, columnsStr,values);
ExecuteNonQueryIndex(sql, CommandType.Text, connStr);
}
/// <summary>
/// 删除索引
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="table">要删除索引的表</param>
/// <param name="condition">需要删除索引的条件</param>
public static void DeleteIndex(string connStr, string table, string condition)
{
string sql = string.Format("delete from {0} where 1= 1 and {1}", table, string.IsNullOrEmpty(condition) ? " 2=2" : condition);
ExecuteNonQueryIndex(sql, CommandType.Text, connStr);
}
/// <summary>
/// 修改索引
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="table">要修改索引的表</param>
/// <param name="setValues">需要设置的值</param>
/// <param name="condition">要修改索引的条件</param>
public static void UpdateIndex(string connStr, string table, string setValues, string condition)
{
string sql = string.Format("update {0} set {1} where 1=1 and {2}", table, setValues, string.IsNullOrEmpty(condition) ? " 2=2 " : condition);
ExecuteNonQueryIndex(sql, CommandType.Text, connStr);
}
/// <summary>
/// 根据table查找列名,并组成字符串
/// </summary>
/// <param name="connStr">要连接的字符串</param>
/// <param name="table">表名</param>
/// <returns>列名字符串</returns>
public static string GetTableColumnsStr(string connStr, string table)
{
string sql = string.Format("exec sp_columns '{0}'", table);
DataSet ds = ExecuteQueryIndex(sql, CommandType.StoredProcedure, connStr);
StringBuilder columnsStr = new StringBuilder();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
foreach (System.Data.DataRow row in ds.Tables[0].Rows)
{
columnsStr.Append(row["FieldName"].ToString()+",");
}
}
if (columnsStr.ToString().EndsWith(","))
columnsStr.Remove(columnsStr.Length - 1, 1);
return columnsStr.ToString();
}
/// <summary>
/// 取得指定表需要分词的词
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="table">要分词的表</param>
/// <returns>需要分词的列表</returns>
public static Dictionary<string, string> GetAnalyzerName(string connStr, string table)
{
string sql = string.Format("exec sp_columns '{0}'", table);
DataSet ds = ExecuteQueryIndex(sql, CommandType.StoredProcedure, connStr);
Dictionary<string, string> dict = new Dictionary<string, string>();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
foreach (System.Data.DataRow row in ds.Tables[0].Rows)
{
if (row["IndexType"].ToString() == "Tokenized")
{
dict.Add(row["FieldName"].ToString(), row["Analyzer"].ToString());
}
}
}
return dict;
}
/// <summary>
/// 根据字段不同的类型选择不同的分词器
/// </summary>
/// <param name="analyzer"></param>
/// <returns></returns>
public static IAnalyzer GetAnalyzer(string analyzer)
{
analyzer=analyzer.ToLower();
if (analyzer == "pangusegment")
return new PanGuAnalyzer();
else if (analyzer == "englishanalyzer")
return new EnglishAnalyzer();
else
return new SimpleAnalyzer();
}
/// <summary>
/// 取得匹配字符串
/// </summary>
/// <param name="connStr">要连接的字符串</param>
/// <param name="table">相关的表</param>
/// <param name="keyword">关键词</param>
/// <returns></returns>
public static Dictionary<string, string> GetMatchString(string connStr, string table, string keyword)
{
Dictionary<string, string> dictAnalyzer = GetAnalyzerName(connStr, table);
Dictionary<string, string> dictMatchStr = new Dictionary<string, string>();
using (HubbleAsyncConnection conn = new HubbleAsyncConnection(connStr))
{
conn.Open();
HubbleCommand cmd = new HubbleCommand(conn);
foreach (string key in dictAnalyzer.Keys)
{
string bySpace = string.Empty;
string matchString = cmd.GetKeywordAnalyzerStringFromServer(table, key, keyword, int.MaxValue, out bySpace);
dictMatchStr.Add(key, matchString);
}
}
return dictMatchStr;
}
/// <summary>
/// 取得匹配字符串
/// </summary>
/// <param name="connStr">要连接的字符串</param>
/// <param name="table">相关的表</param>
/// <param name="field">查询的字段</param>
/// <param name="keyword">关键词</param>
/// <returns></returns>
public static string GetMatchString(string connStr,string table,string field, string keyword)
{
StringBuilder matchString=new StringBuilder ();
string sql=string.Format(" exec SP_FieldAnalyze '{0}', '{1}', '{2}'",table,field,keyword);
DataSet ds = ExecuteQueryIndex(sql, CommandType.StoredProcedure, connStr);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
matchString.AppendFormat(" {0}^{1}^{2} ", row["Word"], row["Rank"], row["Position"]);
}
}
return matchString.ToString();
}
/// <summary>
/// 执行检索
/// </summary>
/// <param name="keyword">要检索的关键词</param>
/// <param name="pageSize">要显示的大小</param>
/// <param name="currentPage">当前页数</param>
/// <param name="order">排序</param>
/// <param name="cost">话费的时间</param>
/// <param name="records">检索的总记录数</param>
/// <returns></returns>
public static DataSet SearchIndex(string connStr, string keyword, string table, int pageSize, int currentPage, out long cost, out int records, out string sql, string order = "score")
{
cost = 1;
Stopwatch sw = new Stopwatch(); //跑秒
sw.Start();
StringBuilder condition = new StringBuilder();
Dictionary<string, string> dictMatch = GetMatchString(connStr, table, keyword);
//如果没有条件,则查出所有
condition.Append(dictMatch.Keys.Count > 0 ? " 1=2 " : " 1=1 ");
foreach (string key in dictMatch.Keys)
{
condition.Append(" or ");
condition.Append(key);
condition.Append(" match '");
condition.Append(dictMatch[key].ToString());
condition.Append("'");
}
//sql = string.Format("select between {0} to {1} * from {2} where {3} order by {4} desc ",(currentPage-1)*pageSize,currentPage*pageSize-1,table,condition.ToString(),order);
sql = string.Format("select top {0} * from {1} where {2} order by score desc", pageSize,table, condition);
DataSet ds = ExecuteQueryIndex(sql, CommandType.Text, connStr);
sw.Stop();
cost = sw.ElapsedMilliseconds;
records = ds.Tables[0].MinimumCapacity;
return ds;
}
/// <summary>
/// 执行检索
/// </summary>
/// <param name="keyword">要检索的关键词</param>
/// <param name="pageSize">要显示的大小</param>
/// <param name="currentPage">当前页数</param>
/// <param name="order">排序</param>
/// <param name="cost">话费的时间</param>
/// <param name="records">检索的总记录数</param>
/// <returns></returns>
public static DataSet SearchIndex(string connStr, string keyword, string table, int pageSize, int currentPage, out int records, string order = "score")
{
StringBuilder condition = new StringBuilder();
Dictionary<string, string> dictMatch = GetMatchString(connStr, table, keyword);
//如果没有条件,则查出所有
condition.Append(dictMatch.Keys.Count > 0 ? " 1=2 " : " 1=1 ");
foreach (string key in dictMatch.Keys)
{
condition.Append(" or ");
condition.Append(key);
condition.Append(" match '");
condition.Append(dictMatch[key].ToString());
condition.Append("'");
}
//sql = string.Format("select between {0} to {1} * from {2} where {3} order by {4} desc ",(currentPage-1)*pageSize,currentPage*pageSize-1,table,condition.ToString(),order);
string sql = string.Format("select top {0} * from {1} where {2} order by score desc",pageSize,table, condition);
DataSet ds = ExecuteQueryIndex(sql, CommandType.Text, connStr);
records = ds.Tables[0].MinimumCapacity;
return ds;
}
/// <summary>
/// 执行索引的更新
/// </summary>
/// <param name="sql">要执行的命令</param>
/// <param name="cmdType">命令类型</param>
/// <param name="connStr">连接字符串</param>
public static void ExecuteNonQueryIndex(string sql, CommandType cmdType, string connStr)
{
try
{
using (HubbleAsyncConnection conn = new HubbleAsyncConnection(connStr))
{
conn.Open();
HubbleCommand cmd = new HubbleCommand(conn);
cmd.CommandText = sql;
cmd.CommandType = cmdType;
int result = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
WriteToLog(ex.StackTrace);
}
}
/// <summary>
/// 执行索引的查询操作
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="connStr"></param>
/// <returns></returns>
public static DataSet ExecuteQueryIndex(string sql, CommandType cmdType, string connStr)
{
DataSet ds = new DataSet();
try
{
using (HubbleAsyncConnection conn = new HubbleAsyncConnection(connStr))
{
conn.Open();
HubbleCommand cmd = new HubbleCommand(conn);
cmd.CommandText = sql;
cmd.CommandType = cmdType;
cmd.CommandTimeout = 5;
ds = cmd.Query(cmd.CommandTimeout);
conn.Close();
WriteToLog(sql);
}
}
catch (Exception ex)
{
WriteToLog(ex.StackTrace);
}
return ds;
}
/// <summary>
/// 写入日志
/// </summary>
/// <param name="content">要写入的内容</param>
public static void WriteToLog(string content)
{
//string path = "c:\\a.txt";
//if (!System.IO.File.Exists(path))
//{
// System.IO.File.Create(path);
//}
//using (StreamWriter sw = System.IO.File.AppendText(path))
//{
// sw.Write("---------------" + DateTime.Now + "\r\n");
// sw.Write("---------------" + content + "\r\n");
// sw.Flush();
// sw.Close();
//}
}
/// <summary>
/// 高亮显示
/// </summary>
/// <param name="oldData">原始字符窜</param>
/// <param name="keyword">高亮显示的字符串</param>
/// <returns></returns>
public static string AddLight(string oldData, string keyword)//对结果加高亮
{
string newStr =null;
if (!string.IsNullOrEmpty(oldData))
{
oldData = HttpUtility.HtmlEncode(oldData);
Highlighter highlighter =new Highlighter(hbIndexFormatter, new PanGuAnalyzer());
//highlighter.FragmentSize = string.IsNullOrEmpty(oldData) ? 100 : oldData.Length;
highlighter.FragmentSize = 50;
newStr = highlighter.GetBestFragment(keyword.Trim(), oldData.Trim());
if (string.IsNullOrEmpty(newStr))
{
newStr = oldData;
}
}
return newStr;
}