/*
* 实现功能：
* 1.输入字符长度超过设定值后，筛选出包含输入内容的项目显示在控件下（上）面的列表框中
* 2.获得焦点时在控件下（上）面的列表框中显示最近的输入内容（区分不同程序/不同窗体/不同控件/不同用户）
* 未实现功能：清理超过一定数量的历史输入记录
使用方法：
1. 创建历史输入表
CREATE TABLE [dbo].[UserInputHistory] (
[ProductName] VARCHAR (255) NULL,
[FormName] VARCHAR (255) NULL,
[ControlName] VARCHAR (255) NULL,
[UserID] VARCHAR (255) NULL,
[InputText] VARCHAR (255) NULL,
[ID] INT IDENTITY (1, 1) NOT NULL,
CONSTRAINT [PK_UserInputHistory] PRIMARY KEY ([ID])
);
2.在设计时填写控件属性附加类别里的内容;
3.在Form_Load里加入Init();
*/
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.Text;
using System.Threading;
namespace werp
{
public class InputHistoryAndFilterTextBox : System.Windows.Forms.TextBox
{
/*
* 实现功能：
* 1.输入字符长度超过设定值后，筛选出包含输入内容的项目显示在控件下（上）面的列表框中
* 2.获得焦点时在控件下（上）面的列表框中显示最近的输入内容（区分不同程序/不同窗体/不同控件/不同用户）
* 未实现功能：清理超过一定数量的历史输入记录
使用方法：
1. 创建历史输入表
CREATE TABLE [dbo].[UserInputHistory] (
[ProductName] VARCHAR (255) NULL,
[FormName] VARCHAR (255) NULL,
[ControlName] VARCHAR (255) NULL,
[UserID] VARCHAR (255) NULL,
[InputText] VARCHAR (255) NULL,
[ID] INT IDENTITY (1, 1) NOT NULL,
CONSTRAINT [PK_UserInputHistory] PRIMARY KEY ([ID])
);
2.在设计时填写控件属性附加类别里的内容;
3.在Form_Load里加入Init();
*/
/// <summary>
/// 查询的表名
/// </summary>
/// <value>The name of the table.</value>
[Description("查询的表名"), Category("附加"), DefaultValue("t_item")]
public string TableName { get; set; } = "t_item";
/// <summary>
/// 筛选的字段名
/// </summary>
/// <value>The name of the field.</value>
[Description("筛选的字段名"), Category("附加"), DefaultValue("item_num")]
public string FieldName { get; set; } = "item_num";
/// <summary>
/// 筛选起始长度
/// </summary>
/// <value>The length of the find.</value>
[Description("筛选起始长度"), Category("附加"), DefaultValue(3)]
public int FindLength { get; set; } = 3;
/// <summary>
/// 附件查询条件
/// </summary>
/// <value>The addi conditions.</value>
[Description("附件查询条件"), Category("附加"), DefaultValue("")]
public string AddiConditions { get; set; } = "";
/// <summary>
/// 数据库名称
/// </summary>
/// <value>The name of the database.</value>
[Description("数据库名称"), Category("附加"), DefaultValue("wsprint")]
public string DatabaseName { get; set; } = "wsprint";
/// <summary>
/// 数据库用户名称
/// </summary>
/// <value>The uid.</value>
[Description("数据库用户名称"), Category("附加"), DefaultValue("sa")]
public string UID { get; set; } = "sa";
/// <summary>
/// 数据库用户密码.
/// </summary>
/// <value>The password.</value>
[Description("数据库用户密码"), Category("附加"), DefaultValue("sa")]
public string PWD { get; set; } = "sa";
/// <summary>
/// 数据库服务器名称或IP
/// </summary>
/// <value>The name of the serve.</value>
[Description("数据库服务器名称或IP"), Category("附加"), DefaultValue("xx-erpsvr")]
public string ServeName { get; set; } = "xx-erpsvr";
[Description("保留历史输入数量"), Category("附加"), DefaultValue(10)]
public int KeepHistorys { get; set; } = 10;
[Description("允许历史输入记录"), Category("附加"), DefaultValue(false)]
public bool EnabledHistory { get; set; } = false;
private System.Windows.Forms.ListBox ItemsListBox { get; set; } = new System.Windows.Forms.ListBox();
/// <summary>
/// 记录历史操作的用户名
/// </summary>
/// <value>The user identifier.</value>
[Description("记录历史操作的用户名"), Category("附加"), DefaultValue("")]
public string UserId { get; set; } = "";
System.Data.DataTable dt = new System.Data.DataTable(), dth = new System.Data.DataTable();
System.Data.SqlClient.SqlConnection conn;
System.Data.SqlClient.SqlCommand cmd;
public InputHistoryAndFilterTextBox()
{
}
public void Init()
{
ItemsListBox.Parent = this.Parent;
ItemsListBox.Top = this.Top + this.Height;
if (ItemsListBox.Top + ItemsListBox.Height > this.Parent.Height)
ItemsListBox.Top = this.Top - ItemsListBox.Height;
ItemsListBox.Left = this.Left;
ItemsListBox.Width = this.Width;
ItemsListBox.Visible = false;
ItemsListBox.BringToFront();
conn = new System.Data.SqlClient.SqlConnection("Data Source=" + ServeName + ";Initial Catalog=" + DatabaseName + ";Connect Timeout=30;UID = " + UID + ";PWD=" + PWD);
cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = conn;
this.TextChanged += FilterTextBox_TextChanged;
this.LostFocus += FilterTextBox_LostFocus;
this.KeyDown += FilterTextBox_KeyDown;
Thread thread = new Thread(LoadData);
ItemsListBox.LostFocus += ItemsListBox_LostFocus;
ItemsListBox.MouseDoubleClick += ItemsListBox_MouseDoubleClick;
this.Validated += FilterTextBox_Validated;
this.GotFocus += FilterTextBox_GotFocus;
thread.Start();
}
private void FilterTextBox_GotFocus(object sender, EventArgs e)
{
if (dth.Rows.Count > 0 && this.Text.Trim() == "")
{
ItemsListBox.Items.Clear();
foreach (System.Data.DataRow row in dth.Rows)
{
ItemsListBox.Items.Add(row[0].ToString());
}
ItemsListBox.Visible = true;
}
}
private void FilterTextBox_Validated(object sender, EventArgs e)
{
if (this.Text.Trim().Length >= this.FindLength)
{
try
{
cmd.CommandText = string.Format("insert into UserInputHistory (ProductName,FormName,ControlName,UserID,InputText) values ('{0}','{1}','{2}','{3}','{4}')",
this.ProductName, this.FindForm().Name, this.Name, UserId, this.Text);
if (cmd.Connection.State == System.Data.ConnectionState.Closed) cmd.Connection.Open();
if(cmd.ExecuteNonQuery()>0)
{
Thread thread = new Thread(LoadInputData);
thread.Start();
}
}
catch
{
throw;
}
finally
{
cmd.Connection.Close();
}
}
}
private void ItemsListBox_MouseDoubleClick(object sender, System.Windows.Forms.MouseEventArgs e)
{
if (ItemsListBox.SelectedItem != null)
{
this.Text = ItemsListBox.SelectedItem.ToString();
}
ItemsListBox.Visible = false;
this.Focus();
}
private void ItemsListBox_LostFocus(object sender, EventArgs e)
{
ItemsListBox.Visible = false;
}
private void FilterTextBox_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
{
switch (e.KeyCode)
{
case System.Windows.Forms.Keys.Up:
if (ItemsListBox.SelectedIndex > 0)
{
ItemsListBox.SelectedIndex -= 1;
}
break;
case System.Windows.Forms.Keys.Down:
if (ItemsListBox.SelectedIndex < ItemsListBox.Items.Count - 1)
ItemsListBox.SelectedIndex += 1;
break;
case System.Windows.Forms.Keys.Enter:
if (ItemsListBox.SelectedItem != null)
{
this.Text = ItemsListBox.SelectedItem.ToString();
}
ItemsListBox.Visible = false;
break;
}
}
private void FilterTextBox_LostFocus(object sender, EventArgs e)
{
if (!ItemsListBox.Focused)
ItemsListBox.Visible = false;
}
private void FilterTextBox_TextChanged(object sender, EventArgs e)
{
string text = this.Text.Trim();
if (text.Length >= FindLength)
{
ItemsListBox.Items.Clear();
foreach (var row in dt.Select(FieldName + " like '%" + text + "%'", FieldName))
{
ItemsListBox.Items.Add(row[0].ToString());
}
ItemsListBox.Visible = true;
}
else
{
ItemsListBox.Visible = false;
}
}
private void LoadData()
{
try
{
//载入筛选内容
cmd.CommandText = "select " + FieldName + " from " + TableName + (AddiConditions.Trim() == "" ? "" : " where ") + AddiConditions + " group by " + FieldName;
new System.Data.SqlClient.SqlDataAdapter(cmd).Fill(dt);
LoadInputData();
}
catch (Exception ex)
{
throw new Exception("使用前请先设置连接属性" + ex.Message);
}
}
private void LoadInputData()
{
try
{
//载入历史输入内容
cmd.CommandText = string.Format("select distinct TOP {4} InputText from UserInputHistory where ProductName = '{0}' and FormName = '{1}' and ControlName = '{2}' and UserID = '{3}' order by ID desc ", ProductName, FindForm().Name, Name, UserId, KeepHistorys);
new System.Data.SqlClient.SqlDataAdapter(cmd).Fill(dth);
}
catch (Exception ex)
{
throw new Exception("使用前请先设置连接属性" + ex.Message);
}
}
}
}
using System; using System.Collections.Generic; using System.ComponentModel; using System.ComponentModel.Design; using System.Text; using System.Threading; namespace werp { public class InputHistoryAndFilterTextBox : System.Windows.Forms.TextBox { /* * 实现功能： * 1.输入字符长度超过设定值后，筛选出包含输入内容的项目显示在控件下（上）面的列表框中 * 2.获得焦点时在控件下（上）面的列表框中显示最近的输入内容（区分不同程序/不同窗体/不同控件/不同用户） * 未实现功能：清理超过一定数量的历史输入记录 使用方法： 1. 创建历史输入表 CREATE TABLE [dbo].[UserInputHistory] ( [ProductName] VARCHAR (255) NULL, [FormName] VARCHAR (255) NULL, [ControlName] VARCHAR (255) NULL, [UserID] VARCHAR (255) NULL, [InputText] VARCHAR (255) NULL, [ID] INT IDENTITY (1, 1) NOT NULL, CONSTRAINT [PK_UserInputHistory] PRIMARY KEY ([ID]) ); 2.在设计时填写控件属性附加类别里的内容; 3.在Form_Load里加入Init(); */ /// <summary> /// 查询的表名 /// </summary> /// <value>The name of the table.</value> [Description("查询的表名"), Category("附加"), DefaultValue("t_item")] public string TableName { get; set; } = "t_item"; /// <summary> /// 筛选的字段名 /// </summary> /// <value>The name of the field.</value> [Description("筛选的字段名"), Category("附加"), DefaultValue("item_num")] public string FieldName { get; set; } = "item_num"; /// <summary> /// 筛选起始长度 /// </summary> /// <value>The length of the find.</value> [Description("筛选起始长度"), Category("附加"), DefaultValue(3)] public int FindLength { get; set; } = 3; /// <summary> /// 附件查询条件 /// </summary> /// <value>The addi conditions.</value> [Description("附件查询条件"), Category("附加"), DefaultValue("")] public string AddiConditions { get; set; } = ""; /// <summary> /// 数据库名称 /// </summary> /// <value>The name of the database.</value> [Description("数据库名称"), Category("附加"), DefaultValue("wsprint")] public string DatabaseName { get; set; } = "wsprint"; /// <summary> /// 数据库用户名称 /// </summary> /// <value>The uid.</value> [Description("数据库用户名称"), Category("附加"), DefaultValue("sa")] public string UID { get; set; } = "sa"; /// <summary> /// 数据库用户密码. /// </summary> /// <value>The password.</value> [Description("数据库用户密码"), Category("附加"), DefaultValue("sa")] public string PWD { get; set; } = "sa"; /// <summary> /// 数据库服务器名称或IP /// </summary> /// <value>The name of the serve.</value> [Description("数据库服务器名称或IP"), Category("附加"), DefaultValue("xx-erpsvr")] public string ServeName { get; set; } = "xx-erpsvr"; [Description("保留历史输入数量"), Category("附加"), DefaultValue(10)] public int KeepHistorys { get; set; } = 10; [Description("允许历史输入记录"), Category("附加"), DefaultValue(false)] public bool EnabledHistory { get; set; } = false; private System.Windows.Forms.ListBox ItemsListBox { get; set; } = new System.Windows.Forms.ListBox(); /// <summary> /// 记录历史操作的用户名 /// </summary> /// <value>The user identifier.</value> [Description("记录历史操作的用户名"), Category("附加"), DefaultValue("")] public string UserId { get; set; } = ""; System.Data.DataTable dt = new System.Data.DataTable(), dth = new System.Data.DataTable(); System.Data.SqlClient.SqlConnection conn; System.Data.SqlClient.SqlCommand cmd; public InputHistoryAndFilterTextBox() { } public void Init() { ItemsListBox.Parent = this.Parent; ItemsListBox.Top = this.Top + this.Height; if (ItemsListBox.Top + ItemsListBox.Height > this.Parent.Height) ItemsListBox.Top = this.Top - ItemsListBox.Height; ItemsListBox.Left = this.Left; ItemsListBox.Width = this.Width; ItemsListBox.Visible = false; ItemsListBox.BringToFront(); conn = new System.Data.SqlClient.SqlConnection("Data Source=" + ServeName + ";Initial Catalog=" + DatabaseName + ";Connect Timeout=30;UID = " + UID + ";PWD=" + PWD); cmd = new System.Data.SqlClient.SqlCommand(); cmd.Connection = conn; this.TextChanged += FilterTextBox_TextChanged; this.LostFocus += FilterTextBox_LostFocus; this.KeyDown += FilterTextBox_KeyDown; Thread thread = new Thread(LoadData); ItemsListBox.LostFocus += ItemsListBox_LostFocus; ItemsListBox.MouseDoubleClick += ItemsListBox_MouseDoubleClick; this.Validated += FilterTextBox_Validated; this.GotFocus += FilterTextBox_GotFocus; thread.Start(); } private void FilterTextBox_GotFocus(object sender, EventArgs e) { if (dth.Rows.Count > 0 && this.Text.Trim() == "") { ItemsListBox.Items.Clear(); foreach (System.Data.DataRow row in dth.Rows) { ItemsListBox.Items.Add(row[0].ToString()); } ItemsListBox.Visible = true; } } private void FilterTextBox_Validated(object sender, EventArgs e) { if (this.Text.Trim().Length >= this.FindLength) { try { cmd.CommandText = string.Format("insert into UserInputHistory (ProductName,FormName,ControlName,UserID,InputText) values ('{0}','{1}','{2}','{3}','{4}')", this.ProductName, this.FindForm().Name, this.Name, UserId, this.Text); if (cmd.Connection.State == System.Data.ConnectionState.Closed) cmd.Connection.Open(); if(cmd.ExecuteNonQuery()>0) { Thread thread = new Thread(LoadInputData); thread.Start(); } } catch { throw; } finally { cmd.Connection.Close(); } } } private void ItemsListBox_MouseDoubleClick(object sender, System.Windows.Forms.MouseEventArgs e) { if (ItemsListBox.SelectedItem != null) { this.Text = ItemsListBox.SelectedItem.ToString(); } ItemsListBox.Visible = false; this.Focus(); } private void ItemsListBox_LostFocus(object sender, EventArgs e) { ItemsListBox.Visible = false; } private void FilterTextBox_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e) { switch (e.KeyCode) { case System.Windows.Forms.Keys.Up: if (ItemsListBox.SelectedIndex > 0) { ItemsListBox.SelectedIndex -= 1; } break; case System.Windows.Forms.Keys.Down: if (ItemsListBox.SelectedIndex < ItemsListBox.Items.Count - 1) ItemsListBox.SelectedIndex += 1; break; case System.Windows.Forms.Keys.Enter: if (ItemsListBox.SelectedItem != null) { this.Text = ItemsListBox.SelectedItem.ToString(); } ItemsListBox.Visible = false; break; } } private void FilterTextBox_LostFocus(object sender, EventArgs e) { if (!ItemsListBox.Focused) ItemsListBox.Visible = false; } private void FilterTextBox_TextChanged(object sender, EventArgs e) { string text = this.Text.Trim(); if (text.Length >= FindLength) { ItemsListBox.Items.Clear(); foreach (var row in dt.Select(FieldName + " like '%" + text + "%'", FieldName)) { ItemsListBox.Items.Add(row[0].ToString()); } ItemsListBox.Visible = true; } else { ItemsListBox.Visible = false; } } private void LoadData() { try { //载入筛选内容 cmd.CommandText = "select " + FieldName + " from " + TableName + (AddiConditions.Trim() == "" ? "" : " where ") + AddiConditions + " group by " + FieldName; new System.Data.SqlClient.SqlDataAdapter(cmd).Fill(dt); LoadInputData(); } catch (Exception ex) { throw new Exception("使用前请先设置连接属性" + ex.Message); } } private void LoadInputData() { try { //载入历史输入内容 cmd.CommandText = string.Format("select distinct TOP {4} InputText from UserInputHistory where ProductName = '{0}' and FormName = '{1}' and ControlName = '{2}' and UserID = '{3}' order by ID desc ", ProductName, FindForm().Name, Name, UserId, KeepHistorys); new System.Data.SqlClient.SqlDataAdapter(cmd).Fill(dth); } catch (Exception ex) { throw new Exception("使用前请先设置连接属性" + ex.Message); } } } }