public IEnumerable <BrgSearchResultModel> Search()
{
List <BrgSearchResultModel> result = null;
var sSql = @"
SELECT
BrgID, BrgName
FROM
Brg ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
result = new List <BrgSearchResultModel>();
while (dr.Read())
{
var item = new BrgSearchResultModel
{
BrgID = dr["BrgID"].ToString(),
BrgName = dr["BrgName"].ToString(),
};
result.Add(item);
}
}
}
}
return(result);
}
public IEnumerable <BrgSearchResultModel> Search(string keyword)
{
List <BrgSearchResultModel> result = null;
if (keyword.Trim() == "")
{
return(null);
}
string[] words = keyword.Split(' ');
var sSql = @"
SELECT
BrgID, BrgName
FROM
Brg
WHERE ";
if (words.Count() >= 1)
{
sSql += @" BrgName LIKE @BrgName1 ";
}
if (words.Count() >= 2)
{
sSql += @" AND BrgName LIKE @BrgName2 ";
}
if (words.Count() >= 3)
{
sSql += @" AND BrgName LIKE @BrgName3 ";
}
if (words.Count() >= 4)
{
sSql += @" AND BrgName LIKE @BrgName4 ";
}
if (words.Count() >= 5)
{
sSql += @" AND BrgName LIKE @BrgName5 ";
}
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
if (words.Count() >= 1)
{
cmd.AddParam("@BrgName1", "%" + words[0] + "%");
}
if (words.Count() >= 2)
{
cmd.AddParam("@BrgName2", "%" + words[1] + "%");
}
if (words.Count() >= 3)
{
cmd.AddParam("@BrgName3", "%" + words[2] + "%");
}
if (words.Count() >= 4)
{
cmd.AddParam("@BrgName4", "%" + words[3] + "%");
}
if (words.Count() >= 5)
{
cmd.AddParam("@BrgName5", "%" + words[4] + "%");
}
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
result = new List <BrgSearchResultModel>();
while (dr.Read())
{
var item = new BrgSearchResultModel
{
BrgID = dr["BrgID"].ToString(),
BrgName = dr["BrgName"].ToString()
};
result.Add(item);
}
}
}
}
return(result);
}
