c# - 在Web Api到Http请求中从Mysql数据库检索数据？

Question

我正在尝试从 WebAPI 应用程序中的 MySQL 数据库检索一组数据，并通过移动应用程序的 HTTP 请求访问它。因此，我创建了一个 WebApi、一个 RestClient 类以及我将在其中显示数据的类，这是我的代码。

Web API

[Produces("application/json")]
[Route("api/Blog")]
public class BlogController : Controller
{
    // GET: api/Blog
    [HttpGet]
    public  IEnumerable<string>  Get()
    {

    }

    // GET: api/Blog/5
    [HttpGet("{id}", Name = "GetBlogItems")]
    public string Get(int id)
    {

    }

    // POST: api/Blog
    [HttpPost]
    public void Post([FromBody]  RetrieveDataClass value)
    {
        string sqlstring = "server=; port= ; user id =;Password=;Database=;";
        MySqlConnection conn = new MySqlConnection(sqlstring);
        try
        {
            conn.Open();
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        string Query = "INSERT INTO test.blogtable (id,Telephone,CreatedSaved,Topic,Summary,Category,Body1,Body2,Body3,Body4)values('" + value.TopicSaved1 + "','" + Value.Telephone + "','" + Value.Created/Saved + "','" + value.TopicSaved1 + "','" +value.SummarySaved1 +"','" +value.CategoriesSaved1 +"','" +value.Body1 +"','" +value.Body2 +"','" +value.Body3 +"','" +value.Body4 +"');";
        MySqlCommand cmd = new MySqlCommand(Query, conn);
        cmd.ExecuteReader();
        conn.Close();

    }

    // PUT: api/Blog/5
    [HttpPut("{id}")]
    public void Put(int id, [FromBody]string value)
    {
    }

    // DELETE: api/ApiWithActions/5
    [HttpDelete("{id}")]
    public void Delete(int id)
    {
    }
}

因此，在我的数据库中，我有三行电话号码为+233892929292，经过过滤器后我必须获得三行。我还会过滤到仅主题和摘要列。

RestClient 类

   public class BlogRestClient<T>
{
    private const string WebServiceUrl = "http://localhost:57645/api/Blog/";

    public async Task<List<T>> GetAsync()
    {

        var httpClient = new HttpClient();

        var json = await httpClient.GetStringAsync(WebServiceUrl);

        var taskModels = JsonConvert.DeserializeObject<List<T>>(json);

        return taskModels;
    }


    public async Task<bool> PostAsync(T t)
    {

        var httpClient = new HttpClient();

        var json = JsonConvert.SerializeObject(t);

        HttpContent httpContent = new StringContent(json);

        httpContent.Headers.ContentType = new MediaTypeHeaderValue("application/json");

        var result = await httpClient.PostAsync(WebServiceUrl, httpContent);

        return result.IsSuccessStatusCode;

    }

    public async Task<bool> PutAsync(int id, T t)
    {
        var httpClient = new HttpClient();

        var json = JsonConvert.SerializeObject(t);

        HttpContent httpContent = new StringContent(json);

        httpContent.Headers.ContentType = new MediaTypeHeaderValue("application/json");

        var result = await httpClient.PutAsync(WebServiceUrl + id, httpContent);

        return result.IsSuccessStatusCode;
    }

    public async Task<bool> DeleteAsync(int id, T t)
    {
        var httpClient = new HttpClient();

        var response = await httpClient.DeleteAsync(WebServiceUrl + id);

        return response.IsSuccessStatusCode;
    }
}

模型数据类

    public class ModelDataClass
    {
         public string Telephone ;
         public string Created/Saved ;
         public string TopicSaved1 ;
         public string SummarySaved1 ;
         public string CategoriesSaved1 ;
         public string Body1 ;
         public string Body2 ;
         public string Body3 ;
         public string Body4 ;


         public ModelDataClass()
         {

         }
    }

ModelDataClass 中的字符串值在另一个类中设置以发布到 MySQL 数据库中。由于这不会导致相关问题，因此我没有包含代码。

检索数据类

 public class RetrieveDataClass
    {
         public string Topic ;
         public string Summary ;

         public RetrieveDataClass()
         {
            GetDataEvent();
            AddBlog();
         }

          public void GetDataEvent()
         {
          BlogRestClient<ModelDataClass> restClient = new 
          BlogRestClient<ModelDataClass>();
            await restClient.GetAsync();
         }


