Commits

Sachirou Inoue committed 50680fc

Add AND search.

  • Participants
  • Parent commits e0be826

Comments (0)

Files changed (4)

UserControl/DBConnect.cs

 namespace UserControl
 {
     /// <summary>
-    /// Userデータベースへ接続する為のSingletonクラスです。
+    /// データベースへ接続する為のSingletonクラスです。
     /// インスタンスを取得するには<see cref="GetInstance"/>を呼び出してください。
     /// Pageから直接使わず、代理classを立てる事を推奨します。
     /// </summary>
         /// <param name="columns">ここにIO入力値を利用してはなりません。</param>
         /// <param name="searchWord"></param>
         /// <returns></returns>
-        public int CountWithSearch(string table, string[] columns, string searchWord)
+        public int CountWithSearch(string table, string[] columns, string[] searchWords)
         {
-            string whereClause = "";
+            string concatColumn = string.Empty;
             foreach (string column in columns)
             {
-                if (whereClause != string.Empty) whereClause += " Or ";
-                whereClause += column + " Like ?SEARCH";
+                if (concatColumn != string.Empty) concatColumn += ",";
+                concatColumn += column + ",' '";
             }
-            return Count(table, whereClause, new Dictionary<string, object>() { { "SEARCH", string.Format("%{0}%", searchWord) } });
+            concatColumn = string.Format("Lower(Concat({0}))", concatColumn);
+
+            string whereClause = string.Empty;
+            var whereParametere = new Dictionary<string, object>();
+            for (int i = 0; i < searchWords.Length; ++i)
+            {
+                if (whereClause != string.Empty) whereClause += " And ";
+                whereClause += string.Format("{0} Like ?SEARCH{1} ", concatColumn, i);
+                whereParametere[string.Format("SEARCH{0}", i)] = string.Format("%{0}%", searchWords[i]);
+            }
+            return Count(table, whereClause, whereParametere);
         }
 
         /// <summary>
         /// <param name="start"></param>
         /// <param name="length"></param>
         /// <returns></returns>
-        public MySqlDataReader SelectWithSearch(MySqlConnection connection, string table, string[] columns, string searchWord,
+        public MySqlDataReader SelectWithSearch(MySqlConnection connection, string table, string[] columns, string[] searchWords,
             string order, string direction, uint start, uint length)
         {
-            string whereClause = string.Empty;
+            string concatColumn = string.Empty;
+            var columnList = new List<string>();
             foreach (string column in columns)
             {
-                if (whereClause != string.Empty) whereClause += " Or ";
-                whereClause += column + " Like ?SEARCH";
+                if (concatColumn != string.Empty) concatColumn += ",";
+                concatColumn += column + ",' '";
+                columnList.Add(column);
+            }
+            concatColumn = string.Format("Lower(Concat({0}))", concatColumn);
+            columnList.Add(concatColumn);
+
+            string whereClause = string.Empty;
+            var whereParametere = new Dictionary<string, object>();
+            for (int i = 0; i < searchWords.Length; ++i)
+            {
+                if (whereClause != string.Empty) whereClause += " And ";
+                whereClause += string.Format("{0} Like ?SEARCH{1} ", concatColumn, i);
+                whereParametere[string.Format("SEARCH{0}", i)] = string.Format("%{0}%", searchWords[i]);
             }
-            return Select(connection, table, columns, whereClause,
-                new Dictionary<string, object>() { { "SEARCH", string.Format("%{0}%", searchWord) } },
-                order, direction, start, length);
+            return Select(connection, table, columnList.ToArray(), whereClause, whereParametere, order, direction, start, length);
         }
 
         /// <summary>

UserControl/UserControl.csproj

   <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
     <DebugSymbols>true</DebugSymbols>
     <DebugType>full</DebugType>
-    <Optimize>true</Optimize>
+    <Optimize>false</Optimize>
     <OutputPath>bin\</OutputPath>
     <DefineConstants>DEBUG;TRACE</DefineConstants>
     <ErrorReport>prompt</ErrorReport>

UserControl/Users.cs

         private static string TableName = "user";
         public uint Length = 10;
         public uint Page = 1;
+
         public string Search { get; set; }
+        public string[] SearchWords
+        {
+            get
+            {
+                if (Search == null) return new string[] { "" };
+                string[] words = Search.Split(new char[] { ' ' });
+                for (int i = 0; i < words.Length; ++i) words[i] = words[i].ToLower();
+                return words;
+            }
+        }
 
         private string _Order;
         /// <summary>
             var db = DBConnect.GetInstance();
             int count;
             if (Search == null) count = db.Count(TableName);
-            else count = db.CountWithSearch(TableName, new string[] { "id", "name", "age" }, Search);
+            else count = db.CountWithSearch(TableName, new string[] { "id", "name", "age" }, SearchWords);
             return count;
         }
 
                 MySql.Data.MySqlClient.MySqlDataReader reader;
                 if (Search != null || Order != null)
                 {
-                    reader = db.SelectWithSearch(connection, TableName, columns, Search, Order, Direction, (Page - 1) * Length, Length);
+                    reader = db.SelectWithSearch(connection, TableName, columns, SearchWords, Order, Direction, (Page - 1) * Length, Length);
                 }
                 else
                 {

UserControl/view.aspx.cs

                 Response.Redirect(string.Format("update.aspx?id={0}", HttpUtility.HtmlEncode(loginID)));
             }
             
-            ShowContents(loginID);
+            if (!IsPostBack) ShowContents(loginID);
         }
 
         protected void ButtonSearch_Click(object sender, EventArgs e)