很多通用查詢器，對查詢條件中的AND及OR的支持度不是很好，要麼全部是AND要麼全部是OR。筆者通過一段時間的摸索，終於完成了一個自己較為滿意的通用查詢器， 可以實現多條件的AND及OR，現將實現過程記錄一下： 1、在App.config中添加資料庫連接字元串。 <connectionStrings ...
很多通用查詢器，對查詢條件中的AND及OR的支持度不是很好，要麼全部是AND要麼全部是OR。筆者通過一段時間的摸索，終於完成了一個自己較為滿意的通用查詢器，
可以實現多條件的AND及OR，現將實現過程記錄一下：
1、在App.config中添加資料庫連接字元串。
<connectionStrings> <add name ="connString" connectionString="server=.;database=db_test;uid=sa;pwd=********;"/> </connectionStrings>
2、添加一個資料庫操作幫助類，命名為DBHelper。
/// <summary> /// SQL資料庫訪問類 /// </summary> public static class DBHelper { private static SqlConnection conn = null; /// <summary> /// 連接對象 /// </summary> public static SqlConnection Connection { get { string connString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString; if (conn == null) { try { conn = new SqlConnection(connString); } catch (Exception) { throw; } } return conn; } } /// <summary> /// 打開資料庫連接 /// </summary> public static void Open() { if (conn.State != ConnectionState.Open) { conn.Open(); } } /// <summary> /// 關閉資料庫連接 /// </summary> public static void Close() { if (conn.State == ConnectionState.Open) { conn.Close(); } } /// <summary> /// 創建一個新的命令對象 /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執行sql語句還是存儲過程。</param> /// <param name="parameters">參數數組</param> private static SqlCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters) { try { using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = commandText; cmd.CommandType = commandType; cmd.Connection = Connection; if (parameters != null) { foreach (SqlParameter param in parameters) { cmd.Parameters.Add(param); } } return cmd; } } catch (Exception) { throw; } } /// <summary> /// 執行SQL命令，並輸出影響的行數。 /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執行sql語句還是存儲過程。</param> /// <param name="parameters">參數數組</param> /// <return>返回影響的行數</return> public static int RunCommand(string commandText, CommandType commandType, params SqlParameter[] parameters) { try { using (SqlCommand cmd = CreateCommand(commandText, commandType, parameters)) { Open(); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); Close(); return rows; } } catch (Exception) { throw; } } /// <summary> /// 執行增刪改的方法 /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執行sql語句還是存儲過程。</param> /// <param name="parameters">參數數組</param> /// <returns>成功返回true</returns> public static bool Save(string commandText, CommandType commandType, params SqlParameter[] parameters) { try { using (SqlCommand cmd = CreateCommand(commandText, commandType, parameters)) { Open(); int n = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); Close(); if (n > 0) return true; else return false; } } catch (Exception) { throw; } } /// <summary> /// 執行增刪改的方法 /// </summary> /// <param name="commandText">要執行的sql語句</param> /// <param name="parameters">參數數組</param> /// <returns>成功返回true</returns> public static bool Save(string commandText, params SqlParameter[] parameters) { try { return Save(commandText, CommandType.Text, parameters); } catch (Exception) { throw; } } /// <summary> /// 獲得DataTable /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執行sql語句還是存儲過程。</param> /// <param name="parameters">參數數組</param> /// <returns></returns> public static DataTable GetTable(string commandText, CommandType commandType, params SqlParameter[] parameters) { try { using (SqlCommand cmd = CreateCommand(commandText, commandType, parameters)) { SqlDataAdapter da = new SqlDataAdapter { SelectCommand = cmd }; DataTable dt = new DataTable(); da.Fill(dt); cmd.Parameters.Clear(); Close(); return dt; } } catch (Exception) { throw; } } /// <summary> /// 獲得DataTable /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="parameters">參數數組</param> /// <returns></returns> public static DataTable GetTable(string commandText, params SqlParameter[] parameters) { try { return GetTable(commandText,CommandType.Text,parameters); } catch (Exception) { throw; } } /// <summary> /// 獲得DataTable /// </summary> /// <param name="commandText">要執行的sql語句</param> /// <returns></returns> public static DataTable GetTable(string commandText) { return GetTable(commandText, CommandType.Text, null); } /// <summary> /// 獲得SqlDataReader /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執行sql語句還是存儲過程。</param> /// <param name="parameters">參數數組</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText, CommandType commandType, params SqlParameter[] parameters) { try { using (SqlCommand cmd = CreateCommand(commandText, commandType, parameters)) { Open(); return cmd.ExecuteReader(); } } catch (Exception) { throw; } } /// <summary> /// 獲得SqlDataReader /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="parameters">參數數組</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText, params SqlParameter[] parameters) { return GetReader(commandText, CommandType.Text, parameters); } /// <summary> /// 獲得SqlDataReader /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執行sql語句還是存儲過程。</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText, CommandType commandType) { return GetReader(commandText, commandType, null); } /// <summary> /// 獲得SqlDataReader /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText) { return GetReader(commandText, CommandType.Text, null); } /// <summary> /// 執行SQL命令，並返回一個值。 /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執行sql語句還是存儲過程。</param> /// <param name="parameters">參數數組</param> /// <returns></returns> public static object GetScalar(string commandText, CommandType commandType, params SqlParameter[] parameters) { object obj = null; try { using (SqlCommand cmd = CreateCommand(commandText, commandType, parameters)) { Open(); obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); Close(); return obj; } } catch (Exception) { throw; } } /// <summary> /// 執行SQL命令，並返回一個值。 /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="parameters">參數數組</param> /// <returns></returns> public static object GetScalar(string commandText, params SqlParameter[] parameters) { return GetScalar(commandText, CommandType.Text, parameters); } /// <summary> /// 執行SQL命令，並返回一個值。 /// </summary> /// <param name="commandText">要執行的sql語句或存儲過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執行sql語句還是存儲過程。</param> /// <returns></returns> public static object GetScalar(string commandText, CommandType commandType) { return GetScalar(commandText, commandType, null); } /// <summary> /// 執行SQL命令，並返回一個值。 /// </summary> /// <returns></returns> public static object GetScalar(string commandText) { return GetScalar(commandText, CommandType.Text, null); } }
3、添加一個數據類型轉換類，命名為SqlDbTypeHelper。
/// <summary> /// SqlDbType轉換為C#數據類型 /// </summary> /// <param name="sqlDbType"></param> /// <returns></returns> public static Type SqlDbTypeToCsharpType(SqlDbType sqlDbType) { switch (sqlDbType) { case SqlDbType.BigInt: return typeof(Int64); case SqlDbType.Binary: return typeof(Object); case SqlDbType.Bit: return typeof(Boolean); case SqlDbType.Char: return typeof(String); case SqlDbType.DateTime: return typeof(DateTime); case SqlDbType.Decimal: return typeof(Decimal); case SqlDbType.Float: return typeof(Double); case SqlDbType.Image: return typeof(Object); case SqlDbType.Int: return typeof(Int32); case SqlDbType.Money: return typeof(Decimal); case SqlDbType.NChar: return typeof(String); case SqlDbType.NText: return typeof(String); case SqlDbType.NVarChar: return typeof(String); case SqlDbType.Real: return typeof(Single); case SqlDbType.SmallDateTime: return typeof(DateTime); case SqlDbType.SmallInt: return typeof(Int16); case SqlDbType.SmallMoney: return typeof(Decimal); case SqlDbType.Text: return typeof(String); case SqlDbType.Timestamp: return typeof(Object); case SqlDbType.TinyInt: return typeof(Byte); case SqlDbType.Udt://自定義的數據類型 return typeof(Object); case SqlDbType.UniqueIdentifier: return typeof(Object); case SqlDbType.VarBinary: return typeof(Object); case SqlDbType.VarChar: return typeof(String); case SqlDbType.Variant: return typeof(Object); case SqlDbType.Xml: return typeof(Object); default: return null; } } /// <summary> /// SQL Server數據類型轉換為SqlDbType類型 /// </summary> /// <param name="sqlTypeString"></param> /// <returns></returns> public static SqlDbType SqlTypeStringToSqlDbType(string sqlTypeString) { SqlDbType dbType = SqlDbType.Variant; //預設為Object switch (sqlTypeString) { case "int": dbType = SqlDbType.Int; break; case "varchar": dbType = SqlDbType.VarChar; break; case "bit": dbType = SqlDbType.Bit; break; case "datetime": dbType = SqlDbType.DateTime; break; case "decimal": dbType = SqlDbType.Decimal; break; case "float": dbType = SqlDbType.Float; break; case "image": dbType = SqlDbType.Image; break; case "money": dbType = SqlDbType.Money; break; case "ntext": dbType = SqlDbType.NText; break; case "nvarchar": dbType = SqlDbType.NVarChar; break; case "smalldatetime": dbType = SqlDbType.SmallDateTime; break; case "smallint": dbType = SqlDbType.SmallInt; break; case "text": dbType = SqlDbType.Text; break; case "bigint": dbType = SqlDbType.BigInt; break; case "binary": dbType = SqlDbType.Binary; break; case "char": dbType = SqlDbType.Char; break; case "nchar": dbType = SqlDbType.NChar; break; case "numeric": dbType = SqlDbType.Decimal; break; case "real": dbType = SqlDbType.Real; break; case "smallmoney": dbType = SqlDbType.SmallMoney; break; case "sql_variant": dbType = SqlDbType.Variant; break; case "timestamp": dbType = SqlDbType.Timestamp; break; case "tinyint": dbType = SqlDbType.TinyInt; break; case "uniqueidentifier": dbType = SqlDbType.UniqueIdentifier; break; case "varbinary": dbType = SqlDbType.VarBinary; break; case "xml": dbType = SqlDbType.Xml; break; } return dbType; }
4、添加一個自定義控制項，命名為：ConditionControl。
註：底下的是panel1，上面的控制項名分別為：cmbLeft1、cmbFieldText1、cmbOperator1、txtValue1、cmbRight1、cmbRelation1、btnAdd、btnRemove。
5、ConditionControl的代碼實現：
public partial class ConditionControl : UserControl { #region 欄位 private int conditionCount = 1; //panel個數 private int panelSpace = 2; //panel間隔 private string[] tempFieldNames, tempFieldTypes, tempFieldTexts; private Control tempTargetControl; //添加ConditionControl控制項承載控制項 #endregion #region 屬性 //欄位名 public string[] FieldNames { get { return tempFieldNames; } set { if (value != null) { tempFieldNames = new string[value.Length]; Array.Copy(value, tempFieldNames, value.Length); } } } //欄位數據類型 public string[] FieldTypes { get { return tempFieldTypes; } set { if (value != null) { tempFieldTypes = new string[value.Length]; Array.Copy(value, tempFieldTypes, value.Length); } } } //欄位文本 public string[] FieldTexts { get { return tempFieldTexts; } set { if (value != null) { tempFieldTexts = new string[value.Length]; Array.Copy(value, tempFieldTexts, value.Length); } } } //要處理的控制項 public Control TargetControl { get { return tempTargetControl; } set { if (value != null) { tempTargetControl = value; } } } #endregion #region 構造函數 /// <summary> /// 構造函數 /// </summary> public ConditionControl() { InitializeComponent(); } #endregion #region 設置其它下拉框數據源 /// <summary> /// 設置左括弧下拉框數據源 /// </summary> /// <param name="comboBox"></param> private void SetLeftDataSource(ComboBox comboBox) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("Value")); DataRow newRow = dt.NewRow(); newRow["Name"] = ""; newRow["Value"] = ""; dt.Rows.Add(newRow); newRow = dt.NewRow(); newRow["Name"] = "("; newRow["Value"] = "("; dt.Rows.Add(newRow); comboBox.DataSource = dt; comboBox.DisplayMember = "Name"; comboBox.ValueMember = "Value"; } /// <summary> /// 設置欄位文本下拉框數據源 /// </summary> /// <param name="comboBox"></param> private void SetFieldTextDataSource(ComboBox comboBox) { if (VerifyFieldMatch()) { comboBox.Items.AddRange(tempFieldTexts); } } /// <summary> /// 設置右括弧下拉框數據源 /// </summary> /// <param name="comboBox"></param> private void SetRightDataSource(ComboBox comboBox) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("Value")); DataRow newRow = dt.NewRow(); newRow["Name"] = ""; newRow["Value"] = ""; dt.Rows.Add(newRow); newRow = dt.NewRow(); newRow["Name"] = ")"; newRow["Value"] = ")"; dt.Rows.Add(newRow); comboBox.DataSource = dt; comboBox.DisplayMember = "Name"; comboBox.ValueMember = "Value"; } /// <summary> /// 設置關係符下拉框數據源 /// </summary> //