      public ObservableCollection<ModelDataClass> BlogItems = new  
      ObservableCollection<ModelDataClass>();

    public void AddBlog()
    {
        BlogListView.ItemsSource = BlogItems;
    }
   }

问题1如何从 Mysql 检索数据到通过 REST 客户端类访问的 WebAPI(这是针对移动设备的，所以我必须使用 Http 请求)？

问题2我想为通过 MySQL 数据库检索的每一行创建一个 listView。标题是主题列中的数据，副标题是摘要列中的数据。

Answer 1

您的应用程序是用 Multitier Architecture 设计的图案。因此，您需要确保关注点分离。

Web API 将代表您的表示逻辑层。它将解析客户端的请求，根据需要查询数据并根据需要格式化返回的数据。

然后 RetrieveClient 可以处理数据访问层。它将管理对数据库的访问，根据需要插入、更新、删除。

这里的关键点是确保每一层都与另一层通信以执行操作，并且您不会直接访问表示层中的数据库。

因此，

如何检索数据？

在数据访问层中:

public class RetrieveDataClass
{
    private IDbConnection connection;

    public RetrieveDataClass(System.Data.IDbConnection connection)
    {
        // Setup class variables
        this.connection = connection;
    }

    /// <summary>
    /// <para>Retrieves the given record from the database</para>
    /// </summary>
    /// <param name="id">The identifier for the record to retrieve</param>
    /// <returns></returns>
    public EventDataModel GetDataEvent(int id)
    {
        EventDataModel data = new EventDataModel();

        string sql = "SELECT id,Telephone,CreatedSaved,Topic,Summary,Category,Body1,Body2,Body3,Body4 WHERE id = @id";
        using (IDbCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            IDbDataParameter identity = cmd.CreateParameter();
            identity.ParameterName = "@id";
            identity.Value = id;
            identity.DbType = DbType.Int32; // TODO: Change to the matching type for id column

            cmd.Parameters.Add(identity);

            try
            {
                connection.Open();
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        data.id = reader.GetInt32(reader.GetOrdinal("id"));
                        // TODO : assign the rest of the properties to the object
                    }
                    else
                    {
                        // TODO : if method should return null when data is not found
                        data = null;
                    }
                }
                // TODO : Catch db exceptions
            } finally
            {
                // Ensure connection is always closed
                if (connection.State != ConnectionState.Closed) connection.Close();
            }
        }

        // TODO : Decide if you should return null, or empty object if target cannot be found.
        return data;
    }

    // TODO : Insert, Update, Delete methods
}

上面将从数据库中获取一条记录，并将其作为对象返回。您可以使用ORM可以使用 EntityFramework 或 NHibernate 等库，但它们有自己的学习曲线。

如何返回数据？

您的客户端将调用 WebAPI，后者又从数据访问层查询数据。

[Produces("application/json")]
[Route("api/Blog")]
public class BlogController : Controller
{
    // TODO : Move the connection string to configuration
    string sqlstring = "server=; port= ; user id =;Password=;Database=;";

    // GET: api/Blog
    /// <summary>
    /// <para>Retrieves the given record from the database</para>
    /// </summary>
    /// <param name="id">Identifier for the required record</param>
    /// <returns>JSON object with the data for the requested object</returns>
    [HttpGet]
    public IEnumerable<string> Get(int id)
    {
        IDbConnection dbConnection = System.Data.Common.DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
        RetrieveDataClass dal = new RetrieveDataClass(dbConnection);

        EventDataModel data = dal.GetDataEvent(id);
        if (data != null)
        {
            // Using Newtonsoft library to convert the object to JSON data
            string output = Newtonsoft.Json.JsonConvert.SerializeObject(data);

            // TODO : Not sure if you need IEnumerable<string> as return type
            return new List<string>() { output };
        } else
        {
            // TODO : handle a record not found - usually raises a 404
        }
    }

    // TODO : other methods
}

网上还有很多关于如何通过 API 访问数据的其他示例。看看谷歌并评论。我想到的一些是

https://learn.microsoft.com/en-us/aspnet/core/tutorials/first-web-api?view=aspnetcore-2.2&tabs=visual-studio

https://learn.microsoft.com/en-us/aspnet/web-api/overview/getting-started-with-aspnet-web-api/tutorial-your-first-web-api