c#搜索方法

本文关键字：方法 搜索 | 更新日期: 2023-09-27 18:17:03

我在使用以下搜索方法时遇到了麻烦:

public override List<Team> Search(Dictionary<string, string> prms, int pageSize, int page, out int results) 
    {
        var tresults = new List<Team>();
        string temp1 = "";
        string temp2 = "";
        using (SqlConnection conn = DB.GetSqlConnection())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = @"Search";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (KeyValuePair<string, string> pair in prms)
                {
                   temp1 = pair.Key;
                   temp2 = pair.Value;
                }
                if (temp1 == "TeamName")
                {
                    SqlParameter p1 = new SqlParameter("TeamName", System.Data.SqlDbType.VarChar);
                    p1.Value = temp2;
                    cmd.Parameters.Add(p1);
                    SqlParameter p2 = new SqlParameter("CityName", System.Data.SqlDbType.VarChar);
                    p2.Value = null;
                    cmd.Parameters.Add(p2);
                }
                else if (temp1 == "CityName")
                {
                    SqlParameter p1 = new SqlParameter("TeamName", System.Data.SqlDbType.VarChar);
                    p1.Value = null;
                    cmd.Parameters.Add(p1);
                    SqlParameter p2 = new SqlParameter("CityName", System.Data.SqlDbType.VarChar);
                    p2.Value = temp2;
                    cmd.Parameters.Add(p2);
                }
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
        }
        //results = 1 + 1;
        throw new NotImplementedException("Must be implemented by class. ");
    }

我要做的基本上就是这个测试要做的:

[TestMethod]
    public void SearchForTeam()
    {
        var dic = new Dictionary<string, string>();
        int total = 0;
        dic.Add("TeamName", "Patriots");
        var nd = new TeamRepository();
        var teams = nd.Search(dic, 100, 1, out total);
        Assert.IsTrue(teams.Find(p => p.TeamName == "Patriots") != null);
    }

我要做的是通过团队名称(SQL列"TeamName"，值"Patriots")或城市名称(SQL列"CityName"值"Chicago"等)进行方法搜索。我认为我的主要问题是我不完全确定我是否理解了字典的工作原理。此外，我不确定我返回的值应该如何工作，因为我既返回int(从out参数)又返回类型List。

c#搜索方法

这个怎么样?

public override List<Team> Search(Dictionary<string, string> prms, int pageSize, int page) 
{
    var tresults = new List<Team>();
    using (SqlConnection conn = DB.GetSqlConnection())
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = @"Search";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            foreach (KeyValuePair<string, string> pair in prms)
                cmd.Parameters.Add(new SqlParameter(pair.Key, System.Data.SqlDbType.VarChar) { Value = pair.Value });
            SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            // I assume you'll use pageSize and page here?
        }
    }
    return tresults; // I assume this is what you want to return.
}

如果你不想使用特定的列进行搜索，那么就没有必要为该列创建SqlParameter并将其Value设置为null——只是不要使用该列!

也不需要out int results。如果您要返回团队列表，那么调用者可以从列表(teams.Count)中获取团队计数。(如果您正在使用results做其他事情，那么无论如何都要忽略这一段。)

很难看出你在这里得到什么，我不确定你是否需要一个字典(你会传递多个记录吗?)

我个人会这样做，假设存储过程可以处理两个参数都被填充的可能性，如果这两个参数都是完整传递的。

public override List<Team> Search(string teamName,string cityName, int pageSize, int page) 
    {
        var tresults = new List<Team>();

        using (SqlConnection conn = DB.GetSqlConnection())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = @"Search";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    SqlParameter p1 = new SqlParameter("TeamName", System.Data.SqlDbType.VarChar);
                    p1.Value = teamName;
                    cmd.Parameters.Add(p1);
                    SqlParameter p2 = new SqlParameter("CityName", System.Data.SqlDbType.VarChar);
                    p2.Value = cityName;
                    cmd.Parameters.Add(p2);

                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                while(reader.Read())
                {
                    tresults.Add(BuildTeamFromReader(reader));
                }
            }
        }
        return tresults;
    }

    private Team BuildTeamFromReader(SqlDataReader reader)
    {
        var team = new Team();
        team.TeamName = reader["TeamName"];//or whatever your column name is for team name
//ToDo other mappings
            return team;
        }
相关文章：